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
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.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Top Solution Authors
Top Kudoed Authors
Users online (3,376)