Forum Discussion
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 column from Purchase Order
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.
Thanks!
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
20 Replies
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.
- this is genius thank you!
- Pure genius Scott. This has made my day a lot easier!
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?
Thanks!
have you added an index to the Status column?
- Hi, Dean.
I did add PO Number and Status as user-defined indices but unfortunately still resulted into the error. As a workaround, I temporarily increased the threshold limit until I implement the archiving functionality.- what version of sharepoint are you using?
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
- Thanks, John.
This what I have been considering at this point i.e. copy over Closed items into a separate archive list/app.
I'll just have to think it through since I would imagine that people will still be interested to view Active and Closed items together for the current year.
- Is this SPO? If so, there is not much you can do here in the list to avoid the threshold limit...as an alternative you could try either to use search to create your view or some custom development
- Thanks, Juan.
It would be nice to use a specific list view that will be used by a lookup column.
