How to integrate with Case Records in Dynamics 365
Of the many use cases of Data Sync you might not be aware of how you can integrate with incident (case) records in Dynamics 365 (crm). The following blog covers the steps you need to follow to successfully integrate an external system with cases in Dynamics.
To successfully integrate with case records we need to create the incident, create activities and associate them with the incident, and be able to close incidents.
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
If you do not have Data Synchronisation Studio you can get a free evaluation edition to try this out.
Connect to Source Data
To start you need to open Data Sync and connect to your source data set. For the example we are using here we have a SQL table with a series of tickets.
To connect to this we can click onto Data Source A to open the connection window. Then navigate to SQl Database > Microsoft SQL Server (SQL Client) and enter in the connection details required. You can save the connection to the database, you will specify this in the next step, by clicking Connect & Create Library Connection.
This will then open a window that will allow you to select the database and table to connect to. Just expand the tree to find the table you need.
Please note that you only need to save the connection once per database as you will be able to access all of the tables within the database from the connection library.
If you have already saved your connection to the database, you can simply select the table you need from the connection library window to the left of the screen.
Connect to Dynamics
The next step is to connect the target to the case (incident) entity in Dynamics.
Even though in the UI of Dynamics they refer to it as case, underneath the entity is actually named incident. So this is what we need to look for in the list of entities.
The easiest way to do this if you have already configured and saved your connection to your Dynamics site is to drag and drop the incident entity from the connection library onto the target column. Alternatively you can right click and select Set as Source (A) and Set as Target (B).
If you have not yet configured your connection to Dynamics please take a look at our documentation to find out how.
Link to Customer/Account and Set Status Code
There are a couple of fields we need to specify when creating an incident in Dynamics: We need to specify the contact or account id the incident relates to, and we need to set the correct status code for Dynamics as the source codes may not match the expected values.
Link to Customer/ Account
To link the incident to the corresponding account or customer we need to do a lookup to return the Guid of that account.
We can do this in Data Sync using either Calculated Columns or the lookup function to lookup the values in each entity. In this example we have both contact and account records so we need to conditionally apply the lookup depending on whether it is a contact or account.
This can be done by using an IF statement and calculated columns to switch the lookup depending on the value in the CustomerType column. To create a new calculated column click onto the fx button in the data source toolbar.
We can then use the following function to check if the customer is a contact or account record and then do the relevant lookup.
IF(CustomerType == "account", LOOKUPB("accountid","account",KV("name",Customer)), LOOKUPB("contactid","contact",KV("fullname",Customer)))
If your case records are only to contacts then you can simplify the function and just do a lookup into the contact entity, or drag the contact entity onto the source customer column:
LOOKUPB("contactid","contact",KV("name",Customer))
If your case records are only for accounts then you can simplify the function and do a lookup into the account entity, or drag and drop the account entity onto the source customer column:
LOOKUPB("accountid","account",KV("name",Customer))
Once the column has been created it can then be added to the schema map. You should make sure to preview the data being returned to make sure the lookups have worked and are returning guids.
Set the correct Status Code
In this example the status from the source system doesn't match the status codes in Dynamics. 0 is open and 1 is closed. So we need to set wherever the status is 0 this should be 1 (1 is in progress in Dynamics) otherwise set the status code to 5 (solved).
This can again be done with a simple IF function using calculated columns.
IF(statuscode == 0,1,5).
Add this to the schema and double check the values are being returned by previewing the data.
Configure the Schema Map
The next step is to map the columns to their corresponding column in Dynamics. Make sure to include a key column that can be used to uniquely identify each record, in this example it is an ID linked to ticketnumber.
We also need to make sure the calculated columns we created are mapped correctly. So the fx_customerid will map to customerid and fx_statuscode will map to statuscode.
Compare and Sync the Results
The final step is to run the compare and synchronise the results.
Click onto Compare A > B to run the comparison. This will show you the differences between the source data and the incident entity. In this example we have 3 additions to make.
To add these records click Synchronise and then Start to begin the sync. Once this completes the case records will be added to the incident entity and linked to their corresponding account/contact.
Synchronising Activity Records with Incident
In order to sync activity records with your case records you will need to handle each activity type separately as each has it's own entity, meaning each activity type will have it's own project.
We have more details on syncing activity records in our blog Creating Dynamics 365 Activity Records. To join an activity to a case you need to supply the incidentid and type code incident.
For example, phonecalls need to be connected to the phonecall entity and you would supply the incidentid into the regardingobjectid column and incident into the regardingobjecttypecode column.
If you only have the name of the case then you can do a lookup into the incident entity to get the incidentid. The easiest way to do this is to drag and drop the incident entity from the connection library onto the case name column in the source. Then configure the lookup to target the title column from the incident entity.
You can then expand the lookup columns and add the incidentid to the schema map and map it to the regardingobjectid column from the phonecalls entity.
If you don't have a column to supply the type code incident then you can create one using calculated columns. Click onto the fx button in the datasource toolbar and enter in a name for the column. Then because we only want to enter incident when there is a value in the incidentid column we use the function:
IF(ISNOTNULL(Lookup3_incidentid),"incident", NULL())
You can then add this column to the schema map and map it to the regardingobjecttypecode column from dynamics.
Then when you run the synchronisation the activity will be updated to be linked to that specific incident/case.
Make sure to save your projects so then you can use them again at a later date, or schedule them to run using Ouvvi or the Run Tool