Answered

How to patch a SharePoint Lookup Column

(2) Share

Report

Posted on by laurel_cpi

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

Categories:

Building Power Apps

All responses (18)

Answers (1)

Sort by

Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

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 🙂
Verified answer

v-micsh-msft Microsoft Employee on at

Like
a
(17)

Report
Copy link

Link copied!
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 :

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 Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

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 20 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks, this is exactly what I was looking for!
vivek-ealumalai Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

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 advance
VE
v-micsh-msft Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!

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-Group-column/m-p/77329#M30632

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

Regards,

Michael
vivek-ealumalai Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

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 !!
mchapman 15 on at

Like
a
(0)

Report
Copy link

Link copied!

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 20 on at

Like
a
(1)

Report
Copy link

Link copied!

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-fields-to-blank/td-p/297926
mchapman 15 on at

Like
a
(0)

Report
Copy link

Link copied!

Well, I tried and I just couldn't get it to work. So I decided on a diffrent strategy. Instead of trying to set it to blank, set it to a default value and then enter that default value into the source table the lookup column is based on. Works like a charm...