Calculated-Fields - Examples AG 5.81

The recommended method of customizing your AppGini-generated application is through hooks. But sometimes you might need to add functionality not accessible through hooks. You can discuss this here.
Post Reply
User avatar
ronwill
Veteran Member
Posts: 228
Joined: 2015-08-08 10:12
Location: Cheltenham UK +Weatherford USA

Calculated-Fields - Examples AG 5.81

Post by ronwill » 2019-10-26 01:03

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
Ron - Gloucestershire, UK: AppGini Pro V 23.15 Rev 1484 - LOVING IT!
Plugins: Mass Update + Search Page Maker + Summary Reports + Calendar + Messages
Bizzworxx: AppGiniHelper + Inline Detail View
Alejandro Landini: To-Do List + MPI + TV Field Editor
Other: Udemy Course

Moh Youba
Veteran Member
Posts: 228
Joined: 2017-03-12 09:31

Re: Calculated-Fields - Examples AG 5.81

Post by Moh Youba » 2019-10-26 19:35

Hello

Have a look here, maybe can help.

I have four tables

- dossier_individuel
- gestion_primes
- categories
- rythme_prime

See picture for more details
allTables.jpg
allTables.jpg (151.05 KiB) Viewed 5911 times
code.jpg
code.jpg (105.8 KiB) Viewed 5911 times
link_1.jpg
link_1.jpg (94.52 KiB) Viewed 5911 times
link_2.jpg
link_2.jpg (90.37 KiB) Viewed 5911 times
link_3.jpg
link_3.jpg (83.15 KiB) Viewed 5911 times
Regards

Moh Youba
Veteran Member
Posts: 228
Joined: 2017-03-12 09:31

Re: Calculated-Fields - Examples AG 5.81

Post by Moh Youba » 2019-10-26 19:35

link_5.jpg
link_5.jpg (112.74 KiB) Viewed 5911 times
link_4.jpg
link_4.jpg (93.86 KiB) Viewed 5911 times

User avatar
ronwill
Veteran Member
Posts: 228
Joined: 2015-08-08 10:12
Location: Cheltenham UK +Weatherford USA

Re: Calculated-Fields - Examples AG 5.81

Post by ronwill » 2019-10-27 12:46

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
Ron - Gloucestershire, UK: AppGini Pro V 23.15 Rev 1484 - LOVING IT!
Plugins: Mass Update + Search Page Maker + Summary Reports + Calendar + Messages
Bizzworxx: AppGiniHelper + Inline Detail View
Alejandro Landini: To-Do List + MPI + TV Field Editor
Other: Udemy Course

Moh Youba
Veteran Member
Posts: 228
Joined: 2017-03-12 09:31

Re: Calculated-Fields - Examples AG 5.81

Post by Moh Youba » 2019-10-27 13:11

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

User avatar
ronwill
Veteran Member
Posts: 228
Joined: 2015-08-08 10:12
Location: Cheltenham UK +Weatherford USA

Re: Calculated-Fields - Examples AG 5.81

Post by ronwill » 2019-10-27 15:46

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
Ron - Gloucestershire, UK: AppGini Pro V 23.15 Rev 1484 - LOVING IT!
Plugins: Mass Update + Search Page Maker + Summary Reports + Calendar + Messages
Bizzworxx: AppGiniHelper + Inline Detail View
Alejandro Landini: To-Do List + MPI + TV Field Editor
Other: Udemy Course

User avatar
ronwill
Veteran Member
Posts: 228
Joined: 2015-08-08 10:12
Location: Cheltenham UK +Weatherford USA

Re: Calculated-Fields - Examples AG 5.81

Post by ronwill » 2019-10-27 18:55

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
image1.jpg
image1.jpg (212.91 KiB) Viewed 5875 times
NO PROBLEM WHEN USING MANUAL ENTRY ITEMPRICE
image2.jpg
image2.jpg (98.25 KiB) Viewed 5875 times
WHEN TRYING TO GET LOOKUP ITEMPRICE IT GETS INTEGER NOT ACTUAL VALUE
image3.jpg
image3.jpg (99.28 KiB) Viewed 5875 times
RESULTS AS SHOWN BELOW:
image4.jpg
image4.jpg (44.85 KiB) Viewed 5875 times
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
Ron - Gloucestershire, UK: AppGini Pro V 23.15 Rev 1484 - LOVING IT!
Plugins: Mass Update + Search Page Maker + Summary Reports + Calendar + Messages
Bizzworxx: AppGiniHelper + Inline Detail View
Alejandro Landini: To-Do List + MPI + TV Field Editor
Other: Udemy Course

Moh Youba
Veteran Member
Posts: 228
Joined: 2017-03-12 09:31

Re: Calculated-Fields - Examples AG 5.81

Post by Moh Youba » 2019-10-27 20:40

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

Moh Youba
Veteran Member
Posts: 228
Joined: 2017-03-12 09:31

Re: Calculated-Fields - Examples AG 5.81

Post by Moh Youba » 2019-10-27 20:54

here the correct code

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

demo.jpg
demo.jpg (143.49 KiB) Viewed 5869 times
order.jpg
order.jpg (60.35 KiB) Viewed 5869 times
Attachments
Demo_Calc_Fields.zip
(2.39 KiB) Downloaded 168 times

User avatar
ronwill
Veteran Member
Posts: 228
Joined: 2015-08-08 10:12
Location: Cheltenham UK +Weatherford USA

Re: Calculated-Fields - Examples AG 5.81

Post by ronwill » 2019-10-27 22:43

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:
solution1.jpg
solution1.jpg (95.54 KiB) Viewed 5864 times
solution2.jpg
solution2.jpg (93.29 KiB) Viewed 5864 times
solution3.jpg
solution3.jpg (34.69 KiB) Viewed 5864 times
Thanks for helping me get there.
Cheers, Ron
Ron - Gloucestershire, UK: AppGini Pro V 23.15 Rev 1484 - LOVING IT!
Plugins: Mass Update + Search Page Maker + Summary Reports + Calendar + Messages
Bizzworxx: AppGiniHelper + Inline Detail View
Alejandro Landini: To-Do List + MPI + TV Field Editor
Other: Udemy Course

Moh Youba
Veteran Member
Posts: 228
Joined: 2017-03-12 09:31

Re: Calculated-Fields - Examples AG 5.81

Post by Moh Youba » 2019-10-28 12:09

Hi

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

lectura
Posts: 28
Joined: 2015-01-10 13:29

Re: Calculated-Fields - Examples AG 5.81

Post by lectura » 2019-11-02 12:41

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

User avatar
ronwill
Veteran Member
Posts: 228
Joined: 2015-08-08 10:12
Location: Cheltenham UK +Weatherford USA

Re: Calculated-Fields - Examples AG 5.81

Post by ronwill » 2019-11-03 22:53

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
Ron - Gloucestershire, UK: AppGini Pro V 23.15 Rev 1484 - LOVING IT!
Plugins: Mass Update + Search Page Maker + Summary Reports + Calendar + Messages
Bizzworxx: AppGiniHelper + Inline Detail View
Alejandro Landini: To-Do List + MPI + TV Field Editor
Other: Udemy Course

Post Reply