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. I am going to use an example of synchronizing a Supplier table from a SQL Database into the Account table in Dynamics CRM.
We can though create 2 Data Sync projects which will allow us to Create, Update and importantly mark as Inactive rather then delete. The first project is a simple synchronisation between the SQL Supplier table and 'Active' Accounts of type 'Supplier' (customertypecode of 10) with deletes disabled. 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.
I simply mapped the Supplier SQL Table to the Account entity and left the EnableDelete as false. It is important to use a FetchXML filter on the Destination Data Source to remove any Inactive records and only care about 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.
We can preview and sync the SQL Suppliers table into the Account Entity.
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 map the Account entity to itself setting the source and the destination CRM's to the same Account entity in Dynamics CRM including the fetchXML and leaving the EnableDelete as 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 acccountname in the source by dragging the Suppliers table from the Connection library onto the name column and mapping it to the CompanyName in the table.
We have essentially joined the Account entity in the Dynamics CRM system to the SQL Supplier Table. If there is no corresponding record in the Supplier stable then we know it has been delete 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.
We can map these columns onto the destination statecode and statuscode.
I have removed a supplier from the source table to illustrate a deletion. Comparing A to B will show us that the missing rows in the SQL Supplier table have been set as updated to the entities.
Synchronizing the source and target will now set the Account entity to InActive.