2. ASPRunner.NET General questions
  3. [SOLVED]  Get Sum of Column From Different Table
This topic is locked
[SOLVED]

 Get Sum of Column From Different Table

 		12/5/2017 6:34:29 PM
ASPRunner.NET General questions
I
 i.NoLim author

Hello all!
I have two tables dbo.Team and dbo.Individual. I have it set up so Team is the master table and Individual is the details table. Team has a field called "Total" and Individual has a field called "Allowance." Additionally, Team has a "Team_ID" that is inherited by each Individual.
I would like for the "Total" field to be updated every time a new member is added to that specific team and an allowance is entered. How would I go about doing this? I thought about doing a SQL query but I'm not sure if a "After record added" event would be a better solution.
MK Frank R 12/5/2017

I would recommend that you dynamically calculate that sum when you need it with some SQL. But, if you truly want to update a field with the sum, yes, you'd add some DAL code in the after add event.
I
 		i.NoLim author 12/6/2017

I managed to code something in SQL but that's not going to work for me, I need something that updates the Total every time a new "Allowance" is added/modified.

UPDATE T

SET T.TOTAL = I.ALLSUM

FROM TEAM T

INNER JOIN

(

SELECT TEAM_ID, SUM(ALLOWANCE) ALLSUM

FROM INDIVIDUAL I

GROUP BY TEAM_ID

)

A ON T.TEAM_ID = I.TEAM_ID


I'm not even sure how to start the code for the event. How do I look through the database and add only those entries with the same "Team_ID" as the newly entered/modified item?
MK Frank R 12/6/2017

Have you read the DAL section in the manual?
Take a look: Data Access Layer
I
 		i.NoLim author 12/6/2017

I actually used something like that for a previous project and had the hardest time figuring it out, but eventually did.

dynamic tabler = GlobalVars.dal.Table("dbo.Deleted");

tabler.Param["logID"] = keys["logID"];
string strRestore = "INSERT INTO dbo.main(dateOpened, logItem, plant, dept, equipmentID, plantSystem, statusNotes, hazard, openedBy, updatedBy, deleted)SELECT dateOpened, logItem, plant, dept, equipmentID, plantSystem, statusNotes, hazard, openedBy, updatedBy, '0' FROM dbo.Deleted WHERE logID = " + keys["logID"].ToString();

CommonFunctions.db_exec(strRestore, null);
tabler.Delete();
XSession.Session["message"] = "This record was restored";


It seems to me like the code should look something like this:



dynamic tableT = glovalVars.dal.Table("dbo.Team"); //access DB Team

dynamic tableI = GlobalVars.dal.Table("dbo.INDIVIDUAL"); //access DB Individual

tableI.Param["TEAM_ID"] = keys["TEAM_ID"]; //Access all entries with same TEAM_ID?
SUM =[ ]; //Add all values from previous command
tableT.Value["Total"] = "SUM";

tableT.Update();


Edit: The page that you link me to, also mentions "This will be extremely helpful for those who don't know SQL." Does this means that I could use SQL code in the "After Record Event?" Because I tried but got errors.
I
 		i.NoLim author 12/11/2017

I solved this issue by entering my SQL code in the "After record added" and "After record updated" events.

DB.Query("SQL Code");