Sep 06 2017 02:03 PM - edited Sep 06 2017 02:12 PM
Sep 06 2017 02:03 PM - edited Sep 06 2017 02:12 PM
I have the following lists with a lookup column that has more than 5,000 items:
Is it be possible to filter out items displayed in the lookup column based on (Status = Active) so that the app doesn't need to have too many items in the dropdown list and avoid the following error?
"This is a lookup column that displays data from another list that currently exceeds the List View Threshold defined by the administrator (5000)."
Note, I've tried to use a calculated column (IF(Status=Active,"PO Number","")) but this doesn't work. I saw a demo of custom InfoPath form with data filter but would like to investigate if there are other options first.
Sep 06 2017 10:56 PM
Sep 06 2017 11:18 PMSolution
Not a fix for the 5000 items issue, but an alternative to your current solution.
Can I assume that Purchase Orders and Invoices are not being changed ever, when the Purchase Order has status Closed? (I assume that)!
Move all Purchase Orders and Invoices into archive lists - Purchase Order Archive and Invoice Archive - thus keeping your active lists item count lower, hopefully below the limit.
Sep 07 2017 12:55 PM
Sep 07 2017 01:00 PM
Sep 07 2017 01:03 PM
Sep 21 2017 06:08 AM
You can call it a trick or a hack, but what I have done for filtered lookups is using calculated columns to display what I want in that column.
Say you have a Project list with a Status column and a Title column. The Title column is what you want to filter based on Status. Create a Calculated Title column called Active Projects with a calculation of =IF(Status="Active",[Title],""). This will create blank entries for items that do not have Status=Active.
Then go into your lookup column and point it to "Active Project" and it will not display anything that is blank.
Below is an example I used for displaying only Active Students, same would be for Active Projects or POs or whatever. I use this process that has a source list of 8000+ students with varying degree programs. I create calculated columns to only show students in lookup columns that match the degree criteria for that lookup.
Sep 21 2017 10:56 AM
Mar 16 2018 05:21 AM
Feb 11 2019 07:25 AM
Thank you for sharing this.
It works fine for lists < 5,000, those with more than 5,000 items will fail even if the number of items in the calculated column is < 5,000.
Hey, Microsoft, I can't help to remind you that 5,000 is not such large number anymore. You are keeping people shackled to a setting that is over A DECADE old!
Aug 21 2019 06:35 AM
@Scott Strausbaughexcellent, this did the trick. I was under the impression that calculated columns could not be used for lookup columns in another list, has this changed recently?
Just one minor caveat. How can I avoid the blank records (for each record with 'None') from being displayed at the top of the combo box?
I can move them to the bottom with the following formula, but descending sort order is not desired:
If I filter for non-blanks, it still shows one blank record at the top, can this be suppressed somehow?
Filter(Choices('Kaizen Championship'.Training_x0020_Batch),Not(IsBlank('Training Batch')))
Aug 21 2019 06:42 AM
@Beat Zimmerman you need to add a ,"" to the end of your calculated column formula. That signifies blank if it doesn't meet the criteria and the drop-down will ignore blanks.
Aug 21 2019 09:06 AM - edited Aug 21 2019 09:08 AM
@Scott StrausbaughI have it like this already for the calculated column in the schedules list:
=IF(OR([Last Enrollment Date]>=TODAY();[Last Enrollment Date]="");[Training Batch];"")
This works fine, batches with an overdue enrollment date get a blank value.
In the enrollment list I have a column Training Batch which is a lookup column for Active Batches.
When I edit an item in a custom form, where the items for the Training Batch are
I get a blank row in the selector for each schedule that has a blank Training Batch.
I can reverse the order
so they appear at the very bottom.
But there's no way to suppress the blank entries. I actually suspect they are not blank, but some Null Strings or the like, as I can save the record to SharePoint leaving the field blank although it's a required field (both in the Form and in the List).
The initial way to filter out the blanks actually didn't work, although it accepted the formula it stated that this formula requires scope which is not supported for evaluation. So I kept trying and found this one which does not require scope (at least it doesn't complain about it):
Filter(Choices('Kaizen Championship'.Training_x0020_Batch),Not(IsBlank('Kaizen Championship'.'Training_x0020_Batch')))
But still it doesn't work. It's simply ignored, which as well would confirm the field is not actually empty.
Any hints would be appreciated. Thank you!
Apr 17 2020 10:42 AM
This "hack" of using a calculated column value for the lookup list just made my day!!! Wow, this was so easy. I'm using it to filter a lookup column on a grid view. The normal methods of CAML like I use on the Edit forms don't work in a grid view.
Thank you to the author.
Aug 18 2020 07:54 AM
May I`m doing something wrong... As soon as I change the status from 'active' to 'inactive', the reports of list with lookup field state 'not assigned' in grouping this field. So link end seems loosing, which is an imaginary behaviour. I am quite new in SPO, is there a way to store the 'ID' of those active records instead of the linked content?
Feb 23 2023 10:26 AM
@Scott Strausbaugh my calculated column (PassedStudents) contains this: =IF(Passed=TRUE,[Student Name],"")
When I create a lookup column within the same list, it works as expected. No blanks.
But when I create a lookup in another list, the blanks are there. Any idea what I might be doing wrong?