Creating records in FileMaker is a multi-step process of defining fields, setting entry options for those fields, and setting relationships among the fields. This chapter covers each step in turn.
Controlling the appearance of your database and its records is covered separately in Creating Layouts starting on page 137.
FileMaker’s eight different types of fields are assigned via the Type radio buttons within the Define Fields dialog box (Figure 9.1). For step-by-step instructions on using this dialog box, see To define a field on page 110. But first, here’s a quick rundown on the best uses for each field type:
Text: A text field can contain up to 64,000 characters (letters, symbols, and numbers as text). Text fields can be sorted (usually A-Z or Z-A) and used in formulas. Even items that might not at first blush seem to be text sometimes should be placed in text fields. For example, telephone numbers usually contain non-numeric hyphens or slashes, and, so, are best made into text fields.
Number: A number field can contain up to 255 characters (numbers or other characters, which will not be treated as numbers). Number fields can be sorted (1–100 or 100–1) and used in formulas for calculations and summary fields.
Date: Date fields must contain at least the day and month of a date. Date fields can be sorted (earliest-latest or latest-earliest) and used in formulas for calculations and summary fields. To avoid any Y2K problems, be sure to use FileMaker’s new option for entering four-digit years. For more, see Validation options on page 116.
Time: Time fields can only contain the hours, minutes, and seconds of a time. Time fields can be sorted (earliest-latest or latest-earliest) and used in formulas for calculations and summary fields.
Container: Container fields hold graphics, sounds, QuickTime movies, or Object Linking and Embedding (OLE) objects (Windows only), or PDF files (Mac OS X only). Container fields cannot be sorted, but can be used in formulas for calculations and summary fields. While container fields cannot contain text or numbers, you can create—and sort—related text or number fields to describe a container field’s contents. For more information, see Understanding Formulas on page 126 and Using Calculation and Summary Fields on page 131.
Calculation: Calculation fields display the results of calculations made using other fields and, so, cannot have values typed directly into them. The result can be text, a number, date, time, or container. With the exception of summary functions, calculation fields operate on data within single records. For more information, see Using Calculation and Summary Fields on page 131.
Summary: Like calculation fields, summary fields cannot have values entered directly into them. Instead, they display summary values based on other fields in the database. In general, summary fields operate on data from a group of records. For more information, see Using Calculation and Summary Fields on page 131.
Global: Global fields display the same value in every record within a database. That value can be text, a number, date, time, or container. Typical uses include displaying boilerplate text or a company logo within each record. Global field values can be used in formulas for calculations and scripts. Since global fields appear in every record, they cannot be used to find records within a database.
As you create fields for your database, you’ll need to assign names and field types (for example, text or number), then choose how they will be displayed. The following steps cover most field types. For information on defining calculation and summary fields, see pages 131 and 136.
To create a field, choose File > Define Fields (Figure 9.2). Or use your keyboard:
(Windows) or 
(Mac).When the Define Fields dialog box appears (Figure 9.3), type the name of your first field in the Field Name text box.
Choose the type of field you want from the lower-left section of the dialog box (Figure 9.4). For more on deciding which field type best suits your needs, see Choosing a Field Type on page 108.
Once you click the Create button, the name of your new field will appear in the center window of the Define Fields dialog box (Figure 9.5).
At this point, you can repeat the steps to create another field. Or you can further define your field by highlighting its name in the center window of the Define Fields dialog box and then clicking the Options button. For more information, see Setting Field Entry Options on page 112.
When you’ve finished creating fields (you can always add more later), click Done. FileMaker will then display the created fields in Browse mode. To dress up a field’s appearance and layout, see Creating Layouts on page 137.
Tips
In naming your fields, FileMaker prevents you from using any of the symbols or words it needs to calculate functions: , (comma),
+,*,/,÷,&,=,>,<,(, .),“,;,:, AND, OR, XOR, NOT. You also cannot use words that are the names of FileMaker functions, such as Status, Count, or Sum. One last thing: Don’t start a file name with a period or a number.When defining a Global field, you’ll need to take an extra step once you’re back in Browse mode: Enter your desired information into the Global field in any record. Now select a new record—presto, the Global field is filled in.
Choose File > Define Fields (
in Windows, on the Mac) to open the Define Fields dialog box (Figure 9.3).To delete a field, click on its name in the center window, then click the Delete button. When the warning dialog box appears, again click Delete.
To add a field, type the new field’s name into the Field Name text box, check one of the radio buttons in the lower left Type area, and click the Create button.
When you’re ready, click the Done button.
Choose File > Define Fields (
in Windows, on the Mac) to open the Define Fields dialog box (Figure 9.3).To change the name of a field, click on its name in the center text window and type in a new name.
To change a field’s type, highlight the field in the center text window, then find and select your new type choice among the eight radio buttons in the lower left Type area.
Click the Save button, then click the Done button.
FileMaker’s Entry Options dialog box offers several powerful tools for speeding data entry and ensuring it meets certain standards. If more than one person will be entering data into the database, these options can reduce keyboard mistakes and problem-generating format variations. The options can be set while you’re defining fields—or added later.
FileMaker lets you customize your field entries for four general areas: Auto-Enter, Validation, Repeating fields, and Storage options. For more information, see page 114 (Auto-Enter), page 116 (Validation), page 124 (Repeating fields), and page 118 (Storage options).
Whether you want to set entry options for a new field or add them to an existing field, the steps are the same: Choose File > Define Fields. Or use your keyboard:
(Windows) or (Mac).In the center window of the Define Fields dialog box, select a field, then click the Options button (Figure 9.6). Or use the shortcut: double-click in the list on the field you want.
When the options dialog box appears, make your selections. Of the three functions handled by the dialog box (Auto-Enter, Validation, and Storage), only one appears at a time (Figure 9.7). Click the tab to reach the desired function (Figure 9.8). Once you’re done, click OK.
The Define Fields dialog box reappears. If you want to set entry options for another field, repeat steps 2 and 3. Once you’re ready, click Done.
Though you’ve changed the entry options for a field, its display remains the same until you change the layout. For more information, see To format a repeating field on page 125, To format a value list field on page 122, and Creating Layouts on page 137.
Follow steps 1-3 in To set field entry options on page 112 to reach the Auto-Enter options (Figure 9.9). Here’s how each functions:
Figure 9.9. The Auto-Enter panel’s seven checkboxes control the automatic entry of values into selected fields.
Creation Date, Time, Name: Use the first checkbox and its related drop-down menu (Figure 9.10) to have the date or time when a record is created or modified entered automatically. It can also automatically enter the name of the person who originally created the record or the name of the person who most recently changed the record. Your choice here must conform with the type of field you’ve created: If you’ve already defined the field type as Date, the Creator Name and Modifier Name choices won’t be available.
Serial number, next value, increment by: Use these checkboxes to generate a unique number for every record in a database. It’s particularly useful for invoices and other records that need one-of-a-kind identifiers. Once you’ve checked the Serial number box, you can then use the next value box to set the starting number for the next record. Starting numbers can include text at the front, such as A100 or Bin10. Use the increment by box to control whether the serial numbers increase in steps of 1, 2, 5, 10, or whatever.
Value from previous record: This checkbox can save you a bit of keyboarding if you’re creating a series of records where some of the fields need to contain the same value.
Data: Use this checkbox and the related text window to have a bit of text or a number automatically appear in a particular field.
Calculated value: Use this checkbox and the Specify button to automatically enter the results of any formula you choose. For more information, see Using Calculation and Summary Fields on page 131.
Looked-up value: Use this checkbox and the Specify button to enter a value from another database. For more information, see Creating Relational Databases on page 231.
Prohibit modification of value: This checkbox only becomes active if you’ve checked one of the previous boxes. Use it to ensure that a field’s data isn’t improperly changed. For more information, see the Strict, Display custom message choices under Validation options.
Follow steps 1-3 in To set field entry options on page 112 to reach the Validation options (Figure 9.11). These options ensure that data entered in the fields you select is correctly formatted.
Figure 9.11. The Validation panel’s nine checkboxes ensure that data entered into selected fields is correctly formatted.
Strict data type: Use this checkbox and its related drop-down menu to automatically create a Numeric, 4-Digit Year Date, or Time of Day type of field. The 4-Digit Year Date choice, which uses the format 2000 instead of 00, is intended to avoid Y2K problems. If FileMaker encounters a 2-digit year date, it uses this rule: numbers in the 90-99 range are converted to 1990-1999; numbers in the 00-10 are converted to 2000-2010.
Not empty, Unique, Existing: Use the first checkbox to make sure a field isn’t skipped during data entry. The other two checkboxes work in opposing ways—Unique ensures that a record contains a one-of-a-kind value while Existing ensures that the value is the same as that of another field.
Member of value list: Use this checkbox and its related drop-down menu to present the user with a predefined list of entry choices. Value lists may be the single best tool in speeding data entry and preventing typos in records. For more information, see Using Value Lists on page 119.
In range: Use this checkbox to ensure that the data entered falls within the range of the text, numbers, dates, or times you specify in the two entry boxes.
Validated by calculation: Use this checkbox and the Specify button to double-check a value against a chosen formula.
Strict, Display custom message: These two checkboxes control what users see if the data they enter doesn’t meet the criteria you’ve already set in the Validation dialog box. By checking Strict: Do not allow user to override validation, you prevent users from simply clicking OK and ignoring warning dialog boxes. This can be necessary if, for example, the field’s value is used in a calculation and must be in one form only. If you check Strict, however, it’s always good to also check the Display custom message if validation fails box and write a message that will explain why the entry was not accepted—and what users might do to conform to the field’s requirements. The message can contain up to 255 characters.
FileMaker’s Entry Options include setting storage options (indexing) for any text, number, date, time, and calculation field. Indexing creates an alphabetical (or numeric) list of all the values in the selected field, greatly speeding any search for records—once the index is created. But indexing also increases your database’s size and can slow down running large files. For that reason, FileMaker gives you field-by-field control of which, if any, fields are indexed. Indexing can also be used to store results for calculation fields. (See To store calculation results on page 135.)
Select a field to index by following steps 1-3 in To set field entry options on page 112 to reach the Storage options (Figure 9.12).
The lower half of the dialog box offers three index settings: On, which creates an index for the selected field; Off, which blocks FileMaker from indexing the field; and Automatically turn indexing on if needed. The third choice, which becomes available when you check the Off radio button, allows indexing only if you later use the field in a search or as part of a relational database. Make your choices, and if necessary, reselect which language the index will use by using the bottom pop-up menu.
Click OK. The Define Fields dialog box reappears. If you want to set indexing for another field, repeat steps 1 and 2. When you’re finished, click Done.
By offering users a predefined list of field entry choices, value lists save lots of time and aggravation. The more people you have entering data into a database, the more important value lists become in maintaining record consistency and accuracy. Don’t worry about locking yourself in: Like so many things in FileMaker, value lists can be altered any time. A quick-and-dirty explanation for formatting value lists is included in this section, but you’ll want to read Creating Layouts on page 137 to get a fuller sense of your formatting options for value lists. While value lists often are created at the same time you create a field, they exist independently of any particular field and can be created at any time.
Choose File > Define Value Lists (Figure 9.13).
or
Follow steps 1-3 in To set field entry options on page 112. When you reach the Validation panel, select the Member of value list checkbox, then click the adjacent drop-down menu and select Define Value Lists (Figure 9.14).
When the Define Value Lists dialog box appears, click New to create a new list or Edit if you want to change an existing value list (Figure 9.15).
When the Edit Value List dialog box appears, a generic name will appear in the Value List Name text box and the Use custom values radio button will be selected. Type in a distinctive name for your new value list, then click inside the blank right-hand text box to enter the custom values you want (Figure 9.16). (You can also create a value list using values from an existing field. See the following steps, To define a value list using another field.)
Type in your first value, then press
(Windows) or 
(Mac) to begin a new value. When you’re done adding values to the list, click OK. To create another value list, repeat steps 2 and 3.When you’ve finished creating lists, click Done (Figure 9.17). Though you’ve made the field into a value list, its display will not change until you change the layout. See To format a value list field on page 122 for a quick rundown. For more information, see Creating Layouts on page 137.
Choose File > Define Value Lists.
When the Define Value Lists dialog box appears, click New to create a new list or Edit if you want to change an existing value list (Figure 9.15).
When the Edit Value List dialog box appears, select the second radio button, Use values from field, and click Specify.
The Specify Fields for Value List dialog box appears listing all the fields within your current database (Figure 9.18). If one of these suits your purposes, select it in the left-side list, and click OK. More likely, you’ll want to use values from a field in another FileMaker record, so click the Specify File button.
Navigate your way to the FileMaker database file you want and open it. A new dialog box will appear listing all of that database’s fields. Select the one you want to use and click OK. Those values will now be used in your pop-up list.
Again, you’ll need to switch to Layout mode to format how you want the value list to appear. See the following steps, To format a value list field, for a quick rundown. For more information, see Creating Layouts on page 137.
If you haven’t already defined your value list, see To define a custom value list on page 119. When you’re ready to format a field, switch to Layout mode (
for Windows, 
on the Mac). Select the field you want to format by clicking on it.Choose Format > Field Format (Figure 9.19). The Field Format dialog box will appear (Figure 9.20).
Choose the second radio button, which will activate the adjacent Pop-up list menu, as well as the adjacent using value list pop-up menu (Figure 9.21).
Select which style you want for your value list: Pop-up list, Pop-up menu, Check boxes, or Radio buttons (Figure 9.22). Also select from the second pop-up menu the value list you created for the field when you defined it earlier.
The Style section of the Field Format dialog box includes three other checkboxes to make your value list formatting more flexible for the user (Figure 9.20). The last two checkboxes can work as double-edged swords so consider whether you want to give users that flexibility or whether you’d prefer to limit entries to what’s in the value list.
Choose Include “Other...” item to allow entry of other values if you want to let users enter a value that’s not in your value list. This formatting option can be added to every value list format except the Pop-up list. If the user picks the Other... choice, a dialog box will open, allowing the user to add another value (Figure 9.23).
Figure 9.23. Checking Include “Other...” (top) allows users to add items not already in the value list (bottom).
Choose Include “Edit...” item to allow editing of value list if you want to let users change the choices in your value list. This choice can only be used for the Pop-up list and Pop-up menu formats. If the user picks the “Edit...” choice, a dialog box will open allowing the user to change an existing value or add more values to the list (Figure 9.24).
When you’re done making your selections, click OK at the bottom of the Field Format dialog box. Switch to Browse mode (
for Windows, 
on the Mac) and your field will appear with its new formatting.
Tip
Which value list style best suits your needs? The Pop-up list and Pop-up menu options simply show a blank field—with no clue that it holds multiple choices—unless the user clicks on it. That’s handy if you don’t have much screen space or want a clean, simple look. The checkbox and radio button options let the user immediately see all the choices for the field. Checkboxes allow multiple selections, while radio buttons only allow one selection at a time.
Repeating fields let you create a single field that accommodates more than one value—saving you the trouble of creating multiple fields that might not always be needed. A quick-and-dirty explanation for formatting repeating fields is included in this section. But to get a fuller sense of your formatting options see Creating Layouts on page 137.
Choose File > Define Fields (
in Windows, on the Mac). When the Define Fields dialog box appears, double-click the name of the field you want to define as repeating (Figure 9.25).When the Options for Field dialog box appears, click the Storage tab, and choose the Repeating field with a maximum of __ repetitions checkbox (Figure 9.26). Fill in the blank with the number of repetitions you want and click OK.
The Define Fields dialog box will reappear; click Done. Though you’ve now defined the field as repeating, its display will not change until you change the layout. See To format a repeating field on the next page for a quick rundown. For more information, see Creating Layouts on page 137.
Switch to Layout mode (
for Windows, on the Mac). Select the field you want to format by clicking on it.Choose Format > Field Format and the Field Format dialog box will appear. Within the box’s Repetitions section, enter how many times you want the field to appear and choose its orientation (Figure 9.27). Choosing vertical will stack the fields; horizontal will place them side by side. Click OK.
FileMaker will return you to the Layout view of the field and the rest of the record. Switch to Browse mode (
for Windows, on the Mac) to see the new format (Figure 9.28).
Tip
In the above example, the repeating fields appear as Pop-up lists, but you can use the Style section’s pop-up menu to have them appear in any of the other three formats. The two pop-up formats work best for repeating fields, however, since the checkbox and radio button options will produce a blizzard of boxes and circles.
Formulas are used in two kinds of FileMaker fields: calculation fields and summary fields. For the most part, formulas used in calculation fields operate on data in the current record. Formulas used in summary fields operate on data from more than one record.
Beneath a sometimes confusing raft of terms and definitions, formulas are simple. Using a set of specific instructions, formulas take data from one or more fields, calculate or compare or summarize it, and then display the results. That’s it. The twist comes in that word specific: Formulas must be constructed in a set order, or syntax. Mess up the syntax and the formula won’t work properly, if at all.
You’ll build most of your formulas within the Specify Calculation dialog box (Figure 9.29), which will go a long way in helping keep your syntax straight. The dialog box works like a construction kit with tools to let you assemble the necessary field references, constants, operators, and functions. Once the formula is run, it spits out results, whose form you also control. Before you start, however, take a moment to understand some of the key terms used in formulas.
Figure 9.29. Within the Specify Calculation dialog box, formulas are built in the center formula box using pieces taken from (upper left to right) the field reference list, the keypad and scrolling list of operators, and the functions list.
Field References: A field reference directs a formula to use the value in the field it’s named after. The left-hand list within the Specify Calculation dialog box displays all the field references in the selected database.
Constants: As the name implies, a constant is a fixed value used in a formula. It remains the same from record to record. A string of text, a number, a date, or a time can all be constants. Each of these types of constants must be typed in a particular format for the formula to recognize which type of constant it represents. For more on the required formats, see Table 9.1, Constants.
Table 9.1. Constants
FOR THIS TYPE DATA | REMEMBER TO | EXAMPLES |
|---|---|---|
Text | Enclose text in quotes (“) | “Welcome to FileMaker” “94530-3014” |
Number | Do not include currency symbols or thousand separators (, or ;) | 80.23 450000 |
Date | Use the value as parameter of the Date function or the TextToDate function. See Date functions, on page 331. | Date (3,13,1998) TextToDate (“03/13/1998”) |
Time | Use the value as parameter of the Time function or the TextToTime function. See Time functions, on page 331. | Time (10,45,23) TextToTime (“10:45:23”) |
Expressions: An expression is simply a value or any computation that produces a value. Expressions can contain field references, constants, and functions, and can be combined to produce other expressions. For more information, see Table 9.2, Expression Examples.
Table 9.2. Expression Examples
TYPE OF EXPRESSION | EXAMPLE |
|---|---|
Text constant | “FileMaker” |
Number constant | 80.23 |
Field reference | Cost per square foot |
Function | TextToDate |
Combination of expressions | (Price/House Size)*0.10 |
Operators: Operators enable a formula to compare the contents of two (or more) fields. Insert operators into your formulas using your keyboard or the keypad and scrolling list within the Specify Calculation dialog box (Figure 9.30). Operators combine expressions and resolve what operation should be performed on the expressions. For example, the addition sign, +, is simply an operator that combines the value appearing before it with the value appearing after it: Subtotal + Tax.
Figure 9.30. Build formulas using the keypad and scrolling list, which contain text, math, comparison, and logical operators.
Mathematical and text operators are used with—surprise—numbers and text. Comparison operators compare two expressions and return a result of True or False, in what is known as a Boolean expression. Logical operators compare two or more conditions, such as whether the Cost field is more than $200,000 (the first condition) and the Square footage field is less than 1,000 (the second condition). For more information, see Tables 9.3–9.6.
Table 9.3. Mathematical Operators (see Figure 9.30)
SYMBOL | NAME | DEFINITION | EXAMPLES |
|---|---|---|---|
+ | Addition | Adds two values | 2+2, Subtotal+Sales Tax |
- | Subtraction | Subtracts second value from first | 2-1,Total-Discount |
* | Multiplication | Multiplies value | Subtotal*Sales Tax |
/ | Division | Divides first value by second | Total/Units |
^ | Exponentiation | Raises first value to power of second | (A2 + B2) returns A^2B^2 |
( ) | Precedence | Expressions inside parentheses evaluated first | (5*20)/5 |
Table 9.4. Text Operators (see Figure 9.30)
SYMBOL | NAME | DEFINITION | EXAMPLES |
|---|---|---|---|
& | Concatenation | Appends the text string on right to end of text string on left | “AAA” & “BBB” returns “AAABBB” |
“ ” | Text constant | Marks beginning and end of text constant. Quotes with no text between them indicate a blank space. Text in formula without quotes is interpreted as a field name or function name. To mark a quote mark within a text constant, precede it with another quote mark. | “Welcome to FileMaker” returns as Welcome to FileMaker “ ” returns an empty (null) value “Welcome to our “favorite” place” returns as Welcome to our “favorite” place |
¶ | Return marker | Inserts a paragraph return in a text constant | “Welcome to ¶FileMaker” returns Welcome to FileMaker |
Table 9.5. Comparison Operators (see Figure 9.30)
SYMBOL | NAME | DEFINITION | EXAMPLES |
|---|---|---|---|
= | Equal to | True when both items are equal | 4=5 returns False 4=4 returns True |
≠ or <> | Not equal to | True when the items are not equal | 4≠5 returns True 4≠4 returns False |
> | Greater than | True when value on left exceeds value on right | 4>5 returns False 5>4 returns True |
< | Less than | True when value on left is less than value on right | 4<5 returns True 5<4 returns False |
≥ or >= | Greater than or equal to | True when value on left is greater than or equal to value on right | 4≥5 returns False 5≥5 returns True |
≤ or <= | Less than or equal to | True when value on left is less than or equal to value on right | 5≤4 returns False 4≤4 returns True |
Table 9.6. Logical Operators (see Figure 9.30)
SYMBOL | DEFINITION | EXAMPLES |
|---|---|---|
AND | True only when both values are true:
| Cost per square foot <200 AND Bedrooms≥ 2 |
OR | True when either value is true:
| Cost per square foot <200 OR Bedrooms≥ 2 |
XOR | True when either, but not both, of values is true:
| Cost per square foot <200 XOR Bedrooms≥ 2 |
NOT | Changes value within parentheses from false to true, or true to false:
| NOT Cost per square foot >200 |
A function is simply a predefined formula with a set name, such as TextToDate. Functions perform a particular calculation and return a single value. FileMaker comes with more than 150 functions, all of which are listed in Functions on page 329. All those functions are available via the right-hand list within the Specify Calculation dialog box, but scrolling through the whole list for one function would be a bother. Instead, use the top-right pop-up menu to display handier portions of the list (Figure 9.31). By toggling among the options, you can zero in on the function you need (Figure 9.32).
Figure 9.31. Use the Specify Calculation dialog box’s View pop-up menu to fine tune your view of FileMaker’s 150+ built-in functions.
Functions have three parts: the predefined function, the parameters used by the function, and a set of parentheses enclosing the parameter. In almost all cases, FileMaker functions follow this syntax, or order:
Function name (parameter)
For example: TextToDate (time) or Average (field)
The parameter (the value within the parentheses) can be a field reference, a constant, an expression, or another function. Sometimes a function needs more than one parameter, in which case separate each parameter from the next parameter with a comma or semicolon:
Average (field1, field2, field3). In this example, field1, field 2, and field3 are just placeholders for the actual field reference you’d place into the formula.
Calculation Results: Once the formula runs, it displays the calculation as a result. The result can take several forms—text, number, date, time, or container—which you control via the Specify Calculation dialog box. For more information, see To change the display of calculation results on page 134.
You cannot enter anything directly into a calculation or summary field. Instead, the fields store and display the results of calculations you build via the Specify Calculation dialog box or the Options for Summary Field dialog box.
Formulas used to define a calculation field can be as basic or as complex as you need and will seldom use every tool available in the Specify Calculation dialog box. Our first example walks through a very simple formula.
To create a calculation field, choose File > Define Fields (
in Windows or on the Mac).When the Define Fields dialog box appears, type into the Field Name text box the name of your field.
Select the Calculation radio button within the Type area of the dialog box, then click Create (Figure 9.33).
The Specify Calculation dialog box, where you define a formula for the selected field, appears (Figure 9.34). The simple Cost per square foot example uses just two field references and a single symbol: (sales price/square footage).
Add the Price field reference by double-clicking its name within the list, click the division symbol (/) in the symbols keypad (Figure 9.35), then double-click the House Size field reference in the left-side list. (For information on using the Storage Options button in the Specify Calculation dialog box, see To store calculation results on page 135.)
Once you’re finished building the formula, click OK.
When the Define Fields dialog box reappears, click Done.
Tip
Instead of mouse-clicking on the symbols keypad within the Specify Calculation dialog box, you can use their equivalents on your keyboard.
Selecting the Do not evaluate if all referenced fields are empty checkbox will keep FileMaker from performing a calculation unless the field referenced by the formula has a value—saving some otherwise wasted time.
To reach the Specify Calculation dialog box and edit a formula, choose File > Define Fields (
in Windows, on the Mac). When the Define Fields dialog box appears, double-click on the name of the calculation field whose formula you want to change.The Specify Calculation dialog box will show the formula in the center box. If you want to start fresh, double-click on the formula, then press
or simply click on the first piece of the new formula (usually a field reference).To edit individual parts of the formula, highlight that piece, and then click the replacement field reference, operator, or function.
When you’ve finished editing the formula, click OK, then click Done when the Define Fields dialog box reappears.
First create a calculation field and build a formula for it. (See To define a calculation field on page 131.) Now click the Calculation result is pop-up menu (Figure 9.36).
Figure 9.36. Use the Calculation result is pop-up menu in the Specify Calculation dialog box to control how the results are displayed.
By default, FileMaker displays the results of a calculation as a number, which is what’s needed in most cases. However, there are formulas that may need to be displayed as text, a time, or even a container. Make your selection among the five choices and release the cursor.
Click OK. When the Define Fields dialog box reappears, click Done.
Choose File > Define Fields (
in Windows, on the Mac).Within the Define Fields dialog box, double-click on the name of the calculation field you want to repeat. When the Specify Calculation dialog box appears, click the checkbox in the lower left labeled Repeating field with a maximum of __ values. Fill in the blank with the number of repetitions you want. Click OK.
When the Define Fields dialog box reappears, click Done. Remember: The appearance of a repeating field doesn’t change until you format it. See To format a repeating field on page 125.
Storing calculation results carries the same tradeoffs as indexing any other field: It speeds finding records but also increases your database’s size. FileMaker offers a decent compromise, however, by giving you the option of only performing a calculation (and, so, storing the result) when it’s needed, such as when you’re printing or browsing that particular field and record.
Unless you tell it otherwise, FileMaker automatically stores calculations except those from summary and global fields, as well as those that depend on another calculation already marked as unstored.
First create a calculation field and build a formula for it. (See To define a calculation field on page 131.) If you already have a calculation field defined, choose File > Define Fields (
in Windows, on the Mac).When the Define Fields dialog box appears, double-click on the name of the calculation field whose results you want to store or index. The Specify Calculation dialog box will appear with the formula in the center box. Click the Storage Options button.
The Storage Options dialog box will appear (Figure 9.37). To keep a result from being stored, select the Do not store calculation results—calculate only when needed checkbox.
Click OK. When the Define Fields dialog box reappears, click Done.
Choose File > Define Fields (
in Windows, on the Mac).When the Define Fields dialog box appears, type into the Field Name text box the name of your summary field. Select Summary within the Type panel in the lower-left section of the dialog box. Click the Create button (Figure 9.38).
When the Options for Summary Field dialog box appears, choose which type of summary you want performed from the left-hand list, and select which field you want summarized from the scrolling list in the center (Figure 9.39, Table 9.7). You can also modify several of the summary types by selecting the checkbox just below the scrolling list, whose function varies in response to which type you’ve chosen. For more information, see Table 9.7, Summary Field Types.
Click OK. When the Define Fields dialog box reappears, click Done.
Table 9.7. Summary Field Types (see Figure 9.39)
NAME
DEFINITION
OPTION VIA CHECKBOX
TO FINE TUNE OPTION:
Total of
Totals values in selected field
Running total
Average of
Averages values in selected field
Weighted average
Pick a field for averaged values
Count of
Counts how many records contain a value for field
Running total
Minimum
Finds lowest number, or earliest time or date, for field
none
Maximum
Finds highest number, or latest time or date, for field
none
Standard Deviation of
Calculates standard deviation from mean of values in field
by population
Fraction of Total of
Calculates the ratio of field’s value to total for all values in field
Subtotaled
Pick a field for subtotaled values
