Writing View Formulas

The Formula language can be used in views in many places. These include selection formulas, column formulas, form formulas, and hide formulas for actions. As pointed out in Chapter 6, "Designing Views," simple actions are available for selection and column formulas, but they are not that sophisticated. Many times, you will turn to the Formula language to accomplish the desired results. The next sections discuss selection formulas, column formulas, and form formulas. Hide formulas are discussed in the section on hide formulas. Form formulas are relatively simple and are covered in Chapter 6, in the section titled "View Selection and Form Formulas."

Creating View Selection Formulas

The default view selection is SELECT @All , which retrieves all documents in the database. A database can contain several different main forms; all documents composed with all main forms are displayed in a view using the default selection formula. What if there are also some response documents and response-to-response documents? To display all of them in a view hierarchy using a responses-only column, you can add an OR condition with the @Function @AllDescendants to the formula. Remember that the pipe symbolizes a logical OR . The formula now becomes the following:

SELECT @All @AllDescendants

Using the simple actions available from the Simple Search type of view selection, you can build a reasonably sophisticated selection formula by successively adding simple actions. By default, these are connected with an AND , as shown in Figure 13.3. The first condition is By Form, and the second is By Field. Of course, there is a Formula language equivalent to this selection criterion that you can examine by switching from Simple Search to Formula:

SELECT ((Form = "Building Owners") (Form = "BO")) & (@Contains(cBuildingsOwned; "Building"))

Figure 13.3. These two simple conditions are connected by AND in the view selection window.

In general, adding Simple Search conditions to the view selection criteria will satisfy most but not all of your needs. However, many situations are unsuited to the simple actions. For example, there is no way to test for an empty value in a field. The following formula cannot be written using the Search Builder window:

SELECT ((Form = "Chapter") (Form = "CH")) & @cStatus != ""

Similarly, adding @AllDescendants to the selection formula can be done only with the Formula language. To add a selection formula, choose Formula from the Run drop-down list in the view selection window.

NOTE

For more information about creating and working with views, see Chapter 6.

Writing Column Formulas

View columns can contain simple functions, fields, and formulas. Column formulas can be written only with the Formula language. As with any other type of formula, column formulas can range from the very simple to the extremely sophisticated and complex. The simplest formula is a single field name .

Of course, because fields can be selected from the database fields list, you don't have to enter the field name in the formula window, although you can. To add a formula to a column, first open the view in design, and then choose the column by clicking the column header. There is a set of three radio buttons in the field labeled Display: Simple Function, Field, and Formula. As pointed out in Chapter 6, the simple functions are primarily view statistics such as date created, the date last modified, and attachments. Similar to the view selection conditions, you can choose a simple function and then switch to Formula and see the corresponding @Function. Unlike the simple actions for selection conditions, you cannot add these functions together to build a complex formula. Choosing Field for the display lists all the database fields.

Because a view is the primary reporting tool for a Domino database and a column is the primary vehicle for presenting data items, you will find many occasions to apply your skills with the Formula language. Some typical types of column formulas are the following:

• Counting documents in categories
• Counting documents in categories that meet certain criteria
• Summing field values and subtotaling by category
• Converting field aliases into a text equivalent
• Sorting and categorizing by date parts , such as year, month, and quarter
• Displaying an icon instead of a field value

Counting and Summing Columns in Views

Counting documents in a view is really quite simple. Just add a column with a formula of 1 (the number 1, not the character 1). Set the column Totals properties on the Sorting tab to Total, and you will get a sum in the column for each category in the view and a total at the bottom for all documents. Extending this to conditionally count documents that meet certain criteria, such as a status of Completed, is also fairly simple. Remember that Notes and Domino represent a logical True with the number 1 and a logical False with a . A simple equality formula of cStatus = "Completed" in a view column adds 1 for completed documents and for those not marked completed. Title the column appropriately, set it to Total, and you instantly have a count of completed documents. This is a relatively simple example; more complex formulas can be built as long as they evaluate to either or 1 .

TIP

When you are creating a column of sums in this manner, consider suppressing the values for the individual documents. You can do so by clicking Hide Detail Rows next to the Totals list.

Figure 13.4 shows the Sorting tab for a view column. The Totals list on the Sorting tab includes the following:

• Total
• Average Per Document
• Average Per Subcategory
• Percent of Parent Category
• Percent of All Documents

Figure 13.4. The Sorting tab provides several options to total the values in a column.

To sum the values stored in a field, simply choose the field itself in the view column and set the Totaling option.

Converting Field and Form Aliases

In keyword fields, designers can use an alias. The user sees Part III, which becomes 3 when the document is saved. Displaying a 3 in a view column is not very informative, so you might want to write a column formula such as the following to enhance the informational value of the data item:

@If( cPart = "0"; "Front Matter"; cPart = "1"; "Part I: Introduction to Domino Designer Release 5"; cPart = "2"; "Part II: Foundations of Application Design"; cPart = "3"; "Part III: Domino Programming Languages"; cPart = "4"; "Part IV: Advanced Design Topics"; cPart = "5"; "Part V: Integrating External Data"; cPart = "6"; "Part VI: Other Development Tools"; cPart = "99"; "Appendices"; "Part: " + cPart)

Similarly, if you have documents created from multiple forms displaying in the same view, you might want to differentiate among the forms by creating a column sorted and categorized on the Form field. Unfortunately, the Form field stores the alias, not the descriptive name that you present to the user. Associate of the Month becomes AOM , and Gold Team becomes GT . Needless to say, displaying the Form field in its unadulterated form isn't very informative to the users. Documents can be sorted and categorized on AOM and GT and whatever other aliases are available. The following formula, which is similar to the previous one, can be employed to compensate for this:

@If(Form = "AOM"; "Associate of the Month"; Form = "GT"; "Gold Team"; Form = "LA"; "Leadership Award"; Form)

Note that the False statement is the Form field itself. This is used as a catchall in case new forms have been added. Similarly, for the cPart field, the text constant "Part: " is added to the field cPart. If cPart doesn't match up with one of the prescribed values, the value of the field itself will be displayed. This is useful when creating a new database.

Displaying Dates

Dates in their native format, such as 10/16/2002 or 10-16-2002 , are easily recognizable and generally understood by users, but they are not always display-friendly. Quite frequently, you are asked to display the date in its more formal format, as in October 16, 2002 . Even with the increased customization available for the custom settings on the Date tab of the Column properties box, you cannot achieve this result. The following is a formula that does just that:

REM "Assign the date field to jdDate"; jdDate := dNomDate ; @Select(@Month(jdDate); "January"; "February"; "March"; "April"; "May"; "June"; "July"; "August"; "September"; "October"; "November"; "December") + " " +@Text(@Day(jdDate)) + ", " + @Text(@Year(jdDate))

TIP

Always look for opportunities to reuse code. It is easy to make this formula reusable by adding the line jdDate := fieldname , as shown in the example. To use this formula in another view column, simply substitute the new date field for fieldname .

Your client might want to know how many referrals were received in a given reporting period. Sorting and categorizing by year, quarter, or month is a typical requirement for many date-dependent views. Sorting by year is a simple matter of using @Year( DateValue ) in a sorted and categorized column. Sorting by month is a little trickier because @Month() returns a number from 1 to 12 for the month. You can convert it to the corresponding text value using the @Select() statement, but sorting on the column containing the name of the months sorts alphabetically . April then comes before January! To solve this problem, include a hidden field with the value @Month( DateValue ) . Sort the hidden column containing the month number, and sort and categorize the column containing the text value for the month. Now the view displays the months in the proper order. Figure 13.5 shows a view sorted and categorized first by the year and then by the month.

Figure 13.5. A date-dependent view sorted by the year and the month. The option View Refresh During Design is much better in Designer 6 than it was in R5.

The formula used to create the category of October 2002 is as follows :

REM "Assign the date field to jdDate"; jdDate := dDate ; @Select(@Month(jdDate); "January"; "February" ; "March" ; "April" ; "May"; "June" ; "July"; "August"; "September"; "October"; "November"; "December" ) + ", " + @Text(@Year(jdDate))

A formula for determining the quarter is as follows:

REM "Assign the date field to jdDate"; jdDate := dDate; "Quarter " + @Text(@Integer((@Month(jdDate)-1) / 3)+1)