Cascading Dropdown with Multiple Choice Columns

[petewalburn]

Hi,

I've been working at this for a while now, and need some help.

I have a Sharepoint list with 4 columns - 'Business Unit', GeoMarket, 'Supported Countries' and 'Service Base'.

'Business Unit' is a choice field.

GeoMarket is a choice field.

'Support Countries' is a multiple choice field.

'Service Base' is a text field.

I need to have a drop down for selecting 'Business Unit'.

Then there is a drop down for GeoMarket, which is filtered by the selected 'Business Unit'

Then there is a drop down for Country which should list the countries in 'Supported Countries' for the selected GeoMarket.

Then there is a drop down for 'Service Base' which needs to have a list of all the 'Service Bases' that serve the selected Country.

I fill the 'Business Unit' dropdown with:

Ungroup(
  Table(
    {businessUnitOptions: Table({businessUnitValue: Blank()})},
    {businessUnitOptions: Sort(Distinct('Surface Service Facilities',BU.Value),Result,Ascending)}
  ),"businessUnitOptions")

Then, the GeoMarket drop down is populated with:

Ungroup(
  Table(
    {geoMarketOptions: Table({geoMarketValue: Blank()})},
    {geoMarketOptions: Sort(Distinct(Filter('Surface Service  Facilities',BU.Value=drpBU.Selected.Result),GeoMarket.Value),Result,Ascending)}
  ),"geoMarketOptions")

I now don't know how to populate the Country drop down with a list of the 'Supported Countries' for the selected GeoMarket.

And also the 'Service Base' drop down with list of the 'Service Base's for the selected Country.

Please help.

Pete

[petewalburn]

OK,

getting nowhere with this.

In the SharePoint list are columns:

Business Unit - choice field

GeoMarket     - choice field

Country          - text field

Service Base   - text field

Supported Countries - choice field (with multiple selections)

Is it possible to populate a drop down control with all of the Country values (Country column) and all of the entries in the 'Supported Countries' column.

I can get all of the Country values using:

Items: Sort(Distinct(Filter('Surface Service Facilities', GeoMarket.Value=drpGeoMarket.Selected.Result && "Service Center" in 'Facility Type'.Value),Country),Ascending)

But don't know how to add the choices from 'Supported Countries'

Maybe I need to create a collection with all of the Country entries and the 'Supported Countries' and then use this collection for the Items property of the dropdown.

Pete

[WarrenBelz]

Hi @petewalburn ,

Two possibilities here (I will let you do the blank choice bit - note if you used Combo Boxes instead of drop-downs, you would not need it). First if GeoMarket only had one record (for each item), it would be simple

LookUp(
   'Surface Service Facilities',
   GeoMarket.Value = ddGM.Selected.Value
).'Support Countries'

but if you have multiple records containing the same GeoMarket selected, you have a many-to-many relationship there, so need this

Ungroup(
   ForAll(
      Filter(
         'Surface Service Facilities',
         GeoMarket.Value = ddGM.Selected.Value
      ),
      'Support Countries'
   ),
   "Value"
)

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.

Visit my blog Practical Power Apps

[petewalburn]

Thanks WarrenBelz

I used a combobox instead and use this for the Items:

Sort(Distinct(
  Ungroup(
    ForAll(
      Filter(
        'Surface Service Facilities',
        GeoMarket.Value=drpGeoMarket2.Selected.Result
      ),
      'Supported Countries'),
    "Value"),Value),Result,Ascending)

This does get all of the values, but the blank value is at the bottom of the list. Is there a way to get the Blank at the top?

Pete

[WarrenBelz · Accepted solution]

@petewalburn ,

If you have a combo box, why the blank value - you can do this natively in a Combo Box by simply deleting the item and saving. Having said that, sorting the whole list should put a blank value at the top unless there is some other code not here.

Sort(
   Distinct(
      Ungroup(
         ForAll(
            Filter(
               'Surface Service Facilities',
                GeoMarket.Value = drpGeoMarket2.Selected.Result
            ),
            'Supported Countries'
         ),
         "Value"
      ),
      Value
   ),
   Result
)

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.

MVP (Business Applications)   Visit my blog Practical Power Apps

[petewalburn]

WarrenBelz

That's exactly what I have. However, a blank entry is put at the bottom of the list.  I have found that there is an empty entry for one of the GeoMarkets.  Should be able to sort it now.

Thanks.

Pete