cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Patching Lookup column in SP

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Patching Lookup column in SP

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

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.

View solution in original post

13 REPLIES 13
Highlighted
Solution Supplier
Solution Supplier

Re: Patching Lookup column in SP

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

 

 

 

 

Highlighted
Regular Visitor

Re: Patching Lookup column in SP

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

Highlighted
Super User III
Super User III

Re: Patching Lookup column in SP

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

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Highlighted
Regular Visitor

Re: Patching Lookup column in SP

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

Here's my Lookup column settings:
lookup.png

 

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.

Highlighted
Super User III
Super User III

Re: Patching Lookup column in SP

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

 

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Highlighted
Regular Visitor

Re: Patching Lookup column in SP

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

 

people.pnggallery.png

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.

Highlighted
Super User III
Super User III

Re: Patching Lookup column in SP

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

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Highlighted
Regular Visitor

Re: Patching Lookup column in SP

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

errors.png

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.

Highlighted
Super User III
Super User III

Re: Patching Lookup column in SP

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

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Watch Now

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (5,682)