It's best practice to avoid Checkboxes or Multiselect fields for any data that will need to be grouped on and counted. This is because capturing those counts require additional effort during the report building process because the values stored in these types of fields will be concatenated, resulting in an inability to group by them and achieve unique counts per option.
The purpose of this article will be to demonstrate an alternative to using a checkbox or multiselect field using linking fields that will allow multiple choices. Grouping will be possible, and you will no longer need to build one section per report using this method.
Real World Example
Let's say we want to track our client's favorite colors. They will have 5 options: blue, green, red, black, or yellow. We want them to be able to select multiple choices as well as be able to group on them in reporting as well as not have to build multiple sections per option chosen.
Form Creation
Our first item will be to create 2 Tier 2 forms under the Individual Tier 1.
1. Favorite Colors: we will begin by creating this Tier 2, it will only have a Date field, don't forget to publish the form.
2. Favorite Colors (Options): This will also be a Tier 2 form under the Individual Tier 1. It will have 1 field (dropdown) of "Pick a Color" with our 5 options. Don't forget to publish the form
Next, we will want to create a linking field onto our "Favorite Colors" form with the following settings:
Display Name: Pick Favorite Colors
Linked To: Favorite Colors (Options)
Link Relationship: Many to 1 (this will allow them to pick multiple colors but each color only once)
Same Folder Linking: Yes
Wizard Style Linking: Yes
Once this has been created you will need to publish the form one more time. Double check the linking field settings again, make sure they match the screenshot above. If they do not, re-adjust them one more time and publish the form one more time.
Next, we will need to go back to our other form of "Favorite Colors (Options)" to make a couple final adjustments to ensure a proper workflow.
Click the gear for the Form Properties
Enable "Hide from Document Folder"
Apply
Click the Section Properties for "Linked 'Favorite Colors' Records"
Enable "Hidden"
Apply
Finally, we will publish the form and now we have completed the setup portion of this article. Next, we will overview how to use this alternative setup.
Creating Records
Once we are in an Individual's Document Folder we will hover over Actions
Select "Create New"
First we will fill out the date
Next, we will select the New button
Pick a color
Choose "Store Linked Record"
We can see the selected color. If this was a mistake you may click "Edit Record" to update the choice or the X over to the far right to "un-check" that option
To pick another color, click the New button and do the same process to pick a different option this time
Save Record
After it is saved we can see the colors which were chosen (you can even click there to update them if needed)
If you realized you chose one that you shouldn't have just un-check the check under the Active column for the proper choice. Remember to Save Record if you do this.
Building A Report
Building the report will be slightly different than usual since this setup actually uses 2 different forms. Let's say we want to know the First Name, Last Name, Date, and all of the answer options chosen. We will have 3 forms in our report: Individual, Favorite Colors, and Favorite Colors (Options).
We need the First field from the Individual Form
The Last field from the Individual Form
The Date field from the Favorite Colors form
Do not bring in Description, Date Added, or Date Modified from the Pick Favorite Colors. Even though this is the linking field the data is stored in a different form. We don't need these to make a proper report.
Drag in the Pick a Color field from the Favorite Colors (Options) form
In the "Forms" area of the Section click on the form name "Favorite Colors (Options)"
In the Connected To area choose Favorite Colors. This is very important!
Apply (Note: you may need to refresh the browser for the report to update)
Now, once that is all complete we will "Group" by the "Pick a Color" column. Open the column properties and select "Yes" under "Group By This Column"
Finally, publish and run the report.
This report can be modified and filtered further for your reporting needs