Help with SUM calculation in a different table

Discussions related to customizing hooks. Hooks are documented at http://bigprof.com/appgini/help/advanced-topics/hooks/
Post Reply
bruceholt
Posts: 17
Joined: 2016-07-30 20:16

Help with SUM calculation in a different table

Post by bruceholt » 2018-03-18 05:44

Hi all,

I am using Version 5.62

I have 4 tables. The first is projects, 2nd is steel_types, 3rd is steel_sizes and the 4th is project_items. I have added the following to the project_items hooks file to calculate the sub total of the steel each line.

Code: Select all

function project_items_before_update(&$data, $memberInfo, &$args){

		$project_steel_weight = sqlValue(
		"SELECT steel_weight FROM steel_sizes where ID='{$data['project_steel_weight']}'"
		);
		$data['project_line_weight'] = $project_steel_weight * $data['project_line_quantity'];
	
		return TRUE;
	}
I am trying to display the sum of the "Sub total weight kg" column in the total_weight of the projects table but after spending hours (now running into days), I am needing a little help if possible.

Image

xbox2007
Posts: 25
Joined: 2016-12-16 16:49

Re: Help with SUM calculation in a different table

Post by xbox2007 » 2018-03-24 17:29

i think there are wrong with your code

"SELECT steel_weight FROM steel_sizes where ID='{$data['project_steel_weight']}'"

from steel_sizes ??????

project_line_quantity ?????

i think first step you should find Sub Total weight for each row


then
make sum for Sub Total weight
then Update

total of (Sub Total weight) to Project table

bruceholt
Posts: 17
Joined: 2016-07-30 20:16

Re: Help with SUM calculation in a different table

Post by bruceholt » 2018-03-24 22:18

Thanks xbox. I have the sub totals working. The "SELECT steel_weight FROM steel_sizes where ID='{$data['project_steel_weight']}'" picks up the steel weight from another table and multiplies it by the quantity.

The problem I have is getting the sum of the "Sub total weight" to display in "Total weight" in the"Project" table.

xbox2007
Posts: 25
Joined: 2016-12-16 16:49

Re: Help with SUM calculation in a different table

Post by xbox2007 » 2018-03-25 08:15

"SELECT steel_weight FROM tablename where ID='{$data['project_steel_weight']}'"

xbox2007
Posts: 25
Joined: 2016-12-16 16:49

Re: Help with SUM calculation in a different table

Post by xbox2007 » 2018-03-25 08:17

you need sum all total wight in child table , then update to main table ,,
what is your tables name , and field name ???

bruceholt
Posts: 17
Joined: 2016-07-30 20:16

Re: Help with SUM calculation in a different table

Post by bruceholt » 2018-03-26 08:01

The child table name is "project_items" and the field name that I need to display the sum of in the parent table is "project_line_quantity".

R Tammam
Veteran Member
Posts: 113
Joined: 2017-08-26 15:35

Re: Help with SUM calculation in a different table

Post by R Tammam » 2018-03-30 14:08

Hello bruceholt ,

i think you will need to update the pojects table (parent table ) after updating project items
you will use after update function in project_items
1- select the sum of subtotal in project_items
2- use update query to update total field with the value you selected in step 1 (the sum of subtotal in project_items )

i hope it would help you

Regards
Reham.

bruceholt
Posts: 17
Joined: 2016-07-30 20:16

Re: Help with SUM calculation in a different table

Post by bruceholt » 2018-03-31 08:56

Thanks for help. I eventually got it to work. Not sure how though : :?

Moh Youba
Posts: 15
Joined: 2017-03-12 09:31

Re: Help with SUM calculation in a different table

Post by Moh Youba » 2018-04-16 21:45

Hello
Can you please share your solution to help. I have the same problem.

Thank you

Post Reply