Thursday, January 23, 2014

CRM2011 & The mystical sorting story

Yes, it still exists good old CRM2011 and you might be glad you still have CRM on-premise else the possibility you still have CRM2011 is becoming smaller and smaller.

In case you have experience or working with dates and times in CRM2011, you  probably are aware that this can be frustrating subject. I think I wrote earlier about time issues and I remembered some very interesting blogposts  about the different 'datetimes' you can receive from Dynamics CRM  depending on which approach you take e.g. web services, database view and filtered views.

Some nice post to refresh your memory or just a trip to memory lane:

Recently, I came across an issue related to date & time where in a view records where sorted on date field and a name field. In the interface it might give you some mystical sorting. Although you might know the alphabet :-)

Take a look at this screenshot from a real life example (no tricks) :

Mystic Sorting

Ok, take a minute to look at the screenshot above, where the date for the records is January 15th and last column is name field. The records are sorted by date and name e.g. A, A,S,W,A,A,A,A, W and W.... ?!#@

It's no trick and your customer will be confused and hopefully you too. As the famous 'young woman or old lady' picture, this is actually happening and you probably can recreate the same if you are a bit creative with dates and times.


What actually happens is this, the first 4 records are actually not created on January the 15th, but on January 14th at exactly 23:00 and other records are created at January 15th at 0:00. The timezone conversion in the user interface is in this case 2 hours and see the result.
In case you test this in SQL server you will see the sorting is correct, because 14th become before 15th. Neverteheless, in the UI of CRM it looks odd :)

Happy sorting :)