DID YOU KNOW? What happens to Audit History on performing database level update?

062915_1445_DIDYOUKNOWW1.jpg

Note: This is all about UNSUPPORTED database level updates. These kind of updates are not recommended (unless you are left with no options).

Though any kind of database level updates are not recommended in CRM, there are scenarios where we are required to do so. I had one such requirement where I was required to perform a simple update (setting two options field value to Yes) for millions of records. Doing same using a supportive way (bulk update or bulk workflow execution) used to take days due to CRM service calls but same updates using SQL script was done in few hours.

I had auditing enabled for these records in CRM, but as this was unsupported db level update I was not expecting any help from audit history. What was expected form auditing was either of below:

  1. Change track as new update request in audit history. (If there is any kind of trigger return at db level to maintain auditing)
  2. No updates at all in audit history. (Assuming auditing are maintained by some kind of workflow/plugin that trigger at UI level)

Unfortunately, none of this happened and what happened was not at all audit friendly or expected. This is what happened:

The change made by SQL script was tracked at create event itself in audit history. But only the field value change was tracked and not the change date/time. The change date/time was still showing when the record was created.

Though these kind of changes are not recommended, but I still feel that in some cases these are required and this behavior of auditing will affect the consistency. I feel that things should be either completely baked or not baked at all than having them half baked. But again this is only what I feel. J

Example:

  1. I created a Contact record on 7/6/2015 at 11:52 AM with ‘First R~~~~~ Created’ set to ‘No’. Audit history shows something like below.

     

  2. Now on 7/6/2015 at 12:00 Noon, I update the value for ‘First R~~~~~ created’ to ‘Yes’ using a database level update script. Ideally there should have been a new entry made in audit log to track this change and there should be no change made to existing audit history records. But CRM updates the changed value in create event itself without even tracking the change time.

 

Hope this helps and thanks if you have come all the way down here reading the article.

Advertisements

DID YOU KNOW? Fields rarely touched in Dynamics CRM

We all know that whenever we create an entity in CRM, CRM creates few fields by default. Some of these like Owner, Owning Business Unit, Created On, Modified On, etc. are used extensively while working with Dynamics CRM. But there are some fields that we use very rarely or even never; thus we tend to forget about them. But some of these fields are really important and can save a lot custom work, it’s just that we should be aware of them.

Recently there was a question on Dynamics Community regarding use of same fields which inspired me to search for them and have it documented. Few fields can be understood from their name but some fields don’t even have proper description in CRM. For such fields, below table can help. Links against the fields has more explanation and scenarios where these can be used.

Field Name Description
CreatedBy User who created the record.
CreatedOn Date and time when the record was created.
CreatedOnBehalfBy To create records on behalf of another user.
Used for impersonation in CRM 2011
ExchangeRate When I create a record, such as an opportunity,
and use a non-base currency, such as GBP in this
hypothetical scenario, this is what happens:

  • I set the Currency field to GBP
  • I put a value in the money field, called Estimated
    Revenue
  • I save the record. During the save operation,
    the following occurs:
  • The Exchange Rate field is populated with the
    value 0.61300000000
  • The Estimated Revenue_base field is populated
    with the value from the Estimated Revenue field
    converted to its inflated USD value
  • Both of these updates occur whether the Exchange
    Rate and Estimated Revenue_base fields are exposed
    on the form or not

Afterwards, when the dollar makes that incredible comeback,
I need to update my exchange rate on the GBP currency.
I do so – it’s now 1, reflecting a 1:1 conversion rate.
What happens to my opportunity? Nothing, at least not immediately.
The Exchange Rate field on my opportunity is not automatically populated
with the new value of 1. However, as soon as I change the value of ANY money
field on the opportunity and save the form, the Exchange Rate field is updated
with the new value from the GBP currency record, and the Estimated Revenue_base
field is updated with the new converted value (and any other _base fields for other
money fields on the form – remember, there is only 1 Currency and 1 Exchange Rate field,
so these values apply to all money fields on the form). This also happens if I change the state
of the record, such as closing the Opportunity as won or lost, activating or closing a quote, etc.

http://andrewbschultz.~exchange-rate-updates/

ImportSequenceNumber The import sequence number is a whole number field. The range can be customized if needed.
The basic idea behind this field is to store the sequence number (ID) of the source record during
data import to CRM. If this field is mapped during migration package/script design,
it provides a one-to-one link between source row and destination CRM record.

 

http://www.powerobjects.com/~-number-field/

