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.



                        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