Calculate Age as Whole Number using Power Automate
This problem has annoyed me for a couple of years. I’ve used several methods over the years to calculate age in D365 from Business Rules, Workflows, to JavaScript and no one solution was ever good enough for me. I didn’t like decimal numbers, I didn’t like massive waiting workflows, I just never figured out a solution I liked...until now. I’m so happy to have finally cracked this problem in a way that is sustainable, works in the real world and easy to replicate.
The Problem
Dynamics 365 CE has been around for over a decade and in that time, Microsoft have not included a way to calculate age by default. I’ve never quite understood this as many companies like to wish people happy birthday and collect date of birth as information. There are several ways to calculate age but none of them ever seemed like a great solution to me.
You could use Business Rules because they include a datediff and UTCnow functions but, this rounds up to the month, so even if it’s not the person's birthday, it rounds it up.
JavaScript can do a better job, but you need something to trigger it, like opening the record, or some kind of update. It’s also the same for plugins, as it would need some sort of trigger.
I’ve looked into using workflows, I’ve never tried to have a waiting workflow for 365 days, but I doubt it would work, plus if you have tens of thousands of records or more, it would just not be sustainable.
The Solution
Power Automate is a great tool, with the ability to do lots more than Classic CDS/D365 Workflows. One of it’s advantages is a proper scheduling engine, something we can take advantage of and run this Flow daily. This solves one problem.
Next, we want to be able to list records where it’s the person's birthday today. The CDS connector has a List Records option and you can query the ones you want to return; so this will solve another problem, which is that we don’t want to return tens of thousands of records each time we run this, instead we just get the 2 or 3 we need to update. However, this does actually lead us onto a secondary problem, how do we return records that have a matching day and month, but not year using odata? The answer, we don’t.
The mini solution
We need a way to return records where just the day and the month match in the Date of Birth field, to today's date, but when you work with date fields, it’s not something you can really specify. You can specify a date range, a specific date but not where just day and month match. Instead, what we will do is convert the date of birth into a string field and then take away the year.
But why? Well, with string fields, you can query records which “Equal” that string, so when we query the records, we can return just the ones where the birthday is today. If we tried to compare the birth date to today's date, it would not match because the year would be incorrect. Create a field which is of type string in your CDS instance and then we are going to create a Power Automate Flow that runs on Create of the record or update of the Date of Birth Field.
It’s important to note the create or update action is only available in the CDS Current Environment connector for Power Automate which can only be accessed inside a solution. It should looks something like this.
Next, create a compose action and in this step, we are going to add an expression which will convert the date of birth into a string and remove the year part.
The expression we want to use for this is:
string(formatDateTime(triggerOutputs()?['body/matt_dateofbirth'],'dd/MM'))
The above formula converts the Date and Time of the date of birth field from UTC to just be day and month, then the “String” function converts that output to a string. Then we just update that record with another step.
And that’s part 1 of the solution, now onto the fun bit.
The Main Solution
We need to create a new Power Automate Flow which is a recurrence Flow which runs once a day.
Then we have a step which gets today's date and formats it into just the day and month. We can put this into a compose action and create an expression.
formatDateTime(utcNow(),'dd/MM')
The above formula just formats the UTC now time and date. If you work in a drastically different timezone, you may need to do some adjusting at this point.
Next we want to query our database to return all records where the Date of Birth String field we populated in the first Flow, meet today's day and month.
The query is simple, where date of birth string = today's formatted date. That will return just the records where it’s the persons birthday today. Perfect.
Next we want to update these records, because it could be multiple records, we need an Apply to Each control around the next few steps. By adding an update record or using any dynamics content in a compose action, Power Automate will automatically put an Apply to Each control, but you can also add this in manually.
The first part in the Apply to Each control is a Parse JSON action because we need access to the data from the records listed, but when you used the Apply to Each Control, you lose access to this. By using the Parse JSON Action, we can interpret and use the data from the list records action and then use it. I’d recommend running the first bit of the Flow first, getting the JSON from the List records action and then using the sample payload button to generate the schema. I have a video on how to use the Parse JSON action here.
Now comes the magic, we have quite a long expression to get the required output. We create a Compose Action and then use the following expression.
add(int(first(split(string(div(div(sub(ticks(utcNow()),ticks(body('Parse_JSON')?['matt_dateofbirth'])),864000000000),365.25)),'.'))),1)
There’s a lot of things happening in this expression so let's break this down so that you understand how we arrived at this.
This part of the expression subtracts the number of ticks of the time now, from the date of birth. A tick is defined as 100 nanoseconds from Jan 1st 1601 at UT 00:00:00, so by subtracting the two, we can get the difference between the current date and the birthdate in ticks.
sub(ticks(utcNow()),ticks(body('Parse_JSON')?['matt_dateofbirth']))
The div (Divide) is dividing the number of ticks by 864000000000 (the number of ticks in a day) and then that answer by 365.25 (which is the number of days in a year roughly) and this gives you the number of years old the person is as a decimal.
div(div(sub(ticks(utcNow()),ticks(body('Parse_JSON')?['matt_dateofbirth'])),864000000000),365.25))
Next part converts the decimal to a string, splits the string using the ‘.’ part of the decimal and then returns just the first answer.
first(split(string(div(div(sub(ticks(utcNow()),ticks(body('Parse_JSON')?['matt_dateofbirth'])),864000000000),365.25)),'.'))
The last bit converts the number to an integer, and add 1 to the answer. The reason for this is that when the answer comes back, we’ll be running this on the birthday, but until the day is over, it will still be a decimal of the previous age and not matching or over.
add(int(first(split(string(div(div(sub(ticks(utcNow()),ticks(body('Parse_JSON')?['matt_dateofbirth'])),864000000000),365.25)),'.'))),1)
It may look all big and scary but it’s much simpler when you break it down. Then the last thing is to update the record. We choose the Contact dynamic content from the List Records action (it will actually be the apply to each but Power Automate is smart enough to figure that out), which is the item ID and put the output of this action into the age field.
And Voila
And there we have it, a solution which returns a whole number, runs on a reliable scheduling engine, returns as an integer and...no code required. I’m really happy about this solution and I hope it helps others.
Ciao for now.