Click this video (use the full screen or click to go to YouTube) to see what you may be missing in your copy of FileMaker Pro! I don't develop without it!
A Validation Calculation
Posted by: Editor / Thursday, April 20, 2000 – 12:00pm
To Validate or Not To Validate
Validating fields is a great feature in FileMaker Pro because it's event driven. Unlike scripts, validation occurs when a user exits a field. Scripts require that you perform an action like clicking a button or choosing a menu item (unless you use a plug-in). However, validation does have it's down sides. This article discusses specific validation techniques with an emphasis on when to use validation, when to avoid it and how to solve common problems.
Don't Go Crazy!
Once you learn how to use a new feature, the tendency is to overuse it. The same is true for validation. It is possible to have too many field validations in a solution. If you over validate your users, they will begin to avoid using your system. You want to make the experience of your solution as pleasant as possible.
To exemplify this point, let's talk about how to validate a phone number field. Let's say you want users to enter phone numbers in the following format:
You could create a validation calculation with the following formula:
PatternCount(Phone, "-") = 2 and Length(Phone) = 12
This formula will make sure the user only enters phone numbers that contain 2 dashes and a total of 12 characters. What it doesn't allow is flexibility in how the data is formatted. Everyone has a different method for entering phone numbers. Let's list a few other options:
If you force people to conform to your rules too often, they'll likely be unhappy and avoid using your system. If they enjoy using your system, they will look forward to using it. With this in mind, let's consider a solution called field filters. Instead of validating the field, use a Define Fields calculation with the following formula:
Left(Abs(Substitute(Phone Filter, ".", "")), 3) & "-" &
Middle(Abs(Substitute(Phone Filter, ".", "")), 4, 3) & "-" &
Right(Abs(Substitute(Phone Filter, ".", "")), 4)
Place this calculation field directly on top of the Phone Filter field it references, set it to a white fill color and uncheck the Field Format option to allow entry into the field. Whenever you click your mouse on the Phone Filter Calc calculation field, FileMaker will bring the Phone Filter text field forward temporarily, since the Phone Filter Calc field does not allow entry, and allow you to modify the phone number. When you exit the Phone Filter field, the Phone Filter Calc field will mask the Phone Filter text field.
Now users can enter their phone numbers any way they desire as long as the number contains 10 digits. The calculation, in general, removes all values except for numbers. The Abs function works very similar to the TextToNum function. It converts the contents of the referenced field to a number. Since brackets, hyphens and other characters are not numbers, you end up with just the 10 digits. The Abs function is used rather than the TextToNum function because it also removes negative numbers. When a parenthesis is converted to a number, FileMaker Pro considers it the accounting method of specifying a negative number. The Substitute function removes all periods from the phone number since they are considered decimal points when converting to a number.
It's also a good idea to provide concise yet clear validation messages. If the user is unclear about what to do, they will become frustrated. Getting them to read the message is a whole other issue. One client I've worked with would never read the error messages generated by the system. In an effort to force them to read the messages, beeps were added, the speak feature was added and the default button was switched. Nothing worked.
Anyhow, the point of this section is to point out how to create good messages and not how to get a user to read the message that appears. Let's take the example from the previous technique. Here is the validation message that appears if a phone number is entered incorrectly.
"Phone numbers must be entered in the following format (e.g. 650-962-1439). Click OK to modify your entry."
For starters, an example of how to enter the phone is provided. People love examples. Use them on validations whenever possible.
Next, notice that the user is directed to the button you wish them to use. In this case, we ask them to click the OK button rather than the Revert Field button. This can make the decision process a lot easier for the user since it is not possible to remove the Revert Field button from a validation message.
As an aside, the Revert button does not show when you are creating a new record. The Revert button only appears when you are editing a record. As soon as you exit the record, any further changes will be considered an edit. You can exit a record by typing the Enter key on the keypad, moving to a new record, moving to a new layout, moving to a new file or changing modes.
Validation by Layout
Field validation is not layout specific. If a user creates a new record on a layout that does not contain the field being validated, the validation message will still appear. If you have set the validation message to be strict, the user will be stuck on the current record with no way to correct the data entry mistake.
An easy solution is to restrict record creation on layouts where your validated fields are present. This can be done with a combination of Access Privilege passwords and scripts. If this is not an option, such as in a tabbed interface, you can make your validation specific to a particular layout. Let's use the example of validating for an empty field. Normally, you would check the box for Not Empty in the validation options. Here is the formula you would write if you only wanted the Last Name field validated on the Detail 2 layout.
Case(Status(CurrentLayoutName) = "Detail 2", not IsEmpty(Last Name), 1)
This solution works well but it is noteworthy to mention two considerations. First, if the layout name changes, you will need to change the validation formula. There is no way to make this validation formula dynamic so that it changes with the layout name. Second, the default Not Empty validation option behaves differently than a calculated validation. The Not Empty option validates the field when the record is exited. That means you can tab from field to field with the Not Empty validation option without the validation message appearing. The calculated validation will occur as soon as the field is exited.
Editor's Note: It is important to note that if no data is entered into the Last Name field when the record is initially created then the validation will not trigger until data has entered initially and the field is existed. Therefore, if you create a new record, enter data into all fields other than Last Name and exit the record the validation will not be triggered for the field, even if you are on the layout designated to be validated upon. To solve this problem you can auto enter data such as "Enter last name here". Do this using the auto-enter option.
If you didn't already know, global fields can be validated. This is not a documented feature but it has been around since FileMaker Pro version 3.0. Therefore, it is unlikely the feature will be removed. If you think about it, it makes sense. Even though global fields were created to allow for variables in FileMaker Pro, they are just another field type.
To validate a field, you need to start by creating a regular field. For example, let's create a Date field with a validation for a 4-digit year. Once you have verified it works, all you have to do is change the field to a global date field and save. It's that easy!
Think of all the uses for a validated global field. You can curb data entry into global fields which are often used for temporary records, to transfer data to find mode and many other data entry processes. FileMaker, Inc. probably never intended for global fields to be used for data entry, but many developers use them in various situations. Unfortunately, not all validation features can be applied to a global field. Here are the validation options that cannot be applied to a global field.
The validation options for Unique and Existing really don't apply since it is impossible for these options to be true on a global field.
Here are the validation options that do work with global fields.
Member of Value List
It is interesting to note that the Revert button never shows when validating a global field. This is likely explained by the fact that global fields are not record specific. Rather, global fields belong to all records. Therefore, rules regarding record creation and editing do not apply.
John Mark Osborne is the owner and president of Database Pros <http://www.databasepros.com>. Visit his web site for hundreds of free tips, tricks and techniques as well information about training and consulting.
Filed under: articles
Let us tell you when a new video is posted. We'll send you an email with a direct link right to your email inbox.Make sure and whitelist (or add to your address book email@example.com
10 Most Recent Videos