Database Reference
In-Depth Information
POWER USERS' CLINIC: FIELD FORMATTING CALCULATIONS
Now that you understand most of FileMaker's calculation power features, you're ready to see
something really powerful. You've already seen how an auto-enter calculation can clean up a phone
number during data entry (
Using the Self() Function
). This calculation, which consolidates a lot of
techniques you've covered in this section, goes one step further:
Let (
cleanPhone = Filter ( Self; "0123456789"
) ;
Case (
Length( cleanPhone ) = 10 ;
"(" & Left ( cleanPhone ; 3 ) & ") " &
Middle ( cleanPhone ; 4 ; 3 ) &
"-" &
Right ( cleanPhone ; 4 ) ;
Self
)
)
First, the calculation uses the Filter function to remove any non-numeric characters from the entered
phone number and puts the result in a variable called
cleanPhone
. Then, if
cleanPhone
has exactly
10 digits, the calculation breaks it apart according to the format you want. Otherwise, it just returns
the phone number the way the person entered it.
To make the calculation work properly, in the Field Options dialog box, be sure you turn off “Do not
replace existing value (if any).”
Using Storage Options
Calculation fields have storage and indexing options, just as other field types do. The results
of most calculations are stored when you define the calculation field and again each time the
value in a field that's referenced in the calculation changes. Storage options let you control
that behavior (
Figure 16-2
).
Understanding Stored and Unstored Calculation Fields
Values in calculation fields are updated when the values in fields referenced in their formulas
change. So even though the value in a calculation field can change, it's considered a
stored
value. When you create a calculation field, the value is automatically stored, and in most
cases you'll leave it that way.
