cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Spinner
Advocate II
Advocate II

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

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.

firefox_VIRkShdth6.png

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.

1 ACCEPTED SOLUTION

Accepted Solutions
v-xiaochen-msft
Community Support
Community Support

Hi @Spinner ,

 

I did a test for you.

vxiaochenmsft_4-1633572733892.png

 

vxiaochenmsft_0-1633571661774.png

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

 

 

vxiaochenmsft_1-1633572559496.png

vxiaochenmsft_2-1633572576786.png

vxiaochenmsft_3-1633572651386.png

 

Best Regards,

Wearsky

View solution in original post

4 REPLIES 4
v-xiaochen-msft
Community Support
Community Support

Hi @Spinner ,

 

I did a test for you.

vxiaochenmsft_4-1633572733892.png

 

vxiaochenmsft_0-1633571661774.png

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

 

 

vxiaochenmsft_1-1633572559496.png

vxiaochenmsft_2-1633572576786.png

vxiaochenmsft_3-1633572651386.png

 

Best Regards,

Wearsky

Spinner
Advocate II
Advocate II

@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
Community Support
Community Support

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

@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!

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Users online (1,020)