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.
Note: Camp = Site; Selecting ">" in the LH gallery populates the RH gallery
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:-
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?
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
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
- 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.
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:
Community Support Team _ Phoebe Liu