Forum Discussion

vikaspasula's avatar
vikaspasula
Qrew Member
20 days ago

Need help building a Formula–Text field

I’m working in Quickbase and need help building a Formula–Text field based on selections in a Multi-Select Text field.

Requirement

The formula should return one of three designations based on a strict priority:

  1. Tier 1 — highest priority
  2. Tier 2 — medium priority
  3. Tier 3 — lowest priority

When selections from multiple tiers are present, the formula must return the highest-priority tier.

Current setup:
The source field is a Multi-Select Text field(20 options). I created a helper Formula–Text field using:

Lower(ToText([Multi-Select Field]))

The helper field correctly displays the selected options as semicolon-separated text.

However, formulas using the following methods do not consistently recognize all selected values:

Contains, Find,  Trim,  RegexMatch

Even a basic test such as this returns the false result, although the helper field visibly contains the phrase:

var text Criteria = Lower(ToText([Multi-Select Field])); If( Contains($Criteria, "example selection"), "MATCHED", "NOT MATCHED" )

I also tried:

  • Adding semicolon boundaries
  • Accounting for spaces around semicolons
  • Matching first, middle, and last list values
  • Removing punctuation from the configured option
  • Recreating the option manually
  • Using Find() instead of Contains()
  • Comparing the converted value directly
  • Using a formula query with the Multi-Select field ID and a HAS operator

The formula-query attempt returned blank.

Constraints

  • I need a formula-field-only solution
  • I do not want to use Quickbase Pipelines
  • I do not want to replace the Multi-Select field with many individual checkbox fields
  • Exact selection matching is preferred to avoid accidental partial matches
  • The solution must work when one or multiple choices are selected
  • The priority order must be preserved

What is the correct supported Quickbase formula syntax for determining whether a specific option is selected in a Multi-Select Text field? Is there a native list-membership function or formula-query method that works reliably for the current record?

 

formulas

