FileMaker Pro 5, 4.X, 3.X
Q: I need a quick and dirty method for forcing people to correctly enter Social Security numbers. I need to make sure they enter the right amount of numbers and that they don't enter any letters.
-Segenthaler Warfield, Atlanta, GA
A: Social Security numbers are kind of a classic. By the way, FileMaker Pro developer Rich Coulombre is fond of saying, "The federal government doesn't guarantee Social Security numbers to be unique human identifiers." In other words, two people can have the same Social Security number, so you shouldn't count on it for a primary key (aka unique record identifier) in your databases -- especially if you’re dealing with large populations of people.
To force people to enter values in a certain way, you need to use FileMaker’s validation feature. You might want to handle this a couple of different ways. If you just want people to enter numbers so you can use a calculation to add in the appropriate delimiters, you only need to validate for nine digits and no alphabetical characters. The calculation to verify the correct number of digits and the correct length is as follows:
(Length( SSN Entry) = 9) and (Substitute(Substitute(Substitute(Substitute(Substitute(Substitute(Substitute(Substitute(Substitute(Substitute(SSN Entry, "0", ""), "1", ""), "2", ""), "3", ""), "4", ""), "5", ""), "6", ""), "7", ""), "8", ""), "9", "") = "")
This calculation first checks to see that the user entered only nine characters. Those nine characters could be letters, though, so the next part of the calculation checks to see that no letters have been entered. Because there isn’t any method of distinguishing letters from numbers, you need to use the process of elimination. By substituting blanks in for each number, you should be left with blank. If the result isn't "", that means a letter or some punctuation character was entered somewhere in the string. You might try creating a new calculation field and using the Substitute portion of the above calculation. You’ll see that if you type in 12345A67/, the result will be A/.
If you want users to enter delimiters with their entry, you need to validate for 11 characters, no alphabetical characters, and delimiters in the proper locations. Assuming your delimiters are "-", the calculation to validate on this scenario is:
(Length( SSN Entry) = 11) and (Substitute(Substitute(Substitute(Substitute(Substitute(Substitute(Substitute(Substitute(Substitute(Substitute(SSN Entry, "0", ""), "1", ""), "2", ""), "3", ""), "4", ""), "5", ""), "6", ""), "7", ""), "8", ""), "9", "") = "--") and (Position(SSN Entry, "-", 1, 1) = 4) and (Position(SSN Entry, "-", 1, 2) = 7)
So you don’t confuse your users, you should enter a custom validation message such as:
The SSN must be in the following format:
XXX-XX-XXXX
...and it must contain only numbers, no letters.