September 24, 2021

Besides operators, OData also enables us to have functions on our data. These are super useful to filter the data on the source to pull only the information that we need. The startswith function enables us to get all values that have the first characters that we define.

As always, we’ll use Power Automate and SharePoint to test, but the concepts can be applied in other areas, even outside Microsoft’s platform.

Usage

To test and demonstrate the syntax, we’ll use a list of random data with fake people.

We’ve already covered the “equals operator” or “eq operator,” so if you have questions, you can check it here.

Let’s fetch everyone that the first name is “Aida.” We’ll use the Title that has the full name of the person.

startswith(Title,'Aida')
If your SharePoint list columns contain spaces then you’ll need to replace the spaces with “x0020” in the URLs. SharePoint does this encoding in the backend, but if we want to reference the table directly, we need to use the encoded version since it will also include the OData expression in the URL.

Here’s the test in Power Automate. We use a table to see the results quickly and check if our filter is working.

Here’s the result

If the startswith function was not available, we needed to fetch all items and filter them. Depending on our data, this would take a long time and some effort, but this way, the Flow runs in seconds and returns our results quickly.

Limitations

The main thing to keep in mind is the name of the fields that you’re querying. Since you can use this strategy for multiple sources, you should be careful and check before the names are returned.

Pro Tip:
A nice way to check the names is to do a search and limit it to one record. Then you’ll see in the result the names of the fields and be sure that you’re using the correct ones.

Here’s how to be sure that the value that you’re importing is correct.

You can then copy and paste the names.

Only strings

We can only use the startswith function with strings. It makes sense to do so, but it could be useful with other types. For example, it would be useful if we could know all the 30-year-olds in our company by using something like:

startswith(Age,3)

This is just an example. The flow will return an error if you try to use the function this way.

URL size

Besides that, we have a limit of URL sizes. The limit is quite high, being the max size of an URL is 2000 characters. But since we’re can have multiple components in an URL, we should be careful not to hit that limit. I’m putting this here for the sake of completion because 2000 characters is a lot. If you’re hitting this value, the OData queries are not your problem for sure.

Recommendations

Here are some things to keep in mind.

Name it correctly

Naming is super important in this case. I see many mistakes because the default view, for example, in the SharePoint “Get Items” action in Power Automate, hides the “advanced options.” Therefore, someone who is scanning your Flow may not notice that there are filters.

Always add a comment.

If you can, regardless of where you use the expressions, try to add a comment indicating what you are trying to do. Do this before you do the expression so that you think before you do. It’s a good practice to map intention with action so that, if something goes wrong, you can check either the initial information or the expression itself. Also, when you’re scanning your Flow to understand what it’s doing, having a comment will help you understand what is being done without looking at the expression. If you’re not familiar with the SharePoint List structure, for example, you’ll at least know what is being done.

Am I missing something? Leave a comment or interact on Twitter. Let’s work together to make this reference as complete as we can

Back to the OData Reference.

Photo by Start Digital on Unsplash

Manuel Gomes

I'm a Project Manager with experience in large projects and companies. I've worked in the past for companies like Bayer, Sybase (now SAP) and I'm currently working for Pestana Hotel Group.

View all posts by Manuel Gomes →

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: