The Scenario
Power Automate has a large selection of built-in connections that make connecting data from disparate sources easy without needing to know how to make an API call. Sometimes a connector doesn’t exist in which case the HTTP action needs to be used.
Here we have data from Dynamics Business Central (BC) being retrieved using an API documented here with a slight change to the API endpoint. (The article linked walks you through setting up authentication) Adjust your API endpoint to reflect the following: https://api.businesscentral.dynamics.com/v2.0/<tenant id>/<environment name>/api/v1.0
where the <tenant id> is the GUID for your tenant and the environment name is something like Production, Sandbox, etc.
Now that we can get the data, I will be using Sales Order Lines from BC, we can parse, filter, and truncate the data before putting it into an HTML table to send in an email. We want to send our customers an email with the items from their open orders that haven’t shipped yet. Let’s get started.
The How
First, we need to parse the data to get only what we need. In this case, we want to get the items that are not completely shipped. That field in BC is calculated this way, which we will see in the filter action. Using the Parse JSON action in Power Automate, we include the Body from our HTTP action and our sample payload is as follows:
{
"type": "object",
"properties": {
"@@odata.context": {
"type": "string"
},
"value": {
"type": "array",
"items": {
"type": "object",
"properties": {
"Document_Type": {
"type": "string"
},
"Document_No": {
"type": "string"
},
"Type": {
"type": "string"
},
"No": {
"type": "string"
},
"Description": {
"type": "string"
},
"Quantity": {
"type": "integer"
},
"Outstanding_Quantity": {
"type": "integer"
}
}
}
}
}
}
You are welcome to include more fields if you would like.
We then filter the data using the Filter array action and include the value form the Parse JSON step along with this advanced mode filter:
@or(equals(item()?['Quantity'], 0),not(equals(item()?['Outstanding_Quantity'],0)))
This takes the data from the API and filters it to only include the items that are outstanding yet retain the JSON formatting.
The Select action lets us select only the columns we want; in case you didn’t simplify your sample payload in the Parse JSON step.
Then we use the Create HTML table with the output from the Select action as the input. You can then include the output from the Create HTML table in an email and see the result.
A visual representation of the flow is below.
Conclusion
You may be tempted to use an array variable as the input to the Create HTML table action, but that will limit your table significantly and you will run into the issue of a column definition. The Select action allows you to use the automatic column definition in the Create HTML table whereas using the custom column definition has a bug and isn’t maintained once you save and run the flow.
Enjoy working with Power Automate and let me know if you have success with these steps!
If you need to work with a partner, please contact a JourneyTEAM representative, today.