9.2  Formula Fields

Formula fields can be used to calculate the value of a field from values of other fields in the record. This could be used, for example, to create a field for the full name of a person by concatenating the "first name" and "last name" fields. It can also be used to compose a complicated URL out of separate items in the record.

To access this feature, you have to be in Class Window mode. Add a new field, and click the Formula icon in the field details toolbar (right hand side of the window). An extra entry appears for the Formula:. Here you can type in an expression referring to other fields of the class. For example, %first name% : %last name%, would cause the new field to be populated with the concatenation of the first and last name fields.

You can use any of the following constructions to refer to a field named Xyz:
$Xyz
${Xyz}
%Xyz%
The form with curly brackets is required if the field name contains spaces.

Formulae are evaluated by substitution, so for example, if the value of Xyz was 9, then %Xyz%%Xyz% would evaluate to 99.

You can specify conditional substitutions as follows:
%Xyz?value-1:value-2%
This evaluates to "value-1" if Xyz is not null, and to "value-2" if it is null. If you wish to use a colon inside a formula as part of the result then it should be preceded by a backslash.

You can use Formula Fields to generate links to databases from values in fields. Create two fields, called pubmedid and pubmedurl. Set the field type of the pubmedurl field to Resource, and click the Formula button on the taskbar. Type a formula such as http://www.database.edu/query?id=%pubmedid%. Every record using this class will have a URL assembled from the value in the pubmedid field. For example, if the value of pubmedid was 123 the query would be http://www.database.edu/query?id=123.

Creating a Publication class - changing the pubmed link field to a formula

A formula to construct a link into the Pubmed database

The pubmed link field will be a Resource (URL) composed from the id field. This Formula constructs the Pubmed URL.

Pubmed ID being used to generate a URL via a formula field

You can now enter values for the id field and the link to Pubmed is constructed automatically. Clicking on the Preview icon opens a web browser on the appropriate Pubmed page.