Getting Data Sets From SharePoint List

(0) Share

Report

Posted on by Dhiran

143

Hi,

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

ClearCollect(Results,Sort(myTest,Loaned_x0020_Out_x0020_To,Ascending))

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?

Categories:

Building Power Apps

I have the same question (0)

All responses (2)

Answers (0)

Sort by

Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
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.
Create a simple text field and let's see if the Filter function is actually returning the 230. this will help us get the other functions out of the way to determine if the order of the functions is causing the mismatch:
CountRows( Filter(Table_query,Status = "Loaned Out") )
If it comes out to 230, then seems the order was just causing an issue. You can then build your final function with the Filter() as the first inner call:
ClearCollect(myTest, Sort( Filter( Tablue_query, Status = "" ), Status, Ascending))
If it is still returning 59 instead of the 230, then the next path we can go down is perhaps a data integrity issue. I'd say at this point go and validate that if you look at the data through SharePoint that you have the exact same value. A common issue is a mismatch on the capitalization or an extra space at the beginning or end of a string. Another way to broaden the Filter is using the StartsWith()
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.

Was this reply helpful? Yes No
Dhiran 143 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @stormea,

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.

Was this reply helpful? Yes No