Advice on multi-lookup column list threshold workaround

%3CLINGO-SUB%20id%3D%22lingo-sub-2425966%22%20slang%3D%22en-US%22%3EAdvice%20on%20multi-lookup%20column%20list%20threshold%20workaround%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2425966%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ESituation%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EWe%20are%20tagging%20most%20our%20data%20in%20Sharepoint%20with%20project%20numbers%20via%20a%20multi-lookup%20column.%3C%2FP%3E%3CP%3ENow%20some%20lists%20start%20to%20reach%20the%20threshold%20limit%2C%20and%20you%20can't%20index%20multi-lookup%20columns.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EProblem%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3ELosing%20the%20functionality%20to%20filter%2C%20aggregate%20or%20sum%20by%20the%20project%20number%20is%20not%20an%20option.%20Neither%20is%20using%20a%20single-lookup%20column%20nor%20keeping%20the%20lists%20small.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EPossible%20Solution%3F%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EAre%20there%20any%20known%20best%20practices%2C%20so%20that%20the%20user%20can%20still%20filter%20and%20our%20pnp-js%20and%20pnp-powershell%20code%20can%20make%20use%20of%20an%20indexed%20column%3F%20By%20archiving%20the%20lists%20and%20libraries%20will%20cap%20at%20around%2010%20000%20-%2050%20000%20items.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20was%20looking%20at%20making%20use%20of%20the%20term%20store%2C%20a%20workflow%20to%20automatically%20mirror%20the%20project%20numbers%20to%20the%20new%20column%20and%20a%20powershell%20script%20to%20update%20the%20term%20store.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20before%20doing%20this%2C%20I%20wanted%20to%20ask%20more%20experienced%20users%2C%20if%20this%20makes%20sense%20and%20if%20there%20might%20be%20other%20solutions.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20wishes%3C%2FP%3E%3CP%3ENico%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2425966%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESharePoint%20Online%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2432815%22%20slang%3D%22en-US%22%3ERe%3A%20Advice%20on%20multi-lookup%20column%20list%20threshold%20workaround%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2432815%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1073454%22%20target%3D%22_blank%22%3E%40de_simon%3C%2FA%3E%26nbsp%3BI%20have%20the%20same%20issue.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eby%20pnp-js%20are%20you%20referring%20to%20the%20web%20parts%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20the%20added%20issue%20of%20our%20powerapps%20that%20access%20project%20numbers%20and%203%20other%20mmd%20(managed-metadata)%20fields%20%22becoming%20unpredictable%22%20%3D%20%22very%20bad%22%20when%20the%20list%20view%20exceeds%205000.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eby%20removing%20all%20other%20fields%2C%20these%20indexed%20columns%20do%20work%20for%20us%20on%20most%20views%2C%20the%20line%20is%20thin%20though%2C%20if%20I%20add%20one%20more%20column%20such%20as%20%22modified%20by%22%20I%20get%20an%20error.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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