cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Marissa_Yee
Level: Powered On

Cascading Combo Box

Hello,

 

I had previously customized a SharePoint list form using PowerApps successfully early in January. However, after the 1/18/2018 updates, I can no longer customoize a SharePoint list form the same way.

 

I have a SharePoint list, Expense Report, that is going to be used to record expenese incurred on projects.  This list has 2 SharePont lookup fields from 2 other SharePoint lists - Clients and Projects.  We'll first select the Client and then based on that the associated Projects would be displayed for selection.  Previously, I had done this using galleries and within the edit form the Client and Project fields were automatically populated with the selections made within the galleries (I used WonderLaura's videos/blog posts).  The fields within the edit form were the Lookup control that was provided; however, now it seems the Lookup control has been replaced with a Combo Box control.

 

I have not been able to find much guidance on the use of Combo Boxes and it is unclear to me what properties to use and modify in order to be able to record the selection of Client and Project from the galleries set up.  This is the formula i had previously used for the Lookup controls for the Default property of the data card:

 

{ '@odata.type' : "Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference", Id : Value(GalleryClient.Selected.ID), Value: GalleryClient.Selected.Title}

 

Does anyone have any recommendations with how to achieve cascading combo boxes and create records for a SharePoint list using customized PowerApps forms?

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
egallis
Level 10

Re: Cascading Combo Box

Hi all,

 

After struggling for a long time with combo boxes and cascading them, I've come up with an easy logic to follow when it comes to manipulating combo boxes.

So here are a few tips and tricks on this particular topic...

 

When you customize a form in PowerApps, the system automatically sets a field as a combo box if its datatype is one of the following:

  • Choice field
  • Lookup field
  • Managed metadata field
  • User field

But you already know this... Smiley Happy

 

The structure of the items in the combo box will then vary according to the field's data type:

  • Choice field

 

{
    Value
}

 

  • Lookup field

 

{
    Id,
    Value
}

 

  • Managed metadata field

 

{
    Label,
    Path,
    TermGuid,
    Value, /* <label>|<guid> */
    WssId
}

 

  • User field

 

{
    Claims,
    Department,
    DisplayName,
    Email,
    JobTitle,
    Picture
}

 

But you probably already know this as well.

 

What is interesting to understand is that, whatever the data type of the field (whatever the structure of the combo box items), the Items property will always be defined as:

 

Choices(<your datasource>.<your field>)

 

 

Here comes the important part...

 

Whatever you want to do with your combo box (define a default value, cascade combo boxes, filter the list of items in your combo box) consider the Choices(...) 'function' as your 'combo box datasource'. You have to respect the data type of your combo box items by always working with the Choices(...) 'items list'.

And forget about using the { '@odata.type' : "Microsoft.Azure.Connectors.SharePoint. ...", Id : ..., Value: ...} artefact (which is usefull in very limited cases, by example for setting a default user in a user field).

 

This means that, if you want to set a default value for your combo box, do it this way (always use the DefaultSelectedItems property):

 

LookUp(Choices(<your datasource>.<your field>), Value='<your default value>')

 

 

It also means that if you want to filter the list of items in your combo box, you have to do it this way:

 

Filter(Choices(<your datasource>.<your field>), <your filter condition>)

 

 

And if you want to cascade lookup combo boxes, you should do it this way:

 

Filter(
Choices(<datasource>.<lookup field>),
Id in ShowColumns(
Filter(<lookup list>,<your condition>),
"ID"
)
)

A bit of an explanation here...

 

The Filter(Choices(...),...) part does not change. We want to filter our combo box 'datasource'.

Then, for the filter condition, we can only use the Id or the Value properties. Here I use the Id property.

Now, the Id in... part means: "I want to specify which items in my Choices(...) I want to keep as items for the combo box."

Since the left-side of the in operator is the Id 'column', on the right side of the in operator I must have a list of ids. That's why I use the ShowColumns(..., "ID") function.

Finally, inside the ShowColumns function, the datasource (first parameter) is where I will filter my lookup items. And in the filter condition I can just filter on any other field in the lookup list.

 

Here is an example... Let's say you have the following lists:

  • Countries
    • CountryName (text)
  • Cities
    • CityName (text)
    • Country (lookup to Countries>CountryName)
  •  Companies
    • CompanyName (text)
    • CompanyCountry (lookup to Countries>CountryName)
    • CompanyCity (lookup to Cities>CityName)

