Forum Discussion

LukeFuruto's avatar
LukeFuruto
Qrew Trainee
4 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 o...
Formulas and functions
Tables and fields
MarkShnier__You's avatar
MarkShnier__You
Icon for Qrew Legend rankQrew Legend
4 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
    4 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
      4 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
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        4 years ago
        You have not provided the error message but if you erase the stand alone line

        var text

        which is sandwiched between the two blocks of code, it will probably work.

        ------------------------------
        Mark Shnier (YQC)
        mark.shnier@gmail.com
        ------------------------------