Field Validation - Answered...Thanks

[im2bethe1]

Hey Everyone,

I feel real forgetful right now (Brain Fart) cause I know I've seen this somewhere in here. So if this is a repeat question, don't make me feel too bad, but here is my problem. Let's say you have two things that you are selling, the first lets call a "Document" and the other is a "CD". Now each document and CD have their own inventory numbers (all different). But every document inventory number starts with "Two letters" and the rest are numbers, where as the "CD's" are just numbers. Now my database has two fields, one which you chose if it is a Document or CD and the other is two input the inventory number. Also, the first field is a dropdown list to chose from the catagories of "CD" or "Document", the 2nd field is an input field for the user. I figure it is the 2nd field that needs to validation to match agains the first choice.

Now my problem: I need a validation script that if "Document" is selected and the user doesn't enter the two Alpha characters first, then have an error message appear or if "CD" is selected and they enter an Alpha prefix another error message appears. I can figure out the error message part, I just can't get the Alpha to non-Alpha validation. Can anybody help me out with this, I know there is an easy answer, I just can't convert my logic to filemaker's logic (frown).

[Damocles]

This solution is a little "clunky" but it worked. In order to do this, I used four fields.....

Document_or_CD: the drop down list with only two options.

Inv_Prefix: the first two letters that only show up if "document" is picked. This field is validated using this calculation:

[color:"blue"] Case( IsEmpty(Inv_Prefix) and Document_Or_CD="CD", 1, not (IsEmpty(Inv_Prefix)) and DocOrCD="Document", 1, 0)

Inv_Number: the actual numerals that are part of the inventory number (this is validated as a strict data type "number" and lets the user know that they are not allowed to put letters in the field.)

Full_Inv_Number this is a calculated text field that serves to validate the number if the user doesn't click on the "prefix" field:

[color:"blue"] If( Document_Or_CD="Document" and IsEmpty(Inv_Prefix), "Invalid Prefix - Please fix" , Inv_Prefix & NumToText(Inv_Number))

Even though this works, I would wait until you hear from someone more "polished" to get a more elegant solution.

Paul

[im2bethe1]

Would these work with the custom dialog box? I am only asking cause I have a small fixed area for this input, and adding the extra fields would be fine if I place the calc's in the custom dialog...

Also I gave it a try and it comes up as a circular definition "Inv_Prefix" and won't let me use it. There has to be a better way....

[im2bethe1]

I would like to thank those who responded. I did find the answer through an associate which worked out perfectly. I am sharing his answer and explaination incase anyone else ever has this problem.

The Key is the Text to Number command.

TextToNum(A1) = (1)

Therefore, if we WANT there to NOT be any text, The type is 'CD' then

TextToNum ( Left( Invoice Number, 2) ) = Left (Invoice Number, 2)

On the otherhand, if we want the text, just the opposite is true, mainly,

TextToNum ( Left(Invoice Number, 2) ) <> Left(Invoice Number, 2)

Sooo, If the item is a 'CD', then check that it is all numbers, else, check for text.

remember a positive result is = "1", where a negative test is something else "0"