cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LukeC
Advocate IV
Advocate IV

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

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!

1 ACCEPTED SOLUTION

Accepted Solutions

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

_____________________________________________________________________________________
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

6 REPLIES 6
RandyHayes
Super User
Super User

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.

_____________________________________________________________________________________
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!

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!

 

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

 

 

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

_____________________________________________________________________________________
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!

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

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

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (3,195)