UK Postcode validation field

[MnR]

hi there,

New to FileMaker Pro, just converted from Micro$oft Access. My problem is with field validation, I basically need a field to stop a user entering an incorrect postcode uk format e.g. LN94 4FM. I guess i must have to use a calculation or script but i need some help plz!

MnR

[transpower]

You could have a valuelist of the postcodes (from another table) popped-up, for the user to choose.

[stanley]

MnR:

Welcome to the Forums.

I know there's a db file out there somewhere with all the UK postcodes in it, but can't think of where I've seen it. If I find it, I'll post a link. Otherwise, you've got to lay out all the rules for UK postcodes, and parse to see if an entered postcode fits within those rules. However, I seem to remember that postcodes in the UK vary a bit in their formats, in that the first set can be XXnn (two letters, two numbers) or XXn (two letters, one number.) If the format is always the same, it's fairly easy to parse for it (like the US Zip Code system, which is either five digits (nnnnn) or nine digits (nnnnn-nnnn).

-Stanley

[MnR]

Thanks for you help but I was looking for a more script based format, seeing as the database in going to only be used locally then the format will remain the same (BS48 5TT). A good idea about the list but that would take years to make :

[MnR]

hello help would be nice, hate to double post!!!!

[Paul de Halle]

I take it you are using FMP 7? If so...

First set the field validation to a calc to restrict the user to only entering 6 or 7 characters ie:

 Let(



PC = Substitute ( postcode ; " " ; "" ) ;



Length(PC) = 6 or Length(PC) = 7



) 

 

This will also strip out any spaces they insert in the wrong place.



Next set the auto-enter calculated value to:



 

 Let(



PC = Substitute ( postcode ; " " ; "" ) ;





Case( 



Length(PC) = 6 ;



Upper( Replace ( PC; 4 ; 0 ; " " ) ) ;



Upper( Replace ( PC; 5 ; 0 ; " " ) )

)



) 

and uncheck the 'Do not replace existing value of field.

I think should do what you require.

Cheers

Paul

[MnR]

wow thanks a lot, i don't know how people know this!!

[Codeus]

I realise this is an old thread but its still relevant.

The validation calc below tests to make sure the format is either "XXnn nXX" or "XXn nXX" (ignoring whitespace).

Hope someone finds it useful.

Codeus

Let(

[

  strippedpostcode = Substitute ( AddressPostcode ; " " ; "" ) ;

  textportion = Left(strippedpostcode;2)&Right(strippedpostcode;2);

  numericportion = If(Length(strippedpostcode)=7;Middle ( strippedpostcode ; 3 ; 3 );Middle ( strippedpostcode ; 3 ; 2 ))

];



  textportion = Filter(textportion;"ABCDEFGHIJKLMNOPQRSTUVWXYZ")

  and

  numericportion = Filter(numericportion;"1234567890")



)

[comment]

I believe it's a bit more complex than that. For example, your test would pass "XX1 1XXANYTHING" as valid.

And it seems there are a lot more rules to follow:

http://en.wikipedia.org/wiki/UK_postcodes#Validation

http://www.evoxfacilities.co.uk/evoxps.htm

[Codeus]

Hi comment,

Thanks for the links, I didn't realise the format was so complex. Native regex support would be a great addition to FileMaker!

I re-worked the validation based on the simple regex formula as per the link to wikipedia: -

Let(

[

  sp = Substitute ( AddressPostcode ; " " ; "" ) ;

  c1=Left(sp;1);

  c2=Middle(sp;2;1);

  c3=Middle(sp;3;1);

  c4=Middle(sp;4;1);

  c5=Middle(sp;5;1);

  c6=Middle(sp;6;1);

  c7=Middle(sp;7;1)

];

 Length(sp)=5 and 

 c1 = Filter(c1;"ABCDEFGHIJKLMNOPQRSTUVWXYZ") and 

 c2 = Filter(c2;"0123456789R") and

 c3 = Filter(c3;"0123456789") and

 c4 = Filter(c4;"ABDEFGHJLNPQRSTUWXYZ") and

 c5 = Filter(c5;"ABDEFGHJLNPQRSTUWXYZ")

or 

 Length(sp)=6 and 

 c1 = Filter(c1;"ABCDEFGHIJKLMNOPQRSTUVWXYZ") and 

 c2 = Filter(c2;"ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789") and

 c3 = Filter(c3;"ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789") and

 c4 = Filter(c4;"0123456789") and

 c5 = Filter(c5;"ABDEFGHJLNPQRSTUWXYZ") and

 c6 = Filter(c6;"ABDEFGHJLNPQRSTUWXYZ") 

or 

 Length(sp)=7 and 

 c1 = Filter(c1;"ABCDEFGHIJKLMNOPQRSTUVWXYZ") and 

 c2 = Filter(c2;"ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789") and

 c3 = Filter(c3;"0123456789R") and

 c4 = Filter(c4;"ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789") and

 c5 = Filter(c5;"0123456789") and

 c6 = Filter(c6;"ABDEFGHJLNPQRSTUWXYZ") and

 c7 = Filter(c7;"ABDEFGHJLNPQRSTUWXYZ") 

)

I am not sure it is 100% bullet-proof and it seems a bit agricutural but it does length check and support most of the rules..... I think!

Codeus

[comment]

I think I would do something like:

Let ( [

preMask = Substitute ( Leftwords ( Postcode ; 1 ) ;  

[ "C" ; "@" ] ; [ "I" ; "@" ] ; [ "K" ; "@" ] ; [ "M" ; "@" ] ; [ "O" ; "@" ] 

) ;



mask = Substitute ( preMask & "-" & RightWords ( Postcode ; 1) ;  

[ "0" ; "#" ] ; [ "1" ; "#" ] ; [ "2" ; "#" ] ; [ "3" ; "#" ] ; [ "4" ; "#" ] ; [ "5" ; "#" ] ; [ "6" ; "#" ] ; [ "7" ; "#" ] ; [ "8" ; "#" ] ; [ "9" ; "#" ] ; 

[ "A" ; "@" ] ; [ "B" ; "@" ] ; [ "D" ; "@" ] ; [ "E" ; "@" ] ; [ "F" ; "@" ] ; [ "G" ; "@" ] ; [ "H" ; "@" ] ;  [ "J" ; "@" ] ; [ "L" ; "@" ] ; [ "N" ; "@" ] ; [ "P" ; "@" ] ; [ "Q" ; "@" ] ; [ "R" ; "@" ] ; [ "S" ; "@" ] ; [ "T" ; "@" ] ; [ "U" ; "@" ] ; [ "V" ; "@" ] ; [ "W" ; "@" ] ; [ "X" ; "@" ] ; [ "Y" ; "@" ] ; [ "Z" ; "@" ] 

) 

] ;

not isEmpty ( FilterValues ( mask ; "@#-#@@¶@@#-#@@¶@##-#@@¶@@##-#@@" ) )

)

Note that this requires the correct placement of the space, which I believe is a required part of a valid code. If you want to be more forgiving, you could auto-correct the entry by removing all spaces and forcing a single space before the last 3 entered characters. Converting everything to upper characters could also be useful.

[Codeus]

That's a great bit of logical thinking, I hadn't considered using FilterValues like that and can see many places I can use it for validating other things, thanks for the tip!

I agree the space is a requirement, I have been using an auto-enter to do that and also convert to upper before validation: -

Let(

sp = Substitute ( AddressPostcode ; " " ; "" ) ;

Upper( Replace ( sp; Length(sp)-2 ; 0 ; " " ) )

)

Thanks again,

Codeus

[frinholp]

Hi all

I have been reading the following thread:

http://fmforums.com/forum/showtopic.php?tid/141887/pid/270581

Comment kindly offered some code that uses masking to valiadte a UK Postcode:

Let ( [

preMask = Substitute ( Leftwords ( Postcode ; 1 ) ;  

[ "C" ; "@" ] ; [ "I" ; "@" ] ; [ "K" ; "@" ] ; [ "M" ; "@" ] ; [ "O" ; "@" ] 

) ;



mask = Substitute ( preMask & "-" & RightWords ( Postcode ; 1) ;  

[ "0" ; "#" ] ; [ "1" ; "#" ] ; [ "2" ; "#" ] ; [ "3" ; "#" ] ; [ "4" ; "#" ] ; [ "5" ; "#" ] ; [ "6" ; "#" ] ; [ "7" ; "#" ] ; [ "8" ; "#" ] ; [ "9" ; "#" ] ; 

[ "A" ; "@" ] ; [ "B" ; "@" ] ; [ "D" ; "@" ] ; [ "E" ; "@" ] ; [ "F" ; "@" ] ; [ "G" ; "@" ] ; [ "H" ; "@" ] ;  [ "J" ; "@" ] ; [ "L" ; "@" ] ; [ "N" ; "@" ] ; [ "P" ; "@" ] ; [ "Q" ; "@" ] ; [ "R" ; "@" ] ; [ "S" ; "@" ] ; [ "T" ; "@" ] ; [ "U" ; "@" ] ; [ "V" ; "@" ] ; [ "W" ; "@" ] ; [ "X" ; "@" ] ; [ "Y" ; "@" ] ; [ "Z" ; "@" ] 

) 

] ;

not isEmpty ( FilterValues ( mask ; "@#-#@@¶@@#-#@@¶@##-#@@¶@@##-#@@" ) )

) 

I have noticed that postcodes such as W1A 1AA and EC1A 1AA are trapped as invalid. These however are valid UK postcodes.

I would like to ammend this code to cater for the aforementioned postcodes.

However, my understanding of the masking part of this code is not brilliant.

Could somebody please explain what the purpose of the carriage return symbols and hyphens are so as that I can ammend this code.

Thanks in advance

Lee

[comment]

When I wrote that calculation three years ago, the sources I used cited only 4 valid patterns for the outward part of the code (one of them still does). I see that the Wikipedia article has been edited to add 2 more patterns for use in "high density areas where codes ran out".

Here is the revised formula:

Let ( [

preMask = Substitute ( Leftwords ( Postcode ; 1 ) ;  

[ "C" ; "@" ] ; [ "I" ; "@" ] ; [ "K" ; "@" ] ; [ "M" ; "@" ] ; [ "O" ; "@" ] 

) ;



mask = Substitute ( preMask & "-" & RightWords ( Postcode ; 1) ;  

[ "0" ; "#" ] ; [ "1" ; "#" ] ; [ "2" ; "#" ] ; [ "3" ; "#" ] ; [ "4" ; "#" ] ; [ "5" ; "#" ] ; [ "6" ; "#" ] ; [ "7" ; "#" ] ; [ "8" ; "#" ] ; [ "9" ; "#" ] ; 

[ "A" ; "@" ] ; [ "B" ; "@" ] ; [ "D" ; "@" ] ; [ "E" ; "@" ] ; [ "F" ; "@" ] ; [ "G" ; "@" ] ; [ "H" ; "@" ] ;  [ "J" ; "@" ] ; [ "L" ; "@" ] ; [ "N" ; "@" ] ; [ "P" ; "@" ] ; [ "Q" ; "@" ] ; [ "R" ; "@" ] ; [ "S" ; "@" ] ; [ "T" ; "@" ] ; [ "U" ; "@" ] ; [ "V" ; "@" ] ; [ "W" ; "@" ] ; [ "X" ; "@" ] ; [ "Y" ; "@" ] ; [ "Z" ; "@" ] 

) 

] ;

not isEmpty ( FilterValues ( mask ; "@#-#@@¶@##-#@@¶@@#-#@@¶@@##-#@@¶@#@-#@@¶@@#@-#@@" ) )

) 

---

I'd be grateful if the moderators appended this to the original thread.

[frinholp]

Thanks Comment

Sorry, I should of added to the original thread in hindsight

[Lee Smith]

Done.

[Thomas Seidler]

Mask is great logical thinking! Comment is impressive!  And gracious in giving his thinking to UK FMers! I've uploaded a custom function to do a few more of the Wikipedia validation information trix too... http://www.briandunning.com/cf/1813