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.
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.
 472 Points
 needy
Posted 3 months ago
 648 Points
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])
)
If so, this numeric formula might do it:
If(
[Letters Field] = "NONE", 0,
Length([Letters Field])
)
 472 Points
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.
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.
 648 Points
Instead of [Tubs Needed]="NONE", try this:
Contains([Tubs Needed], "NONE")
Contains([Tubs Needed], "NONE")
 660 Points
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
If([Tubs Needed]="NONE","0",ToText(Length([Tubs Needed])))
Though for reporting purposes a numeric field seems like the way to go
 472 Points
I want to use formula numeric field. I tried this:
 472 Points
As you can see, the formula "sort of works" so we are closer :)
 472 Points
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.
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.
 660 Points
Have you tried going back to the Length([Tubs Needed]) instead of the Count argument you're using at the end of your IfStatement?
Jake Rattner  Solutions Architect
(847) 9271427  jrattner@quandarycg.com
Quandary Knowledge Base
Jake Rattner  Solutions Architect
(847) 9271427  jrattner@quandarycg.com
Quandary Knowledge Base
(Edited)
 472 Points
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?
 648 Points
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
)
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
)
(Edited)
 472 Points
GENIUS! That worked :)
Thank you so much, especially for your patience!
Thank you so much, especially for your patience!
Related Categories

App builders
 949 Conversations
 47 Followers

Formulas & functions
 3013 Conversations
 80 Followers