cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TGrounds
Level 8

Patch SharePoint List with Lookup Values - Cannot update Lookup fields

I have a SharePoint listing with a field called 'PowerAppUser' that is a lookup of 'people'

 

 I've run into two issues.  I'm trying to do a lookup of the currently logged in use to grab a default value for the app

 

Set(SelectedCommunity,LookUp(PowerAppUserPreferences,User().Email = PowerAppsUser.Email,DefaultCommunity))

This expression isn't throwing an error but it also isn't bringing back the value.  I'm not getting an error on the expression.  I've also tried reversing the condition and using the 'in' expression that didn't work either.

 

Along with that, I'm trying to update that record and I'm getting an error on my expression with regards to the PowerAppsUser field.  It is expecting a record rather than text.  I'm not sure how to build that expression

 

{PowerAppsUser:{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",Id:[where would I find the Id].ID,Value:User().FullName}}

Above is what I think I would use but I'm not sure what source I go against to find the 'ID' of a SharePoint user list - certainly it's not a value that is associated with my PowerUser user?  Also, what value should I be passing (Full Name, E-mail, etc.)?

 

Has anyone else tried this?  I searched the community and couldn't find an example.

 

Thanks!

Tom.

1 ACCEPTED SOLUTION

Accepted Solutions
TGrounds
Level 8

Re: Patch SharePoint List with Lookup Values - Cannot update Lookup fields

Thank you so much for the code!  I have been able to do what I needed to do.  

 

Part 1:  Set the default in the dropdown for the 'Default Community' from the SharePoint listing looking up the user.  After much trial and error - I wasn't able to pull back any results using the .Email - but was able to pull back the record with the following statement:

 

LookUp(PowerAppUserPreferences,User().FullName in PowerAppsUser.DisplayName,DefaultCommunity.Value)

Part 2:  Update the value in the SharePoint Listing when the user changed the Community so that the next time they started the App, it defaults to the last used community (or if they never change the community, it defaults to the appropriate one for that user).  This code is in the OnChange action of the Community dropdown.  It first looks to see if a record already exists for the current user and if there is one, updates the 'DefaultCommunity' to the one they changed to.  If the system doesn't find the user, then it creates the record in the SharePoint list and sets the Default community value.  Here is the code:

 

If(LookUp(PowerAppUserPreferences,User().FullName in PowerAppsUser.DisplayName,"Update") = "Update",

Patch(PowerAppUserPreferences,First(Filter(PowerAppUserPreferences, PowerAppsUser.DisplayName =User().FullName)),
{DefaultCommunity:{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",Id:'CmbSmiley FrustratedelectedCommunity'.Selected.ID,Value:'CmbSmiley FrustratedelectedCommunity'.Selected.Value}}),

Patch(PowerAppUserPreferences,Defaults(PowerAppUserPreferences),{PowerAppsUser:{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",   Claims:Concatenate("i:0#.f|membership|",User().Email), DisplayName:User().FullName, Email:User().Email, Department:"", JobTitle:"", Picture:"" }},
{DefaultCommunity:{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",Id:'CmbSmiley FrustratedelectedCommunity'.Selected.ID,Value:'CmbSmiley FrustratedelectedCommunity'.Selected.Value}}))

Thank you so much for the syntax!

 

Tom.

3 REPLIES 3
Super User
Super User

Re: Patch SharePoint List with Lookup Values - Cannot update Lookup fields

The User().Email function actually returns the UPN. For most tenants the email address is the UPN. However, there are tenants that use a different value for UPN then their email address. Just something you might want to check because it is being used in your lookup. What is the error you are getting when setting the SelectedCommunity variable?

 

Setting the value for a person record looks something like this:

PowerAppsUser:
{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
   Claims:Concatenate("i:0#.f|membership|",User().Email),
   DisplayName:User().FullName,
   Email:User().Email,
   Department:"",
   JobTitle:"",
   Picture:""
 }



--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.
TGrounds
Level 8

Re: Patch SharePoint List with Lookup Values - Cannot update Lookup fields

Thank you so much for the code!  I have been able to do what I needed to do.  

 

Part 1:  Set the default in the dropdown for the 'Default Community' from the SharePoint listing looking up the user.  After much trial and error - I wasn't able to pull back any results using the .Email - but was able to pull back the record with the following statement:

 

LookUp(PowerAppUserPreferences,User().FullName in PowerAppsUser.DisplayName,DefaultCommunity.Value)

Part 2:  Update the value in the SharePoint Listing when the user changed the Community so that the next time they started the App, it defaults to the last used community (or if they never change the community, it defaults to the appropriate one for that user).  This code is in the OnChange action of the Community dropdown.  It first looks to see if a record already exists for the current user and if there is one, updates the 'DefaultCommunity' to the one they changed to.  If the system doesn't find the user, then it creates the record in the SharePoint list and sets the Default community value.  Here is the code:

 

If(LookUp(PowerAppUserPreferences,User().FullName in PowerAppsUser.DisplayName,"Update") = "Update",

Patch(PowerAppUserPreferences,First(Filter(PowerAppUserPreferences, PowerAppsUser.DisplayName =User().FullName)),
{DefaultCommunity:{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",Id:'CmbSmiley FrustratedelectedCommunity'.Selected.ID,Value:'CmbSmiley FrustratedelectedCommunity'.Selected.Value}}),

Patch(PowerAppUserPreferences,Defaults(PowerAppUserPreferences),{PowerAppsUser:{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",   Claims:Concatenate("i:0#.f|membership|",User().Email), DisplayName:User().FullName, Email:User().Email, Department:"", JobTitle:"", Picture:"" }},
{DefaultCommunity:{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",Id:'CmbSmiley FrustratedelectedCommunity'.Selected.ID,Value:'CmbSmiley FrustratedelectedCommunity'.Selected.Value}}))

Thank you so much for the syntax!

 

Tom.

simkessy
Level: Powered On

Re: Patch SharePoint List with Lookup Values - Cannot update Lookup fields

What if you have multiple users and you need to patch more than 1 user to 1 record?

Helpful resources

Announcements
firstImage

PowerApps Monthly Community Call!

Join us next Wednesday for our Demo Extravaganza, October 16, 2019 8am PDT.

firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Top Kudoed Authors
Users Online
Currently online: 222 members 4,642 guests
Please welcome our newest community members: