( Length ( field ) = 6 and PatternCount ( field ; " " ) = 0 ) or ( Length ( field ) = 7 and PatternCount ( field ; " " ) = 1 )
Might this all make a case for enforcing one way or the other, but not both, of holding the data? I'd be inclined to use an autoenter calc to replace whatever text is entered with all spaces removed—or in the format xxxSPACExxx if that is your preference (either would cover Mike's last point).
It's an interesting point (that neither suggested solution addresses) that the space has to be in a certain position. You'd have to compensate for that as well.
Let's go back to the OP. We want either 6 alphanumerics, or 3 alphanumerics, followed by a space, followed by 3 more alphanumerics. This should do it:
Let ( [
fieldLength = Length ( field ) ;
spaceCount = PatternCount ( field ; " " ) ;
spacePosition = Position ( field ; " " ; 1 ; 1 )
( fieldLength = 6 and spaceCount = 0 ) or ( fieldLength = 7 and spaceCount = 1 and spacePosition = 4 )
(With a hat tip to the usefulness of the Let function.)
... or even better:
Set Field [TO::text ; Upper ( TO::text )]
If [Length ( Filter ( TO::text ; "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789" ) = 6]
This allowes to enter the postcode in lower case letters and ignores all characters that don't belong in there.
(I don't know about the 'o' and 'i' which can be mixed up with zero and one.
Huslik Verlag GmbH • Bgm.-Widmeier-Str. 42 • 86179 Augsburg, DE
CEO Winfried Huslik - HRB Augsburg 12386 - VAT-Id. DE127485099
Phone +49 821 565606, Fax +49 821 565001, Email email@example.com
Verify your FileMaker Pro files with FMDiff - http://fmdiff.com
FileMaker Developer Conference July 28-31 2014 • San Antonio TX
After reading <http://en.wikipedia.org/wiki/Postal_codes_in_Canada> I suggest this solution to the problem and better error checking:
Let ( [
p = Upper ( to::postcode ) ;
a = "ABCEGHJKLMNPRSTVWXYZ" ; // do not include the letters D, F, I, O, Q or U.
n = "1234567890" ;
p = Filter ( p ; a & n ) ; // remove blanks and illegal characters
c = Middle ( p ; 1 ; 1 ) & Middle ( p ; 3 ; 1 ) & Middle ( p ; 5 ; 1 ) ;
d = Middle ( p ; 2 ; 1 ) & Middle ( p ; 4 ; 1 ) & Middle ( p ; 6 ; 1 ) ;
k = Length ( Filter ( c ; a ) & Filter ( d ; n ) ) = 6
and not Position ( "WZ" ; Left ( c ; 1 ) ; 1 ; 1 ) // W and Z may not be at pos 1
If ( k ; Left ( p ; 3) & " " & Right ( p ; 3 ) ; "" )
A wrong entry returns a blank result.