ModifiedBy User who modified the record.
ModifiedOn Date and time when the record was modified.
ModifiedOnBehalfBy To create records on behalf of another user. Used for impersonation in CRM 2011
OverriddenCreatedOn Date and time that the record was migrated.
http://www.magnetismsolutions.~_crm_2011_part_1
TimeZoneRuleVersionNumber The definition of a recurrence pattern of how local time is converted to/from Universal Coordinated Time (UTC). This includes information about Daylight Savings Time (DST) versus Standard Time. Time zone rules can change over time, thus a time zone can have multiple rules from a historic point of view, but can have only one rule that is current and in effect.

 

http://blogs.msdn.com/b/crm/archive/2008/05/14/time-zones-in-microsoft-dynamics-crm-4-0.aspx

TransactionCurrencyId Currency associated with the entity.
UTCConversionTimeZoneCode Time zone code that was in use when the record was created.
VersionNumber This column is used mainly for concurrency support. The VERSIONNUMBER field is a unique value that gets incremented as records are updated – it can be very useful.

DID YOU KNOW? Value for overriddencreatedon is swiped with createdon at database level

One of my college recently had a requirement to import few records into CRM with back dated values. Isn’t it a simple task? Just map/set the back dated value for ‘overriddencreatedon’ field while create/import of records. But has anyone checked what happens to the value for ‘createdon’ field?

All views and fields in CRM displays ‘createdon’ field and not ‘overriddencreatedon’. Thus, wherever we set value for ‘overriddencreatedon’, the value is set to ‘createdon’ field and the actual date/time when the record is created into CRM is set to ‘overriddencreatedon’.

E.g.: I’m creating a record on 2015-06-16 11:45:23.000 UTC into CRM with value for overriddencreatedon set to 2015-06-16 11:44:39.000 UTC. Upon completion, if you query the database you will see that the value what I set to overriddencreatedon is set to ‘createdon’ and the value for ‘overriddencreatedon’ is replaced with actual date/time when the record was created.

Update request at 2015-06-16 11:45:23.000 UTC:

Createdon = null

overriddencreatedon = 2015-06-16 11:44:39.000 UTC

Result:

Createdon = 2015-06-16 11:44:39.000 UTC

Overriddencreatedon = 2015-06-16 11:45:23.000 UTC

date

Note: Setting ‘overriddencreatedon’ on update of record is not supported.

CRM 2015 Update 1: Store date and time values with or without time zone information

Until the previous release of CRM, all date and time values in CRM were time zone aware, including those with the Date Only format.

Use the new DateTimeAttributeMetadata.DateTimeBehavior property to store date and time values with or without time zone information. Until now, all the date and time values in CRM were stored using the user’s local time zone information, including those with the DateOnly format. The CRM system use to convert values from the user’s local time zone to Coordinated Universal Time (UTC) on update, and back to the current user’s time zone when displayed on the form.

This was not an optimal experience for storing and using:

  • Date only values without the time information, such as birthdays and anniversaries.
  • Date and time values without the time zone information so that users across multiple time zones see the same date and time value, such as the hotel check-in time.

You can use the DateTimeBehavior enumeration to specify a value for the DateTimeBehavior property, or can directly specify the string value. The DateTimeBehavior property can have any of the following three values:

The following sample code demonstrates how to set a DateOnly behavior for a new attribute:

// Create a date time attribute

DateTimeAttributeMetadata dtAttribute = new DateTimeAttributeMetadata

{

// Set base properties

SchemaName = “new_Birthday”,

DisplayName = new Label(“Birthday”, _1033),

Description = new Label(“Birthday”, 1033),

DateTimeBehavior = DateTimeBehavior.DateOnly

Format = DateTimeFormat.DateOnly,

ImeMode = ImeMode.Disabled

};

 

In the above code, you can also set the value of the DateTimeBehavior property by directly specifying the string value: DateTimeBehavior = “DateOnly”

Use the DateTimeAttributeMetadata.Format property to specify the date/time display format of the attribute irrespective of how it is stored in the database. You can use the DateTimeFormat enumeration to specify the display format: DateAndTime or DateOnly.

If the DateTimeBehavior property is set to DateOnly, you cannot set or change the value of the Format property to DateAndTime.

 

***Reference: https://msdn.microsoft.com/en-us/library/89969adc-8577-424c-bfcc-7f65c5d4bd19#BKMK_DateTimeAttrbuteMetadata

***Important: This topic is pre-release documentation and is subject to change in future releases.

TimeZone field format in CRM

