When synchronizing data into Dynamics CRM many time we would rather mark the item as InActive rather than deleting it completely. Logically making something inactive it easy but the physical difference when synchronizing items is significant. The following guide is going to use an example of synchronizing a Supplier table from a SQL Database into the Account table in Dynamics CRM.
We can create 2 Data Sync projects which will allow us to Create, Update and mark entities as Inactive rather than deleting them.
The first project is a simple synchronisation between the SQL Supplier table and 'Active' Accounts of type 'Supplier' (customertypecode of 10) with deletes disabled. This adds any new entities and updates any details that have changed.
The second project maps the Account Entity to Itself but uses a lookup to see if the underlying entity still exists in the SQL Supplier table. If the lookup returns no data from the lookup then it is missing and the status should be changed to InActive.
Map the Supplier SQL Table to the Account entity and leave EnableDelete
as false
.
It is important to use a FetchXML filter on the Destination Data Source to remove any Inactive records and only return the supplier customer type (code 10):
<filter type="and">
<condition attribute="statecode" operator ="eq" value="0"/>
<condition attribute="customertypecode" operator="eq" value="10"/>
</filter>
For completeness we created a new Calculated Column in the source to set the customertypecode to 10 in the target.
We can compare the two to preview any changes and then and sync the changes to the Account Entity.
Clicking Synchronize
and Start
will make the updates to Dynamics.
We are going to use Calculated Columns to set the statecode and statuscode to the correct values depending on whether the lookup can find the existing items in the SQL Suppliers table.
Firstly set the Account entity as the source and target (including the fetchXML statement) and keep EnableDelete
set to False.
We only need to map the accountid, statecode and statuscode as we are only interested in updating the status if we need to.
We can now create a lookup column on the acccount name in the source by dragging the SQL table from the Connection library onto the name column and setting the linking column to the CompanyName in the table.
We have essentially joined the Account entity in Dynamics to the SQL Table.
If there is no corresponding record in the SQL table then we know it has been deleted and we need to set entity to inactive.
The filter expression just configures the source only to return rows that have nothing returned from the lookup.
ISNULL(Lookup1_SupplierID)
Create the calculated column to set the statecode to inactive.
Create the calculated column to set the status code to inactive.
We now need to add these columns to the schema, mapping them with the corresponding target statecode and statuscode.
If we now compare the source to the target we can see that there is one row missing in the SQL Supplier table, that needs to be updated to inactive.
Clicking Synchronize
and Start
will set the Account entity to InActive.