cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
Helper II

Getting Data Sets From SharePoint List

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?

2 REPLIES 2
Highlighted
Advocate III
Advocate III

Re: Getting Data Sets From SharePoint List

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.

  1. 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") )
  2. 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))
  3. 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.

 

Highlighted
Helper II
Helper II

Re: Getting Data Sets From SharePoint List

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. 

Helpful resources

Announcements
Ignite

Microsoft Ignite

This will be a conference that you do not want to miss!

secondImage

New Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (4,351)