How to Create a Microsoft Dynamics 365 Business Central CRM Integration

While Microsoft Dynamics 365 provides a stable and fertile environment to accomplish much of what your organization needs from a daily operational and reporting perspective, there are quite a few areas that could use some improvements. When it comes to its out-of-the-box functionality, we continuously hear from clients the need for more native Microsoft Dynamics 365 CRM Business Central integrations – especially with their Microsoft Dynamics 365 Customer Relationship Management (CRM), Sales and Customer Experience.  

There is an integration option available through Common Data Service Connection Setup, but the integration is two layers: (1) the technical layer, and (2) on top of that Microsoft built an integration, the transfer of data back and forth. For our purposes, we just want to integrate the technical layer. In this blog, we’ll cover this process step by step. 

How to Set Up a Custom Coded Dynamics 365 CRM-Business Central Integration 

STEP 1:  

In the Common Data Service (CDS), instead of going through the assisted set up, simply connect the lowest level by entering information on the first page and toggling to ‘Enabled’.   

Common Data Service Connection Setup Entry Screen 

STEP 2: 

Now if you look at your Job Queue, you’ll see some pending jobs related to the integration synch.  Set all these pending jobs to ‘On Hold’.  

Status for Pending Job Queue Entries ‘On Hold’ 

The Technical Connection for the Dynamics 365 CRM Business Central Integration 

STEP 3: 

Now, open ‘Contacts - Common Data Service’. What looks like a normal table will display. If you open the page inspection tool, you’ll see it is a table called ‘CRM Contact’ with normal fields. 

The page inspection tool reveals this is a table called ‘CRM Contact’ with normal fields. 

Now if you go into your Microsoft Base Application and find the CRM Contact table, you will see it is a table object, but the table type is different. From an AL code perspective, this is just a table. In it, we can do normal table things, like ‘find’, ‘set’, and ‘insert’ all the usual stuff. But the table is actually stored in the CRM, so the table here is more like a proxy. 

CRMs have lots of customizations, so you may have added extra fields or entities in your CRM table. If you view all the fields in your table, you’ll notice that they have many more properties than a normal field in Business Central. For example, you see that field 4, ‘CustomerTypeCode’, has the property ‘ExternalName’ - that’s actually the ‘field type’ in CRM. Everything gets translated. You could just type this all out, but that would be very tedious. 

Extra Fields in Business Central CRM Integration Table 

Luckily, Microsoft has a solution. Back in the Microsoft Dynamics NAV (Navision) days, Microsoft had a PowerShell-based solution, but now we have a new tool. 

Creating a Business Central CRM Integration with the AL Table Proxy Generator. 

STEP 4: 

This helpful tool is available with the AL Language extension. Look for the ‘altpgen.exe’ tool in the equivalent folder of ‘c:\users\<username>\.vscode\extensions\<al extension version>\bin’. In the ‘bin’ folder, you will have a handful of executables (as shown below). 

Executables in the AL Language Extension bin Folder 

STEP 5: 

What we’re interested in now is the ‘altpgen.exe’ - AL Table Proxy Generator. To generate a proxy table from the command prompt, type in ‘\altpgen.exe’ followed by the parameters:  

-Project  

-PackageCachePath  

-ServiceURL  

-Entities  

-BaseId  

-[TableType]. 

The table will be generated in the folder of the specified AL project 

Image title – AL Table Proxy Generator for Business Central Integration to CRM   

Alt Text– Code showing AL Table Proxy Generator for Business Central Integration to CRM  

STEP 6: 

