Forum Discussion

Copper Contributor

Sep 29, 2022

Solved

How to filter lookup column based on another column from using specific criteria?

Hello, I have the following lists List - 1 : EmployeeList EmployeeName - Single line text column Active- Choice column : True or false List - 2 : EmployeeWorkLoad EmployeeName - looku...

SvenSieverding
Oct 02, 2022
You could create a new calculated column in the EmployeeList, that only displays the EmployeeName if the employee is active...And nothing otherwise.

=IF([Active],[EmployeeName],"")

Then change the lookup column EmployeeName the EmployeeWorkLoad list to use that column instead of the EmployeeName column

SvenSieverding

Bronze Contributor

Oct 02, 2022

You could create a new calculated column in the EmployeeList, that only displays the EmployeeName if the employee is active...And nothing otherwise.

=IF([Active],[EmployeeName],"")

Then change the lookup column EmployeeName the EmployeeWorkLoad list to use that column instead of the EmployeeName column

New_learner

Copper Contributor

Oct 03, 2022

SvenSieverding,

I have done like this only.

=IF(Active="true",Name,"")

And it worked as expected.
Thank you for your response.

mobmsc
Copper Contributor
Mar 16, 2023
Does that produce a lookup list with alot of blanks ? If you have 200 employees of which 50 are inactive does the lookup list still show 200 rows but only the names of 150?
- New_learner
  Copper Contributor
  Mar 17, 2023
  mobmsc SvenSieverding
  Yes, its shows blank rows for inactive employees.
  so like If you have 200 employees of which 50 are inactive, the lookup list still shows 200 rows but only the names of 150.
  
  Do you have a solution for this? It will help.
  - SvenSieverding
    Bronze Contributor
    Mar 17, 2023
    New_learner @mobmsc
    
    Given you have a list with 200 entries like this (197 entries that are inactive und 3 that are active)
    
    and with a calculated field calc defined like this
    =IF([Active],[Title],"")
    
    If you now create another list with a new lookup column "Lookup" to that list on the field "calc", then that lookup column will look like this on a classic SharePoint form
    
    and like this on a modern form
    
    So only the three active users will be displayed, 197 inactive Users will not be visible.
    
    Best Regards,
    Sven
- SvenSieverding
  Bronze Contributor
  Mar 17, 2023
  Hi mobmsc,
  
  no. You just see one blank entry.
  
  Best Regards,
  Sven