Discussions

Expand all | Collapse all

Additive Multi Select issues with contains

  • 1.  Additive Multi Select issues with contains

    Posted 11-05-2018 17:49
    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!








  • 2.  RE: Additive Multi Select issues with contains

    Posted 11-05-2018 18:04
    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.



  • 3.  RE: Additive Multi Select issues with contains

    Posted 11-05-2018 19:08
    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)



  • 4.  RE: Additive Multi Select issues with contains

    Posted 11-05-2018 19:17
    I modified the value to have the three characters in the front and rear:
    var Text string = " ; " & ToText([Radius Fees]) & " ; " ;


  • 5.  RE: Additive Multi Select issues with contains

    Posted 11-05-2018 19:15
    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!


  • 6.  RE: Additive Multi Select issues with contains

    Posted 11-05-2018 19:20
    Which field is Formula Number?


  • 7.  RE: Additive Multi Select issues with contains

    Posted 11-05-2018 19:40
    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.


  • 8.  RE: Additive Multi Select issues with contains

    Posted 11-05-2018 20:18
    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?


  • 9.  RE: Additive Multi Select issues with contains

    Posted 11-05-2018 22:29
    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.

    ">https://d2r1vs3d9006ap.cloudfront.net/s3_images/1760675/RackMultipart20181105-25135-1pkpfr7-image_inline.png?1541456598">

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



  • 10.  RE: Additive Multi Select issues with contains

    Posted 11-05-2018 22:39
    Please also post your actual code as we are not able to edit screen shots.


  • 11.  RE: Additive Multi Select issues with contains

    Posted 11-05-2018 22:59
    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)



  • 12.  RE: Additive Multi Select issues with contains

    Posted 11-05-2018 23:01
    remove the extra "

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



  • 13.  RE: Additive Multi Select issues with contains

    Posted 11-05-2018 23:04
    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)


  • 14.  RE: Additive Multi Select issues with contains

    Posted 11-05-2018 23:20
    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!



  • 15.  RE: Additive Multi Select issues with contains

    Posted 11-05-2018 23:47
    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!