How to patch a SharePoint Lookup Column

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

[Anonymous]

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 🙂

[v-micsh-msft · Accepted solution]

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

[Anonymous]

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]

Thanks, this is exactly what I was looking for!

[vivek-ealumalai]

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]

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

[vivek-ealumalai]

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]

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]

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