SharePoint List Filter gets all items only when hard-coded

[Anonymous]

Hi,

I have a SP List which has around 5400 items and a column named 'Site'. When I use Collect() & Filter() using a hard-coded Site name I get all the items in the List with this name - however - if I use a reference to the Site name (eg from another collection) I can only get the number of items with this Site name from the first 2000 IDs. 

Note: the Site column in SharePoint is a Dropdown list so I need to use .Value in my formulas

Eg.

Collect(colData, Filter(mySPList, Site.Value = "Site1"))  ---   this collects 492 items, which is all items for Site1

- however - 

ClearCollect(colSiteNames, {SiteName: "Site1"});

Collect(colData, Filter(mySPList, Site.Value = First(colSiteNames).SiteName))  ---   this collects ONLY 146 items, which equates to the number of Site1 items in the first 2000 items from my SPList by ID number.

I'm trying to build a more dynamic collection of my SP List data whereby I can create a collection of Site Names then use ForAll() to collect ALL items for each Site, not just those in the first 2000 ID numbers.

Is there a way to do this? I have tried setting a variable to the Site name and this works but I can't use Set() or UpdateContext() to update the variable inside of the ForAll function.

Any ideas? Is there a better way? Any help appreciated.

Cheers

[Anonymous]

Hi @Anonymous,

Can you send a screenshot to your project.

 and also to your expected output.

Thanks!

Best Regards,

Boynogs123

[Anonymous]

@Anonymous 

Thanks for your reply.

I don't have the project working as yet so I've set out in Excel (see attached picture) what I'm looking to achieve. Basically, I want the ForAll() function to collect the same count values as the hard-coded code.

The reason I don't want to use hard-coded Site names is because Sites become Active/InActive from time to time and I want my code to adjust when this happens rather than me having to maintain the code when this happens.

Hope this makes sense? 

Cheers

EDIT: I think the delegation warning is the issue and hence won't allow me to do what I want to?

[v-monli-msft]

Hi @Anonymous ,

What are the columns types of Site and SiteName columns in sharepoint list? 

Regards,

Mona

[Anonymous · Accepted solution]

@v-monli-msft 

'Site' is a column in my SP List and it is a Choice/Menu column, hence why I use .Value.

'SiteName' is a column in a collection I create and is of type Text, I think. At least that's how I think I set it?

I think I've solved my issue though now. I have a dropdown menu collection of the Site Names and when a user selects one I set a global variable to the selected dropdown value. Then I use this variable in my filter on the SP List and I can query the entire 5400 items in my SP List and return the required subset.

It would be good if this was written up as a 'Best Practice', or similar, when querying SP.

Thanks for your reply.

Cheers