AppGini Community Forums

Hello everybody.
As always I hope for the help of those who are more experienced than me and I thank you!
I have three fields:
start_date (date format)
end_date (date format)
updated_end_date (date format)

I would like to create a calculated field with the name "start_end_year" possibly directly in appgini project (and not in the hooks file but if it doesn't make sense to put it in appgini project it's also okay to put it in the hooks file) with a conditional query that:

1 always take the last two numbers of the year from the start_date field
2 add a dash
3 if updated_end_date is empty take the last two numbers of the year of end_date but if instead updated_end_date is not empty take the last two numbers of the year of updated_end_date

Example 1:
start_date = 2020/01/01
end_date = 2021/05/25
updated_end_date = empty
Result: start_end_year = 20-21

Example 2:
start_date = 2020/01/01
end_date = 2021/05/25
updated_end_date = 2022/05/25
Result: start_end_year = 20-22

Thank you very much if you can help me with the code!

Fabiano

Hi Fabiano,

first check the following SQL command with your database tool (like phpMyAdmin or Adminer):
* replace TABLENAME

I have created a table for testing and inserted those two records you have given as testcases.

This should show up with results like this:

If this works for you, you can use the following SQL command in for your calculated fields:
* replace TABLENAME and perhaps replace primary key column name (here: id)

Note that I'm using '%y' (lower case) in MySQL's date_format-function which returns the last two digits, only. If you need the full 4-digit years, just replace '%y' by ''%Y' (upper case).

Please ensure you have configured the correct datatype for your calculated field, for example VARCHAR[5].

You are simply the best Jan!
It works perfectly.
Thank you so much.
Fabiano.

Thank you, Fabiano, I'm happy this solution works fine for you.