Author 
Message 
Crai #1 / 8

Field validation
I'm trying to make the data in my CustID field conform to the following rules: 1. Length of fields must be 6 characters. 2 letters and 4 numerals. 2. 1st 2 characters must be GR or LM 3. Letters must be in uppercase I would think the following formula would work. The LM or GR work but it lets me outta the field before validating the length and/or upper case. Can anyone help me understand this?? Left(CustID , 2 ) ="LM" or Left(CustID,2) ="GR" and Length(CustID)=6 and Upper(CustID) Thanks...

Mon, 05 Mar 2001 03:00:00 GMT 


Mr3Put #2 / 8

Field validation
Looks like you're missing some parentheses to logically group your validations... (Left(CustID,2)="LM" or Left(CustID,2)="GR") and (Length(CustID)=6) Now... that'll check your field length, but the "Upper" function does not return a binary (yes/no) result. Therefore, you cannot logically check the CASE of the text entered. Can't the text format of the field in question simply be set to UPPERCASE? Or, create another, calculated, version of CustID = Upper(CustIDEntry). One point you don't cover is checking that 4 numerals have been entered (chars 36 of CustID)... depending on your data requirements, I would mandate that the NUMERIC portion of CustID MUST BE > 1000. You could then add "(TextToNum(CustID) >=1000 and TextToNum(CustID) <=9999)" to validate the correct numeric values. Hope this is of some value... good luck. Quote:
> I'm trying to make the data in my CustID field conform to the following > rules: > 1. Length of fields must be 6 characters. 2 letters and 4 numerals. > 2. 1st 2 characters must be GR or LM > 3. Letters must be in uppercase > I would think the following formula would work. The LM or GR work but it > lets me outta the field before validating the length and/or upper case. Can > anyone help me understand this?? > Left(CustID , 2 ) ="LM" or Left(CustID,2) ="GR" and Length(CustID)=6 and > Upper(CustID) > Thanks...
 ========================================================== Michael Little
NOTE: Remove 'PAR3' from my address when replying ==========================================================

Mon, 05 Mar 2001 03:00:00 GMT 


David Burkhar #3 / 8

Field validation
Change your formula to the following: (Left(CustID , 2 ) ="LM" or Left(CustID,2) ="GR") and Length(CustID)=6 The parenthesis isolates the 'OR' portion of the test from the 'AND' portion. I find using parenthesis always helps provide clarity in defining how I want a logical equation to be evaluated. The Upper(text) changes the text in the text field to all uppercase. There is not a test that I am aware of that will check for upper/lower case. In other words, 'm' is the same as 'M'. There are 2 ways to address this issue: 1. create a calculation field that will do this conversion and use this field in your layouts. 2. In the layout mode, select the field, goto format and select text... Select the checkbox that says UPPERCASE. Regards, David Burkhard Quote:
> I'm trying to make the data in my CustID field conform to the following > rules: > 1. Length of fields must be 6 characters. 2 letters and 4 numerals. > 2. 1st 2 characters must be GR or LM > 3. Letters must be in uppercase > I would think the following formula would work. The LM or GR work but it > lets me outta the field before validating the length and/or upper case. Can > anyone help me understand this?? > Left(CustID , 2 ) ="LM" or Left(CustID,2) ="GR" and Length(CustID)=6 and > Upper(CustID) > Thanks...

Mon, 05 Mar 2001 03:00:00 GMT 


Crai #4 / 8

Field validation
Mike, Quote: >Can't the text format of the field in question simply be set to UPPERCASE?
Done. Works fine. Thanks.. Quote: >One point you don't cover is checking that 4 numerals have been entered (chars >36 of CustID)... depending on your data requirements, I would mandate that the >NUMERIC portion of CustID MUST BE > 1000. You could then add >"(TextToNum(CustID) >=1000 and TextToNum(CustID) <=9999)" to validate the >correct numeric values.
Here's what I came up with with no luck. I am very rusty at this boolean stuff. I seem to be having trouble understanding nesting functions. I get an error of too many seperators when using the following: (Left(CustID , 2 ) ="LM" or Left(CustID,2) ="GR") and (Right(custid,TextToNum(custid),4) >="0000") or (Right(custid, TextToNum(custid),4) <="9999") and (Length(CustID)=6) Second line between (custid),4) has error of too many seperators. I thought I had followed the manual page C2 but am missing something. Thanks for your input once again.

Mon, 05 Mar 2001 03:00:00 GMT 


Crai #5 / 8

Field validation
Answer to my own question??? This formula works. It seems that I don't need the TextToNum(custid)+Right(custid,4)<="9999" as the logic as follows won't allow anything else. (Left(CustID , 2 ) ="LM" or Left(CustID,2) ="GR") and (TextToNum(CustID+Right(CustID,4) >="0000")) and (Length(CustID)=6) 1. Length of fields must be 6 characters. 2 letters and 4 numerals. 2. 1st 2 characters must be GR or LM 3. Letters must be in uppercase

