Solved

How to use multiple choices of a checkbox?

Posted on 2010-11-22
9
1,077 Views
Last Modified: 2013-12-18
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
Comment
Question by:jkee54
9 Comments
 
LVL 22

Expert Comment

by:mbonaci
ID: 34189164
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

0
 
LVL 3

Expert Comment

by:jjphardy
ID: 34189255
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...
0
 
LVL 22

Accepted Solution

by:
mbonaci earned 250 total points
ID: 34189266
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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 22

Expert Comment

by:mbonaci
ID: 34189327
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

0
 
LVL 21

Expert Comment

by:shuboarder
ID: 34189328
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
 
LVL 22

Expert Comment

by:mbonaci
ID: 34189439
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

0
 
LVL 31

Expert Comment

by:qwaletee
ID: 34196689
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"
0
 
LVL 22

Expert Comment

by:mbonaci
ID: 34196710
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 Closing Comment

by:jkee54
ID: 34197140
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

For Desktop Techs: How to retain a user's Notes configuration data when swapping out the end user's computer. (Assuming that you are not upgrading to a completely different version of Notes client) All you need to do is: 1) install Notes o…
You’ve got a lotus Domino web server, and you have been told that “leverage browser caching” is a must do. This means that we have to tell the browser everywhere in the web to use cache. In other words, we set (and send) an expiration date in the HT…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question