Verify data format on the fly?

[russconte]

I'm using FM 3.0 designing a small business database. One of the fields is an employee's Social Security Number. Is there a way to force FM to only accept entries in the xxx-xx-xxxx format? Similar question for such things as phone or fax numbers; (xxx) xxx-xxxx, making sure an email address has an "@" sign, and so forth. FM is fine with dates and currency, but I can't find anything on other formats.

I'd rather not run scripts after the data is entered, is there a way to make sure data is entered in the correct format on the fly, and to politely warn the user if it is not?

Thanks!

Russ Conte

[Vaughan]

Do a field-level validation, based on calculation. The calculation could look at the Length() and PatternCount().

[DykstrL]

You can set up 2 fields to reformat the entries.

SSAN_Entry = standard text field

SSAN_Calc = Calculation Field:

Middle(NumToText(Abs(TextToNum(SSAN_Entry)*10000000)), 1, 3) & "-" & Middle(NumToText(Abs(TextToNum(SSAN_Entry)*10000000)), 4, 2) & "-" & Middle(NumToText(Abs(TextToNum(SSAN_Entry)*10000000)), 6, 4)

Make sure the calculation is unstored and the result is text.

The users can enter anything they want in whatever format, with or without format. Converting the text to number drops or ignores whatever is not a number - even text. The (*10000000) ensures that if any decimals are entered are converted to a whole number.

The entry field is placed on the layout and is formatted to not print. Whenever the SSAN is needed to be printed, simply display the SSAN_Calc field.

The calculation field is placed on top of the entry field and should be formatted not to allow entry into the field. It should also be set with a solid color background in order to "hide" the entry field. Also make sure the calculation field is omitted from the tab order.

When the user either tabs into or clicks into the stacked fields, they will enter into the entry field. When they click or tab out, the calculation field will re-calculate a display the formatted data.

This method can even be used to format phone numbers or any other "number" type data where a specific format or mask is needed.