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 23 days ago
    Edited by Venkatesh Gandi 23 days ago
    Hi @Avi Sikenpore, I'm struggling in looping though the each selection & make a child record. Can you elaborate more on how to achieve that.

    ------------------------------
    Venkatesh Gandi
    ------------------------------



  • 5.  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!


  • 6.  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.


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

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



  • 8.  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.


  • 9.  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.


  • 10.  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.


  • 11.  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.


  • 12.  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]});
          }


  • 13.  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?


  • 14.  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.


  • 15.  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?


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

    Posted 05-05-2020 20:50
    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
    ------------------------------



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

    Posted 22 days ago
      |   view attached
    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
    ------------------------------



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

    Posted 21 days ago
    Hi @Jennifer Juhasz, I came to know about the solution , but can't able to implement it. The solution I'm thinking is based on the response in multiselect field, we need to create a child record for each value in the response. I think webhooks is the option for us. I was able to create/ mirroring the response. But I'm unable to do the looping through the each value in the response as mentioned by @Avi Sikenpore above. Hope for this some advanced XML skills is needed.​​

    ------------------------------
    Venkatesh Gandi
    ------------------------------



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

    Posted 21 days ago
    @Venkatesh 

    This is the way that I make a pie chart from a multi select field.
    I create a child table representing each of the multi select choices which the user selected.

    Then set up an automation that when the parent record is edited, to delete all the children and then to create a child record for every possible multi select choice, let's say there are 10 multi select choices so 10 children will be created, then the next step of the automation or pipeline will be to delete any of the children that were not actually selected.

     Then set up an automation that when the parent record is edited, to delete all the children and then to create a child record for every possible multi select choice, let's say there are 10 multi select choices so 10 children will be created, then the next step of the automation or pipeline will be to delete any of the children that were not actually selected. 

     Because the children are children of the parent, they can have a look up field to know which selections were made in the parent and then a formula field can determine for each child that was created whether or not it deserves to remain or needs to be deleted. By having the very first step of the automation delete any children, it ensures  that for any editing we are always starting with a clean slate.

    Once you have the child table then it is very easy to make your pie charts off the child table




    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------



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

    Posted 20 days ago
    Hi @Mark Shnier (YQC), Thanks for the reply, This is a decent solution. How did you achieved this; using Webbooks or Pipelines? Just wanted to check this: Will this works if users will keep editing the ​records periodically?

    Thanks

    ------------------------------
    Venkatesh Gandi
    ------------------------------



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

    Posted 20 days ago
    This can be done with either an Automation or a Pipeline.  Yes it will work fine if users edit records because the first step is to delete any children records so it always starts with a clean slate as soon as the parent record is edited or added.

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------



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

    Posted 20 days ago
    Hi @Mark Shnier (YQC), Thanks for the reply, This is awesome,  let me try from my end. I'm not very good at XML codes to opt Web hooks. I will try with pipelines.

    Thanks!​

    ------------------------------
    Venkatesh Gandi
    ------------------------------