cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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
Super User
Super User

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

View solution in original post

38 REPLIES 38
Highlighted
Community Champion
Community Champion

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.

Highlighted
Kudo Kingpin
Kudo Kingpin

Re: Cascading Combo Box

did you ever get an answer on this?

 

Cascading Combobox is pretty common now ...

Highlighted
Helper II
Helper II

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
Super User
Super User

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

View solution in original post

Highlighted
Helper II
Helper II

Re: Cascading Combo Box

Hi @R3dKap 

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 😞

 


@R3dKap 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


 

Highlighted
Super User
Super User

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

Highlighted
Helper II
Helper II

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 @R3dKap 

Highlighted
Helper I
Helper I

Re: Cascading Combo Box

Hi @R3dKap ,

 

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

Highlighted
Super User
Super User

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... 🙂

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

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