cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Dropdown search on second table.

I have a drop down that lists a job number

dropdown.items= Sort(Distinct('[dbo].[JCM_MASTER__COMMITMENT]',Job),Result)

 

Currently i have another dropdown that lists the vendor number based on the job number selected.

dropdown1.items= Sort(Distinct(Filter('[dbo].[JCM_MASTER__COMMITMENT]',Job=dropbox.Selected.Value),Vendor),Result)

 

This will not work because my boss wants the user to pick a job number and the vendor name not the vendor number in the app. The problem is the vendor name is in a diffrent table. The vendor number is in both tables. Is there a way I can create a dropdown that will search my second table and return all vendor names based on vendor numbers that match the job number in the first dropdown?

 

Thanks in advance for any help

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Dropdown search on second table.

Hi Nick
It sounds like you have two tables with a many to many relationship. Ie one job can have many vendors and one vendor can have many jobs. If this is true then you can use the AddColumns() function to achieve your goal. However, it isn't clear why you are using Distinct() in the first Dropbox unless you have many jobs with the same job number but that seems unlikely.
I suggest charging the first dropdown items property to Sort('[dbo].[JCM_MASTER__COMMITMENT]', jobnumber) then go to the value dropdown in the right hand column under Advanced and select jobnumber to display in the window of the Dropbox.
The items property of the second dropdown control should be AddColumns(Filter('[dbo].[JCM_MASTER__COMMITMENT]', Job= Dropdown.Selected.jobnumber), "vendorname", Lookup(Table2, Dropdown.Selected.vendornumber = vendornumber, vendorname)
Tip - dropdown controls select a row from a table but only display one field from the row. However, all fields in the dropdown can be referenced by other controls. You can prove this to yourself by looking at the value under Advanced properties of the dropdown and all the fields are shown. Distinct() creates a one field table of unique values, renamed Result. By doing this you lose the ability to reference the other fields in the dropdown.

View solution in original post

4 REPLIES 4
Highlighted
Skilled Sharer
Skilled Sharer

Re: Dropdown search on second table.

Have you considered creating a collection OnStart joining the tables to include the vendor name, number and job number?  

Highlighted
Super User
Super User

Re: Dropdown search on second table.

Hi Nick
It sounds like you have two tables with a many to many relationship. Ie one job can have many vendors and one vendor can have many jobs. If this is true then you can use the AddColumns() function to achieve your goal. However, it isn't clear why you are using Distinct() in the first Dropbox unless you have many jobs with the same job number but that seems unlikely.
I suggest charging the first dropdown items property to Sort('[dbo].[JCM_MASTER__COMMITMENT]', jobnumber) then go to the value dropdown in the right hand column under Advanced and select jobnumber to display in the window of the Dropbox.
The items property of the second dropdown control should be AddColumns(Filter('[dbo].[JCM_MASTER__COMMITMENT]', Job= Dropdown.Selected.jobnumber), "vendorname", Lookup(Table2, Dropdown.Selected.vendornumber = vendornumber, vendorname)
Tip - dropdown controls select a row from a table but only display one field from the row. However, all fields in the dropdown can be referenced by other controls. You can prove this to yourself by looking at the value under Advanced properties of the dropdown and all the fields are shown. Distinct() creates a one field table of unique values, renamed Result. By doing this you lose the ability to reference the other fields in the dropdown.

View solution in original post

Highlighted
Frequent Visitor

Re: Dropdown search on second table.

Thanks for the replys guys sorry it took so long for me to respond. I ended up going with @Drrickryp method and it worked perfectly. Thank you so much

Highlighted
Super User
Super User

Re: Dropdown search on second table.

Ritz.gif

 

 

Helpful resources

Announcements
secondImage

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Robotic Process Automation

Let's talk about the solution provided by Microsoft for Robotic Process Automation (RPA)

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

Top Solution Authors
Top Kudoed Authors
Users online (6,843)