Filter by Lookup Column

(0) Share

Report

Posted on by SKK12

Hi All,

I have a SQL Table Employees with the following data. The Manager's column is referencing the ID in the same table.

I would like to build a dropdown showing the distinct Manager's by Name. So in this case the Manager's dropdown would include Amy, Carl, Emma. Then I want to use that dropdown to filter the managers. I got this to work where the dropdown shows the IDs but would like to have it show the names of the manager instead. I believe I need to use the addcolumns function but I am struggling with it.

Current DropDown items

Distinct('EmployeesTable',Manager)

Current Gallery Items

SortByColumns(Filter('EmployeesTable',Manager=Dropdown.Selected.Result),"Name",Ascending)

Any help would be greatly appreciated.

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Sort by

Verified answer

mdevaney 29,991 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
@SKK12

You will want to use a ComboBox instead of a Dropdown control. The ComboBox can display the Manager's Name but filter using the ID number. This is important because 2 managers might have the exact same name. ID numbers are unique and therefore will not cause issues.

Insert a new ComboBox onto the screen with this code in the Items property

Filter('Employees List', ID in Distinct('EmployeesTable',Manager).Result)

Then change the DisplayFields and SearchFields properties to this code

["Name"]

Finally, change your gallery Items code to this

SortByColumns(Filter('EmployeesTable',Manager=ComboBox.Selected.ID),"Name",Ascending)

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @SKK12

You can build out the filter using AddColumns for the foreign key in your table and then filter the resulting table according to your dropdown.. The syntax would be

Filter( AddColumns( Employees,"Mgr", Lookup( Managers, ID = ThisItem.managerID, ManagerName ) ), Mgr = Dropdown1.Selected.Result )

Was this reply helpful? Yes No
SKK12 10 on at

Like
a
(0)

Report
Copy link

Link copied!
Thanks for your response Drrickryp. I am still have trouble with this. I think there is a problem with the Lookup input. There is only one table called employees, as the managers are also employees. So I edited the lookup to (Employees, ID = ThisItem.ID

If I throw a label in the gallery referencing this new add column Mgr. It just referencing the manager of the first item for all the rows

Filter( AddColumns( Employees,"Mgr", Lookup( Employees, ID = ThisItem.ID, ManagerName ) ), Mgr = Dropdown1.Selected.Result )

Was this reply helpful? Yes No
SKK12 10 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you both for the quick replies. The combo box makes sense, for both the solutions provided I noticed the example code is referencing two different tables. Both an Employees and a Manager table. And I only have one table relating to itself. Would your solutions work in this instance. Because I tried both and I am still not getting anywhere

Was this reply helpful? Yes No