Hard-Code Values for Graphs

[Connonymous]

I am putting together some graphs to summarize the results of questions, status of completion, and things like that. In each of these cases, I always know what the possible responses are, and I would like every possible response to be included in the graph, even if the corresponding value is equal to 0. For example, one of the graphs is a pie chart to show completion status. The only possible statuses are "Complete" and "Incomplete," but by default if all participants are "Complete" then the "Incomplete" value does not show up and vice versa. Is it possible to always have both categories included in my graph?

In case it is helpful to have another example, a second graph would be a bar graph of completion status by Department. In this case, it would definitely be helpful to quickly see which departments have 0% completion.

Thank you in advance for any help!

[CoopperBackpack]

Hello @Connonymous,

As far as I understood, you have a table like this (there are no records with an 'Incomplete' status since only records with a 'Complete' status are submitted).

 

If this is correct, the option can be an additional table with the following design:

 

This is the table to store a summary per department and status. The 'Count' field in this table can be populated/updated by a Task or by a Triggered Action. And this table should be used as a data source to create a Chart.

The result example is:

 

As for the Bar Chart, by default, it doesn`t show 0 values:

The reason for that is described in the 'highcharts' documentation 

https://api.highcharts.com/highcharts/plotOptions.bar.minPointLength 

 

It is possible to apply JavaScript code to override the default value.
The Chart example:

Please let me know if you need further assistance with this case. 

[Connonymous]

4 hours ago, CoopperBackpack said:

Hello @Connonymous,

As far as I understood, you have a table like this (there are no records with an 'Incomplete' status since only records with a 'Complete' status are submitted).

 

If this is correct, the option can be an additional table with the following design:

 

This is the table to store a summary per department and status. The 'Count' field in this table can be populated/updated by a Task or by a Triggered Action. And this table should be used as a data source to create a Chart.

The result example is:

 

As for the Bar Chart, by default, it doesn`t show 0 values:

The reason for that is described in the 'highcharts' documentation 

https://api.highcharts.com/highcharts/plotOptions.bar.minPointLength 

 

It is possible to apply JavaScript code to override the default value.
The Chart example:

Please let me know if you need further assistance with this case. 

Thank you, I am following the logic overall, but am running into a couple small issues. First, with the triggered action I set up I am receiving the values that are currently input, rather than the values that are being inserted (this means my data is always 1 entry behind). From my understanding, the way to combat this is to Join the tables and make sure to use the #inserted values rather than the values from the tables themselves. In this case though, I am not sure how to join the tables since the two tables do not have similar setups. Would you be able to share the triggered action setup you used to accomplish filling in your 'Count' column?

The second issue is on the JavaScript for the bar chart. I really have never used JavaScript, so I don't have much of an understanding of it-- sorry! I take it from reading the link you sent that the portion below is the portion of interest to include in my Footer. I expect that I am supposed to update some of the fields here with information specific to my Caspio chart (possibly the 'Highcharts.chart' and/ or the 'container' portions?) but I am not positive which sections would be updated, and how to find the information within my Caspio app that would need to be input. Any chance you can help me with these issues?

<script>
Highcharts.chart('container', {
    plotOptions: {
        series: {
            minPointLength: 3
        }
    }
});
</script>

[CoopperBackpack]

Hello @Connonymous,

Sure, I will try to help. 

First of all, I would like to better understand the logic of the 'count' calculation.
Do you count the number of incomplete depending on the number of employees in the specific department? 
For example, there are 10 employees in the Marketing department and there are 7 records with the 'Complete' status, so we can understand, that 3 employees must complete a survey (3 records with the 'Incomplete' status).

As for the Bar chart, the JavaScript code complexity depends on the Chart configuration. Do you have a Search enabled on the Bar chart? 

[Connonymous]

Thank you!

Yes, basically once the survey has been submitted, it would be marked 'Complete,' and in all other cases it will be market 'Incomplete' so there is no third case to have to worry about. So in the example you gave above, in a department of 10 employees, if 7 have 'Complete' listed, then the other 3 would have 'Incomplete' listed.

No, a search bar would not be needed, but I may have it filter by pre-defined criteria of login credentials and things-- I am not sure if that would matter for the JavaScript.

Please let me know if you have any follow up questions-- I really appreciate the help!

[CoopperBackpack]

