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

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
R3dKap
Community Champion
Community Champion

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

45 REPLIES 45
Meneghino
Community Champion
Community Champion

Hello @Anonymous

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.

did you ever get an answer on this?

 

Cascading Combobox is pretty common now ...

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


 

R3dKap
Community Champion
Community Champion

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

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


 

R3dKap
Community Champion
Community Champion

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

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 

Anonymous
Not applicable

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

R3dKap
Community Champion
Community Champion

Hi @Anonymous,

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

Celebrating the May Super User of the Month: Laurens Martens

  @LaurensM  is an exceptional contributor to the Power Platform Community. Super Users like Laurens inspire others through their example, encouragement, and active participation. We are excited to celebrated Laurens as our Super User of the Month for May 2024.   Consistent Engagement:  He consistently engages with the community by answering forum questions, sharing insights, and providing solutions. Laurens dedication helps other users find answers and overcome challenges.   Community Expertise: As a Super User, Laurens plays a crucial role in maintaining a knowledge sharing environment. Always ensuring a positive experience for everyone.   Leadership: He shares valuable insights on community growth, engagement, and future trends. Their contributions help shape the Power Platform Community.   Congratulations, Laurens Martens, for your outstanding work! Keep inspiring others and making a difference in the community!   Keep up the fantastic work!        

Check out the Copilot Studio Cookbook today!

We are excited to announce our new Copilot Cookbook Gallery in the Copilot Studio Community. We can't wait for you to share your expertise and your experience!    Join us for an amazing opportunity where you'll be one of the first to contribute to the Copilot Cookbook—your ultimate guide to mastering Microsoft Copilot. Whether you're seeking inspiration or grappling with a challenge while crafting apps, you probably already know that Copilot Cookbook is your reliable assistant, offering a wealth of tips and tricks at your fingertips--and we want you to add your expertise. What can you "cook" up?   Click this link to get started: https://aka.ms/CS_Copilot_Cookbook_Gallery   Don't miss out on this exclusive opportunity to be one of the first in the Community to share your app creation journey with Copilot. We'll be announcing a Cookbook Challenge very soon and want to make sure you one of the first "cooks" in the kitchen.   Don't miss your moment--start submitting in the Copilot Cookbook Gallery today!     Thank you,  Engagement Team

Announcing Power Apps Copilot Cookbook Gallery

We are excited to share that the all-new Copilot Cookbook Gallery for Power Apps is now available in the Power Apps Community, full of tips and tricks on how to best use Microsoft Copilot as you develop and create in Power Apps. The new Copilot Cookbook is your go-to resource when you need inspiration--or when you're stuck--and aren't sure how to best partner with Copilot while creating apps.   Whether you're looking for the best prompts or just want to know about responsible AI use, visit Copilot Cookbook for regular updates you can rely on--while also serving up some of your greatest tips and tricks for the Community. Check Out the new Copilot Cookbook for Power Apps today: Copilot Cookbook - Power Platform Community.  We can't wait to see what you "cook" up!      

Tuesday Tip | How to Report Spam in Our Community

It's time for another TUESDAY TIPS, your weekly connection with the most insightful tips and tricks that empower both newcomers and veterans in the Power Platform Community! Every Tuesday, we bring you a curated selection of the finest advice, distilled from the resources and tools in the Community. Whether you’re a seasoned member or just getting started, Tuesday Tips are the perfect compass guiding you across the dynamic landscape of the Power Platform Community.   As our community family expands each week, we revisit our essential tools, tips, and tricks to ensure you’re well-versed in the community’s pulse. Keep an eye on the News & Announcements for your weekly Tuesday Tips—you never know what you may learn!   Today's Tip: How to Report Spam in Our Community We strive to maintain a professional and helpful community, and part of that effort involves keeping our platform free of spam. If you encounter a post that you believe is spam, please follow these steps to report it: Locate the Post: Find the post in question within the community.Kebab Menu: Click on the "Kebab" menu | 3 Dots, on the top right of the post.Report Inappropriate Content: Select "Report Inappropriate Content" from the menu.Submit Report: Fill out any necessary details on the form and submit your report.   Our community team will review the report and take appropriate action to ensure our community remains a valuable resource for everyone.   Thank you for helping us keep the community clean and useful!

