Forum Discussion

BiffStockdale's avatar
BiffStockdale
Qrew Trainee
7 years ago

Additive Multi Select issues with contains

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!






Formulas and functions
  • _anomDiebolt_'s avatar
    _anomDiebolt_
    Qrew Elite
    7 years ago
    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.

  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    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)

    • _anomDiebolt_'s avatar
      _anomDiebolt_
      Qrew Elite
      7 years ago
      I modified the value to have the three characters in the front and rear:
      var Text string = " ; " & ToText([Radius Fees]) & " ; " ;
  • BiffStockdale's avatar
    BiffStockdale
    Qrew Trainee
    7 years ago
    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!
    • BiffStockdale's avatar
      BiffStockdale
      Qrew Trainee
      7 years ago
      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.
    • _anomDiebolt_'s avatar
      _anomDiebolt_
      Qrew Elite
      7 years ago
      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?