• Status: Solved
• Priority: Medium
• Security: Public
• Views: 1087

# How to use multiple choices of a checkbox?

I have a checkbox field named Grade giving the user 5 choices - let's say
A
B
C
D
E

The user can choose one or more up to the entire 5.

Now I have another, computed field that gives results based on those choice(s).  So my @if would say something like this:

@If(Grade = "A"; "BLUE"
; @If(Grade = "B"; "PINK"
; @If(Grade = "C"; "WHITE"
; @If(Grade =" "; "make another choice"
; @If(Grade = ("A” : “B” : “C” : “D” : “E”); "RED"
; @If(Grade = "D"; "PURPLE"
; @If(Grade = "E"; "ORANGE"

; "invalid")))))))

What is happening is if the user has chosen all 5 letters, it is returning BLUE, where I want it to fail the first 4 evaluations and succeed on the statement that returns RED.

I understand that since all 5 has been chosen, it finds A and returns BLUE - but the field contents isn't JUST A, it reads in the Document Properties Field Tab as
"A"
"B"
"C"
"D"
"E"
without separators.

I've tried different separators for the list that should return RED but I still get BLUE.  I've tried no separators. The point is that I need a distinct evaluation if all of the letters are chosen vs. just one.  I put the multiple choice line in the middle to see if the order of statements matter but it doesn't seem to.

I know I could try lotus script but this seems simple enough for formula language, plus I would still have the separator difficulty in the  case when I am avaluating someone who chose all 5.

So my question is, experts - how do you refer to a checkbox or listbox to have all choices evaluated together?
0
jkee54
1 Solution

Commented:
First, you don't need all those @If-s when you have multiple if options:
``````@If( Grade = "A"; "BLUE";
Grade = "B"; "PINK";
Grade = "C"; "WHITE";
"Grade was none of the above" )
``````

It doesn't work like that, because this:
``````Grade = ("A” : “B” : “C” : “D” : “E”)
``````
is the same thing as this:
``````@IsMember( Grade; "A” : “B” : “C” : “D” : “E”)...
``````

You can check whether all options are selected in a number of ways.

1. You can count list members in Grade, like this:

``````@If( @Elements( Grade ) = 5; "RED"; "all options are not selected" )
``````

2. Or you can Compare lists:

``````@If( @Compare( Grade; "A” : “B” : “C” : “D” : “E” ) = 0; "RED"; "all options are not selected")
``````
0

Commented:
There are 2 issues here. One is order of precedence and the other is how lists are compared.

Grade is returned as a list. Not a single value. When you test grade against one value. It returns true if any entry in the list is the one value.
If Grade is "A” : “B” : “C” : “D” : “E” then Grade = "A".
You can solve this by using an implode function @if(@implode(grade)="ABCDE");"RED"..
Your above equation will still return "BLUE" because of the order the test are done. It will first test grade against "A"..It will be true and return "BLUE" then stop. If you want to return "RED" as the priority, put the test first.

What is not clear is what about the other values? If only "A" and "B" are selected. What do you want to do? If you use your equation, only one color is returned. You will need to consider this.

I think this will get you what you want.
; Grade = "A"; "BLUE"
; Grade = "B"; "PINK"
; Grade = "C"; "WHITE"
; Grade =" "; "make another choice"
; Grade = "D"; "PURPLE"
; Grade = "E"; "ORANGE"
; "invalid");

If you notice there is only one @if. The 3rd param can be a returned value or a test. It will return the 4th param if it is true or go to the 5th param. That can be a return value or a test and so on and so on...
0

Commented:
So, you should first check whether all options are selected, and then, if not, narrow down to specific options, like this:

@If( @Elements( Grade ) = 5; "RED";
Grade = "A"; "BLUE";
Grade = "B"; "PINK";
Grade = "C"; "WHITE"; ...

I don't know what exactly do you want the formula to do, if not all options are selected...
0

Commented:
One correction to jjphardy's post,
``````@Implode( "A” : “B” : “C” : “D” : “E” )
``````

should, according to the spec, return:
``````"A B C D E”
``````

(with space as the separator), not:
``````"ABCDE”
``````

That result would need something like this:
``````@Implode( "A” : “B” : “C” : “D” : “E”; "" )
``````
0

Commented:
One way this could be achieved is by creating a string value out of the selected checkbox choices then using @Contains on the string to determine which colours have been selected.
0

Commented:
And one more thing I just noticed,
to check whether none of the options are selected, you should compare grade with empty string, not with space.

So you should write it like this:
``````@If( @Elements( Grade ) = 5; "RED";
...
Grade = ""; "none selected";
...
``````

not like this:
``````@If( @Elements( Grade ) = 5; "RED";
...
Grade = " "; "none selected";
...
``````
0

Commented:
Here's a simpler way of doing it. Use @Implode, which turns the LIST into a SINGLE STRING, which is easy to compare against if you want to compare EXACT COMBINATIONS.

@If(
combo = "A"; "BLUE";
combo = "B"; "PINK";
combo = "C"; "WHITE";
combo =""; "make another choice";
combo = ("A B C D E”); "RED";
combo = "D"; "PURPLE";
combo = "E"; "ORANGE";
"invalid")

Now A has to be the ONLY selection for the first test to pass, and all items have to be selected to make the 5th test pass. Of curse, it will be tedious to account for every single combination. That's 32 combinations! Oof course, you can mix and match, and you can order things so that after some special cases are checked, the rest only checks for important elements of the combination (e.g., only check if it has an E without checking what else might be checked, which is just grade="E"
0

Commented:
That approach would be more consistent with option button, not check-box.
Since it's a check-box, the natural thing to assume is that you want to check out whether A is one of the selected ones... Not if it's the only one selected.

That's why I asked about use case.
0

Author Commented:
I tried all of the approaches and although I like the Implode function, I only needed to see when all 5 were chosen so I used @elements and it worked for what I needed.  I had problems with the implode function because I didn't realize that it adds spaces between the list members.  Thank you all!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

## Featured Post

Tackle projects and never again get stuck behind a technical roadblock.