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
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,'CMBSmiley FrustratedelectCustomer'.Selected.Title in Customer.Value && 'CMBSmiley FrustratedelectSuccessEngagement'.Selected.Title in Success_Engagement.Value && Function_Code.Text in Function_ID.Value,"Update") = "Update",Patch(CustomerFunctionAdoption,First(Filter(CustomerFunctionAdoption, Customer.Value='CMBSmiley FrustratedelectCustomer'.Selected.Title && Success_Engagement.Value='CMBSmiley FrustratedelectSuccessEngagement'.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:'CMBSmiley FrustratedelectCustomer'.Selected.ID,Value:'CMBSmiley FrustratedelectCustomer'.Selected.Title}},
{Success_Engagement:{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",Id:'CMBSmiley FrustratedelectSuccessEngagement'.Selected.ID,Value:'CMBSmiley FrustratedelectSuccessEngagement'.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}}))

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
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? 

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,'CMBSmiley FrustratedelectCustomer'.Selected.Title in Customer.Value && 'CMBSmiley FrustratedelectSuccessEngagement'.Selected.Title in Success_Engagement.Value && Function_Code.Text in Function_ID.Value,"Update") = "Update",Patch(CustomerFunctionAdoption,First(Filter(CustomerFunctionAdoption, Customer.Value='CMBSmiley FrustratedelectCustomer'.Selected.Title && Success_Engagement.Value='CMBSmiley FrustratedelectSuccessEngagement'.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:'CMBSmiley FrustratedelectCustomer'.Selected.ID,Value:'CMBSmiley FrustratedelectCustomer'.Selected.Title}},
{Success_Engagement:{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",Id:'CMBSmiley FrustratedelectSuccessEngagement'.Selected.ID,Value:'CMBSmiley FrustratedelectSuccessEngagement'.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}}))

Helpful resources

Announcements
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: 105 members 4,483 guests
Please welcome our newest community members: