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.<\/p>\n
Here was my problem:<\/strong><\/p>\n 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.<\/p>\n Great! \u00a0This means I could now filter these by using the \u2018in\u2019 operator. For example:<\/p>\n Filter(\u2018MySPList\u2019, \u201cFilterValue\u201d in Multilinestringfield)<\/p>\n 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).<\/strong><\/p>\n 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!<\/p>\n But\u2026.the only operands that currently support this are the \u2018=\u2019 and the \u2018StartsWith\u2019 operands. Oh, and the columns in SharePoint have to be indexed for these to work. (Guess what type of field you can\u2019t index? That\u2019s right, A multi-line field.)<\/p>\n So, I was still out of luck.<\/p>\n So, my workaround for this was to leverage the \u2018StartsWith\u2019 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.<\/p>\n Then I did it again with the rest.<\/strong><\/p>\n ClearCollect(mycollection, Filter( \u2018MySpList\u2019, StartsWith(SingleLineofText, \u201cFilter1\u201d )|| StartsWith(SingleLineofText, \u201cFilter2\u201d)|| StartsWith(SingleLineofText, \u201cFilter 3\u201d)))<\/p>\n This started my ‘Collection.’ I collected 463 items into my PowerApp. Then I followed it up with this command:<\/p>\n Collect(mycollection,, Filter( \u2018MySpList\u2019, StartsWith(SingleLineofText, \u201cFilter4\u201d )|| StartsWith(SingleLineofText, \u201cFilter5\u201d)))<\/p>\n 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!<\/p>\n Obviously, this isn\u2019t 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.<\/p>\n 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.<\/strong><\/p>\n You can certainly run this on your \u2018OnVisible\u2019 or \u2018OnStart\u2019 property of the home screen to help mitigate this delay to your users.<\/p>\n I\u2019m hoping this limitation is something PowerApps will get past in the near future, but for now, here\u2019s 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.<\/p>\nIt\u2019s not the best solution, but it works…<\/h2>\n
The Drawback<\/h2>\n
Conclusion<\/h2>\n
\n