Dashboards That Pop: Survey123 Select Multiple Clean Up
A pretty popular workflow is to use Dashboards to display data collected in Survey123. One issue that frequently trips folks up with this workflow is the use of Select Multiple questions. The results of these questions are stored as a comma separated list of selected options.
These comma separated values are less than ideal when trying to use the field in Dashboards. Luckily, all is not lost. We can easily clean up these values using some Arcade and other tricks. This blog assumes you are familiar with using Arcade and HTML in Dashboards. For an intro you might want to first check out these posts:
Replace()
The Replace() function is useful for turning those choice strings into something a little more human friendly.
The Replace() function has 3 required parameters:
- value: The text in which to make replacements.
- searchText: The text to search for.
- replacementText: The replacement text.
Replace(value, searchText, replacementText)
Replace('the quick brown fox', 'brown', 'red') = the quick red fox
The tricky part in using Replace() to clean up our select multiple fields, is that we have to chain multiple Replace() statements together. While you can do this all at once, I recommend you break it out into multiple steps so you can easily read, change, and troubleshoot it.
The number of Replace() statements you will need depends on the number of options your select multiple question has. You will then need a final one to add some spaces between the options. So, if you have 5 choice options, you will need 6 statements.
Start by creating a variable and use Replace() for your first choice.
var kid = Replace($datapoint.activities, "kid_zone", "Kid's Zone")
Next, do the same thing for your next choice, only instead of using your field as the value, use the variable you just created.
var food = Replace(kid, 'food_trucks', 'Food Trucks')
Repeat for each choice option, ensuring that the value is the variable of the previous statement.
If you were to stop now, you would end up with some nicely formatted choices, but they would all be smooshed together.
We will add one more Replace() statement to create some spaces. Again, the value should be the previous variable.
This time we will replace our comma with a comma space.
var act = Replace(music, ',', ', ')
We can tweak this final statement and use some HTML to force each choice onto a new line.
var act = Replace(music, ',', '<br>')
This final variable is what we will use in our return statement and ultimately display in our element. We can use this method in pop-ups, the List element, and the Table element.
Find()
The Find() function is handy when you want to incorporate some dynamic elements based on the choices selected.
The Find() function has 2 required parameters:
- searchText: The text to search for.
- targetText: The text to search.
If the searchText is found, the function will return the character index where it was found.
Find(‘fox’, 'The quick red fox') = 14
That part isn’t really important for this usecase. The important part is what is returned if the searchText is not found. In that case, -1 is returned.
Find(‘bear’, 'The quick red fox') = -1
So we can combine our Find() function with a When() function to dynamically return certain values.
When(Find('craft_fair', $datapoint.activities) != -1, cficon, '') That is essentially saying, when craft_fair is found in the list of selected activities, provide the craft fair icon, otherwise don’t return anything.
We do this for every choice option.
var cf = When(Find('craft_fair', $datapoint.activities) != -1, cficon, '')
var ms = When(Find('music_stage', $datapoint.activities) != -1, msicon, '')
var ft = When(Find('food_trucks', $datapoint.activities) != -1, fticon, '')
var kz = When(Find('kid_zone', $datapoint.activities, ) != -1, kzicon, '') Instead of returning an icon, this can easily be tweaked to return a color or HTML that is then implemented into your final display. We can use this method in pop-ups, the List element, and the Table element.
Split Choices Data Expression
It takes a little more work to split out the choices into a format that plays nicely with grouped values Tables, Serial Charts, and Pie Charts.
We will need to use a Data Expression reformat the data. Luckily, someone else has already done the hard part for us. The SplitCategories(PieChart) sample expression is available here. While the title specifically calls out Pie Charts, the expression can be used in Serial Charts and Tables as well.
Note that the expression only splits out your choices. It doesn’t clean up those values. You likely will also need to override your categories in each element as well. How you do that depends on the element.
Serial Chart > Category axis > Labels
Pie Chart > Slices
Tables will require using Arcade and Advanced Formatting. However, since the choices have been split out for you, you can use a simple When() statement.
var act = When($datapoint.split_choices == 'kid_zone', "Kid's Zone",
$datapoint.split_choices == 'food_trucks', "Food Trucks",
$datapoint.split_choices == 'craft_fair', "Craft Fair",
$datapoint.split_choices == 'music_stage', "Music Stage", ''
)
Category Selector
To ensure our Category Selector only displays the individual choice options, and filters based on whether or not the option was part of the selected choices, we will need to set up the Category Selector using Defined values.
The Value field should be the choice name, while the Display name can be anything you want.
Then in the Selector tab, ensure that you are using contains as the Operator.
If you do not use contains, the Category Selector will only filter for each choice when that choice was the only option selected.
For more great (if I may toot my own horn) Dashboard tips check out the rest of the series: Dashboards That Pop.
Happy Dashboarding!