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.

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.

Only update things you need to

While it is important not to reduce the benefit of a Dynamics CRM system by excluding activities that would be beneficial, often requests are made to include customization that add little business value but drive real technical complexity.

Consider a simple business scenario:

If every time we create a task we also update the user record with the number of tasks they currently have allocated, that could introduce a secondary level of blocking as the user record would also now be heavily contended. It would add another resource that each request may need to block and wait for, despite not necessarily being critical to the action. In that example, consider carefully whether storing the count of tasks against the user is important or if the count can be calculated on demand or stored elsewhere such as using hierarchy and rollup field capabilities in Dynamics CRM natively. Limited update philosophy should also be adopted while extending CRM using Processes, Plugins or Scripts.

 

 

This is something that reminds me of normalization in DBMS J

***References : Scalable Dynamics CRM Customization documentation

[Error] – Data missing for few fields on all records for an entity in organization created using dbRestore

We have this strange issue where a new organization was created using a database backup of production. Issue here is, for Entity A in source (i.e. production) we have two fields of type decimal populated with a value for all the Entity A records, but the target environment created using dbrestore is missing these value for all the records.

          Audit history on source shows the field populated while creating the records.

 

         We do have same audit history record on target with same date and time but the field entry is missing there. Instead there are two unusual entries with attribute mask number appended by [deleted]. I’m not aware what this entry points too as this got added to the create step itself. This is for all such records of that entity on target.

         Two deleted records points to the two fields for which data was not populated.

 

         But strangely these two fields still exits on target and were never deleted.

I had also added the issue on Community forum (link) below few days back but no help from there too yet. I’m planning to go for a support ticket so any inputs will be helpful. Thanks in advance!

https://community.dynamics.com/crm/f/117/t/151328.aspx

System Administrator VS System Customizer

Title sounds very basic right? But after working for 3 years on CRM platform this was it that I had missed. But as it is never too late to learn anything here is what I searched and added in a consolidate list. It may not be that helpful for someone who is working from long on CRM but may be for someone who is new to it. J

Traditional definition of these two roles say:

System Administrator

System Administrator is the highest level role which encompasses all the privileges and has over-riding rights. The System Administrator has the authority to allow and remove access of other users and define the extent of their rights. For example, the System Administrator and the System Customizer are given access to custom entities by default while all other users need to be given access. This is the only role that cannot be edited.

System Customizer

The System Customizer role is similar to the System Administrator role which enables non-system administrators to customize Microsoft Dynamics CRM. A Customizer is a user who customizes entities, attributes and relationships.

Ok, so do that mean that the only difference between System Administrator and System Customizer is that System Customizer enables User non-system administrator to customize CRM? No there is more.

System Administrator has Organization level access to all system (Out Of Box) entities while System Customizer has only User level access to all system entities. While both System Administrator and System Customizer have Organization level access to all custom entities.

 

The next important difference is that User with System Administrator role can customize System Customizer security role but no user can customize System Administrator security role.

To conclude in tabular format:

System Administrator

System Customizer

Organization level access to all system (Out Of Box) entities User level access to all system entities
Cannot Customize Can be customize
No restriction on any entity Restricted access (Create/Delete) to Plugin assembly, SDK Message Processing Steps, Service endpoints, Web resource, System Job, Calendar, Business Closer, Business Unit, Currency, Security Role, Team, User Impersonation, Override Created On, Enable/Disable User, Audit Partition, Bulk Delete

 

Do you have your version of the difference? Please share in comments below.

Solutions in Microsoft Dynamics CRM

In this post we will briefly discuss about types of solutions available in Microsoft Dynamics CRM and some important points to be remembered while working with Solutions.

There are two types of solutions in Dynamics CRM:

1. Managed Solution: It prevents the solution from being modified in the environment to which it is imported.

2. Unmanaged Solutions: It allows for the solution to be modified after it is imported.

It is recommended that users deploying normal customization changes should do so using unmanaged solutions. Managed solutions are great for ISV’s who are selling their intellectual property and don’t want it to be modified by their customers.

Challenge with using managed solution for normal customizations is that there are some level of customization changes that are required to be made even in a CRM production environment, such as view, chart, and dashboard changes. Also, maintaining a managed solution requires that you preserve an unmanaged copy of that solution somewhere, since that is the only way you can update the solution or delete components.

Removal of a Managed solution does following things:

1. Removes all custom entities and components contained within the solution i.e. entities, web resources, processes.
2. Removes all customizations to system entities contained within the solution i.e. fields, views.
3. Deletes the data that is held within these custom entities and fields.

4. Any personal views/charts/dashboards in any custom entities will be lost.

 

When you import a solution, custom entity object type codes value changes in the database. If you have any reports or custom development that calls records using the OTC, this may break them. It is recommend to call hyperlinks using the entity name rather than OTC. Also you may find that some processes, such as workflows, cannot be published. This can happen because they reference a record that does not exist.

 

We usually perform a round of validation after every import of a solution just to be sure that we have deployed everything correctly. Below are some brief components that should also be validated.

1. CRM UI comparison: Verify that all custom forms behave the way they did before by checking create, update, and saving of a record.

2. Security role assignments: Import of unmanaged version of the solution restores the security roles, but it does not links them to the appropriate users as it was in the source environment.

3. Duplicate detection rules and Audit settings: Import of a solution disables auditing and un-publish duplicate detection rules.

4. Processes.

Hope this helps!

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.