Answered

How do I patch a lookup column with values from another single line column text in the same list?

(0) Share

Report

Posted on by MrClick

Introduction:

I exported a table (Contacts) from a web application to an Excel file (Contacts.xlsx). This table had a column called CompanyName that basically takes the company name from another table called Companies. There are about 400 contacts. Of course, some contacts belong to the same company. I also exported the content of the table Companies to an Excel file (Companies.xlsx).

I wanted to recreate the same structure using Power Apps. So I first created a List called Companies based on Companies.xlsx file. Then I created another list called Contacts, based on the Contacts.xlsx file. Now the Contact List has a column called Title that has the company names. All good so far. So I created another column called CompanyName which of type Lookup that takes the company name from the Company List. Of course, this column in the Contacts list is empty. I could manually add the company name one by one. And I did that for about 50 names. There must be a smarter way to do it. All I want is to set the value of the CompanyName lookup column to the name in the Title column. But I have no idea how to go about it using PA. And help will be great. Thank you in advance.

Color Guide: Table from a web database

Color Guide: Excel File

Color Guide: Sharepoint List

Color Guide: Column in a Sharepoint list.

Categories:

Building Power Apps

All responses (3)

Answers (1)

Sort by

v-yutliu-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @MrClick ,

Do you want to update CompanyName field(lookup field) by using Title field(text field) and these two fields are belong to the same list Contact List ?

If so, try this formula:

ForAll( RenameColumns( Contact,"ID","ID1" ), Patch(Contact,LookUp(Contact,ID=ID1), {CompanyName:{Value:Title,Id:LookUp(Company,'company name'=Title,ID)} } ) )

Best regards,
MrClick 64 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @v-yutliu-msft ,

Thanks for the answer. Before I try it, I need to understand a few things.
First, yes, CompanyName (which is a lookup field) and Title (text field) is in the same List. Basically, I want to copy the content of the Title text into my CompanyName.

- Why did you use the RenameColumns function? Basically whatever in record one Title, I want to copy it to the value of CompanyName.
- Why do you mean by this line below? Do you mean Contact instead of Company?
LookUp(Company,

Thanks once again.
Verified answer

v-yutliu-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @MrClick ,

Do you want to update Title field in Contact List by using CompanyName field in Contact List?

If so, in your formula, you do not need to use any part about Company list.

What's more, the reason why I use RenameColumns function is that ForAll function and Patch function could not use for the same data source.

If you use formula like this:

ForAll( Contact, Patch(Contact,... ))

You will get error.

To avoid this error, using RenameColumns is a good way.

Have your tried this formula?

ForAll( RenameColumns( Contact,"ID","ID1" ), Patch(Contact,LookUp(Contact,ID=ID1), //compare contact list with the renamed table {CompanyName:{Value:Title,Id:LookUp(Company,'company name'=Title,ID)} } ) )

I suggest you read this blog in details: https://powerapps.microsoft.com/en-us/blog/bulk-update-using-forall-and-patch/.

Then you will know more about how to use the combination of ForAll and Patch function.

I suggest you notice the RenameColumns part and [@] part.

Best regards,