Simple List with Birthdays

[ChrisL]

HI all,

I'm trying to create a simple report with a list of the Contacts Birhtdays.

I would like, that the Report is ordered by the next upcoming Birthdays.

Actually I have the issue, that I can only display the full date of Birth, and if I sort after that, it sorts obviously from the age...

thx for help

[tanya]

have you read https://www.espocrm.com/blog/report-...have-birthday/

https://github.com/espocrm/documentation/blob/master/docs/user-guide/reports.md#or-group

With sorting there is a problem. I think it could be implemented with development
an example, but for float value

https://forum.espocrm.com/forum/general/34663-report-filter-for-comparing-values-of-2-float-fields

you can create not storable field, {currentYear-BirthMonth -BirthDay}

https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html

[ChrisL]

Hi Tanya,

yes, we have read the blog report and already tried to play around with it, unfortunately with now success... (as do not need to see for this case the Birthdays today, tomorrow or in this month - we will need this for a reminder email via a workflow!)

We would only like to have a list, sorted by Birthdays trough they year (it's also ok, if this list always starts with January and not the actual month/day!)
so first field - months - January to December
second sorting field - days, 1-31

We could theoretically achieve this quite easy, if the fields "day" "month" "year" are all single fields, but it would be a shame, not to use date field for that..., you agree?

[tanya]

the third way, I wrote
This is part of custom/Espo/Custom/Resources/metadata/entityDefs/Contact.json fields section
"birthday": {
"notNull": false,
"type": "date",
"required": false,
"isPersonalData": true,
"audited": false,
"readOnly": false,
"tooltip": false,
"isCustom": true
},
"thisYearBirthday": {
"notStorable": true,
"type": "varchar",
"required": false,
"readOnly": true,
"isCustom": true,
"select": "DATE_FORMAT(contact.birthday, '2018-%m-%d')",
"orderBy": "thisYearBirthday {direction}"
},

In contact entity, that's why you see contact in select.

This field is not filterable, but it is sortable.
After checking whether json is valid, Administration > Rebuild

[ChrisL]

just for my understaind, I need to add this code to the contact.json file at the end, to the "fields"? correct?

[tanya]

it hasn't metter, at the end or at the start. It has to be in "fields" section in custom/Espo/Custom/Resources/metadata/entityDefs/Contact.json
If you want, you can share your file and I will show you the needed place

[MicKress]

Hi Tanya, would it be possible to create a notstorable field for age?

[tanya]

Hello,
of course, you can create notStorable field for age.

[MicKress]

So I write „datetime\diff(datetime\today(), birthday, 'years')“ in the select statement in the above code?

[tanya]

if you want to make it notStorable, but with calculated value, better to set calculation in entityDefs in "select"

[MicKress]

Do you mean in contact.json in entityDefs?

[tanya]

yes,
check the post #4 in this topic