Answered

Patching Lookup column in SP

(0) Share

Report

Posted on by Ras21

It seems like no matter what I try, I get the same errors.

I have a table with general info, and a department lookup column. I can't figure out how to patch this value. I either get errors about it expecting a different schema, or not having an Id of type number. What's the correct way of patching a Lookup column value from the OnSelect on a button? I've looked up many solutions and none work for me. I display the info in a gallery and I have a button that should patch the changes.

This is an example of what a lot of other posts say to do:

Patch(Training,
 Defaults(Training),
 {Lookup:{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
 Id:Gallery1.Selected.ID,
 Value:Gallery1.Selected.Title}
 }
 )

This gives me the schema error, missing id of number type error, and invalid arguments for Patch error.

Categories:

Building Power Apps

I have the same question (0)

All responses (14)

Answers (1)

Sort by

CU-18081211-6 9,270 Moderator on at

Like (0)

Report
Copy link

Link copied!

Hi @Ras21 ,

There are many ways to patch a choice/lookup column in sharepoint. The one that I preffer, although is a little bit resources consumer, is:
Patch (your_list, record_to_patch, {choice_column_name: LookUp(Choices([@'your_list'].choice_column_name, Value=Gallery.Selected.Value)})

presuming that the values presented in gallery are all valid values of lookup column.

Hope it helps !
Gabi

Was this reply helpful? Yes No
Ras21 37 on at

Like (0)

Report
Copy link

Link copied!

Hi @gabibalaban I tried your solution and I'm still encountering errors. My Gallery doesn't even have a Selected.Value property. Also not sure if I should use Defaults(Personal), or ThisItem, or something else. I'm also updating a title, which works fine to update on its own, but I just want to get the Lookup to work first.

Was this reply helpful? Yes No
RandyHayes 76,299 Super User 2024 Season 1 on at

Like (1)

Report
Copy link

Link copied!

@Ras21

The syntax of your formula is correct. There is no noticeable change needed to it.

What do the settings on your list look like? You first appear to only be working with one column, are there any required columns defined in your Training list?

What is the definition of the Lookup column in your list? What column from the foreign (looked up) list is defined as the primary column?

All of those factors play into setting a Lookup type column in SharePoint. Take a look over them and see if you notice anything from the settings. If you get stuck still, try posting back a screen shot of the Lookup column definition in SharePoint.

I hope this is helpful for you.

Was this reply helpful? Yes No
Ras21 37 on at

Like (0)

Report
Copy link

Link copied!

@RandyHayes The Training example isn't actually mine, it was just an example.

Here's my Lookup column settings:

My main list is 'Personal', the only required columns are First name, last name, and title, all of which are already set (but which I will also be able to edit here). I'm able to update the title on its own, but the lookup throws me errors.

Was this reply helpful? Yes No
RandyHayes 76,299 Super User 2024 Season 1 on at

Like (1)

Report
Copy link

Link copied!
@Ras21

Couple things here.

1) You mention:

My main list is 'Personal', the only required columns are First name, last name, and title, all of which are already set (but which I will also be able to edit here). I'm able to update the title on its own, but the lookup throws me errors.

However, in your formula you are creating a new record (you're using Defaults - that's going to create a new record). Therefore, you're going to need to supply the FirstName, LastName and Title to your formula or else you will get an error.

2) Change your formula to the following:

Patch(Training, Defaults(Training), { Lookup:{ '@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference", Id: Gallery1.Selected.ID, Value: Gallery1.Selected.ID }, 'First Name': "Some value", 'Last Name': "Some value", Title: "Some Title" } )

Was this reply helpful? Yes No
Ras21 37 on at

Like (0)

Report
Copy link

Link copied!

@RandyHayes
Creating a new record isn't what I want to do (even so, with the code you supplied it still throws the same errors), I only want to update existing information from this page. I'll try to clarify with some screenshots of how it looks right now.

My gallery is connected to my data source, the Personal SP list. From here I display the current Name, Department, and Title of the person. the 'Avdelning' is the department, which is the Lookup item. I simply want to update these existing assigned departments, not create a new record. (The warning is a delegation warning about the search function, nothing to worry about for now)

The code you supplied me with is similar or the same as in other solutions I've read, including one where it was only used to update a record, but it still doesn't work on my app.

Was this reply helpful? Yes No
RandyHayes 76,299 Super User 2024 Season 1 on at

Like (1)

Report
Copy link

Link copied!
@Ras21

Yes, so in your original formula you were using the Defaults function - this will ALWAYS create a new record.

If you want to Update a record, I would suggest using the UpdateIf (or Patch with a reference to the record you want to update).

Personally, I prefer UpdateIf and so the formula would be the following:

UpdateIf(Training, ID=ThisItem.ID, { Lookup:{ '@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference", Id: Gallery1.Selected.ID, Value: Gallery1.Selected.ID } } )

HOWEVER, I believe you are pointing your Lookup column to the wrong place. You are setting it to the ID of the Gallery selected item. This will be an ID to the Personal SP list - NOT to the Avdelning list, which is I believe what you want. Since your Department dropdown is apparently based on the Avdelning list, you really want THAT information.

Now, not knowing what your Items property is for the dropdownAvdelning, I will assume it has a full avdelning record, in which case the formula would be the following:

UpdateIf(Training, ID=ThisItem.ID, { Lookup:{ '@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference", Id: dropdownAvdelning.Selected.ID, Value: dropdownAvdelning.Selected.ID } } )

See if that gets you further.

Was this reply helpful? Yes No
Ras21 37 on at

Like (0)

Report
Copy link

Link copied!

@RandyHayes

Correct, my dropdownAvdelning Items are just all the records from Avdelning.
I updated my button with your code, but I'm facing the same errors.

Invalid argument type. Expecting a record value, but of a different schema.
Missing column. Your formula is missing a column 'Id' with a type of 'Number'.
The function 'UpdateIf' has some invalid arguments.

Was this reply helpful? Yes No
Verified answer

RandyHayes 76,299 Super User 2024 Season 1 on at

Like (2)

Report
Copy link

Link copied!
@Ras21

First, let's get your formula correct.

You were referencing the wrong ID's in your original.

UpdateIf(Personal, ID=ThisItem.ID, { Avdelning:{ '@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference", Id: dropdownAvdelning.Selected.ID, Value: dropdownAvdelning.Selected.ID } } )

If you are still seeing the errors, then let's go with a troubleshooting step...

Set your formula then to the following:

UpdateIf(Personal, ID=ThisItem.ID, { Avdelning:{ '@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference", Id: 1, Value: 1 } } )

If that doesn't remove the errors, then we have other issues to look at. If it does remove them, then we need to look at the Items property of your DropDown.

Was this reply helpful? Yes No
Ras21 37 on at

Like (0)

Report
Copy link

Link copied!

@RandyHayes
I changed the formula to both of the ones in your latest reply with the correct ID's, and they both give me the same errors. The reasoned i typed Id instead of ID was because it didn't find any property with the name ID, only Id

Was this reply helpful? Yes No