Problem: Calculated fields

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

Problem: Calculated fields

Post by facos79 » 2020-04-01 14:19

Hello, I have a calculation problem. I have to multiply two values in a table.
One of these values is taken by Auto-fill from a second table. The calculation doesn't work.
See attached photo.

Code: Select all

SELECT
`Scadenze_servizi`.`Quantita` * `Scadenze_servizi`.`Costo`
FROM `Scadenze_servizi` 
WHERE `Scadenze_servizi`.`id` = '%ID%'
the 'Costo' field is taken by auto-fill from a table called services
Attachments
1problemacalcolo.jpg
1problemacalcolo.jpg (126.17 KiB) Viewed 2623 times

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

Re: Problem: Calculated fields

Post by facos79 » 2020-04-01 14:32

I also tried using the 'after_update' hooks by taking the value directly from the table without taking as a reference but something is not working well. The value is not calculated

Code: Select all

	$Q=sqlValue("SELECT sum(`Quantita`) FROM `Scadenze_servizi` WHERE `id`=".$data['Quantita']);
	$C=sqlValue("SELECT `Costo_vendita` FROM `Servizi` WHERE `id`=".$data['Codice']);
		
	$Tot=$Q*$C;
	sql("UPDATE `Scadenze_servizi` SET `Guadagno`='".$Tot."' WHERE `id`=".$data['selectedID'],$eo);

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

Re: Problem: Calculated fields

Post by pbottcher » 2020-04-01 16:43

Hi,

assuming you are in the Scadenze_servizi after_update hook.

Try

Code: Select all

$Q=sqlValue("SELECT `Quantita` from `Scadenze_servizi` WHERE `id`=".$data['selectedID']);
$C=sqlValue("SELECT `Costo_vendita` FROM `Servizi` WHERE `id`=".$data['Costo']);

$Tot=$Q*$C;

[code]sqlvalue("UPDATE `Scadenze_servizi` SET `Guadagno`='".$Tot."' WHERE `id`=".$data['selectedID']);
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: Problem: Calculated fields

Post by facos79 » 2020-04-01 18:56

Yes, it works. Thanks.
So you can't do the same thing without using the hooks?

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

Re: Problem: Calculated fields

Post by pbottcher » 2020-04-01 19:16

Where do you want to do that?

If you think about Guadagno as calculated fiel, you could try

Code: Select all

select sc.Quantita*se.Costo_vendita from `Scadenze_servizi` sc left join `Servizi` se on se.id=sc.Costo where sc.id='#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: Problem: Calculated fields

Post by facos79 » 2020-04-01 19:37

yes, I would like to calculate 'Guadagno'.
But the code you gave me doesn't work. Nothing is happening.

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

Re: Problem: Calculated fields

Post by pbottcher » 2020-04-01 20:19

Hi,

can you try the code directly in an sql query (like phpmyadmin) to see if there is a type.
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: Problem: Calculated fields

Post by facos79 » 2020-04-02 15:48

Hello,
I solved the problem. I was wrong to insert the final part of the code: I didn't put the%

Thanks for the support.

Code: Select all

SELECT `Scadenze_servizi`.`Quantita`*`Servizi`.`Costo_vendita` 
FROM `Scadenze_servizi` LEFT JOIN `Servizi` 
ON `Servizi`.`id`=`Scadenze_servizi`.`Costo` 
WHERE `Scadenze_servizi`.`id`='%ID%'

Post Reply