cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Antonioclk
Helper III
Helper III

Nested dropdowns and related data sources

Hello in my app i need to have the user specify his city. I can't use the address component because it only allow you to search for a full address and for privacy i can't ask the user to type his address.

 

there are about 7000 cities, divided into 110 provinces, grouped in 21 regions.

 

I want to make a dropdown where the user must select one province and when he does that, the other dropdown shows all cities of that province.

 

How to do that? How can i prepare the sharepoint lists with cities? the best thing would be to have a single list with all cities, but would be too long, i know lists shouldn't be over 2000 items. Is it possible to have 21 lists one for each region, and then show the dropdown as i described? how?

 

Thank you so much 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @Antonioclk ,

First one will (obviously) get a list of your Regions), then for the Provence list

Filter(
   ProvinceList,
   Region = DropRegion.Selected.Name
).Province

then for the City List

Filter(
   CitiesList,
   Province = DropProvince.Selected.Province
)

Although if no Region has more than 2,000 cities (and you set your Delegation limit to this), you do not need the Provence list - the Provence drop-down would be

With(
   {
      wList:
      Filter(
         CitiesList,
         Region = DropRegion.Selected.Name
      )
   },
   Distinct(
      wList,
      Provence
   ).Provence
)

And your Cities

Filter(
   CitiesList,
   Province = DropProvince.Selected.Result
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

View solution in original post

6 REPLIES 6
WarrenBelz
Super User
Super User

Hi @Antonioclk ,

The easiest way would be one list with the 7,000 cities and the province and region on each one. You would also need a separate list of regions as you cannot get a distinct list with that many records.

Antonioclk
Helper III
Helper III

I made a collection named RegionList where i added 20 regions, then i made an excel (loaded with onedrive business) with 2 tables:

  • CitiesList : with 5 columns: Region, Province, City, Lat, Long
  • ProvinceList: with 2 columns: Province, Region

In the app I added 3 components

  1.  DropRegion, linked with the RegionList collection
  2. DropProvince, linked with the oneDrive connector with the ProvinceList table. I added a depend on filter Filter(myCities, Region = DropRegion.Selected.Name)
  3. DropCity, linked with the oneDrive connector with the CitiesList table, I added a depend on filter Filter(myCities, Province = DropProvince.Selected.Province)


When the user open the app, he should select the region in the first dropdown, then the second one should be filled with provinces related to the region selected in the previous dropdown and when he select a province, the last dropdown should be filled with cities related to that province.

 

it actually works, but if i select certain provinces, it won't load any city... but i can see that in the excel file there are cities related to such province. why? 

It seems everything correct, what am i doing wrong?

 

edit: it seems like it works for the first 2 regions on all provinces, then on the third region it loads just cities of the first provinces, and then it doesn't load anything else. Can it be a loading/memory error? how to fix eventually?
 

Hi @Antonioclk ,

First one will (obviously) get a list of your Regions), then for the Provence list

Filter(
   ProvinceList,
   Region = DropRegion.Selected.Name
).Province

then for the City List

Filter(
   CitiesList,
   Province = DropProvince.Selected.Province
)

Although if no Region has more than 2,000 cities (and you set your Delegation limit to this), you do not need the Provence list - the Provence drop-down would be

With(
   {
      wList:
      Filter(
         CitiesList,
         Region = DropRegion.Selected.Name
      )
   },
   Distinct(
      wList,
      Provence
   ).Provence
)

And your Cities

Filter(
   CitiesList,
   Province = DropProvince.Selected.Result
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

it does work with the code i wrote, and sadly there are region with over 2000 cities so i need to split in province too. 

the problem is that the third dropdown doesn't work after a certain point.

 

If a user select any region, the provinces will appear. But cities doesn't work well. They appear only for all the provinces of the first 2 regions and for the first province of the third region, but after that they don't appear.

it's super weird 

@Antonioclk ,

What is your Delegation limit ? If not 2,000, set it to this and use the first two code items I sent.

WarrenBelz
Super User
Super User

Hi @Antonioclk ,

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

Helpful resources

Announcements
Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on August 17, 2022 at 8am PDT.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Users online (4,404)