Advice on multi-lookup column list threshold workaround

[de_simon]

Hello,

Situation:

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.

Problem:

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

Nico

[#2]

@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.

[#3]

Yes, we are using a web part to create user forms. It allows me to run js.

[#4]

Hello,
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.
Thanks,
Suhas

[#5]

Hi,
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.