Calculated fields & Children tables

If you're a new user of AppGini, feel free to ask general usage questions, or look for answers here.
Post Reply
mysh_eireannach
Veteran Member
Posts: 35
Joined: 2016-02-16 22:31

Calculated fields & Children tables

Post by mysh_eireannach » 2020-08-20 15:12

Hi All

I need some advice. I have in my app table "Employee" with some Children tables...

One of Children tables called "Trainings" where I can add any amount of trainings.

For field "Cert status" I used Calculated fields option with code below:

Code: Select all

SELECT
IF( DATEDIFF(`valid_until`, NOW()) >= 60,
'<span class="text-success">Valid</span>',

IF( DATEDIFF(`valid_until`, NOW()) >= 0,
'<span class="text-warning">Expiring</span>', '<span class="text-danger">Expired</span>'
)
)
FROM `trainings`
WHERE `id` = '%ID%'
All works fine, just one problem, because Children table called "Trainings" not reloaded every visit to Employee Detailed view, Calculated fields not recalculated at field "Cert status" at Children tables.

Looks like I need add something for force to recalculated all fields at Children tables.

If I manually enter into each record, then all updated data will appear at field "Cert status".


Print Screen for better understanding.

Screenshot 2020-08-20 at 15.43.51.png
Screenshot 2020-08-20 at 15.43.51.png (214.36 KiB) Viewed 1081 times

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

Re: Calculated fields & Children tables

Post by jsetzer » 2020-09-03 11:43

Calculated fields are fine, but, from my experience, if you want to update all records or a many records, I recommend executing SQL update statements in TABLENAME_init() function instead of calculated fields for integrity and performance reasons.

That's the way I do it in cases like this. The good old bullet-proof way.

Excample

Code: Select all

function calculations_init(&$options, $memberInfo, &$args)
{
	sql("UPDATE calculations SET label=concat_ws(': ', code, name) WHERE label IS NULL", $eo);
	return TRUE;
}
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