Hi
I am working on a feature for a product, I have to add a field in a table to calculate age in days. I created a dictionary entry of type duration named it age and marked it as function field. The function definition for the field is given as 'glidefunction:datediff(now(), last_opened);' the now() is a function which returns current date with time and last_opened is a field in the same table which is of type Date. This is calculating the age in ‘--Days --hours --minutes’ format but I need to show the date only in '--Days' format without hours and minutes. It is required to show this field in this format only. I cannot create a client script or other script to just trim the unnecessary part as this field will be used in the reports and dashboards for PA. So, I need to calculate this field in --Days itself.
Note:
- I intent to use only DB functions as other functions will be non performant. The table has millions of records so we cannot calculate manually the age for all records and set using update() or updateMultiple() and this cannot be market as calculated field. These will be non performant.
- type of the column cannot be changed to anything else otherwise the datediff function will return the duration in Date format. So, the column type should be such that the datediff will return in days format and not in the date format. example: if type is selected as integer/DateTime age_duration will be calculated as '1971-03-17 15:08:17' this is calculated as,
( 1970-01-01 00:00:00+<difference between now() and last_opened> ) this is how datediff function is defined but if we select duration field it is showing in --days --hours --minutes format.
this should be only '440Days' OR '440' OR rounded-off to '441' also works
Thanks
