Combobox to show all users in a person column with multiple persons

[petewalburn]

I have a SharePoint list that has a Country column, a City column and a Manager column.  The Manager column can have multiple users in it.  

I have a drop down for selecting with country to use and I would like to have a combobox listing al of the Manages for the selected country.  This could end up being multiple records within the SharePoint List and each of those records could have multiple Managers.

I guess that I need to build up a collection of all the Managers within the list for the selected country.

So my SharePoint list is something like:

Country       City                Manager

=======================

USA             Anchorage      Fred

USA             Irvine               Bob, John

USA             New Orleans   Albert, Kennie, Ali

UK               London            Joe, Christine

If I select USA, I want to build a collection of users that should have Fred, Bob, John, Albert, Kennie and Ali in it.

If I select UK, the  collection would have Joe and Christine in it.

I have managed this kind of thing before when the Manager column could not be multiple users, but in this case the collection always has a column with [Table] in each row.

I have manage to have a gallery to show the correct data and thought I could possibly use the gallery as the datasource of the combobox, but don't seem to be able to do this either.

I'm sure I'm missing something really simple, but can't see it.

Could someone help or point me in the right direction please?

Thanks,

Pete

[anandm08]

hi @petewalburn ,

Create a dropdown for selecting the country:

Distinct(YourSharePointList, Country)

On the OnSelect property of the dropdown or a button, create a collection for managers:

ClearCollect(
    colManagers,
    Filter(YourSharePointList, Country = DropdownCountry.Selected.Result)
);

ClearCollect(
    colFlattenedManagers,
    ForAll(
        colManagers,
        ForAll(
            Split(Manager, ","),
            Trim(Result)
        )
    )
);

ClearCollect(
    colUniqueManagers,
    Distinct(colFlattenedManagers, Result)
);

Set the Items property of the combobox to the collection:

colUniqueManagers

[petewalburn]

I managed to do this with help from a post by RogierE in the post: 

Combo Box to show list of persons from Sharepoint list Person or Group Column (Multi choice) and use it to filter a Gallery

So, my Country combobox Items is:

Sort(Distinct(Filter('SharePoint List',GeoMarket.Value=drpGeomarket.Selected.Value),Country),Value)

In the OnSelect I have:

Clear(colApprovers);
ForAll(
    'SharePoint List',
    ForAll(
        Manager,
        Collect(
            colApprovers,
            AddColumns(Table(ThisRecord),GeoMarket,'SharePoint List'[@GeoMarket].Value,Country,'SharePoint List'[@COUNTRY])
        )
    )
);

and the Items of the Approver combobox is:

ForAll(Distinct(Sort(Filter(colApprovers,GeoMarket=drpGeomarket.Selected.Value And Country=drpCountry.Selected.Value),DisplayName),ThisRecord),Value)

The DisplayFields property of the Approver combobox is:

["DisplayName","Email"]

And I have what I need.

I'm sure your solution would work too anandm08, but I'd already started with the solution from RogierE.

Thanks

Pete.

[petewalburn]

Looks like I was too quick to assume things were working.

The combobox does show the expected items. However, the Selected item does not have the Email as a property.

When I try to patch the selected approver, the only properties for the cboApprover.Selected object are: Country, Department, DisplayName and JobTitle.  Email or Mail is not listed.  I suspect that it has something to do with the Items property of the combobox, but can't quite figure it out.

Please help,

Pete

[petewalburn]

Hi,

Just thought  that I should clarify that the Manager column is not a text column with comma-separated names, but is a Person column which allows multiple people to be included. I have just written it like I had to indicate that there could be multiple people in the column.

Pete