Let's say that you customize the Companies list form and that you want the following behavior: after choosing a country in CompanyCountry combo box, I want the CompanyCity combo box to show me only cities from that country.

Here is how you should set the Items property of the CompanyCity combo box:

 

Filter(
    Choices(Companies.CompanyCity),
    Id in ShowColumns(
        Filter(Cities,Country.Id=DataCardValueCompanyCountry.Selected.Id),
        "ID"
    )
)

 

 

Note: PowerApps provides now an automatic way of cascading combo boxes:

Image 1.png

 

Hope this helped...

Emmanuel

15 REPLIES 15
Meneghino
Level 10

Re: Cascading Combo Box

Hello @Marissa_Yee

My constant recommendation is not to use the standard forms, but to build your own.

How to implement cascading dropdowns/combo boxes depends a bit on the data structure otherwise you risk running into performance issues.  I am happy to hop ona a Skype call to give you some hints/help.  Will send contacts via private message.

skylitedave
Level 10

Re: Cascading Combo Box

did you ever get an answer on this?

 

Cascading Combobox is pretty common now ...

sgrpdl
Level: Powered On

Re: Cascading Combo Box

I am too seraching easy sol for multi select cascading combo box.. till the date.. 


@skylitedave wrote:

did you ever get an answer on this?

 

Cascading Combobox is pretty common now ...


 

Highlighted
egallis
Level 10

Re: Cascading Combo Box

Hi all,

 

After struggling for a long time with combo boxes and cascading them, I've come up with an easy logic to follow when it comes to manipulating combo boxes.

So here are a few tips and tricks on this particular topic...

 

When you customize a form in PowerApps, the system automatically sets a field as a combo box if its datatype is one of the following:

  • Choice field
  • Lookup field
  • Managed metadata field
  • User field

But you already know this... Smiley Happy

 

The structure of the items in the combo box will then vary according to the field's data type:

  • Choice field

 

{
    Value
}

 

  • Lookup field

 

{
    Id,
    Value
}

 

  • Managed metadata field

 

{
    Label,
    Path,
    TermGuid,
    Value, /* <label>|<guid> */
    WssId
}

 

  • User field

 

{
    Claims,
    Department,
    DisplayName,
    Email,
    JobTitle,
    Picture
}

 

But you probably already know this as well.

 

What is interesting to understand is that, whatever the data type of the field (whatever the structure of the combo box items), the Items property will always be defined as:

 

Choices(<your datasource>.<your field>)

 

 

Here comes the important part...

 

Whatever you want to do with your combo box (define a default value, cascade combo boxes, filter the list of items in your combo box) consider the Choices(...) 'function' as your 'combo box datasource'. You have to respect the data type of your combo box items by always working with the Choices(...) 'items list'.

And forget about using the { '@odata.type' : "Microsoft.Azure.Connectors.SharePoint. ...", Id : ..., Value: ...} artefact (which is usefull in very limited cases, by example for setting a default user in a user field).

 

This means that, if you want to set a default value for your combo box, do it this way (always use the DefaultSelectedItems property):

 

LookUp(Choices(<your datasource>.<your field>), Value='<your default value>')

 

 

It also means that if you want to filter the list of items in your combo box, you have to do it this way:

 

Filter(Choices(<your datasource>.<your field>), <your filter condition>)

 

 

And if you want to cascade lookup combo boxes, you should do it this way:

 

Filter(
Choices(<datasource>.<lookup field>),
Id in ShowColumns(
Filter(<lookup list>,<your condition>),
"ID"
)
)

A bit of an explanation here...

 

The Filter(Choices(...),...) part does not change. We want to filter our combo box 'datasource'.

Then, for the filter condition, we can only use the Id or the Value properties. Here I use the Id property.

Now, the Id in... part means: "I want to specify which items in my Choices(...) I want to keep as items for the combo box."

Since the left-side of the in operator is the Id 'column', on the right side of the in operator I must have a list of ids. That's why I use the ShowColumns(..., "ID") function.

Finally, inside the ShowColumns function, the datasource (first parameter) is where I will filter my lookup items. And in the filter condition I can just filter on any other field in the lookup list.

 

