Merge Sharepoint Lists

[zmansuri]

I have 3 SharePoint Lists. Currently user first comes into List A and then move to List B. Here some fields are added. Then he moves to List C. Here some more fields are added.

All of them have some fields in common like FirstName Last name etc. And also have one unique field in common.

What i want is to create one master database. That have all the columns from all 3 list. and no redundant data in common fields.

I know it's not a PowerApps question but i am active here and most of you are working with SharePoint so thought i ask.

[BCLS776]

My first thought is to use Power Automate to create a new item in List B using only the columns from List A you want to keep. Similarly, for List B to List C. You can trigger the flow when an item is created and/or updated in a list.

Does that do what you are after?

[zmansuri]

I need to create List D with all the column of List A B and C.

But records shouldn't be duplicated because all 3 list have common records with some extra fields.

[BCLS776]

Sounds like you can still do all that in Power Automate:

1. Use an appropriate trigger (maybe when an item is created or modified on List C?)
2. Use a Get Items on each of Lists A & B with a filter query to find the applicable item that matches the common identifier from List C.
3. Add an item to List D that combines only the fields that you want to keep from Lists A, B, & C

What do you think?

[PaulD1]

Can you consider reviewing/changing your data model?

May not be practical due to the limitations of SharePoint as a 'database' but, typically, having a good data model using related tables or flags and views to determine whether a user is A, B, or C will be much more robust than a reliance on Flows to shuffle records between lists.

[zmansuri]

I am sorry guys i was not clear. I don't wanna trigger it on new items created or modified. I want to do this for an existing items in my list. Once they are properly merged i will only use that final list.

[PaulD1 · Accepted solution]

If it is a one-off exercise (data cleansing) then I would use MS Access. Use the External Data option to create linked tables to the SharePoint lists. You can then use Action Queries (Append, Update, Delete) to move the data between the lists (or even copy/paste in some cases).

Using Access to modify the contents on SharePoint lists in bulk (and then do things like search for duplicates) is much easier that trying to do so through the SharePoint interface.

[zmansuri]

Thank you. One final thing can you provide me with some reference links to do this. Youtube videos blogs anything. I haven't worked with access yet. And since it is a crical job. I don't wanna make any mistakes.

[PaulD1]

Here you go:

https://youtu.be/bER_KhWpDdQ