Populate a combobox Person field from SP list A with a filtered list of people from a Person column in List B (single select)?

[Rico36]

How can I populate a Person's combobox field linked to a column in SP list "A" exclusively with a filtered list of people from list "B" and still allow that combobox to remain full person schema? (can extract email, picture, etc) ??  In my case, when the user selects a person from list B in the combobox,  it should save the selected as a Person into List A but also should allow to display the photo of the selected person in a separate image control.  Both SP lists A and B have a Person column however, if my Combobox Items property has:

Distinct(Filter([@POCs], Role.Value="Assistant" && IsActive.Value = "Yes"), POC_Person.DisplayName)

then the end result is that the combobox shows those names and allow selection from List B (POCs) but the selected it is no longer a full Person schema (i.e., my image control could not extract the Picture from the combobox selected property).  I only see combobox.Selected.Results but not combobox.Selected.Email, .DisplayName, ,Picture etc as example.

[StalinPonnusamy]

Hi @Rico36 

Set the Items property and DefaultSelectedItems property of Combobox2 to

Distinct(Filter(SPList, 'PersonColumnName'.DisplayName=ComboBox1.Selected.Result).'PersonColumnName','PersonColumnName'.DisplayName)

And Set Onchange property of Combobox 1 to

Reset([@ComboBox2])

[Rico36]

Thanks Stalin.  Interesting solution but what if the selected person is in POCs but not in SPList.PersonColumnName yet?  Also, there is only one combobox.  

[StalinPonnusamy]

Hi @Rico36 

• The list will be empty. We may need to block other operations based on your need.
• Whenever you use Distinct which returns a one-column table that contains the results.

[StalinPonnusamy]

Hi @Rico36 

Since you are expecting a person record (full information), avoid using distinct on the person combobox.

Person combo box can be

Office365Users.SearchUser({searchTerm: ""})

or

Choices(SPList.PersonColumn)

Using these we can use Filter to filter the SPlist person (s)

[Rico36]

Thanks Stalin .  However, if I use the Filter command:

Filter([@POCs].POC_Person)

then it won't accept necessary filter conditions..:  Role.Value="Assistant" && IsActive.Value = "Yes".

If I use the following Filter command instead:

Filter([@POCs], Role.Value="Assistant" && IsActive.Value = "Yes").POC_Person

shows nothing in the combobox because the returned in POC_Person are of type Records. If I add ".DisplayName" at the end it doesn't recognize it as valid.  That is why I was using the Distinct().

Distinct(Filter([@POCs], Role.Value="Assistant" && IsActive.Value = "Yes"), POC_Person.DisplayName)

[StalinPonnusamy]

Let me work on this and get back to you.

[StalinPonnusamy · Accepted solution]

Hi @Rico36 

Sorry for the late response. And took a while to find out the answer. This is not straightforward also.

Set Onvisible property of the screen to 

Clear(colPersons);
ForAll(
    With(
        {_Item: [@POCs]},
        Filter(
            _Item,
            Role.Value = "Assistant" && IsActive
        )
    ),
    Collect(
        colPersons,
        ThisRecord.POC_Person
    )
);

And Set Items Property of Combobox to

colPersons

Set the Layout as Person

Thanks,
Stalin

[Rico36]

Hi Stalin.  Thank you so much for all your time helping me on this.     I will try your solution using a collection and will comment back.  

[Rico36]

Stalin, it worked beautifully !!!  Pretty simple too.  Thanks so much.  😀