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.

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:

1. 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
2. Log in at admin.microsoft.com and create an application in your Azure instance. Note the application ID.
3. Assign at least one application user in your Dynamics instance to the application you created.
4. 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 as https://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.

1. Log in to  Agiloft as an admin.
2. Go to Setup > System and click Manage Global Variables.
3. Go to New > Short Text Constant.

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

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

1. Go to Setup [Table] and go to the Fields tab.
2. Go to New > Short Text to create a short text field.
3. 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.
4. 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.

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.

1. First, create a new table with a recognizable label and the table name agiloft_dynamics_metadata_mapper.

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

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

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

1. Create a new record in the table.
2. In the Agiloft Table Logical Name field, enter the name of the table, such as people.
3. In the Dynamics Entity Name field, enter the name of the Dynamics entity, such as contacts.
4. 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.

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

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

1. Go to the Rules tab and click New.
2. Name the rule A2D Create Entity.
3. On the Rule Type tab, apply the rule when a record is created, edited, or deleted.
4. On the Condition tab, apply the rule only when a record is created.
5. On the Action tab, click Create Script Action.
6. 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.
7. Click Finish to create the action and then to complete the rule.

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