Solved

How to use multiple choices of a checkbox?

Posted on 2010-11-22
9
1,073 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
 
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Lack of Storage capacity is a common problem that exists in every field of life. Here we are taking the case of Lotus Notes Emails, as we all know that we are totally depend on e-communication i.e. Emails. This article is fully dedicated to resolvin…
Article by: Rob
Notes 8.5 Archiving Steps and Tips This article covers setting up a Notes archive, and helps understand some of the menu choices making setting up and maintaining a Notes archive file easier.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

705 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now