Synchronizing Business Central with Dynamics For Sales, Dataverse or Power Platform is a common need.
For the ones who worked with the Business Central Connector, you may have some awful experience with it.
However the underlying process to connect with it is not that complicated :
Both OnPremise CRM and Dataverse have the same API capability : Query Data using Web API
Simple HTTP request can retrieve datas with filter, order, join and even modify, insert or delete. It does respect the OData standard.
I’m not sure why Microsoft decided to use a weird Adapation of .Net library for BC to connect with it, therefore now Busienss Central 22 the connection does not work with OnPremise CRM anymore.
In the end, I ended up rebuilding the whole Connector in AL language fixing all annoying things I got in pasts implementations. And it was worth 🙂
It use the same architecture for error handling and modification date time, but with a large set of improvments.
Improvments vs Original
- Work both OnPremise with Windows authentication and on Cloud with OAuth2
- Access the complete list of available entities, attributes, relations and options.
No tricky powershell script, just one clic button to download the full list.
(its easy to request the metadata definition see here : Query table definitions using the Web API)
- The mapping is editable by anyone, doesn’t need development to change default Microsoft Mapping. (Events are still available to code additional rules)
- Option mapping made in one click
- Support synchronisation on lookup entity (aka SQL join), ManyToOne relation, and detect modification on joined entity. Up to 15 joins per mapping supported.
Web API join tables using $expand option - Synchronisation state is updated lively with all records details, making it easier to test and control what happened. HTTP requests are also logged.
- Synchronisation can be stopped manually and restart where it stopped. Synchronisation run in chronological order and the progression is commited.
Web API using $orderby option - Optimisation – much much faster than Microsoft Connector depending scenario
Up to 25% quicker to synch BC to Dataverse
Up to 5-10x quicker to synch Dataverse to BC - More coupling methods – Auto (default), Specific field, On the fly, Alway insert
- No more “Ignored Record”, either it succeed or fail. Failed record have attempts counter.
- User account configured for the connector can also be used to edit Data in the CRM for test purpose. The coupling system is smart enough to avoid infinite loop in bidirectionnal sync.
Unsupported features☹️
- Nested join synchronisation are not supported
- Deletion are not synchronised
- Opening Entity directly inside BC page as a proxy is not supported
- Manipulating an entity in AL as a proxy record is not supported, but have side solution
Connection
Both OnPrem Windows and OAuth2 connection are easy to configure.
The system suggest the default OData and API version to use regarding entered URL.
Customzation
Table mapping, field mapping, coupling method, direction are all editable out of the box.
Apply constant value, transformation rules, and triggers options are also configurable per field.
Events are still available inside synchronisation process, allowing developpers to code custom rules before a record is inserted or updated.
Performance
- Query only ask for mapped fields. Getting all the 250 fields of contact table to use 10 of them was not very efficient.
- The coupling system is mounted in RAM using fast List[] variable instead of slow temporary table
- The cache only load for large set of query (100+ records) making synchronisation near instant for incremental updates, even with very large tables
- Group SQL instructions using TryFunction (Direction Dataverse to BC) greatly decrease network activities
- Web API Insert/Update requests are sent by batch, decreasing network activities
Performance options for synchronisations
Mapping configuration
Options Mapping
Mapping Lookup Entity (aka left join in SQL)
ManyToOne Relation Mapping (multiple Joins on a single field)
Source Code
The project source code is not yet available but might be published on Github in upcoming weeks.