Microsoft Flow for AX7 integration – Part 3 – exporting data to a local file

Let’s continue our journey looking at how Microsoft Flow can be used to build integrations for Microsoft Dynamics 365 for Operations (also known as AX7).

This latest post gives examples of how to export data from Microsoft Dynamics 365 for Operations (AX7) into your local file system in JSON, XML, or CSV formats using Microsoft Flow. It assumes that the Microsoft Flow on-premises data gateway is already installed and configured – if not, refer to Part 2 of the series.

Scenario

  1. Based on recurring schedule (trigger),
  2. Query all customer records from AX (action),
  3. Transform the data into required format (action),
  4. Write the data into a file in the local file system (action).

Defining the flow for JSON-formatted data file – step by step

    1. Determine where you want the files to go in the local file system. I have created a separate folder on my C: drive.
      Also make sure that the Microsoft Flow on-premises data gateway service has write access to it.
    2. Create a new flow and give it a name
    3. For the trigger, this time we use “Schedule – Recurrence” and set the frequency.
      image
    4. First action: “Dynamics 365 for Operations – Get records”.
      We also set some filters and order by – just because we can! Note that these expressions must follow the OData syntax.
      image
    5. Second action: “Data operations – Compose” to convert the recordset from the query into a JSON string.
      Use “value” from dynamic content to send all records into the compose action.
      image
    6. Third and final action: save the result to a file with “File system – Create file”
      Use the output from the compose action for file content.
      Microsoft Flow defnition for JSON file export
    7. All done – save the flow and wait for it to run, checking the results under My flows.
      imageThe file has appeared in the local folder:
      image

      And it contains the data:
      image

Converting data to CSV format

Just replace the “Compose” step in the previous example with “Data Operations – Create CSV table”

image

Converting data to XML format

You can use the “Compose” action for this again. This time we need to build an expression in Workflow Definition Language.

The reference from Microsoft is here: https://docs.microsoft.com/en-gb/azure/logic-apps/logic-apps-workflow-definition-language

Note that for Microsoft Flow to recognise it as an expression, it must be entered in double quotes.

The expression to use will look like this: “@xml(json(concat(‘{Customers: {Customer: ‘, string(body(‘Get_records’)?[‘value’]), ‘}}’)))”

  • body(‘Get_records’)?[‘value’] – gives us the output variable “value” from action “Get records” – this is an array
  • json(concat(…)) – takes care of adding a root object around the array, this is needed for the XML conversion, as every XML document must have a root node. Adjust the member names according to what you need in the XML
  • xml() – converts the JS object to XML

image

Note that after saving the flow, the expression can no longer be edited, you have to delete and re-create the action.

The resulting XML file will look like this:

image

 

Final notes

A similar scenario could be built with files on an SFTP server or in cloud storage, e.g. OneDrive, without using the on-premises data gateway. The corresponding file create actions are available in Flow. On-premises data gateway can also be used to push/pull data in a local SQL server, not just files. The data storage type will depend on the implementation requirements.

Next time I will show how to connect Microsoft Flow to recurring data jobs in AX7 Data Management, using generic HTTP methods to authenticate and access the REST API.

Leave a Reply

Your email address will not be published. Required fields are marked *