Discussions

 View Only
  • 1.  Multi-select text field- List contains item in the other list

    Posted 01-11-2022 15:22
    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
    ------------------------------


  • 2.  RE: Multi-select text field- List contains item in the other list

    Posted 01-11-2022 17:47
    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
    ------------------------------



  • 3.  RE: Multi-select text field- List contains item in the other list

    Posted 01-12-2022 12:48
    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
    ------------------------------



  • 4.  RE: Multi-select text field- List contains item in the other list

    Posted 01-12-2022 13:44
    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
    ------------------------------



  • 5.  RE: Multi-select text field- List contains item in the other list

    Posted 01-12-2022 14:05
    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
    ------------------------------



  • 6.  RE: Multi-select text field- List contains item in the other list

    Posted 01-12-2022 18:43

    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?






  • 7.  RE: Multi-select text field- List contains item in the other list

    Posted 01-12-2022 19:33
    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
    ------------------------------



  • 8.  RE: Multi-select text field- List contains item in the other list

    Posted 01-13-2022 08:16

    Works like a charm! I'm so grateful. Thanks so much for all your help on this.