Function to identify whether field contents are text or number

[aharown07]

Want to make a little "quicksearch" field in a solution. (Still in Fmp10adv).

The script should check a global field and then proceed based on whether the field contains a number or text.

So I need some "if" logic. Simple enough. But my brain's cramping on how to identify whether the field contains text or a number.

Tried some IsValid combinations like IsValid(Abs(thefield)) but apparently the absolute value of some text is valid.... as is the Int of some text.

So I need a boolean result based on whether the field contains text or a number.

... or maybe a reliable way to generate a particular error whenever it's text or whenever it's a number. Would be easy enough to condition the script on the error.

[comment]

whether the field contains a number or text.

That's hard to say, because if the field type is Text, and it contains only numeric characters - it's still text. What it the purpose here?

[Raybaudi]

The script should check a global field and then proceed based on whether the field contains a number or text

If there aren't fractions of 1 written with a leading zero ( like 0.23 ) or positive numbers written this way: +1.3 or numbers with thousand separators, you can try:

Length ( YourGlobalField ) = Length ( GetAsNumber ( YourGlobalField ) )

This returns 1 ( true ) for a number and 0 ( false ) for a text.

[comment]

Length ( YourGlobalField ) = Length ( GetAsNumber ( YourGlobalField ) )

Why would the length be the same? (trick question...)

[Raybaudi]

Why would the length be the same? (trick question...)

GetAsNumber ( YourGlobalField ) returns:

1) each number of the field ( excluding all leading zeros )

00aaaa0002 returns 2

2) the eventual first dot starting from left and ALL the other numbers ( including zeros ) that follow the dot

aaa000.aaa0.002 returns .0002

3) the eventual minus sign ( only if the leftmost char of the field )

-aaa000.aaa0002 returns -.0002

a-aa000.aaa0002 returns .0002

So, if we'll write a number respecting the above conventions, we'll have:

Length ( aNumber ) = Length ( aNumber )

P.S.: there can be problems when FileMaker starts to modify the number notation ( -10^9 or 10^9 )

[comment]

So, if we'll write a number respecting the above conventions, we'll have:

Length ( aNumber ) = Length ( aNumber )

Wouldn't you also (or first) have:

aNumber = aNumber

?

[Raybaudi]

This is reeeeeally what I was now testing...

I do not know why I always used Length !!

[aharown07]

That's hard to say, because if the field type is Text, and it contains only numeric characters - it's still text. What it the purpose here?

What I want the script to do is perform a search on field A if it's text but search on field B if its numeric. So, basically, trying to use one search field to perform multiple searches based on user behavior.

The app has an Amount field and also an Item Number field and a couple of text fields... so trying to search all of them from one box is probably a dream... it may be better to have one search box for text fields and one for the numeric fields or else buttons to route the script in one direction or the other.

Another option might be to just decide on a sequence and whenever the search comes back empty, try it again on the next field until there are hits... or a "nothing found" message.

... Unfortunately, the amount field could well contain something like .59 so that eliminates one of the other ideas above

[comment]

The app has an Amount field and also an Item Number field and a couple of text fields... so trying to search all of them from one box is probably a dream...

Is it?

http://fmforums.com/forum/topic/65087-search-multiple-fields/page__p__308601#entry308601

[mfrapp]

this works for me,

Exact ( GetAsText ( $value ) ; GetAsText ( GetAsNumber ( $value ) ) )

returns 1 when its a number

[comment]

0.5

[mfrapp]

i see, my bad

i can't believe there is no way of finding out if a value is a valid number without using a script or recursion

so what would be a valid number?

5

5.123

0.5

.5

0.0

1.0e+5

123,456

123,456.789

123 456

123 456.789

(same with minus sign)

am i missing any and is taking into consideration the formatting with "," and " " overkill?