 |
s |
I hope you enjoy this FREE online training. Let me know if you'd like to see more! You can email me
at jmo@filemakerpros.com. And, if you really like this online training, consider one of
my live classes based on my wildly popular book and CD ROM.
Happy FileMaking!
|
Field Filters
FileMaker offers a feature for filtering dates, times and numbers. These options are found under
the Format menu while in layout mode. However, there are some major exceptions. There are no filters
for phone numbers, social security numbers, credit card numbers or any other type of data you might
want to format in a standard appearance.
Fortunately, there is an easy solution using a feature that was added in FileMaker Pro 3.0.
This feature can be found in the Field Format dialog under the Format while in layout mode.
Normally, a field is set to allow entry into a field. For this trick, you want to set a field
to not allow entry by unchecking the option.
When you combine this Field Format feature with a calculation that formats the data
(e.g. a phone number), you get exactly the same results as you do with the built-in date,
time and number formatting that FileMaker offers. Here is what the data looks when no fields
are selected.
When you select the phone number field, you can see that you can modify the unformatted version,
just like you can with formatting for dates, times and numbers.
Start by creating the calculation to filter the data input. We'll focus on the social security number
formatting. The idea behind the calculation is to remove all existing formatting and insert dashes
in the proper location. Since most people enter a SSN with dashes or no formatting at all, the formula
removes the dashes if they are present and then inserts them at the proper location using the Substitute,
Left, Middle and Right functions:
Left(Substitute(SSN, "-", ""),3) & "-" & Middle(Substitute(SSN, "-", ""),4,2) & "-" & Right(Substitute(SSN, "-", ""),4)
The calculation is pretty straightforward. The trick comes when using Field Format.
Set the calculation field to not allow entry, but leave the data entry field with the capability
of being entered. Then, place the calculation field on top of the data entry field.
NOTE: If the calculation field was placed on the layout first, it will be covered by the data entry field.
You want the calculation field to be on top. To do this, select it and choose Bring to Front from the Arrange menu.
NOTE: Since you are placing one field over the other, the field on top cannot be set to transparent.
Otherwise, the contents in the data entry will be seen below the calculation formatting that data and you
will not be able to read anything. For this reason, you will need to set the fill color of the calculation
field to an opaque color like white.
Download an example FileMaker file
.
DONE
|
|
 |
s |
Database Pros offers training classes across the globe. Classes
include the wildly popular Intermediate and Advanced Scriptology training series.
|
|
