-
facos79
- Veteran Member
- Posts: 115
- Joined: 2014-10-29 12:31
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 (126.17 KiB) Viewed 2643 times
-
facos79
- Veteran Member
- Posts: 115
- Joined: 2014-10-29 12:31
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
- Posts: 1638
- Joined: 2018-04-01 10:12
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
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
- Posts: 1638
- Joined: 2018-04-01 10:12
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
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
- Posts: 1638
- Joined: 2018-04-01 10:12
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
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%'