Monday, July 15, 2013

Bulk import and delete of Subject data - A most excellent adventure

The Subject lookup is very unique in Dynamics CRM due to the fact that it is the only lookup that is formatted in an hierarchical tree structure. The typical use, at least in the out of the box arena, is around incidents and knowledge base articles. Recently I was working with a customer who wanted to leverage the Subject lookup, and had a whole bunch of Subjects (both active and inactive) that needed to be imported. Here are a few things I learnt along the way which might prove useful to you!

Subject Bulk Import

Similar to other entities, Subject has a data map that can be downloaded from Settings/ Data Management/ Data Maps. Once downloaded, the data map looks something like this:

Most of the fields are self explanatory - so I will focus only on these 2 fields:

Parent Subject - this row needs to be filled only if your current Subject is a child Subject. Note that the Parent should either already exist in the system, of should be up above in the list so that it is created first

Feature Mask- This row, when set to 1, will display the Subject in the Subject tree. When the row value is zero (0), the subject is imported, but not visible to the user. Think of it as "inactive" subjects that might be needed due to legacy data, but is not being used currently.

Bonus Tip: If you are looking to set the feature mask to zero to import an "inactive" Subject, make sure there is no Parent Subject value filled out. If you do,  the Subject automatically becomes active, meaning it is visible in the subject tree.

Enter the needed values into the file, save it as a csv an use the native data import wizard to import the data values.

Subject Bulk Delete

So after I imported about 300 odd Subjects, it was time to go on my merry way, right? Well, not so fast. There were slight modifications to about 10% of the values, which was a decent number when you look at the total Subjects imported.

It did not help that Subject values are not available in the Advanced find, and there is also no way to export the data and make the changes and re-import the data file.

So my next thought was to just delete the imported subjects, make the changes to the source csv file and redo the import. Typically, if you go to the data import list, you will be allowed to select the rows that were processed, and delete the records from the CRM system. Unfortunately, Subject imports are unique in the sense that the delete button is not available, which means that I cannot bulk delete the imported Subjects.

Before we give up hope, notice that the Run Workflow button is available/ enabled! So if we write a custom workflow (as delete is not possible from the OOB workflow), we could run that workflow to delete the imported Subjects.

If you like to see the custom code, here is an example of how to delete a record by Dave Berry. Another excellent resource is the CRM 2011 Workflow Utilities project on codeplex written by Gonzalo Ruiz.

Once we create a wf to delete the subjects, select the list of imported subjects, click on run workflow and pick the wf created. The result is that the imported Subjects are deleted from the system, which then lets me redo the import with the correct values.

Note: If you are using subjects in your workflows, when you move your solution from one org to another, the wf will deactivate due to missing/ invalid references. If you want to avoid that, you will need to import the Subjects with their guids as the Subject value. Since you cannot export the Subject list, the only way to get the Subject guids is from the organization database sql query, unless of course you are willing to write custom code to extract all the id's through webservices.

Thanks for reading!

No comments:

Post a Comment