Forum Discussion

Kreg's avatar
Kreg
Helper I
3 years ago
Solved

Query does not work as expected

Hi,

 

I have a query which works partially:

Model Type =
 IF (LEFT(model[type], 8) = "https://" ||
 OR(
    ISNUMBER(model[type])
    ,0
    ),
    "Accepted","Failed")
 
Now, you can imagine that someone provide a full link to car model field which is a "type" i.e. https://tesla.com/345345 or only 345345 and both of that inputs should be treat aa Accepted, but now only hyperlink goes to the Accepted bucket, but number - based on the above query - still to the Failed bucket. It is only provided in the first place, so I thought that part with ISNUMBER solves that issue, but not. How should I change it?




Need Help
  • rsbin's avatar
    rsbin
    3 years ago

    Kreg ,

    Here is a slight modification:

     

     

    Accepted/Failed = SWITCH(
                      TRUE(),
                      LEFT( [Type],8 )   = "https://", "Accepted",
                      LEFT( [Type], 1 ) IN {"1","2","3","4","5","6","7","8","9","0"}, "Accepted",
                      "Failed" )

     

     

    Type                                       Accepted/Failed

    https://tesla.com/345345 Accepted
    345345 Accepted
    - Failed
    Not Applicable Failed

    The limitation with this code is that it assumes if the first character is a digit, then the entire value is numeric.  If your entries will consist of both numbers and letters, then further adjustments will have to be made.

    Regards,

4 Replies

  • rsbin's avatar
    rsbin
    Community Champion
    3 years ago

    Kreg ,

    Looks like there are a few issues with your syntax.  Please try this:

     

    Accepted/Failed = SWITCH(
                      TRUE(),
                      LEFT( [Type],8) = "https://", "Accepted",
                      ISNUMBER( VALUE([Type] )) = TRUE(), "Accepted",
                      "Failed" )

     

     

    I trust I interpreted your question correctly.

    Regards,

    • Kreg's avatar
      Kreg
      Helper I
      3 years ago

      You interpreted it very correctly but I missed that in some rows might be some string provided by the user like "-", "Not Applicable", etc. and when I used your query I got message:
      "Cannot convert value '-' of type Text to type Number."

      So lets say that everything those kind of input must be treated as Failed. 

      • rsbin's avatar
        rsbin
        Community Champion
        3 years ago

        Kreg ,

        Here is a slight modification:

         

         

        Accepted/Failed = SWITCH(
                      TRUE(),
                      LEFT( [Type],8 )   = "https://", "Accepted",
                      LEFT( [Type], 1 ) IN {"1","2","3","4","5","6","7","8","9","0"}, "Accepted",
                      "Failed" )

         

         

        Type                                       Accepted/Failed

        https://tesla.com/345345 Accepted
        345345 Accepted
        - Failed
        Not Applicable Failed

        The limitation with this code is that it assumes if the first character is a digit, then the entire value is numeric.  If your entries will consist of both numbers and letters, then further adjustments will have to be made.

        Regards,