Combine two multi-select fields

  • 0
  • 1
  • Question
  • Updated 5 months ago
  • Answered
  • (Edited)
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?
Photo of Alex Gale

Alex Gale

  • 1,124 Points 1k badge 2x thumb

Posted 5 months ago

  • 0
  • 1
Photo of Rob White IV

Rob White IV

  • 968 Points 500 badge 2x thumb
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
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,928 Points 50k badge 2x thumb
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
Photo of Alex Gale

Alex Gale

  • 1,124 Points 1k badge 2x thumb
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. 
Photo of Alex Gale

Alex Gale

  • 1,124 Points 1k badge 2x thumb
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
Photo of Alex Gale

Alex Gale

  • 1,124 Points 1k badge 2x thumb
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. 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,928 Points 50k badge 2x thumb
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.  
Photo of Alex Gale

Alex Gale

  • 1,124 Points 1k badge 2x thumb
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
(Edited)