Report that parses checkbox selections

[hilk_]

I need a report (ideally a chart, but let's start with a report) that counts selections from a checkbox custom field. I've searched the discussion threads about this and the best response I've seen so far is to use SUMIF expressions. Each of those solutions is then followed by, "That's untenable because of how many options we offer." I agree. What else do we have out there?

[Doug_Den_Hoed__AtAppStore]

This might sound outrageous Lyndsy, but...

I recommend you spit each checkbox into its own custom data parameter. Doing so will allow you to report (and filter, and group, and chart) on each individually, with ease.

If you need to see them combined, you can then do so by adding a calculated parameter that does so in a sensible, predictable, extendible order using (for example) CONCAT(IF(ISBLANK(Checkbox1),"","Checkbox 1 |"),IF(ISBLANK(Checkbox2),"","Checkbox 2 |"), etc.)

Similarly, you can add another calculated parameter that counts the selections using (for example) IF(ISBLANK(Checkbox1),0,1)+IF(ISBLANK(Checkbox2),0,1), etc.

Regards,

Doug

[hilk_]

Phew, this might still be over my head. Indeed, I want to be able to report, filter, group, and chart (especially chart).

Tell me more about the custom data parameter. How would I get there?

[skyehansen]

hey Lyndsy, Doug's telling you to break up your checkbox field into separate fields for each answer.

e.g. If you have a checkbox field called "What kind of fruit do you have" with checkbox answers for Apples, Oranges and Bananas, make three fields called "Do you have apples", "Do you have oranges" and "Do you have bananas", all with Yes/No answers. Or if you want to see the outrageousness example: if you have a field called "Which US State are you in", you would split that out into 50 questions.

[hilk_]

Thank you for the explanation. With about 30 options and an API in play, I can't imagine this is viable for us. I've reached out to our account manager to try to connect with a consultant.

[CalvinFold]

@Doug Den Hoed‚ and @Skye Hansen‚

Could you possibly do this in reverse? Instead of 30 separate questions, could it stilll be one question/checklist, but then 30 separate calculated fields to parse out if the various possible answers exist (with a yes/no or 1/0 that can then be totalled in yet another calculated field)?

Or does "exist" not work for things like checkbox answers?

[Doug_Den_Hoed__AtAppStore]

I appreciate your positive contrariness, Kevin!

Lyndsy could indeed do the reverse, and even hide all those calculated fields in a SysAdmin only section of the Custom form in order to not confuse casual users. Because the calculation is still persisted, all of the other advantages (e.g. Filtering, Grouping, Sorting, Charting, etc.) would still be available.

I'm putting this one in The Vault for future consideration -- thanks for suggesting it.

Regards

Doug

[ChloeRo]

Lyndsy - are you looking for something that just counts the # of options that were selected? If so, you could use this:

IF(ISBLANK(Field Name),"0",LEN(Field Name)-LEN(REPLACE(STRING(Field Name),", ","x"))+1)

Just a heads up - this formula wouldn't work if your checkbox options contain a comma. I have a feeling that this probably isn't what you were looking for, but just in case anyone else is stumbling around looking for a way to get the field count, it might help.

[hilk_]

As follow-up: Ultimately I'm trying to pull in a consultant to help us build a solution. And I've posted to the Innovation Lab (please upvote) because I feel like this is coding that could be applied at the system level to translate checkbox responses into readable reports. I've seen other systems parse checkboxes (Google, SurveyMoney, Microsoft). I'm very surprised that there are discussion posts back through 2017 about this issue and it's still a system limitation.

[LouAn]

Couldn't agree more!! I voted for your Innovation Lab suggestion - thanks!!