calculate how many DAYS have passed since a date in a lookup field

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
fgazza
Veteran Member
Posts: 205
Joined: 2019-04-30 17:37

calculate how many DAYS have passed since a date in a lookup field

Post by fgazza » 2019-11-21 19:25

Hi!

I have a table partecipazione_evento_pubblico.
In the table there are a field data_evento and a field giorni_da_evento.
The data_evento field is a lookup field that points to the date fielde data_appuntamento of the table appuntamenti.

Both tables have the primary key name field: pri_key

I would like the giorni_da_evento field to automatically calculate how many DAYS have passed since the date indicated in the data_appuntamenti field.

In calculated field area in appgini i have set this code but it don't work:

SELECT(
DATEDIFF(`data_evento`, NOW())
FROM 'partecipazione_evento_pubblico'
WHERE `pri_key` = '%ID%'

Another question: how do I make the result of the calculated table field update by itself without having to open every single record?

Thanks!

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: calculate how many DAYS have passed since a date in a lookup field

Post by pbottcher » 2019-11-22 14:12

Hi,

try

SELECT(
DATEDIFF(`appuntamenti`.`data_appuntamento`, NOW())
FROM `partecipazione_evento_pubblico` left join `appuntamenti` on `partecipazione_evento_pubblico`.`data_evento` = `appuntamenti`.`pri_key`
WHERE `partecipazione_evento_pubblico`.`pri_key` = '%ID%'
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

fgazza
Veteran Member
Posts: 205
Joined: 2019-04-30 17:37

Re: calculate how many DAYS have passed since a date in a lookup field

Post by fgazza » 2019-11-22 21:43

Thank you so much.
I've tryed it.
I'm sorry but it doesn't work.
Any other suggestions?
Thank you!
Fabiano

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: calculate how many DAYS have passed since a date in a lookup field

Post by pbottcher » 2019-11-23 10:28

Hi,

please try

SELECT
DATEDIFF(`appuntamenti`.`data_appuntamento`, NOW())
FROM `partecipazione_evento_pubblico` left join `appuntamenti` on `partecipazione_evento_pubblico`.`data_evento` = `appuntamenti`.`pri_key`
WHERE `partecipazione_evento_pubblico`.`pri_key` = '%ID%'
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

fgazza
Veteran Member
Posts: 205
Joined: 2019-04-30 17:37

Re: calculate how many DAYS have passed since a date in a lookup field

Post by fgazza » 2019-11-23 18:28

thank you very much!
I hadn't noticed the parenthesis after SELECT!
Now works.
The only problem, even if it's not a very big problem, is the slowness to fill in the calculated field!
Does anyone have any idea if there is a solution?
Thanks!
Fabiano

Post Reply