Making a Dynamics CRM Entity InActive rather than deleting it

I am going to use an example of synchronizing a Supplier table from a SQL Database into the Account table in Dynamics CRM.

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.

Requirements

  1. When a Supplier exists in the SQL Table but is not active or Inactive in the Account entity, create it and set it to Active.
  2. When a Supplier exists in the SQL Table but Inactive in the Account entity, set to Active and update any fields.
  3. When a Supplier exists in the SQL Table and exists as Active in the Account entity, update required fields.
  4. When a Supplier does not exists in the SQL Table and exists as Active set to InActive
  5. When a Supplier does not exists in the SQL Table and exists as InActive, do nothing.

Solution

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.

Data Sync Project 1 for standard Sync with no delete

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.

Calculated Column

We can preview and sync the SQL Suppliers table into the Account Entity.

Compare Results

Data Sync Project 2 for standard Sync with no delete

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.

Project

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.

Filter Expression to show deleted items

The filter expression just configures the source only to return rows that have nothing returned from the lookup.

Filter Expression

New statecode Calculated Column set to InActive

Calculated COlumn

New statuscode Calculated Column set to InActive

Calculated COlumn

We can map these columns onto the destination statecode and statuscode.

Schema Map

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.

Results

Synchronizing the source and target will now set the Account entity to InActive.


Can't find the answers you're looking for? Contact our support team and we'll personally get back to you as quickly as we can.