Forum Discussion
Fred Y
Sep 06, 2017Iron Contributor
Is it possible to filter a lookup column using a specific criteria?
I have the following lists with a lookup column that has more than 5,000 items: Purchase Orders list PO Number - text Status - Active or Closed Invoices list PO Number - lookup colum...
- Sep 07, 2017
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.
Kind regards
John
Scott Strausbaugh
Sep 21, 2017Iron Contributor
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.
Pablo Destefanis
Feb 11, 2019Iron Contributor
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!