Making Dynamics 365 Items Inactive rather than Deleting them
When integrating with Dynamics you might need to mark items that are no longer needed as inactive rather than deleting them completely. Logically making something inactive is easy but the physical difference when synchronising items is more involved.
The following blog will discuss how you can set entity items to inactive rather than deleting them. We will be synchronising a Supplier table from a SQL database into the Account Entity in Dynamics 365.
Requirements
Before getting started you need to ensure you have the following:
- Windows 10 or Windows Server
- Downloaded & Installed Data Synchronisation Studio
- Access to Dynamics 365
- Configured your connection to Dynamics 365 in Data Sync
Considerations
If you are regularly updating activity records in Dynamics then we would recommend containing all of your projects within an Ouvvi project. This will enable you to run each project on a schedule and run them in sequence. You can additionally add a step to notify you that the project has completed and the details of what changed.
You can find more details on the project status report step in our documentation. Alternatively you could also use the Run Tool to schedule your projects.
These are the parameters we need to consider and set to make an entity item inactive or active:
- 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.
- When a Supplier exists in the SQL Table but Inactive in the Account entity, set to Active and update any fields.
- When a Supplier exists in the SQL Table and exists as Active in the Account entity, update required fields.
- When a Supplier does not exists in the SQL Table and exists as Active set to Inactive
- When a Supplier does not exists in the SQL Table and exists as Inactive, do nothing.
To do this we will create two Data Sync projects, which will allow us to create, update and mark items 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 (EnableDeletes=False). This adds any new entities and updates any details that might have changed.
The second project maps the Account entity to itself but uses a lookup to see if the underlying record still exists in the SQL Supplier table. If the lookup returns no data then it is missing and the status should be changed to InActive.
Project 1 - Adding New and Updating Existing Records
We need to start by creating the project that will add and update records within the Account entity.
To do this open Data Sync and connect the source to the Supplier table (SQL) and the target to the Account entity in Dynamics. Make sure to leave EnableDelete set to false, which is the default value.
Filter Target for Active and Supplier Records Only
We now need to use a FetchXML filter on the target data source (Dynamics) to remove any inactive records and to only return the supplier customer type (code 10).
You can add a FetchXML filter to the target by entering the filter into the FetchXMLFilter property field found in the target connection properties (below the columns). Click onto the ellipsis to open the editor and enter your FetchXML statement.
An example of the FetchXML filter you could use is:
<filter type="and">
<condition attribute="statecode" operator ="eq" value="0"/>
<condition attribute="customertypecode" operator="eq" value="10"/>
</filter>
This filter will be applied when we run the compare later on. You can check it is correct by previewing the target data using the preview button in the datasource toolbar.
Set the CustomerTypeCode
The next step is to set the customertypecode to 10 to set the records as Suppliers within the account entity. To do this we can create a calculated column in the source by clicking onto the fx button. Then enter in a name for the column e.g.customertypecode, set the data type to System.Int32 and enter in 10 for the expression.
Clicking OK will create the column which can then be added to the schema map.
Configure the Schema Map
We now need to configure the schema map so that the calculated column we just created is linked to the corresponding column in Dynamics (customertypecode). Then continue to add and map the columns from the source table to the Accounts entity in Dynamics as you need, and make sure to add a column that is unique to each record e.g. in this example is the SupplierID which is linked to the accountnumber field.
Your mapping should now look similar to this:
Compare and Synchronise the Results
The next step is to run the comparision to preview the differences between the source SQL table and the target Account entity.
Do this by clicking onto the Compare A > B button in the toolbar. In the screen capture below we can see that there are 29 records to add.
Now click Synchronize and Start to apply the updates to Account Entity.
Make sure to save the project so that you can use it again.
Project 2 - Setting the Inactive Status
We will now create the second project which will set the status to inactive on records that do not exist in the SQL supplier table.
Start by setting the Account entity from Dynamics as the source and target in your Data Sync project, and keep EnableDelete set to False. This will prevent any accidental deletion of your data.
We need to add the FetchXML statement we used in the first project, so that only the supplier records are targeted in the Account Entity. If we didn't filter the records then it would set all account records not included in the supplier table to inactive.
<filter type="and">
<condition attribute="statecode" operator ="eq" value="0"/>
<condition attribute="customertypecode" operator="eq" value="10"/>
</filter>
Make sure to add this to the source and target datasets.
Create the Lookup
The next step is to create a lookup on the account name in the source by dragging the supplier table from the connection library onto the name column in the source.
You then need to define the lookup configuration setting the target lookup column to be the CompanyName from the SQL table.
Here 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 to only show deleted items
We need to then add a filter to the source data to only return rows that have nothing returned from the lookup.
ISNULL(Lookup1_SupplierID)
Set State Code to InActive
We are now 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.
The state code field is expecting a string value and the value for inactive is 1, so we can do this by adding a new calculated column that returns 1.
Set Status Code to InActive
Then repeat the same steps to create a calculated column to return the statuscode. The status code field is a Int32 field and the value for inactive is 2.
Configure the Schema Map
We now need to configure the schema map and add the calculated columns we just created making sure to join them with their corresponding target columns statecode and statuscode.
Make sure to define a key column that can be used to uniquely identify each record, in this case accountid. You should only need these three columns and your schema map should now look similar to this:
Compare and Synchronise the Results
To finish we need to compare the source to the target by clicking the Compare A > B button in the toolbar. We can then preview the data to check the results by clicking onto the different result lines.
In this example we can see that there is one row missing in the SQL Supplier table (showing as an update) so that record needs to be updated to inactive status. The 29 records showing as delete items are the suppliers that exist in the SQL table but have been filtered out for the synchronisation. As deletes are disabled they will not be affected by this synchronisation.
To make this record inactive click Synchronize and Start to apply the changes.
You can now save this project and schedule both projects to run in sequence using either Ouvvi or the Run Tool.
For more examples on integrating with Dynamics take a look at our solutions page. Alternatively if you have any questions please send us an email at support@simego.com.