asked on

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?

mbonaci

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" )

Open in new window

It doesn't work like that, because this:

Grade = ("A” : “B” : “C” : “D” : “E”)

Open in new window

is the same thing as this:

@IsMember( Grade; "A” : “B” : “C” : “D” : “E”)...

Open in new window

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" )

Open in new window

2. Or you can Compare lists:

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

Open in new window

jjphardy

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.
@if(@implode(grade)="ABCDE");"RED"
; 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...

ASKER CERTIFIED SOLUTION

mbonaci

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mbonaci

One correction to jjphardy's post,

@Implode( "A” : “B” : “C” : “D” : “E” )

Open in new window

should, according to the spec, return:

"A B C D E”

Open in new window

(with space as the separator), not:

"ABCDE”

Open in new window

That result would need something like this:

@Implode( "A” : “B” : “C” : “D” : “E”; "" )

Open in new window

shuboarder

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.

mbonaci

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";
...

Open in new window

not like this:

@If( @Elements( Grade ) = 5; "RED";
...
       Grade = " "; "none selected";
...

Open in new window

qwaletee

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.

combo := @Implode(Grade);
@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"

mbonaci

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.

jkee54

ASKER

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!