Here is an example... Let's say you have the following lists:

  • Countries
    • CountryName (text)
  • Cities
    • CityName (text)
    • Country (lookup to Countries>CountryName)
  •  Companies
    • CompanyName (text)
    • CompanyCountry (lookup to Countries>CountryName)
    • CompanyCity (lookup to Cities>CityName)

Let's say that you customize the Companies list form and that you want the following behavior: after choosing a country in CompanyCountry combo box, I want the CompanyCity combo box to show me only cities from that country.

Here is how you should set the Items property of the CompanyCity combo box:

 

Filter(
    Choices(Companies.CompanyCity),
    Id in ShowColumns(
        Filter(Cities,Country.Id=DataCardValueCompanyCountry.Selected.Id),
        "ID"
    )
)

 

 

Note: PowerApps provides now an automatic way of cascading combo boxes:

Image 1.png

 

Hope this helped...

Emmanuel

sgrpdl
Level: Powered On

Re: Cascading Combo Box

Hi @egallis 

but question is

how we can cascde if we select two or more items in companycountry combo

Suppose I select China and India in companycountry combo then i should get option to select city coresponding to it's country eg 

see attachment.. i could not solve it Smiley Sad

 


@egallis wrote:

Hi all,

 

After struggling for a long time with combo boxes and cascading them, I've come up with an easy logic to follow when it comes to manipulating combo boxes.

So here are a few tips and tricks on this particular topic...

 

When you customize a form in PowerApps, the system automatically sets a field as a combo box if its datatype is one of the following:

  • Choice field
  • Lookup field
  • Managed metadata field
  • User field

But you already know this... Smiley Happy

 

The structure of the items in the combo box will then vary according to the field's data type:

  • Choice field

 

{
    Value
}

 

  • Lookup field

 

{
    Id,
    Value
}

 

  • Managed metadata field

 

{
    Label,
    Path,
    TermGuid,
    Value, /* <label>|<guid> */
    WssId
}

 

  • User field

 

{
    Claims,
    Department,
    DisplayName,
    Email,
    JobTitle,
    Picture
}

 

But you probably already know this as well.

 

What is interesting to understand is that, whatever the data type of the field (whatever the structure of the combo box items), the Items property will always be defined as:

 

Choices(<your datasource>.<your field>)

 

 

Here comes the important part...

 

Whatever you want to do with your combo box (define a default value, cascade combo boxes, filter the list of items in your combo box) consider the Choices(...) 'function' as your 'combo box datasource'. You have to respect the data type of your combo box items by always working with the Choices(...) 'items list'.

And forget about using the { '@odata.type' : "Microsoft.Azure.Connectors.SharePoint. ...", Id : ..., Value: ...} artefact (which is usefull in very limited cases, by example for setting a default user in a user field).

 

This means that, if you want to set a default value for your combo box, do it this way (always use the DefaultSelectedItems property):

 

LookUp(Choices(<your datasource>.<your field>), Value='<your default value>')

 

 

It also means that if you want to filter the list of items in your combo box, you have to do it this way:

 

Filter(Choices(<your datasource>.<your field>), <your filter condition>)

 

 

And if you want to cascade lookup combo boxes, you should do it this way:

 

Filter(
Choices(<datasource>.<lookup field>),
Id in ShowColumns(
Filter(<lookup list>,<your condition>),
"ID"
)
)

A bit of an explanation here...

 

The Filter(Choices(...),...) part does not change. We want to filter our combo box 'datasource'.

Then, for the filter condition, we can only use the Id or the Value properties. Here I use the Id property.

Now, the Id in... part means: "I want to specify which items in my Choices(...) I want to keep as items for the combo box."

Since the left-side of the in operator is the Id 'column', on the right side of the in operator I must have a list of ids. That's why I use the ShowColumns(..., "ID") function.

Finally, inside the ShowColumns function, the datasource (first parameter) is where I will filter my lookup items. And in the filter condition I can just filter on any other field in the lookup list.

 

Here is an example... Let's say you have the following lists:

  • Countries
    • CountryName (text)
  • Cities
    • CityName (text)
    • Country (lookup to Countries>CountryName)
  •  Companies
    • CompanyName (text)
    • CompanyCountry (lookup to Countries>CountryName)
    • CompanyCity (lookup to Cities>CityName)

