Improve your flow with OData

There can be several scenarios where you have a need to make requests by leveraging OData.

I am not only talking about code based solutions but also more simply you might come across this need with your power automate flows ( to name one ).

We will shortly see an example of how you can leverage OData directly in your flows to improve their performace.

First, however, let’s give an introduction to OData and try to understand the main functions made available.

The Open Data Protocol (OData) is an API implementation standardized by OASIS that provides a method for querying and updating data using the REST architecture (REpresentational State Transfer).

Many Microsoft products or services support this protocol.

Below is some basic information to get you started with this protocol.

Comparators: 

  • eq  [equals]  /Users?$filter=Name eq ‘Mario’
  • ne  [does not equal] /Users?$filter=Name ne ‘Mario’
  • gt  [greater than] /Users?$filter=Age gt 15
  • lt   [less than] /Users?$filter=Age lt 15
  • ge [greater than or equal to] /Users?$filter=Age ge 15
  • le  [less than or equal to] /Users?$filter=Age le 15
The preceding comparators can be used in conjunction with arithmecious operators :

  • add  /Products?$filter=Prices add 2 eq 10
  • sub  /Products?$filter=Prices sub 2 eq 10
  • mul — /Products?$filter=Prices mul 2 eq 10
  • div — /Products?$filter=Prices div 2 eq 10

Clearly you can join the different conditions by exploiting the logical operators.

In addition to this you have the option of using functions to best suit your needs. 

Among the most popular you can find the contains function that will allow you to check whether a particular character is contained in a string, e.g. /User?$filter=contains(Name, ‘f’). 

You can also manage pagination using the top and skip paramenters.

With pagination you can load a bulk of data incrementally to better handle large amounts of data. 

The top parameter limits the number of objects returned where the limit is a positive integer. For example: ?$top=100

Instead the parameter skip allows a number of objects to be ignored before retrieving the result, the offset being a positive integer. For example : ?$skip=100 it will return the elements from the 101st position in the list.

Clearly the top and skip parameters in many situations coexist in the same request.

In short, you can indulge as you see fit.

Great! 

Now that we have seen what OData is and framed some of the main functions let’s see how this can be applied in the power platform world.

To do this let’s use Power Automate as an example.

A common case is to get items from a SharePoint list.

If the SharePoint list contains a lot of information there may be performace problems. 

It is much more effective to use the action with an OData filter so that the data we receive is only the data we need.

By doing this you will receive a response in less time from SharePoint. 

Also, SharePoint is much better at filtering data than our flow.

So suppose you have a list available and you have to filter it by name.

To do this you will need to expand the “advanced options” of the action and write your query as in the example below.

Clearly you can also take advantage of the other options made available, such as Order By. 

In this example I have given you a case considering SharePoint but there are many connectors that allow you to apply that filters directly on the call, one among many is Dataverse.

What we have seen today is a quick introduction to the OData world applied to an automate power flow. 

This functionality sometimes takes a back seat but if your goal is to improve performace then you cannot overlook it.