At this point you've added all your data points for your main table. But sometimes it’s useful to create fields that derive their values from other fields. In FileMaker Pro, these fields are called calculations.
Rather than being entered manually, the data in each of these fields is calculated by FileMaker Pro as the result of a formula that you specify. Often calculations are used to derive values based on other fields. You can also make use of FileMaker Pro’s many functions, including "Get" functions which return information such as the current date and time.
Let’s try creating a “Last Initial” calculation field in your main (people) table. It will return the first letter of the person’s last name.
Create a Last Initial calculation field derived from the Last Name field.
- Make sure that you have the Manage Database… dialog open with the Fields tab selected.
- For Field Name, enter Last Initial.
- Choose “Calculation” from the Type list.
- Click the Create button.
A Specify Calculation dialog box appears.
- In the large empty box (called the formula entry area), enter the following expression: Upper ( Left ( Last Name ; 1 ) )
This expression involves two functions, one enclosed inside the other. A function is a predefined formula that returns a value based on information that you give to it. You provide one or more values to the function and it returns a result.
- Left(text, numberOfCharacters) takes two values — a text value and a number. It returns a portion of the text, starting at the first character and ending at the position given by the number. For example: Left (“once”, 3) = “one”
- Upper(text) takes a text value and converts it into uppercase. For example: Upper(“loud”) = “LOUD”.
When one function is enclosed in another (or nested), FileMaker Pro starts with the one on the inside and works its way outward.
In this case, it takes the first character of the Last Name — that is, the initial — and then makes sure that it’s in uppercase.
- Make sure that the Calculation result (lower left of dialog window) is set to “Text”
- Click the OK button to dismiss the Specify Calculation dialog and create the field.