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)
22 Jan, 2015 06:47 AM

Spot On With This Write-up, I Really Suppose This Web Site Wants Rather More Consideration. I?ll Probably Be Again To Learn Much More, Thanks For That Info. uno judi bola

Keven Peters
Keven Peters
29 Jan, 2015 08:24 PM

Whichever enterprise you're jogging, you'll want to hold data connected with buyer balances, payables to companies, taxes obligations, memorabilia, and lots of various other information critical to taking care of your online business at all fronts. Including the competition, you need to possibly be in addition to every thing, specifically when it concerns your web visitors. writing a statement of purpose

sad
sad
26 May, 2015 07:58 AM

Programming language is intresting is you intreset in it. If you like programming then you only concentrate on it because programming is totally base on logic and in this Article you need good skills and logic.So, like this website and made an interesting discovery about your code. It works great on create, does not work so well with update. Yeah programming is very bored sometime and when i feel bored i watched movies in this site Watch Series Game Of Thrones

23 Jun, 2015 11:45 AM

Great article. Here I found a lot to learn. I'm a student and I found this blog as an awesome topic for tok essay with professional help of writing service.

16 Jul, 2015 03:16 PM

I'm happy to meet a person with the same opinion as mine.

renuka
renuka
28 Jan, 2016 11:00 AM

Glad to visit your blog, I look forward to more good articles and I think we all like to thank so many good articles, blog to share with us.
Citrix Mobility Solutions

renuka
renuka
28 Jan, 2016 11:07 AM

Maharishi Markandeshwar University, Mullana - Ambala, erstwhile known as Maharishi Markandeshwar Education Trust was founded with the objective of social, educational and economic upliftment of society in the year 1993, in the name of Lord Shiva's devotee, "Maharishi Markandeshwar Ji.
Top BCA college
Top MBBS College
BSc nursing college

Linda
Linda
01 Apr, 2016 10:52 AM

Our rates are affordable and you shouldn't have any trouble click here acquiring our services.

Masala
Masala
06 Apr, 2016 04:11 AM

This is the website that has helped me to understand more on the technical details of CRMs. I am a computer science student and the site has helped me to make a good idea on CRMs. Keep up the good show directv choice package

06 Apr, 2016 09:33 AM

You Made Some Respectable Points There. I Seemed On The Internet For The Problem And Located Most Individuals Will Go Along With Along With Your Website. diobola.com

Heather
Heather
19 Apr, 2016 12:52 PM

We are the only one among custom writing services that is able to write custom essay writing service essay UK shortly.

12 Sep, 2016 05:10 PM

At least these days for home work services, We provide help university of phoenix homework help (UOP) to all students across the world.

Megan Taylor
Megan Taylor
15 Nov, 2016 05:24 AM

I'm happy to meet a person with the same opinion as mine. Negan Jacket - Black Leather Jacket

Alexandra Brisbane
Alexandra Brisbane
20 Dec, 2016 05:39 AM

We normally use the router to have a wireless connection to connect our mobile phones, tablets, TVs, laptops and so on. Slow internet connection makes all these devices quite unusable. Therefore it is important to buy a quality modem and router with ip 192.168.1.1 which will certainly help avoid most connection problems.

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