You can see in the screenshot below, I’ve chosen entities ‘Contact’ and ‘tabletype: CDS’ - it can be CRM or CDS, but more on that later. The Table Proxy Generator will read all the metadata for this entity, and will generate a table for us (along with a lot of errors – don't worry about that yet).  

When you go into your source code editor, you will see your new table there – in my example we have ‘Contact.al’ 

Your new table in custom code Business Central CRM integration 

STEP 7: 

Now you can create a page with the AL Page Wizard. Choose ‘CDS Contact’ as the source table and select the fields you want from the available fields. You will now have a page like any other page or table – with one very small but very important difference. In order for Microsoft Dynamics 365 Business Central to use this table, that table needs to be connected. That connection is manual. We need go into ‘OnInit’, and we need to call a ‘Codeunit’. 

Call a “Codunit” 

This connection tells the server “Please connect our session to the CRM”. Be patient, the deployment will take a minute. Once it’s finished, you will see that you have a table with the fields you chose, and with data that you can edit in Business Central. When you make changes to that table in Dynamics 365 Business Central, those changes will appear for the same fields in your CRM or Microsoft Dynamics 365 Sales.  

STEP 8: 

Now, as a test, delete that table and do it again with ‘tabletype: CRM’ instead. With this version we get a much shorter list of warnings, and instead of a table we have a table extension. This is because the Table Proxy Generator figured out that in our Base App we already have this entity mapped. Instead of mapping the entire table, it now creates a table extension with any new fields. In this example, I used the field ‘youtube’. 

YouTube field table extension 

STEP 9: 

Now if we go back change the page to using ‘CRM Contact’ instead of ‘CDS Contact’ as the source table, it’ll be using the standard table from the Base App. Since we have the table extension, the new fields will remain visible.   

But what about all those warnings?  

Let's go back to the CDS version with all the warnings and look at an example. 

Let’s look at the error: “No table found for entity ‘account’. Unable to create TableRelation for ‘Contact.AccountId’.” 

No Table Found error message 

STEP 10: 

When we look for the field ‘AccountId’, we can see it’s not found. If a field is related on the CRM side, you need to have the related entity also present. In order for the tables to link correctly, you need to put them in the same call to the table proxy generator. In this case, we need to say ‘contact,account’. You can add all the entities you need, separated by commas.  Image Title – Linking Tables with the Table Proxy Generator 

Linking Tables with the Table Proxy Generator 

STEP 11: 

Then the proxy will look at all of them and create all the linking fields. When you run this, you will have a lot more errors, but you won’t have the ‘account’ error, and ‘AccountId’ will now exist as a field. Since you need to put all the entities in the same call for it to work, I usually create a script that has all the entities in it, so I know I can always run the script. 

Congrats, You’ve Created a Business Central CRM Integration with the AL Table Proxy Generator 

That’s how you can create Business Central CRM Integration using a proxy table in Microsoft Dynamics 365 Business Central that enables you to treat data in your CRM as if it’s just Business Central data.  

At EFOQUS, when designing more demanding and complex solutions for our clients, we frequently included functionality like this, and it’s just AL code. No external code of any kind. The only thing you need to do is create that low level connection between Microsoft Dynamics 365 Business Central and your Microsoft Dynamics 365 CRM. Create a proxy table, and you’re off to the races. We hope you’ve found this quick step-by-step how to on building a simple Business Central CRM integration is helpful. 

Of course, if you have any questions or want to learn more about how EFOQUS can help you gain that ‘deep exhale’ feeling through better enabling your Microsoft technology stack to your business operations to achieve more, get in touch today!  

Author: Erik Hougaard 

Erik Hougaard,Microsoft Business Applications MVP, has been programming since he was a kid in Basic, Poly Pascal, and everything else that would run on a 4.77 Mhz CPU. Programming for IBM, Hougaard started with Dynamics NAV with “IBM-Navigator 3.00”. Working as an R&D Manager at EFOQUS Denmark, Hougaard programmed in Navision and other system in both Denmark and the USA.  Hougaard designed several Dynamics NAV add-ons and architected on the Glomaris OI .NET based framework. Hougaard now is the Managing Partner of EFOQUS Canada (rotating the CxO hats with the other partners). 

EFOQUS, the Microsoft Partner 100% focused on Dynamics 365 Business Central, develops Microsoft Dynamics 365 BC Apps available on AppSource and builds tailored customer projects with Business Central. In his spare time, Hougaard programs robots, builds 3D printers and plays the trombone.