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

[New_learner]

Hello,

I have the following lists

• List - 1 : EmployeeList 
  • EmployeeName - Single line text column
  • Active- Choice column : True or false
    
    
• List - 2 : EmployeeWorkLoad
  • EmployeeName - lookup column from Employee List

Is it possible to filter out items displayed in the lookup column based on (Active= True) so that, while creating new entry in EmployeeWorkLoad List it won't show deactivated employees in look-up column.

[#2]

@New_learner This is not possible using SharePoint out of the box list forms.

You have to customize the list forms using Power apps for this. Then you can change the "items" property of lookup column combo-box/dropdown based on active column. Check below links for more information:

1. PowerApps filter on Lookup column from SharePoint 
2. Filter Lookup Dropdown list based on values in another SharePoint list 

---

Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

[#3 · Accepted solution]

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

[#4]

@SvenSieverding,

I have done like this only.

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

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

[#5]

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?

[#6]

Hi @mobmsc,

no. You just see one blank entry.

Best Regards,
Sven

[#7]

@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.

[#8]

@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

 

[#9]

@SvenSieverding Question: if the status of [Active] changes from true to false, will that affect history in the referencing table?