Microsoft Dynamics Integration
Integrate a knowledgebase with Microsoft Dynamics to send real-time, event-driven updates between the systems. Changes in Agiloft are posted to Dynamics using Dynamics ODATA and ADAL frameworks, and any changes on the Dynamics side are posted to Agiloft using REST APIs.
This integration is built with custom script actions, so you might need to make adjustments to suit your requirements. This article describes a basic, metadata-driven script action framework to use as a starting point to set up basic real-time information sharing between the systems.
This integration requires an Enterprise or Web Service license, so you can use REST APIs in Agiloft.
Prepare Systems
Before you can set up a link between Dynamics and Agiloft, you need to prepare both systems for integration.
Microsoft Dynamics Preparation
First, prepare Dynamics for the integration:
- Establish an admin account for Microsoft that you can use to log in to three Microsoft portals:
- admin.microsoft.com
- aad.portal.azure.com
- Your Dynamics portal, such as example.dynamics.com
- Log in at admin.microsoft.com and create an application in your Azure instance. Note the application ID.
- Assign at least one application user in your Dynamics instance to the application you created.
- In the Azure portal, create a Client Secret for the application. Use the application ID you noted in step 2. This generates a secret key and password pair.
From these steps, you should have these essential pieces before you move on to the next section:
- The base API URL of your Dynamics instance, in the form
https://<YOUR_DYNAMICS_INSTANCE>/api/data/v9.1/
- The Dynamics Microsoft Online authority, in the form
https://login.microsoftonline.com/<YOUR_DOMAIN>
, such ashttps://login.microsoftonline.com/mydomain.onmicrosoft.com
- The Application ID associated with the application user in your Dynamics instance
- The secret key and password associated with the application ID, which will be used for authentication in accessing Dynamics APIs from Agiloft custom scripts
Global Variables Preparation
Next, set up global variables in Agiloft.
- Log in to Agiloft as an admin.
- Go to Setup > System and click Manage Global Variables.
- Go to New > Short Text Constant.
Create three short text constants with Localization set to No:
Name Value dynamics_base_url https://<YOUR_DYNAMICS_INSTANCE>/api/data/v9.1/
dynamics_clientid Your application ID dynamics_msonline_authority https://login.microsoftonline.com/<YOUR_DOMAIN>
- Create one additional short text constant named dynamics_client_secret. Set the constant to be encrypted and enter your secret key and password pair as the value.
Make sure your work is saved and you can see all four variables.
Script Action JAR Files
Contact Support to receive a .zip file and extract the .jar files it contains. To make these scripts available to your
Agiloft system, move or copy the .jar files to your
Agiloft install directory at <directory>/data/<KB_name>/scripts
.
ID and GUID Field Preparation
For data to be shared smoothly between systems, each record in each system needs to store the ID used by the other system.
In Agiloft, for each table you want to integrate with Microsoft Dynamics:
- Go to Setup [Table] and go to the Fields tab.
- Go to New > Short Text to create a short text field.
- Name the field dynamics_guid. You can use a different name, but make sure you use the same name for every field you add, to make it easier to maintain in the future.
- On the Options tab, go to Require unique value? and set it to Yes.
In Microsoft Dynamics, for each entity that you want to integrate with Agiloft, create a Whole Number field named agiloft_record_id or something similar. This field holds the ID of the corresponding Agiloft record.
If you're integrating a table with Microsoft Dynamics that is linked to other tables, consider adding the dynamics_guid field to those linked sets as tablename_dynamics_guid.
Create Mapping Table
The custom scripts in the .jar files you downloaded are driven by the metadata you define. The metadata is defined in a new table in Agiloft. Each record in the table defines the relationship between a named table in Agiloft and its corresponding entity in Microsoft Dynamics.
- First, create a new table with a recognizable label and the table name
agiloft_dynamics_metadata_mapper
. Next, create the following fields:
Field Name Data Type Description agiloft_table_logical_name
Short Text
The logical table name of the table whose data is sent to Dynamics when a record is created, deleted, or updated. For example, "company" would represent the Company table.
dynamics_entity_name
Short Text
The entity name in Dynamics that corresponds to the Agiloft table. This value typically comes from your Dynamics instance, or from the administrator of the instance you're integrating with. For example, "accounts" might represent a Dynamics entity.
a2d_create_mapping_json
Text (data role Plain Text, max field length 5000 characters)
This is used by the A2D_ENTITY_CREATOR.jar script that uses Agiloft source data to create corresponding entities in Dynamics. For every field in Agiloft that you want to send to Dynamics when a new record is created in Agiloft, enter the Dynamics field name that should receive the data and the Agiloft field name that holds the source data. These key-value pairs are used by the .jar script to push data to Dynamics.
a2d_update_mapping_json
Text (data role Plain Text, max length 5000 characters)
This is used by the A2D_ENTITY_UPDATOR.jar script that uses Agiloft source data to update Dynamics entities when their corresponding records are updated in Agiloft. For every field in Agiloft that you want to send to Dynamics when a record is updated in Agiloft, enter the Dynamics field name that should receive the data and the Agiloft field name that holds the source data. These key-value pairs are used by the .jar script to push data to Dynamics.
a2d_update_mapping_json_adhoc
Text (data role Plain Text, max length 5000 characters)
This is used by the A2D_ENTITY_UPDATOR_ADHOC.jar script that can update the Dynamics entity with certain values, either to force the systems to sync values or to overwrite Dynamics data with hard-coded values. For every field in Agiloft that you want to send to Dynamics when this script runs, enter the Dynamics field name that should receive the data and the Agiloft field name that holds the source data. These key-value pairs are used by the .jar script to push data to Dynamics.
This .jar script is typically used as a utility and only run as needed, but it can be attached to an action button as well.
key_fields_to_populate_guid
Text (data role Plain Text, max length 5000 characters)
Similar to record IDs in Agiloft, Microsoft Dynamics has a GUID field that identifies Dynamics entities with a unique value. This field holds the Agiloft and Dynamics field names as key-value pairs, which are used to determine the appropriate entity match to assign an entity to an existing Agiloft record. If there are multiple Agiloft records that match, the first record is marked with the entity GUID. This information is used by the D2A_GUID_SYNCER.jar script to sync GUIDs from Dynamics back to Agiloft.
dynamics_fields_list
Text (data role Plain Text, max length 5000 characters)
This field holds the list of fields associated with the Dynamics entity. This field can be populated automatically by a .jar script, which fetches the field names corresponding to the Dynamics entity entered in the dynamics_entity_name field in this table. When this field has been populated, you can easily copy and paste the relevant fields from this list into the fields that hold JSON metadata.
GET_DYNAMICS_ENTITY_FIELD_LIST.jar
Script Action
This action runs the script in the .jar file.
Get Dynamics Fields
Action Button for above Script Action
This button runs the script action and populates the dynamics_fields_list with all the field names of the Dynamics entity identified in the dynamics_entity_name field.
Finally, set up a layout to make the table usable. We recommend this arrangement:
Common Area Metadata Fields List Agiloft Table Logical Name
Dynamics Entity Name
A2D Create Mapping JSON
A2D Update Mapping JSON
A2D Update Mapping JSON ADHOC
D2A Key Fields to Populate GUID
Get Dynamics Fields
Dynamics Fields List
- Complete any other desired setup and then click Finish to create the table.
Create Mappings
Now that you have a mapping table, you can create a record for each mapping you want to use to integrate with Microsoft Dynamics.
- Create a new record in the table.
- In the Agiloft Table Logical Name field, enter the name of the table, such as
people
. - In the Dynamics Entity Name field, enter the name of the Dynamics entity, such as
contacts
. - Go to the Fields List tab and click Get Dynamics Fields. This fetches all the field names of the Dynamics entity "contacts" and adds the names to the Dynamics Fields List. Now that you have a complete list of Dynamics fields, you can start building key-value pairs in the JSON string fields.
Go to the Metadata tab and locate the A2D Create Mapping JSON field. Fill in the field with the Dynamics field name as the key and the Agiloft field name as the value in a
"key":"$value",
format. Add a key-value pair for each field you want to map, such as:
Note that the last line connects the Dynamics record and the Agiloft record by linking their IDs.When you finish, paste your work into an online JSON validation tool to make sure your syntax is correct. If you try to run the integration with incorrect JSON syntax, it might not work correctly.
Repeat step 5 for each JSON string field. Make sure to include only the fields you want to sync during the process that uses the field; for example, the A2D Update Mapping JSON field is used when a record is updated, so include only the fields you want to sync when records are updated in Agiloft.
When you complete all the JSON string fields, go to the setup wizard for the table you want to integrate, such as Setup People. To make the JSON strings usable, you need to create actions that use the scripts in the .jar files.
- Go to the Rules tab and click New.
- Name the rule A2D Create Entity.
- On the Rule Type tab, apply the rule when a record is created, edited, or deleted.
- On the Condition tab, apply the rule only when a record is created.
- On the Action tab, click Create Script Action.
- Name the action A2D_ENTITY_CREATOR.jar and enter a description. Make sure the syntax of the name matches the .jar file name you uploaded to your Agiloft script directory. This script automatically searches for the metadata mapping table you created and pulls the a2d_create_mapping_json field contents to make the necessary changes in Dynamics.
- Click Finish to create the action and then to complete the rule.
Repeat these steps for the other scripts: A2D_ENTITY_UPDATOR.jar, A2D_ENTITY_UPDATOR_ADHOC.jar, and D2A_GUID_SYNCER.jar.
If you need to sync attachments between systems, you can use A2D_CONTRACT_ATTACHMENT_SYNCER.jar if you set up the Dynamics side as a Notes field. File fields in Dynamics do not allow syncing.
Standard Dynamics Entities
As a reference point, here are the names of some common CRM, ITSM, and Contract Management entities in Microsoft Dynamics that you might want to sync with:
CRM | ITSM | Contract Management |
---|---|---|
Account | Incident | Account |
Contact | Problem | Contract |
Campaign | Change | Document |
Lead | Article | Contract Template |
Opportunity | Asset | |
Task |