As we all know the world is about to end. Make sure your CRM is ready for it.

Comment unsuccessful. Please correct the errors below.

How to correctly record the end of the world in CRM

Far Away Long Time Ago

Once upon a time at precisely 6:15 PM on a pleasant spring afternoon in far far away land a baby was born. The baby grew up, got a girlfriend, learned how to swim, finished high school, got his heart broken, drunk profusely, got a degree, fell in love, got married, quit science, moved to Australia, lost a fortune, cloned himself twice, got his friends together to celebrate his birthday then ruined the celebration by realising that it's not his birthday. You see, when he was born far, far away, it was already tomorrow in Sydney.

I consider myself lucky, in fact, that I was not born on 31st of December that would make me one year older in Australia.

Money To Be Made But The End Is Nigh

If you think that birthday example is far fetched - after all who cares if you start celebrations just a few hours earlier (though in some countries it's considered to be a very bad luck, look for #87) - but how about postponing company's profits by moving money from New York branch late afternoon on 30th of June to Auckland, New Zealand so that money leave the bank this financial year but arrive to the destination in the next one?

Of course, it all pales compared to the end of the world but here I am really confused. Is it a rolling event with the world slowly rotating into abyss? Or is it a preset moment in space-time continuum when the last timezone rolls over? Edit: apparently it all blows at winter soltice at precisely 11:11 AM UTC. Not cool, I'd rather have it at 21:12:12 UTC but that would get a lot of people off the hook and into tomorrow. Rolling end of the world is so much cooler which would have been bad news for our Samoans neighbours - not only they already lost Friday, they would perish first - that's what you get for cheating the system!

CRM To The Rescue

Regardless whether humanity will end its existence one by one or expire all at the same time, one thing for sure, I don't want to rely on my feeble memory to remember that date. A few minutes later our CRM had a new entity Important Date with a date-only field.

List of important dates

Very nice. Let's find out now how our users from Seattle, WA branch are going to see that list.

List of incorrect important dates

March Simpleton Day, an extra day to party on New Year and my birthday is not quite right either. All due to the fact that CRM does not have a true date-only field, and for "date only" fields always sends midnight to the server where it's duly converted to UTC and stored away. Only to be converted to a local date/time when users ask for it.

We've had some explanations of the design, good discussions and some attempts to fix the problem which can be distilled to the javascript code on the client setting time portion of the field to 12:00 PM. Nice try, our egocentric US friends, it does not work if you have users in Australia, New Zealand, Japan, China and many other countries that are massive 12-19 hours ahead of you.

What we really need is a datetime field not dissimilar to the money field where we would use multiple database columns and, in addition to a UTC value, save the timezone information, updating user timezone and include ability to eliminate offset altogether creating a true date-only field. But until that date comes, we need a reliable workaround.

Rescue To The CRM

Cool running

As for our cheeky Samoan neighbours, the move in 2011 to a whopping +14 hours zone was plain cheating. Now they are up to 25 hours ahead of their sibling American Samoa and the phrase "come back tomorrow" never was more ambiguous.

I call this "the lap effect" - running around the stadium so fast that you can see your own butt. As a result, the workaround does not work for Samoan users but they brought it upon themselves (same with being first in line for the rolling end of the world).

I wish I could take a credit for the solution but it was given to me on a platter by one of the members of Dynamics CRM team who observed that the following SQL:

SELECT fiscalcalendarstart FROM OrganizationBase

yields something similar to 2011-07-01 10:59:00.000. As you guessed correctly, the secret sauce is 10:59 and the solution is to force time portion of the field to 10:59 AM UTC. Converted to user's date time when value is retrieved, results in correct date for all time zones in the world including cool +13 hours for New Zealand in summer but excluding some uninhabited US islands, tiny Chatham Islands and all Samoan Islands (though American Samoa will work correctly in summer);

Cool Stuff - The Code

Here is the entire code of the plugin. To keep it short, I have not included any error handling or safeguards.

using System;
using System.Collections.Generic;
using Microsoft.Xrm.Sdk;

namespace Georged.TrueDate
{
    public class Plugin : IPlugin
    {
        private List<string> dateAttributes;
        public Plugin(string unsecure, string secure)
        {
            dateAttributes = unsecure == null ? new List<string>() : new List<string>(
                unsecure.Split(
                    new char[] { ',', ';', ' ', '\r', '\n' },
                    StringSplitOptions.RemoveEmptyEntries));

            // Sanitise the list
            foreach (var systemAttribute in new string[] { "createdon", "modifiedon", "overriddencreatedon" })
            {
                if (dateAttributes.Contains(systemAttribute)) dateAttributes.Remove(systemAttribute);
            }
        }

        public void Execute(IServiceProvider serviceProvider)
        {
            IPluginExecutionContext context = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));
            Entity entity = (Entity)context.InputParameters["Target"];
            foreach (var attr in dateAttributes)
            {
                if(entity.Contains(attr) 
                    && entity[attr] != null
                    && entity[attr].GetType() == typeof(DateTime))
                {
                    DateTime utc = (DateTime)entity[attr];
                    if(utc.TimeOfDay >= new TimeSpan(11,0,0)) 
                    {
                        // Calling user is to the right of Greenwhich
                        // and conversion to UTC moved value to the previous day 
                        utc = utc.AddDays(1);
                    }
                    entity[attr] = utc.Date.AddMinutes(659);
                }
            }
        }
    }
}

