Forum Discussion

MattMelrose's avatar
MattMelrose
Qrew Member
5 years ago

Contains -- Multiple options

Hey,

Basically I have a bunch of locations and different jobs that I would like to be able to Populate the field with one outcome, and I am wondering if its possible to have something like

IF(Contains([LOCATION],"Milton","Oakville","Mississauga") AND ([WORKTYPE],"Spill","Containment"), "Emergency Response"

Or if I would have to list out all the possible combinations. Which would total about 100 different types. 

Thanks for any help

------------------------------
Matt Melrose
------------------------------
Formulas and functions

10 Replies

Sort By
  • DonLarson's avatar
    DonLarson
    Qrew Commander
    5 years ago
    Matt,

    You can build a statement

    IF (  Contains([LOCATION],"Milton","Oakville","Mississauga") AND
            Contains ([WORKTYPE],"Spill","Containment"), "Emergency Response", 
            Next set of conditions, ....

    Is there a deeper relationship you can build and exploit to get your result so that you do not have to create a massive If Statement?

    I am assuming that your users type in a [LOCATION] from information about the customer so there is an unlimited amount of possible answers. If  "Spill" and "Containment"   are always going to be "Emergency Response"  then you could have the table where you need the information "Emergency Response"  inherit from the relationship.


    In this example the user would be able to type "Milton" into the Work Order, then select "Spill" and it would automatically be an "Emergency Response"   I realize this does not exactly answer your question but I hope it sparks an idea on how to architect your application to get the answer you need.  


    Reach out if you want to share more info on the app and look deeper at structure.
     






    ------------------------------
    Don Larson
    Paasporter
    Westlake OH
    ------------------------------
  • AdamKeever1's avatar
    AdamKeever1
    Qrew Commander
    5 years ago
    The 'Contains' formula function requires a single variable text string and a text field to query, but you can use the Or function to query all of the combinations of your options.

    Try this Matt:

    IF(
    Contains([LOCATION],"Milton")=true or Contains([LOCATION],"Oakville")=true or Contains([LOCATION],"Mississauga")=true
    and Contains([WORKTYPE],"Spill")=true or Contains([WORKTYPE],"Containment")=true,
    "Emergency Response",
    "")

    ------------------------------
    Adam Keever
    ------------------------------
    • MarkShnier__You's avatar
      MarkShnier__You
      Icon for Qrew Legend rankQrew Legend
      5 years ago
      Matt, 
      I agree with Don.  You can do this by formula,  but really this should be table driven so you don't end up with a formula with 100 conditions in a long IF statement.  It's just not a good design and it means that regular users can't make a new entry.

      If you can describe the parameters which drive the result, we can suggest a table structure.  Is the result solely determined by the combination of the Location and Work Type?

      ------------------------------
      Mark Shnier (YQC)
      Quick Base Solution Provider
      Your Quick Base Coach
      http://QuickBaseCoach.com
      mark.shnier@gmail.com
      ------------------------------
      • MattMelrose's avatar
        MattMelrose
        Qrew Member
        5 years ago

        I am good with making a table, it's just something I have never had to build, so it's causing hesitation on my end.

         

        Yes, the result is solely based on the two factors (location and work type)

         

        Thanks,

         



        ------Original Message------

        Matt, 
        I agree with Don.  You can do this by formula,  but really this should be table driven so you don't end up with a formula with 100 conditions in a long IF statement.  It's just not a good design and it means that regular users can't make a new entry.

        If you can describe the parameters which drive the result, we can suggest a table structure.  Is the result solely determined by the combination of the Location and Work Type?

        ------------------------------
        Mark Shnier (YQC)
        Quick Base Solution Provider
        Your Quick Base Coach
        http://QuickBaseCoach.com
        mark.shnier@gmail.com
        ------------------------------