cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
laurel_cpi
Level: Powered On

How to patch a SharePoint Lookup Column

I can update all other SharPoint fields using the Patch function, but I am unsucessful in udpating lookup columns using a button.

 

I have a Customers list and a Projects list and I am trying to patch the ID of the Customer into the Projects list which has a lookup column to the Customers list ( column ID).

 

This is my function which is connected to the button -

 

Patch(Projects,{Title: Gallery.Selected.Title, CustomerID: Gallery.Selected.ID})

 

The error message I get is 'The type of this record does not match the expected type 'Record.' Found type 'Number'.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: How to patch a SharePoint Lookup Column

Hi @laurel_cpi,

 

Would you please share more information regarding your lookup field?

For patch usage with lookup field, we need to patch a record rather than a value.

The Record must match the Schema defined under your lookup field, basically, the following should work:

 

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

The corresponding column could be found under the lookup field :

71.PNG

For the '@odata.type' field (Which I think is a predefined column), just copy and paste the following:

'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference"

Adding a note: please change the lookup field display value to @odata.type to check if this value matchs the one defined under the SharePoint list you created.

Another thread for reference:

Manually set lookup column in new SharePoint list item

If you have any further questions, please feel free to post back.

Regards

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

10 REPLIES 10
Anonymous
Not applicable

Re: How to patch a SharePoint Lookup Column

A lookup seems to be a special field, (much like person or group) - you might have more luck using the lookup control to populate the field and the editform to update it than you would have trying to patch it with text.

 

https://powerapps.microsoft.com/en-us/blog/support-for-lookups/

 

I'll fiddle later today and report back 🙂

 

Community Support Team
Community Support Team

Re: How to patch a SharePoint Lookup Column

Hi @laurel_cpi,

 

Would you please share more information regarding your lookup field?

For patch usage with lookup field, we need to patch a record rather than a value.

The Record must match the Schema defined under your lookup field, basically, the following should work:

 

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

The corresponding column could be found under the lookup field :

71.PNG

For the '@odata.type' field (Which I think is a predefined column), just copy and paste the following:

'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference"

Adding a note: please change the lookup field display value to @odata.type to check if this value matchs the one defined under the SharePoint list you created.

Another thread for reference:

Manually set lookup column in new SharePoint list item

If you have any further questions, please feel free to post back.

Regards

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Anonymous
Not applicable

Re: How to patch a SharePoint Lookup Column

Great detail, thanks a mil - I was hoping there was a way to emulate the editform function through patch, this looks like it 🙂

laurel_cpi
Level: Powered On

Re: How to patch a SharePoint Lookup Column

Thanks, this is exactly what I was looking for!

vivek-ealumalai
Level: Powered On

Re: How to patch a SharePoint Lookup Column

Hi @v-micsh-msft ,

This is helpful for patching lookup column , similarly do you have any idea on how to patch lookup column with multi slect ?

 

Thanks in advanceL_8390.tmp.png

 VE

Community Support Team
Community Support Team

Re: How to patch a SharePoint Lookup Column

Hi @vivek-ealumalai,

 

You may take a look at my reply in the following thread for how to patch multiple value person fields.

https://powerusers.microsoft.com/t5/General-Discussion/Saving-data-from-powerapps-to-SP-lists-person...

 

I will make some tests and share the steps about how to patch Lookup fields later (Will add the link after test).

 

Regards,

Michael

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
vivek-ealumalai
Level: Powered On

Re: How to patch a SharePoint Lookup Column

Thanks @v-micsh-msft !!

It worked Now im able to update lookup column with Multiselect option using the syntax in the link you provided.

 

Thanks again ! much appreciated !! 

Highlighted
mchapman
Level: Powered On

Re: How to patch a SharePoint Lookup Column

Hey this worked great for me thanks a ton!  I like this so much better than the forms as you can update many records at once intead of one at a time.  I do have one additional question.  How to I patch a lookup column so that it will be empty in the table it's linked into?  Here's the code I used and it works great to update the values but it won't empty them out when I pass blanks:

 

ForAll(
    GalleryDimProj.AllItems,
    Patch(
        'List-DimMembers',
        LookUp(
            'List-DimMembers',
            ID = GalleryDimProj.Selected.ID
        ),
    {Project:{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
                        Id:Value(lblDimProjID.Text), //Value() Turns lbl from text to number
                        Value:lblDimProj.Text}       //Both labels are passed set to blanks when I want to exclude
              }

    )
)

laurel_cpi
Level: Powered On

Re: How to patch a SharePoint Lookup Column

Hi mchapman,

 

I recently encountered a similar issue when trying to patch blank to a date column in SharePoint. This is what eventually worked. It may work for this as well.

 

I had to enable this setting.

 

https://powerusers.microsoft.com/t5/Building-PowerApps-Formerly/Still-no-solution-for-setting-date-f...

 

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (5,964)