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

Question

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
------------------------------

MarkShnierYou · Answer

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
------------------------------

Sign up

Login to the community