Forum Discussion

New_learner's avatar
New_learner
Copper Contributor
Sep 29, 2022

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

  • SvenSieverding's avatar
    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's avatar
    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's avatar
      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's avatar
        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?
  • ganeshsanap's avatar
    ganeshsanap
    MVP
    Sep 29, 2022

    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.

Resources