Additive Multi Select issues with contains

  • 1
  • 1
  • Question
  • Updated 1 month 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

  • 104 Points 100 badge 2x thumb

Posted 1 month ago

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

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 28,004 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

  • 53,240 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

  • 28,004 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

  • 104 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 discobeef

discobeef

  • 104 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

  • 53,240 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

  • 28,004 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

  • 104 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

  • 104 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!