Solved

How to use multiple choices of a checkbox?

Posted on 2010-11-22
9
1,076 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
Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Creating a bat file fo Note.ini 4 213
How to access Names.nsf DB from a C# application 14 1,531
Missing Rules 1 301
Lotus DOMINO change "Delivery Failure Report" email address delivery 8 407
I thought it will be a good idea to make a post as it will help in case someone else faces these issues. I trust this gives an idea how each entry in Notes.ini can mean a lot for the Domino Server to be functioning properly. This article discusses t…
Notes Document Link used by IBM Notes is a link file which aids in the sharing of links to documents in email and webpages. The posts describe the importance and steps to create a Lotus Notes NDL file in brief.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

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