This lesson requires Registrations.fp7.

Filemaker also provides the capability to restrict data type and, the ability to limit the maximum number of characters in a field. Here on campus, I have encountered databases that include term fields where data entry personnel have entered Fall 98, F98, Fall 1998, and F 98. A report subsummarizing information on term would produce four distinct groups of data sets when actually all activities occurred in Fall 1998. Our Banner student information system handles this problem quite distinctly. Banner specifies terms as beginning term year followed by 01 for Fall, 02 for Winter, 03 for Spring and 04 for Summer. Therefore, Fall 1998 is 199801. Spring 1999 is still within the 1998 academic year so is defined as 199803. Using this definition scheme, you could ensure that the banner format was used by setting the field validation to "Strict Data Type" and selecting "Numeric Only". (Setting the Data Type to "Number" is not sufficient.) You would then uncheck the "Allow user to override validation" checkbox and could display a message like: "Please use Banner format such as 200101".

1. Using the Registrations.fp7 file, select File -> Define -> Database then the Field tab. Create a Number field called "Term". Click "OK".

2. In Browse Mode, place your cursor in the new "Term" field and type "F 98" and press "Tab". Notice that this non-numeric value is allowed even though the field is set as a Number field.

3. Now, select File -> Define -> Database and the Field tab. Choose the "Term" field and select the Validation tab. Check the "Strict Data Type" checkbox and select the value "Numeric Only". Check the "Maximum number of characters" checkbox and enter "6" in the value field. Also uncheck the "Allow user to override validation" and the "Display custom message if validation fails" checkboxes. Enter "Please use Banner format - Example: 200101 (six digits)" in the message text field.

4. In Browse Mode, place your cursor in the new "Term" field again and type "F 98" and press "Tab". This time you should receive the message "Please use Banner format - Example: 200101 (six digits)" with the option "Revert Field" or "OK". Click "OK". Type "2000101" and press enter. Again you will see the validation failure message because you entered more than six digits. Click "OK". Type "200101". This value should be accepted.

Although the "Maximum Number of Characters" validation limits the number of the characters, it does not prevent too few characters however. Also, keep globalization issues in mind when using validation options. If you have address information that includes foreign addresses and have defined a single "state or province" field. Do not set the maximum characters to "2". Foreign provinces are not limited to two characters. Likewise, if you have defined a single "postal code" field, do not limit it to "Numeric Data" and five characters. Foreign postal codes frequently include alpha characters and are not limited to five characters.

 

COE Techweb

COE Home

UO Home