Validate Data Entry with a Calculation

In Chapter 3 you were introduced to several ways to validate data entered into a field. But what if the Validation tab in the Field Options dialog box doesn't have a checkbox to meet your needs? For example, you might want to use validation on the Zip Code field in the Customers table. A valid Zip Code has either five characters or 10 characters (in other words, it can look like this: 90210, or this: 902101100). The closest validation option you'll find is "Maximum number of characters"close, but not right.

This situation is just the kind where the "Validate by calculation" option comes in handy. Your job is to create a calculation with a Boolean result. It should return True when the data is valid, and False otherwise. Here's how it works:

1. View the field definitions for the Customers table (In File Define Database). Select the Zip Code field and click Options.

   Click the Validation tab, and turn on "Validate by calculation."

   The Specify Calculation window appears, ready for you to enter your validation calculation.

   POWER USERS' CLINIC
   Do Not Replace Existing Value

   Auto-Enter calculation fields don't act exactly like other calculation fields. If you change the invoice date, the due date doesn't update to reflect the change. Instead, it keeps its original value, as in the example on the previous page. That's normally the way Auto-Enter calculations work: Once the field gets a value, the calculation never changes it. It only acts when the field is empty.

   Often, though, you don't want this behavior. Instead, you want it to change the field value every time any field used in the calculation is changed, just like a normal calculation. You can easily get this modified behavior by turning off the "Do not replace existing value of field (if any)" checkbox in the Field Options dialog box (shown here). When you turn this checkbox is off, FileMaker dutifully updates the field value whenever the calculation evaluatesin other words, when any field it uses changes.

   Which option you should choose depends on the situation. For the Date Due field, you probably want to turn this option off. After all, if you're changing the date of an invoice, it's reasonable to assume you want to rethink the due date as well.

   But suppose you have a database of products, and you use an Auto-Enter calculation to copy the distributor's product code into your internal product code field. If you then change the internal product code to something unique to you, you probably don't want it to change again if you switch to a different distributor. In that case, you would leave the "Do not replace existing value (if any)" option turned on, ensuring that once you've put in your own special value, it never changes.

   
    
2. From the View pop-up menu, choose "Text functions."

   The function list updates to show just the text functions.

3. In the function list, double-click "Length (text)."

   The function appears in the calculation box. Notice that "text" is already highlighted, ready to be replaced. The Length function returns the length of a text value. You use it here to see how many characters are in the Zip Code field

4. In the field list, double-click the Zip Code field.

   FileMaker puts this field inside the parentheses, where it becomes the parameter to the Length function. Now that you have a function to tell you how long the Zip code is, you need to use the comparison operator to compare it to something.

5. Click to the right of the closing parenthesis. Then, in the operators list, double-click =.

   FileMaker adds the comparison operator (=) to your calculation.

   FREQUENTLY ASKED QUESTION
   Validate Only if Field Has Been Modified

   What is the "Validate only if field has been modified" checkbox for? I don't remember seeing this in the Specify Calculation window before. For that matter, where did the Result Type pop-up menu go?

   Good eye. The Specify Calculation window can show up in lots of placeswhen defining a calculation field, when specifying an Auto-Enter calculation, and so onand it can change slightly in each case.

   First, the Result Type pop-up menu only shows up when you're defining a calculation field, since it can produce any data type. Since a validation calculation always has a Boolean result, there's no need to ask you here.

   In place of this pop-up menu, you often see some new option specific to the calculation typelike the "Validate only if field has been modified" checkbox here.

   Normally when you edit a record, FileMaker validates only the fields you actually change. Any field in the record that hasn't been changed is accepted even if it violates the validation rule. This can happen when you have your field set to validate "Only during data entry" and the records have been set some other wayfrom an import (Section 17.3) or a script (Section 17.5). If you want to validate this field whenever you edit the record, not just when the field itself changes, turn off this checkbox.

    
6. After the = operator, type 5.

   Your calculation compares the length of the Zip code to the value 5. If they're equal, it returns True. But you also want to accept a Zip code with 10 characters.

7. In the operator list, double-click or.

   The "or" operator is added to the end of the calculation. Remember that this operator connects two Boolean values and returns True if either value is true. Next, you set up the second value.

8. Double-click the Length function again, then double-click the Zip Code function again, and then double-click the = operator.

   This second check should also compare the length to some other value.

9. In the calculation box, type 10.

   Your calculation is complete. It should look like the one in Figure 9-6.

10. Click OK, then OK again, and then a third time.

    You're now back in your database and ready to test. Try giving a customer a few different Zip codes and make sure the validation works.

Most validations occur as soon as you leave the field, even if you're just moving to another field in the record. But some validation typesincluding most validation calculationsdon't happen until you exit the record.

Figure 9-6. Now you have two comparisons in your calculation. One comparison checks to see if the length is five, while the other looks for a length of 10. If either is True, your calculation is True as well, and FileMaker accepts the Zip code. If both conditions fail, the calculation result is False and FileMaker shows you an error message.

---

Tip: If you're dying to know what determines when your validation occurs, here's the skinny: If, when validating a field, FileMaker looks at the data only in the field itself, it performs the validation immediately. If it has to look at data in other fields or other records, then it waits until you commit the record before validating.

---