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. After this I then created another import file in Excel that contained columns for the account (Connected From), contact (Connected To) and two static columns with the value “Primary Contact” to import them into the connection role fields. In this case I was using the generated import ID fields mentioned earlier to ensure that duplicate names wouldn’t cause problems in matching the connections to the right accounts and contacts.
After starting the data import job, I checked back to the workflow process and saw that there were now a few hundred instances of the workflow having been triggered on the imported connection records, just like I wanted.
Once all the workflows had been processed, I reviewed the list of accounts and sorted them based on the primary contact column to verify that the amount of records with a value in the field matched the number of connections I had imported. It did, which meant my primary contact import and mapping had been successfully carried out.
The final step of this process could be the deletion of the connection records that were created as an intermediate storage for the data. However, these records actually provide one added benefit that the standard primary contact lookup field can’t deliver: you can view the relationship from either the account or the contact record. The primary contact relationship on the other hand is one of the built-in hidden relationships that you can’t directly expose on the parent record’s form navigation (you’d need to add a custom sub-grid on the form).
In a scenario where the same contact is often the primary contact for a variety of different accounts this approach of using connections may be a more informative method of tracking the relationships. Not only can you see the various connections of a particular contact record easily but there’s a possibility to also track additional information about these relationships, such as free text descriptions, validity periods, timestamps on data create/update events etc. that would not be available through the standard lookup field alone.
The benefit of the primary contact lookup field is the simple data model it provides. Unlike with connections, there will always be just a single value in that field, which means it can be referenced in workflows, displayed in views, presented on the form header and so on. We could of course use both of these methods simultaneously and automate the creation of a connection record every time the primary contact field is populated with a new value. The downside of this would be that in order to enforce the “only one primary contact at a time” rule we would need to have plugins in place to delete/deactivate the previous connections. So, it’s best to evaluate the different use cases of the primary contact data, alongside all the other types of contact roles that may exist, and then decide on a data management process that best suits those needs.
Excellent article, Jukka. Just the kind of weird customer data that needs something creative to make it work, rather than the obvious.
I almost always end up with a custom field on Account and Contact for “import ID”, and often on other entities too if I may need to import additional records such as Activities against Leads, or Orders against Opportunities.
If the data is coming from a system with reliable unique values already I would tend to use this in a custom field which also gives users the chance to go back and compare records if they think they find an anomaly in the CRM version of their data. (I often get cries of “but this record has the mobile number in the fax number field! But when they check they actually see that’s where the data was in the source system. (incidentally, I tend to try and parse phone numbers so if I see a mobile number in “main phone” but not in “mobile” I would copy it to there too, and other variations on this pattern)
However, I generally don’t bother with concatenating fields together (and if I did I would just use the shorthand “&” rather than CONCATENATE, eg =”Account Name”&” – “&”Postcode” or whatever). I just use a generated unique number.
I tend to add a prefix for the entity, maybe something for which “round” of imports this is (short date is good) and the row number using a formula like =”ACC-130710-“&TEXT(ROW()-1,”0000”) which would give an output of ACC-130710-0001 to ACC-130710-9999 (you could use more zeroes if you need of course).
Add this to a column, copy it all the way down, then copy the results and “Paste Special” > Values so they stay fixed when you sort the data etc.
If Accounts and Contacts are in the same sheet, add two columns with different prefixes before splitting the data out. So on the Accounts sheet their own IDs are used as their import ID, and the Contact ID is used for Primary contact.
In your data import, simply change the field used to identify the record in the lookup from using Account Name or primary key, to using “Import ID”, and likewise from using Contact name to “Import ID”.
A minor wording error in your article: you say “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.” The first sentence should refer to problems mapping “Parent Customer” if there are multiple matching Accounts, and the second would be “Similar problems will arise with Primary Contact if several people…”
Nice Article,thanks for sharing this information.Looking forward for more posts on dynamics crm.
Dynamics CRM Developers