The Import Wizard in Dynamics CRM can do a lot more than what may initially seem possible. I’ve covered some of these features in a previous article called CRM 2011 Data Import Wizard in Practice. Among these capabilities is the possibility of importing records to different entities that have multiple relationships connecting them in both directions, not just the simple parent-child relationship pointing from one entity to the other.
A typical example of such a relationship would be the Primary Contact of an account. The account is a parental record to all the child contacts, but one of these contacts may however have a 1:N relationship back to the account and be presented in the Primary Contact lookup field on an account form. If you are importing both the account and contact columns in a single file (such as an Outlook contacts export) then mapping these relationships should only be a matter of mapping the right fields. If you have two separate files, then simply zipping them up into a single file will allow you to map both entities in the same import process.
One of the problems that you may encounter during such an import process is that there are multiple matching records for the mapping fields. If you have more than one account record called “Litware, Inc”, to for example represent different regional offices of the company, mapping the primary contact by account name won’t work. Similar problems will arise if several people have the same first and last names. Therefore it’s a good practice to always generate a unique ID for each record before importing it. You can construct the identifier field in Excel with the Concatenate function and combine several fields into a single Import ID string (account name + address 1 city, for example) which you then map into a temporary field in CRM. You can use this field as the lookup reference to the related entity instead of the standard primary field when importing data.
Appending Existing Records
In a recent import task I was once again faced with the Primary Contact issue. Only this time the account data that I needed to map the contacts into was already in the CRM database. As these were new contact records being imported, my first thought was to create a workflow rule to be triggered from the create event of a contact record. Using some temporary contact field to store the primary contact flag into, like “governmentid = PrimaryContact”, and then searching for this value in the workflow rule would have allowed me to start a record update step for the parent account of the newly imported contact. In the update step I could just state that the account’s primary contact would be the contact that the workflow process instance has been initiated on.
Fortunately I looked through the source data once again before proceeding any further, as that revealed a flaw in the assumptions behind the above workflow rule logic. A single contact was sometimes the primary contact for more than one account. Also, there were occurrences where the contact wasn’t the primary contact of its own parent account. The relationships were therefore more complex than what a single workflow rule could cover.
This doesn’t mean that leveraging workflows was out of the question, though. To enable the creation of multiple relationships from a single imported contact record I just needed to have an intermediate stage in my process, to store the data in CRM. What this requires in practice is that you first import the data into a different entity, then trigger the update step from that record into the actual record you want to append with new information.
One option would have been to create a new temporary entity just for the sake of getting the data imported correctly. However, since these were account and contact records for which we wanted to link the imported data, there was already a logical place available in the default data model of Dynamics CRM: connections. It’s in fact the perfect entity for importing any relationship data into, as the two parties of the connection can be references to any entity type that has connections enabled for it, meaning several default entities and any custom entity you’ve created. Therefore we could cover several different import scenarios with connections and not have to go into system customizations to add relationships to other entities.
The Import Process
First I had to add a new connection role for “Primary Contact” (step 1) to identify the connection records that I want to run my workflow process on. As this role will be used exclusively between account and contact records, I specified them as the available record types for the role (step 2). Also, I always tend to put the same role value on the “other side” of the relationship to keep the data consistent and simple to view/search for, so I set this new role to be its own matching connection role (step 3).
Then I proceeded to creating a new workflow process that would be triggered on the create event of a new connection record. In the workflow rule I specified it to run only on connections that have the Primary Contact connection role. I also wanted to validate that the Connected From and Connected To entities are mapped the right way around in the connection record, so I simply check that the account and contact records behind each relationship contain data. Without these conditions being met, the update step wouldn’t produce any meaningful results anyway.
In the update step I mapped the Connected To contact record into the Primary Contact field of the Connected From account record.
Now we were ready to start the actual import work. Since the primary contact relationship data was handled with a separate entity, I first imported the contact records through the normal process. (more…)