Jump to content
  • 0
emarchewka

Insert and Update Sums in other Table

Question

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!

 

 

Share this post


Link to post
Share on other sites

2 answers to this question

Recommended Posts

  • 0

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 

Share this post


Link to post
Share on other sites
  • 0

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!

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now


×