Validating global field for numbers only

[Jim McKee]

I'm looking for the most efficient calc to validate that a user's entry into a global number field contains only numbers -- i.e., no punctuation marks or other non-numeric characters including spaces. The user should be able to enter a decimal mark (e.g., 22.5).

The validation will be performed within a script prior to inserting the value into a regular number field.

TIA for your help.

[CyborgSam]

My method:

Define a global text field named g.Zeros & fill it with enough "0"s to account for an below average idiot entering leading zeros.

Define the data entry as text in a global text field: g.TheNum.Entry

Define a calculation, gc.TheNum.N2T.T2N.Length returning the length of g.TheNum.Entry when converted to a number and then back to text:

Length(NumToText(TextToNum(g.TheNum.Entry)))

Define a calculation to validate the entry, gc.TheNum.NotValid, 1 is not valid, 0 is OK:

If( gc.TheNum.N2T.T2N.Length = Length(g.TheNum.Entry),

NumToText(TextToNum(g.TheNum.Entry)) <> g.TheNum.Entry,

If( (NumToText(TextToNum(g.TheNum.Entry)) =

Right(g.TheNum.Entry, Length(g.TheNum.Entry) - gc.TheNum.N2T.T2N.Length + 1)) and

Position( Left( g.TheNum.Entry, Length(g.TheNum.Entry) - gc.TheNum.N2T.T2N.Length, g.Zeros, 1, 1) <> 0,

0,

1

)

)

This calc checks if the lengths of g.TheNum.Entry matches its Text to Num:Num to Text conversion, if so, it's a number if the two strings match. If not, the Text to Num:Num to Text conversion will be shorter than g.TheNum.Entry, so check the right of g.TheNum.Entry for a matching number in the Text to Num:Num to Text conversion and then check the left of g.TheNum.Entry to ensure it is all zeros. If not, it ain't a good number...

If you want to simplify, you could just flag any leading 0 as an error. But there can be leading zeros in dates, Social Security Numbers, etc.

Finally: gc.TheNum is a calc with TextToNum(g.TheNum.Entry).

Does this make sense?

[CyborgSam]

Doh! I forgot the decimal point. Here's how I did it with decimals (from one of my internal docs, I hope it's readable...):

Integer & Reals with 2 digit precision (no exponent) Validation:

To make sure there are no letters or punctuation and only one period in a number. The number of leading and trailing zeros is defined by the global text "gZeros", which should be set to over 20 "0"s to catch book corners leaning on the keypad 0...

Reals (any # with a . in it) are truncated at 2 decimals without rounding. Exponents are not accepted. Not 100% accurate for all reals, but OK for checking integers and US currency.

g.Zeros

g.SomeNum.Entry

gc.SomeNum.Pos.Decimal

gc.SomeNum.Pos.N2T.T2N

gc.SomeNum.NotValid

gc.SomeNum.Pos.Decimal

------------------------------------------------------

Position(NumToText(TextToNum(g.SomeNum.Entry)), ".", 1, 1)

------------------------------------------------------

gc.SomeNum.Pos.N2T.T2N

------------------------------------------------------

Position(g.SomeNum.Entry, NumToText(TextToNum(g.SomeNum.Entry)), 1, 1)

------------------------------------------------------

gc.SomeNum.AutoFormat

------------------------------------------------------

If( IsEmpty(g.SomeNum.Entry),

"",

If( gc.SomeNum.NotValid,

"Invalid#",

If( gc.SomeNum.Pos.Decimal,

Left(NumToText(TextToNum(g.SomeNum.Entry)) & gZeros, gc.SomeNum.Pos.Decimal + 2),

NumToText(TextToNum(g.SomeNum.Entry)) & ".00"

)

)

)

------------------------------------------------------

gc.SomeNum.NotValid

------------------------------------------------------

If( (not IsEmpty(g.SomeNum.Entry)) and

(

(not gc.SomeNum.Pos.N2T.T2N) or

Position(g.SomeNum.Entry, ".", 1, 2) or

(Left(g.SomeNum.Entry, gc.SomeNum.Pos.N2T.T2N - 1) <>

Left(gZeros, gc.SomeNum.Pos.N2T.T2N - 1)) or

If( Position(g.SomeNum.Entry, ".0", 1, 1),

(Right(g.SomeNum.Entry, Length(g.SomeNum.Entry) - gc.SomeNum.Pos.N2T.T2N -

Length(NumToText(TextToNum(g.SomeNum.Entry)))) <>

Right(gZeros, Length(g.SomeNum.Entry) - gc.SomeNum.Pos.N2T.T2N - Length(NumToText(TextToNum(g.SomeNum.Entry))))),

(Right(g.SomeNum.Entry, 1) <> ".") and

(Right(g.SomeNum.Entry, Length(g.SomeNum.Entry) - gc.SomeNum.Pos.N2T.T2N -

Length(NumToText(TextToNum(g.SomeNum.Entry))) + 1) <>

Right(gZeros, Length(g.SomeNum.Entry) - gc.SomeNum.Pos.N2T.T2N -

Length(NumToText(TextToNum(g.SomeNum.Entry))) + 1))

)

),

1,

0

)

------------------------------------------------------

[BobWeaver]

Ugo posted a validation calculation a few weeks ago that, as I recall, went something like this:

Length(TheField) = Length(TextToNum(

Substitute(Substitute(Substitute(Substitute(Substitute(Upper(TheField), "T", "X"), "F", "X"), "Y", "X"), "N", "X"),"0","1"

)))

<<Edited: I missed a couple of Substitutes the first time around.>>

[Ugo DI LUCA]

Hi Bob,

It's late on this Sunday, so I'm unsure why you use thes T, F and X here.

But yes the TextToNum function seems to be what is needed here.

If an entry of type 0259 is considered a valid entry, then you'd just need to substitute this leading 0 with a 1 as for the TextToNum to return 1259.

This will therefore allow us to compare the 0259 lenght (4) to the converted one (4).

Converting all 0 with a 1 won't change the validation process on Length, so a calc along the line of :

not IsEmpty(global) and (Length(TextToNum(Substitute(global,"0","1")))=Length(global))

for leading 0's

or

Not IsEmpty(global) and (Lenght(TextToNum(global))=Length(global))

should do it.

but I'm still curious about Bob's "TXN" calc...

[Jim McKee]

Sam and Bob .. thanks a lot. I appreciate your posts. Still haven't figured out yours, Sam

Ugo DI LUCA said:

Not IsEmpty(global) and (Lenght(TextToNum(global))=Length(global))

should do it.

Hi Ugo ...

I don't care about leading zeros because the field is a global number and they'll be ignored. What I do care about is letters, spaces, and other non-numeric characters embedded within the number string.

Attached is a file using your formula above. If only numbers, or numbers AND letters are entered into the global number field, the test returns true (passes validation) because TextToNum(gnumber) only returns the numbers from the string. So:

value entered into global -> 12a34b56c

is returned as

123456 by the TextToNum(gnumber) part of the formula, AND

6 is returned by the Length(gnumber) part of the formula because the Length() function is only counting the numeric characters, so they are always equal.

This doesn't tell me if non-numeric characters were entered into the global field

Would the PatternCount function work better in this situation?

Thanks.

Test for numbers only.zip

[Ugo DI LUCA]

Hi Jim,

I skipped the Num format in your first post.

Well, then I don't know anything else than a copy/paste to another global (text) and check its content with the given calculation...

[BobWeaver]

I put in the substitution for T and F (true and false) because they will evaluate to 1 and 0 and would be considered valid numbers. With the substitution, they won't. Likewise for Y and N. I thought you did that on one of your posts a few days ago, or was it just the substitution of 1 for 0? Anyway, this seems to work.

[djgogi]

I've modified the file you sent to achieve the goal:

It's done thru additional look-up field (of type text)

As alternative approach I've also, set the validation on gnumber to strict numeric type (no script required)

Dj

Test for numbers only1.fp5.zip

[Ugo DI LUCA]

Neat Dj,

I had tried this route, but wouldn't even had thought validation could have been forced this way. Neat.

No Bob, if this was the postcode stuff, yes I only substituted the "0" for "1", but still this T and F doesn't seem to work on num fields...

[BobWeaver]

Hmm, it seems that I had the global defined as text, not number. So, maybe that's why I had to use all the substitutions.

[CyborgSam]

Ugo's test will fail on one format of a number: digits followed by a decimal point with no trailing digits: e.g.: "47." All that is needed is a second test looking for a rightmost "."

I started with Ugo's test, added NumToTexts & my fix for trailing decimals: I can't make this fail for anything I enter! My original test works fine, but it is way too complex & I can't remember why I did it that way. I blame old age and Red Dye #40...

gc.SomeNum.NotValid (0=OK, 1=Bad#)

If( (not IsEmpty(g.SomeNum.Entry)) and

If( Right(g.SomeNum.Entry, 1) = ".",

(Length(NumToText(TextToNum(Substitute(g.SomeNum.Entry,"0","1")))) <> Length(g.SomeNum.Entry) - 1),

(Length(NumToText(TextToNum(Substitute(g.SomeNum.Entry,"0","1")))) <> Length(g.SomeNum.Entry))

),

1,

0

)

[CyborgSam]

Forgot to add:

The above is the easy to read calculation. The compact version is:

If( (not IsEmpty(g.SomeNum.Entry)) and

(Length(NumToText(TextToNum(Substitute(g.SomeNum.Entry,"0","1")))) <> Length(g.SomeNum.Entry) - (Right(g.SomeNum.Entry, 1) = ".")),

1,

0

)

I test for bad=1, if you want good = 1 and don't accept an empty field:

(not IsEmpty(g.SomeNum.Entry)) and (Length(NumToText(TextToNum(Substitute(g.SomeNum.Entry,"0","1")))) = Length(g.SomeNum.Entry) - (Right(g.SomeNum.Entry, 1) = ".")

[Ugo DI LUCA]

Hmm..

Why not simply add a space to the global and check if it has ". ", then it's false by substituting ". " by "1"... or by Patterncount (global & " ",". ")

No ?

So if "47." should be a valid entry

Not IsEmpty(global) and (Lenght(TextToNum(Substitute(global& " ",". ","1")))=Length(global))

[CyborgSam]

Oops: my last method allowed two decimals like 47.0., this fixes it:

IsEmpty(g.SomeNum.Entry) or Position(g.SomeNum.Entry, ".", 1, 2) or

(Length(NumToText(TextToNum(Substitute(g.SomeNum.Entry,"0","1")))) <> Length(g.SomeNum.Entry) - (Right(g.SomeNum.Entry, 1) = "."))

> Why not simply add a space to the global and check if it has ". ", then it's false by substituting ". " by

> "A"... or by Patterncount (global & " ",". ")

That should work. But doesn't a PatternCount takes more CPU time/resources than an If test?

[Ugo DI LUCA]

Ok, mine doesn't solve the double "." but, as Jim said it should be a valid number entry, why bother with an ending "." which is IMO not valid...

This would apparently solve it though, by substituting ". " by 0 instead of 1

Not IsEmpty(global) and (Lenght(TextToNum(Substitute(global& " ",". ","0")))=Length(global))

And in case ".000" shoudn't be considered valid

Not IsEmpty(global) and (Lenght(TextToNum(Substitute(global& " ",". ","0")))=Length(global)) and TextToNum(global)

But then :

Lenght(TextToNum(Substitute(global& " ",". ","0"))=Length(global) and TextToNum(global)

would work too...

[MoonShadow]

If you want to stop Users from entering text in that global number, why not turn it into a regular number, set its field Validation to

[CyborgSam]

> why bother with an ending "." which is IMO not valid...

From what I read, Jim wanted to ensure the # was valid in ALL cases. So I'm being extra picky.

IMHO, 47. is a valid format. 47. it's an abbreviation for 47.0, used when there are many 47.x numbers (e.g. table of data). Personally, I wouldn't use it, but I have seen it used by scientists/engineers.

Putting Strict Number Validation on a number field flags 47. as an error. I'd agree that this is OK for 99.99% of the people. I work with academics...

FileMaker parses numbers a bit wierd (IMHO). Enter abc.123.456@def into a number field, NumToText of that field is ".123456". FileMaker only took the digits and the first period, ignoring other characters. A calc like SomeNum + 1 verifies that FileMaker really parsed to ".123456".

So, I still think this is the most thorough typo catching validation:

IsEmpty(g.SomeNum.Entry) or Position(g.SomeNum.Entry, ".", 1, 2) or

(Length(NumToText(TextToNum(Substitute(g.SomeNum.Entry,"0","1")))) <> Length(g.SomeNum.Entry) - (Right(g.SomeNum.Entry, 1) = "."))

Ugo: do you know if the PatternCount takes more time to execute than a few If tests? I seem to remember reading that years ago in a "Speed Up FileMaker" article.

[Ugo DI LUCA]

Hi Sam,

I can't see any difference in results from your calc to mine

Length(TextToNum(Substitute(global& " ",". ","0")))=Length(global) and TextToNum(global) seems to be working fine, except it has exactly the opposite boolean result.

047would not be valid in mine with this calc, but could by simply adding the 0 to 1 substitution trick.

Now, I'd guess we'd need some sampler data to compare and evaluate the speed of PatternCount vs If, but because it involves a "Count", I'd guess it is more involved than a If. Now, a not Patterncount could be eaven quicker I assume.

If you find this test, I'd like to have a look at it too...

[CyborgSam]

Ugo->

Correct, we both have a working calculation.

I did a quick test of yours versus mine by putting both in a script using 10 SetFields in a loop (to compensate for loop overhead). Ran it for over 8 minutes each, mine was 8:22 and yours 8:40. Essentially equal, there is some error slop because I didn't shut down all other apps/processes.

It would be neat to compare a PatternCount to another method, but I don't think this calculation is a good example. Something that really takes advantage of PatternCount is needed.

I can't find the speed tips for FileMaker I once had. I'll keep looking...

Sam

[Ugo DI LUCA]

Hi Sam,

I just like these kinds of comparisons. Apps running in the background surely could explain this gap, because If SHOULD be more time consuming than a kind of boolean construction.

For personal purpose, I'd be interrested to know if there would be gaps if this same test, with all Apps closed was running on the "Not valid" side, rather than the valid one. I mean, would it be quicker to check FALSE or TRUE ?

I'd build an example for the If/Patterncount comparisons this evening. If your mail isn't in the Profile, send me a message so that I can mail you the file.

[Ugo DI LUCA]

Bob,

It's hard to find a way to an accurate comparison, but here's in brief what came up from some tests on a subset of 10,000 records with an auto-entered Text Field with calculation of type

Small-3GQAN-H9E0Y1643AN3463OO

-5WWLH-VMT5V1643DO3463PY

Medium-0MBIO-RCY8U1643EU34633R

XLarge-1OCWV-BTA5I1643MT3463L4

....

1) Testing if the First Word is either XLarge

Patterncount(MiddleWords(TextField,1,1),"XLarge") wins compared to a simple MiddleWords(TextField,1,1)= "XLarge"

2) Testing if the First Number in the string is 3, with an overcomplicated formula

(Patterncount(MiddleWords(TextField,1,1),"XLarge") or Patterncount(MiddleWords(TextField,1,1),"Small") or Patterncount(MiddleWords(TextField,1,1),"Large") or

Patterncount(MiddleWords(TextField,1,1),"Medium")) and

Middle(MiddleWords(TextField,2,1),2,1)="3"

gives the same result (same elpased time) than

If(MiddleWords(TextField,1,1)= "Xlarge" or MiddleWords(TextField,1,1)= "Small" or MiddleWords(TextField,1,1)= "Large" or MiddleWords(TextField,1,1)= "Medium", If(Middle(MiddleWords(TextField,2,1),2,1)="3",1,0),0)

while

If(not TextToNum(MiddleWords(TextField,1,1)),

If(Middle(MiddleWords(TextField,2,1),2,1)="3",1,0),0)

wins the "contest"

So my guess, which would need to be confirmed by some more seriously tested solution is that Patterncount ( ) 's performance decrease according to the number of pattern to be scanned.

...

[CyborgSam]

Ugo->

Did the subset of records reside totally in memory? That's why I did the exact same SetField script step on the same record over & over. Any paging, disk reading, etc., really complicates matters.

I'll try again with a clean login.

Sam