Jump to content
  • 0

Consolidate rows via trigger


Benades
 Share

Question

Hi there, I need to create a trigger that sums different column values per user.

I have multiple users on a table (below) and would like to on another table insert sum the number values of each a_user per column and insert it into another table as a combined history.

ie user1: 106 | 60 | 73

user3: 96 | 45 | 71

screenshot-c1dcn529.caspio.com-2021_07.13-10_43_03.png.0fee5c11e0f42d4c84abe25d561d858a.png

Link to comment
Share on other sites

1 answer to this question

Recommended Posts

  • 0
9 hours ago, Benades said:

Hi there, I need to create a trigger that sums different column values per user.

I have multiple users on a table (below) and would like to on another table insert sum the number values of each a_user per column and insert it into another table as a combined history.

ie user1: 106 | 60 | 73

user3: 96 | 45 | 71

screenshot-c1dcn529.caspio.com-2021_07.13-10_43_03.png.0fee5c11e0f42d4c84abe25d561d858a.png

Here's a sample

image.png.de0ce973b8d3d6528f9dcea123f9a781.png

 

image.png.04eaab467b3035d58d34af63923a7e7c.png

Child Table MUST HAVE a unique ID, you'll see why.

 

First Part of the Trigger

This is for inserting to PARENT Table Names that DO NOT EXIST yet on the Parent Table

image.thumb.png.e09b110c6b14c20161b23cb20bafcc80.png

 

1.) Set a VARIABLE,  Select from the Child Table, and Inner Join with the #inserted, or vice versa so we can use both fields in the WHERE.  Important here is to select Expression on the fields, then append the aggregation block (COUNT/SUM, ETC) use WHERE so you'll only SUM the Name in the Child Table that is the same as the #inserted

2.) Select From WHERE NOT EXISTS, select the field you want to check, in this case I want to check the Parent Table's Name Field, and then I'll check for values that are equal to #inserted.Name

If it does not exist, it will proceed with inserting the values I've set

3.) For Total Allowance, I'll check first if it's not a number (because the sum can be a NULL), if it's not, I will output 0, else, the SUM. Then, you'll add the #inserted.Allowance, that's only named Allowance because you're already Selecting From #inserted in the Insert Into Block. Check if it's not blank, if it's not, get the value, else 0.

 

2nd Part

just below the Insert Into Block

 

image.thumb.png.dd3c1e69c527a59d22bd3770e5751fdb.png

 

What this does is UPDATE the values that are in the Parent Table.

1.) Inner Join with #inserted.Name then parent.Name

2.) Use Case When, same concept as the one in the INSERT INTO. In this case, the WHERE checks if the Child.Name that is getting summed up is equal to the Parent.Name. We're also excluding the Child ID that's being updated from that SUM (#inserted.Child_ID not equal to Child.Child_ID) because we're ADDING the NEW VALUE instead

 

This looks complicated, but, it's actually not.

Link to comment
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...
 Share

×
×
  • Create New...