Writing Field Formulas

2017-11-03 09:05:02

Just as there are a variety of programmable events for forms, there are a number of programmable events for fields. Just a few field events are programmable with the Formula language. There are no events with mixed languages. Table 13.1 displays the events that are scriptable by the Formula language. Different events are available for the two broad types of fields, computed and editable.

Table 13.1. Programmable Field Events

Event	Field Type
Default Value	Editable
Value	Computed
Input Translation	Editable
Input Validation	Editable
Input Enabled	Editable
HTML Body Attributes	Editable and computed

Default and Value Formulas

The Default Value event executes only when a document is composed . If you create an editable field with a default value of @UserName , the name of the user will be stored in that field when the document is first composed. Because the field is editable, if the user changes it and saves the document, it contains the new value, not the default value. In other words, when you edit the document again, it will not revert to the default value. A default value formula for an editable field is not required.

All computed fields have a similar event, the Value event. However, you must enter a value formula for a computed field before you can save the form.

Switching Forms

Sometimes you want to create a form that displays the data in a document in a different format from the form used for data entry. For example, you might want to do this for a special printed version of a document. A handy trick for this type of form is to use all Computed for Display fields and then reference the field itself in the Value event. Users can then switch to that form when printing, and there won't be any editable fields. An extension of this technique is to create a view and specify the print form in the form formula for the view. If you do this, users don't have to switch forms for printing.

In a typical workflow form, there is usually a field that contains the document's status. Quite often, a view will be created to sort and categorize by this status. If you created a sort on the status field, the View column would be sorted alphabetically , which might not be what you want. In a workflow process, a document passes through specific stages, reflected in the status field. For example, the status might go from New to Pending Approval, and then to Denied or Approved, and finally to Completed. A list of possible document status is shown in Table 13.2.

Table 13.2. Document Status Sorting

Life-Cycle Sort	Alphabetical Sort
New	Approved
Pending Approval	Completed
Approved	Denied
Completed	New
Denied	Pending Approval

As you can see from Table 13.2, the alphabetical sort might not be what you want to see in a By Status view. Users can still find the documents, but the sort order doesn't make much sense. It is far better to sort in the order of the life cycle of the document. To accomplish this, you can create a computed field hidden at the bottom of the form that contains numeric values for the sort order of the status conditions. The following code produces values that sort the documents in the same order as the Life-Cycle Sort column of Table 13.2:

@If(cDocStatus = "New"; 1; cDocStatus = "Pending Approval"; 2; cDocStatus = "Approved"; 3; cDocStatus = "Completed"; 4; cDocStatus = "Denied"; 5; 99)

To create a view sorted and categorized by the document status, add a hidden column in the first position of the view and set it to the numeric sort field. Set the column to sort ascending . Next, add a sorted and categorized column set to the status field. The status column will be sorted in the proper order because of the hidden column to the left. Note that although you can use text in a sort field, numbers sort more easily.

Storing and Sorting Status Fields

As you can imagine, this technique is not the only way to handle a view sort. You can include the formula listed previously in the first View column, set it to sort ascending, and hide the column. You can also include synonyms in a keyword field, separating the two values with the pipe symbol ”for example: New 1 and Pending 2 . However, I personally don't like using synonyms. You still end up doing the same work in a view, including a hidden sort column with the synonym and a second column that translates the synonym back into text.

Input-Translation Formulas

Input-translation formulas are used to enforce rules for the format of data in fields. The Input Translation event executes when the document is being refreshed and when the document is being saved. This event executes before the Input Validation event. It is available only for editable fields. Using this event, you can modify the contents of the field after the user has entered data. The result of the input-translation formula replaces the field's contents.

In some programming languages, you can enter a mask for a field that ensures that data is entered and displayed in a specific format. In Domino, this is not available as a field property, and you must write some code to enforce the desired format. This is done in the Input Translation event of the field. For example, it is relatively easy to enforce the case of a field, as shown in Table 13.3.

Table 13.3. Input-Translation Formulas

Purpose	Formula
Proper-case a city name	`@ProperCase(cCity)`
Uppercase a state name	`@UpperCase(cState)`

However, enforcing more complicated patterns is not always an easy task. Phone numbers are good examples of more difficult formats with which you might have to contend. Usually, you enter a phone number in one of the following commonly used formats (in the United States, at least):

