Tuesday, 28 June 2011

Record Versioning using Self-referential relationships in Microsoft Dynamics CRM 4.

On of the best new features in Microsoft Dynamics CRM 2011 is the built in data audit system. By simply checking a few boxes, you can track any changes made to any type of record. However, what if you want to track complete copies rather than merely the edited data? What if you want to be able to restore past versions of a record with just a few clicks?

We can do this with one self-referential relationship and a couple of workflows for the entities you'd like to apply it to. I'm demonstrating this on MS Dynamics CRM 4 on the Contact entity, but the same technique should work in CRM 2011 and on any other entities.
There are three basic steps:

  1. Create a self-referential relationship for the entity
  2. Create two workflows: one to create a backup version (or 'restore point', if you prefer), and one to do the restoring.
  3. Minor tweaks to make it all work smoothly.

Let's get down to it.

Creating the Self-Referential Relationship.
Open up the entity you're going to customize, and create a new 1:N relationship. The only difference between this any any other relationship is that in this case, both the Primary and Secondary entities are set to 'Contact'.

You can leave most of the settings as Default. I labelled it  'Parent Version' and 'Versions' respectively; but that's just a matter of choice. An extra point: you should probably set the 'Type of behaviour' to either Parental or Configurable Cascading (see the notes below.)

We don't have to worry about mapping, as CRM 4 handles self-referential relationships beautifully. Add the 'Parent Version' field somewhere to the main form, and let's continue.

Workflow 1: Creating a new version
Again, pretty standard - an initial check condition, and then we create a child Contact.
  • Set the workflow to start when the record is create and when record attributes change (whichever attributes you want to keep track of, I selected them all). By starting it when the record is created, you get a clean copy to start with.
  • If you have any attributes that change VERY frequently - exchange rates, daily variables - you might want to leave them unselected to avoid dozens of versions a day.
  • You need a check condition to make sure that the 'Parent Version' field is empty. Without this, the workflow will trigger again on each new version being created and create an infinite loop.
 Now the boring part: match up each field from the parent contact with the child contact. The most important is highlighted red - for this to work, you need the "Parent Version" field set to the parent contact.
I'm working off our own customized CRM here, so you can ignore any variations from the standard Contact entity.

Workflow 2: Restoring  to a previous version.
  •   This workflow is going to be run on a specific child version, so we have to update the parent record within the workflow.
  •  Again, we have a check condition - but this time it's to make sure the 'Parent Version' contains data. That way, we won't have any issues if it's run on an incorrect record.
  •  It should be set to run on demand only.
  •  This will trigger the first workflow to run again and create a new version.
 Again, just match each field in the record we're updating with the field in the current entity - Make sure to leave the 'Parent Version' field blank this time.

Publish both of the workflows, and we can move on to tidying things up a bit.

Now we've got the version creation and restoration complete, we'll have dozens of extra records floating around. The quickest fix for this is to set up some new views - here I've replaced 'My Active Contacts' with 'My contacts' which performs the same job but filters out any extra versions.

You'll probably want to modify the associated view to sort by date as well.

Now in your contact record on the left, you can view all past versions and it should look something like this.

When you choose the 'Run Workflow' button with any previous version selected, you get this:

Clicking okay will replace the current record with the selected version. By now every time a contact is modified it will generate a new copy of itself and you should be able to easily roll back any record to a prior version.

  • You have to modify existing reporting/advanced find to take this into account, as it would dramatically skew the results. Simply ignoring records where the 'Parent Version' field contains data is enough.
  • The self-referential relationship should be set to 'Parental' or 'Configurable Cascading' if possible, so that all versions are deleted if the original is. If you want to retain copies even when the original is gone, stay with referential relationships.
  • Because this will create a whole new record whenever triggered, you may want to avoid using frequently-updated attributes to trigger it - for example, I would avoid using record status as a trigger if the status changed half a dozen times each day.
  • If you have duplicate detection running for contacts, you'll need to modify your detection rules to take this into account.
  • While I've stuck with a basic solution here, you can make this much smoother and nicer if you're okay for working with javascript, editing isv.config, and creating custom workflows - the logical next step is an 'audit' text field to record the changes made since the last version.


    1. Excellent post very descriptive and lays it out simply. only problem now - migrate from 4.0 to 2011!

    2. Migrating our CRM system from 4.0 to 2011 is on my to-do list for June/July this year. If it goes according to plan, I'll update this post (and probably add a few more I never got around to)


    Note: only a member of this blog may post a comment.