by 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')
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.
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.
Back to the OData Reference.
Photo by Start Digital on Unsplash
