It is obvious why you want your Dynamics CRM system to have an 'Age' attribute on your contacts. You want to differentiate your tone of voice or even message depending on the age of your customer. Age could mean how old a person is in years or looking at it from a marketing intelligence approach how long is a customer your customer and what can we say abouth churn, retention and maybe even average lifetime value. But before I get carried away let's focus on how old a person is with the assumption we have a birthdate. This is where Data Manipulation Framework really shows it's power because we need to:
1. Have a formula to convert the birthdate to the age of the current time
2. Have the ability to execute that calculation upon entry of the birthdate
3. Have the ability to re-calculate 'Age' overnight at 12:00 AM when it might be the next birthday.
This article will demonstrate the steps to accomplish this in Dynamics CRM 2013, but it can also be used in version 4.0 or 2011.
What you need first is an Excel formula that is able to convert a date to age in years. To find this I use Google or any search engine of choice. There are many variants and most of them can be used in Data Manipulation Framework. You just have to change the cell references to field reference using our field selector or type it in and your done. The formula I choose for this example is as follows (BTW this one would not work in Excel, but it does in DMF) :
Floor((DateDiff("day", [target].[birthdate], now())/(365+(8/33))),0)
Furthermore you need to create an age attribute on your contact:
After creation of the attribute make sure to refresh your Data Manipulation Framework meta data so it can recognize the new attribute. (In CRM On premise this is automatically done upon publish of the new attribute). The refresh is executed from the Wavextend Framework Online solution configuration page. First you select the contact entity in the entity list and after that you hit 'Refresh Metadata'. Make sure to leave the window open until the message 'Completed Refresh' is displayed.
Now we've taken all the preparations we can start to make the WaveXtend Calculated Attribute. The result should look something like this:
After creation of the calculated attribute you can test it. The test only works in CRM Online when you close the calculated attribute form and open it again from the list of calculated attributes after you saved for the first time. In other versions of Dynamics CRM you can start testing instantly after the initial save.:
Important remark! : null handling exception (or what to do if you don't have a birthdate) is not part of this example. Please contact us if you need help doing that.
Now the second part is to make sure the calculation is triggered when the birthdate is entered on form and re-calculate every night at 12:00 AM.
The form trigger can be added to the calculation as a related entity:
Make sure to choose a Client side (on form) field change trigger. Choose Birthday as the field and set it as asynchonous. This doesn't pause entering data in the form upon entry and is good enough for the purpose. After saving the trigger you need to publish it from the calculated attribute form or calculated attributes list.
Remark!: don't forget to put the new 'Age' attribute on the contact form.
The second event is to re-calculate the Age every night at 12:00 AM. To do so add a related schedule to the Calculated Attribute that runs every night.
And your done. You now have an Age calculation that is automatically re-calculated every night and your 'Age' information is always up-to-date.
If you want to calculate the time in year a customer is your customer, than use the [target].[createdon] field as a source in your calculation instead of the birthdate.
Please come back next month for an article on how to segment your customers in Age classes. (child, teenager etc.) using the WaveXtend Data Manipulation Framework.