Another PowerApps tip for you: You may need to query a data source that has more than 500 rows. We know how.
A minor annoying thing with PowerApps right now is the inability to query past the first 500 records of a data source. For this PowerApps tip, I will focus on SharePoint, mostly because I ran into this exact situation.
Here was my problem:
I needed the ability to filter certain records by a ‘Choice’ column that allowed multiple values (PowerApps does not currently support ‘Multiple Choice’ columns). So, I migrated the data in those columns into a multiple-line column.
Great! This means I could now filter these by using the ‘in’ operator. For example:
Filter(‘MySPList’, “FilterValue” in Multilinestringfield)
But, this caused another issue: The 500 item limit. PowerApps will only query the first 500 items in the SharePoint list, and will not bring back any results past 500. (Note: my SP List had 750 items).
Good news is that Microsoft has been working on getting around this, by allowing you to delegate certain operands back to SharePoint, so it can do the processing. This will allow you to get past the 500 item limit!
But….the only operands that currently support this are the ‘=’ and the ‘StartsWith’ operands. Oh, and the columns in SharePoint have to be indexed for these to work. (Guess what type of field you can’t index? That’s right, A multi-line field.)
So, I was still out of luck.
It’s not the best solution, but it works…
So, my workaround for this was to leverage the ‘StartsWith’ operand for a different column (Indexed Single line of text), which I could filter to pull in 463 results, and then toss into a collection.
Then I did it again with the rest.
ClearCollect(mycollection, Filter( ‘MySpList’, StartsWith(SingleLineofText, “Filter1” )|| StartsWith(SingleLineofText, “Filter2”)|| StartsWith(SingleLineofText, “Filter 3”)))
This started my ‘Collection.’ I collected 463 items into my PowerApp. Then I followed it up with this command:
Collect(mycollection,, Filter( ‘MySpList’, StartsWith(SingleLineofText, “Filter4” )|| StartsWith(SingleLineofText, “Filter5”)))
This appended my collection with the rest of the items, and now I can perform any query against this collection in the comfort of my PowerApp!
The Drawback
Obviously, this isn’t something you want to do for a list or table with hundreds of thousands or millions of rows. This is a stop gap to get you over the 500 limit hump.
But, you should be prepared for a delay while it pulls in your data. In my case, on a SharePoint list with 750 rows and 70 columns, it added about 7-to-8 seconds of load time to the app.
You can certainly run this on your ‘OnVisible’ or ‘OnStart’ property of the home screen to help mitigate this delay to your users.
Conclusion
I’m hoping this limitation is something PowerApps will get past in the near future, but for now, here’s a measure you can apply if you just need a bit more wiggle room on your data querying — I hope this PowerApps tip helps make it easier.
Part four of a series.
