Discussions

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

    Posted 10 days ago
    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 10 days 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
    ------------------------------



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

    Posted 9 days 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
    ------------------------------



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

    Posted 9 days 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
    ------------------------------



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

    Posted 9 days 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
    ------------------------------



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

    Posted 9 days ago

    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 9 days ago
    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 8 days ago

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