Field Validation

[hcmbrbj]

I've got a field that has to have 2 numeric characters and 1 alpha character i.e 06B. I can't figure out how to set the Validation. Do I need a script for this field? If so, any pointers for this newbie would be appreciated.

[Matthew F]

How about using this as a calculated validation (assuming your field is named 'test')...

GetAsNumber ( Left ( test ; 2 ) ) < 100 and GetAsNumber ( Left ( test ; 2 ) ) ≥ 0 and (PatternCount ( "abcdefghijklmnopqrstuvwxyz" ;Right ( test ; 1 ) ) = 1) and (Length ( test ) = 3 )

[comment]

Your test lets "1AB" pass.

I believe this should do it:

Length ( text ) = 3

and

Exact ( Left ( text ; 2 ) ; Filter ( text ; "0123456789" ) )

and

Right ( text ; 1 ) = Filter ( Upper ( text ) ; "ABCDEFGHIJKLMNOPQRSTUVWXYZ" ) 

Edited December 19, 2009 by Guest

[hcmbrbj]

Thanks. Both suggestions work except when I test it with a bad entry if I hit tab there is no warning dialog, but if I hit enter I get a warning dialog. The logical entry procedure would be to hit tab. Also when I do get a warning dialog and hit Revert Record there is a second warning, "Revert all changes to this record since it was last entered?" If that's referring to any and all fields, I need to get rid of it. In the instance I'm working on other entries could have been made that shouldn't be reverted.

[comment]

Yes, this is often the issue with validating by calculation: it kicks in only when the record is committed. In version 10 you can add a OnObjectModify script trigger to the field on the layout - this can run the same test with a more gentle result for the user. I'd still leave the data-level validation in place, if the requirement is critical for data integrity.