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

Build an App based on Large SharePoint Document Library

Hi,

I'm trying to build a fairly simple App using a Large SP library (>8500 items). The schema of the relevant columns looks like:

 

SPLib [ FileName, ModifiedDate, ModifiedBy, SiteName, DocType ]

- FileName column contains upload documents eg pdf, jpeg, Excel files, etc

- SiteName and DocType are Choice columns in SP

 

Because of it's size I've tried delegation to capture the data I want using:

 

ClearCollect(colSPData, Filter(SortByColumns(mySPLib, "ModifiedDate", Descending)), SiteName.Value = Dropdown1.Selected.Title)

 

...but this only brings in the last 2000 items listed by ModDate descending order and due to upload volume only the last 2-3 months of data. This filter also shows unusual behaviour at times by skipping some ModDates and adding older dates in the list instead.

 

Is there a way to capture more data via delegation eg last 6months, 12 months? The aim of the App is to have filtered data by SiteName

 

I've tried filtering by SiteName first to no avail as this only grabs the data with oldest dates because this is how the SPLib is ordered. The SPLib has Indexed columns but I'm thinking this may be playing some part?

 

A pic of my basic App so far is shown below.

 

I would be grateful for any assistance.

 

Cheers

 

LargeSPLib_App.jpg

Note: Camp = Site; Selecting ">" in the LH gallery populates the RH gallery

 

 

 

 

 

 

9 REPLIES 9
Super User
Super User

Re: Build an App based on Large SharePoint Document Library

Hi @Eelman 

 

If you more than 2000 records, you can use a local collection following these steps:
 
a) We will use the ID column of the SP list to identify, filter and sort the items. This is an autoincrement field. 
 
b) create a collection to store the number of loop count:
ClearCollect(LoopCount,FirstN([0, 2000, 4000, 6000, 8000, 10000],RoundUp(First(Sort(<<YourSPList>>,ID,Descending)).ID/2000,0)));
 
c) Now work on the main collection to get all the data, this can be done through OnVisible property of the page.
Clear(ListItems);
ForAll(LoopCount,Collect(ListItems,Filter(<<Sp LIST>>,ID> Value && ID <= Value+2000)));
 
Note: This is assuming that you have made the app settings change and set the query limit to 2000 (500 is the set soft limit)
 
You can add all your filters (month etc.) with an "and" condition in the filter formula of the forall loop. 
 
Hope this Helps!
 
If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!
Eelman
Level: Powered On

Re: Build an App based on Large SharePoint Document Library

Hi,

Thanks for your quick reply yashhag2255.

 

I have implemented your code however it still only works on the first 2000 items (in ID order I think) in my SPList then stops. I think I'm missing something?

 

Code I tried:-

Clear(ListItems);

ForAll(LoopCount,Collect(ListItems,Filter('MySPLib', ID>Value && ID<=Value+2000 And SiteName.Value="hardCoded_SiteName")));

 

- "hardCoded_SiteName" used to check functionality here, will be a dropdown list when working

- Yes, I have set the query limit to 2000 in Settings

 

This only returns items no later than 09-Jul-2018. Data should be shown from Today() backwards.

 

Currently, a SiteName has no more than 600 records so 2000 query limit would cover this easily. I feel this solution is very close so do you have any other tips?

 

Cheers

Super User
Super User

Re: Build an App based on Large SharePoint Document Library

@Eelman 

 

Can you confirm the data that is entered into your loopcount collection? This will let us know the number of iterations that will be executed. 
 
You can check that in:
Select View in the top navigation, then go to collections, when collection list appears, select loopcount and then on the right side, you can see the data into your collection.
 
Thanks, 
Eelman
Level: Powered On

Re: Build an App based on Large SharePoint Document Library

@yashag2255 

 

I threw LoopCount into a gallery to show all data. See below pic

 

LargeSPLib_App_LoopCount.jpg

 

 

Cheers

Super User
Super User

Re: Build an App based on Large SharePoint Document Library

@Eelman 

 

Can you please try to make a little change, instead of filtering the data into collection for SiteName, first you get all the data in collection, then apply the filter on gallery.
 
ForAll(LoopCount,Collect(ListItems,Filter('MySPLib', ID>Value && ID<=Value+2000)));
 
Gallery: Filter(ListItems, SiteName.Value="hardCoded_SiteName")
 
Also, if this is certain that records for a specific SiteName will not exceed 2000 records, then you can directly refer to the Data Source, instead of making a local copy in collections. The warning might still be there because it thinks of possibility where it can exceed 2000, but since we do not have such a scenario, then nothing to worry about.
 
Hope this Helps!
 
Eelman
Level: Powered On

Re: Build an App based on Large SharePoint Document Library

@yashag2255 

 

Stats from applying the above code (ID maxxed out at 2125, which isn't correct). 

 

ListItems filtered by hardCoded_SiteName

Gallery ro count: 82

Gallery Max date: 09/07/2018

Gallery Min date: 19/04/2018

 

ListItems row Count: 2000

ListItems Max ID: 2125

ListItems Min ID: 4

 

Could the SP site be doing something to the filter or is it the limitations of PowerApps?

 

Thanks again for all your help

 

Super User
Super User

Re: Build an App based on Large SharePoint Document Library

@Eelman 

 

Since you do not have more than 600 records corresponding to each site name, can you please try out a simple test to check if all the data is coming as per requirement. If the below expression gets the correct response, we can get rid of the looping collections.
 
ClearCollect(colSPData, Sort(Filter(mySPLib,SiteName.Value = Dropdown1.Selected.Value),ModifiedDate,Descending))
 
Note: I am assuming that your data row limit is set to 2000
Highlighted
Eelman
Level: Powered On

Re: Build an App based on Large SharePoint Document Library

@yashag2255 

When I saw this code I thought "Yes, that will work!" - but alas it does not.

 

Stats from running this code:

- Max ID number in mySPLib:  9206

- For all sites, Max ID returned:  2125

- For all sites, Min ID returned:   4

 

Other stats:

- Max date on all site runs:  around 10/07/2018

- This Max date equates to the first 2000 items in the SPLib - (by ModDate in Ascending order) 

- New sites started in 2019 have zero items collected

 

Seems to me that either SharePoint is setup incorrectly or indexed in an odd way (I'm not the admin for this library, I just have read access) OR PowerApps can only collect 2000 items to 'work on' prior to Filter/Sort/SortByColumns rather than filter the SPLib first?

 

Side note: I removed the Sort() function from above and still got the same stats.

 

My original code, which Sorts the SPLib by Modified Date first at least gives me a more relevant / up-to-date list but it's still not data I'm comfortable giving to my users.

 

Thanks again, this has been a great learning experience.

 

v-yutliu-msft
Level 10

Re: Build an App based on Large SharePoint Document Library

Hi @Eelman ,

Do you get delegation warning?

As far as I know, the limit of 2000 records in SharePoint is caused by delegation.

I suggest you two ways:

1) modify the formula and use delegable functions

2) splict the table every 2000 records.

 

Here's a doc about delegable functions in SharePoint for your reference:

https://docs.microsoft.com/en-us/connectors/sharepointonline/

 

Best regards,

Community Support Team _ Phoebe Liu