Search
Close this search box.
16. 6 月 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.