Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

Counting Selections in a Checkbox Field

Avatar

Level 4
For billing purposes I need to know how many languages are selected in a field called languages. So if English, French, Spanish is selected in my checkbox I need to know that there are 3 languages. I couldn't figure out a direct way to do this and think I remember a previous conversation about this. So I came up with a hack. So I have a calculated field called language count with the following formula: SUM(LEN(Languages),-LEN(Replace(Languages,", ",",")),IF(LEN(Languages)>0,1,0)) I'm replacing the normal syntax of a comma with a space with just the comma and subtracting the lengths of these 2 strings. Since either a blank or a single selection can have no commas, I just check if there is any values set and add 1. I'll simplify with A, B, C, D instead of longer names: So the Length of A, B, C, D is 10 The length of A,B,C,D is 7 10-7=3 If there is at least one element in the list add 1 So 10-7+1=4 -- Melinda Layten, Senior Consultant Work Management Improvement CapabilitySource Phone: (484) 505-6855 site: www.capabilitysource.com email: melinda.layten@capabilitysource.com - we simplify your work so you can run your business -
3 Replies

Avatar

Level 5
Do you know if there is a way to modify this to show the actual selections? I was told that there wasn't really a way to report on a multi-selection dropdown, but we totally need it. Any thoughts? Jazmin Allen-Collins Analog Devices, Inc.

Avatar

Level 8
How many options are there in your multi-select list? You can make a series of calculated fields (in a section only visible for admins if necessary) for each option: Field: Language A IF(Contains("A",Languages),"A") Field: Language B IF(Contains("B",Languages),"B") Or depending on your need you might prefer to use numbers Field: Language A IF(Contains("A",Languages),1,0) Barry Buchanan Work Management Australia

Avatar

Level 4
If you are looking to group by a checkbox field, you can't do that directly. What I usually do is create a field that captures the value of the checkbox field like CheckboxA CheckboxA_ (my notation for a display field) = CheckboxA - This sometimes puts the list items in a different orders if more then one is selected so if I want to simplify CheckboxA_ = IF(CONTAINS(",",CheckboxA),"Multiple",CheckboxA) Checkboxes are stored as comma separated lists (so don't use commas in your values!) so if there is a comma more than 1 option was selected. Shoutout to Adina Pierce for coming up with that syntax (or at least showing it to me). -- Melinda Layten, Senior Consultant Work Management Improvement CapabilitySource Phone: (484) 505-6855 site: www.capabilitysource.com email: melinda.layten@capabilitysource.com - we simplify your work so you can run your business -