Flow lookup in other list

[Thansson]

Hi

I have 3 sharepoint list connected with id's.

WeekmasterID      Connected to Weekmaster by Title to ID   

WeekmasterID      Connected to WeekHeader by Master to ID

How can I lookup the values in the 2 list connected using the join ID?

I have to make a backup, with information from all 3 list.

[LuisArellano]

Your filters looks fine, are you getting an error?

Or what you want is to query both WeekmasterID and WeekHeader with one action/query?

[Thansson]

@LuisArellano 

Thank you

Yes I want query both weekmasterID and WeekHeader?

Tony

[LuisArellano]

@Thansson I don't think we can get that in a single query but maybe this other post could help.

Solved: Re: Flow running too slow || Merging Excel Files a... - Power Platform Community (microsoft....

[Thansson]

Hi @LuisArellano  Thank you

My knowledge is not up to this.

I got this far, can you direct me to a homepage that can teach to make the last compose?

Tony

[LuisArellano · Accepted solution]

@Thansson Hi, this might not be the best implementation and has not been tested for several scenarios, but hopefully can help you to get what you need or at least get you a better idea on how this could be done. My concern is the performance, and depending on the size of the lists you might want tweak it to retrieve the full list instead of being constantly filtering.

I minimally recreated your scenario:

SharePoint

WeekmasterID

Weekmaster

WeekHeader

Flow

Get WeekmasterID - Is to get the complete list since this is where the ID that will join both of the other tables is found

Initialize variable - both of them are arrays, in this specific scenario 'ArrConcat' is to hold the first Join and 'finalArray' is the join between the 3 tables

Apply to each - 

@{outputs('Get_WeekmasterID')?['body/value']}

Get Weekmaster - Uses this filter, you might need to update the column name: 

Id0 eq '@{items('WeekmasterID_Weekmaster')?['Title']}'

Select - From 

@{outputs('Get_Weekmaster')?['body/value']}

The columns I created were just to better identify the columns from the different lists, you may add, remove or rename them as you need:

For the columns from WeekmasterID table, target your apply to each action name

@items('WeekmasterID_Weekmaster')?['ID']
@items('WeekmasterID_Weekmaster')?['Title']

For the columns from Weekmaster table. Since "Select" is from Get_Weekmaster, we can use the item() function

@item()?['ID']
@item()?['Title']
@item()?['IdLookup/Value']

Since Select outputs an array...

Apply to each 2 - 

@{body('Select')}

Value:

@{items('Apply_to_each_2')}

This should give you the join between WeekmasterID and Weekmaster

Now we do a similar thing to join WeekHeader

Apply to each - Again we use the first list we got

@{outputs('Get_WeekmasterID')?['body/value']}

Get WeekHeader - Uses this filter, you might need to update the column name: 

idLookupId eq @{items('WeekmasterID_WeekHeader')?['ID']}

Apply to each 3 - 

@{outputs('Get_WeekHeader')?['body/value']}

Filter array 2 - 

From:

@variables('ArrConcat')

The first field, enter the name of the column you used for WeekmasterID's main ID, in this example

@item()?['WeekmasterIDID']

 is equal to

@items('WeekmasterID_WeekHeader')?['ID']

Select 2 - Match the column names you used in the previous "Select", see how the rows in the "map" area have matching the column names and item() array names

@item()?['WeekmasterIDID']
@item()?['WeekmasterIDTitle']

For the attributes from the Weekmaster list use

@items('Apply_to_each_3')?['ID']
@items('Apply_to_each_3')?['Title']

Apply to each 4 - Again, the "Select" will return an array add each element to our "final" variable

@{items('Apply_to_each_4')}

You can now use the body in "finalArray", which for the example I shared is:

[

  {

    "WeekmasterIDID": 1,

    "WeekmasterIDTitle": "WeekmasterID 1",

    "WeekmasterID": 1,

    "WeekmasterTitle": "Weekmaster 1",

    "WeekmasterIDLookup": "WeekmasterID 1",

    "WeekHeaderID": 1,

    "WeekHeaderTitle": "WeekHeader 1"

  },

  {

    "WeekmasterIDID": 1,

    "WeekmasterIDTitle": "WeekmasterID 1",

    "WeekmasterID": 2,

    "WeekmasterTitle": "Weekmaster 2",

    "WeekmasterIDLookup": "WeekmasterID 1",

    "WeekHeaderID": 1,

    "WeekHeaderTitle": "WeekHeader 1"

  },

  {

    "WeekmasterIDID": 1,

    "WeekmasterIDTitle": "WeekmasterID 1",

    "WeekmasterID": 1,

    "WeekmasterTitle": "Weekmaster 1",

    "WeekmasterIDLookup": "WeekmasterID 1",

    "WeekHeaderID": 4,

    "WeekHeaderTitle": "WeekHeader 4"

  },

  {

    "WeekmasterIDID": 1,

    "WeekmasterIDTitle": "WeekmasterID 1",

    "WeekmasterID": 2,

    "WeekmasterTitle": "Weekmaster 2",

    "WeekmasterIDLookup": "WeekmasterID 1",

    "WeekHeaderID": 4,

    "WeekHeaderTitle": "WeekHeader 4"

  },

  {

    "WeekmasterIDID": 2,

    "WeekmasterIDTitle": "WeekmasterID 2",

    "WeekmasterID": 3,

    "WeekmasterTitle": "Weekmaster 3",

    "WeekmasterIDLookup": "WeekmasterID 2",

    "WeekHeaderID": 2,

    "WeekHeaderTitle": "WeekHeader 2"

  },

  {

    "WeekmasterIDID": 3,

    "WeekmasterIDTitle": "WeekmasterID 3",

    "WeekmasterID": 4,

    "WeekmasterTitle": "Weekmaster 4",

    "WeekmasterIDLookup": "WeekmasterID 3",

    "WeekHeaderID": 3,

    "WeekHeaderTitle": "WeekHeader 3"

  }

]

[Thansson]

@LuisArellano 

Thank you for your time   (more complicated than I expected)

It is working, and time is okay.