Can't restrict values in Combo Box for SharePoint Person column

[Spinner]

I have a Person column called "Assigned To" in a SharePoint list. It allows multiple values.

In my PowerApp I want to show the possible users that the item could be assigned to. However, instead of showing individual users it shows various groups. No individuals are initially present in the combo box list.

However, once I start typing in the name of a user, it appears. I believe this glitch

I have tried two approaches.

Approach 1 - filter the combo box items to show only users, no groups

I applied a filter using something like:

Filter(Choices([@SPTable].'Assigned To'),StartsWith(Claims, "i:0#.f|membership|"))

This did not work as it appears no individual users are loaded until the user starts to enter text to search.

Approach 2 - fill the combo box with a list from the O365 Groups connector

Office365Groups.ListGroupMembers(First(Filter(Office365Groups.ListOwnedGroupsV3().value,mail="***O365 Group email***")).id).value

This shows the right items in the combo box - but I cannot then show the current value of the column. Setting DefaultSelectedItems to ThisItem.'Assigned To' does not do anything.

I tried adding a "Claims" column to the table that the O365 Groups connector returned, but this did not make any difference.

Can anyone please suggest a way to get this working? Thanks.

[v-xiaochen-msft · Accepted solution]

Hi @Spinner ,

I did a test for you.

Insert a combo box control and set its Items property to:

AddColumns(RenameColumns(ShowColumns(Office365Users.SearchUserV2({top:500}).value,"DisplayName","Mail","Department","JobTitle"),"Mail","Email"),"Claims","i:0#.f|membership|"&ThisRecord.Email,"Picture","")

Set its DefaultSelectedItems property to:

ThisItem.Test_Person

Best Regards,

Wearsky

[Spinner]

@v-xiaochen-msftThanks Wearsky. That's a big step forward. Since that code shows all users, and I needed only the subset in a particular group, I combined it with my code to produce this:

AddColumns(
    RenameColumns(
        ShowColumns(
            Office365Groups.ListGroupMembers(
                First(
                    Filter(
                        Office365Groups.ListOwnedGroupsV3().value,
                        mail = "***O365GroupEmail***"
                    )
                ).id
            ).value,
            "displayName",
            "mail",
            "jobTitle"
        ),
        "mail",
        "Email",
        "displayName",
        "DisplayName",
        "jobTitle",
        "JobTitle"
    ),
    "Claims",
    "i:0#.f|membership|" & ThisRecord.Email,
    "Picture",
    "",
    "Department",
    ""
)

This appears to work.

For my benefit, where was I going wrong? Was it my failing to add extra columns (e.g. Department) - are some columns other than Claims, DisplayName and Email required? I note that I also should have been using ThisRecord, which makes sense.

[v-xiaochen-msft]

Hi @Spinner ,

The formula looks great.

According to my test, the Claims and Email columns seem to be required. (I'm sure the Claims column is required.)

Some other columns such as department, picture... are not necessary.

Best Regards,

Wearsky

[Spinner]

@v-xiaochen-msft  In view of that, for performance I've stripped it down to the following, which seems to work fine:

AddColumns(
    RenameColumns(
        ShowColumns(
            Office365Groups.ListGroupMembers(
                First(
                    Filter(
                        Office365Groups.ListOwnedGroupsV3().value,
                        mail = "***Office 365 Group email***"
                    )
                ).id
            ).value,
            "displayName",
            "mail"
        ),
        "mail",
        "Email",
        "displayName",
        "DisplayName"
    ),
    "Claims",
    "i:0#.f|membership|" & ThisRecord.Email
)

 Thanks for your help!