Another filtered lookup question

Copper Contributor

Ok, I hope I can explain this clearly.

I'm looking to filter a lookup (to another list) by data entered in the source list.  So, basically, I want lookup column B to display only the items that are associated with Column A. 

 

For example, I have a list of Open Positions (jobs) and each position has the property (hotel) associated with it.  I also have a Candidate list of applicants that apply for these positions. When I add an applicant to the Candidate list, I select the position they are applying for and the property where the position is located. Because I have many different positions located at different properties, I want to be able to filter the property list by the position name.  So, if I have 3 Executive Chef positions, I want the property lookup to only display the properties that need Executive Chefs instead of displaying all the properties that have open positions.

 

I hope this makes sense. Thanks for any input you have.

2 Replies

@asapboston 

In SharePoint Online, you can create a filtered lookup by using Power Apps to customize the form for your Candidate list. With Power Apps, you can add logic to your form to filter the choices in the Property dropdown based on the selected value in the Position dropdown.

Here’s a high-level overview of how you can set up a filtered lookup using Power Apps in SharePoint Online:

  1. In your Open Positions list, create a new Single line of text column called PositionProperty. Use a workflow or Power Automate flow to populate this column with the concatenated values of the Position and Property columns separated by a hyphen.
  2. In your Candidate list, create two new Lookup columns called Position and Property. Set the Get information from field for both columns to your Open Positions list and set the In this column field for the Position column to the Position column in the Open Positions list and for the Property column to the Property column in the Open Positions list.
  3. In your Candidate list, click on the Power Apps button in the command bar and select Customize forms. This will open the Power Apps editor where you can customize the form for your Candidate list.
  4. In the Power Apps editor, select the Property data card and go to the Advanced tab in the right-hand pane. In the DataField property, change the value from Property to PositionProperty.
  5. In the Update property of the Property data card, enter a formula that uses the Split function to split the selected value of the PositionProperty column at the hyphen and return only the second part of the split string (i.e., the Property value).
  6. In the Items property of the Property dropdown control within the Property data card, enter a formula that uses the Filter function to filter the choices in the Property dropdown based on the selected value in the Position dropdown.

Once you have set up your form with these customizations, save and publish your changes. Now, when you add a new item to your Candidate list and select a Position from the Position dropdown, the Property dropdown will automatically update to display only properties that need Executive Chefs based on the selected Position.

 

In SharePoint (not Online), you can create a filtered lookup by using a combination of calculated columns and the Cascading Dropdowns feature in the Lookup column settings.

Es wäre von Vorteil wenn genauere Informationen vorhanden wären.

 

Hope its helps!

Thank you very much for your suggestion. I was hoping I wouldn't have to use Power Apps so I may just have to live with it.