Run a report that tracks attribute changes

(0) Share

Report

Posted on by Community Member

Hi Everyone

I am trying to run a query on my CRM data where the status of an lead has changed within the past 30 days (not created on or modified - the entire record). Just a specific attribute change - and the query could be modified,e.g., last 30 days, last 2 weeks, etc.

I don't see how to do this - out of the box.

I saw something about "auditing" records online but it looks pretty involved. I'm sure i'm not the only person who's asked this question or needs to query the data in this way.

thanks

Mark

*This post is locked for comments

I have the same question (0)

All responses (11)

Answers (0)

Donna Edwards 2,996 on at

Like (0)

Report

CRM out-of-the-box does not provide field level change tracking. If you want to track a specific field level change, then you could create a workflow that updates a custom date field every time the specified field changes. You can also add an activity or note with information to the record when the field is changed. This will allow you to query on the activity or note and obtain the information needed.

There are also some third-party add-ons that you can purchase for auditing. You can search on this and find offerings.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Again, thank you Donna for your response.

I found a couple of "auditing" products online, but I wasn't terribly impressed with either. I did create a workflow to create a "note" for each change, which works, but it doesn't track the "before and after" values. Of course, this is not a huge requirement but a "would be nice to have" requirement.

Mark

Was this reply helpful? Yes No
Donna Edwards 2,996 on at

Like (0)

Report

I understand, in order to track the change before it occurs, you will need to create a custom field to store that data and add it to the note. After you create the note with the workflow, add a step that updates the field with the current value on the record.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Oh that's pretty cool. I need to test this today and I'll let you know.

thanks

Mark

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Donna....I'm very familiar with custmoizing entities, but I don't "see" the one for notes. Can you guide me to the correct location for customizing notes?

thanks

Mark

Was this reply helpful? Yes No
Donna Edwards 2,996 on at

Like (0)

Report

Hi,

You'll want to add the custom field to the entity that contains the field you want to track. Basically, you are going to store the current field value in the custom field so that you can add that value to the note in your workflow. After you create the workflow note, then update the custom field on the entity you are tracking with the current value so that when the value changes again, you will have the value before it changed.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Ok I think I understand.

I'm using the Lead entity, and a field called Status (leadqualitycode) to update leads based on a custom list of statuses from a picklist we have.

So what you're saying is, to add another custom field (maybe Status Current), that has the same picklist values (?), and add it to the note in the workflow.

To recap - the steps in the workflow would be to update the Status Current field first, then Create a note in the Leads record (that contains thet new custom field). Is this correct? I'm still not clear on how to add the new custom field (Status Current) to the notes for the record update.

thanks
Mark

Was this reply helpful? Yes No
Donna Edwards 2,996 on at

Like (0)

Report

Hi Mark,

If you are trying to track changes to the field called Status on the Lead entity, here is what I am recommending as a potential solution:

Add a new field to the Lead entity - StatusPrevious or something like that that matches the Status field in type and value

Populate all the Lead records StatusPrevious field with the value of the Lead record Status field. This will ensure the field contains the current data

Create your Lead entity workflow that watches for and triggers on changes to the Lead field Status

When the Lead field Status changes, create the note in CRM and populate the note with the field values from Status and StatusPrevious

The next step is to update the StatusPrevious field on the Lead form with the value from the Status Field. This will allow you to know what the previous Status value was when the Status changes again.

I hope this makes sense. Basically, the StatusPrevious field is a holding place for the Status value before it changed.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

thanks again for the clarification.

It sounds like we'd just be using another field (kind of like "before and after") to track these changes. Since we only need the most previous one (not all history) this should work fine. It's an extra field to maintain....but I was thinking the workflow would update this field automatically?

I suppose we could also do this manually (as a second choice) - e.g., update the 2 fields when a status changes but that's sort of error prone perhaps or people would forget, etc.

The item that I'm not sure how to do is to populate the note with the two statuses - that's my disconnect here. Any advice on that would be great.

thanks

Mark

Was this reply helpful? Yes No
Donna Edwards 2,996 on at

Like (0)

Report

Ok, a couple of items:

You will update the 'after' field automatically in the workflow that creates the note or call another workflow after you create the note to automatically update the field on the Lead record.

Regarding the note, I recommend you create an e-mail, task or some other activity type. That will let you update the body field of the activity with the information from the before and after. Notes are more limited than Activities.

Was this reply helpful? Yes No

Community site session details

Run a report that tracks attribute changes

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > 🔒一 Microsoft Dynamics CRM (Archived)

Featured topics

Product updates

Community site session details

Run a report that tracks attribute changes

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > 🔒一 Microsoft Dynamics CRM (Archived)

Featured topics

Product updates