AppGini Community Forums

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.

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

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: SQL-Statement for calculated column details.fee: 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.

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

Great, glad I could help!