SOLVED

Autofill column in a SP list with information from a different list in the same site?

Copper Contributor

Hello!

I have two lists:  "Customers" and "Projects."  "Customers" has two columns:

Customer_Company_Name (unique)       and     Customer_Company_Number (unique and sequential).

 

In the "Projects" list there are multiple "Projects" items for each customer.   

 

In the "Projects" list I would like a column that contains the Customer_Company_Number from the "Customer" list. Something like "If the project Customer_Company_Name is X, populate the Customer_Company_Number from the "Customers" list."

 

I have tried it with Lookup (evidently the Lookup function doesn't support drawing from a different list) and PowerAutomate, which I can't figure out.  I get this far on PowerAutomate:

When an item is created in the "Projects" list --> Get Items: Customer_Company_Number from the "Projects" list --> Apply to Each valuex --> the Condition: if valuex is greater than 1, then... and I'm stuck.

 

I would be grateful for some help :)

6 Replies
Sounds like you have a customer name column in your Projects list. Try replacing that column with a lookup column that pulls in the Customer Name from the Customers list. Then you can configure that column to also pull in the Customer Number.

Hope that helps.
Thanks for the reply, Kelly. I tried that, maybe incorrectly. If I did it right it seems that the out-of-the-box Lookup column type doesn't support getting information from a different list, even if it's in the same site. But I will try that again!
best response confirmed by clackarama (Copper Contributor)
Solution

Hi @clackarama , you could use a simple Flow, watching for new items in the Projects list. When a new item is created you would use a Get items action to retrieve all records from the Customers list where the Customer_Company_Name matched the one input in the Projects list. An Update item action would then append the Customer_Company_Number to each returned item.

Ah! Yes, I will try that. Thank you, John and Kelly. I'll loop back and let you know.
Hi Jon,
It took me some time to backfill my knowledge of SharePoint and PowerAutomate in order to understand and execute you solution. But in the end, it worked. Thank you.
Alan
Hi Kelly,
This also worked, but for a different item I was trying to use. Thank you very much!
Aan
1 best response

Accepted Solutions
best response confirmed by clackarama (Copper Contributor)
Solution

Hi @clackarama , you could use a simple Flow, watching for new items in the Projects list. When a new item is created you would use a Get items action to retrieve all records from the Customers list where the Customer_Company_Name matched the one input in the Projects list. An Update item action would then append the Customer_Company_Number to each returned item.

View solution in original post