I’m sure that everyone of us must have added the field of type Whole number with format TimeZone in at least one CRM implementation. But have you tried to set the value of this field using a custom C# application or a Plugin or Workflow?

Problem with setting the value for this field using C# is that as the field is of type whole number, it accepts only integer value while setting it, but the values for each timezone labels are not mentioned. Also the minimum and maximum range mentioned for whole number field do not apply for format as timezone. Then what are the values for these?

The values ranges from 0 to 300 but this are not in sequence. Below table shows the values for the labels.

0: “Dateline Standard Time”;
1: “Samoa Standard Time”;
2: “Hawaiian Standard Time”;
3: “Alaskan Standard Time”;
4: “Pacific Standard Time”;
5: “Pacific Standard Time (Mexico)”;
6: “UTC-11”;
10: “Mountain Standard Time”;
12: “Mountain Standard Time (Mexico)”;
13: “Mexico Standard Time 2”;
15: “US Mountain Standard Time”;
20: “Central Standard Time”;
25: “Canada Central Standard Time”;
29: “Central Standard Time (Mexico)”;
30: “Mexico Standard Time”;
33: “Central America Standard Time”;
35: “Eastern Standard Time”;
40: “US Eastern Standard Time”;
45: “SA Pacific Standard Time”;
47: “Venezuela Standard Time”;
50: “Atlantic Standard Time”;
55: “SA Western Standard Time”;
56: “Pacific SA Standard Time”;
58: “Central Brazilian Standard Time”;
59: “Paraguay Standard Time”;
60: “Newfoundland Standard Time”;
65: “E. South America Standard Time”;
69: “Argentina Standard Time”;
70: “SA Eastern Standard Time”;
73: “Greenland Standard Time”;
74: “Montevideo Standard Time”;
75: “Mid-Atlantic Standard Time”;
76: “UTC-02”;
80: “Azores Standard Time”;
83: “Cape Verde Standard Time”;
84: “Morocco Standard Time”;
85: “GMT Standard Time”;
90: “Greenwich Standard Time”;
92: “Coordinated Universal Time”;
95: “Central Europe Standard Time”;
100: “Central European Standard Time”;
105: “Romance Standard Time”;
110: “W. Europe Standard Time”;
113: “W. Central Africa Standard Time”;
115: “E. Europe Standard Time”;
120: “Egypt Standard Time”;
125: “FLE Standard Time”;
129: “Jordan Standard Time”;
130: “GTB Standard Time”;
131: “Middle East Standard Time”;
133: “Syria Standard Time”;
135: “Jerusalem Standard Time”;
140: “South Africa Standard Time”;
141: “Namibia Standard Time”;
145: “Russian Standard Time”;
150: “Arab Standard Time”;
155: “E. Africa Standard Time”;
158: “Arabic Standard Time”;
160: “Iran Standard Time”;
165: “Arabian Standard Time”;
169: “Azerbaijan Standard Time”;
170: “Caucasus Standard Time”;
172: “Mauritius Standard Time”;
173: “Georgian Standard Time”;
175: “Afghanistan Standard Time”;
180: “Ekaterinburg Standard Time”;
184: “Pakistan Standard Time”;
185: “West Asia Standard Time”;
190: “India Standard Time”;
193: “Nepal Standard Time”;
195: “Central Asia Standard Time”;
196: “Bangladesh Standard Time”;
200: “Sri Lanka Standard Time”;
201: “N. Central Asia Standard Time”;
203: “Myanmar Standard Time”;
205: “SE Asia Standard Time”;
207: “North Asia Standard Time”;
210: “China Standard Time”;
215: “Malay Peninsula Standard Time”;
220: “Taipei Standard Time”;
225: “W. Australia Standard Time”;
227: “North Asia East Standard Time”;
228: “Ulaanbaatar Standard Time”;
230: “Korea Standard Time”;
235: “Tokyo Standard Time”;
240: “Yakutsk Standard Time”;
245: “AUS Central Standard Time”;
250: “Cen. Australia Standard Time”;
251: “Adelaide (Commonwealth Games 2006)”;
255: “AUS Eastern Standard Time”;
256: “Canberra, Melbourne, Sydney (Commonwealth Games 2006)”;
260: “E. Australia Standard Time”;
265: “Tasmania Standard Time”;
266: “Hobart (Commonwealth Games 2006)”;
270: “Vladivostok Standard Time”;
275: “West Pacific Standard Time”;
280: “Central Pacific Standard Time”;
281: “Magadan Standard Time”;
284: “UTC+12”;
285: “Fiji Standard Time”;
290: “New Zealand Standard Time”;
295: “Kamchatka Standard Time”;
300: “Tonga Standard Time”;

