Understanding OData Expressions

In my last blog we talked about the amazing new Find action in the Business Central Connector. It allows you to quickly filter down to specific sets, or individual records.  The key to that is using OData filters.  In this blog I want to walk through OData filters and queries using realistic examples for my fellow Business Central Citizen Developers.

Here is what we are going to cover in this blog:

• What is Odata?
• Filter By
• Order By
• Common Scenarios

What is OData?

I imagine there is a wide rage of experience with OData out there.  Some people know it all, some are brand new to the concept.  Regardless, it’s very helpful to understand this concept when working with Business Central Connector. (or any other API’s). OData is a universal language for building and consuming API’s.  So basically it’s a set of rules that everyone follows so more work can be done because everyone is on the same page. If you want to find out more information Check out THIS SITE.  I also thought THIS was an easy, informative read. The most common expressions are $filter & $orderby. Let’s take a look at them now!

$filterby

Filters the data to criteria that you identify. From a Power Automate perspective this is helpful because it cleans up your data, and improve the speed of your flow.  When using it in the Find, the ‘$filter’ is already taken into account.  You will just need to identify the field on the table and the property you want to filter by. You may get one record, or many depending on the criteria. Here is a blog from Perituza that also discusses some common errors in Power Automate. I’d definitely bookmark it!

$orderby

When you need to have data returned in a specific order, you will use this expression. This is common with date ranges. You look at the field that you want to base the order on and then determine ‘asc’ or ‘desc’ order. You can have multiple orderby sorts in one call.

Common Cases with Business Central Processes

$filterby

The most common scenario I see for $filter is passing the ROW ID from a header to the lines.  This can be for Sales Orders, Purchase Orders, Assembly Orders, Journals, etc. 

The ability to easily pass the Row ID from one table to another is helpful for Dimensions. They are a separate table in Business Central, which can be a real pain sometimes.  In the past I have usually done this with a nested call in an HTTP Request.  It takes a lot of work and trouble shooting to use the date, but it is possible.  Have a step for calling each ID through the Business Central connector makes trouble shooting easier, and the data is instantly accessible and properly formatted.

Let’s say your Flow is triggered when a sales order is released, and you need to take the Sales Order ID to get  the sales order lines for that record.

You will notice that I used “documentId“.  According to the BC API Docs, I know that this is the field on the salesOrderLines API that is associated with the Sales Order GUID, which is what I get when I create a new Sales Order. (Business Central API v2 Documentation)

If we take a look at how the raw inputs translate in Power Automate, we can see how it is filtering the query

I’ve added a compose step to show the results of what we get when this flow runs successfully. 

As you see I the sales order lines.  In this flow I just used the body, but you actually have access to all the dynamic content associated with the sales order Lines.

$orderby  

Let’s use a $orderby scenario.  If I want to sort Invoices by Due Date for example it would look like this:

This will put the dates as the due farthest in the future will be listed as the first invoice.  If you wanted to make it the oldest listed first you would use ‘dueDate asc’ instead.

Here are the results of the flow using this orderBy filter.

Now let’s say you wanted to use $filterby and $orderby together.  Let’s say you needed to filter all the invoices, but wanted to sort them by a customer and order by due date like in our example above.

Below you can see the array produced, meeting the criteria  specified. Only records for customer 10000, Adatum are showing and they are in descending order by due date.

Summary

These examples are just scratching the surface of possibilities.  They are intended more to give visual guides and help you start to think about how you can use them for your specific need.

The power of Find action lies in the Odata expressions. I hope after reading this blog you understand what ODATA expressions are, have a deeper understanding of how to use $filterby $orderby, and can envision how you can apply that to your Business Central Power Platform projects!