Copy some columns values from List A to List B based on another columns value

[markikav1955]

Hi Community 

I need to pull some contact details (Name, Phone, Email) from SharePoint List A (which contains approx 2K records) into List B.

List B is then used as a "tracker" to validate the contacts are still most up to date.

I have a Customer Ref number column  which is unique to each Customer and exists in both lists so assume that can be used to ensure my case created in List B pulls the correct contacts from List A, but I'm unsure how to implement? 

I've tried this in InfoPath which I find easier but I hit limit restrictions when it tries to run the query, probably because list A is quite large and the admins have some limit set.

So my questions are how best to implement this in Power Automate?

I just want it to run once when the "case" is created in List B they will enter some values and the Customer Ref, then in the background it should just run and populate the contact details so users don't have to look them up and enter them.

Thanks!

[#2]

Hello @markikav1955,

For all new items created in List A, you could create a simple Flow that would copy the required values to List B using the When a new item is created trigger.

For multiple records with conditions in List A, you could create a Scheduled Flow with the SharePoint Get items action.

Let me know if you need help with any of this.

Norm

[#3]

Hi Norm

Yes help would be good as I assume I need to use an expression somewhere to state where Customer Ref in A = Customer Ref in B , so I pull in the correct contacts?
Or is it done differently?

Thanks!
Mark

[#4]

Hi Mark,

I need to understand the requirements further. Are you trying to copy the contact details from list A to list B if they don't exist in B?

Norm

[#5]

Hi Norm
Great question.
Its a messy process and it all starts with an upload of columns from an excel file into List B.
This will never populate the 3 contacts columns , they will always be blank.
In List A however they maintain the contact info for every client they have ever reviewed, so once the customer details (with the key column being the customer ref number) is loaded into List B, I would just then go to List A and populate the 3 contacts fields form List A into List B for that particular customer ref (which will exist in both lists).
I have a default progress status = New for when they do any new bulk upload from excel, so I would also only run the Flow at New status (or if Item create does the same job then great).

Then thats it for the flow for that particular record in list B.

I may get the same customer ref number again a few weeks later and I would just do the same again as this would be a new "review case" in list B..

Does that all make sense?
Thanks!

[#6]

Actually you've made me think of another use case.
List A may not have the customer stored because it could be a brand new customer who have never been reviewed before.
So in that case what would the best thing to do be?
have another field that I could display on the form and in SharePoint to state "Customer record does not exist in List A" ?

[#7]

I think you need to have 2-way sync. Once when you upload a items to list B, then go fetch the item in list A with the same ID and update all metadata. And another flow on list A, that you run manually that would go and query all items in list B, loop through them and update their metadata.

[#8]

Hi @markikav1955,

I would look at having two Flow:

1. Import Excel to List B
2. Sync List A contacts to List B

The Sync would look for blank entries in List B and populated contact values in List A. If you simplify the tasks it will make this more approachable.

I hope this helps.

Norm

[#9]

Hi Norm and derhallim

it's even simpler than this I think.
They are fine with upload from excel into SharePoint because I have a "bulk upload" view where they just copy the few fields out of excel and paste into the corresponding fields in grid view in SharePoint. This task is quick as there are only ever couple hundred rows max to copy in and they would only do this once a day.

So once this upload has taken place into List B I just want to use the Customer Ref number to go to List A, see if contact name exists there, if it does copy it into List B, if the Customer Ref doesn't exist then output text to another field that says "Customer doesn't exist in database" or something like that so they know this is a new customer or they have never stored this customers details before.

I see what you are saying on the 2 way sync which would be good but its another roles responsibility further down the process to populate the contacts if they don't exist so we don't need to worry about that right now.

Where I am struggling is setting up the Flow for List B to look into List A and copy the 3 contact fields to List B based on the Customer Ref field and then the IF clause, if Customer REf doesnt exist in List A then output that into a field in List B, could even use the Contact Name field to put that text in..

Hope this makes more sense!
Thanks
Mark