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
take two numbers of year in appgini project with calculated field
Re: take two numbers of year in appgini project with calculated field
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].
first check the following SQL command with your database tool (like phpMyAdmin or Adminer):
Code: Select all
SELECT *,
concat_ws('-',
DATE_FORMAT(start_date, '%y'),
DATE_FORMAT(ifnull(updated_end_date, end_date), '%y')) as 'calculated_years'
FROM `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:
Code: Select all
SELECT concat_ws('-', DATE_FORMAT(start_date, '%y'), DATE_FORMAT(ifnull(updated_end_date, end_date), '%y'))
FROM `TABLENAME` WHERE `id`='%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].
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: take two numbers of year in appgini project with calculated field
You are simply the best Jan!
It works perfectly.
Thank you so much.
Fabiano.
It works perfectly.
Thank you so much.
Fabiano.
Re: take two numbers of year in appgini project with calculated field
Thank you, Fabiano, I'm happy this solution works fine for you.
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