text field validation

dmelody · March 20, 2002

How do I set up a field validation that allows only the characters "a-z" and the dash "-". I have a text field but users can enter numbers and/or other characters because the field is text. Examples of what can be entered are:

A-H

AAA

Where the characters represent a person's initials (first, middle or none, last)

I'm on a mac using FM 5.

TIA,

Diana

danjacoby · March 21, 2002

One rather lengthy way involves a "Validation by calculation" using the PatternCount formula:

PatternCount(TextField, "a") and PatternCount(TextField, "b") and PatternCount(TextField, "c") and ...

Et cetera, ad nauseum. One thing to note -- you need to have separate PatternCount entries for lower- and upper-case letters, meaning (I believe) 53 total entries, including the hyphen.

There may be a more elegant method; if so, someone will post it soon.

HTH,

Dan

RussBaker · March 21, 2002

You can use the > and < calc functions with text, so this validation will accept only text in the ranges a-z, A-Z or "-".

. text field >= "a" and text field<= "z" or text field>= "A" and text field<= "Z" or text field = "-"

BUT - without a script to loop through the characters entered, it only works on the first character. If you have the plugin which lets you execute a script on leaving a field, then you could use that to loop through all the characters entered, checking for this validation.

Or - maybe use the clairvoyance samples to check the text character by character as it is entered.

Just a thought at this stage, not a complete answer.

Russ Baker

Canberra, Australia

danjacoby · March 21, 2002

Disregard previous telegram; only smoke damage.

It appears that the solution I put up here earlier isn't working any more -- I could have sworn it worked before.

Played around with Russell's solution and came up with a long-winded version using a "Validation" field:

If((Left(TextField, 1) * "a" and Left(TextField, 1) * "z") or Left(TextField, 1) = "-",

If(IsEmpty(Middle(TextField, 2, 1)) or (Middle(TextField, 2, 1) * "a" and Middle(TextField, 2, 1) * "z") or (Middle(TextField, 2, 1) = "-"),

"", "NoGood"), "NoGood")

Etc., etc., adding the "Middle" command for as many characters as the TextField can contain.

Hm. There's gotta be a better way.

RussBaker · March 21, 2002

Diana,

I have sent you and email with an answer that works for up to 5 characters - but am having trouble posting the reply in the forum.

Russ Baker

Canberra, Australia

The Land of TWOGM

PS. Dan - Its waaaaaaay longer than yours...

danjacoby · March 21, 2002

Well, yeah, it's gonna get long -- the longer the possible entry in the field, the longer the validation calc.

You should see the calc I had to do to concatenate two different sets of related fields into one field in the parent DB. The printout of this one calc is about three pages long!

But oh, was it worth it. That one calc landed me two customers, worth thousands of dollars each.

RussBaker · March 21, 2002

Diana,

This validation will work for entry from 1 to 5 characters into the field "EntryText". It will bring up your validation warning if the field is left empty after a correction. Despite its length and "inelegance" , it actually works very quickly.

You might want to consider modifying the validation slightly because although you are willing to accept a "-", I suspect you would not actually accept that as either the first or last character. The "64=72" at the end is just a way of producing an invalid result if the length of "EntryText" is outside the 1-5 range. I have split the calc up into groups to hopefully make it easier to understand.

code:

If(Length(EntryText)=5,

(Middle(EntryText, 5, 1) >= "a" and Middle(EntryText, 5, 1) <= "z" or Middle(EntryText, 5, 1) >= "A" and Middle(EntryText, 5, 1) <= "Z" or Middle(EntryText, 5, 1) = "-") and

(Middle(EntryText, 4, 1) >= "a" and Middle(EntryText, 4, 1) <= "z" or Middle(EntryText, 4, 1) >= "A" and Middle(EntryText, 4, 1) <= "Z" or Middle(EntryText, 4, 1) = "-") and

(Middle(EntryText, 3, 1) >= "a" and Middle(EntryText, 3, 1) <= "z" or Middle(EntryText, 3, 1) >= "A" and Middle(EntryText, 3, 1) <= "Z" or Middle(EntryText, 3, 1) = "-") and

(Middle(EntryText, 2, 1) >= "a" and Middle(EntryText, 2, 1) <= "z" or Middle(EntryText, 2, 1) >= "A" and Middle(EntryText, 2, 1) <= "Z" or Middle(EntryText, 2, 1) = "-") and