Jump to content

Validate Field Format


This topic is 6759 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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 ?

Link to comment
Share on other sites

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 smile.gif

Link to comment
Share on other sites

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) ) )

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 6759 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.