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

    Portal filtering

    tays01s

      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
          user19752

          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
            tays01s

            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
              tays01s

              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
                taylorsharpe

                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
                  tays01s

                  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
                    user19752

                    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
                      tays01s

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

                      • 8. Re: Portal filtering
                        dtcgnet

                        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
                          tays01s

                          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
                            dtcgnet

                            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
                              tays01s

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

                              • 12. Re: Portal filtering
                                dtcgnet

                                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
                                  user19752

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

                                  • 14. Re: Portal filtering
                                    tays01s

                                    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