cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RNobleman
New Member

Populate a dropdown based on a multiple values People field

I'm trying to populate a single-select dropdown box with a list of unique names that come from a multiple values People or Groups field in SharePoint. If there's a blank entry, I want to convert it to the text "Unspecified". I also want the dropdown to have an "All People" entry. 

 

I'm thinking that I need to end up with a table of distinct values for the dropdown, but the source data is in a record format. So how can I convert a record into a table? Can you provide any guidance?

 

Note: I'll use the dropdown box to filter the results of a gallery.

 

Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User
Super User

@RNobleman 

Use the following formula on the Items property to get what you want:

With({_people:
    ForAll(
        DropColumns(
            GroupBy(
                ShowColumns(
                    Ungroup(
                        YourDataSource.MultiplePersonColumnName,
                        "MultiPerson"
                    ),
                    "DisplayName", "Email"
                ),
                "DisplayName", "Email", "_records"
            ),
            "_records"
        ) As _person,
        Patch(_person, {DisplayName: Coalesce(_person.DisplayName, "Unspecified")})
    )
    },

    Ungroup(
        Table({Items: Table({DisplayName:"All People"})},
              {Items: _people}
        ),
        "Items"
    )
)

Replace YourDataSource.MultiplePersonColumnName in the formula to the name of your datasource and the column name.

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

3 REPLIES 3
RandyHayes
Super User
Super User

@RNobleman 

Use the following formula on the Items property to get what you want:

With({_people:
    ForAll(
        DropColumns(
            GroupBy(
                ShowColumns(
                    Ungroup(
                        YourDataSource.MultiplePersonColumnName,
                        "MultiPerson"
                    ),
                    "DisplayName", "Email"
                ),
                "DisplayName", "Email", "_records"
            ),
            "_records"
        ) As _person,
        Patch(_person, {DisplayName: Coalesce(_person.DisplayName, "Unspecified")})
    )
    },

    Ungroup(
        Table({Items: Table({DisplayName:"All People"})},
              {Items: _people}
        ),
        "Items"
    )
)

Replace YourDataSource.MultiplePersonColumnName in the formula to the name of your datasource and the column name.

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
RNobleman
New Member

It worked! Thanks so much for the quick response. 

RandyHayes
Super User
Super User

@RNobleman 

No problem!  Glad it helped!

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (4,274)