Send query results to Microsoft Dataverse

You can send data from Amperity to Microsoft Dataverse. First build a query that outputs a table with rows that match the schema of a table in Microsoft Dataverse. The column names, data types, and column order must match the schema that is defined in Microsoft Dataverse. Then use an orchestration to send that data from Amperity to Microsoft Dataverse.

Send data tables from Amperity to Microsoft Dataverse.

This topic describes the steps that are required to send table rows that match a table schema to Microsoft Dataverse from Amperity:

  1. Build a query

  2. Add orchestration

  3. Run orchestration

Note

Microsoft Dataverse must be enabled as a webhook destination before you can configure an orchestration to send query results.

Build query

Build a query that returns rows that match the schema of a table in Microsoft Dataverse. For example, to send a list of first names, last names, and email addresses from Amperity to a table in Microsoft Dataverse, where all columns in that table have a cr812_ column prefix and different column names from the ones in Amperity use a query similar to:

SELECT DISTINCT
  given_name AS cr812_firstname
  ,surname AS cr812_lastname
  ,email AS cr812_email_address
FROM Merged_Customers

Note

A Lambda function running in AWS Lambda will validates that rows match the table schema before sending data to Microsoft Dataverse.

Add orchestration

An orchestration defines the relationship between query results and a destination, including the location to which those query results will be sent and the frequency at which the orchestration will be run.

To add an orchestration

  1. From the Destinations tab, click Add Orchestration. This opens the Add Orchestration dialog box.

  2. From the Object Type drop-down, select Query.

  3. From the Object drop-down, select the query for which results will be sent to Microsoft Dataverse.

  4. From the Destination drop-down, select a destination that is configured for sending data to Microsoft Dataverse.

  5. From the Data Template drop-down, select a data template.

  6. Verify all settings.

  7. Set the workflow to Manual. (You can change this to automatic later, after verifying the end-to-end workflow.)

  8. Click Save.

Run orchestration

Run the orchestration manually to validate that it works.

To run the orchestration

  1. From the Destinations tab, under Orchestrations, open the    menu for the Microsoft Dataverse orchestration, and then select Run.

  2. The Status column for the orchestration will update to say “Waiting to start…”, after which the notifications pane will update to include a notification that shows the current status.

  3. When the orchestration has run successfully, the status is updated to “Completed”.