While PowerApps is a very powerful tool, and can connect to MANY data sources, one of the biggest limitations is in it’s ability to pull back records from a data source. For performance reasons, PowerApps will only pull in the first 500 records from any data source. This allows the App to perform well, but also means that you as a PowerApps designer must be aware of how to filter your results to meet this threshold.
PowerApps has the ability to offload some filtering to the back end data source. For example, instead of having the PowerApp filter any results you may return, it can send the query to the data source, have it filtered there, and then only return those filtered results. Essentially offloading the heavy work to the data source back end.
However….
Currently, the delegation abilities for PowerApps are not as robust as they need to be. This table outline which data sources can be delegable, and specifically the OPERATORS that can be delegated:
Top-level delegable functions
| Common Data Service | SharePoint | SQL Server | Dynamics 365 | Salesforce | |
| Average | No | No | Yes | No | No |
| Filter | Yes | Yes | Yes | Yes | Yes |
| LookUp | Yes | Yes | Yes | Yes | Yes |
| Max | No | No | Yes | No | No |
| Min | No | No | Yes | No | No |
| Search | Yes1 | No | Yes | Yes | Yes |
| Sort | Yes | Yes | Yes | Yes | Yes |
| SortByColumns | Yes | Yes | Yes | Yes | Yes |
| Sum | No | No | Yes | No | No |
1For string fields only
Filter and LookUp delegable predicates
| Common Data Service | SharePoint | SQL Server | Dynamics 365 | Salesforce | |
| Not | Yes | No | Yes | Yes | Yes |
| IsBlank | No | No | Yes | Yes | No |
| TrimEnds | No | No | Yes | No | No |
| Len | No | No | Yes | No | No |
| +, – | No | No | Yes | No | No |
| <, <=, =, <>, >, >= | Yes | Yes (only =) | Yes | Yes | Yes |
| And (&&), Or (||), Not (!) | Yes2 | Yes (except Not(!)) | Yes | Yes | Yes |
| in | No | No | Yes | No | Yes |
| StartsWith | No | Yes | No | No | No |
2For operators only. And/Or/Not function not delegated.
As you can see above, I can only filter a SharePoint list by using ‘=’ or StartsWith. Only SQL Server can delegate ‘+’ or ‘-‘. You can see how not having the full range of delegable operators, and then being limited by the first 500 Items in a data source could cause serious heartburn.
I know the PowerApps team is hard at work to find solutions for these, so I am hopeful that in the future this becomes a non-issue. But for now, know hat you can and can’t do with a back end data source before you start designing your PowerApp, it will save you time and energy in the future!
Please check out some of my other articles to take a deeper dive into the product, and as always, please feel free to reach out to me at jo.karnes@centricconsulting.com
Office - 365 Days a Year 