Close this search box.
Logo der Arineo GmbH
16. June 2022
0 Minute
Typing person in front of a laptop.

PowerSnippets – Filter Findings

Filter with boolean values. So you want to filter a large dataset by some boolean value and it just does not work, because you always get a delegation warning.

The downside is: it just doesn’t let you filter.

On the upside, you can sort by boolean values. Simply add a button and assign it with a context variable and sort ascending or descending depending on what your context variable say.

Make a context variable boolean:

πš„πš™πšπšŠπšπšŽπ™²πš˜πš—πšπšŽπš‘πš({[π™½πšŠπš–πšŽπš˜πšπš…πšŠπš›]:![π™½πšŠπš–πšŽπš˜πšπš…πšŠπš›]}) – this creates a boolean context variable that you can put on the onSelect property of your designated control. This will then switch the variable’s value when selecting the control from true to false and vice versa.


Filter by current user

You might have noticed that using the current user’s e-mail (User().email) in a filter will give you a delegation warning.
This is due to PowerApps not interpreting the User().email as a constant. To get PowerApps to see the current user as a constant, create a variable and set the variable to the current user.

This is best done on start of the app. Simply put πš‚πšŽπš(πšŸπšŠπš›π™²πšžπš›πš›πšŽπš—πšπš„πšœπšŽπš›;πš„πšœπšŽπš›().πšŽπš–πšŠπš’πš•) and henceforth use varCurrentUser as your filter value for filtering by current user.


Filter SQL Database (large datasets)

You have decided to use an Azure SQL DB for all your data storage needs, but somehow it doesn’t show the correct number of rows when applying a filter and when trying to show all rows in your database.
The solution here is rather simple: get rid of all NULL values in your db. Set all columns in your SQL as nullable, but provide default values, so there are no NULLs in your db.


Filters in PowerApps ( filter by control)

Filtering a large dataset by a text-input control can lead to an unsatisfactory user experience, because the filter works in real-time, meaning it starts filtering as soon as you start typing. This is ok when you are dealing with smaller datasets <500 entries. Once you are filtering larger databases >500, this can give the impression the app is “stuttering” and lead to user discontent.
To prevent this, filter by variables. Add a πš‚πšŽπš([π™½πšŠπš–πšŽπ™Ύπšπ™΅πš’πš•πšπšŽπš›πš…πšŠπš›πš’πšŠπš‹πš•πšŽ],[πšƒπšŽπš‘πšπšŸπšŠπš•πšžπšŽπ™²πšžπš›πš›πšŽπš—πšπ™²πš˜πš—πšπš›πš˜πš•]) to the on change property of your text input control and add that variable to your filter.

Now your filter will only trigger on entry in the text input control when the entry is commited by pressing either TAB or enter.