Read This Before Importing Contacts to Business Central

With the recent “Edit in Excel” and enhancements to Configuration Packages, importing data into a new Microsoft Dynamics 365 Business Central (BC) Company is more streamlined than ever before. But I recently encountered issues with the Business Central Contact List during the data migration phase of a project. The issue: thousands of Contact records that were not intended to be there. What made matters worse? At first attempt, it seemed near impossible to delete the records. One more tick in my daily grind where I scream to the BC gods, “Bring back my Object Designer!”

Normally, a little bit of research on the Microsoft Partner Portal, forums or Microsoft documentation will give some good tips and tricks for handling this massive amount of records. There is some great intro training on Marketing and Business Central Contacts in the MS documentation here. Sadly, there is nothing on my current dilemma.

But, of course, defeat is not an option for BC consultants. And luckily, I know the ins-and-outs of the Contact (Marketing) functionality and table structure to assist me in this troubleshooting. Here are some key facts in order to grasp the entirety of what happened.

Fun Fact #1

Contacts are automatically created when a Customer, Bank or Vendor is created. THIS IS A SETUP THAT CAN BE TURNED OFF. If you don’t want to read any further, all you need to know is this: turn off this setup before you start importing new master records. Go to the Marketing Setup table and remove codes assigned to Bus. Relations. (At the end of this article I describe how to properly get contacts into the system.)

By turning this off, I am preventing each customer, vendor and bank record from creating a matching contact record.

Fun Fact #2

The Hidden Tables! Good ole Config Package is our only insight into the once beloved ‘Object Designer.’ Here we can view the tables behind the scenes that cause the linkage. The key tables are Business Relations (5053) and Contact Business Relations (5054).

Business Relations Table

This table provides the types of relations that can be assigned to a contact. However, the standard is to a Customer, Vendor and Bank Account. In the Marketing Setup discussed above, it is this table that is the Lookup for relation code setup.

Contact Business Relations Table

The golden linking table. Any contact that has been assigned a Business Relation will create a record in this table. There can be unlimited business relations created and unlimited assignment to a contact. The table is simple in that there are only four fields:

1. Contact No. = the unique ID from the Contact Table (the Contact No.)
2. Business Relation Code = Is the Contact a Customer, Vendor or Bank? (This also involves an additional table.)
3. Link to Table = the table associated with the Relation Code, i.e., if the contact is associated with a customer then the relation code is “CUST” (and this code is associated with the additional table referenced in #3)
4. No. = the actual unique ID for the table assigned in #2 and #3

Fun Fact #3

Understand the rules of change when a contact is linked to another master record. In basic Contact documentation, you learn that if an address (or any other info) is changed on the master record OR the contact record, it will update the linked record. However, if you delete the master record or the contact record, the linked record will remain.  During the deletion of a record, the link record created in table 5054 is deleted.

How did we get here again? 

Fun Facts #1 and #3 are how we got the thousands of records. Data migration of Customers and Vendors failed. This is an easy fix: delete and try again. This works great except each new re-load of master records subsequently creates even more contacts.

Now what? 

Obviously, all we need to do is delete the contacts! Sadly, the select all rows and delete function in BC is not allowed. Prior to the new Edit in Excel, I would have to use a Config Package and select the “Delete Table Records” option for a table. Next, I would delete all records from the import file thus importing a blank table. In most cases, the delete records trick works. But no matter how I turn off validation, the system is not allowing me because the link record in table 5054 exists (and yes, I tried to delete those records too). I suppose it would work to delete the master records, which in turn deletes the link record. But then I have to re-import the master records.

Thankfully, the validation necessary to delete the link record is coded into the “edit in Excel’ functionality. Therefore, if I Edit in Excel the entire Contact List, and then delete all records in Excel and publish back, I now have No Contacts! Defeat is not an option.

Takeaway and Last Steps

If you start every implementation with the Marketing Setup codes removed, then you will avoid this issue. Just before go-live, if the client wants to have contacts setup in the system, then change the setup back.

Because Contacts were not setup and linked to Customers and Vendors, you can run the Create Contact Task at this time and all the master records will auto-create a contact linked back to the master contact.

Problem averted. Success!

What can we do for you?

A great Microsoft Dynamics 365 Business Central consultant draws from experience and understands not just the platform itself but also how it works with the other solutions – like email, CRM, and collaboration tools – that drive your business. Our team can provide that insight if you want to support your existing BC solution, have questions about Business Central contacts, or need to implement a brand new ERP. Drop us a line!