I recently encountered an issue when trying to create an Exact Age column for a contact in Microsoft Dynamics CRM. There were several solutions available on the internet, but none of them was a good match for my specific situation. Some ideas I explored included:

  1. Creating a calculated field using the formula DiffInDays(DOB, Now()) / 365 or DiffInYears(DOB, Now()) – I used this at first, but if the calculated field is a decimal type, then you end up with a value like 23 years old which is not desirable. If the calculated field is a whole number type, then the value is always the rounded value. So, if the DOB is 2/1/1972 and the current date is 1/1/2019, the Age will be 47 when the contact is actually still 46 until 2/1/2019.
  2. Using JavaScript to calculate the Age – The problem with this approach is that if the record is not saved, then the data becomes stale. This one also does not work with a view (i.e., if you want to see a list of client ages). The JavaScript solution seems more geared towards the form of UI experience only.
  3. Using Workflows with Timeouts – This approach seemed a bit complicated and cumbersome to update values daily across so many records.

Determining Exact Age

Instead, I decided to plug some of the age scenarios into Microsoft Excel and simulate Dynamic CRM’s calculations to see if I could come up with any ideas.

Note: 365.25 is used to account for leap years. I originally used 365, but the data was incorrect. After reading about leap years, I decided to plug 365.25 in, and everything lined up.

Excel Formulas

Setting up the formulas above, I was able to calculate the values below. I found that subtracting the DATEDIF Rounded value from the DATEDIF Actual value produced a negative value when the month/day was after the current date (2/16/2019 at the time). This allowed me to introduce a factor of -1 when the Difference was less than or equal to 0.  Using this finding, I set up the solution in CRM.

Excel Calculations

The Solution

  1. Create the necessary fields.
    Field  Data Type  Field Type  Other  Formula 
    DOB  Date and Time  Simple  Behavior: User Local   
    Age Actual  Decimal Number  Calculated  Precision: 10  DiffInDays(new_dob, Now()) / 365.25 
    Age Rounded  Whole Number  Calculated    DiffInDays(new_dob, Now()) / 365.25 
    Age Difference  Decimal Number  Calculated  Precision: 10  new_ageactual – new_agerounded 
    Age  Whole Number  Calculated    See below 
  1. Create a business rule for DOB; setting it equal to birthdate when birthdate contains data. This way when birthdate is set, the DOB is set automatically. This arrangement is necessary for other calculated fields.
    Business Rules
  2. Set up the Age calculated field as follow:
    Age Calculated Field

Once these three steps have been completed, your new Age field should be ready to use. I created a view to verify the calculations. I happened to be writing this post very late on the night of 2/16/2019. I wrote the first part before 12:00 a.m., then I refreshed the view before taking the screenshot below. I was happy to see Age Test 3 record flip from 46 to 47 when I refreshed after 12:00 a.m.

Age Solution Results

Determining Exact Age at Some Date in the Future

The requirement that drove my research for this solution was the need to determine the exact age in the future. Our client needed to know the age of a traveler on the date of travel. Depending on the country being visited and the age of the traveler on the date of departure, different forms would need to be sent in order to prevent problems when the traveler arrived at his or her destination. The solution was very similar to the Age example above:

The Solution

  1. Here is an overview of the entity hierarchy:
    Age at Travel Entities
  2. Create the necessary fields.
    Entity  Field  Data Type  Field Type  Other  Formula 
    Trip  Start Date  Date and Time  Simple  Behavior: User Local   
    Contact  DOB  Date and Time  Simple  Behavior: User Local   
    Trip Contact  Age at Travel Actual  Decimal Number  Calculated  Precision: 10  DiffInDays(contact.dobnew_trip.start) / 365.25 
    Trip Contact  Age at Travel Rounded  Whole Number  Calculated  n/a  DiffInDays(contact.dobnew_trip.start) / 365.25 
    Trip Contact  Age at Travel Difference  Decimal Number  Calculated  Precision: 10  new_ageattravelactual – new_ageattravelrounded 
    Trip Contact  Age at Travel  Whole Number  Calculated  n/a  See below 
  1. Create a business rule for Contact DOB; setting it equal to birthdate when birthdate contains data. This way when birthdate is set, the DOB is set automatically. This arrangement is necessary for other calculated fields.
    Business Rules
  2. Set up the Trip Contact’s Age at Travel calculated field as follow:
    Age at Travel Calculated Field

