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'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
TGrounds
Level 8

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
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
TGrounds
Level 8

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
TGrounds
Level 8

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
thirdimage

Power Automate Community User Group Member Badge

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

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (5,002)