Unanswered

How do you patching a Lookup column with values from a text column in the same List?

(0) Share

Report

Posted on by MrClick

I have a list called TestList that has two columns, Title (single-line text) and CustName (Lookup). The Title field is already filled with customer names. However, the CustName column is empty. I want to be able to copy the values from the Title column to the CustName column. How do I do this? Thanks.

I have the same question (0)

All responses (17)

Answers (0)

mdevaney 29,987 Moderator on at

Like (0)

Report
Re: How do you patching a Lookup column with values from a text column in the same List?

@MrClick

You can copy the text from Title to CustName in all rows with a single line of code.

UpdateIf(TestList, true, {CustName: Title})

Or you can do it for a specific row if you know the ID number.

Set(varLookupRecord, LookUp(TestList, ID=1)); Patch(TestList, varLookUpRecord, {CustName: varLookUpRecord.Title});

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Was this reply helpful? Yes No
MrClick 64 on at

Like (0)

Report
Re: How do you patching a Lookup column with values from a text column in the same List?

Thanks for the help @mdevaney Unfortunately, it did not work. I had to take a photo of the error instead of the screenshot. Where did I go wrong? Do not forget that CustName is a Lookup column.
Also, would this line below do it for all the records in the list? Ideally, I want all empty CustName to be filled with the value of the Title column. I could have done it manually, and saved myself hours of figuring out, but I just think it would be a failure for me. 🙂
UpdateIf(TestList, true, {CustName: Title})

Was this reply helpful? Yes No
mdevaney 29,987 Moderator on at

Like (0)

Report

Re: How do you patching a Lookup column with values from a text column in the same List?

@MrClick
Oh, it's a lookup column? Can you please provide more info in that case? I need to see the other list that stores your lookup column to provide an answer.

Once you do this I can replicate it on my side and give you some code to try.

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Was this reply helpful? Yes No
MrClick 64 on at

Like (0)

Report

Re: How do you patching a Lookup column with values from a text column in the same List?

@mdevaney
- Ok. so, the TestList has two columns, one called Title (single-line text) and the other called CustName which is a Lookup column from a table called CustName.
- The other List is called CustName and has only one column called Title (single-line text) used to store the name of the customers.
Needless to say, those two lists are created to test what I want to do with the real Lists.

I hope the screenshot below helps. It is from the TestList CustName column. Thank you once again.

Was this reply helpful? Yes No
mdevaney 29,987 Moderator on at

Like (0)

Report
Re: How do you patching a Lookup column with values from a text column in the same List?

@MrClick
I'm having some trouble. I thought this would have worked but I'm getting an error similar to this thread:
https://powerusers.microsoft.com/t5/Building-Power-Apps/Fetching-items-failed-Possible-invalid-string-in-filter-query/td-p/619241

With( {wBlankRecords: Filter(TestList, CustName.Value=Blank())}, ForAll(wBlankRecords As BlankRecord, Patch(TestList, BlankRecord, { CustName: { '@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference", Id: LookUp(CustName, Title=BlankRecord.Title, ID), Value: BlankRecord.Title } } ) ) )

Was this reply helpful? Yes No
MrClick 64 on at

Like (0)

Report

Re: How do you patching a Lookup column with values from a text column in the same List?

Thanks @mdevaney for trying.

I never knew something a simple as copy the value of one text column to another column from the same list could be that difficult.

I will try to use my common programming knowledge to figure out a solution.

Thanks.

Was this reply helpful? Yes No
WarrenBelz 151,875 Most Valuable Professional on at

Like (0)

Report
Re: How do you patching a Lookup column with values from a text column in the same List?

Just another suggestion to expand on @mdevaney 's considerable effort

With( { wBlankRecords: Filter( TestList, CustName.Value=Blank() ) }, ForAll( wBlankRecords As BlankRecord, Patch( TestList, {ID:BlankRecord.ID}, //changed here { CustName: { '@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference", Id: LookUp( CustName, Title=BlankRecord.Title ).ID, //changed here Value: BlankRecord.Title } } ) ) )

Was this reply helpful? Yes No
MrClick 64 on at

Like (0)

Report

Re: How do you patching a Lookup column with values from a text column in the same List?

Thanks, @WarrenBelz.

I am having some errors when I try to patch. Will try to figure out and update. Thanks again.

Was this reply helpful? Yes No
WarrenBelz 151,875 Most Valuable Professional on at

Like (0)

Report
Re: How do you patching a Lookup column with values from a text column in the same List?

@MrClick ,

Put @mdevaney 's ID code at the bottom back the way it was. You really need to only change your existing code in the area

{ID:BlankRecord.ID},

instead of

BlankRecord,

this is only a suggestion as I would have thought the original would have worked, but put a couple of variations in.

The easy solution is get rid of the Lookup field and do the same Lookup in Power Apps instead.

Was this reply helpful? Yes No
MrClick 64 on at

Like (0)

Report

Re: How do you patching a Lookup column with values from a text column in the same List?

Hi @WarrenBelz

What do you mean by this?
"The easy solution is to get rid of the Lookup field and do the same Lookup in Power Apps instead."

The Lookup field was done using Power Apps.

Thanks.

Was this reply helpful? Yes No