automatic calculation instead of after update/insert code

[facos79]

Hi everyone, I have a problem: I have a code that works well if inserted in the after update/insert section hook page.
However, on this page the values are updated only when I update the fields.
Can anyone help me convert the code so I can use it directly in automatic calculations?

Code: Select all

$tb = sqlValue("SELECT `Tot_blocchi` FROM `Acquisto_peso` WHERE `id`=" . $data['Totfat']);

$sum = sqlValue("SELECT SUM(`Lordo`) FROM `Movimenti_acquisto_peso` WHERE `Totfat`=" . $data['Totfat']);

$rr = $sum - $tb ;

sql("UPDATE `Movimenti_acquisto_peso` SET `Differenza`='" . $rr . "' WHERE `id`=" . $data['selectedID'], $eo);

[pbottcher]

Hi,

you may try:

Code: Select all

SELECT 
SUM(`Movimenti_acquisto_peso`.`Lordo`) - `Acquisto_peso`.Tot_blocchi` 
FROM `Movimenti_acquisto_peso`, `Movimenti_acquisto_peso` 
WHERE  `Movimenti_acquisto_peso`.`Totfat`= `Movimenti_acquisto_peso`.`Totfat` 
AND  `Acquisto_peso`.`id` = `Movimenti_acquisto_peso`.`Totfat` 
AND `Movimenti_acquisto_peso`.`id` = %ID%

[facos79]

Hi, thanks for the help.
The code doesn't work. It shows no results.
I tried to insert the following code. The formula works. But there is a problem: the formula is applied to all the rows without distinction, however I would like it to be applied to all the rows that have the same value contained in the 'Block' cell
See attached photo.

Code: Select all

SELECT (`Movimenti_acquisto_peso`.`Totfat` - (SELECT SUM(`Movimenti_acquisto_peso`.`Lordo`))) FROM `Movimenti_acquisto_peso` 
WHERE `Movimenti_acquisto_peso`.`id`=%PKFIELD%

[pbottcher]

Hi,

without having the table really visibile it is hard to understand. You may try:

Code: Select all

SELECT SUM(`m2`.`Lordo`)-acquisto_peso.Tot_blocchi 
FROM `Movimenti_acquisto_peso` 
LEFT JOIN Movimenti_acquisto_peso m2 on m2.Totfat = Movimenti_acquisto_peso.Totfat 
LEFT JOIN acquisto_peso on acquisto_peso.id=m2.Totfat 
WHERE `Movimenti_acquisto_peso`.`id`=%ID%