Try this one. It tests OK for me.
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,";"));
If(
$BOne <>"" and Contains($MultiA, $BOne), "Yes",
$BTwo <>"" and Contains($MultiA, $BTwo), "Yes",
$BThree <>"" and Contains($MultiA, $BThree), "Yes",
$BFour <>"" and Contains($MultiA, $BFour), "Yes",
$BFive <>"" and Contains($MultiA, $BFive), "Yes",
$BSix <>"" and Contains($MultiA, $BSix), "Yes",
$BSeven <>"" and Contains($MultiA, $BSeven), "Yes",
$BEight <>"" and Contains($MultiA, $BEight), "Yes",
$BNine <>"" and Contains($MultiA, $BNine), "Yes",
$BTen <>"" and Contains($MultiA, $BTen), "Yes",
$BEleven <>"" and Contains($MultiA, $BEleven), "Yes",
$BTwelve <>"" and Contains($MultiA, $BTwelve), "Yes",
$BThirteen <>"" and Contains($MultiA, $BThirteen), "Yes", "No")
------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com------------------------------
Original Message:
Sent: 01-12-2022 18:42
From: Luke Furuto
Subject: Multi-select text field- List contains item in the other list
Thanks again for your help. I think we are close. I removed the var text, and I receive no error code now, but the field always answers "yes" even when it is not the case. Any thoughts?
Original Message:
Sent: 1/12/2022 2:05:00 PM
From: Mark Shnier (YQC)
Subject: RE: Multi-select text field- List contains item in the other list
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
Original Message:
Sent: 01-12-2022 13:43
From: Luke Furuto
Subject: Multi-select text field- List contains item in the other list
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
Original Message:
Sent: 01-12-2022 12:47
From: Mark Shnier (YQC)
Subject: Multi-select text field- List contains item in the other list
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
Original Message:
Sent: 01-11-2022 17:46
From: Mark Shnier (YQC)
Subject: Multi-select text field- List contains item in the other list
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
Original Message:
Sent: 01-11-2022 15:21
From: Luke Furuto
Subject: 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
------------------------------