Setting Up a New Data Transfer Job (MS Dynamics to Forsta Plus Custom Data Table)

Creating the Job

1. Go to the Create job menu in the upper-right corner of the Job List and select Dynamics to Forsta Plus.
2. To define where the CRM data is to be stored, select Custom data table from the Target type drop down menu.
3. Enter a job name to uniquely identify this job.
4. Select the hub where the custom data table for storing the CRM data will be located.

You can choose between the existing hubs available to you as a Forsta Plus user.

5. To write into an existing custom table, select Existing tables (ringed below - ), and select the dataset and custom data table where you want to store the CRM data.



Figure 1 - Selecting to use an existing custom data table

To write into a new custom data table, select Create new table (ringed below - ), select the dataset or create a new one, and provide a label and name for the new custom data table.



Figure 2 - Selecting to use a new custom data table

6. Click Next to proceed.

The data is imported into the selected custom data table.

Defining Which MS Dynamics Data Fields to Import

You will define a FetchXML query to specify which data fields will be exported from MS Dynamics and imported into Forsta Plus. You can use the FetchXML builder UI to generate the query, and you can edit the FetchXML query directly using the FetchXML editor. You can also switch between the FetchXML builder and editor so that you have the ability to adjust or modify the basic query generated by the FetchXML builder. After this query has been defined, you will map the selected MS Dynamics fields to Forsta Plus fields.

1. First, decide which MS Dynamics records you wish to include when you import data from MS Dynamics to Forsta Plus:
• All records - in this mode, all records matching the FetchXML query will be imported
• New records - in this mode, any records that match the FetchXML query will be imported, but the query should be written so that only records that were created since the last transfer will be included. When selected, the FetchXML builder flow will automatically generate the query with an appropriate filter condition using the special macro called :LAST_SYNC_DATETIME.

Example FetchXML query:

<fetch mapping="logical">

<entity name="contact">

<attribute name="contactid"/>

<filter>

<condition attribute="createdon" operator="gt" value=":LAST_SYNC_DATETIME" />

</filter>

</entity>

</fetch>

Changed records - in this mode, any records that match the FetchXML query will be imported, but the query should be written so that only records that were changed or created since the last transfer will be included. When selected, the FetchXML builder flow will automatically generate the query with an appropriate filter condition using the special macro called :LAST_SYNC_DATETIME.

Example FetchXML query:

<fetch mapping="logical">

<entity name="contact">

<attribute name="contactid"/>

<filter>

<condition attribute="modifiedon" operator="gt" value=":LAST_SYNC_DATETIME" />

</filter>

</entity>

</fetch>

When the FetchXML query is executed, if you have used :LAST_SYNC_DATETIME, the macro will be replaced with the actual date-time when CRM Connector most recently ran without errors. This makes it much easier for you to setup queries that will only import records that were recently edited or changed.

If the query has never run before, then it will be replaced with a date-time at the beginning of the epoch, and so it will initially include all historical records in the query response.

2. Use the FetchXML Builder to generate your query
• Select a primary MS Dynamics query object/table from which data will be imported. When you select the object, the list of fields available on that object is shown in the MS Dynamics field list menu.
• Select which MS Dynamics field(s) should be imported using the field list menu. You can filter the list to more easily search for a field. The field names shown are the MS Dynamics API names (not the MS Dynamics field labels). Use the > button to add the fields you want to import to the list of fields to transfer.
• (Optional) Add filter criteria to be applied when selecting the records. If filters are defined, only query results matching the filter(s) will be included in the transfer. First, select the field name from the list of available fields. The fields listed are the API names of the fields on the primary query object; in parenthesis next to each field is the MS Dynamics data type. Next, select a filtering operator from the list of choices (ie =, !=, etc). Finally, add the filter criteria. You can add multiple filters, which will be all be applied to the query. In the example figure a filter is added to exclude any records unless the Address1_Country field contains the text ‘USA’

As you make changes in the FetchXML builder, the changes are reflected in the FetchXML editor. If you have selected «New Records» or «Changed Records», then an appropriate filter condition is added automatically to the query, too.

Note: Some MS Dynamics field data types are unsupported. In many cases, unsupported fields are completely excluded from the query builder to keep the user experience uncluttered (these unsupported types are: BigInt, CalendarRules, ManagedProperty, Virtual). There are also some types that are unsupported that will appear as greyed out (these types are: Memo, and PartyList). As an example, in the figure below - , the field IsPrivate is greyed out because it is an unsupported type.



Figure 3 - Defining query with FetchXML Builder

3. (Optional) Manually edit the query using the FetchXML Editor

If you want to change the query created by the FetchXML builder, you can update the query using the FetchXML editor. To switch to the FetchXML editor, click on the Pencil icon in the upper right corner of that region on the page. One reason to change the FetchXML might be if you want to import data from fields on objects related to the primary object. In Figure 132, we have added company.accountid and company.accountname, as examples of references to fields on a related object. In this case, these fields are not on the Contact object, but instead sit on the Account object, which is related to the Contact via the Company relationship. So, this is how FetchXML approaches multi-object queries.

Also, note that if you use the Advanced Find function inside MS Dynamics, you can use that UI to generate a multi-object FetchXML query and export the query syntax. Note that we only support the syntax needed for multi-object field selection and filtering, and don’t guarantee to support all syntax constructs.

