Field validation
Author Message 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  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
3-6 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

==========================================================

Mon, 05 Mar 2001 03:00:00 GMT  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
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  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
>3-6 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 C-2 but am missing something.
Thanks for your input once again.

Mon, 05 Mar 2001 03:00:00 GMT  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  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 text-string 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  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 text-string 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  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

 Page 1 of 1 [ 8 post ]

Relevant Pages