cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bimtng
Helper I
Helper I

Populate dropdown in a card depending on the data in the dropdown of another one

Hi all.

 

I need to populate de items of a dropdown in a form, depending on what the user has selected in another dropdown contained in other data card. I have tried something like this:

 

1. In first dropdown I have data of clients (datasource is "Clients").

2. In second dropdown I would like to load just the shops of the client selected in the first dropdown (datasource is "Shops").

3. In the items property of the second dropdown of the second datacard I have this code:

 

Filter(Shops; LookUp(Clients;Client_name=DataCardValue30.Selected.Value;Client_id) in Client_id)

 

DatacardValue30 is the datacard that contains the clients dropdown.

 

The result is that the second dropdown is always empty.

 

I would appreciate your help.

 

Thanks in advance.

 

1 ACCEPTED SOLUTION

Accepted Solutions

I got it.

 

What I have done is to "denormalize" the data. I have now just one table with four columns: Client_id, Client_name, Shop_id, and Shop_name. Client name is several times now in this table. I don´t like it, but it works. In DD1 I show distinct(Newtable,client_name). In DD2 i show the filtered shop_names of the selected client_name from DD1. Function distinct has been very useful for me.

 

Thanks very much again, hpkeong.

View solution in original post

12 REPLIES 12
hpkeong
Community Champion
Community Champion

Hi

 

As you are referring to first DD1, so may you try on second DD2 with

- Distinct(Filter(Shop, Client_Name = Dropdown1!Selected!Client), Shop)

- Distinct(Table, FieldYouWant)

 

DropDown1 shall be your DataCardValue30

 

Note:

DropDown expects a TABLE VALUES.

If you are using Gallery, Filter can show ALL fields.

But you are using DropDown, which only SHOW one field, and always from the first field.

Hence, Distinct(DS, SelectedField) will help you to show WHAT YOU WANT to show.

 

Hope this helps.

Have a nice day.

 

 

hpkeong

Thanks very much for your reply.

 

Still not working. I add ore information:

 

1. My Clients table has this structure (two fields): Client_id, Client_name.

2. My Shops table has this one (three fields): Client_id, Shop_id, Shop_name. Client_id is foreign key to clients table.

3. I populate the first DD with Client_name.

4. I want to populate the second DD with Shop_name, but only the ones that the Client_id field in Shops table is the same that the Client_id of the Client_name selected in DD1 in Clients table. This is the reason why I use lookup, just to search the Client_id that has been selected by his Client_name in DD1.

 

Thanks again.

hpkeong
Community Champion
Community Champion

Hi

Since you want only Shopid linked to selected ClientId, then you should have used Label (TexyBox) to show the results if you are using LookUp.

TextBox =LookUp(TableShops, Client_I'd = DD1.Selected.Value, Shop_Name).

If you insist to use DD,then
Distinct(Filter(TableShop, Client_Id = Dropdown1!Selected!Value), Shop_Name).
(anyway, this does not serve any purpose because the result contains only one result, unless you have multiple ShopName linked to single ClientId)

Hope this helps.
Have a nice day.
hpkeong

I need the second DD because I have a list of shops of the client selected in DD1, not just one shop. This is why i can not use a label.This is what i want: in second DD to be selected one of the shops, but just the shops of the client selected in DD1. If I take the whole shops table to populate DD2, the second DD would show all shops, but i want just the ones belonging to the client selected in DD1. I use lookup just to search de only client_id of the client_name selected in DD1. Then, I try to filter te shops result to just the ones (many) having this client_id. One client can have many shops.

 

Thanks for your patience.

... And what I show in DD1 is not Client_id, but Client_name. This is the reason why I have to lookup the client_id in Clients table.

 

Sorry.

hpkeong
Community Champion
Community Champion

Ok

I guess we are almost there but maybe I don't really get your final needs.

May you screenshot to show
1. example of your data
2. You desired results
So that I can propose exactly what you want.

FYI, most of the time, we are sharing ideas for you to design to your needs based on your explanation.

Thanks
hpkeong

I got it.

 

What I have done is to "denormalize" the data. I have now just one table with four columns: Client_id, Client_name, Shop_id, and Shop_name. Client name is several times now in this table. I don´t like it, but it works. In DD1 I show distinct(Newtable,client_name). In DD2 i show the filtered shop_names of the selected client_name from DD1. Function distinct has been very useful for me.

 

Thanks very much again, hpkeong.

View solution in original post

hpkeong
Community Champion
Community Champion

Hi

 

Glad to hear that it finally works.

It doesn't matter if you have 1 Table or 2 Tables, but 1 Table is ideal as you can always use Distinct to show the "field" that you want for Dropdown or ListBox.

In fact, https://powerapps.microsoft.com/en-us/tutorials/control-drop-down/

has mentioned the use of Distinct function for Dropdown but not not inside Distinct Tutorial.

 

Key notes are:

- Distinct, Filter will generate Table Values

- LookUp generates Single value

 

Hence, the above must always be reminded to oneself,

 

Have a nice day.

 

hpkeong

You were on the right track using the two tables, I've implemented something very similar to what you're talking about. What I discovered was that you can use any field from the from the selected record in your drop down, even if it is not the one that your drop down is showing. To do this the you need to put the whole table in the item property of your drop down.

 powerappstutorial1.jpg

 powerappstutorial2.jpg

 

 

As you can see, I'm using the info_list data source, and applying a filter to ignore any blank fields in the source_client_name column. Even with the filter, I can still choose which value to display in the drop down.

 

The Item, and value properties in my second drop down look like this:

powerappstutorial3.jpgThis code can be a bit confusing, but all it does is, it looks for the sub client names that start with the source client IDs, it then removes those IDs from thee start of the sub client names, and finally it ignores any empty fields. I added an or operator so that the filter function won't remove the default value that I also have in  my column.

 

My big break through with this code came when I realised that I can use source_client_drop_down.Selected.source_client_ID to filter the items in my second drop down. One of the big differences with my method and your's is that I used a two letter code in front of my dependant items in order to identify them, meaning the ID is part of he dependant item's name, instead of in being in another column. Although that means that I need to remove the ID before displaying it in my drop down, but that's not a big issue. 

 

With this method, you don't need to duplicate anything, because you can us the IDs to identify the dependant items.

 

The need to explicitly ignore empty fields is caused by the way new records are added to the info_list table. The number of sub clients is much larger than the number of source clients, and any new records that are created are placed at the bottom of my table. This means that any new sub clients or source clients added to the table will have several empty fields above them. The empty fields are then also shown in the drop down, that is why I need to explicitly ignore them. All of this is why I say two tables are the way to go for this type of data, because then any new items added would be directly beneath any old items, and the spreadsheet would look better.

 

Note that my data source is an excel spread sheet, and your experience regarding empty fields might differ for a different data source, but with this method there is no need to have any duplications, other that the source client ID.

 

I hope this gives you some more helpful information on how to handle the two drop downs.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (2,281)