999.999.9999

(999) 999-9999

999-999-9999

Because the user can enter the number in many different formats, you must test for many things, including length, to see whether the user entered an area code, used an incorrect length, or used incorrect characters ; to see what type of separators are used; and so forth. The following example first strips out punctuation such as parentheses, periods, and dashes, leaving only numbers. Then it tests for the length; if it is over 7, it assumes that there is an area code and builds the number accordingly . Otherwise, the formula builds a local number. The format it returns is (999) 999-9999 with an area code, or 999-9999 without the area code. Bear in mind that the following formula works only with U.S. phone numbers:

REM "Get rid of the separators, if any" ; jcHomePhone1 := @ReplaceSubstring(cHomePhone; "("; "") ; jcHomePhone2 := @ReplaceSubstring(jcHomePhone1; ")"; "") ; jcHomePhone3 := @ReplaceSubstring(jcHomePhone2; "-"; "") ; jcHomePhone := @ReplaceSubstring(jcHomePhone3; "."; "") ; REM "Determine the length, and if it is over 7, assume an area code."; REM "If not, assume no area code. Put the pieces together accordingly."; @If(@Length(jcHomePhone) > 7; "(" + @Left(jcHomePhone; 3) + ") " + @Middle(jcHomePhone; 3; 3) + "-" + @Right(jcHomePhone; 4); @Length(jcHomePhone) = 7; @Left(jcHomePhone; 3) + "-" + @Right(jcHomePhone; 4); cHomePhone)

Of course, you can get more sophisticated and add code to test for international numbers, but this should serve as a good example of what you can accomplish in the Input Translation event of a field.

Input-Validation Formulas

Like the Input-Translation formulas, the Input Validation event is available only for editable fields. This event also executes when a document is refreshed and when a document is saved. You use this event to make sure that the entered data is valid or to ensure that there actually is data present if the field is required.

The Input Validation event executes after the Input Translation event. If the data entered in the field fails the validity test of this event, an error message can be displayed and the user (at least in a Notes client) is returned to the field with the offending entry. In a Web client, an error message is displayed on a separate HTML page.

NOTE

Notes and Domino ensure that the correct data type is entered. For example, users cannot enter AABC in a numeric or time field. An error message is generated, and the document cannot be saved until an entry of the correct data type is made.

Continuing with the phone number example from the previous section, you might want to ensure that users don't enter characters instead of numbers for the phone number. First, remove any punctuation so that a legitimate phone number won't generate an error, as in the following code:

