Page 1 of 1

Help with SUM calculation in a different table

Posted: 2018-03-18 05:44
by bruceholt
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

Re: Help with SUM calculation in a different table

Posted: 2018-03-24 17:29
by xbox2007
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

Re: Help with SUM calculation in a different table

Posted: 2018-03-24 22:18
by bruceholt
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.

Re: Help with SUM calculation in a different table

Posted: 2018-03-25 08:15
by xbox2007
"SELECT steel_weight FROM tablename where ID='{$data['project_steel_weight']}'"

Re: Help with SUM calculation in a different table

Posted: 2018-03-25 08:17
by xbox2007
you need sum all total wight in child table , then update to main table ,,
what is your tables name , and field name ???

Re: Help with SUM calculation in a different table

Posted: 2018-03-26 08:01
by bruceholt
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".

Re: Help with SUM calculation in a different table

Posted: 2018-03-30 14:08
by R Tammam
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.

Re: Help with SUM calculation in a different table

Posted: 2018-03-31 08:56
by bruceholt
Thanks for help. I eventually got it to work. Not sure how though : :?

Re: Help with SUM calculation in a different table

Posted: 2018-04-16 21:45
by Moh Youba
Hello
Can you please share your solution to help. I have the same problem.

Thank you

Re: Help with SUM calculation in a different table

Posted: 2018-10-01 15:03
by lectura
You can use jquery in hooks. instead of using directly on pages.
prepare ajax file, for sum and subtotal which will be generated when you input data. the lesson in udemy can solve the problem