• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL

Chapter 4. Calculation Fields > Combining Fields Using Calculations

Combining Fields Using Calculations

Calculations provide an easy way to create a new field that's a collection of existing information—in the database and on the computer system. Once you've merged the separate fields into one “super field,” creating layouts gets much easier. Compare the ease with which you can align and reposition a calculation field to how you work with the individual fields, and we think you'll be convinced (Figure 4.1).

Figure 4.1. The layout on the top uses three text fields, the one on the bottom uses one calculation field. The printed document looks the same either way.

In these steps the calculation field Full Name combines the First Name, Middle Initial, and Last Name fields (with spaces in between) into a single field that you can place in any layout instead of the three separate fields. It also leaves out the Middle Initial (and the extra spaces around it) if a name doesn't contain one.

To combine fields

Choose File > Define > Database. (Control+Shift+D/Command+Shift+D).

When the Define Database dialog box appears, click the Fields tab. Type the name of your field in the Field Name text box. From the Type drop-down list, select Calculation, then click Create (Figure 4.2). In this example, we use Full Name.

Figure 4.2. Create a calculation field and call it Full Name.

The Specify Calculation dialog box appears. In the function list on the right, double-click the Trim function to begin the calculation in the formula box.

Trim seeks out initial and trailing spaces that surround other text and “trims” them away from the text or number. The parameter “text” will be highlighted in the formula box (Figure 4.3).

Figure 4.3. The Trim function removes any extra spaces entered before or after the text in a field.

The Trim function acts on the field that replaces its parameter. In the field list on the left, double-click the first of the fields you want to combine. This example uses First Name (Figure 4.4).

Figure 4.4. The Trim function will trim the First Name field in this example.

Click to the right of the parentheses. In the Operators keypad, click the ampersand button (&) to add it to the formula box (Figure 4.5).

Figure 4.5. Choose the ampersand (&) operator to combine functions.

The quotes operator is used to add text characters to a calculation. In the Operators keypad, click the quotes button and type a space inside the quotes. Click to the right of the quotes in the formula box and click the ampersand button again (Figure 4.6).

Figure 4.6. Type a space within the quotes in the formula box to add a space to the text.

To leave out a field and its extra space when that field is empty, double-click If to choose it from the function list (Figure 4.7).

Figure 4.7. Use an If statement to test whether or not a field contains data.

If the name has a middle initial, you'll need to add a space after it. But some names don't have middle initials. You need a function that will allow for both possibilities. The If statement checks the field to see whether or not a situation exists. It can have two results: result one (if test is true) and result two (if test is not true).

In the formula box, double-click the “test” parameter in the If statement to select it. In the function list, double-click IsEmpty (Figure 4.8).

Figure 4.8. To test whether a field is empty, use the IsEmpty function as the test parameter.

From the field list, double-click the next field you want to add to the calculation field.

This example uses the MI field (Figure 4.9). The IsEmpty function looks at this field to see if there is anything in it.

Figure 4.9. The IsEmpty function looks to see if the MI field in a record is empty or contains data.

In the formula box, double-click to highlight “resultOne” (Figure 4.10). In the Operators keypad, click the quotes button to add a set of quotes to the calculation.

Figure 4.10. To ignore the field if it's empty, replace the “resultOne” parameter with the quotes operator and don't type anything inside the quotes.

By replacing the “resultOne” parameter with the quotes operator, without typing anything inside the quotes, you ignore an empty field. (Much nicer than using the Sliding command!)

In the formula box, double-click to highlight “resultTwo.” In the function list, double-click Trim. The “text” parameter is highlighted in the formula box (Figure 4.11). Since you'll want to trim the second field if it has data in it, double-click the same field you chose earlier in step 9.

Figure 4.11. To insert a field's contents if it contains data and trim any extra spaces around it, choose the Trim function and the field name.

This example uses the MI field, so Trim will cut away any extra spaces around the middle initial if the full name contains a middle initial.

In the formula box, click between the two parentheses, then click the ampersand button in the Operators keypad.

In the Operators keypad, click the quotes button. Type a space between the quotes, then click to the right of the parentheses and select the ampersand button from the Operators keypad (Figure 4.12).

Figure 4.12. To add a space to the field text, put it between the quotes.

This adds a space after the middle initial if a name has one.

In the function list, double-click Trim. With “text” highlighted in the formula box, select the next desired field from the field list.

This example uses the Last Name field (Figure 4.13). In this case, you use the Trim function to eliminate leading spaces.

Figure 4.13. In this case, the Trim function eliminates leading or trailing spaces in Last Name.

Set “Calculation result is” to Text since this calculation field won't contain any numerical calculations (Figure 4.14). Click OK, then OK again.

Figure 4.14. Use the drop-down menu to change the calculation result to Text.

Now you have a completed formula for a field. When you place this field in a layout, it will display the full name on a record in Browse mode and also on a printout.

✓ Tips

  • When you select a function, FileMaker highlights its parameter in the formula box to prompt you to choose a field.

  • The previous example can be customized to add fields at the beginning (like Dr. or Mrs.) or a title field (like Chair or Accounts Payable) at the end. Remember to add a comma to the space between quotes before the ending title field.

  • As calculations grow in complexity, they become increasingly difficult to read in the formula box. You can insert returns in long formulas to make them easier to read. FileMaker ignores the returns when evaluating the calculation. You can also add comments to help you (or someone else) remember the calculation's purpose (Figure 4.15). To add a comment, begin it with /* and end it with */.

    Figure 4.15. The same formula as in Figure 4.14 with carriage returns inserted for readability, and a comment at the end to explain the function.

  • If you want to insert a return into the text result of a calculation, click the paragraph button in the Operators keypad (). Like all other characters you want to display as text, the paragraph marker must always be surrounded by quotes in the formula.

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint