Cascading Combo Box

[Anonymous]

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?

[Meneghino]

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.

[skylitedave]

did you ever get an answer on this?

Cascading Combobox is pretty common now ...

[sgrpdl]

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 · Accepted solution]

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

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:

Hope this helped...

Emmanuel

[sgrpdl]

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

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:

Hope this helped...

Emmanuel

---

[R3dKap]

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]

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]

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

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

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]

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