Examples: Adding programming to columns

Determining text, based on a field value

In a column, you can show custom text based on a condition or on a number of conditions. For example, you can create a formula that displays custom text if sales exceed a certain amount. This formula tests the value in the Sales_February field (a number field) and displays text based on that value:

@If(Sales_February>60000;"Great month!";"")

Determining text, based on the form name

The Subject column in the All Documents view of the Mail template uses the following formula. It displays a subject line based on the form the document was created with.

@If(Form = "NonDelivery Report"; "DELIVERY FAILURE: " + FailureReason; Form = "Delivery Report"; "DELIVERED: " + Subject; Form = "Return Receipt"; "RECEIVED: " + Subject; Form = "ReturnNonReceipt"; "NOT RECEIVED: " + Subject; Subject)

Combining text and dates

You can combine text and field values in a column in a scheduling database to add an explanation about the documents. The formula as follows displays a text message and a date, with two variations that depend on the value in the Sched field. The "D1S0" part of the @Text formula is a formatting option that removes clutter from the view by showing the month and day portion of the date field only and omitting the exact time.

@If(Sched = "Special schedule"; "Special schedule for the week of: " + @Text(Date; "D1S0"); "My regular schedule, as of " + @Text(@Modified; "D1S0"))

Combining text and names

The following formula results in three possible results, based on the status of a request:

@If(Status="Rejected";"Rejected by " + LastApproveName; SignatureCount=0; "Original Request";"Approved by " + LastApproveName)

• If the request has been rejected, the column displays "Rejected by" and the name of the person who last signed the form.
• If the request has no signatures (meaning that no approver has acted on the request), the column displays "Original Request."
• If the request has been approved, the column displays "Approved by" and the name of the person who last signed the form.

Showing when a document was created

The Created column in the By Author view of the Document Library template uses the following formula to display the date on which the document was created:

@Created

Expandable levels (variation on the simple function)

The simple function Collapse/Expand (+/-) uses @IsExpandable without any arguments. If you want to display a plus symbol (+) when a document has responses, but suppress a minus symbol (-) when responses are expanded, use the formula:

@IsExpandable("+";"")

Displaying two field values in one column

To show people's names and phone numbers together in one column, create a column that is sorted in ascending order (the recommended order for alphabetical listings). The following formula separates the two field values with a blank space:

Name + " " + Phone

Creating "fake" indenting

When Notes® indents response documents, the indentation is always the same. If you want to make a document in a flat view appear indented without using response documents, or display document titles in the same column at different indentations, you can use a field on the form to determine how much each document indents and use a column formula that appends space characters to the document titles in the view. The following formula "indents" documents in three different ways depending on the value in the Indent field. For example, if the value of the Indent field is 2, Notes® prepends 18 space characters to the Subject of the document in the column.

@If(Indent = 1; @Repeat(" "; 12) + Subject; Indent = 2; @Repeat(" "; 18) + Subject; @Repeat(" "; 6) + Subject)

Numbering documents

In a By Author view, the following formula numbers (as in 1., 2., 3.) each of the documents within each author's category:

@IsCategory("";@DocNumber("") + ". ")

If the row is a category (the author's name), @IsCategory returns a null string (""). If the row isn't a category, then the row represents a document, and @DocNumber returns a string that represents the entry number of the document. The document's full entry number is something like 1.1, 1.2, 1.3, and so on, but when used with the null string (""), @DocNumber returns the rightmost component of the entry number. The formula then adds a period and a space ". " after the number.

Setting column colors programmatically

In Notes® client applications, you can set a column's background color and text color programmatically by inserting a color control column to the left of that column. A color control column has the column property "Use value as color" enabled, and its formula specifies what colors you want to use. This selection applies to all columns following the color control column, until the next color control column. Normally, you would have two color control columns, one before and one after the column(s) you want to color. The possible values for the column are:

A color field to set the text color and leave the background color unchanged.

A list of two color fields separated by a colon; the first sets the text color, the second the background color. Example formula:

textcolor:bgcolor

For example, the following formula example shows how RGB coordinates can be used in both single and paired sets.

red := 255:0:0;
blue := 0:0:255;
yellow := 255:255:0;
pink := 255:193:253;
white := 255:255:255;
black :=  1:1:1;
apricot := 255:155:133;
plain:= 0:0:0;

@If (category = "cats";blue:red ;subcategory = "collars";pink;subcategory  ="leashes";black:plain;0:0:0);

A list of three number values in the range 0 to 255 to set the red, green and blue components of the text color. The background color is left unchanged.

A list of six number values in the range 0 to 255 to set the red, green and blue components of the text color and of the background, respectively.

The special value -1:-1:-1, which reverts to the normal view colors.

You can't mix color fields and number lists. For example, textcolor:255:255:0 does not work if textcolor is a Color field. However, the Color field is just stored as a string of hexadecimal digits, so you can hardcode a color field value into the column formula as quoted text; in this case, colorfield: 
00FFFF00.

You can also set a column's background color and text color programmatically by selecting the "Use value as color" option on the Info tab of the Column Properties box and then supplying RGB coordinates in the Programmer's pane as the value for the column.

You can also set the column value equal to a field value that supplies RGB coordinates. You can use this feature in conjunction with the color field to allow a user to set a color with a color picker. You can then apply that color to a view component -- for example to the text in a column. For information on using this feature to make view colors customizable, see "Allowing users to set colors in a view."