How do you view comma separated strings as aggreggated values in a chart?

[ubar]

I have a simple app where a user can input one or more colors using an EnumList. In pie graph and table format, I get visuals that look like these:

How can I create a visualization that parses [Color(s)] and counts the number of times each value between a separator appears in a row?

Using the above graph as an example, my desired visual would not include the sections labelled Orange,Red and Red,Blue. Instead, it would display a graph consisting of 3 blue, 2 orange, 5 red, and 1 yellow. Do I need to create a separate pivot table and graph that, or is there an Appsheet function that will parse and count comma separated values?

[Marc_Dillon]

You don't. Multiple values in a single column is not a good data design practice, and does not work well with charting, amongst other things. You need to split it out to separate records.

[Mitchos]

Hi Marc,

I agree in principle and historically I have followed that data design pattern closely.

However I find that AppSheet is pushing us towards EnumLists for multi-select dropdowns. More recently, especially with the search functionality in a dropdown selection boxes, applications like Monday.com allow you to select numerous values in one column/field and then have it presented back with a default of tokenising into individual items (or storing them that way in the first place, I'm not sure). 

My use case for example, is staff shift's that people can do multiple things during. The number of unique things people do will hit a saturation point at some point in time.

The way I've set it up is to have a table of activities and then an EnumList column on the shifts table referencing that activities table. This allows people to easily search for, select or add multiple activities that were completed in the shift.

I've only been using AppSheet for a few weeks, so I may not know, but the alternative looks to be having a child table with reference's and then having to do multiple adds for each activity (I like that for adding photo's for example and tagging info about the photo, but not for encouraging high volumes of quick hit topics of what people have been up to)?

The add refs, is much clunkier than an EnumList column in terms of clicks and speed and I will (I would think) quickly run out of storage space storing a whole row for each activity done in each shift.

If there is a concept that Ubar has here, or my use case, given modern apps and advancement in text parsing, could we challenge or view the data design practices with a different lens?

I'd be interested to know how Monday.com do it so seamlessly (including fast filtering and charting of live data) and if AppSheet would be able to be uplifted to match.
So that if people do choose or feel the need to redefine or fly in the face of data practices then the option is there.

[Marc_Dillon]

My initial response here I guess was more of a literal response to the exact question. On a second read, the solution for both OP and you would be to create a table for the individual enumlist options (which you already have, OP maybe not). Then in that table, add a Virtual column to count the number of records, of the table with the enumlist, that include the value in the enumlist selection. With something like:

COUNT( FILTER( table , IN( [_THISROW].[col] , [enumlist] ) ) )

Then you could create your charting view on this table, charting the new VC.

----

I do agree with you somewhat, an EnumList can be an appropriate choice for very simple multi-select solutions, and more performant than separate records. In some situations, you can even present an EnumList to the user, but create the separate records in the background.

---