Square Brackets Search in Dataverse with Power Apps

Published at Nov 17, 2023

#PowerApps #PowerFx #Dataverse 

Updated on 7th March, 2024

Why the square bracket search doesn’t work

(SharePoint is fine, by the way)

In a canvas app, when you use the Search() function to query Dataverse, Power Apps sends a request to Dataverse using what’s called a GET HTTP request. In essense, it requests data using a URL, with some parameters specifying what we want to retrieve.

One of those parameters will contain the text we’re searching for. The reason why square brackets don’t work in the Search() function is because the square brackets are URL encoded to make them compliant with web standards, which turns them into %5B for a left square bracket or %5D for a right square bracket.

If we use the monitor tool in Power Apps Studio we can see the result of the query, containing %5B and %5D instead of the brackets we entered:

Monitor Tool in Power Apps Studio

How do we fix it?

We can wrap our square brackets in…square brackets. Instead of searching for [, you would search for [[]. This tells Dataverse not to treat the square bracket as a wildcard. You can use the same technique for any wildcards. See here for the documentation.

Update! HUGE thank you to Rob Wood for making me aware of being able to wrap wildcard characters in square brackets, and then Laurens Martens for finding out that you only need to replace the first square bracket.

Let’s say that our search function looks like this:

Search(Accounts, TextInput3.Text, "name")

In the code above we’re searching the “name” column in our Accounts table for anything entered in our text input. This won’t allow us to search for anything with square brackets in it. So let’s convert the square brackets to wildcards.

Search(Accounts, Substitute(TextInput3.Text, "[", "[[]")), "name")

This takes our inputted text and substitutes the square brackets with the same character wrapped in square brackets. Now we can search for anything with square brackets contained in it!