I can only get the first 256 entries to show on a powerapp from a sharepoint list with ~ 1,000 entries.
Online is says that there were problems with delegation 3 months ago where only the first 500 results would show. Responses to that said it would get fixed in about a month, so I'm wondering if this issue is still current, but changed from 500 to 256?
I do see that ("For lists that contain more than 500 items...") and I have made changes to the "Items" however this has not worked. The change I made was the following, did not produce the desired result of allowing all items in the unfiltered list to be seen.
SortByColumns(Filter( TestList, (TextSearchBox1.Text in UserDisplayName) || (TextSearchBox1.Text in Manager) ), "UserDisplayName", If(SortDescending1, Descending, Ascending))
To ensure I was not confounded by multiple criteria, I simplified my test case to
SortByColumns(Filter( TestList, (TextSearchBox1.Text in UserDisplayName) ), "UserDisplayName", If(SortDescending1, Descending, Ascending))
Oddly, I got one (1) more item in my list that I did not previously have. I can work with changing my filter criteria to work like "Search". That is simple enough but I am not getting more than 500 items displayed or even searchable in from my O365 SharePoint list. I went from 499 to 500 items displayed out of roughly 800. This is a critical issue, making this a non-deployable solution for our company.
Per the article:
SharePoint connector now supports “equals” type of queries, therefore if you change your function to
SortByColumns(Filter( TestList, (TextSearchBox1.Text = UserDisplayName) ), "UserDisplayName", If(SortDescending1, Descending, Ascending))
you should get your desired results.
While I agree (in part) this gets the results that I want, those results are very constrained. It is a very limited service when you must search for something in which you know exactly the name and you also know the case (case-sensitive) way in which the text appears. Maybe the text is mixed case or title case or uppercase. Only under those circumstance where I dont attempt to coerce the text case, meaning I must know EXACTLY how what I am searching for does this work.
I have tried different criteria in order to perform the search and under no circumstance am I able to extract the desired records, even if I only utilize one field for the search criteria.
Currently I have only one scenario where I can see all the records belonging the the "Items" field in the "Browse Screen" portion of the gallery. That scenario is very contrived, where I intentionally created a single column where every rows has a single item such as "A". If I filter the "Item" field in the gallery for "A" I can see (scroll) through all the records and sort them. Since they are in alpha order, just reversing the order shows me my last item in my list so I know for an absolute fact I can see every item in the list but only under this very contrived scenario. This to me suggest the issue is not delegation as has been suggested in technical articles.
This works to the extent that it allows me to see every record (currently less than 1000)
SortByColumns(Filter( TestList, ( TestField = "A") ), "UserDisplayName", If(SortDescending1, Descending, Ascending))
However, now, I can not search using the search field for all the records that meet my search criteria. Any criteria that I've put in returns less than the number of matching records and in my test case, there are only four (4). Once I add a search criteria using a textbox search field, my visible list returns to 500 and my searches return less than the actual number of records that match my criteria. I am not suggesting its a search control issue as I have tried many different options without an actual search control in my quest to return accurate results.
The below constrains the view to 500 items and the search results are less than what is actually present (4 items in scenario)
Sort(If(IsBlank(TextSearchBox1.Text), TestList, Filter( TestList, Lower(TextSearchBox1.Text) = Lower(Text(UserDisplayName)) )), UserDisplayName, If(SortDescending1, SortOrder.Descending, SortOrder.Ascending))
Even in this scenario, I still need to know exactly what I am looking for even though the search is no longer case sensitive.
It is my personal belief this issue has not been important enough to the PowerApps team. I believe it has been over a year and even the most basic premise on which the PowerApps are founded (SharePoint lists) and tutorials are built are not working in a way that customers are actually using them. I am holding out for hope but PowerApps from my vantage point seem to only work for tutorials and simple cases with short lists, not where users really live.
I wish to see a solution, however whatever the PowerApps teams priorities are, and I am very sure they have many, searching and working with SharePoint lists greater than 500 items is likely very close to the bottom.
Dropbox itself does not have any limitation. But the Excel connector do have this limitation.
This is a known feature gap today - which we hope to address in the next couple of months.
Hi @Anonymous, thank you for the detailed explanation of your scenario. I agree that the equals operator is fairly limited, and would work better for filter operations where radio buttons or dropdowns would be used.
We are closing on rolling out support for "StartsWith" type of queries, which should cover the case of being able to type in a prefix for a value, and get matching results back.
Also in a few months, we expect to support the search operation by connecting to the SharePoint search service, and returning results from that service for the list you are connected to.
You raise a good point with case sensitivity. Let us look into that and get back to this thread with more details.
We are definitely committed to making SharePoint lists a first class data source, it is just taking us some time to code, test and roll out the rich set of functionality we want to make available.
I'm trying to think my away around the 500 record limitation. After thinking about this for a few days, I am wondering the following if the follow methods are possible:
1) Can we split our tables into groups of 500 and then use a work process or function to coalesce the query results. Let's say that I have 2000 records (I actually have less than 1000) and so Table1 contains the first 500 records, Table2 contains the second 500... until I get to Table4 which contains the last 500 records. My work function loops through the queries and collects the results from table1-table4. At that point, I now have all my results in which I hold in what we will call WorkTable1.
2) Can we use Collections to achieve a seamless search or filtering across multiple tables?
Now, if this is possible, how so, since all queries seem to be bound to a control (not independent as in a procedural language).
Can you define an independent function?
The above methods would create a workaround to increase the limitation from 500 to 5000 records using method 1 (Maximum 10 tables, 10 * 500 = 5000). Since you can only view 5000 records at one time. This would increase the limitation from 500 to 2500 records using method two, since a maximum of 5 tables can be used in a Collection and using a self-imposed limit of 500 records (5 * 500 = 2500).
In doing so, I can perform any type of query up to a minimum of 2500 records and a maximum of 5000 without service limitations
I've been looking at Collections and Patch, which seems to give me hope
This is more work for me, however, it sounds like a workable solution in theory. If this is possible, (I havent tried it yet), retrieval is one thing, updating across multiple tables is another challenge.
Thanks for pointing it out.
I've changed "in" to "=" in the filter and voila, now it shows all the items!!!
So in my app, I filtered to show the items where the logged in user's full name matches the Title of the SP list item
It had Username.Text in Title in the filter code. I now changed it to Username.Text = Title
Now only if we can get the date field fixed then it'd be awesome (App shows a day behind when created a SP item via the app but it's fine in SP list itself but any item created via SP is fine in the app).
Check out the News & Announcements to learn more.
Did you know that you can visit the Power Query Forum in Power BI and now Power Apps
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.
DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.