Use Workflows to Audit Deleted Records

Sometimes What’s NOT there is What’s Important!

In Dynamics CRM it’s possible to delete Opportunity and Case records. (This might sound trivially obvious, but in fact there are many kinds of records that cannot be deleted, such as User, Business Unit, and others.) Even though a well-designed implementation will use security roles to guard against the accidental deletion of important data…well, you know what they say about the best laid plans of mice and men. If you’ve ever been in a situation where a number of important records were deleted, you’ll be glad to know about the technique I discuss in this article!

In Dynamics CRM 4.0, one of the big improvements to workflows was the introduction of the “Record is deleted” trigger. I appreciate from (painful!) experience the value of this trigger, but I also know it can be hard to understand at first.

You might wonder, for example, if a workflow can use this trigger to roll back (”un-delete”) a deleted record. It can’t.

So if you can’t un-delete a record, what good is the delete trigger? Plenty, it turns out, since a workflow instance triggered by a delete event contains all of the values of the deleted record! The trick is to write them out to some kind of “audit” record so you don’t lose all of the data. I’ll show you an example of that here for the Opportunity entity, and you can adapt it to your specific business requirements.  

The approach I will take here is to create a custom entity that can be used to store information about deleted Opportunity records. As you’ll see, most of the work is in creating the custom entity; once the audit entity is created, the workflow is pretty simple!

Part 1: Design and Create the Audit Entity

Start by selecting from the 36 attributes available in the un-customized CRM Opportunity entity and determining the ones you want to store values of in your audit trail. This will obviously be determined by your business requirements, and will generally be a longer list than the one I propose in this table:

To create and configure the custom audit attribute, follow these steps:

1.  On the Dynamics CRM site map, click Settings, then click the Customization tab. Click Customize Entities, then click New.
2. Name it “Opportunity Audit”, and (optionally) change the name of the default primary key label to “Topic”, so it matches up to the corresponding attribute in Opportunity.
3. Specify that the entity is to be organization-owned, that it does need support for Notes and Activities, and that it will only appear in the Settings area. Click Save. After the custom entity is saved, your entity form should look like the following:

4. After the entity is created, the next step is to add the required custom attributes. Of the six attributes indicated above in, one (Name/Topic) is already created, three (Potential Customer, Modified By, and Owner) will be created by creating custom relationships, and the Estimated Close Date and Estimated Revenue attributes need to be created “by hand”. The next figure shows what a subset of the Opportunity Audit attributes (the ones I created) look like once they’re created.

5. Rather than go through a detailed step-by-step recitation of how to create each one, I’ll provide high-level instructions here:

a. To create Est. Close Date and Est. Revenue, simply click New, enter the Display Name and select the appropriate type. Remember what you want to do is have these match up exactly to the corresponding attributes in Opportunity, so if necessary, open it up in another window to make sure!

b. The three lookup attributes (Deleted by, Owned by, and Potential Customer) provide a good example of Dynamics CRM’s custom database features. Both “Deleted by” and “Owned by” should be filled in automatically, respectively, with the user who deleted the record, and the record’s owner at the time it was deleted. In CRM 4, you can create a new relationship between a System entity like User and a custom entity like Opportunity Audit to get this done. Since we will potentially have many audit records for a single user, click N:1 Relationships, and then click New Many-to-1 Relationship. For the “Deleted by” attribute, you want the form to look like the following figure. The “Owned by” will be identical (except for the name of the attribute), and the “Potential Customer” needs to be a lookup against either Account or Contact. I’ll use Contact for this example.

6. After creating the custom attributes you want to track, you can add the attributes as fields on the entity’s form. This isn’t a real requirement, but it’s helpful to have a form to look at in certain circumstances and it doesn’t take much work, so I generally do it.

7. Finally, save and publish the Audit Entity.

 Part 2: Build the Delete Workflow

In this example, all the hard work’s essentially done. To build the workflow, follow these steps:

1. Create a new workflow with a scope of Organization and automatically triggered on the “Record is deleted” trigger. It only needs one action, which will create an audit record:

2. To populate the Opportunity Audit record with the values you need, click Set Properties.

3. Use Dynamic Values to configure the values you want. I often use a mix of static text and the audited record’s “Topic” field to construct a sufficiently eye-catching Topic. Remember this is supposed to flag deleted records and make them stand out, so in this example I included some “***” asterisks on the front of the Topic field.

4. Click Save and Close, then Publish the workflow.

Some Closing Thoughts

Test it to make sure it works as expected. The simplicity of a workflow like this one is due to its singleness of purpose, so to speak. An alternative approach is to create a more general audit capability, where you write out audit records not just for deleted records but also for important changes (changes in Status, changes in estimated close dates and revenue, etc.)

If you’re just starting out, I recommend you start with the simpler approach I reviewed here. Here’s an example of a problem you can run into with a more general approach:

Suppose you create your audit entity as a child entity of Opportunity (Opportunity has a 1:N relationship to the audit entity). This works fine if you write out an audit record on an existing Opportunity record. But if you want to keep track of deleted records it won’t work: you can’t create a child record related to a deleted Opportunity record…since the record isn’t there anymore! Not that there isn’t a way to solve this problem…but it is more complex!

If you find this useful…

This example is taken from my book on Dynamics CRM workflows, cleverly titled “Workflows in Microsoft Dynamics CRM 4.0″. It’s available for purchase on Amazon and Lulu.com (cheaper on Lulu). Here’s the link if you’d like to purchase the book: