Report that parses checkbox selections | Community
Skip to main content
Lyndsy-Denk
Community Advisor
Lyndsy-DenkCommunity Advisor
Community Advisor
December 3, 2021
Question

Report that parses checkbox selections

  • December 3, 2021
  • 3 replies
  • 1721 views

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?

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

3 replies

Doug_Den_Hoed_AtAppStore
Community Advisor
Doug_Den_Hoed_AtAppStoreCommunity Advisor
Community Advisor
December 3, 2021

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

Lyndsy-Denk
Community Advisor
Lyndsy-DenkCommunity AdvisorAuthor
Community Advisor
December 3, 2021

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?

Doug_Den_Hoed_AtAppStore
Community Advisor
Doug_Den_Hoed_AtAppStoreCommunity Advisor
Community Advisor
December 7, 2021

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

C
ChloeRo
Level 4
December 4, 2021

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.

Lyndsy-Denk
Community Advisor
Lyndsy-DenkCommunity AdvisorAuthor
Community Advisor
December 13, 2021

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.

L
LouAn
Level 2
January 3, 2022

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

    Terms & ConditionsAccessibility statement

    Loading footer...