Advice on multi-lookup column list threshold workaround

Copper Contributor




We are tagging most our data in Sharepoint with project numbers via a multi-lookup column.

Now some lists start to reach the threshold limit, and you can't index multi-lookup columns.



Losing the functionality to filter, aggregate or sum by the project number is not an option. Neither is using a single-lookup column nor keeping the lists small.


Possible Solution?:

Are there any known best practices, so that the user can still filter and our pnp-js and pnp-powershell code can make use of an indexed column? By archiving the lists and libraries will cap at around 10 000 - 50 000 items.


I was looking at making use of the term store, a workflow to automatically mirror the project numbers to the new column and a powershell script to update the term store.


But before doing this, I wanted to ask more experienced users, if this makes sense and if there might be other solutions.


Best wishes



4 Replies

@de_simon I have the same issue.


by pnp-js are you referring to the web parts?


I have the added issue of our powerapps that access project numbers and 3 other mmd (managed-metadata) fields "becoming unpredictable" = "very bad" when the list view exceeds 5000.


by removing all other fields, these indexed columns do work for us on most views, the line is thin though, if I add one more column such as "modified by" I get an error.


Yes, we are using a web part to create user forms. It allows me to run js.
Did you ever figured out a solution for this problem of yours. I'm having the same issue and not sure how can I use PnP JS to get around this. Please let me know if you found any possible solution.
no there is no real solution. What I'm doing is using pnp-powershell scripts to aggregate and sum the data on a local server and write it back into a sepprate sp list.

Also we are using plumsail forms, this replaces the standard forms. There i can run javascript and recreate certain functionalities with pnp-js.

In the long term we are planing to move the data to a SQL Server and use PowerBI to visualize it.