WRITTEN BY Duco Boer - 10 September 2014

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.

The publishing mechanism will take care of the generation of needed Javascripts, put them on the form and can even publishes the customizations.

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.

WRITTEN BY Duco Boer - 29 July 2014

Many Dutch organizations have a subscription to the Dutch zipcode table from Cendris:

http://www.postcodeshop.nl/

In this case we will demonstrate how you can auto-fill the city and street on the account or contact form upon change of the zipcode or house number.

This example is valid for : CRM 4.0, 2011 and 2013 On Premise. (not for CRM Online)

For example purposes we use the following fields:

1. address1_line1 = Street

2. address1_line2 = House number

Furthermore we have created a custom entity named new_TPGpostcode where we import the Cendris zipcode table frequently. In this example we assume you import the fields without any modification.

Cendris zipcode entity

To accomplish the autofill you need to create two calculated attributes. One for the street and one for the city. Also you need to add two client side triggers to the calculated attributes which will fire upon change of the zipcode and house number (address1_line2).

The target entity for the calculated attributes is either account or contact. The target attribute is either address1_city or street (address1_line1) depending on your configuration. The type is 'Single entity calculation'. The Calculation operator is 'Custom SQL'.

And now comes the most important part : the custom calculation syntax. For the city retrieval use the following:

case
   when [address1_line2] = '0' then
           isnull((select top 1 new_WoonplaatsnaamNEN from new_TPGpostcode where new_Huisnummerreeks = 0 and upper(replace([target].[address1_postalcode], ' ', ''))= new_postcode), case when len([address1_city]) > 0 then [address1_city] else 'ONBEKEND' end)   
when CAST([address1_line2] AS int) % 2 = 0 then
       isnull((select top 1 new_WoonplaatsnaamNEN  from new_TPGpostcode where new_Huisnummerreeks = 2 and new_nummervan <= CAST([target].[address1_line2] as INT) and new_nummertot >= CAST([target].[address1_line2] as INT) and upper(replace([target].[address1_postalcode], ' ', ''))= new_postcode), case when len([address1_city]) > 0 then [address1_city] else 'ONBEKEND' end)      
   else
           isnull((select top 1 new_WoonplaatsnaamNEN from new_TPGpostcode where new_Huisnummerreeks = 1 and new_nummervan <= CAST([target].[address1_line2] as INT) and new_nummertot >= CAST([target].[address1_line2] as INT) and upper(replace([target].[address1_postalcode], ' ', ''))= new_postcode) , case when len([address1_city]) > 0 then[address1_city] else 'ONBEKEND' end)   

end

For the second calculation 'the street' please use the following:

case
   when [address1_line2] = 0 then
           isnull((select top 1 new_StraatnaamNEN from new_TPGpostcode where new_Huisnummerreeks = 0 and upper(replace([target].[address1_postalcode], ' ', ''))= new_postcode), case when len([address1_line1]) > 0 then [address1_line1] else 'ONBEKEND' end)   
when [address1_line2] % 2 = 0 then
       isnull((select top 1 new_StraatnaamNEN from new_TPGpostcode where new_Huisnummerreeks = 2 and new_nummervan <= CAST([target].[address1_line2] as INT) and new_nummertot >= CAST([target].[address1_line2] as INT) and upper(replace([target].[address1_postalcode], ' ', ''))= new_postcode), case when len([address1_line1]) > 0 then [address1_line1] else 'ONBEKEND' end)  
   else
           isnull((select top 1 new_StraatnaamNEN from new_TPGpostcode where new_Huisnummerreeks = 1 and new_nummervan <= CAST([target].[address1_line2] as INT) and new_nummertot >= CAST([target].[address1_line2] as INT) and upper(replace([target].[address1_postalcode], ' ', ''))= new_postcode), case when len([address1_line1]) > 0 then [address1_line1] else 'ONBEKEND' end)

end

Of course if you used other field names, you should change them accordingly.

Now the only thing that remains is to publish your triggers and you are good to go.

RSS Feed