As Microsoft turns the tanker towards its "One Dynamics One Platform" vision, functional D365 consultants also need to begin that pivot. Power Apps and Power Automate offer a multitude of features, connectors, and automation tools to manipulate D365 dataverse, modules, and workflows. In this article, I will demonstrate Power Automate's ability to send real-time data to third-party endpoints.
1. Activate entities in Power Apps
a. Open Power Apps
b. Tables > All > search for “Available Finance and Operations Entity”
i.
ii. Toggling “Visible” to Yes will open that entity up to be interfaced with Power Automate
iii. Hitting + ### additional rows will bring a long list of all available entities in D365. This list will could a long time to load and scroll.
2. Create a Power Automate flow
a. Flows > New flow > Automated cloud flow
i.
b. Name your flow
c. Search for “dataverse”
d. Select “When a row is added, modified or deleted”
i.
e. Select it and click “Create” in the bottom right
i. “When a row is added, modified or deleted” actively monitors the virtual entity table that is selected at the beginning of the flow. When a row/record on that table is created, edited, or deleted it will trigger the flow. The information passed along by the trigger will depend on the entity table.
3. Configure the flow’s trigger
a. Change type – defines how the virtual entity should be monitored by the trigger
b. Table name – select the D365 virtual entity that should be used in the flow. Table names that end in (mserp) are tied to D365 tables.
c. Scope – limits which rows can trigger the flow
i. Organization – actions are taken by anyone within the environment
ii. Business unit – actions are taken on rows owned by anyone in your business unit
iii. Parent: Child business unit – actions are taken on rows that are owned by anyone in your business unit or child business unit
iv. User – Actions are taken on rows owned by you
d. Select columns – comma-separated unique value names. The flow will trigger if any of them are modified (not used for create or delete). To find the names of certain columns either navigate to the table or trigger the flow from D365, outlined below.
e. Filter rows - are used to specify the condition on which the trigger should fire. This makes sure that we do not exceed flow execution limits. This is done using OData filter expressions and scheme names. To find the names of certain columns either navigate to the table or trigger the flow from D365, outlined below.
4. Locating the Schema (Option 1)
1. Navigate to Tables > search “mserp” in the top right corner, select and click into the desired entity
2. If the column does not appear, click the “+# more” button on the column row and select it.
3. Left-click on a column and select “edit column”
4. In the “Edit column” side pane, open “Advanced options” and find the “Schema name”. Copy that into a notepad for future reference or directly into “Select columns” in the flow.
5. If I only want the flow to trigger when a financial dimension is modified to be suspended or not suspended. The flow will monitor ONLY the IsSuspended column on the table, all other changes will not trigger the flow.
5. Locating the Schema (Option 2)
Running the flow and referencing the “body”
1. A flow needs a trigger and an action to run, so add an “initialize variable” to capture the JSON produced by the trigger.
The “body” is all the contents of what the trigger produces, it also contains values that we will want to pass along to the third party. Add the “body” as the value, in the third data field.
2. Now, trigger the flow from D365. In this case, I toggled a financial dimension to “suspended”. In other words, I edited the virtual entity table that’s tied to D365.
3. Navigate to the flow to see the 28-day run history, after you trigger the flow, we should see succeeded value here.
4. Click into one of the runs by clicking the date on the left. Once in, click on the trigger component of the flow to open it up.
5. Copy and paste the whole “body” into a notepad file. Then find the name(s) of the columns you want to filter or limit the trigger with.
f. In our example, the flow will only be monitoring the column “mserp_issuspended” and will only trigger if the financial dimension is equal to “Location”
So, in theory, if we toggle a “department” financial dimension to suspended, the flow will not trigger. If we update the “group dimension” value on a “location” financial dimension, the flow will not trigger.
Test your filters by specifically trying to trigger the flow and perform actions that should NOT trigger the flow.
6. Access the 3rd party’s endpoint and receive access token
a. Add an HTTP action below the main trigger
b. Method - “POST”
c. URL – should be provided by the third party
d. Headers – should be defined by the third party
e. Body – “client_id=XXXXXXXXXXXXXXXXXXXXXXXXXXXX&scope=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX/.default&grant_type=client_credentials&client_secret=XXXXXXXXXXXXXXXXX”
i. All values denoted by an X should be provided by the third party.
7. Parsing the trigger and HTTP token request JSON
a. Add a “Parse JSON” action directly after the HTTP request, add “body” from the HTTP request to the content field, and copy and paste the scheme from the txt file into the scheme.
b. Add another “Parse JSON” action and add the “body” from the trigger into the content field. Grab the scheme from a successful test of the trigger, paste it into a notepad txt file, then click “Generate from sample”, and paste that schema into the pop up.
8. Create the outgoing HTTP message with a JSON payload
a. At the end of your flow, transform the data into whatever format is needed.
i. Example: Does a field need to be a Boolean true/false? Are some values hard-coded? Do we need to translate a D365 value into the third party’s nomenclature?
b. Populate the HTTP message with the correct method, URI, Headers, and Body.
i. These should be provided by a third party.
c. The access token we parsed out of the HTTP POST in earlier steps will come back into play now.
9. Testing and configuring the data
It can be useful while testing the output to swap the HTTP action for a “Send an email V2” action. This way you can see exactly what is being distributed and when. The “Compose JSON” can be useful for translating different outputs into required formats. Here are some examples:
a. equals('No',body('Parse_JSON')?['_mserp_issuspended_label'])
If the “Is Suspended” value is toggled to ‘No’, this equation returns a true value. This is then translated into a Boolean value required for third-party consumption.
b. if(empty(body('Parse_JSON')?['mserp_primaryemailaddress']),'Manual','Email')
If a primary email address exists on the vendor record, this function will return an ‘Email’ value, if not, it will be ‘Manual’. This is required by the third party, as seen below in the payload.
c. equals('No',body('Parse_JSON')?['_mserp_onholdstatus_label'])
If the “On hold status” equals ‘No’ this expression will return a true value, all other inputs will be false. We then convert the output to the Boolean required by the third party.
d. if(empty(body('Parse_JSON')?['mserp_xxxxxx_xxxxxxxxxvendorid']),'NoID','YesID')
This expression checks for the existence of a field, if it does not exist or the value is empty, we get a ‘NoID’. If there is a value, we get ‘YesID’. This value is then used down the road to determine which condition path to go down. See below.
e. equals('In process',body('Parse_JSON')?['_mserp_status_label'])
If the Project status is equal to “In process” we receive a true value, which is then transformed into a Boolean for an “active:” field required by the third party.
f. split(body('Parse_JSON')?['mserp_categoryname'],' -')
This parses the category name by the delimiter ‘-‘.
g. equals('Yes',body('Parse_JSON')?['_mserp_xxxxxx_isxxxxxxxxxx_label'])
This returns a true value if a front-end toggle is set to “Yes”.
10. Summary
I hope this article/walkthrough was helpful! We went over the basics of Power Automate flows and how they can be used to track and sent data from different data entities within D365 using data events. This feature is extremely useful when integrating with third-party systems and the data needs to be 1:1 real-time in both D365 and the other software. The best part is, it's a functionality that we finance functional consultants can figure out!
Comments