Filtered people picker - patching result to SharePoint list

[StacyO]

My scenario:

List A (list with stored data from the form)

List B (lookup for Vendor and Manager fields)

I have a form connected to a SharePoint list (List A) with a people picker (Manager) that is filtered from another SharePoint list (List B) based on a selection from another combobox (Vendor).  When user selects a Vendor, the Manager field should display filtered values from List B.  I have managed to get the filtered results in the field, however I can't seem to get the values patched to the SharePoint list (List A).

Here is the formula I am using for Manager combobox where it correctly shows Emails for all the filtered results:

If(

    varViewProduct = false,

    Office365Users.SearchUserV2(

        {

            searchTerm: Trim(Self.SearchText), isSearchTermRequired: false,

            top: 10

        }

    ).value,

        ForAll(Filter(

            'List B',

            Vendor = cmbVendor.Selected.Value

        ).'Manager','Manager'.Email)

    )

When Patching the manager field in the OnSuccess property, Email is not recognized.  I've looked at the card and DisplayFields and SearchFields only allow ["Value"] and wont allow ["DisplayName","Email"].  I suspect it's the way I'm filtering the Manager combobox(?) but can't figure out the right recipe to get this to work.

Note:  user should also be able to enter another Manager that is not filtered, if desired, thus the If statement.

Any tips?  Thanks in advance!

[Amik]

@StacyO - is there a specific reason you need to use the Patch function on on the OnSuccess property, rather than using SubmitForm?

[StacyO]

with the Submit function all my fields in the form updated on the SharePoint except the people picker so I figured I'd patch it. Is there a better way to do this?

Here are some additional details: Update property is set to ThisItem.Manager and DefaultSelectedItems is Parent.Default.  I'm using a modern form but swapping out with a classic combobox control.  Thanks Amik!

[Amik]

@StacyO - assuming the people field in List A ("Manager") is a single-select people picker, see the blog post I wrote a few months ago on a similar topic using the existing SubmitForm function.

https://powerusers.microsoft.com/t5/Power-Apps-Community-Blog/Filter-SharePoint-single-select-People... 

You should be able to customise this to suit your needs but if not, feel free to message back. For example, in the Items property of the "Manager" ComboBox control, use:

With(
    {
        _prefiltered_data: Filter(
            'List B',
            Vendor = cmbVendor.Selected.Value
        )
    },
    Sort(
        ForAll(
            Ungroup(
                _prefiltered_data,
                "Manager" //Note we are using the Logical Name
            ),
            Manager
        ),
        DisplayName,
        SortOrder.Ascending
    )
)

[StacyO]

You are amazing Amik!  This works when I'm using the filtered results and it writes back to the SharePoint list (no Patching needed). However, when I need to enter a person from AAD, I can enter a user but it won't write back to the SharePoint list.  

Here is my modified formula with your inputs: 

If(

    varShowOpPlanProduct = false,

    Office365Users.SearchUserV2(

        {

            searchTerm: Trim(Self.SearchText),

            isSearchTermRequired: false,

            top: 10

        }

    ).value,

    With(

        {

            _prefiltered_data: Filter(

                'List B',

                Vendor = cmbVendor.Selected.Value

            )

        },

        Sort(

            ForAll(

                Ungroup(

                    _prefiltered_data,

                    "field_8"

                ),

                Manager

            ),

            DisplayName,

            SortOrder.Ascending

        )

    )

)

It seems adding the If condition is the culprit?

In the field properties, Email isn't an option??? I can force it in the DisplayField property but it still won't show in the layout fields.

Am I stuck or is there a way to enter a user manually AND have the filter as well?  Thank you, Thank you!

[StacyO]

^^^NOTE:  the Manager field is now ComboBox1

[StacyO]

also, curious as to how I can filter out accounts that are disabled in AAD.  I saw something Reza Dorrani published that addressed this so I'll see if I can work it in with what I have.

[Amik · Accepted solution]

@StacyO - The Office 365 users table and the SharePoint AD table share totally different schemas. You're going to need to use a separate solution for that scenario by dynamically changing the same table based on the output of "varShowOpPlanProduct".

1. In the Items property, use:

If(
    !varShowOpPlanProduct,
    Office365Users.SearchUser({searchTerm: Self.SearchText}),
    With(
        {
            _prefiltered_data: Filter(
                'List B',
                Vendor = cmbVendor.Selected.Value
            )
        },
        ForAll(//filter the Office365 users table to return only users in the List B Manager people field
            ForAll(
                Ungroup(
                    _prefiltered_data,
                    "Manager"//Note we are using the Logical Name
                ),
                Manager
            ),
            First(Office365Users.SearchUser({searchTerm: Email}))
        )
    )
)

2. In the Update property of the DataCard, use:

If(
    Len(cmbVendor.Selected.Mail) > 0,
    {
        DisplayName: cmbVendor.Selected.DisplayName,
        Claims: "i:0#.f|membership|" & Lower(cmbVendor.Selected.Mail),
        Department: "",
        Email: Lower(cmbVendor.Selected.Mail),
        JobTitle: "",
        Picture: ""
    }
)

3.  In the DefaultSelectedItems property of the ComboBox control, use:

If(
    !IsBlank(ThisItem.Manager.Email),
    First(Office365Users.SearchUser({searchTerm: ThisItem.Manager.Email}))
)

Do not make any other modifications to the other DataCard properties.

[StacyO · Accepted solution]

Again, Amik to the rescue!  Your solution worked like a charm and I'm well on my way now. All data in all scenarios is correctly displayed and updated to the SharePoint list.  I couldn't help but notice you used the original SearchUser (not SearchUserV2).  I tried both -- only the original one worked.  I'm still new to PowerApps but all that I've read and researched so far says to always use the V2.  At this point, I'm not questioning it as I've already wracked my brain enough on just this one field so I'm going with it.

I really appreciate your knowledge and taking time to respond!  Thank you, thank you!!!!