Forum Discussion

AlexGale's avatar
AlexGale
Qrew Captain
7 years ago

Combine two multi-select fields

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?
App builders
Ask about Quickbase functionality
Formulas and functions
  • RobIV's avatar
    RobIV
    Qrew Cadet
    7 years ago
    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
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    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
    • AlexGale's avatar
      AlexGale
      Qrew Captain
      7 years ago
      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. 
    • AlexGale's avatar
      AlexGale
      Qrew Captain
      7 years ago
      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
    • AlexGale's avatar
      AlexGale
      Qrew Captain
      7 years ago
      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.