cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

Filtering a lookup column according to another lookup column

Hi all,

 

I recently started using SharePoint Lists to connect to my PowerApp, so excuse me if this is fairly simple.

 

I have a main table, in which users provide a variety of information in the "editform" including country and city that the delivery has to be sent to.

 

FilterAccordingToCountryAndCity3.JPG

 

I have two lookup columns for country and city, which I am connecting to the lists "Cities" and "Countries" respectively as can be seen below:

 

 

FilterAccordingToCountryAndCity1.JPGFilterAccordingToCountryAndCity2.JPG

 

 

Now I would like for the list to filter down to only show the cities that are geographically located in the country I have chosen like seen below:

 

FilterAccordingToCountryAndCity4.JPG

 

How do I make sure that these two lists connect so that when I choose Germany (as done on the right hand side) it will only show fx. "Berlin","Düsseldorf" and "Hamburg"?

 

7 REPLIES 7
Highlighted
Advocate I
Advocate I

Hi @Anonymous 

 

Since you are having country as well as city in one sharepoint list, I could imagine that the Filter-function might solve your problem. It would look like that on your city combobox/dropdown:

 

Filter( SharepointList, Country = dropdownCountry ).City

You can then also use the Distinct-function to make sure that every city is shown only once.
https://docs.microsoft.com/de-de/powerapps/maker/canvas-apps/functions/function-distinct

Please let me know if this works for you or if I misunderstood something.

Highlighted
Anonymous
Not applicable

@brimstone 

 

Thank you for the response! I am not sure I am following. So right now I have three Sharepoint lists:

 

1. main table where all information gets stored (including country and city of shipment)

 

2. country

 

3. city

 

Does it make sense if the "Cities" sharepoint list has the country in a column so that it can be filtered according to the country that way? In that case how do I do that?

Highlighted
Anonymous
Not applicable

Does anyone have an idea how to do this?

Highlighted

Hi @Anonymous 

 

I would just use the first of your sharepoint lists, as you have both of your needed columns, country and city, there together. Therefore there is no need to add a column to one of the other lists. 

I would use the first sharepoint list for both of your item values and display each city and country. 

 

So first your country dropdown/combobox item value can look like this (Distinct is necessary to list every country once):
Distinct(SharepointList.Country, Country)

And then your dropdown/combobox item value for city looks like this:
Distinct(Filter(SharepointList, Country = dropdownCountry.Selected.Result).City, City)

Hope this helps

 

Highlighted
Anonymous
Not applicable

@brimstone 

 

Okay now I get it. I am trying to use the distinct function, but when I use this:

 

Distinct([@'Main Table'].Country, Country)

 

It shows an empty combobox..

 

The original item is:

 

Choices([@'Main Table'].Country)

 

I have been trying to write the formula with "Choices()" in it, but it gives me an error

Highlighted

Hi @Anonymous 

 

I don't have much experience with Choices, but if this works for your country combobox then I wouldn't change it. I just showed the possible code for both comboboxes to make it more understandable. Also the code for your city combobox is the one you need to get your desired outcome, so please try to change only this code with the one I recommended. I hope this will help you.

 

Highlighted
Anonymous
Not applicable

@brimstone 

 

Right I understand it now. I have managed to filter it down to the right cities. However, now another problem evolves. I am not able to save the parent.default (the chosen item in the cities combobox) after submitting the editform.

 

In a previous application I created using an excelsheet as datasource I put the following in the defaultselecteditems:

 

RenameColumns(Split(Parent.Default,","),"Result","Senior_x0020_Advisor")

 

and in the update property of the card:

 

Left(
    Concat(ComboBox3.SelectedItems,'Senior Advisor'& ","),
    Len(Concat(ComboBox3.SelectedItems,'Senior Advisor'& ","))-1
)

 

then it would keep the information. However, the information does not get saved in the combobox now using a sharepoint list as a datasource.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (9,523)