I recently saw a “trick” with Dynamics 365 that I thought was quite cool. So, I decided to document it in a blog post. This must be a reasonably common scenario! Our sales team want to purge the system of some old contacts. They were given a spreadsheet listing all the existing data. And people marked the ones that needed to be deleted. Great! But how do I easily remove these contacts from our Dynamics 365 instance.

Our spreadsheet list of contacts had been exported from Dynamics 365. Meaning it contained the GUID of the contacts. (In a hidden column). And we now had a new column which said “Delete” or “Keep”. I wanted to be able to “import” this spreadsheet and after the business had reviewed remove the contacts.

Another constraint I had was that I didn’t want to make any customizations in Dynamics 365. Because then I’d need to test this process in multiple sandbox environments before the changes could be deployed. And we simply didn’t have time for that.

And finally, I wanted a “safe” approach. Meaning if I made a mistake or the business changed their minds, I wanted to be confident no contacts will be harmed in the process. And I wanted the business to be able to check the contacts that would be removed before I deleted them.

Plus (for good measure) we have loads of duplicates! So, when matching contacts, I want to be confident I am removing the correct one. Meaning a match using “Full Name” or “Email Address” would not work. I needed to locate contacts using their unique record ID. (GUID)

So, my requirement is simple! Well, you may be surprised it was.

The process is this …

1. Export contacts (or any table) to an Excel spreadsheet.
   
2. Add a column and ask the business users to confirm which records should be deleted.
   
3. Unhide the hidden columns and convert the spreadsheet to a CSV file.
   
4. Import the CSV file but creating an activity linked to my contacts! (I’ll explain how below.)
   
5. Create a view of contacts to be deleted.
   
6. Once the business is ready run a bulk delete to remove all the records.
   

I will explain each of these steps in detail below. All the steps are simple.

Step One – The Export

My first step is to export all my contacts. I’m using contacts in this example, but you could obviously adapt this concept to pretty much any Dynamics 365 table.

Obviously, the export is dead simple. Just find the records you want to export and use the Export to Excel option. I opted to create a static worksheet of all my contacts.

FYI:
I am demonstrating my example in a test system, so my volume of contacts is low. But you could follow these steps with very large datasets!

Step Two – Business Updates

In my example I added a new column to the spreadsheet. I called it “Delete or Keep”. The idea being that someone in the business would now review this data and complete the “Delete or Keep” column.

Step Three – Prepare a CSV file

I now want to prepare my data for import. You may have noticed that the exported data starts in column D. Columns A, B and C are hidden. So, in Excel I simply highlight the columns and select “unhide”.

The first column is the one I am interested in. As that contains the GUID of the contact.

Why do I need the GUID? As I could match my contacts using name! But imagine I could have duplicate contacts, then to be confident the correct contact has matched I need use the GUID as being a field that uniquely identifies the record.

Finally, I simply save the file as a CSV file. So, select “Save As” and change the file type to be “csv”. (Plus give you file an appropriate name.)

Step Four – Import creating regarding tasks

Everything so far has been super easy. In this next step I want to import my spreadsheet. But not as contacts! What I want to do is create a task linked to each contact. (The point being that the contact records will not actually be updated in anyway.)

I haven’t been able to complete my import and match using the contact GUID using the new style user interface for importing data! But all is not lost. As we can still using the old approach. I hope I’m eventually proved wrong on this comment, as matching using the GUID is a really powerful approach.

First open “Advance Settings”.

Now navigate to the “Data Management” area. Here you will find the “Imports” option.

Select the “Import Data” option and select your file. Then click next.

Now click next again!

And guess what … next again!

In the next screen we need to select the type of data to import. I have opted to create a task. But I could have used any activity type or maybe a note.

Having selected “Next” I now map the fields in my spreadsheet to those found on the task.

The subject on a task is mandatory. So, I opted to map that to the “Full Name” of my contact. (Which usefully is also a mandatory field.)

Next, I mapped the column in the spreadsheet I’d called “DELETE OR KEEP” to the description of my task.

Now the clever bit. If there is a clever bit in this “hack”! I map the GUID of the contact (held in the column called “(Do Not Modify) Contact”) to the “Regarding” field on the task.

As the regarding field is a lookup and could map to many different table types. I need to select contact.

Each of the record types has a default method for matching. Contacts would typically be matched using the “Full Name” field.

I use the lookup option to show me the available fields. Here I untick “Full Name” and tick “Contact (Primary Key)”. (AKA The GUID of the contact.)

Having selected how I want to match with the regarding lookup. I scroll down and click ok.

With my fields matched I now click next.

I get another chance to click the next button!

And finally, I check everything is ready and click submit.

You can now monitor the import process. Mine didn’t take many seconds as I had a very small number of contacts. Assuming you’ll be working with a large volume of data you may need to wait for the import to finish!

Step Five – Give the business a view

Next, I’d probably want to give my business users a view showing all the contacts I plan to delete. Just so they can confirm the list is correct.

Below you can see that if I look at my activity list, I have a task for each contact. These tasks are regarding the contact and the description of the contact includes the word “Delete” or “Keep”.

Therefore, it is simple enough to use advanced find to create a filtered list of contacts that are associated with a task that has a description of “Delete”. Below you can see that I have used the new style advanced find to filter my contacts. (The old style would have worked just as well.)

Step Six – Complete the delete

Assuming you want to continue and delete the contacts. It is now a simple process to use the bulk delete option.

Again, using the advance settings option. In the data management area you will find the bulk record deletion option.

I can create a one-off bulk delete option using the “New” button.

I simple select that I want to delete contacts.

At this point I built a query which looked for contacts with a related task containing “Delete” in the description.

Notice the “Use Saved View” option, I could have selected a view! If you have followed the suggestion above to give your users a view of the contacts you plan to delete you could now select that view. Which is a great way to be confident the deletion is going to work as expected!

If you aren’t confident of your query, the “Preview Records” option might be useful to help you confirm the correct contacts will be removed.

Once you are happy click “Next”.

We now select when the delete should run. If you are working with large data volumes, maybe you’d want to schedule this for a quiet time.

You get one final chance to check your job and click submit.

If you want to monitor your deletion. Change the view on bulk deletion jobs to “My Deletion System Jobs”. You should then be able to find your deletion routine and confirm the correct number of contacts are removed.

Anyone who knows Dynamics 365 will no doubt find this a very simple approach. (In my opinion that is no bad thing.) Hopefully you agree this is a neat and simple way to achieve our goal without any customizations. Enjoy.