Separate Multi-Select Text Fields on a Pie Chart

  • 0
  • 1
  • Question
  • Updated 4 weeks ago
  • In Progress
OK QBase power users, here's my situation:

We are a manufacturing company that also installs our product. I have created a table to track what we call "Loose Ends" - these are the situations where something goes wrong, such as a part is made incorrectly, it's damaged in some way, etc. I created a field called "Cause of LE" and set it as "Multi-Select Text", as sometimes the issue is caused by more than one issue.

Where I'm struggling is the reporting - I created a pie chart to see "Cause of LEs Year to Date" (sorted by %) and every time more than one option is selected in the "Cause of LE" field, the pie has a new slice added. What I'd like to have happen is for the options selected to be allocated a percent of that issue - i.e., if two selections are made, then those selections are each given 1/2 of that entry. This would make the pie chart only show those causes I have created, and not a bunch of small, combined sections. Is this even possible?
Photo of moleman108

moleman108

  • 458 Points 250 badge 2x thumb
  • Thankful

Posted 1 month ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
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.


Photo of Avi Sikenpore

Avi Sikenpore

  • 532 Points 500 badge 2x thumb
Mark's approach is definitely what I would recommend. Make sure your multi select choices are coming from a table and not a field dropdown so you can maintain them easily. Secondly The automation should get triggered whenever that field is changed in the modified record option which cycles through and creates a child record  for each of the selected item. You can add a step to first delete all existing child records and then add the ones in the multi select checkbox to avoid any duplication. Feel free to reach out if you need help setting up the automation - they are fairly intuitive. Obviously you can use middleware like Workato or Zapier to accomplish it as well. 
Photo of moleman108

moleman108

  • 458 Points 250 badge 2x thumb
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.
(Edited)
Photo of moleman108

moleman108

  • 458 Points 250 badge 2x thumb
So exactly where would I input this formula?

Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,256 Points 50k badge 2x thumb
I think that the flaw in Alex approach is that while you would be able to make a summary report (which may be good enough for your purposes) you not be able to make a pie chart as each record can only appear in one slice of the pie chart.

But you would be able to make a summary report, so maybe tackle that "easier" approach first and see if its good enough.
"each record can only appear in one slice of the pie chart"

I did not know that. Thank you.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,256 Points 50k badge 2x thumb
Right,  but on a summary report you can choose to summarize multiple fields, but I think there is a limit to the number of fields allowed to summarize.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,522 Points 20k badge 2x thumb
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.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,522 Points 20k badge 2x thumb
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]});
      }
(Edited)
Photo of moleman108

moleman108

  • 458 Points 250 badge 2x thumb
So I'm a noob at these things, where do i input this formula? How do I make this chart?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,346 Points 50k badge 2x thumb
If you want Dan's solution  you will need to contract with Dan to implement it for you.  It's an advanced technique.

My summary report solution is the low tech solution that will give you the results, though not in a pie chart.

Alternatively I offered up a solution to make the chart which is native techniques, but you will need to learn about Automations.