Few notes about the code:

  • We need to let our code know what attributes we are interested in processing. In CRM 4.0 we used filteringattributes property during the plugin registration, however in CRM 2011 this property is no longer supported for create plugin. Not sure why, I have not thought about it yet. So instead, we simply define the list of attributes to process in unsecure configuration property of the plugin. The attribute names can be separated by commas, semicolon, space, new line and the upside is that the list can be updated without re-registering the plugin.
  • Plugin receives the value of the attribute already converted to UTC. To figure out what user actually entered we need to find out the timezone of the calling user and convert the value back to user's. But since we know that user has entered date only, i.e. time part of the user input was midnight, we can figure out if UTC conversion cost us a day. If user is to the "right" of UTC, i.e. offset is positive, we would lose a day. As explained above, we only support up to +13 so cut off time is (midnight - 13 hours) == 11 AM. That does not overlap with Hawaii which is -10.
  • entity.Contains performs case-sensitive search - make sure you get your attribute name right. For example, the schema name is Anniversary but the attribute name is anniversary.

Registration

The plugin needs to be registered for both create and update messages either for pre-operation step:

Plugin registration

Pre-validation step should not be used as security checks are not applied yet an this point.

For update message performance can be improved by setting filtering attributes to only date fields requiring intervention.

Existing Records

To fix the existing records all that is required is an update message for the record that includes the required date attribute. The value itself does not need to change, in fact. That can be done by:

  • Writing few lines of code iterating through the records and updating the values
  • Running SQL update directly against the database. Brave enough to do that surely can figure out the SQL required.
  • Executing simple one-step on-demand workflow that "updates" attributes using existing values.

Future Enhancements

In CRM 2011, instead of registering the plugin for every single entity we'd like to support, we can register single plugin for all the entities in the system. To figure out what entities and attributes we need to process, configuration would need to be slightly extended to include names of the supported entities and attributes as part of the configuration. That's the homework, folks.

I don't do no code

Some people deliberately avoid any customisations that involve code. For their benefit you can download unmanaged solution containing the following:

  • Compiled and ready to use plugin
  • Registration of the plugin for create and update messages for the contact entity. Registration is preconfigured to deal with birthdate and anniversary attributes of the contact.
  • Sample on-demand workflow fixing the dates above for the existing records.

The usual warnings apply: use at your own risk, swim between the flags, choking hazard – small parts – not suitable for children under 3 years.

See you on the other side in just over 7 hours.

plugincodeonlineonpremisescrm
Posted by: George Doubinski
Last revised: 24 Jul, 2013 10:52 AM

Comments

Juan Trujillo
Juan Trujillo
24 Jul, 2013 06:43 PM

Great post George,
Do you have the code available for download as well? I would like to make some modifications to it (included the one for setting the Entity dinamically).

Thanks a lot

Kevin
Kevin
09 Aug, 2013 07:51 PM

Made an interesting discovery about your code. It works great on create, does not work so well with update. Here is what seems to happen.
Make new entry, and set date to 7 jul 2014 CRM sets it as 7 jul 2014 - code sets time to 6:59 AM open entry and change the date portion. Time stays at 6:59 AM so code does not fire correctly.

31 Aug, 2013 01:34 AM

@Juan - the public code is above in its entirety. Just create a new plugin project, add new class and paste the code above.

@Kevin - time shouldn't be 6:59, it's 10:59. We use similar code in production and it's passed all the tests. In any case, I'll double check but need to know what time zone you are in.

Cheers George

Giacomo
Giacomo
06 Sep, 2013 12:56 PM

tooooooooooop!!!

Scully
Scully
26 Sep, 2013 09:23 AM

Great example.

You can replace:

if(entity.Contains(attr) 
      && entity[attr] != null
      && entity[attr].GetType() == typeof(DateTime))

With:

if(entity.Contains(attr) && entity[attr] is DateTime)
Arthur
Arthur
01 Sep, 2014 09:33 AM

Are you guys crazy enough to think and act like fools who shout out like the wordl will end by 2012.Apart from that, the movie entitled 2012 was the most stupendous movie I have ever come across. The world will come to an end, that we agree, but not like you say. Telesteps Telescoping Ladder

Your Comments

Comment unsuccessful. Please correct the errors below.
Used for your gravatar. Not required. Will not be public.
Posting code? Indent it by four spaces to make it look nice. Learn more about Markdown.

Preview