Validate Field Format

[alta_flyer]

Does anyone have a simple way to validate a field so that entries are only in the format letter/number/letter number/letter/number. I am basically trying to confirm that a valid Canadian Postal code has been entered ?

[Jim McKee]

Hi alta_flyer ...

Welcome to the Forums!

Something like this in the "Validate by calculation" option for your field ("cpc" = canadian postcode) will work to verify that 7 characters are entered, and that alpha and numeric characters are in the correct positions:

Case(

Length(cpc) = 7 and

not TextToNum(Left(cpc, 1)) and

TextToNum(Middle(cpc, 2,1)) and

not TextToNum(Middle(cpc, 3, 1)) and

Middle(cpc, 4, 1) = " " and

TextToNum(Middle(cpc, 5,1)) and

not TextToNum(Middle(cpc, 6, 1)) and

TextToNum(Right(cpc, 1)), 1,

0

)

However, this won't disallow non-letter characters like "-" "," "=" "&", etc. You could deal with these in the first line of the Case statement with multiple Substitute() functions, but it would require quite a few to deal with every possible non-letter character.

Other members will probably offer more efficient calcs

[-Queue-]

Well, mine will be both more efficient (the Case is redundant) and less efficient (any character besides a digit will make not TextToNum() true, thus *, /, +, -, !, etc. could really screw you up.

IsEmpty(cpc) or (

Length(cpc) = 7 and

PatternCount( "ABCDEFGHIJKLMNOPQRSTUVWXYZ", Left(cpc, 1) ) and

TextToNum( Middle(cpc, 2, 1) ) and

PatternCount( "ABCDEFGHIJKLMNOPQRSTUVWXYZ", Middle(cpc, 3, 1) ) and

Middle(cpc, 4, 1) = " " and

TextToNum( Middle(cpc, 5, 1) ) and

PatternCount( "ABCDEFGHIJKLMNOPQRSTUVWXYZ", Middle(cpc, 6, 1) ) and

TextToNum( Right(cpc, 1) ) )

[Jim McKee]

Queue ...

Excellent! Nice job. It's more efficient AND more comprehensive.

I gotta put a post-it on my monitor: "PATTERNCOUNT". I always forget about using that function.

[Ugo DI LUCA]

Hi

Length(TextToNum(Substitute(textentry,"0","1"))) = 3 and IsEmpty(TextToNum(Left(textentry,1))) and IsEmpty(TextToNum(Middle(textentry,3,1)))

Would it work...not tested

[-Queue-]

If you add

and IsEmpty(TextToNum(Middle(textentry,6,1))) and Middle(textentry,4,1) = " "

then I think you've got it.

Very nice, Ugo. I knew you'd upstage me.

[Ugo DI LUCA]

Right ! (about the addings... )

I was going to add it after I read yours...

[BobWeaver]

You can make Queue's validation method slightly more accurate by removing the letters D,F,I,O,Q and U from the alphabet string. These letters are not used in Canadian postal codes. So you would have:

PatternCount("ABCEGHJKLMNPRSTVWXYZ",...etc.

Might be able to adapt Ugo's method by substituting these invalid letters with some special character before running it through the rest of the function.

[alta_flyer]

I haven't tried any of these but I did come up with something that worked! I created two "GLOBAL" fields a numeric of 10 repetitions and an alphabetic of 27.

Then verified with Pattern counts that there were 3 letters and 3 numbers. Next I created 3 Middle(Text,X,1) fields to extract the 2nd,4th and 6th characters and with PatternCount and the numeric "GLOBAL" field confirmed that the total of numbers was 3. It all started by using the original suggestion on the Forum of using PatternCount! THANKS A HEAP. I may try the other suggestions if they turn out simpler.

[alta_flyer]

Hi ALL. I am not sure of the logic sequence of Jim or Ugo's suggestions but I had one problem when I use the TextToNum(text) function the letter gives a number value of 1 and of course N gives 0. A default of "Yes" and "No" I assume.

[-Queue-]

Alta, try TextToNum( "a" & text ). Older versions of FileMaker seem to do exactly as you suggested, converting logical values true, false, yes, no, and their abbreviations into boolean results. Add an 'a' before them and you circumvent this issue.

Bob, thanks for the tip. I didn't know that about Canadian postal codes.