Insert and Update Sums in other Table

[emarchewka]

Been struggling with this for a bit and can only get pieces to work.

Before someone says just use aggregation in the reports, that doesn't meet the needs and there are limitations that prevent it from working.

Table A: Metric_ID (unique), Company_ID, Reporting_Period, Score, Score_Max, C_Value, C_Value_Max

Table B: Summary_ID, Company_ID, Reporting_Period, Score_Sum, Score_Max_Sum, C_Value_Sum, C_Value_Max

Sample:

Table A: Metric_ID (unique), Company_ID, Reporting_Period, Score, Score_Max, C_Value, C_Value_Max

MET1, Acme, 1/1/2018, 3, 3, 2, 3

MET2, Acme, 1/1/2018, 2,3,1,2.5

MET3, Acme, 2/1/2018, 3, 3, 2, 3

MET4, Acme, 2/1/2018, 2, 3, 1,2

Based on this data:

Table B should look like this:

Table B: Summary_ID, Company_ID, Reporting_Period, Score_Sum, Score_Max_Sum, C_Value_Sum, C_Value_Max

SUM1, Acme, 1/1/2018, 5, 6, 3, 5.5

SUM2, Acme, 2/1/18, 5, 6, 3, 5

 

The logic is, find the metrics for a company, sum the score by date insert into Table B. Repeat for each field, Score_Max, C_Value, C_Value_Max.

Also, in the event, MET1 is updated, then SUM1 needs to update.

Thanks in advance!

 

 

[Vitalikssssss]

Hi emarchewka,

You can create a Tabular Report Datapage based Table B and add Calculated fields with SQL expression which will sum scores from Table A.

Here is an example of SQL expression to sum values from score table:

SELECT SUM(Score) FROM Table_A WHERE Company_ID = target.[@field:Company_ID] AND Reporting_Period = target.[@field:Reporting_Period]

You can find more information about Calculated field SQL usage in this article.

Regards,

@Vitalikssssss 

[emarchewka]

Thanks @Vitalikssssss 

This doesn't fully produce what I am looking for.  In order to chart the values I need  combined Chart and Table.  The table, to be effective, needs to show aggregate values by Reporting Period.  There are 7 total values that need be be charted and displayed.  With the limitation of 5 aggregation fields in Caspio this causes a problem.  Therefore, I am trying to get the values into another table.

Cheers!

[Randy]

@emarchewka Have you been able to achieve this yet? I'm looking to do the exact same thing and can't seem to get anything to work either. Triggered actions continue to produce a SQL 157 error which Caspio support doesn't seem to be able to decipher. 

[emarchewka]

Hi @Randy

I used one of my expert sessions and they built a really complicated triggered action to move and update the values into another table.

Another thing I did was show some of the values in a combined chart and table and then modify the iframe to show only the chart.  Thereby hiding the table part.

-Edward

[edujobs2k18]

On 2/11/2018 at 5:36 PM, Vitalikssssss said:

Hi emarchewka,

You can create a Tabular Report Datapage based Table B and add Calculated fields with SQL expression which will sum scores from Table A.

Here is an example of SQL expression to sum values from score table:

SELECT SUM(Score) FROM Table_A WHERE Company_ID = target.[@field:Company_ID] AND Reporting_Period = target.[@field:Reporting_Period]

 You can find more information about Calculated field SQL usage in this article.

Regards,

@Vitalikssssss 

I was trying to do the same with my table. Where i wanted to count Number of candidate_Placement_Status. But its giving invalid formula.

[Vitalikssssss]

Hi @edujobs2k18,

I believe that you are not able to validate your expression because of your field name under SUM function.

Please note that you should use the name of the field from your table "Student_Master" and be careful since the system is case-sensitive.

Hope this helps.

Regards,

vitalikssssss  

[bitcoinmax]

On 7/31/2018 at 8:09 AM, emarchewka said:

Hi @Randy

I used one of my expert sessions and they built a really complicated triggered action to move and update the values into another table.

Another thing I did was show some of the values in a combined chart and table and then modify the iframe to show only the chart.  Thereby hiding the table part.

-Edward

Can you post the screenshot of how the triggered action was set up?

[cheonsa]

Hello!

Just wanted to share with you this solution using Triggered Actions. User_ID in TBL_A is connected with FK_User_ID in TBL_B. Every time a record is inserted/ updated in TBL_A, the Total_Allowance in TBL_A is updated as well.

 

 

 

Hope this helps!