cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Lookup columns from other lists

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
1 ACCEPTED SOLUTION

Accepted Solutions

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

 

View solution in original post

3 REPLIES 3
Community Support
Community Support

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

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

 

View solution in original post

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

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

MPA Community Blog

Power Automate Community Blog

Check out the community blog page where you can find valuable learning material from community and product team members!

Top Solution Authors
Top Kudoed Authors
Users online (7,981)