Set/update a person data field (Office 365) upon selection of a drop-down choice data field in a PowerApps form linked to a SharePoint list

[LukeC]

Hi PowerApps Community,

I'm looking for some advice on the best approach to creating the following app functionality of a PowerApps form linked to a SharePoint list, and am hoping some folks might be able to share insight! Thanks in advance.

SharePointList1 contains two data fields:

1. "Person" which is a person data field type (Office 365 user)

2. "Category" which is a choice data field type

I'd like to create functionality where when a user makes a selection from the "Category" data field (during either a New entry or upon Editting an existing record via its PowerApps form), the "Person" person data field is automatically populated. 

A second SharePointList2 defines which Office 365 User is assigned to which Category available for selection in SharePointList1, containing the following 2 data fields:

1. "Person2" which is a person data field type (Office 365 user)

2. "Category2" which is a choice data field type with all values matching Category

example of SharePointList2:

Bob, Accounting

Bob, Finance

Bob, Admin

Debbie, Engineering

Debbie, Legal

Charles, Maintenance

Therefore, if a user selects "Legal" in the Category data field,  "Debbie" automatically populates in the Person data field in the PowerApps form during new entry and edit of SharePointList1.

The following solution gets me close, but it appears to only return results for text-based fields. Although not absolutely necessary, it would be ideal for a manager-level user to be able to update the values Person2 and Category2 in SharePointList2 from time-to-time and not have to re-write a hard-coded solution like suggested in the following (whereby there is no use of a second SharePoint list to create the relationship between Person and Category):

https://powerusers.microsoft.com/t5/General-Discussion/Assigning-ticket-to-user-based-on-help-type-s...

Thanks for reviewing this issue and providing any feedback!

[RandyHayes]

Hi @LukeC 

Yes, this is all very possible to do.

First question - is the Category column the same in List 1 and List 2?  Even better - is it a site column rather than a list column?  I ask this only to make sure that you will be comparing apples to apples.

Next question - for your scenario - will users be able to actually enter a person in the person column, or will they need to select a Category in order to have a name appear only.

Final question - will there ever be a category with two or more people associated?  Or will there always be one and one? (ex. Bob, Accounting and also Debbie, Accounting)

Let's start there and then I can supply a better solution for you based on those factors.

[LukeC]

Hi @RandyHayes 

Thanks for getting back to me and for offering your help. Here are those answers:

1.) Yes, the Category column is the same in List 1 and List 2. They both have matching values/order and are both List Columns.

2.) Users would have to select a Category to force selection of the Person. The Person field could (probably should) be non-edittable from the form, but the key here is that the actual Office 365 User is swapped out on the record each time the Category drop-down is changed.

3.) No, only one person will ever be associated (Bob and Debbie cannot both be associated with Accounting). But Bob is associated with Accounting, Finance, and Admin in my example, so a person can be linked to multiple Categories.

Thanks so much for your assistance, and I look forward to your feedback!

[LukeC]

@RandyHayes to clarify on the answer to question 3, specifically "a person can be associated with multiple categories."

Only one category can be selected on a record at a time, i.e. the user wouldn't be able to select multiple options on the Category drop-down. Merely, if the user selects "Accounting", "Finance", or "Admin", Bob is updated in the people column associated with Office 365 user.

[RandyHayes · Accepted solution]

@LukeC 

Very good...so then it should be fairly straight forward with that scenario.

What you will want to do is focus on the DataCard for the Person column in your Form.

You'll want it to be display only so the user will not edit.

Then change the Update property of the DataCard to something similiar to this:

  Lookup(SharePointList2, Category2.Value = yourDataCardValueForCategoryControlName.Selected.Value).Person2

This way when you Submit Form, it will update the underlying field based on a lookup based on the DropDown value.

See if that help you get to the next step.

[LukeC]

Hey @RandyHayes ,

Thanks so much for all your help. That worked!

Here is the only modification I had to make to get it to work:

ORIGINAL

Lookup(SharePointList2, Category2.Value = yourDataCardValueForCategoryControlName.Selected.Value).Person2

MODIFICATION

Lookup(SharePointList2, Category2.Value = yourDataCardValueForCategoryControlName.Selected.Value).Person2

Perhaps this may be related to an update w/ PowerApps because I've seen that syntax elsewhere in other posts, yet PowerApps won't return a "Value" for me let alone any other variable at that point in the sequence before moving onto the "=" in the equation. So I just dropped ".Value" from the equation altogether and it works like a charm!

Thanks @RandyHayes . Appreciate your time on this problem and your helpful advice. This solution worked great!

[Scott0044]

This is fantastic!  Thanks for this info!  I have a curveball requirement that I'm wondering if you can add to it.  Same exact setup as described on this thread except there is an additional column called Priority.  Example would be:

Bob, Accounting, Priority 1

Bob, Accounting, Priority 2

Stacey, Accounting, Priority 3

Frank, Finance, Priority 1

Jane, Finance, Priority 2

Helen, Finance, Priority 3

and so on....

Any thoughts on how to leverage the code provided in the solution on this thread?

TIA