Dependant dropdowns - multiple selections

[AuburnMist]

Hello,

I have a SharePoint list which lists all of the Provinces and Cities in Atlantic Canada where we have offices. 

In my app, I have added a combo box for the Provinces and a combo box for the cities. The cities list is dependant on the province list, only displaying the cities within the selected province. 

This works perfectly for my form question: "What is your current work location?" 

I have to add a second question which is "Which locations would you be interested or willing to work?" 

This requires the user to be able to make more then one selection in Provinces (ex: New Brunswick and Nova Scotia). 

Making multiple selections isn't a problem in the Provinces combo box, but the results in the Cities combo box only displays the cities for one of the provinces (the first one selected). 

Is it possible to have the dependant dropdown (Cities combo box), display cities for more then one province? 

This is my code for the single selection question. Can it be modified slightly?

Ungroup(Table({Value: Blank()},{Value:Distinct(Filter(Work_Locations, Title = cmbProvince.Selected.Result),City)}),"Value")

Thank you very much! 

[RandyHayes]

@AuburnMist 

Yes, your current formula is only using Selected - which will be one selection.  You want to incorporate the SelectedItems table into your formula.  This is available only with Comboboxes (which I believe you have, but you mentioned Dropdowns).

I would make your formulas only deal with datasources once as the values you need are already in your comboboxes.

Example (and I don't know your exact data, so some of this is a guess):

Province Control Items property:

GroupBy(
    Work_Locations,
    "Province", "Cities"
)

 Then in your Cities Combobox, the Items property:

Ungroup(cmbProvince.SelectedItems, "Cities")

This should give you what you are looking for.

I hope this is helpful for you.

EDIT: Sorry had a typo in the cities combobox formula...it is corrected above.

[EdViegas]

Try something like this in the items property of the cities combobox. For multiple selection you need to use SelectedItems property instead of Selected. Assuming column Title for province: 

Ungroup(
    ForAll(
        cmbProvince.SelectedItems As ThisProvince,
        {
            City: Filter(
                Your_SPOList,
                Title = ThisProvince.Title
            )
        }
    ),
    "City"
)

[AuburnMist]

Hi Ed, 

I think I'm close now, (thank you!), but getting the red squiggly lines under "=" and ".Title". 

You are correct, that the province column is the Title column, so not sure why it isn't working. The error message for the "=" is "Incompatible types for comparison. These types can't be compared: Text, Error. 

And the error message for the ".Title" is "Name isn't valid. This identifier isn't recognised."

Any ideas how to fix? 

[RandyHayes]

@AuburnMist 

Keep in mind that if you use that method, you will be hitting your datasource more than you need to.  So if performance is not an issue, then use that.  Otherwise, use the values from your first control that you already have hit the datasource for once.

[Anonymous]

Hi @AuburnMist,

@EdViegas thanks for the better suggestion.

Try this below formula hope it will work,

Ungroup(
    ForAll(
        cmbProvince.SelectedItems,
        {
            City: Filter(
                Your_SPOList,
                Title in cmbProvince.SelectedItems
            )
        }
    ),
    "City"
)

[AuburnMist]

Thank you Vishu, this one worked!

Everything is perfect, except, the results in the Cities combo box actually displays all of the cities for New Brunswick and Nova Scotia (my test provinces),  twice! So I see all of the NB cities, followed by the NS cities, then again the NB cities followed by the NS cities. 

Anyway to correct this? Perhaps the Distinct function in there somewhere?

[RandyHayes]

@AuburnMist 

I am not understanding why all of your suggestions you are following are going to the datasource that many times.  You already have your data in your first combobox!  Why bog down your app with filtering data over and over?

[AuburnMist]

Lol, I hear ya Randy, but unfortunately, I was unable to resolve my concern with the suggested coding you provided. 

Both of the codes rejected (threw errors). The other ones seem to be getting me closer. 

Perhaps I am missing something. When I use this code in the Items property of the Province combo box: 

Group(
    Work_Locations,
    "Province", "Cities"
)

I get a red squiggly under the whole code and the error message reads: "Invocation of unknown or unsupported function".

And when I use this one in the Items property of the Cities combo box;

Ungroup(cmbProvince.SelectedItems, "Cities")

 I get "The function 'Ungroup' has some invalid functions. 

[Anonymous]

Hi @AuburnMist,

try this,

Ungroup(
    ForAll(
        cmbProvince.SelectedItems,
        {
            City: Distinct(Filter(
                Your_SPOList,
                Title in cmbProvince.SelectedItems
            ),City)
        }
    ),
    "City"
)