Counting Varies Depending on Multiple Choice Selected | Discussions

Expand all | Collapse all

Counting Varies Depending on Multiple Choice Selected

  • 1.  Counting Varies Depending on Multiple Choice Selected

    Posted 03-28-2019 14:59
    I have a multiple choice field that displays the following:

    -NONE
    -A
    -B
    -C
    -D

    I need a new formula field that displays "0" if NONE is selected and then if any other letters are selected to add them up.

    So if unit

    101 needs BD = 2
    102 NONE = 0
    103 AD = 2
    104 C =1

    Please help me with the formula.  I tried looking in the knowleadgebase but didn't see a case where if a certain selection in my case NONE would equal to "0" and count if any others.

    I can provide screenshots if needed.


  • 2.  RE: Counting Varies Depending on Multiple Choice Selected

    Posted 03-28-2019 15:25
    It was unclear, but I think you are counting the number of characters in the multiple choice. Am I correct?
    If so, this numeric formula might do it:
    If(
         [Letters Field] = "NONE", 0,
         Length([Letters Field])
    )


  • 3.  RE: Counting Varies Depending on Multiple Choice Selected

    Posted 03-28-2019 15:51
    That is correct, I want to count each letter as +1 only.  If NONE is selected then it should say 0

    I tried this:
    If([Tubs Needed]="NONE",0,Length([Tubs Needed]))

    and received this error on the = sign

    The operator '=' can't be applied to on types textlists text

    I am using a Formula Text field.  




  • 4.  RE: Counting Varies Depending on Multiple Choice Selected

    Posted 03-28-2019 15:55
    Instead of [Tubs Needed]="NONE", try this:
    Contains([Tubs Needed], "NONE")


  • 5.  RE: Counting Varies Depending on Multiple Choice Selected

    Posted 03-28-2019 15:58
    I'd suggest using a numeric formula field if possible because I think you're looking for a number as the output of your formula.  If you wanted to keep using a text formula field you might be able to use this formula based on what you provided:

    If([Tubs Needed]="NONE","0",ToText(Length([Tubs Needed])))

    Though for reporting purposes a numeric field seems like the way to go


  • 6.  RE: Counting Varies Depending on Multiple Choice Selected

    Posted 03-28-2019 16:29
    I want to use formula numeric field. I tried this:



  • 7.  RE: Counting Varies Depending on Multiple Choice Selected

    Posted 03-28-2019 16:29
    As you can see, the formula "sort of works" so we are closer :)


  • 8.  RE: Counting Varies Depending on Multiple Choice Selected

    Posted 03-28-2019 16:34
    Screenshot #1 
    Is the [Tub Count] Formula Field, it is a Formula Numeric Field - you can see the current formula I am using

    Screenshot #2 
    This is what the Tub Count is summarizing/counting.  If user selects NONE then I need 0, any others are selected then I need a count.  

    Screenshot #3
    The report, the last two columns on the right, Tubs Needed and Tub Count.  The NONE argument returns a 0, YAY! This works just as I want,  the second argument should COUNT every letter, instead I get a number 3 no matter how many letters are selected.  

    Please help.


  • 9.  RE: Counting Varies Depending on Multiple Choice Selected

    Posted 03-28-2019 16:36
    Have you tried going back to the Length([Tubs Needed])  instead of the Count argument you're using at the end of your If-Statement?

    Jake Rattner | Solutions Architect
    (847) 927-1427 | jrattner@quandarycg.com
    Quandary Knowledge Base


  • 10.  RE: Counting Varies Depending on Multiple Choice Selected

    Posted 03-28-2019 16:44
    Yes, I tried but, I may be doing something wrong.  It returns this error.  I thought maybe because it is not expecting the multiple choice textlist?


  • 11.  RE: Counting Varies Depending on Multiple Choice Selected

    Posted 03-28-2019 17:28
    Because you are using a text list, you have to do some mapping.
    Try this:

    var number a = if(contains([Tubs Needed], "A"),1,0);
    var number b = if(contains([Tubs Needed], "B"),1,0);
    var number c = if(contains([Tubs Needed], "C"),1,0);
    var number d = if(contains([Tubs Needed], "D"),1,0);
    If(
        contains([Tubs Needed], "NONE"),0,
        $a + $b + $c + $d
    )


  • 12.  RE: Counting Varies Depending on Multiple Choice Selected

    Posted 03-28-2019 18:36
    GENIUS! That worked :)
    Thank you so much, especially for your patience!