Combine 2 multi-select, one lookup, and one free text field into one followed by commas controlling for null values.

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

I have 2 multi-select text fields, one lookup field, and one free text field that I'd like to combine into one field separated by a comma and controlling for null values that can occur.  I've tried to combine two formulas I found from the community but I can't seem to get them to work in combination. 

Service_1 - multi-select text  

Service_2 - multi-select text 

Service KB - look up field from another table 

Service FT - simple text field 


This formula works fine...

List(" ", ToText([Impact: Service_1]), ToText([Impact: Service_2]), [Service KB], [Service FT])

This formula to add commas between fields an no commas I finding to be really complicated. I started with something crazy like this...

If(Trim(List(" ", ToText([Impact: Service_1]) <>"", List("-",[Impact: Service_1],ToText([Impact: Service_2])))))

I don't know what to Trim, what to List - or how to control for null values. 

Any help would be greatly appreciated. 

Photo of Anne

Anne

  • 100 Points 100 badge 2x thumb

Posted 3 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,720 Points 50k badge 2x thumb
I think that you are overthinking this.  The List function will concatenate text values and separate non null values them by any string you specify.



List(", ",

ToText([Service_1]),

ToText([Service_2]),

[Service KB],

[Service FT])
Photo of Anne

Anne

  • 100 Points 100 badge 2x thumb
This get's me part way. The semicolons that are usually used within the Multi-Select text field are replaced by commas but when stringing two multi-select text fields together, I get a semicolon between them.  

So, I get Service_1, Service_1; Service_2, Service 2;
Photo of Anne

Anne

  • 100 Points 100 badge 2x thumb
Oops - I have that backwards... Service_1; Service_1, Service_2; Service_2
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,720 Points 50k badge 2x thumb
The easy solution would be to semi colon delimit then so they are consistently semi colons and not a mix between comma and semi colon,

But if you want to have them all as one long comma delimited string, there is a formula for that.  Let me know and be ready for a long formula.
Photo of Anne

Anne

  • 100 Points 100 badge 2x thumb
A long formula - that is what I was afraid of... Yes, unfortunately, they need to be commas.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,720 Points 50k badge 2x thumb
OK, here goes .....

var text ServiceOne = ToText([Service_1]);

var text SAOne = Trim(Part($ServiceOne, 1, ";"));
var text SATwo = Trim(Part($ServiceOne, 2, ";"));
var text SAThree = Trim(Part($ServiceOne, 3, ";"));
// and continue the pattern here up until
// var text SATwenty = Trim(Part($ServiceOne, 20, ";"));


var text ServiceTwo = ToText([Service_2]);

var text SBOne = Trim(Part($ServiceTwo, 1, ";"));
var text SBTwo = Trim(Part($ServiceTwo, 2, ";"));
var text SBThree = Trim(Part($ServiceTwo, 3, ";"));

// and continue the pattern up to
// var text SATwenty = Trim(Part($ServiceOne, 20, ";"));

List(", ",
$SAOne, $SATwo, $SAThree,
// and the rest of the 20

$SBOne, $SBTwo, $SBThree,
// and the rest of the twenty

[Service KB],
[Service FT])
Photo of Anne

Anne

  • 100 Points 100 badge 2x thumb
How much do I owe you?  LOL!  So it looks like I have to create a variable for each multi-select text option.  I suppose I could create a zillion check boxes but that gets messy... are there other options I might not have thought about?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,720 Points 50k badge 2x thumb
Yes, the limit is the user could select up to 20 choices in a Multi-select field, so you need to isolate each of the 20 and then list them comma separated.

It would only take you ten minutes to complete the formula.  Its just copy and paste and edit.
Photo of Anne

Anne

  • 100 Points 100 badge 2x thumb
In my parent table, I can have 50 multi-select choices.  However, in child tables, it reduces to 20.  Am I just lucky?  Or will this someday bite me as a feature that was never intended.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,720 Points 50k badge 2x thumb
I think that the list of choices can be 50 choices, of which you can choose up to 20 on any particular record.  So I think that you just need to handle up to 20.
Photo of Anne

Anne

  • 100 Points 100 badge 2x thumb
Gotcha.. thank you again