How to filter a lookup list based on which lookup is calling it?

Like (0) Share

Report

Posted on 29 Mar 2023 21:06:41 by jase

Hi,

I wondering if someone could suggest a / the solution to this query I have regarding filtering a list based on which lookup is calling it?

Scenario:

I have a table of Employees, which stores their Name and a Job Title (a fixed choice field)

On a form for another table I have two fields I wish to set:

Project Manager (lookup)
Branch Manager (lookup)

When selecting it, I would like the lookup for Project Manager to display the records in the Employees table but only show those whose Job Title is Project Manager, so the user can only select ones for the correct job. Then the same principle for the other lookup, but using the Branch Manager job title.

Do I need to create two new lookup views, each with their specific filter applied, and set each lookup on the form to call the relevant lookup view? Or is there something more simple I'm missing?

Thanks

Categories:

General Questions

All responses (4)

Answers (1)

jase 86 on 30 Mar 2023 at 08:44:47

Like (0)

Report

Re: How to filter a lookup list based on which lookup is calling it?

Thanks to both @CNT and @rswain.

For anyone else, as a conclusion I ended up creating two separate views on the Employees table; one filtering by 'Project Manager' and one filtering by 'Branch Manager'. Then I added the two lookups to my form; one pointing at the 'Project Manager' view and the other to the 'Branch Manager' view. Remember that you might need to force refresh your browser when trying it out to see the changes, as that caught me out to begin with!

I have a follow up question about adding multiple people of the same job title but I'll ask that in a separate post.

Thanks again.
Verified answer

rswain 173 on 30 Mar 2023 at 07:35:34

Like (1)

Report
Re: How to filter a lookup list based on which lookup is calling it?

Yes, you can create two separate views for the Employee table and apply the necessary filters for each view. For the Project Manager lookup, you can create a view where the Job Title is filtered to show only "Project Manager". Then, set the lookup control's "Lookup field" property to the "Name" column of the Employee table, and the "Lookup data source" property to the view you created for the Project Managers. Similarly, you can create another view for the Branch Managers and set the lookup control's "Lookup data source" property to this view. When the user selects a value from the Project Manager lookup, the selected value will be the Name of the Employee, but only those employees whose Job Title is "Project Manager" will be displayed in the lookup control. The same applies to the Branch Manager lookup.
jase 86 on 29 Mar 2023 at 21:39:45

Like (0)

Report

Re: How to filter a lookup list based on which lookup is calling it?

Thanks for the reply @CNT . I’ve realised I left off a key piece of information previously, which is that I’m creating a model driven app rather than canvas.

Is your solution still applicable?
CNT 10,919 Super User 2024 Season 1 on 29 Mar 2023 at 21:27:57

Like (1)

Report

Re: How to filter a lookup list based on which lookup is calling it?

@jase You can use Filter to get only the desired records. Assuming that you have a Dataverse table, the formula would be,

Filter(Employees, 'Job Title' = Choices('Job Title').'Project Manager')

Please remember to give a 👍 and accept my solution as it will help others in the future.