# Discussions

View Only

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

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

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