Let's say that you customize the Companies list form and that you want the following behavior: after choosing a country in CompanyCountry combo box, I want the CompanyCity combo box to show me only cities from that country.

Here is how you should set the Items property of the CompanyCity combo box:

 

Filter(
    Choices(Companies.CompanyCity),
    Id in ShowColumns(
        Filter(Cities,Country.Id=DataCardValueCompanyCountry.Selected.Id),
        "ID"
    )
)

 

 

Note: PowerApps provides now an automatic way of cascading combo boxes:

Image 1.png

 

Hope this helped...

Emmanuel


 

egallis
Level 10

Re: Cascading Combo Box

And if your CompanyCountry field allows multiple selections and you want to see the cities for the selected countries (which could be a bit confusing by the way), you could just change the Filter function to something like this:

Filter(
    Choices(Companies.CompanyCity),
    Id in ShowColumns(
        Filter(Cities,Country.Id in ShowColumns(DataCardValueCompanyCountry.SelectedItems, "Id")),
        "ID"
    )
)

I haven't test it, so give me feedback about it...

Emmanuel

sgrpdl
Level: Powered On

Re: Cascading Combo Box

Wow! It works now..

Few things that i added...

in sharepont list in lookup, I  allowed multiple values

Also on visible property i added collection collcities to collect the cities details...

 

Items for companyciites looks:

Filter(Choices(Companies.CompanyCity), Id in ShowColumns(Filter(collcities,Country.Id in ShowColumns(DataCardValue2.SelectedItems,"Id")),"ID"))

 

Thanks a lots @egallis 

dileeprajam
Level: Powered On

Re: Cascading Combo Box

Hi @egallis ,

 

I am new to this tool and thank you for nice explanation of Combobox.

I am trying to create cascading comboboxes and my data source is a table (Table7) from excel and my data will be something like as shown below
image.png

Now when i try to set the Items for one combobox as "Choices(Table7.Column1)" i am getting below error:

image.png

 

Also can you please help me how can set 3 combo boxes one for each Column in my table and also they should show distinct values i.e

Combo box 1 - Shows A1, A2(distinct of Column1)
Combo box 2 - Shows distinct values from Column2 based on selected values from Combobox 1

 

thanks,

-Dileep

egallis
Level 10

Re: Cascading Combo Box

Hi @dileeprajam,

Ok, here is the solution (just tested it on my side):

ComboBox1
    Items -> Distinct(Table7,Column1)
    DisplayFields -> ["Result"]
    SearchFields -> ["Result"]
    SelectMultiple -> false
    OnChange -> UpdateContext({locCol2WrongValue: IsBlank(LookUp(MyTable,Column1=ComboBox1.Selected.Result && Column2=ComboBox2.Selected.Result))})

ComboBox2
Items -> Distinct(Filter(Table7,Column1=ComboBox1.Selected.Result),Column2)
DisplayFields -> ["Result"]
SearchFields -> ["Result"]
SelectMultiple -> false
Reset -> locCol2WrongValue
OnChange -> UpdateContext({locCol2WrongValue:false}); UpdateContext({locCol3WrongValue: IsBlank(LookUp(MyTable,Column1=ComboBox1.Selected.Result && Column2=ComboBox2.Selected.Result && Column3=ComboBox3.Selected.Result))})

ComboBox3
 Items -> Distinct(Filter(Table7,Column1=ComboBox1.Selected.Result && Column2=ComboBox2.Selected.Result),Column3)
DisplayFields -> ["Result"]
SearchFields -> ["Result"]
SelectMultiple -> false
Reset -> locCol2WrongValue || locCol3WrongValue
OnChange -> UpdateContext({locCol3WrongValue:false})

As you can see, I've added some code to the OnChange events of the combo boxes so that when the users has selected A1 & B3 and then he changes his mind and chooses A2, the ComboBox2 is reset and blanked because combination A2 & B3 does not exist.

Tell us if this works...

Emmanuel

PS: I hope I didn't forget anything... Smiley Happy

Helpful resources

Announcements
firstImage

PowerApps Monthly Community Call!

Join us next Wednesday for our Demo Extravaganza, October 16, 2019 8am PDT.

firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Top Kudoed Authors
Users Online
Currently online: 178 members 4,014 guests
Please welcome our newest community members: