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.
Please Help - Calculated Field
Re: Please Help - Calculated Field
Did you join master table?
I cannot see the lookup field in details table (foreign key), pointing to master table's primary key
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
AppGini 24.10 Revision 1579 + all AppGini Helper tools
<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 readabilityAppGini 24.10 Revision 1579 + all AppGini Helper tools
Re: Please Help - Calculated Field
master
- id (primary key)
- rate
details
- id (primary key)
- master_id (foreign key, lookup)
- duration
- rate
- fee
Ensure that
Also ensure that
SQL-Statement for calculated column
SQL-Statement for calculated column
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.
- 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,2Also 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%'
details.fee
:
Code: Select all
SELECT duration * master.rate
FROM details INNER JOIN master
ON details.master_id = master.id
WHERE details.id = '%ID%'
Kind regards,
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
AppGini 24.10 Revision 1579 + all AppGini Helper tools
<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 readabilityAppGini 24.10 Revision 1579 + all AppGini Helper tools
Re: Please Help - Calculated Field
Jan, you are absolutely fantastic! It all worked perfectly for me. Thank you so very much for your help ... it is REALLY appreciated.
Re: Please Help - Calculated Field
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
AppGini 24.10 Revision 1579 + all AppGini Helper tools
<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 readabilityAppGini 24.10 Revision 1579 + all AppGini Helper tools