Discussions

Expand all | Collapse all

Separate Multi-Select Text Fields on a Pie Chart

Chris Newsome10-17-2018 12:03

  • 1.  Separate Multi-Select Text Fields on a Pie Chart

    Posted 10-16-2018 13:58
    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?


  • 2.  RE: Separate Multi-Select Text Fields on a Pie Chart

    Posted 10-16-2018 14:50
    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.




  • 3.  RE: Separate Multi-Select Text Fields on a Pie Chart

    Posted 10-17-2018 18:40
    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. 


  • 4.  RE: Separate Multi-Select Text Fields on a Pie Chart

    Posted 10-16-2018 15:42
    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!


  • 5.  RE: Separate Multi-Select Text Fields on a Pie Chart

    Posted 10-16-2018 18:46
    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...">https://community.quickbase.com/quickbase/topics/trying-to-count-the-number-of-selections-in-a-multi-select-text-field">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.


  • 6.  RE: Separate Multi-Select Text Fields on a Pie Chart

    Posted 10-17-2018 12:03
    So exactly where would I input this formula?



  • 7.  RE: Separate Multi-Select Text Fields on a Pie Chart

    Posted 10-17-2018 12:11
    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.


  • 8.  RE: Separate Multi-Select Text Fields on a Pie Chart

    Posted 10-17-2018 15:36
    each record can only appear in one slice of the pie chart"

    I did not know that. Thank you.


  • 9.  RE: Separate Multi-Select Text Fields on a Pie Chart

    Posted 10-17-2018 15:38
    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.


  • 10.  RE: Separate Multi-Select Text Fields on a Pie Chart

    Posted 10-18-2018 16:53
    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.


  • 11.  RE: Separate Multi-Select Text Fields on a Pie Chart

    Posted 10-19-2018 01:48
    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]});
          }


  • 12.  RE: Separate Multi-Select Text Fields on a Pie Chart

    Posted 10-19-2018 13:09
    So I'm a noob at these things, where do i input this formula? How do I make this chart?


  • 13.  RE: Separate Multi-Select Text Fields on a Pie Chart

    Posted 10-19-2018 15:29
    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.


  • 14.  RE: Separate Multi-Select Text Fields on a Pie Chart

    Posted 04-05-2019 14:04
    Where do you place that IOL formulated attribute so that the DIV is inserted properly on a dashboard? Inside of another report?