I have a SharePoint list with around 3500 records. Each item has a status of "Loaned Out" or "Returned" There are around 230 items which are loaned out
What I would like to do is show all the "Loaned Out" items on a canvas app. I have 3 buttons which basically get the data, sort the data by name and sort data by product. This is all displayed in a datatable.
When I get the data I use
ClearCollect(myTest,Filter(Sort(Table_query,Status,Ascending),Status = "Loaned Out"))
The reason is that whenever I get the data it never gives me all the records of the loaned out items (I am assuming delegation is in play). So the above command is to first sort the data source so that I have all the loaned out items together and then just create the collection with Loaned out records
My 2nd button is to sort the data table by name for which I use the following command
My datatable items list shows the Results collection but it still does not show all the records of which there around 230. It only displays 59!
any ideas of why this is happening or a better way to do what I want?
There might be a few things in play, but I can say that if the filter queries are setup correctly, you should be able to get all of it back minding the delegation limits.
My first recommendation is to always start with your filter queries first then work outward adding the additional functions such as sortBy. This way we can determine first if the Filter() is actually being applied correctly.
CountRows( Filter(Table_query,Status = "Loaned Out") )
ClearCollect(myTest, Sort( Filter( Tablue_query, Status = "" ), Status, Ascending))
CountRows( Filter(Table_query,StartsWith(Status,"loaned")) )
Let me know what these steps get ya and that'll help narrow what else we could test. In practice though as long as you are returning under the 500 limit in PowerApps & your data count is under the list view threshold in the SharePoint list (5k) then you should expect all the data back.
Apologies for the reply but I thought I had replied to report my findings but somehow it did not submit it.
Anyway.. I tried your helpful tips and I still couldnt get it correctly bring back the right data set. Just to explain how the list data was created. We initially built the list to replicate a Lotus notes list and did some tests to ensure that PowerApps would work. so we already had some records which had imported to test with. So with ID's in SharePoint we were already around 200-300. Then we deleted all and imported the whole 3000+ list in with records spread of items loaned out and retured. So we had some instances where ID's 1000-1500 would have some and then 2500-3000 would have have other loaned out items.
So we I tried to count records of the sort I would still get delegation warnings and the maximum lines I could get in would be around 2500 max. It still would sort out all the loaned out items becuase of that limit, therefore I could never get items from 2500+.
I hope this makes sense and the workaround we have done is to create an archive list where we now move items from the Loans list when they are returned, so the Loans list will only show "Loaned Out" items only.
Fill out a quick form to claim your user group badge now!
Find out where you can attend!
Features releasing from October 2019 through March 2020
Learn how to build the business apps that you need.