Patch SharePoint List with Lookup Values - Cannot update Lookup fields

[TGrounds]

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!

[Shanescows]

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

[TGrounds]

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 · Accepted solution]

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}}))