Square Brackets Search in Dataverse with Power Apps
Published at Nov 17, 2023
Updated on 18th November, 2023
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:
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. Here’s Rob’s website if you want to find out more.
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(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!