I wanted to filter by an ID and the value of a 2nd field. The latter was to be "<100". This presents a problem because some records have a non-numeric value.

How would I filter by a range? Say between >0 and <100?

I wanted to filter by an ID and the value of a 2nd field. The latter was to be "<100". This presents a problem because some records have a non-numeric value.

How would I filter by a range? Say between >0 and <100?

Let ( [

n = secondField ;

m = If ( Left ( n ; 1 ) = "-" ; 1 ) ;

o = filter ( n ; "0123456789." ) ;

p = If ( m = 1 ; "-" & o ) ;

q = GetAsNumber ( p ) ;

r = If ( q > 0 and q < 100 ; 1 )

] ; r )

I'm still surprised your GetAsNumber didn't handle negatives properly. Maybe you had a space between the negative and the number.

The "IsValid" test will work IF "secondfield" is set up as a number field and IF in that number field there is ONLY text (not numbers).

Set up a number field, enter "Is this valid 1?", and you'll see that the data WILL be called valid. If you use GetAsNumber, it will evaluate to 1.

Also, take the following example:

secondfield contains "This will cause problems 2".

n would evaluate as 2 since GetAsNumber ("This will cause problems 2") will evaluate as 2.

IsValid ("This will cause problems 2") would evaluate as true, and n would be between 0 and 100.

So..."This will cause problems 2" would be included in your find.

Try:

GetAsNumber ( secondfield ) = GetAsText ( secondfield ) and secondfield >=0 and secondfield<100

Apologies, I'd made a syntax error. Yes, it captures 0-100.

BTW the fields are all text, but contain either numbers only or text, ie. not a mixture, so either of your solutions would probably work but this latter one safeguards in case there becomes a mix.

Showing my ignorance, but in plain English, how does the formula sequence below work exactly:

GetAsNumber ( secondfield ) = GetAsText ( secondfield ) and GetAsNumber ( secondfield ) >=0 and GetAsNumber ( secondfield ) <100

user19752 is right on all counts.

Finding a GetAsNumber >= 0 and < 100 is the easy part. Making sure the value in a text field is the number that GetAsNumber SAYS it is is the tough part.

For instance:

GetAsNumber ( "99" ) will give a result of 99

GetAsNumber ( "9fffffffff9" ) will give a result of 99 [which is not what you want]

GetAsNumber ( "1,000" ) will give a result of 1000 [which is not the same as a text value of "1,000"]

GetAsNumber ( "0.1" ) will give a result of .1 [which is not the same as a text value of "0.1"]

Further, IsValid applies ONLY to number fields. AND IsValid on a number field containing "9fffff9" will give a meaningless result of 1 (valid).

So...I turned to ExecuteSQL. Create a calculated field, and make sure it is UNSTORED, with the following formula:

**Let (****[****~ID = YOURTABLE::IDFIELD ;****~InRange = Case (****GetAsNumber ( YOURTABLE::YOURFIELD ) ≥ 0 and GetAsNumber ( YOURTABLE::YOURFIELD ) < 100 ; "InRange" ;****"") ;****~IsNum = ExecuteSQL (****"SELECT YOURFIELD****FROM YOURTABLE****WHERE NUMVAL(YOURFIELD) >= 0 and IDFIELD = ?"****; "" ; "" ; ~ID )****] ;****Case****( ~InRange = "InRange" and ~IsNum ≠ "?" ; "In Range" ;****""****)****)**Your script could then search on this new field for "In Range".

The components:

1) ~ID. By setting this variable equal to the ID of each record, the calculation will look at each record individually.

2) ~InRange. If the GetAsNumber results in something >=0 and < 100, it's in range.

3) ~IsNum. Uses NUMVAL from ExecuteSQL to determine if your field will evaluate as a Numeric value greater than or equal to 0 for each record.

The case statement in the Let statement determines if the numeric value is in the range you wanted, and also if the ExecuteSQL statement returned a valid value.

Sorry for the length of this. You should be able to copy and paste the above Let statement and modify the following:

1) YOURTABLE::IDFIELD [include the table name and the field, match your file exactly]

2) YOURTABLE::YOURFIELD [include the table name and the field, match your file exactly]

3) YOURFIELDHERE [include the fieldname only, match your file exactly]

4) YOURTABLE [include the table name only, match your file exactly]

If you want to exclude "-" (or any other "non-number" values) in number field, use IsValid() function.

Let ( n = GetAsNumber ( secondField ) ;

IsValid ( secondField ) and n>=0 and n<100

)

GetAsNumber() returns empty value for "-", then empty is treated as 0 when compared to number.