Timer control in Microsoft Dynamics CRM 2013

This great little feature is a new control which you can place on any customizable out of the box or custom entity form. They can also be added to Quick Create and Quick View forms.

This visual indicator will let the user know how many hours/minutes/seconds remain, or have been exceeded against the specified defined failure date/time field. The visual indicators are different colors based on the status and are in large text on the form which is an excellent way to let the user know exactly where they stand against the deadline.

green red

Below links provide few examples that any business user could leverage a timer for to help keep them on track with their time sensitive tasks.

https://community.dynamics.com/crm/b/crmsoftwareblog/archive/2014/11/01/the-best-little-microsoft-dynamics-crm-2013-feature-you-might-not-know-what-to-do-with-timers.aspx

Migrating history information while data import in Dynamics CRM

When you import historical data in Dynamics CRM, you may want to override property information like Created By, Created On, Modified By, Modified On associated to the record. It seems possible because CRM has provided security privileges called “Override Created on or Created by for records during data import”. However, simply update the appropriate Security Role to enable this privilege does not completely meet all requirements to override such fields as the name sounds.

The truth is, only “Created On” field can be overridden and only if the ‘allow creating duplicate records’ option is checked during the import process. In other words, we cannot update this field on existing records. Only creating new records does work for overriding “Created On” field.
While the Created By, Modified On, Modified By fields cannot be updated once the data is imported even though the user has full privilege on “Override Created on or Created by for records during data import”.

A workaround for such request can be to have a plugin that fires on pre-create of the entity and set the values for remaining fields problematically.

 

**IMPORTANT**

It is recommended that you do an import process by a administrative user which has a timezone set to GMT/UTC.

The reason is in 2005 (2005 revision to dates of observance) the date when Daylight Savings Time starts and ends in the US was changed. As a result, if you import the data back in for previous years as a user that is not on GMT, some of the records will be off by an hour.

 

Note:

  • When one have a list of related entities for which data import is required, order in which data for these entities are imported is important. In some cases, you have many relationships, and you cannot practically determine the order in which they should be imported. In this case, do a two-pass operation.
  • It is recommended to map the record ID/GUID fields when the records are imported. If you map the GUID fields, all relationships will work when imported.
  • It is recommended to map createdon to overriddencreatedon. You can only populate this when the record is created, and this is what will set the created on date for the record to match the original version.
  • There are some fields that you do not want to map, such as version number, import sequence.
  • There are several fields that you cannot import like modified by, created by, modified on.
  •  Custom data cannot be reimported for closed opportunities.

Date Time field in Microsoft Dynamics CRM

The major pain for MS Dynamics CRM developers is to work with the DateTime field in CRM. MSD CRM doesn’t have a only Date field. This may not look as an issue when I say that CRM don’t have only date field, but due to the nature how CRM handles these DataTime fields, it becomes a challenge for CRM developers to work with it.

Consider a simple example, CSR is working in the IST time zone enters a value for the birthday field as 22nd Sept 1989. Due to the nature of CRM, this value is stored in a database as 21st Sept 1989 06:30 PM. There is no issue when any CSR working in the IST time zone checks the record as CRM will convert the value from UTC to IST and which will be correct value. But what if a CSC working in PST time zone checks the record? CRM will convert the same UTC value (09/22/2014 06:30 PM) to PST time zone which will be shown as 21st Sept 2014 11:30 AM. How can CRM change the birthday of a record?

This is just because there is no only Date field in CRM. CRM DateTime always comes with a time component. There is a setting in creating a DateTime field that defines date only, but all that does is default the time to midnight local time.

Below are a few points which if considered before implementing your logic about a DateTime field in CRM can avoid the errors.

  • DateTime is saved in database as UTC time only
  • DateTime in CRM UI is always shown based on user’s local time zone. This is true even if UI is only showing the date component. This leads to following effect:
  • DateTime retrieved through CRM Web Services is always UTC time
  • DateTime set through CRM Web Services is user’s local time zone by default
  • Note that extra care must be taken to understand whether the call is done with the calling user or a service user
  • DateTime set through CRM Web Services can be defined to be UTC instead
  • DateTime queried directly from SQL table or base view returns UTC
  • DateTime queried from filtered view returns users local time

Note: It is recommended that you set the timezone for Administrative User to GMT/UTC irrespective of that users actual timezone or server physical location as GMT/UTC is the base timezone for CRM.