If the phone number is entered properly, converting the text value to a number should not generate an error, so the test @If(@IsError... is used. If an error is generated, a message box is displayed using @Failure() . Both the input-translation and the input-validation formulas can act in concert, as in this case. As the code is written now, if you were to enter ABCDEFGHIJ , you would get the error message contained in the @Failure() statement, but the input-translation formula would be applied anyway and the field would then look like (ABC)DEF-GHIJ . You need to stop the input-translation formula from executing using @Return() , as in the following code:

REM "Get rid of the separators, if any" ; jcHomePhone1 := @ReplaceSubstring(cHomePhone; "("; "") ; jcHomePhone2 := @ReplaceSubstring(jcHomePhone1; ")"; "") ; jcHomePhone3 := @ReplaceSubstring(jcHomePhone2; "-"; "") ; jcHomePhone := @ReplaceSubstring(jcHomePhone3; "."; "") ; REM "Make sure we have all numbers. If not, stop the execution"; REM "of this formula."; @If(@IsError(@TextToNumber(jcHomePhone)); @Return(cHomePhone); @Success) ; REM "Determine the length, and if it is over 7, assume an area code."; REM "If not, assume no area code. Put the pieces together accordingly."; @If(@Length(jcHomePhone) > 7; "(" + @Left(jcHomePhone; 3) + ") " + @Middle(jcHomePhone; 3; 3) + "-" + @Right(jcHomePhone; 4); @Length(jcHomePhone) = 7; @Left(jcHomePhone; 3) + "-" + @Right(jcHomePhone; 4); cHomePhone)

Frequently, you need to require the entry of data in a field. You accomplish this with input-validation formulas such as the following:

REM {Now test for a numeric value, and since this is a required}; REM {field, make sure there is data present.}; @If(cHomePhone = "" @IsError(@TextToNumber(jcHomePhone)); @Failure("Please use the format (999) 999-9999 for the Phone Number Field!"); @Success)

This formula builds on the earlier version of the home phone number input validation by adding a test for an empty field.

If the user leaves the Home Phone Number field empty and either refreshes or tries to save the document, this formula executes. The prompt box shown in Figure 13.2 displays. Furthermore, this keeps the user from saving the document. This is truly a very convenient feature, at least for you (although some users might get annoyed when they can't break the rules).

Figure 13.2. The error window generated by @Failure displays the message you typed within the quotes.

CAUTION

Be careful using input-validation formulas in forms used on the Web. Remember that the Web is page-based; when an error condition is generated, no dialog box is available to alert the user. Instead, a new HTML page is generated. This is particularly inconvenient if there are multiple fields with input-validation tests. If your application runs on the Web, you can use the JavaScript event onBlur . For more information on JavaScript, see Chapter 16, "Writing JavaScript for Domino Applications," and Chapter 17, "Real-World JavaScript Examples."

HTML Attributes for Fields

The HTML attributes available for fields are based on the HTML form tags , , and </tt> . As a Domino developer, you don't have to worry about the tags themselves ”the HTTP task generates them for you. These tags are HTML equivalents of data entry fields. </p> <p> You can modify certain attributes that control the display of fields to Web clients . A rich-text field is equivalent to a <tt> <TEXTAREA> </tt> in HTML and takes the <tt> ROWS </tt> and <tt> COLS </tt> tags. As the names imply, <tt> ROWS </tt> specifies the number of rows for the field and <tt> COLS </tt> specifies the width in columns . For example, <tt> "ROWS=5 COLS=30" </tt> sets up a multiline text area in an HTML document that is 5 rows deep and 30 columns wide. Table 13.4 lists commonly used tags. </p> <h5> Table 13.4. Field Body Attribute Tags </h5> <table border="1" cellspacing="0" cellpadding="1" width="100%"> <colgroup align="left" span="3"> <tbody> <tr> <th align="left" valign="top"> <span> Attribute </span> </th> <th align="left" valign="top"> <span> Tag </span> </th> <th align="left" valign="top"> <span> Effect </span> </th> </tr> <tr> <td align="left" valign="top"> <tt> SIZE </tt> </td> <td align="left" valign="top"> <tt> <INPUT> </tt> </td> <td align="left" valign="top"> Display size of the field. </td> </tr> <tr> <td align="left" valign="top"> <tt> MAXLENGTH </tt> </td> <td align="left" valign="top"> <tt> <INPUT> </tt> </td> <td align="left" valign="top"> Maximum number of characters the field will accept. If greater than the <tt> SIZE </tt> , the field scrolls during entry. </td> </tr> <tr> <td align="left" valign="top"> <tt> ROWS </tt> </td> <td align="left" valign="top"> <tt> <TEXTAREA> </tt> </td> <td align="left" valign="top"> Number of rows. </td> </tr> <tr> <td align="left" valign="top"> <tt> COLS </tt> </td> <td align="left" valign="top"> <tt> <TEXTAREA> </tt> </td> <td align="left" valign="top"> Number of columns. </td> </tr> <tr> <td align="left" valign="top"> <tt> WRAP </tt> </td> <td align="left" valign="top"> <tt> <TEXTAREA> </tt> </td> <td align="left" valign="top"> <p> Determines how text flows in a text area. </p> <p> <tt> VIRTUAL </tt> allows words to wrap within the text box. </p> </td> </tr> </tbody> </table> <adsense> <p> To restrict the length of the entry in a standard text field for a Notes client, you can use an input-validation formula to return an error using something like <tt> @If(@Length(cHomePhone) > 14, @Failure(...) </tt> . The HTML equivalent is to add the following code to the HTML body attributes: </p> <pre> "SIZE=14 MAXLENGTH=14" </pre> <p> Unlike a Notes client, which enables you to continue typing and alerts you only when you refresh or save the document, a Web client does not permit entry beyond 14 characters. The HTML attributes can be very handy for Web forms. </p> </td> </tr> </tbody> </table> <table width="100%" border="0" cellspacing="0" cellpadding="0"> <tbody> <tr> <td valign="top"> <h3>Writing View Formulas</h3>

Switching Forms

Storing and Sorting Status Fields

Категории