Automatic cell update with total table calculations

Discussions related to customizing hooks. Hooks are documented at http://bigprof.com/appgini/help/advanced-topics/hooks/
Post Reply
facos79
Veteran Member
Posts: 115
Joined: 2014-10-29 12:31

Automatic cell update with total table calculations

Post by facos79 » 2019-04-19 13:26

Hi everyone, I have a problem that I have to solve in a short time. Let me explain quickly:
I have 2 tables. In the first there is a list of products and in the last column I did calculate a total multiplying the values present in 2 cells of the same table (using the following code written in the hook). I indicate below the code used in After Insert and in After Update:

Code: Select all

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

		$ESKgTot=sqlValue("SELECT `KgTot` FROM `Elementi` WHERE `id`=".$data['id']);
		$ESCostoProduzione=sqlValue("SELECT `CostoProduzione` FROM `Elementi` WHERE `id`=".$data['id']);
		$ESCostozincatura=sqlValue("SELECT `Costozincatura` FROM `Elementi` WHERE `id`=".$data['id']);
		$ESSupplementoverniciatura=sqlValue("SELECT `Supplementoverniciatura` FROM `Elementi` WHERE `id`=".$data['id']);
		$ESSupplementotriplex=sqlValue("SELECT `Supplementotriplex` FROM `Elementi` WHERE `id`=".$data['id']);

		$ESsomma=$ESCostoProduzione+$ESCostozincatura+$ESSupplementoverniciatura+$ESSupplementotriplex;
		$ESCostoTotale=$ESKgTot*$ESsomma;

		sql("UPDATE `Elementi` SET `CostoTotale`='".$ESCostoTotale."' WHERE `id`=".$data['selectedID'],$eo);

		return TRUE;
Nella seconda tabella utilizzo il valore 'COSTO TOTALE' calcolato nella prima tabella per eseguire altri calcoli tramite codice inserrito nel file hook di riferimento:

Code: Select all

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

$ESSpessore=sqlValue("SELECT `Spessore` FROM `Elementi` WHERE `id`=".$data['Codice']);
$ESPesokgmt=sqlValue("SELECT `Pesokgmt` FROM `Elementi` WHERE `id`=".$data['Codice']);
$ESPesokgCad=sqlValue("SELECT `PesokgCad` FROM `Elementi` WHERE `id`=".$data['Codice']);
$ESNumero=sqlValue("SELECT `Numero` FROM `Elementi` WHERE `id`=".$data['Codice']);
$ESMt=sqlValue("SELECT `Mt` FROM `Elementi` WHERE `id`=".$data['Codice']);
$ESCostoMt=sqlValue("SELECT `CostoMt` FROM `Elementi` WHERE `id`=".$data['Codice']);
$ESKgTot=sqlValue("SELECT `KgTot` FROM `Elementi` WHERE `id`=".$data['Codice']);
$ESCostoProduzione=sqlValue("SELECT `CostoProduzione` FROM `Elementi` WHERE `id`=".$data['Codice']);
$ESCostozincatura=sqlValue("SELECT `Costozincatura` FROM `Elementi` WHERE `id`=".$data['Codice']);
$ESSupplementoverniciatura=sqlValue("SELECT `Supplementoverniciatura` FROM `Elementi` WHERE `id`=".$data['Codice']);
$ESSupplementotriplex=sqlValue("SELECT `Supplementotriplex` FROM `Elementi` WHERE `id`=".$data['Codice']);
$ESCostoTotale=sqlValue("SELECT `CostoTotale` FROM `Elementi` WHERE `id`=".$data['Codice']);

$ESTotelementi=sqlValue("SELECT `Quantita_elementi` FROM `Elementi_Strutture` WHERE `id`=".$data['id']);
		
$rr=$ESKgTot*$ESTotelementi;

sql("UPDATE `Elementi_Strutture` SET `Spessore`='".$ESSpessore."' WHERE `id`=".$data['selectedID'],$eo);
sql("UPDATE `Elementi_Strutture` SET `Pesokgmt`='".$ESPesokgmt."' WHERE `id`=".$data['selectedID'],$eo);
sql("UPDATE `Elementi_Strutture` SET `PesokgCad`='".$ESPesokgCad."' WHERE `id`=".$data['selectedID'],$eo);
sql("UPDATE `Elementi_Strutture` SET `Numero`='".$ESNumero."' WHERE `id`=".$data['selectedID'],$eo);
sql("UPDATE `Elementi_Strutture` SET `Mt`='".$ESMt."' WHERE `id`=".$data['selectedID'],$eo);
sql("UPDATE `Elementi_Strutture` SET `CostoMt`='".$ESCostoMt."' WHERE `id`=".$data['selectedID'],$eo);
sql("UPDATE `Elementi_Strutture` SET `KgTot`='".$ESKgTot."' WHERE `id`=".$data['selectedID'],$eo);
sql("UPDATE `Elementi_Strutture` SET `CostoProduzione`='".$ESCostoProduzione."' WHERE `id`=".$data['selectedID'],$eo);
sql("UPDATE `Elementi_Strutture` SET `Costozincatura`='".$ESCostozincatura."' WHERE `id`=".$data['selectedID'],$eo);
sql("UPDATE `Elementi_Strutture` SET `Supplementoverniciatura`='".$ESSupplementoverniciatura."' WHERE `id`=".$data['selectedID'],$eo);
sql("UPDATE `Elementi_Strutture` SET `Supplementotriplex`='".$ESSupplementotriplex."' WHERE `id`=".$data['selectedID'],$eo);
sql("UPDATE `Elementi_Strutture` SET `CostoTotale`='".$ESCostoTotale."' WHERE `id`=".$data['selectedID'],$eo);

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

