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

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

@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. 

Share this post


Link to post
Share on other sites
  • 0

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

Share this post


Link to post
Share on other sites
  • 0
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.

Capture22.JPG

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...