Discussions

Expand all | Collapse all

Combine two multi-select fields

  • 1.  Combine two multi-select fields

    Bronze
    Contributor
    Posted 06-28-2018 19:15
    I have set up a formula field to combine two multi-select fields into a using the list function. It looks like this: 
    List(";", [Select1], [Select2])

    At a basic level, this works fine. However, Select1 and Select 2 frequently end up having intersecting values. This is a problem for my formula. For example if: 
    Select1= A;B;D
    Select2=A;C;E
    the formula would output: A;A;B;C;D;E

    I should note that the number of selections for each field is not constant, and can be pretty much as large as the user wants. 

    Is there a way to fix this?


  • 2.  RE: Combine two multi-select fields

     
    Posted 06-28-2018 23:40
    What are you trying to combine them into?  A text value?  

    And, in your example, what are you expecting the result to be?

    Thanks,

    ~Rob


  • 3.  RE: Combine two multi-select fields

    Posted 06-28-2018 23:55
    You will need to first break up [Select 2] into its parts.

    var text PartOne = Part([Select 2],1,";");
    var text PartTwo= Part([Select 2],=2,";");

    etc etc etc

    var text PartTwenty = Part([Select 2],20,";");



    var text StepOne =
      List(";", [Select 1], IF(not Contains([Select 1], $PartOne), $PartOne);

    var text StepTwo = 
      List(";", $StepOne, IF(not Contains($StepOne, $PartTwo), $PartTwo);

    var text StepThree = 
      List(";", $StepTwo, IF(not Contains($StepTwo, $PartThree, $PartThree);

    etc etc etc

    var text StepTwenty = 
      List(";", $StepNineteen, IF(not Contains($StepNineteen, $PartTwenty, $PartTwenty);



    $StepTwenty


  • 4.  RE: Combine two multi-select fields

    Bronze
    Contributor
    Posted 06-29-2018 18:03
    That's awkward, but I suppose it makes sense. I'll have to inform my users in some way that there is a limit to the number of categories, although I can't imagine they'd ever select 20.

    One question, don't I have to check that each part isn't empty? It seems like if I don't, I'd end up with a bunch of empty entries in the list, still separated by semicolons. 


  • 5.  RE: Combine two multi-select fields

    Bronze
    Contributor
    Posted 06-29-2018 18:38
    Oh nevermind, I see now how the if functions on each of the parts don't actually return anything, so the list wouldn't add a blank space


  • 6.  RE: Combine two multi-select fields

    Bronze
    Contributor
    Posted 06-29-2018 19:27
    This worked great, with a few small tweaks. Thanks!

    I had to convert the multi-select fields to text, but that wasn't a huge issue. The bigger problem I ran into was that the function refused to work until I applied Trim() to each part individually.

    From what I can tell, this is because Multi-select fields, at least when converted to text, have a trailing space after each entry. For example, a ToText(MSelect) field might look like: "Option 1 ;Option 2 ;Option3". For this reason, the Contains() functions weren't working. 

    It is working now and again, I appreciate the help. 


  • 7.  RE: Combine two multi-select fields

    Posted 06-29-2018 19:42
    Great to hear. can I see you final formula or email it to me off line if you prefer.  I'd like to save it in my collection of tips and tricks.  


  • 8.  RE: Combine two multi-select fields

    Bronze
    Contributor
    Posted 06-29-2018 19:47
    Sure. It's very similar to what you posted. I did end up going with 20 parts, because it seemed like a good number. 

    var text SelectText = ToText([MultiSelectOne]); 

    var text POne = Trim(Part($SelectText, 1, ";"));
    var text PTwo = Trim(Part($SelectText, 2, ";"));
    ...


    var text StepOne = 
      List(";", ToText([MultiSelectTwo]), If(not Contains(ToText([MultiSelectTwo]), $POne), $POne));

    var text StepTwo = 
      List(";", $StepOne, If(not Contains($StepOne, $PTwo), $PTwo));
    ...

    I then used $StepTwenty in a GenAddRecord function, which I didn't copy here