7 Replies

  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    20 days ago

    There should be a very easy formula. Can you post a copy paste example of the semi colon delimited field's result from an example record, and the result you would like?

    If you do a copy paste as opposed to a screenshot, then I can actually test the formula myself.  

  • vikaspasula's avatar
    vikaspasula
    Qrew Member
    20 days ago

    Hey Mark! 

    Sure. Here is a generic copy/paste example of the semicolon-delimited result:

    option from tier 3 ; option from tier 2 ; option from tier 1

    Expected result:

    Tier 1

    The required priority is:

    Tier 1 Tier 2 Tier 3

    So when selections from multiple tiers are present, the formula should return the highest-priority matching tier.

    A simpler example:

    option from tier 2 ; option from tier 1

    Expected result:

    Tier 1

     

  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    20 days ago

    IF(

    Contains([My Semi field], "tier 1"), "Tier 1",

    Contains([My Semi field], "tier 2"), "Tier 2",

    Contains([My Semi field], "tier 3"), "Tier 3")

     

  • vikaspasula's avatar
    vikaspasula
    Qrew Member
    20 days ago

    Thanks, Mark. I have already tried Contains() directly against the Multi-Select Text field, but I’m still getting inconsistent results.

    To keep the example generic, here is the structure I tested:

    If(

        Contains([My Multi-Select Field], "Tier 1 - Option A") or

        Contains([My Multi-Select Field], "Tier 1 - Option B") or

        Contains([My Multi-Select Field], "Tier 1 - Option C"),

     

        "Tier 1",

     

        Contains([My Multi-Select Field], "Tier 2 - Option A") or

        Contains([My Multi-Select Field], "Tier 2 - Option B") or

        Contains([My Multi-Select Field], "Tier 2 - Option C"),

     

        "Tier 2",

     

        Contains([My Multi-Select Field], "Tier 3 - Option A") or

        Contains([My Multi-Select Field], "Tier 3 - Option B") or

        Contains([My Multi-Select Field], "Tier 3 - Option C"),

     

        "Tier 3",

     

        "NOT MATCHED"

    )

    ->I also tested a single condition:

    If(

        Contains(

            [My Multi-Select Field],

            "Tier 1 - Option A"

        ),

        "MATCHED",

        "NOT MATCHED"

    )

    When Tier 1 - Option A is the only selected value, the result is still NOT MATCHED.

    I created a helper Formula-Text field using:

    Lower(ToText([My Multi-Select Field]))

    and the displayed result is:

    tier 1 - option a

    I also tested multiple selections:

    tier 1 - option b ; tier 1 - option a

    Expected result:

    Tier 1

    But the formula still returns:

    NOT MATCHED

    Some other choices work when selected alone, so it seems to be specific to certain configured choices or to how the Multi-Select field is being evaluated.

     

  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    19 days ago

    You can only use the function Contains a field, which is of type Text.  So in my example, I assumed that you would make a helper field that would convert the multi select text field to be a text field.

    also, the contains function should just be looking for the phrase Tier 1.

     

    so if you were not using a helper field, that would look like this.

     

    IF(

    Contains(ToText([My Multi-Select Field]), "tier 1"), "Tier 1",

    Contains(ToText([My Multi-Select Field]), "tier 2"), "Tier 2",

    Contains(ToText([My Multi-Select Field]), "tier 3"), "Tier 3")

     

  • vikaspasula's avatar
    vikaspasula
    Qrew Member
    19 days ago

    Thanks, Mark. Below is a complete sanitized example using sample data.

    The Multi-Select Text field contains options grouped into three priority tiers.

    Tier 1 — highest priority

    Red Option

    Orange Option

    Yellow Option

    Green Option

    Tier 2 — medium priority

    Blue Option

    Purple Option

    Silver Option

    Gold Option

    White Option

    Black Option

    Gray Option

    Tier 3 — lowest priority

    North Option

    South Option

    East Option

    West Option

    Center Option

    The required priority is:

    Tier 1

    Tier 2

    Tier 3

    If a record contains selections from multiple tiers, the highest-priority matching tier should be returned.

    I created a helper Formula-Text field using:

    Lower(ToText([My Multi-Select Field]))

    Example helper-field result:

    north option ; blue option ; red option

    Expected result:

    Tier 1

    Another helper-field result:

    east option ; purple option

    Expected result:

    Tier 2

    A single-selection example:

    red option

    Expected result:

    Tier 1

    Here is the full Formula-Text formula I tested:

    var text Criteria =

        Lower(ToText([My Multi-Select Field]));

     

    If(

        Contains($Criteria, "red option") or

        Contains($Criteria, "orange option") or

        Contains($Criteria, "yellow option") or

        Contains($Criteria, "green option"),

     

        "Tier 1",

     

        Contains($Criteria, "blue option") or

        Contains($Criteria, "purple option") or

        Contains($Criteria, "silver option") or

        Contains($Criteria, "gold option") or

        Contains($Criteria, "white option") or

        Contains($Criteria, "black option") or

        Contains($Criteria, "gray option"),

     

        "Tier 2",

     

        Contains($Criteria, "north option") or

        Contains($Criteria, "south option") or

        Contains($Criteria, "east option") or

        Contains($Criteria, "west option") or

        Contains($Criteria, "center option"),

     

        "Tier 3",

     

        "NOT MATCHED"

    )

    I also tested Contains() directly against the Multi-Select Text field:

    If(

        Contains([My Multi-Select Field], "Red Option") or

        Contains([My Multi-Select Field], "Orange Option") or

        Contains([My Multi-Select Field], "Yellow Option") or

        Contains([My Multi-Select Field], "Green Option"),

     

        "Tier 1",

     

        Contains([My Multi-Select Field], "Blue Option") or

        Contains([My Multi-Select Field], "Purple Option") or

        Contains([My Multi-Select Field], "Silver Option") or

        Contains([My Multi-Select Field], "Gold Option") or

        Contains([My Multi-Select Field], "White Option") or

        Contains([My Multi-Select Field], "Black Option") or

        Contains([My Multi-Select Field], "Gray Option"),

     

        "Tier 2",

     

        Contains([My Multi-Select Field], "North Option") or

        Contains([My Multi-Select Field], "South Option") or

        Contains([My Multi-Select Field], "East Option") or

        Contains([My Multi-Select Field], "West Option") or

        Contains([My Multi-Select Field], "Center Option"),

     

        "Tier 3",

     

        "NOT MATCHED"

    )

    Some choices still return NOT MATCHED, even when selected by themselves, while the helper field visibly shows the expected value.

    Could you please test this sample setup and provide the exact formula you recommend for reliable Multi-Select Text membership checks and tier priority?

     

  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    19 days ago

    re:"Some choices still return NOT MATCHED"

    Can you give me an example semi colon delimited string which returns Not Matched?