Microsoft Dynamics vendors provide comparisons and opinions to professionals in the ERP/Accounting software selection process

 
 

JourneyTEAM

Microsoft Dynamics 365 Business Central and Power Automate — Filtering API Query Results in HTML Table


Email | Print

You can use Microsoft Dynamics 365 Business Central to automate your workflow. All you need is a valid account with Business Central and with Power Automate. Using Power Automate is a life saver and can help you in a variety of ways.

 

Microsoft Dynamics 365 Business Central and Power Automate — Filtering API Query Results in HTML Table

The Scenario

Power Automate has a large selection of built in connections that make connecting data (including in Dynamics 365 Business Central) and other 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 365 Business Central being retrieved using an API documented here with a slight change to the API endpoint. 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 guide for your tenant and the environment name is something like Production, Sandbox, etc.

 

The article accurately walks you through setting up authentication. Now that we can get the data, I will be using Sales Order Lines from Dynamics 365 Business Central, 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 Dynamics 365 Business Central 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 (image and also code):

 

{

"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 us 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 to use as the input to the Create HTML table action, but that will limit your table significantly and you will run into the issue of 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 Dynamics 365 Business Central and Power Automate and let me know if you have success with these steps!

 

SEE FULL ARTICLE HERE

 

If you need to work with a partner, please visit https://journeyteam.com. JourneyTEAM is a Microsoft Gold partner and the 2019 US Partner of the Year for Dynamics 365 Business Central. JourneyTEAM has deep experience in the Power Platform, Office 365, SharePoint, Microsoft Teams, Dynamics 365 Business Central, Cloud solutions and more.

 


Article by: Dave Bollard - Head of Marketing | 801-436-6636

JourneyTEAM is an award-winning consulting firm with proven technology and measurable results. They take Microsoft products; Dynamics 365, SharePoint intranet, Office 365, Azure, CRM, GP, NAV, SL, AX, and modify them to work for you. The team has expert level, Microsoft Gold certified consultants that dive deep into the dynamics of your organization and solve complex issues. They have solutions for sales, marketing, productivity, collaboration, analytics, accounting, security and more. www.journeyteam.com

 

Ask This Expert a Question / Leave a Comment