Lookup columns from other lists

[pepeday]

Hey there,

I'm trying to generated an automatic report using scheduled trigger.

I'm using a Get Items (Sharepoint) to pull all the data I need and filter them accordingly using Filter Array.

Now the next step is that some columns contain lookup values from other lists in the form of an integer. That is in my List A, the column "Companies" contains an integer which is the ID of the item in reference from List B. In list B I have both ID and Title columns.

I want to create a report which will add a column in the array I have created, adding the Title from List B in the corresponding item of the array.

Sounds complicated but it's nothing more than a standard SQL Join operation which I have no idea how to do in Flow.

Any ideas?

Pete

[v-yuazh-msft]

Hi @pepeday,

I have made a test on my side and couldn't find any way to achieve your needs to add a column in the array, I afraid that there is no any way to achieve it.

Best regards,

Alice

[pepeday · Accepted solution]

So after playing around a bit, I found a way to do it:

1. Retrieve the items you wish to edit using GET ITEMS
2. Retrieve the items you want your lookup to work on using GET ITEMS
3. Initiate an array variable varArrayOutput
4. Create an Apply to each loop based on the VALUES output of the GET ITEMS from Step 1.
5. In the Apply to each, create a FILTER ARRAY on the VALUES output from Step 2. Use as filter the ID of the lookup list and set it to equal each time to the _FK (the foreign key) from the list from Step 1. This way you end up with an array with only one item.
6. In the Apply to each, create an array item using a COMPOSE, like below. If for example we wanted to refer to the Title from the other list:

{
"ID":[reference],
"Title":Body('Get_lookup_list")?[0]['Title'],
"Date":[reference]
}

7. Append the output from the COMPOSE to the varOutputArray

And there you go! Let me know if you want me to explain further what I did.

Pepe

[v-yuazh-msft]

Hi @pepeday,

If your problem has been solved, please go ahead to share more details about the solutions and mark the post as solved by clicking “Accept as Solution” so that this thread will be marked for other users to easily identify!

Best regards,

Alice