1 2 Previous Next 16 Replies Latest reply on Nov 20, 2015 3:29 PM by dtcgnet

# Portal filtering

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?

• ###### 1. Re: Portal filtering

You can convert value to number, but what is "non-numeric value" in your case, and how do you want to treat it?

Let ( n = GetAsNumber ( secondField ) ;

n>0 and n<100

)

• ###### 2. Re: Portal filtering

I implemented your solution in a test and got exactly the records between >0 and <100, but also records with "-" values. These are what I meant by non-numeric values, but I'd thought the 'GetAsNumber' would exclude these. I only want values literally from 0 to <100 selected.

• ###### 3. Re: Portal filtering

The problem appears to be when using either n=0 or n>0. Either of these in the code cause the records with a "-" value to be included.

• ###### 4. Re: Portal filtering

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.

• ###### 5. Re: Portal filtering

Thanks.......your script captures the "-" records, not the numeric ones. However, I need to look again at the original. I didn't say, but the first lines of code worked fine, ie. no "-" values, but by the time I completed alternative numeric ranges, it went pear-shaped. I'll look again.

• ###### 6. Re: Portal filtering

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.

• ###### 7. Re: Portal filtering

User 19752: Brilliant ! I certainly wans't aware of the treatment of non-numeric values.

• ###### 8. Re: Portal filtering

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

• ###### 9. Re: Portal filtering

Unfortunately the above only returns records with a value = 0.

The fields are all 'text' and results from scripted evaluations of text fields.

• ###### 10. Re: Portal filtering

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

Sorry about that. Since they are text fields, you'll need to add the GetAsNumber parts as shown above.

• ###### 11. Re: Portal filtering

It now eliminates all the values = "-" (ie. text) but still captures numeric vales =>100 as well as between 0-100.

• ###### 12. Re: Portal filtering

Can you paste the formula here or attach a screen shot? It appears to be evaluating properly on my side. If it's including things over 99, then something is not right with the following portion of the formula:

GetAsNumber ( secondfield ) < 100

• ###### 13. Re: Portal filtering

Hmm, I thought second field is defined as number. IsValid(textfield) will always return true.

• ###### 14. Re: Portal filtering

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

1 2 Previous Next