Once these steps have been completed, your new Age at Travel field should be ready to use. I created a view to verify the calculations.

You’ll notice that in the red example, the trip starts on 8/14/2020. The contact was born on 9/29/2003 and is 16 on the date of travel but turns 17 a month or so later. In the green example, the trip is also on 8/14/2020. The contact was born 4/12/2008 and will turn 12 before the date of travel.

Age at Travel Solution Results

Conclusion

While there are several approaches to the Age issue in Dynamics CRM, this is a great alternative that requires no code and works in real time. I hope you find it useful!

While it isn’t quite as good as having complete control of your CSS, Dynamics CRM (2015 Online Update 1, and On-Prem 2016) now offers a feature called Themes. Themes enable the organization to customize their CRM Web interface to some degree, although we still don’t have complete control of the styling.

There are plenty of good blogs on Dynamics CRM themes, but I’ve yet to find one that includes good tips on determining the hex values for the colors you need. This blog will help you determine these values, including using a color picker to pull a color’s hex value from an image. Read More…

Microsoft Dynamics CRM is an interesting and powerful business application. A core out-of-the-box (OOTB) benefit of Dynamics CRM is the ability to extensively tailor the application to address business needs, and here there are two approaches to consider: development or customization. Determining which approach to take is the key to maximizing the benefits of Dynamics CRM while keeping costs low.

Dynamics CRM is a basic web user interface fronting a SQL Server database that manages relational data. However, it is flanked by a built-in array of basic analytical tools and extensive administrative features, such as auditing, which give it enterprise-level credentials. Throw in a customizable user interface (UI), and you have a tool that is capable of supporting both small businesses and multinational corporations. So it would be logical to assume that Dynamics CRM has a developer-friendly, structured architecture to support customizations.

However, the reality is a little more complicated and brings up some curious paradoxes about Dynamics CRM. Read More…

Microsoft has revamped its licensing model for Dynamics CRM 2013.  Here’s a summary of the information from the latest Microsoft documentation.

There are three basic versions of Microsoft Dynamics CRM 2013, and each has its own particular licensing requirements:

  1. Microsoft Dynamics CRM 2013 On-Premises: Most useful for organizations that do their deployments in-house.  You must purchase a license for each server that will run the CRM Server software.  You must also purchase Client Access License (CAL) for each user or device that will access the software.
  2. Microsoft Dynamics CRM 2013 Online: Used for solutions that will be hosted in the cloud. You must purchase a User Subscription License (USL) for each user that will access the solution. USLs are assigned to a named user, which means that USLs cannot be shared. A single USL licenses the user to access any instance of Microsoft Dynamics CRM 2013 or earlier associated with the same tenant.  (USLs do not include use rights for Yammer or Skype.)
  3. Microsoft Dynamics CRM 2013 SPLA: Used by service providers and independent software vendors who license CRM to provide solutions to customers. You must purchase a Subscriber Access License (SAL) for each unique individual user who is authorized to access or otherwise use the licensed products. SALs are assigned to a named user, which means that SALs cannot be shared. A SAL will authorize a user to access any number of instances of CRM 2013 or earlier running on the organization’s servers. Read More…
Software development is a risky endeavor, with many things that can go wrong. At any moment, you may find that your budget or schedule targets have been completely missed and your developers and customers disagree about the scope and functionality of the project. In fact, numerous studies state that up to 60% of projects completely fail or massively exceed their budgetA recent study by McKinsey found that on average, most software projects over $5 million exceed their budget by 45%, turning that $5 million application into a $7+ million application.  As responsible software systems developers, we have to constantly ask ourselves – how do we prevent this from happening to our projects?  The answer is to reduce risk. Read More…