$ESPeso_elementi=sqlValue("SELECT `Peso_elementi` FROM `Elementi_Strutture` WHERE `id`=".$data['id']);

$ESCostototelementi=$ESTotelementi*$ESCostoTotale;

sql("UPDATE `Elementi_Strutture` SET `Costototelementi`='".$ESCostototelementi."' WHERE `id`=".$data['selectedID'],$eo);

Now if I update a value in the first table how can I do so that the second table is updated without having to enter each row and click Update?

I can't solve.
Thank you.

Greetings
Attachments
appgini-info.JPG
appgini-info.JPG (116.88 KiB) Viewed 2920 times

facos79
Veteran Member
Posts: 115
Joined: 2014-10-29 12:31

Re: Automatic cell update with total table calculations

Post by facos79 » 2019-04-19 13:28

I forgot: I use APPGINI 5.75

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Automatic cell update with total table calculations

Post by pbottcher » 2019-04-19 19:29

Hi,

not sure why you replicate a lot of data from the Elementi table to the Elementi_Strutture table. You could probably use the AppGini "auto-fill" function for the lookup.

But back to your question. If you want to change the data in the Elementi-Structture table once a record in the Elementi table is changed you would need to do something like this:

Code: Select all

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

		$ESKgTot=sqlValue("SELECT `KgTot` FROM `Elementi` WHERE `id`=".$data['id']);
		$ESCostoProduzione=sqlValue("SELECT `CostoProduzione` FROM `Elementi` WHERE `id`=".$data['id']);
		$ESCostozincatura=sqlValue("SELECT `Costozincatura` FROM `Elementi` WHERE `id`=".$data['id']);
		$ESSupplementoverniciatura=sqlValue("SELECT `Supplementoverniciatura` FROM `Elementi` WHERE `id`=".$data['id']);
		$ESSupplementotriplex=sqlValue("SELECT `Supplementotriplex` FROM `Elementi` WHERE `id`=".$data['id']);

		$ESsomma=$ESCostoProduzione+$ESCostozincatura+$ESSupplementoverniciatura+$ESSupplementotriplex;
		$ESCostoTotale=$ESKgTot*$ESsomma;

		sql("UPDATE `Elementi` SET `CostoTotale`='".$ESCostoTotale."' WHERE `id`=".$data['selectedID'],$eo);

		$ESTotelementi = sqlValue("SELECT `Quantita_elementi` FROM `Elementi_Strutture` WHERE Codice = ".$data['id']);
		$rr=$ESKgTot*$ESTotelementi;
		sql("UPDATE `Elementi_Strutture` SET `Peso_elementi`='".$rr."' WHERE Codice = ".$data['id'].",$eo);

		$ESCostototelementi=$ESTotelementi*$ESCostoTotale;
		sql("UPDATE `Elementi_Strutture` SET `Costototelementi`='".$ESCostototelementi."' WHERE Codice = ".$data['id'].",$eo);
	    ........   ADD THE STATEMENTS TO UPDATE ALL THE OTHER FIELD YOU NEED HERE ...........	
         return TRUE;
}
I believe that you can reference the item in your second table through : WHERE Codice = ".$data['id']
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

facos79
Veteran Member
Posts: 115
Joined: 2014-10-29 12:31

Re: Automatic cell update with total table calculations

Post by facos79 » 2019-04-23 07:34

Hi pböttcher, thanks for the reply. In the day I will try to enter the code. I didn't use the 'auto-fill' function simply because with 'auto-fill' I can't use 'Column Sum'. I have another question: the code you gave me allows me to update the values of the second table when I change them in the first one. Would it be possible to modify them before the change? I have already entered some values and to update them now I should click on every single line and save the changes. Would it also be possible to duplicate only some values (columns) of the first table in a new table? I should only show some read-only columns to a user.
Thank you very much for your support.

facos79
Veteran Member
Posts: 115
Joined: 2014-10-29 12:31

Re: Automatic cell update with total table calculations

Post by facos79 » 2019-04-23 09:21

Hi, I tried to enter the code but the following error appears:
Errore di analisi : errore di sintassi, "UPDATE" imprevisto (T_STRING), attesa "," o ")"

Code: Select all

		$ESKgTot=sqlValue("SELECT `KgTot` FROM `Elementi` WHERE `id`=".$data['id']);
		$ESCostoProduzione=sqlValue("SELECT `CostoProduzione` FROM `Elementi` WHERE `id`=".$data['id']);
		$ESCostozincatura=sqlValue("SELECT `Costozincatura` FROM `Elementi` WHERE `id`=".$data['id']);
		$ESSupplementoverniciatura=sqlValue("SELECT `Supplementoverniciatura` FROM `Elementi` WHERE `id`=".$data['id']);
		$ESSupplementotriplex=sqlValue("SELECT `Supplementotriplex` FROM `Elementi` WHERE `id`=".$data['id']);

		$ESsomma=$ESCostoProduzione+$ESCostozincatura+$ESSupplementoverniciatura+$ESSupplementotriplex;
		$ESCostoTotale=$ESKgTot*$ESsomma;

		sql("UPDATE `Elementi` SET `CostoTotale`='".$ESCostoTotale."' WHERE `id`=".$data['selectedID'],$eo);

		$ESTotelementi = sqlValue("SELECT `Quantita_elementi` FROM `Elementi_Strutture` WHERE Codice = ".$data['id']);
		$rr=$ESKgTot*$ESTotelementi;
		sql("UPDATE `Elementi_Strutture` SET `Peso_elementi`='".$rr."' WHERE Codice = ".$data['id'].",$eo);

		$ESCostototelementi=$ESTotelementi*$ESCostoTotale;
		sql("UPDATE `Elementi_Strutture` SET `Costototelementi`='".$ESCostototelementi."' WHERE Codice = ".$data['id'].",$eo);
		sql("UPDATE `Elementi_Strutture` SET `Spessore`='".$ESSpessore."' WHERE Codice = ".$data['id'].",$eo);
		sql("UPDATE `Elementi_Strutture` SET `Pesokgmt`='".$ESPesokgmt."' WHERE Codice = ".$data['id'].",$eo);
		sql("UPDATE `Elementi_Strutture` SET `PesokgCad`='".$ESPesokgCad."' WHERE Codice = ".$data['id'].",$eo);
		sql("UPDATE `Elementi_Strutture` SET `Numero`='".$ESNumero."' WHERE Codice = ".$data['id'].",$eo);
		sql("UPDATE `Elementi_Strutture` SET `Mt`='".$ESMt."' WHERE Codice = ".$data['id'].",$eo);
		sql("UPDATE `Elementi_Strutture` SET `CostoMt`='".$ESCostoMt."' WHERE Codice = ".$data['id'].",$eo);
		sql("UPDATE `Elementi_Strutture` SET `KgTot`='".$ESKgTot."' WHERE Codice = ".$data['id'].",$eo);
		sql("UPDATE `Elementi_Strutture` SET `CostoProduzione`='".$ESCostoProduzione."' WHERE Codice = ".$data['id'].",$eo);
		sql("UPDATE `Elementi_Strutture` SET `Costozincatura`='".$ESCostozincatura."' WHERE Codice = ".$data['id'].",$eo);
		sql("UPDATE `Elementi_Strutture` SET `Supplementoverniciatura`='".$ESSupplementoverniciatura."' WHERE Codice = ".$data['id'].",$eo);
		sql("UPDATE `Elementi_Strutture` SET `Supplementotriplex`='".$ESSupplementotriplex."' WHERE Codice = ".$data['id'].",$eo);
		sql("UPDATE `Elementi_Strutture` SET `CostoTotale`='".$ESCostoTotale."' WHERE Codice = ".$data['id'].",$eo);

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Automatic cell update with total table calculations

Post by pbottcher » 2019-04-23 10:49

Hi,

I think you need to use

Code: Select all

sql("UPDATE `Elementi_Strutture` SET `Peso_elementi`='".$rr."' WHERE Codice = ".$data['id'],$eo);
instead of

Code: Select all

sql("UPDATE `Elementi_Strutture` SET `Peso_elementi`='".$rr."' WHERE Codice = ".$data['id'].",$eo);
To your questions:
Would it be possible to modify them before the change?
Yes, you can modify whatever values you want. The code is set in the after_insert/after_update. It depends on the changes you try to make, if you should also put some code to the before_insert/before_update functions.
I have already entered some values and to update them now I should click on every single line and save the changes. Would it also be possible to duplicate only some values (columns) of the first table in a new table?
Sure, but I'm not clear what you try to acheive. You could also consider doing some changes directly in the database if it is a one-time update to sync some data.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

facos79
Veteran Member
Posts: 115
Joined: 2014-10-29 12:31

Re: Automatic cell update with total table calculations

Post by facos79 » 2019-04-23 11:36

thank you very much. The code works!
Thank you :)

Post Reply