Make a Dynamics Enitity Inactive Rather than Deleting

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.

Parameters

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 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.

Project 1 - Adding New and Updating Existing Entities

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.

Project 2 - Setting the Inactive Status

Set up the Project

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.

Create the Lookup

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.

Filter Expression to show deleted items

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

ISNULL(Lookup1_SupplierID)

New statecode Calculated Column set to InActive

Create the calculated column to set the statecode to inactive.

New statuscode Calculated Column set to InActive

Create the calculated column to set the status code to inactive.

Update the Schema Mapping

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.