AppGini Community Forums

Can someone please provide an example query for the new calculated-fields feature that deals with lookup fields in calculations as shown in below advanced topics (using hooks method). I've been trying but without success!
https://bigprof.com/appgini/help/advanc ... lculations

Would also maybe be useful for other demo query examples under a new forum heading? for helping out those of us who struggle, have little knowledge of SQL language, specifically SQL SELECT statement etc.

Cheers,
Ron

Hello

Have a look here, maybe can help.

I have four tables

- dossier_individuel
- gestion_primes
- categories
- rythme_prime

See picture for more details
Regards

Excellent, thank you Moh
I'll give it a go (thanks for showing all the steps/relevant screen shots - makes it very clear to follow).
Cheers, Ron

My pleasure. I also receive a lot of help here on the forum, then if me too I can help, it is an honor for me.

All my best

Not having success yet! Seems to always use 'Integer' value instead of actual lookup value when I try this using just 2 tables:
I'm going to make new DBase with just the 2 tables and post screenshots showing what I've tried....
Ron

This is an example of what I'm trying to resolve:
2 Tables with ORDER table fields 'itemname' as lookup & 'itemprice' as lookup auto fill from PRODUCTS table NO PROBLEM WHEN USING MANUAL ENTRY ITEMPRICE WHEN TRYING TO GET LOOKUP ITEMPRICE IT GETS INTEGER NOT ACTUAL VALUE RESULTS AS SHOWN BELOW: How to amend SQL query to retrieve the actual `itemprice` value from the parent table and use those retrieved values in the calculation formula?

Ron

ok please let me create your tables and see, it seems something missing in your code

here the correct code

SELECT `order`.`qtyitem` * `order`.`manualprice`
FROM `order`
WHERE `order`.`id`='%ID%'

Hi Moh,

I wanted to use lookup for 'itemprice' based on user selection of 'itemname' (in order not to have to put in a 'manualprice') by using the new CALCULATED FIELD' instead of by using the hooks solution found here: http://bigprof.com/appgini/help/advance ... lculations.

I've done lots of trials and errors and have now FOUND A SOLUTION all done under the new CALCULATED FIELD, that is a quicker and easier solution to the hooks method as it does not require any edit to hooks file.

CODE:
SELECT `products`.`itemprice` * `order`.`qtyitem`
FROM `order`
LEFT JOIN `products` ON `products`.`id`=`order`.`itemname`
WHERE `order`.`id`='%ID%'

IMAGES: Thanks for helping me get there.
Cheers, Ron

Hi

OK I understand now, yes it is possible with AppGini 5.81, ok back to you...

If you change the value in lookup calculations it will re culculate previous orders hence it affects the history values. if there is a way to maintain the order details for past entries it will be great

I hope this will be explained, covered and shown in new open source AppGini apps when they are published.
See:
viewtopic.php?f=4&t=3288