Cascading multi selection combobox from choices columns

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hello,

I have a single sharepoint list containing mainy columns.

Some of them are choices columns I use to filter gallery results.

I would like to use a multi selection item combobox to fill a second one but failed to achieve it.

Example could be this:

1st choices column "Country" contains Spain, France, Italy, Germany

2nd choices column "City" contains Paris, Nice, Roma, Milano, Berlin, Studgart etc...

In my Fisrt combobox, when I select France and Italy, the second one should be field with Paris, Nice, Roma, Milano

I failed in many possibilities. When I get 1st combobox, SelectedItems, I'm not able to filter in a way or another my list to retrive related cities. With Filter or Forall function, usinf collection or not, I just get a collection of record or table and not a simple list of item to populate my second combobox.

Please some help would be appreciated 🙂

Categories:

Building Power Apps

All responses (9)

Answers (1)

Sort by

WarrenBelz 156,532 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @Anonymous ,

Are these simply Choices columns with the countries and cities hard-coded or are they both derived from the same list, where you have a record with the matching value?

If for instance the Items of the first drop-down (I will call it cbCountry) was

Distinct( YourSPListName, Countries ).Result

then the Items of the second would be

With( {wList: SPListName}, Filter( wList, Country in cbCountry.SelectedItems.Result ).City )

Note the With() Statement is only to avoid the Delegation warning. Also all of this will only work on lists up to your (500 - 2,000) Delegation limit as the in Filter is not Delegable.

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.
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @WarrenBelz and thank you for your answer (really appreciated).

Both columns (Country and City) are Choices columns in the same SPlist.

My first combobox is populated this way
Choices(SPListName.Country)

Then the With() return an error above the /.Result (Name isn't valid. The identifier is not recognized)

With( {wList: SPListName}, Filter( wList, ColNameCountry in cbCountry.SelectedItems.Result ).City )

cbCountry.SelectedItems has a Table DataType
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
@WarrenBelz I get rid of the error by adding value attribute to my column name ColNameCountry.value and replacing .Result by .Value

With( {wList: SPListName}, Filter( wList, Country.Value in cbCountry.SelectedItems.Value ).City )

Teh result of this function is a Table of many records (I get 6 rows in my Table and each is not value or text but a record).

There's where I'm stuck. How to retreive the value of those records to populate my City combobox ?
WarrenBelz 156,532 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

@Anonymous ,

That is because my example used a Distinct filter on the first drop-down - you need .Value . I have a blog on Controls and their references that may be of use to you.

I am assuming here that you have hard-coded the countries in the Choice field - if so you also need them in a field in the list to do the second filter.

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.
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Let's start over 🙂

here is my form to sort a Galery

Combobox Country SPList Column type Choices with harde coded values
I get the Items with

Choices(TestCountryList.Country)

The second combobox City SPList Column type Choices with hard-coded values
I get the items with

Choices(TestCountryList.City)

When I select Country (ex: France & Spain), it filters my Gallerry and should popumate City field accordingly to my selection

Here with Spain & France I should only get (Paris, Nice, Barcelona & Madrid as items in my City Lists) instead of a full choices items.

Gallery filtering is working fine

Filter( TestCountryList, IsBlank(cbCountry.SelectedItems) || IsEmpty(cbCountry.SelectedItems) || Country in cbCountry.SelectedItems, IsBlank(cbCity.SelectedItems) || IsEmpty(cbCity.SelectedItems) || City in cbCity.SelectedItems )

I tried this:
Create a collection of my cities that I applied for Items value.
OnChange attribute of my Country selection I clearCollect the collection and populate it with the filtered value of the column based on the selecteditems

ClearCollect(colCity, Filter(TestCountryList, Country in cbCountry.SelectedItems).City )

But the updated collection is now a set of distinct record with only one value inside
WarrenBelz 156,532 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!

@Anonymous ,

The confusion is that you have hard-coded both columns (which you did not mention until now) and is a bit of an unusual approach.

One solution would be to simply make the second one a Text column and use the approach I have posted getting the values from the list filtered on the first drop-down.

I looked at filtering the Choices of the second box, but you cannot really do this as they do not contain the Country value you need to filter on.

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.
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @WarrenBelz ,
Sorry for the delay (days off in France 😉 )

Althought I understand the difference between both scenario, I don't get the "unusual" approach as I have all my informations within a single SP List so I always get for any row the country and city information.
The 2 hard-coded columns is a choice driven by :
- the fact that SP List let me do this on as many column I want 😉
- the seciruty it brings in filling the column values when I'll let end user do so (avoiding unnecessary mistakes)

So it felt natural to do with this 2 hrad-coded columns as choices as I'm not going to have plenty of choices in here.

Another solution I know would be to get another List for my countries and another one for my cities so I can call the other lists when it comes to fill the form to create an entry in the main list and let columns as text values in the main list.
But that's not what I intend to do.

If you find a solutio, I'm pretty sure it will help alot of people 😉

Many thank for your time and let's make this challenge a succes !
Verified answer

WarrenBelz 156,532 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @Anonymous ,

Firstly, this should give you the selection you need if used in the Items of cbCity

Filter( TestCountryList, Country in cbCountry.SelectedItems ).City

I had a look at trying to filter a Choices list using the value - it may be possible - but why do it when you can simply get the values from a filtering of the list you have.

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.
WarrenBelz 156,532 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Anonymous ,

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.