Unanswered

Dropdown control where value is a lookup column (relationship column)

(0) Share

Report

Posted on by Community Power Pla...

Hello

I am currently trying my hand out building some power apps but have run into a bit of a problem I am not sure how to solve.

I am using the Microsoft dataverse to store the data

I have 2 data tables:

users table: contains a first name, last name, company (lookup to the company table) etc.

Company table: contains, company name, Managing director (this is a user, so lookup to the user table), reporting responsible (is also a user, so lookup to the user table)

Let's say i have a gallery showing the companies in the company table, i now want to filter that based on who they have assigned as Managing director. I would like to do this from a dropdown, but I can't make it so that my choices is a list of users that are assigned as managing directors in the company table.

If i use the Company table as the "items" in the dropdown control, I can't select Managing director as the "value" as it is basically a record of a lot of data (I would think i could use the unique identifier and the return the requested value).

If I use the Users Table as the "Item" in the drowdown I can't seem to filter it so it shows only those users that have been assigned as a managing director in the Companies table.

I am not sure if i should somehow join the 2 tables as 1 collection first, or what the intended way to go about this is. If anyone can tell me how to go about this i would really appreciate it.

Thank you in advance

Categories:

Using Apps Using Formulas

All responses (3)

Answers (0)

EricRegnier 8,714 Most Valuable Professional on at

Like (0)

Report
Re: Dropdown control where value is a lookup column (relationship column)

Hi @Anonymous, did you get this resolved yet? If not, it's possible, see below example. Have drop down that is mapped to the Company table and filter the Items property of the gallery control of users:

Filter(Accounts, 'Owning User'.User = Dropdown1.Selected.User)

Note: using Accounts instead of Users and out-of-the-box Users instead of Company
Community Power Pla... on at

Like (0)

Report

Re: Dropdown control where value is a lookup column (relationship column)

Hi @EricRegnier

I think the problem I am running into is that the galleries director is a complex column. So i want to filter the gallery based on the GUID of the MD.

I did a quick mockup in excel, but as you can see, i want to sort the gallary on the company table, based on the director id, and i want the dropdown to show a list of users who have been assigned as directors in the company table. So the dropdown should only contain id 1 and 2, not 3 and 4, as they are not assigned as directors in the company table.

If it was excel I would just make a lookup based on the director id, and filter that column. Not quite sure how to do that in powerapps though.

I hope this helps explain, and I am not sure if this is something you would normally do in powerapps.

Thank you in advance
EricRegnier 8,714 Most Valuable Professional on at

Like (0)

Report
Re: Dropdown control where value is a lookup column (relationship column)

Hi @Anonymous,

The retrieve the related records whether you want to retrieve the related companies based on a User or the Users based on the company you can filter directly in the Filter expression. Example:

Filter(Companies, Director = Gallery1.Selected) //or Filter(Users, Company = Gallery1.Selected)

More on filter: https://docs.microsoft.com/powerapps/maker/canvas-apps/functions/function-filter-lookup

Hope this helps!