Separate Multi-Select Text Fields on a Pie Chart

The only way i can picture doing this would be to have an Automation which would create individual child records when the Multi select record is saved.  it would need to create a child for every one of the selections, and then based on a relationship to the parent multi select field total up the # of children, and then look that up down to the children so each could calculate what weighting to receive, say 1/3 or 1/3rd.

You would need to start off the automation by deleting all its children for that multi select Parent in case the user edits the multi select, ie delete the children, if any, and then re-create the children.

hmm... that's an interesting solution. I may be out of my league on this one. I haven't used automations yet. I'll explore it though!

I feel like you can come up with a solution without going that far. If you're willing to forego multi-select, anyway. Create multiple 'cause of LE' fields (set form rules to only display 2nd cause if 1st cause is populated, etc). Create a (hidden) formula field that's an inverse of the count of non-empty cause fields. Then have your reporting sum the weights by matching cause. Would that work?

Not sure if there's a way to keep the multi-select field and parse out the individual selections into a formula field, I've avoided working with multi-select fields for similar reasons... but I imagine not, as if there was, I guess Mark would have suggested it.

Or, if there are a fairly manageable number of different causes, you could go another route and create formula checkbox fields for each cause, using CONTAINS against the multi-select field (does CONTAINS(ToText([multi-select])) work?) to check them, another field to invert the count of the checked causes, and then sum those weights in reporting.

EDIT: Bless the 'Related Conversations', Mark has already written a formula to part out multi-select fields: https://community.quickbase.com/quickbase/topics/trying-to-count-the-number-of-selections-in-a-multi...
So you don't need to know the names of the causes to do it, then - just the maximum number of causes that might be selected. If it's, say, 5, create 5 fields to use that Part formula to store the individual causes selected, another field to invert the count of the non empty of those, and Bob's your uncle.

So exactly where would I input this formula?

Those Multi-Select Text Fields are display in what designers sometimes call "pills" because with rounded corners they look like a pharmaceutical pill. So I created a demo using the top ten pharmaceutical pills as my multi-select values.

I have a working demo that looks like this:



I have a little cleanup to do that I will complete over the weekend and post.

Here is the demo:

Parents ~ List All
https://haversineconsulting.quickbase.com/db/bn4cs5qyy?a=dr&rid=1

Pastie Database
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=677

I would have liked to spend a little more time refining this as I think it could be simplified but I am short on time and wanted to get this off my todo list.

Notes:

(1) This demo builds the chart from scratch using the following HighChart example:

Pie Chart Basic
https://www.highcharts.com/demo/pie-basic

I made minimal changes to the HighChart example but every aspect of the chart can be modified to suit your needs.

(2) This demo loads the HighChart library using the requirejs library that QuickBase uses to load many of their libraries:

  require(["highcharts"], function(Highcharts) {
    // reference to HighChart object 
  });

(3) These statements in the code successively transform the semi-colon separated records containing the pill values into the object needed by HighCharts to specify the pie chart data:

      var data1 = qdb_data.reduce(function(result, item) {
        result.push(...item[0].split(" ; "));
        return result;
      }, []); 
      var data2 = {};
      data1.forEach(function(val, index) {
        data2[val] = data2[val] + 1 || 1;
      }); 
      var data3 = [];
      for (var key in data2) {
        data3.push({name: key, y: data2[key]});
      } 

I am not a QB pro, although I aspire to be one. I found a workaround for me that didn't require anything too fancy. I created a report and used the Dynamic Filter feature. Here is my situation: I have a table for Activities. One of the fields is mult-select text called Reporting Area(s) Completed During Activity. There are four areas possible and those are the four response options, and more than one can be selected. The four responses are Correspondence, Meeting, Presentation, Special Event. Let's say I only want to see how many meetings were conducted. Since I created a table report and added Reporting Area(s) Completed During Activity as a Dynamic Filter, all I have to do is select Meeting from the options and it will only show all the records where Meeting was selected. The total is displayed above the table. It's not perfect because I want to set up email notifications that have all of this data in the email, but it's the easiest way to count individual responses. 


------------------------------
Steve Hanson
------------------------------

I posted an almost identical request for this help a month ago, but never received any replies.  So, super grateful for this thread!

I ended up giving up on my goal of displaying my data in a pie chart all-together - the summary report options just didn't work for me and as I'd had no response from the community with suggestions on how to achieve that, I ended up opting for a different route; and additionally stopped using Multi-select text fields as an option in really any of my forms.

Instead, I broke apart my 'multi-select' field into 'check box' fields (depending on the role of the user, you have 4-10 options to select from).  Then ran a pipeline to populate the answers based on what had been previously selected by our users in the multi-select tool.

Even after breaking it apart, I still couldn't run a pie chart because Quickbase (unlike something like Excel) can't run this type of report comparing column data.  Instead, I now use a Horizontal Stacked Bar Chart, which gives me a visual representation/comparison of values based on our communities.
It IS nice in that I can hover over a value and see an immediate visual representation of it across our communities; but still a little disappointing because I can't get that lovely, singular pie chart that displays the individual values in a comparative way... 

Hopeful that there's a better solution for not-super-advanced users like myself somewhere down the line - but in the meantime, I hope this maybe helps?

------------------------------
Jennifer Juhasz
------------------------------