Please Help - Calculated Field

If you're a new user of AppGini, feel free to ask general usage questions, or look for answers here.
Post Reply
ozcpa
Posts: 6
Joined: 2022-01-13 00:57

Please Help - Calculated Field

Post by ozcpa » 2022-01-13 02:32

I am a very recent AppGini user. I have creeted a fairly simple AppGini "client application" and have spent quite a bit of time on researching my issue and making trials, but without any success. The problem seems fairly simple (from a logical viewpoint), but as I have no SQL experience I just can’t solve the problem. I have read the post: https://bigprof.com/appgini/help/advanc ... lculations which seems to refer to the issue that I am having, but I just can't work out the actual SQL "code" that I need to use. I was hoping that someone may be able to help.

The issue is as follows:

DATABASE = CLIENTS
TABLES = MASTER and DETAILS
FIELDS (MASTER) = MasterID, Name, ChargeRate
FIELDS (DETAILS) = DetailsID, Date, Name, JobDetails, JobDuration, ChargeRate, Fee


The process that I want for each record that I add (or modify) in the DETAILS table is as follows:

• Enter Date
• Lookup Name in MASTER table
• Enter JobDetails
• Enter JobDuration
• Lookup ChargeRate in MASTER table
• Calculate Fee and store it in Fee field. This calculation should work by multiplying the JobDuration by the ChargeRate

At the moment, everything works EXCEPT for the Fee calculation which is incorrectly multiplying the JobDuration by the MasterID field (rather than
the value in the ChargeRate field). I know that this is due to my SQL statement being incorrect for the Fee field, but I have absolutely no idea
regarding the proper SQL statement to place there,

I would be extremely grateful if someone would be able to provide the correct SQL statement to me for the Fee field.

Many thanks in advance.

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Please Help - Calculated Field

Post by jsetzer » 2022-01-13 15:19

Did you join master table?
I cannot see the lookup field in details table (foreign key), pointing to master table's primary key
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Please Help - Calculated Field

Post by jsetzer » 2022-01-13 15:33

master
- id (primary key)
- rate

details
- id (primary key)
- master_id (foreign key, lookup)
- duration
- rate
- fee

Ensure that rate, duration and fee are numeric, for example decimal 10,2
Also ensure that fee is readonly and calucated.

SQL-Statement for calculated column details.rate:

Code: Select all

SELECT  master.rate
FROM details INNER JOIN master
ON details.master_id = master.id
WHERE details.id = '%ID%'
SQL-Statement for calculated column details.fee:

Code: Select all

SELECT  duration * master.rate
FROM details INNER JOIN master
ON details.master_id = master.id
WHERE details.id = '%ID%'
Please note that all details will be re-calculated if master rate changes. I guess this is not what you want for older jobs, already finished and billed. You should consider doing that kind of calculation in _after_update hook and only re-calculate fees for current jobs, not for already billed jobs. But that's a business decision and obviously I don't know your business case.
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

ozcpa
Posts: 6
Joined: 2022-01-13 00:57

Re: Please Help - Calculated Field

Post by ozcpa » 2022-01-13 21:58

Jan, you are absolutely fantastic! It all worked perfectly for me. Thank you so very much for your help ... it is REALLY appreciated.

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Please Help - Calculated Field

Post by jsetzer » 2022-01-13 22:11

Great, glad I could help!
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

Post Reply