Forum Discussion

LukeFuruto's avatar
LukeFuruto
Qrew Trainee
3 years ago

Multi-select text field- List contains item in the other list

I have two multi-select text lookup fields on a form. I want to create a third formula field that will provide a "yes" or "no" for whether [Multi-select field A] has any of the items that are in the other multi-select field (Multi-select field B]. How should I proceed? Thanks in advance for your help.

I've tried the below formula, but it does not seem to work.
If(Contains(ToText([Multi-select field A]), ToText([Multi-select field B])), "Yes", "No")

------------------------------
Luke Furuto
------------------------------
Formulas and functions
Tables and fields
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    3 years ago
    not tested but you would need to parse out the [Multi-select field B] components into separate formula variables.

    var text MultiA = ToText([Multi-select field A]);
    var text MultiB = ToText([Multi-select field B]);

    var text BOne = Trim(Part(ToText([Multi-select field B]),1,";"))); 
    var text BTwo = Trim(Part(ToText([Multi-select field B]),2,";")));
    var text BThree = Trim(Part(ToText([Multi-select field B]),3,";")));
    etc ...
    var text BTwenty = Trim(Part(ToText([Multi-select field B]),20";")));

    var text 
    IF(
    Contains($MultiOne, $BOne), "Yes",
    Contains($MultiOne, $BTwo), "Yes",
    Contains($MultiOne, $BThree), "Yes",
    etc ...
    Contains($MultiOne, $BTwenty), "Yes", "No")


    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------
    • MarkShnier__You's avatar
      MarkShnier__You
      Icon for Qrew Legend rankQrew Legend
      3 years ago
      Luke, if there are any problems with the syntax that you pleas post the formula and the error message as a copy and paste (not a screen shot).

      ------------------------------
      Mark Shnier (YQC)
      mark.shnier@gmail.com
      ------------------------------
      • LukeFuruto's avatar
        LukeFuruto
        Qrew Trainee
        3 years ago
        My apologies! Here's the code.

        var text MultiA = ToText([Secondary Risk Categories]);
        var text MultiB = ToText([Reporting - Secondary Risk Category]);

        var text BOne = Trim(Part(ToText([Reporting - Secondary Risk Category]),1,";"));
        var text BTwo = Trim(Part(ToText([Reporting - Secondary Risk Category]),2,";"));
        var text BThree = Trim(Part(ToText([Reporting - Secondary Risk Category]),3,";"));
        var text BFour = Trim(Part(ToText([Reporting - Secondary Risk Category]),4,";"));
        var text BFive = Trim(Part(ToText([Reporting - Secondary Risk Category]),5,";"));
        var text BSix = Trim(Part(ToText([Reporting - Secondary Risk Category]),6,";"));
        var text BSeven = Trim(Part(ToText([Reporting - Secondary Risk Category]),7,";"));
        var text BEight = Trim(Part(ToText([Reporting - Secondary Risk Category]),8,";"));
        var text BNine = Trim(Part(ToText([Reporting - Secondary Risk Category]),9,";"));
        var text BTen = Trim(Part(ToText([Reporting - Secondary Risk Category]),10,";"));
        var text BEleven = Trim(Part(ToText([Reporting - Secondary Risk Category]),11,";"));
        var text BTwelve = Trim(Part(ToText([Reporting - Secondary Risk Category]),12,";"));
        var text BThirteen = Trim(Part(ToText([Reporting - Secondary Risk Category]),13,";"));

        var text
        If(
        Contains($MultiA, $BOne), "Yes",
        Contains($MultiA, $BTwo), "Yes",
        Contains($MultiA, $BThree), "Yes",
        Contains($MultiA, $BFour), "Yes",
        Contains($MultiA, $BFive), "Yes",
        Contains($MultiA, $BSix), "Yes",
        Contains($MultiA, $BSeven), "Yes",
        Contains($MultiA, $BEight), "Yes",
        Contains($MultiA, $BNine), "Yes",
        Contains($MultiA, $BTen), "Yes",
        Contains($MultiA, $BEleven), "Yes",
        Contains($MultiA, $BTwelve), "Yes",
        Contains($MultiA, $BThirteen), "Yes", "No")

        ------------------------------
        Luke Furuto
        ------------------------------