How to use Power Automate (or something else) to Join multiple Sharepoint Lists

[ecrespol]

Apologies in advance what may be a simple solution using Power Automate (or that might be more easily done with some other method). I currently have a SQL table that is being exported out to a SharePoint List on a nightly basis ("List A"), I need to present some subset of that list to my users with the values from List A and give the users the ability to populate additional fields that are currently configured in a different SharePoint List ("List B")

For Simplicity List A would be populated from a query coming out of the data warehouse would contain the following with values changing or additional records added on a daily basis (the UniqueID for existing records would not change):

UniqueID, Customer_Name, Assigned_Employee, Dollar_Value

List B (which would be populated by our employees) on the list would contain the following:

UniqueID, Field1_MultiSelect, Field1_TextArea, Field2_MultiSelect, Field2_TextArea, Field3_MultiSelect, Field3_TextArea

Since the information in both lists is needed by the users populating the values in List B via SharePoint the idea would be to present all of this information in a single List that contains both sets of fields (i.e. UniqueID, Customer_Name, Assigned_Employee, Dollar_Value, Field1_MultiSelect, Field1_TextArea, Field2_MultiSelect, Field2_TextArea, Field3_MultiSelect, Field3_TextArea) as if it were an outer join from List A to List B on the UniqueID.

The issues are that;

1. the values coming from List A may change upstream of this application and we need to have those changes reflected in the combined List.

2. The values input into List B (or the combined list) would then need to be relinked to the new List A when that was refreshed.

3. There is no ability to insert values from the input into List B (or the combined list) back into the Data Warehouse so that it can be created linked as part of the creation of List A.

[annajhaveri]

@ecrespol so in nutshell when user goes to fill in details in List B they should see the data from List A for a specific unique ID? You can create a lookup column in List B which refers to unique ID column of List A, that is how you can join lists, then you can add other fields from List A as associate lookups (only basic field types are supported as associated lookups)

[ecrespol]

My only issue is that List B should be based on the UniqueIDs in List A in effect an outer join from List A to List B and I think a lookup will only work of the UniqueIDs are already populated in List B