Storing and Querying Age in Dynamics CRM

In the previous post, I discussed how we can use North52 Formula Manager to calculate age in Dynamics CRM.

In a recent implementation, we needed to store a running age of each contact in years and months in CRM. Users also needed an ability to query contacts based on the age including an ability to search contacts using the age range. For example, find all contacts with ages between 1 year and 9 months and 2 years and 9 months. Of course, they wanted to be able to do this intuitively. They wanted to be able to enter 1 year and 9 months using the numbers 1 (year) and 9 (months) rather than 1.75 because this would require users to calculate the fraction in their head before doing the search. Also, some of the fractions would be difficult to calculate mentally. For example, 7 months would be equal to 0.583333 (years).

We cannot store the age value in two different fields i.e. years and months because this would prevent users from using ranges i.e. > and < operators in the Advanced Find query or in the System View.

For example, the following query will return incorrect results:

Age_Years >= 1 AND

Age_Months >= 9 AND

Age_Years <= 2 AND

Age_Months <= 9

We addressed this problem by storing the age values as a decimal number with 2 decimals. The whole number represents years and the fraction represents months. For example, 1 year and 1 month is stored as 1.01, 1 year and 10 months is stored as 1.10 and 2 years is stored as 2.00. This is both intuitive to the users in terms of how they think (in years and months rather than fractions) and also enables users to create Advanced Find queries and views based on the age range. Now, they can easily use the following filter criteria to query all contacts between the ages of 1 year and 9 months and 2 years and 9 months:

Age >= 1.09 AND

Age <= 2.09