cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver II
Resolver II

Patch SharePoint List with Lookup Values - Cannot update Lookup fields

I'm trying to update a record or add a new record in the table 'CustomerFunctionAdoption' based on the value selected from a dropdown.  When that dropdown changes, the OnChange will either find the record and update a single field, or create a new record populating the entire record.  I've run into an issue because all of the fields in the 'CustomerFunctionAdoption' table are SharePoint lookup values from other existing tables.  I didn't think my function was working until I added a new field that was strictly text and it finds the record in the table and updates the text field.  How do I have sharepoint do the lookup for me based on the text value I'm passing?

 

Context:

Tables:

  • Function 
    • Function_ID (Text)
    • Function_Description (Text)
  • Adoption:
    • Adoption_Level (Text)
  • Customers
    • Customer (Text)
  • CustomerFunctionAdoption
    • Customer (Lookup from Customers)
    • Function_Description (Lookup from Function)
    • Adoption (Lookup from Adoption)
    • Adoption_Text (Text field)

 

Screen

  • Customer Dropdown: Items - built using SortByColumns(Customers,"Customer")
  • Gallery - Loaded from the Function table
    • Function_ID - saved into a label in the gallery
    • Function_Description - saved into a label in the gallery
    • Adoption Drop-Down list
      • Items - built using Distinct(Adoption,Adoption_Level)
      • Default set with a Lookup(CustomerFunctionAdoption,CustomerDropdown.Selected.Title in Customer.Value && Function_ID.Text in Function_ID.Value,Adoption_Level.Value) <- there is other IF logic around this
      • OnChange - This is where I'm having issues - What I'm trying to accomplish is when the dropdown is changed, then I either look up the existing record and upate the value in the table with the new value from the dropdown, or I create new record and populate the Customer, Function and Adoption value.  The command works if I'm updating a standard 'Text' field in the SharePoint listing - but when I try to set the value to the Lookup field I get an error:

The type of this argument 'Adoption' does not match the expected type 'Record'. Found type 'Error'

 

When I change it to 'Adoption_Text' it finds the record in the table and updates that field.

 

I'm guessing from the message that because the 'Adoption' field is a lookup, it's looking for the internal ID to place in the field rather than the value?  How do I have SharePoint do the lookup for me based on the text I'm trying to pass?

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resolver II
Resolver II

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

I've obviously been looking at this way too long today.  I have it working - here is the final expression:

 

If(LookUp(CustomerFunctionAdoption,'CMB:SelectCustomer'.Selected.Title in Customer.Value && 'CMB:SelectSuccessEngagement'.Selected.Title in Success_Engagement.Value && Function_Code.Text in Function_ID.Value,"Update") = "Update",Patch(CustomerFunctionAdoption,First(Filter(CustomerFunctionAdoption, Customer.Value='CMB:SelectCustomer'.Selected.Title && Success_Engagement.Value='CMB:SelectSuccessEngagement'.Selected.Title && Function_ID.Value=Function_Code.Text)),{Adoption_Level:{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",Id:Customer_Usage.Selected.ID,Value:Customer_Usage.Selected.Value}}),Patch(CustomerFunctionAdoption,Defaults(CustomerFunctionAdoption),{Customer:{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",Id:'CMB:SelectCustomer'.Selected.ID,Value:'CMB:SelectCustomer'.Selected.Title}},
{Success_Engagement:{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",Id:'CMB:SelectSuccessEngagement'.Selected.ID,Value:'CMB:SelectSuccessEngagement'.Selected.Title}},
{Function_ID:{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",Id:LookUp(D365Functions,Function_Code.Text in Title,ID),Value:Function_Code.Text}},{Adoption_Level:{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",Id:Customer_Usage.Selected.ID,Value:Customer_Usage.Selected.Value}}))

View solution in original post

3 REPLIES 3
Highlighted
Super User
Super User

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

Check this out. LookUp columns and PowerApps aren't friends for sure. But it is possible.

 

https://powerusers.microsoft.com/t5/General-Discussion/How-to-patch-a-SharePoint-Lookup-Column/td-p/...

Shane - Microsoft MVP, YouTube, and PowerApps Consulting for when you are in a bind to get this fixed quickly. And finally we now have PowerApps Training
Highlighted
Resolver II
Resolver II

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

I was able to figure out the Patch for an update - I was missing the @odata.type syntax.  

 

Now I'm running into issues with the Patch 'Add' - where I set the Default values.  I have checked every entry and have the lookup information needed.  As far as the 'Standard' fields (create date/time, create user, ID, etc.) are these all populated using the Defaults() function or do I have to provide the information? 

Highlighted
Resolver II
Resolver II

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

I've obviously been looking at this way too long today.  I have it working - here is the final expression:

 

If(LookUp(CustomerFunctionAdoption,'CMB:SelectCustomer'.Selected.Title in Customer.Value && 'CMB:SelectSuccessEngagement'.Selected.Title in Success_Engagement.Value && Function_Code.Text in Function_ID.Value,"Update") = "Update",Patch(CustomerFunctionAdoption,First(Filter(CustomerFunctionAdoption, Customer.Value='CMB:SelectCustomer'.Selected.Title && Success_Engagement.Value='CMB:SelectSuccessEngagement'.Selected.Title && Function_ID.Value=Function_Code.Text)),{Adoption_Level:{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",Id:Customer_Usage.Selected.ID,Value:Customer_Usage.Selected.Value}}),Patch(CustomerFunctionAdoption,Defaults(CustomerFunctionAdoption),{Customer:{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",Id:'CMB:SelectCustomer'.Selected.ID,Value:'CMB:SelectCustomer'.Selected.Title}},
{Success_Engagement:{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",Id:'CMB:SelectSuccessEngagement'.Selected.ID,Value:'CMB:SelectSuccessEngagement'.Selected.Title}},
{Function_ID:{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",Id:LookUp(D365Functions,Function_Code.Text in Title,ID),Value:Function_Code.Text}},{Adoption_Level:{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",Id:Customer_Usage.Selected.ID,Value:Customer_Usage.Selected.Value}}))

View solution in original post

Helpful resources

Announcements
secondImage

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Robotic Process Automation

Let's talk about the solution provided by Microsoft for Robotic Process Automation (RPA)

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (8,517)