Mon, 05 Mar 2001 03:00:00 GMT 


Eberha #6 / 8

Field validation
Quote:
> Answer to my own question??? This formula works. It seems that I don't > need the TextToNum(custid)+Right(custid,4)<="9999" as the logic as follows > won't allow anything else. > (Left(CustID , 2 ) ="LM" or Left(CustID,2) ="GR") and > (TextToNum(CustID+Right(CustID,4) >="0000")) and (Length(CustID)=6)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ Quote: > 1. Length of fields must be 6 characters. 2 letters and 4 numerals. > 2. 1st 2 characters must be GR or LM > 3. Letters must be in uppercase
I think this will not work properly as you want: 1st: TextToNum requires a textstring inside the (..). "+" is an operator for numbers and not for text strings (that would be "&"). But as I think you don't need the "Right(CustID,4)" stuff in there. 2nd: Did you try to insert "LM5h16" or "LMR314" or "GR345F" to the CustID? Did you get an error message? It's better to require a number higher than 1000 in the CustID field. Only in this case you can be sure that you've got 4 figures in the CustID. 

Tue, 06 Mar 2001 03:00:00 GMT 


David Burkhar #7 / 8

Field validation
Here is the equation that is needed: (Left(CustID , 2 ) ="LM" or Left(CustID,2) ="GR") and Middle(CustID,3,1) >=0 and Middle(CustID,3,1)<=9 and Middle(CustID,4,1) >=0 and Middle(CustID,4,1)<=9 and Middle(CustID,5,1) >=0 and Middle(CustID,5,1)<=9 and Middle(CustID,6,1) Quote: >=0 and Middle(CustID,6,1)<=9
The issue is as follows: FMP treats numbers within a word several ways. You could have the situation where LMAA12 would pass your original equation because 12 is between 0 and 9999. FMP will automatically convert text to numbers when evaluating text strings and comparing the result to a number. This would be a simple equation of CustID >= 0 or CustID <= 9999. However, to computers, 0 is the same as 0000. To evaluate a text string of numbers, you would use the equation Middle(CustID,3,4)>="0000". This result would be accurate for the first digit location, but not for the rest. "A001" would be false while "0A01" would be true. This is why each digit needs to be individually checked in this case. Dave Quote:
> > Answer to my own question??? This formula works. It seems that I don't > > need the TextToNum(custid)+Right(custid,4)<="9999" as the logic as follows > > won't allow anything else. > > (Left(CustID , 2 ) ="LM" or Left(CustID,2) ="GR") and > > (TextToNum(CustID+Right(CustID,4) >="0000")) and (Length(CustID)=6) > ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ > > 1. Length of fields must be 6 characters. 2 letters and 4 numerals. > > 2. 1st 2 characters must be GR or LM > > 3. Letters must be in uppercase > I think this will not work properly as you want: > 1st: TextToNum requires a textstring inside the (..). "+" is an > operator for numbers and not for text strings (that would be "&"). But > as I think you don't need the "Right(CustID,4)" stuff in there. > 2nd: Did you try to insert "LM5h16" or "LMR314" or "GR345F" to the > CustID? Did you get an error message? It's better to require a number > higher than 1000 in the CustID field. Only in this case you can be sure > that you've got 4 figures in the CustID. > 

Tue, 06 Mar 2001 03:00:00 GMT 


Crai #8 / 8

Field validation
I see I have a lot to learn. Field programming is surely different between programs. I started playing with the Middle function but never thought to validate each character. I did indeed have the problem of it passing text where only numbers should have been. Thank you so very much for your help. Quote:
>Here is the equation that is needed: >(Left(CustID , 2 ) ="LM" or Left(CustID,2) ="GR") and Middle(CustID,3,1) >=0 >and Middle(CustID,3,1)<=9 and Middle(CustID,4,1) >=0 and
Middle(CustID,4,1)<=9 Quote: >and Middle(CustID,5,1) >=0 and Middle(CustID,5,1)<=9 and Middle(CustID,6,1) >>=0 and Middle(CustID,6,1)<=9 >The issue is as follows: >FMP treats numbers within a word several ways. You could have the situation >where LMAA12 would pass your original equation because 12 is between 0 and >9999. FMP will automatically convert text to numbers when evaluating text >strings and comparing the result to a number. This would be a simple equation >of CustID >= 0 or CustID <= 9999. However, to computers, 0 is the same as >0000. >To evaluate a text string of numbers, you would use the equation >Middle(CustID,3,4)>="0000". This result would be accurate for the first digit >location, but not for the rest. "A001" would be false while "0A01" would be >true. This is why each digit needs to be individually checked in this case. >Dave

Tue, 06 Mar 2001 03:00:00 GMT 


