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

SecondImage

PowerApps Monthly Community Call

Next Wednesday, August 21st at 8am PDT

Top Community Contributors for July 2019

Top Community Contributors for July 2019

Let's thank our top community contributors

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

FifthImage

Dynamics 365 and Power Platform April 2019 Release notes

Features releasing from April 2019 through September 2019!

SixthImage

Power Summit Australia 2019

August 20-23rd 2019

Users Online
Currently online: 189 members 3,877 guests
Please welcome our newest community members: