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

Autopopulating a field or using lookups to another list

 
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Autopopulating a field or using lookups to another list

Hi @jay312 

 

Let's try to go from the beginning. First, you get the "Normal People" details (in my example, I use a manual trigger to test), and try to find a trainer in that city. After that, you attribute it to the person based on another list of trainers. 

 

Lists:

Normal People

This list has a lookup to the trainer's list, and I configured it as follows:

Screenshot 2020-02-01 at 17.06.58.png

 

Here's the lookup

Screenshot 2020-02-01 at 17.07.10.png

 

Trainers

This list is more straightforward. Here it is:

Screenshot 2020-02-01 at 17.07.52.png

 

Power Automate

You have four steps:

  1. Trigger getting the information of the "Normal People"
  2. Find a trainer in that city
  3. Check if one exists
  4. If yes, assign it to the person.

 

Here's the result:

Screenshot 2020-02-01 at 17.10.06.png

 

Let's break it down into steps. 

 

Get the trainers for that city

For that, you need an OData query, but it's just comparing the city:

Screenshot 2020-02-01 at 17.10.11.png

Check if I have at least one:

You can check the length of the array and see if you have one value:

Screenshot 2020-02-01 at 17.10.14.png

 

The formula is as follows:

length(body('Get_All_Trainers_for_that_city')?['value'])

 

Fetch the trainer's ID

This is necessary so that you can insert it in the next step. The formula is the following:

first(body('Get_All_Trainers_for_that_city')?['value'])?['ID']

 

Insert the data

Now get all the data that you have and add a new record in the "Normal People" list. 

Screenshot 2020-02-01 at 17.10.19.png

 

 

Hope this helps!

 

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Cheers
Manuel

 

View solution in original post

14 REPLIES 14
Highlighted
Super User
Super User

Re: Autopopulating a field or using lookups to another list

HI @jay312 

 

A simple OData query will do the trick. Here's how to do a Get Items and only get the items that you need:

Screenshot_2020-01-28_at_10_03_46.jpg

 

I'm using a button trigger just to have values to test, but you can use the values that you get from the email.

 

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Cheers
Manuel

Highlighted
Helper IV
Helper IV

Re: Autopopulating a field or using lookups to another list

 
Highlighted
Super User
Super User

Re: Autopopulating a field or using lookups to another list

Hi @jay312 

 

Yes indeed. I just tried to explain how to do the queries in the lists. Then you can use the same strategy to query multiple lists and cross-reference the information between them.

 

Just a sidenote. I made a mistake in the screenshot. When you do the comparison, since it's a string, you need to encompass the values in '. So it should be "City eq 'cityField' "

 

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Cheers
Manuel

Highlighted
Helper IV
Helper IV

Re: Autopopulating a field or using lookups to another list

 
Highlighted
Super User
Super User

Re: Autopopulating a field or using lookups to another list

Hi @jay312 

 

Let's try to go from the beginning. First, you get the "Normal People" details (in my example, I use a manual trigger to test), and try to find a trainer in that city. After that, you attribute it to the person based on another list of trainers. 

 

Lists:

Normal People

This list has a lookup to the trainer's list, and I configured it as follows:

Screenshot 2020-02-01 at 17.06.58.png

 

Here's the lookup

Screenshot 2020-02-01 at 17.07.10.png

 

Trainers

This list is more straightforward. Here it is:

Screenshot 2020-02-01 at 17.07.52.png

 

Power Automate

You have four steps:

  1. Trigger getting the information of the "Normal People"
  2. Find a trainer in that city
  3. Check if one exists
  4. If yes, assign it to the person.

 

Here's the result:

Screenshot 2020-02-01 at 17.10.06.png

 

Let's break it down into steps. 

 

Get the trainers for that city

For that, you need an OData query, but it's just comparing the city:

Screenshot 2020-02-01 at 17.10.11.png

Check if I have at least one:

You can check the length of the array and see if you have one value:

Screenshot 2020-02-01 at 17.10.14.png

 

The formula is as follows:

length(body('Get_All_Trainers_for_that_city')?['value'])

 

Fetch the trainer's ID

This is necessary so that you can insert it in the next step. The formula is the following:

first(body('Get_All_Trainers_for_that_city')?['value'])?['ID']

 

Insert the data

Now get all the data that you have and add a new record in the "Normal People" list. 

Screenshot 2020-02-01 at 17.10.19.png

 

 

Hope this helps!

 

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Cheers
Manuel

 

View solution in original post

Highlighted
Helper IV
Helper IV

Re: Autopopulating a field or using lookups to another list

 
Highlighted
Super User
Super User

Re: Autopopulating a field or using lookups to another list

HI @jay312 

 

I think your condition is correct. Is your first "Get Items" from the Trainers list? If so your condition is correct as long as the city that you compare on the right side comes from the Customer. Then you'll match the customer's location with one Trainer in that location.

 

Makes sense?

 

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Cheers
Manuel

Highlighted
Helper IV
Helper IV

Re: Autopopulating a field or using lookups to another list

Hello @manuelstgomes 

 

Yes, the Get items is from the list which has the users + city 

Highlighted
Super User
Super User

Re: Autopopulating a field or using lookups to another list

Hi @jay312 

 

Then I don't understand the issue, sorry. If you follow the flow before, it should work as you like. I tested it on my side, and it's filling correctly.

 

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Cheers
Manuel

 

Highlighted
Helper IV
Helper IV

Re: Autopopulating a field or using lookups to another list

Hi again @manuelstgomes 

 

I recreated everything from scratch just to replicate your pictures.

 

So I have 2 lists. 

1) Normal People 

image.png

 

2) Trainers 

 
Highlighted
Helper IV
Helper IV

Re: Autopopulating a field or using lookups to another list

 
Highlighted
Super User
Super User

Re: Autopopulating a field or using lookups to another list

HI @jay312 

 

In the "Get Items," the city inside the quotes needs to be selected from the "Dynamic Content".

 

Attached you can find a template. You can import it and run it yourself combined with the instructions that I wrote previously.

 

If you have questions in how to import a template just go here and I explain everything in detail:

https://manueltgomes.com/microsoft/flow-how-to-import-a-flow-template/

 

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Cheers
Manuel

Highlighted
Helper IV
Helper IV

Re: Autopopulating a field or using lookups to another list

Delete

Highlighted
Helper IV
Helper IV

Re: Autopopulating a field or using lookups to another list

Solved

Helpful resources

Announcements
firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

firstImage

Join the new Power Virtual Agents Community!

We are excited to announce the launch of Power Virtual Agents Community. Check it out now!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

Top Solution Authors
Users online (10,871)