take two numbers of year in appgini project with calculated field

Got something cool to share with AppGini users? Feel free to post it here!
Post Reply
fgazza
Veteran Member
Posts: 205
Joined: 2019-04-30 17:37

take two numbers of year in appgini project with calculated field

Post by fgazza » 2020-10-20 07:47

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

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

Re: take two numbers of year in appgini project with calculated field

Post by jsetzer » 2020-10-20 08:16

Hi Fabiano,

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`
* 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:
chrome_4qlfzDSy3z.png
chrome_4qlfzDSy3z.png (4.74 KiB) Viewed 1173 times

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%'
* 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].
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

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

Re: take two numbers of year in appgini project with calculated field

Post by fgazza » 2020-10-20 15:35

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

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

Re: take two numbers of year in appgini project with calculated field

Post by jsetzer » 2020-10-20 16:31

Thank you, Fabiano, I'm happy this solution works fine for you. :D
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