Report on next birthdays in 30 days

[chrismc]

Hi, first of: I love Espo! its such a clean design and I can customize such a lot of things with only view technically know-how.

My question:
- for each contact we have configured a birtday date field ("contactBirthday")
- we want to generate reports about the upcoming birthdays (we have the Advanved Pack)
- my idea: have a field with the date of the next upcoming birthday for a contact ("contactBirthdayNextdate", hidden from user interface)
- what is the best way to regularly recalculate those upcoming birthdays? Is it a process, workflow, calculated field? I want it to be scheduled recalculation

Any other ideas how to implement this?

Thanks
Christoph

[eymen.elkum]

this can be done in several ways, but I prefer to make it from the Contact.json entityDefs

just add a new field entry for 'contactBirthdayNextdate' and mar

"fields" : {
	"contactBirthdayNextdate": {
		"type": "date",
		"notStorable": true,
		"select": "DATE_ADD(contacts.contactBirthday, INTERVAL 1 YEAR)"
	}
}

please note I not tested this, but think it must work

[chrismc]

Thanks! I will try it out, just one question: how can this work is e.g. someone is born 12. December 1980 and INTERVAL 1 YEAR will add one year its 1981? What I want is 12. December 2019 as the next upcoming birthday.

[yuri]

Hi Christoph,

You can create a scheduled workflow with Update Target Record action with formula:

$bd = entity\attribute('contactBirthday');
$today = datetime\today();
$diffY = datetime\diff($today, $bd, 'years');
$nextBd = datetime\addYears($bd, $diffY + 1);
entity\setAttribute('contactBirthdayNextdate', $nextBd);

[chrismc]

Amazing, thank you very much! Works perfect!

[chrismc]

Just for the archive if someone searches for it:
To also include todays birthdays I added:
$today = datetime\addDays($today, -1);