Hello @Connonymous,

As for the bar chart and zero values, please add this script to the 'Configure Chart Options' page in the Footer section.

Disable the HTML editor on the Advance tab before pasting the code.

 

<script>
document.addEventListener('DataPageReady', chartUpdateHandler)

function chartUpdateHandler(event) {
    var cleaner = function (interv) {
    clearInterval(interv);
 }

let interv = setInterval(() => {

  var chart = Highcharts.charts[0];
  if (!chart) return;
  chart.update({
         plotOptions: {
                    series: {
                      minPointLength: 5
                  }
         }
  });

  cleaner(interv);

 }, 200);
} 
</script>

You may decrease the 'minPointLength' value (for example, set it to 3). 

In case there is a search enabled, the chart will be updated every time and this scenario requires applying more complex code.

[CoopperBackpack]

As for the Pie chart, it is still unclear to me how the statuses are set.

You mentioned that the status is 'Complete' by default. Does it mean that the Table is populated with the list of employees who should complete a survey and the Status field is set to Complete? How the status can be changed to Incomplete? 
An example of the Table design that stores statuses can be helpful.

[Connonymous]

4 hours ago, CoopperBackpack said:

As for the Pie chart, it is still unclear to me how the statuses are set.

You mentioned that the status is 'Complete' by default. Does it mean that the Table is populated with the list of employees who should complete a survey and the Status field is set to Complete? How the status can be changed to Incomplete? 
An example of the Table design that stores statuses can be helpful.

No, it is the opposite, it is set to 'Incomplete' by default, and only switches to 'Complete' once the submit button has been clicked. Please let me know if an example of the table would still be beneficial, but I am assuming that clears it up.

Really the only thing I am still struggling with in this area is the Triggered Action portion mentioned. It is working how I would like, except that it is showing the pre-updated information, rather than the information after the change. I understand that in order to do that, you need to use the #inserted table, but I am struggling with how to properly do that... every time I try to adjust the tables to reflect the #inserted table I seem to get further away from the correct numbers.

[Connonymous]

6 hours ago, CoopperBackpack said:

Hello @Connonymous,

As for the bar chart and zero values, please add this script to the 'Configure Chart Options' page in the Footer section.

Disable the HTML editor on the Advance tab before pasting the code.

 

<script>
document.addEventListener('DataPageReady', chartUpdateHandler)

function chartUpdateHandler(event) {
    var cleaner = function (interv) {
    clearInterval(interv);
 }

let interv = setInterval(() => {

  var chart = Highcharts.charts[0];
  if (!chart) return;
  chart.update({
         plotOptions: {
                    series: {
                      minPointLength: 5
                  }
         }
  });

  cleaner(interv);

 }, 200);
} 
</script>

You may decrease the 'minPointLength' value (for example, set it to 3). 

In case there is a search enabled, the chart will be updated every time and this scenario requires applying more complex code.

This works perfectly, thank you!

[CoopperBackpack]

Hello @Connonymous,

As for the Triggers, you are correct, you need to take into account the #inserted table.

It is better to use the following approach: 

1) If you already have data in the tables, you may use a Task to populate the additional table with the current data.

For example, I have this table as a main table:

And this one as an additional table to use to create a Chart:

This Task will populate the Count field:

 

2) After that, you may use Triggers to re-calculate the Count field.

Trigger example that works on Insert:


Trigger example on Delete (work for Inline delete, requires a Loop for bulk delete)

 

Trigger example on Update (work for Inline edit, requires a Loop for bulk edit)

[Connonymous]

On 6/23/2023 at 2:07 PM, CoopperBackpack said:

Hello @Connonymous,

As for the Triggers, you are correct, you need to take into account the #inserted table.

It is better to use the following approach: 

1) If you already have data in the tables, you may use a Task to populate the additional table with the current data.

For example, I have this table as a main table:

And this one as an additional table to use to create a Chart:

This Task will populate the Count field:

 

2) After that, you may use Triggers to re-calculate the Count field.

Trigger example that works on Insert:


Trigger example on Delete (work for Inline delete, requires a Loop for bulk delete)

 

Trigger example on Update (work for Inline edit, requires a Loop for bulk edit)

Wow, this works like a dream! Can't thank you enough for taking the time to help me out with this-- really appreciate it!