cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dhiran
Level: Powered On

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
stormea
Level 8

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.

 

Dhiran
Level: Powered On

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
firstImage

PowerApps Monthly Community Call!

Join us next Wednesday for our Demo Extravaganza, October 16, 2019 8am PDT.

firstImage

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Top Community Contributors for September  2019

Top Community Contributors for September 2019 🎉🎉

Let's thank our top community contributors

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

Top Kudoed Authors
Users Online
Currently online: 184 members 5,224 guests
Please welcome our newest community members: