Field Validation

[Tom England]

We have client specifiect codes made up of three letters then three numbers eg the first Smith would be SMI001. Is it possible to validate the field so that userrs can only put entries in in this format. The only way I can think of at present would be to have two seperate fields, one for the 'SMI' bit, then one for the '001' bit, then combine the two field in another field.

Is this a good way to operate??

[LiveOak]

I don't know how to tell if they put in the correct value, but you can check the three letter, three number format:

Valid (calculation, number) =

Length (Code) = 6

and

PatternCount ("ABCDEFGHIJKLMNOPQRSTUVWXYZ", Left (Code, 1)) > 0

and

PatternCount ("ABCDEFGHIJKLMNOPQRSTUVWXYZ", Middle (Code, 2, 1)) > 0

and

PatternCount ("ABCDEFGHIJKLMNOPQRSTUVWXYZ", Middle (Code, 3, 1)) > 0

and

PatternCount ("0123456789", Middle (Code, 4, 1)) > 0

and

PatternCount ("0123456789", Middle (Code, 5, 1)) > 0

and

PatternCount ("0123456789", Middle (Code, 6, 1)) > 0

This will return a "1" if the calculation is valid. Make sure to uncheck "Do not evaluate if all fields are empty".

-bd

[Thom]

It depends where you are in the system. The very first time the code is entered, it could be automatically created based on the last name. After that, the code field should be used as a value list for entering into other fields. The fields using the code would have a pop-up list and be validated by being a member of the same value list.

To have the codes created automatically based on last name, create these fields:

lastNameCode(calc,text) = Upper(Left(lastName,3))

Create a relationship, LastNameCode = lastNameCode=::lastNameCode before defining the rest of the fields.

serial(number) - to be redefined later

serialNext(calc,number) = Max(LastNameCode::serial) + 1

personID(calc,text) = nameLastCode & Right("00" & serial, 3)

After defining these fields, choose the options for serial, and make it look-up serialNext based on the LastNameCode relationship; if no exact match, then use 1.

Make a value list using the personID field. Use this value list in any other files where you will be entering the personID codes.