Forum Discussion
Is it possible to filter a lookup column using a specific criteria?
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 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:
Sort(Choices('Kaizen Championship'.Training_x0020_Batch),Value,Descending)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')))
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.
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?
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
Choices('Kaizen Championship'.Training_x0020_Batch)
I get a blank row in the selector for each schedule that has a blank Training Batch.
I can reverse the order
Sort(Choices('Kaizen Championship'.Training_x0020_Batch),Value,Descending)
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!
