web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id : /7k/WGnhSZSiooCoZYfCTZ
Power Apps - Building Power Apps
Answered

Patch SharePoint List with Lookup Values - Cannot update Lookup fields

Like (0) ShareShare
ReportReport
Posted on 4 Apr 2018 18:17:28 by Microsoft Employee

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!

Categories:
I have the same question (0)
  • Verified answer
    T.Grounds Profile Picture
    Microsoft Employee on 04 Apr 2018 at 21:37:22
    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}}))

  • T.Grounds Profile Picture
    Microsoft Employee on 04 Apr 2018 at 21:29:45
    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? 

  • Shanescows Profile Picture
    2,218 Most Valuable Professional on 04 Apr 2018 at 19:50:51
    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/24094 

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Telen Wang – Community Spotlight

We are honored to recognize Telen Wang as our August 2025 Community…

Congratulations to the July Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
WarrenBelz Profile Picture

WarrenBelz 637 Most Valuable Professional

#2
stampcoin Profile Picture

stampcoin 570 Super User 2025 Season 2

#3
Power Apps 1919 Profile Picture

Power Apps 1919 473

Loading started
Loading complete