Data Import and Duplicate Detection Questions

(0) Share

Report

Posted on by Community Member

Hello and thank you for looking at my post!

I've been tasked with creating a database that's a bit more involved than I had initially thought. I have no database experience but I took a six-day training course and I have a Microsoft Dynamics CRM 2013 book to help guide me.

We track some of our employee information in an Excel spreadsheet, which we want to be able to import on a monthly basis. We want to be able to track when an employee has had a change in certain aspects of their job (work location and job title come to mind), without overwriting the previous work location and/or job title. In an effort to automate as much as possible, I'm reaching out to the community here to see if there is a better way to handle this than what I have planned.

Currently, I know that I can set up a Duplicate Detection rule that deletes any records with exact matches on Employee ID Number, Job Title and Work Location. I also know that I can then set up a Duplicate Detection job that is only run manually that checks for any records with exact matches on the Employee ID Number and then see what on the record has changed that allowed it to bypass the Duplicate Detection rule. From there, the CRM user can manually enter in the new Job Title and/or Work Location onto the form in the appropriately created field.

Does anybody know of a way that this process can be handled automatically? Could a workflow be set up to make this process streamlined? If anybody has the time and inclination to give me some insight, I would very much appreciate it! I want to create a product that takes as many manual entries out of the equation as possible.

*This post is locked for comments

I have the same question (0)

All responses (3)

Answers (1)

Suggested answer

J Matlon 1,465 on at

Like (0)

Report

Brandon,

After you do your initial import of your spreadsheet into CRM, recreate your spreadsheet by Exporting the data out of CRM. When you export, be sure to check the box to allow you to re-import.

This way, your users can work from the spreadsheet to add new users, and any modifications will merge neatly when you re-import. As long as your users aren't adding duplicate employees to the spreadsheet, you won't need to worry about duplicates.

Each month, you will need to re-export your spreadsheet (to account for any additions). But you can save an Advanced Find with your columns, so this can be done with just a couple clicks.

It would be better if your users would interact with the data at the source (CRM, in this case), instead of with these spreadsheets. But if that's not an option, its best to use these CRM-generated spreadsheets that are set up to re-import.

Was this reply helpful? Yes No
Verified answer

ThomasN 3,190 on at

Like (0)

Report
Hello Brandon, Thank you for reaching out.

I think we all have started creating database only to find out is a bit more involved than initially thought. Famous last words "It's only..."

First lets clarify duplicate detection. If you use that process you mentioned you will have duplicate records, triplicate, as many records as are imported. The difference will be only one will Active and the others will be inactive. During an import the import will just fail because it finds a duplicate according to the rules.

Now the real need is how to keep historical values of certain user fields (assuming you are updating employee data in the User entity not a custom entity). Is this correct?

I would say Business rules can populate custom fields on the user that hold historical values. But that would only work for manual updates not during a data import.

Create a custom record for historical data.

Create two fields for each User Field that you want to track changes (Old and New).

Create a process/workflow that runs on creation and change of those tracked fields.

Set a condition to check if data exists,

IF NO data exists, create a new historical entity record and populate old fields making sure to set relationship between historical entity and user.

ELSE data exists then populate New fields in the matching historical entity record.

Essentially there is a Custom Entity called HistoricalUserData, that has fields like OldJobTitle, and NewJobTitle. There would be one row for each User if you only want the last value, but if you need several iterations you will have to add date fields and populate those to track over time.

Now that is a bit of work, but is a good automated solution. However, I would recommend going back to ask more about the real business need to make sure the amount of effort matches the benefit to the business. Perhaps you will find the need is just to know if something has been changed, not really know the specifics.

If the workflow mentioned above does not solve it the other solution is a Plugin but you would need to know c# and customizing CRM using the SDK.

I hope this helps. Please verify if this did get you where you need to be.

Have a wonderful day!

Tom

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thank you for the information! I'm going to have a meeting next Friday with the Committee and we will decide then how we want to proceed. It's definitely possible that we only need to track that something has changed and not necessarily keep a history of it.

Thanks!

Was this reply helpful? Yes No