Community Roundup: A Look Back at Our Last 10 Tuesday Tips

As we continue to grow and learn together, it's important to reflect on the valuable insights we've shared. For today's #TuesdayTip, we're excited to take a moment to look back at the last 10 tips we've shared in case you missed any or want to revisit them. Thanks for your incredible support for this series--we're so glad it was able to help so many of you navigate your community experience!   Getting Started in the Community An overview of everything you need to know about navigating the community on one page!  Community Links: ○ Power Apps ○ Power Automate  ○ Power Pages  ○ Copilot Studio    Community Ranks and YOU Have you ever wondered how your fellow community members ascend the ranks within our community? We explain everything about ranks and how to achieve points so you can climb up in the rankings! Community Links: ○ Power Apps ○ Power Automate  ○ Power Pages  ○ Copilot Studio    Powering Up Your Community Profile Your Community User Profile is how the Community knows you--so it's essential that it works the way you need it to! From changing your username to updating contact information, this Knowledge Base Article is your best resource for powering up your profile. Community Links: ○ Power Apps ○ Power Automate  ○ Power Pages  ○ Copilot Studio    Community Blogs--A Great Place to Start There's so much you'll discover in the Community Blogs, and we hope you'll check them out today!  Community Links: ○ Power Apps ○ Power Automate  ○ Power Pages  ○ Copilot Studio    Unlocking Community Achievements and Earning Badges Across the Communities, you'll see badges on users profile that recognize and reward their engagement and contributions. Check out some details on Community badges--and find out more in the detailed link at the end of the article! Community Links: ○ Power Apps  ○ Power Automate  ○ Power Pages  ○ Copilot Studio    Blogging in the Community Interested in blogging? Everything you need to know on writing blogs in our four communities! Get started blogging across the Power Platform communities today! Community Links: ○ Power Apps  ○ Power Automate  ○ Power Pages  ○ Copilot Studio   Subscriptions & Notifications We don't want you to miss a thing in the community! Read all about how to subscribe to sections of our forums and how to setup your notifications! Community Links: ○ Power Apps  ○ Power Automate  ○ Power Pages  ○ Copilot Studio   Getting Started with Private Messages & Macros Do you want to enhance your communication in the Community and streamline your interactions? One of the best ways to do this is to ensure you are using Private Messaging--and the ever-handy macros that are available to you as a Community member! Community Links: ○ Power Apps  ○ Power Automate  ○ Power Pages  ○ Copilot Studio   Community User Groups Learn everything about being part of, starting, or leading a User Group in the Power Platform Community. Community Links: ○ Power Apps  ○ Power Automate  ○ Power Pages  ○ Copilot Studio   Update Your Community Profile Today! Keep your community profile up to date which is essential for staying connected and engaged with the community. Community Links: ○ Power Apps  ○ Power Automate  ○ Power Pages  ○ Copilot Studio   Thank you for being an integral part of our journey.   Here's to many more Tuesday Tips as we pave the way for a brighter, more connected future! As always, watch the News & Announcements for the next set of tips, coming soon!

Hear what's next for the Power Up Program

Hear from Principal Program Manager, Dimpi Gandhi, to discover the latest enhancements to the Microsoft #PowerUpProgram, including a new accelerated video-based curriculum crafted with the expertise of Microsoft MVPs, Rory Neary and Charlie Phipps-Bennett. If you’d like to hear what’s coming next, click the link below to sign up today! https://aka.ms/PowerUp  

Top Solution Authors
Top Kudoed Authors
Users online (3,249)