If you have selected «New Records» or «Changed Records», you still need to use :LAST_SYNC_DATETIME as part of the filter conditions of your query. However, you could change it to use a different filter criteria than the default generated by the FetchXML builder.

When the Job is actually submitted, we replace the :LAST_SYNC_DATETIME with the date-time when CRM Connector most recently ran. This makes it much easier for you to setup an integration that only imports records that were recently edited or changed since the last data transfer.

4. Click the Test Query button to submit your query to MS Dynamics to test it - .

You must test the query before proceeding to the next step to map the fields selected in the query. When you test the query, it is submitted to the MS Dynamics API, and the first 200 records of the query response are displayed so you can check to see that the query behaves as expected. If there is an error in the query or with the MS Dynamics platform, a message is displayed.

Important
MS Dynamics FetchXML queries can timeout. If your query is too complex or MS Dynamics has too much load on the system when you submit the query and it cannot be completed, it will fail. This could also happen when running the query in batch mode. If the query is timing out, consider optimizing the table structure in MS Dynamics or simplifying the query. Work with your MS Dynamics administrator and MS Dynamics customer support to resolve this sort of FetchXML performance issue.

When importing data from MS Dynamics, the application will use the MS Dynamics Application user, not the MS Dynamics user logged in during interactive mode. These users could have different access privileges in the MS Dynamics CRM. So, be aware that even if the job works in the interactive mode, it could fail in the batch mode because the Application User is not configured to read the necessary data tables and fields.



Figure 4 - Editing a query with FetchXML editor and viewing sample data returned by a query

5. Once you are satisfied with your query, click Next to proceed.

Mapping the Selected MS Dynamics Fields to Forsta Plus Fields

Now that you have selected the fields for import via the FetchXML query, they need to be mapped to fields in the Forsta Pluscustom data table.

Select the Import mode from the available choices - :

• Append – in this mode, MS Dynamics data retrieved via the FetchXML query will only be added if there is not already a respondent record matching the defined key. For this option a key field must be defined in the mapping.
• Merge – in this mode, MS Dynamics data retrieved via the FetchXML query will be added if there is not already a respondent record matching a defined key, and if there is an existing record matching the defined key then the existing record will be updated with the FetchXML query results. For this option a key field must be defined in the mapping.

Figure 5 - Selecting the import mode

1. Map the MS Dynamics fields to Forsta Plus fields - .

Choose the desired mapping option for each field from the menu in the Action column.

• If you choose Create new, then you provide a new respondent field name, and the MS Dynamics field will be mapped to a new respondent field that will be created with that name.

When creating a new field, you have the choice of several custom table data field types: Open text, Numeric, Date, Single. A significant advantage of importing CRM data into custom data tables is that the data will be properly typed.

When writing into a custom data table field of the single (categorical) type, then the data must also be defined as a MS Dynamics picklist (categorical) variable in MS Dynamics. Conversion from MS Dynamics text into a Forsta Plus single (i.e. auto-coding) is not supported. This ensures that best practices are used and that data is properly categorized at the source inside the CRM prior to importing it into Forsta Plus.

• If you are creating a new custom data table (as shown below), then you will only have the option to create new fields.
• If you choose Link to, then you select an existing field name and the MS Dynamics field will be mapped to that existing field.



Figure 6 - Mapping the fields

2. Select a key
• When importing into custom data tables you must always define a key field for the records. To select a field as the key, click the Key button in the row containing the key field. It is important that the key field is unique; if it is not then this process will not work correctly. Duplicates can be created when switching between the import modes, particularly when changing a key definition, so it is recommended that you set this aspect of a job and then do not change it.

Sometimes one key is not enough to make each record unique, and a composite key field is required. For example, a composite key is useful for an annual Voice of the Customer relationship survey. In this case you might only survey the Contact once per year. MS Dynamics has a unique ContactID, however when you run the survey program over multiple years you also need to survey that Contact over multiple years, so the ContactID cannot be used as the unique key by itself. In this case you could create a composite key of ContactID + Year.

To define a composite key, click on the Key button for multiple fields, and they will be combined to form a composite key.

3. When you have completed the field mapping, click Next to proceed.

Setting Up the Task

Each job creates a data processing task within the Forsta Plus data processing environment to execute the data transfer.

Define when the data should be transferred - :

• Off (Sync manually) - the task will be setup and saved but will not actually run. Use this when you plan to start running the task later.
• Scheduled - you may choose to run the job hourly, daily, weekly, monthly, or one time only. Select the data and time when the job is to run.

Figure 7 - Setting up the task

Reviewing and Completing the Setup

Before completing the job setup, review the job information. Check that the job will run when expected. Make sure the FetchXML query includes all the fields and filtering criteria that you require. Make sure the MS Dynamics fields are mapped to the expected Forsta Plus custom data table fields, and that any key fields are properly identified. If you need to fix anything you can go back to change it in the wizard before accepting the job.

The query section is collapsible/expandable using the triangle icon, so you will need to expand that section to review the query.

Click Complete setup to save your job - .

You will then be taken to the Job Overview page of your new job.

Figure 8 - Reviewing the job