Search

Power Automate - Filtering API Query Results in HTML Table

Successful Steps to Enjoy Working With Power Automate When Filtering API Query Results in HTML Table


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. 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 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 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 Power Automate and let me know if you have success with these steps!

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. They have deep experience in the Power Platform, Office 365, SharePoint, Microsoft Teams, Dynamics 365, Cloud solutions and more.


67 views

833-438-2312
info@journeyteam.com

CORPORATE OFFICE UTAH

121 W Election Rd #300

Draper, UT 84020

--------

Nashville, TN

Salinas, CA

Houston, TX

Pittsburgh, PA

Concord, NH

High Point, NC

Baltimore, MD

Louisville, KY

St. Louis, MO

Waukesha, WI

VAR_Star_logo White.png

VAR STARS
2020 WINNER

  • White LinkedIn Icon
  • White Instagram Icon
  • White Facebook Icon
  • White YouTube Icon
  • White Twitter Icon

Microsoft Dynamics 365, SharePoint, Cloud, ERP, Field Service, Finance and Operations, CRM and Marketing Consultant in Utah and Tennessee. Microsoft Gold Partner and Okta top 20 World-Wide Partner. © 2020 JourneyTEAM. All Rights Reserved. Privacy Policy Terms and Conditions