Additive Multi Select issues with contains

  • 1
  • 1
  • Question
  • Updated 7 months ago
  • Answered
I have a multi select field where the choices affect one or more of six other currency fields.  The idea is that when a choice is made in the Multi Select that choice may add dollar amounts to one or more of the six fields and multiple selections should add together in those six fields.

The problem is this

If I use the following formula syntax then multiple choices seem to cancel out or keep further Ifs from processing

If(ToText([Radius Fees])="Cable Abort",0,0)+
If(ToText([Radius Fees])="Greater than 24 - No date",0,0)+
If(ToText([Radius Fees])="LEAD Abort",0,0)+
If(ToText([Radius Fees])="HANDS Abort",0,0)+
If(ToText([Radius Fees])="RADIUS Abort",100,0)+

If instead I use Contains in the syntax it seems to work better, However, due to similar words in the choices in the multi select it seems that Contains improperly adds too much. Essentially, where I would expect it to look for and only add if the whole quoted string exists, its seems to add selections when it sees similar words in the portions of the quoted strings.

If(Contains(ToText([Radius Fees]),"Cable Abort"),0,0)+
If(Contains(ToText([Radius Fees]),"Greater than 24 - No date"),0,0)+
If(Contains(ToText([Radius Fees]),"LEAD Abort"),0,0)+
If(Contains(ToText([Radius Fees]),"HANDS Abort"),0,0)+
If(Contains(ToText([Radius Fees]),"RADIUS Abort"),100,0)+
If(Contains(ToText([Radius Fees]),"Missing Cable - LMI"),400,0)+
If(Contains(ToText([Radius Fees]),"Missing Cable - Radius"),0,0)+
If(Contains(ToText([Radius Fees]),"Radius Turn away LEAD"),275,0)+
If(Contains(ToText([Radius Fees]),"Radius Turn away HANDS"),0,0)+
If(Contains(ToText([Radius Fees]),"Revisit LEAD Abort"),0,0)+
If(Contains(ToText([Radius Fees]),"Revisit Radius Abort"),0,0)

Any suggestions as to syntax I could use so that contains only kicks in when the full quoted string is found?  Case maybe?  Can Case be used with + ?


Thanks for any suggestions!






Photo of discobeef

discobeef

  • 114 Points 100 badge 2x thumb

Posted 7 months ago

  • 1
  • 1
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
THIS ANSWER HAS BEEN UPDATED TO FIX A TYPO

Try this:

var Text string = " ; " & "ToText([Radius Fees]) & " ; " ;

If(Contains($string), " ; Cable Abort ; "), 0, 0) +
If(Contains($string), " ; Greater than 24 - No date ; "), 0, 0) +
If(Contains($string), " ; LEAD Abort ; "), 0, 0) +
If(Contains($string), " ; HANDS Abort ; "), 0, 0) +
If(Contains($string), " ; RADIUS Abort ; "), 100, 0) +
If(Contains($string), " ; Missing Cable - LMI ; "), 400, 0) +
If(Contains($string), " ; Missing Cable - Radius ; "), 0, 0) +
If(Contains($string), " ; Radius Turn away LEAD ; "), 275, 0) +
If(Contains($string), " ; Radius Turn away HANDS ; "), 0, 0) +
If(Contains($string), " ; Revisit LEAD Abort ; "), 0, 0) +
If(Contains($string), " ; Revisit Radius Abort ; "), 0, 0)

When a multi-select field is converted to a string all the selected options are concatenated together with the three characters " ; ". If you append and prepend these tree characters to the text version of the field you can successfully use Contains() to test for a particular value.

(Edited)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,558 Points 50k badge 2x thumb
Dan, I see where you are going, but what if the section was the first or last record on the list.  It would not have the leading ; or trailing ; (semi colon)

Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
I modified the value to have the three characters in the front and rear:
var Text string = " ; " & ToText([Radius Fees]) & " ; " ;
(Edited)
Photo of discobeef

discobeef

  • 114 Points 100 badge 2x thumb
Thanks for the reply Dan!   Quick follow up.
My fields are Formula Numeric and the Formula analyzer is having an issue with the Var statement saying its text but it expects numeric. How do we get around that?
Thanks!
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
Which field is Formula Number?
Photo of discobeef

discobeef

  • 114 Points 100 badge 2x thumb
I have the multi select text and then the six numeric formula fields which look for the items.chosen in the multi select.  Those six are what are getting fed the dollar amounts which is what the IFs determine.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
My formula takes the multi-select field [Radius Fees] as input and returns a number as output. I don't see a connection or conflict with the fact that you have six other fields. What am I missing?
Photo of discobeef

discobeef

  • 114 Points 100 badge 2x thumb
I cant save the formula as QB has an issue with the end of the Var statement.  It gives the reason that it is expecting a number but is finding text.  Wont let me save it.



When I try to save it shows a syntax error midway through the first If.

Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,268 Points 50k badge 2x thumb
Please also post your actual code as we are not able to edit screen shots.
Photo of discobeef

discobeef

  • 114 Points 100 badge 2x thumb
var Text string = " ; " & "ToText([Radius Fees]) & " ; " ;

If(Contains($string),"Cable Abort"), 0, 0) +
If(Contains($string),"Greater than 24 - No date"), 0, 0) +
If(Contains($string),"LEAD Abort"), 0, 0) +
If(Contains($string),"HANDS Abort"), 0, 0) +
If(Contains($string),"RADIUS Abort"), 100, 0) +
If(Contains($string),"Missing Cable - LMI"), 400, 0) +
If(Contains($string),"Missing Cable - Radius"), 0, 0) +
If(Contains($string),"Radius Turn away LEAD"), 275, 0) +
If(Contains($string),"Radius Turn away HANDS"), 0, 0) +
If(Contains($string),"Revisit LEAD Abort"), 0, 0) +
If(Contains($string),"Revisit Radius Abort"), 0, 0)

Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,268 Points 50k badge 2x thumb
remove the extra "

var Text string = " ; " & ToText([Radius Fees]) & " ; " ;

Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
And add the three character prefix and suffix:

If(Contains($string), " ; Cable Abort ; "), 0, 0) +
If(Contains($string), " ; Greater than 24 - No date ; "), 0, 0) +
If(Contains($string), " ; LEAD Abort ; "), 0, 0) +
If(Contains($string), " ; HANDS Abort ; "), 0, 0) +
If(Contains($string), " ; RADIUS Abort ; "), 100, 0) +
If(Contains($string), " ; Missing Cable - LMI ; "), 400, 0) +
If(Contains($string), " ; Missing Cable - Radius ; "), 0, 0) +
If(Contains($string), " ; Radius Turn away LEAD ; "), 275, 0) +
If(Contains($string), " ; Radius Turn away HANDS ; "), 0, 0) +
If(Contains($string), " ; Revisit LEAD Abort ; "), 0, 0) +
If(Contains($string), " ; Revisit Radius Abort ; "), 0, 0)
Photo of discobeef

discobeef

  • 114 Points 100 badge 2x thumb
Thank you both very much.  I never would have thought of the prefix suffix approach.  Additionally, I have noticed that whenever I paste someone else code i dont grasp whats going on very well vs when i write it.  So apologies for having to hold my hand the whole way through this.

Final thought, I needed an extra opening parenthesis on the left of string.

If(Contains(($string), " ; Cable Abort ; "), 0, 0) +

Again, thanks so much for a workable solution!

Photo of discobeef

discobeef

  • 114 Points 100 badge 2x thumb
Here is the final working code for anyone that ends up on this thread with a similar issue.


var Text string = " ; " & ToText([Radius Fees]) & " ; " ;

If(Contains(($string), " ; Cable Abort ; "), 0, 0) +
If(Contains(($string), " ; Greater than 24 - No date ; "), 0, 0) +
If(Contains(($string), " ; LEAD Abort ; "), 0, 0) +
If(Contains(($string), " ; HANDS Abort ; "), 0, 0) +
If(Contains(($string), " ; RADIUS Abort ; "), 100, 0) +
If(Contains(($string), " ; Missing Cable - LMI ; "), 400, 0) +
If(Contains(($string), " ; Missing Cable - Radius ; "), 0, 0) +
If(Contains(($string), " ; Radius Turn away LEAD ; "), 275, 0) +
If(Contains(($string), " ; Radius Turn away HANDS ; "), 0, 0) +
If(Contains(($string), " ; Revisit LEAD Abort ; "), 0, 0) +
If(Contains(($string), " ; Revisit Radius Abort ; "), 0, 0)

// Special thanks to Dan D and Mark S!



Again, thanks so much for a workable solution!