A client of mine recently asked for support to show the next birthday for a contact. Often there will be an ask for a list view of contacts with a birthday in the next month. In this post I’m going to show you how to use a business rule and calculated fields to achieve this functionality without writing any code. This will be a a native calculated field so it can be used in lists, forms, queries, etc.

This is not limited to birthdays, the same method use can used to calculate any anniversary date. Sale anniversaries, on-hire anniversaries, wedding anniversaries, etc. In this example I’m going to do the birthday for a contact – but this strategy can be used with any date field.

The design uses all out-of-the-box configuration capabilities. There is no code, nothing unsupported, and nothing “wacky” – just a few fields and a business rule (and a workflow if you want to update historical records.) So lets get started.

For this post I’ll be working with the most recent online release.
Microsoft Dynamics 365 Version 1612 (9.0.0.2180) (DB 9.0.0.2180) online

Get The Right Type of Birthday Field

Our goal is to have a field which shows us the Next Birthday of the contact.

Showing the out of the box definition of the Birthday field on Contact

Contact’s Birthday Field

The birthday  field on the contact entity is date only. Unfortunately this means we will not be able to use the date differential calculations in a calculated field with it.

The solution to this is to copy the value to a date and time field that can be used in a calculated field formula. We’ll start by creating a new field, new_internalbirthday, to hold this value. By creating a new field we allow any existing uses of birthday field to continue working – so we don’t have to change any forms, views, reports, etc. In addition, we don’t have to worry about an instance where someone (or some AppSource solution) expects birthdays to be in the birthday field, and they aren’t.

Create a field, Internal Birthday, as a simple date and time field configured as user local and date only

Create the Internal Birthday field

Next we need to create and activate a business rule to populate the new_internalbirthday field – this rule should be scoped to Entity. This rule copies the value of birthday to new_internalbirthday, or clears it if birthday is blank.

If Birthday contains data then set Internal Birthday to Birthday else clear Internal Birthday

Business Rule to Populate Internal Birthday

What Day Is It

You’ll find that calculated field conditions do not have the ability to use the NOW() function. I have not investigated whether you can work around this by updating your calculated field formula using the SDK (i.e., is it really not possible or just a UI limitation.) In order to work around this limitation we’ll create a calculated field to represent the current date minus one day, this will allow us to refer to that field in a calculated field condition statement. (BONUS: You can use this same strategy to access the current date in a business rules – just account for the minus one day!)

Minus one day? Yep, it’s the only way I’ve found to reliably ensure that if a birthday falls on today’s date that it will show today as the birthday and not the birthday next year (which could cause a lot of confusion.)

Create a field, Internal Today Minus One, as a calculated date and time field configured as user local and date only

Create the Internal Today Minus One field

And configure the field calculation to set the field equal to NOW() – 1:

For the Internal Today Minus One field calculation, set the field to SubtractDays(1, Now())

Calculated Field Definition

Calculate The Next Birthday (or Anniversary date)

Now we can create a calculated field to get the next birthday of the contact. This will require an intermediate calculated field to calculate the contact’s birthday for the current calendar year. WHY? Well, because of the limited functions available for a calculated field formula we have to use two formulas to calculate the Next Birthday, depending on whether the contact’s birthday this year has already occurred. And, we need to know the birthday in the current calendar year so we can compare it to the new_internaltodayminusone field we previously created.

So, yes, we are creating two calculated fields (new_internaltodayminusone and new_internalbirthdaythisyear) to use in an If/Then condition to know what formula will give us the correct date for the contact’s next birthday.

Create a field, new_internalbirthdaythisyear, which will always represent the contact’s birthday in the current calendar year:

Create a field, Internal Birthday This Year, as a calculated date and time field configured as user local and date only

Create the Internal Birthday This Year field

And configure the field calculation:

Calculate The Contact’s Birthday for the Current Year

Next we create the field new_nextbirthday (this will be the field we actually show on forms and views):

Create the Next Birthday field

Now we setup the field calculation:

Calculated Field Definition for new_nextbirthday

This calculation checks if the new_internalbirthdaythisyear field is greater than or equal to new_internaltodayminusone. If it is greater than the contact’s birthday has not occurred yet and we set the field equal to new_internalbirthdaythisyear. Otherwise, the birthday has occurred and we the field equal to new_internalbirthdaythisyear plus one year (since their next birthday is in the next calendar year.)

Go ahead and add the new_nextbirthday field to your contact form (if you want.) I also decided to rename Birthday to Birthdate, for clarity.

And now you are free to start building views like this (so you don’t forget to send out a birthday card to your favorite customers!)

Filter a Contact View by Birthday Next Week

Btw, this is also a great place to use the new_internalbirthdaythisyear field – since it doesn’t change when their birthday occurs. So if you want a list of all birthdays in the current month that’ll stay static through the month, use the new_internalbirthdaythisyear field.

Warning: You can filter on new_nextbirthday in the view but you cannot sort on it.

Existing Records

If you have existing contacts with the birthday field populated you’ll need to create a workflow to copy the value over to new_internalbirthday. For new and modified records this will be handled by the business rule we created, but the business rule only runs during record creation and when there is an applicable update. Create an on-demand workflow that will check if birthday has a value and copy it to new_internalbirthday. It should look similar to:

You can execute this manually, by selecting up to 250 records at a time and running it, or us the Bulk Workflow Tool plugin for XRM Toolbox.

Have fun, keep making users happy, and continue to bring the awesomeness of Dynamics 365 to users everywhere!


Also published on Medium.