cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jay312
Level: Powered On

Autopopulating a field or using lookups to another list

Hi,

 

I have 2 lists.

 

  • List 1: A bunch of emails of REGULAR people and what city they are from.
    • E.g Email: John@sfsfss.com  City: United Kingdom
  • List 2: A bunch of emails of TRAINERS and what city they are from. 
    • E.g. Support Email: dsdsad@yhadd.com City: United Kingdom

 

What I want to do is when an email arrives in a mail box, create a new sharepoint item in a list with the contents of the email.

My list contains:

  1. Email address of sender
  2. Subject of email
  3. Body of email
  4. City
  5. Trainer assigned

I need help getting 4 and 5 please. 

  1. What I want to do is Look up the persons name/ email address on List 1, if they exist on List 1 then populate the City field with their city on the sharepoint list using MS Flow

 

Please do you have any thoughts on how to do this look up/ auto-populate step? Below is a step by step run through

 

  1. John emails Help@sdsdd.com saying he has an issue
  2. Flow autopopulates basic fields in a sharepoint list, e.g. the subject + body
  3. Flow would then search list 1, which contains a list of all users names and their city. If the person exists on that list then flow will take the persons city and populate the city field in the sharepoint item.

I can provide more info if needed. All my flow now is get an email and create an item on a SPO list.. 

I also saw this thread. https://powerusers.microsoft.com/t5/Building-Flows/lookup-values-from-2-lists/td-p/235739

image.png

1 ACCEPTED SOLUTION

Accepted Solutions
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
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

jay312
Level: Powered On

Re: Autopopulating a field or using lookups to another list

Hi @manuelstgomes 

 

Thank you for reply!

 

So my flow at the moment is in the pics below. So I need to change the filter to City eq City to pull the city field from list 2? 

Don't I need some condition that check if the user name of John is in List 1 and list 2? If its in list 2 pull the City value from List 2 and put it in List 1? Also my update item part seems to be a mess so will be updating that too.

 

How does the flow know to only get the city value if the persons name or email is John @ ... emaildomain.com? Is that why  you need the user name eq user name?

Thank you!

 

image.png1image.png2image.png3

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

jay312
Level: Powered On

Re: Autopopulating a field or using lookups to another list

image.pngThanks @manuelstgomes 

Would this pic work? In order to fill in City field?

Do you have any thoughts on the update item step after?

It looks like it may work - just testing.

 

So to summarise:

List 1: has Name and email and other stuff (filled from email) and city which is blank

List 2: Has Name and City

 

Use get item to filter List 2 to only City.

 

Then use update item to update City field in List 1 with the City field in List 2

 

Do I not need a condition that checks if the Name on List 1 = Name on List 2? and if Yes then use the city field in List 2 for that person?

 

Actually should I filter on the name and then use update item to get the City field? Because the City field in List 1 is blank at the start

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

jay312
Level: Powered On

Re: Autopopulating a field or using lookups to another list

Hi @manuelstgomes 

 

Thank you for the detailed reply! Some questions please.

 

My flow is as follows to achieve the goal off:

 

3 lists - no look ups / all standard 

  1. SPO list which contains all entries 
  2. SPO list which contains a list of all users emails and their city 
  3. SPO list which contains a list of all the possible citys and an equivalent trainer (we could ignore this step for now as it should be the same set/ flows as in part 2?)

Process

  • Create an item when an email arrives and extract standard stuff. It has 2 unknown fields 'City' and 'Trainer' 
  • If users email or name exists in List 2, then copy the city field from list 2 and add it to the record in list 1 
  • Bonus: If a city is found and added to record, check List 3 if that city is there and thus pull the trainer and add to record in list 1

 

Questions I am unsure on:

  • What would I type in the Filter Query? 'City eq City' ? using Get items on List 2?

 

Please see pic below of my flow.

 

image.png

 

 

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

jay312
Level: Powered On

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 

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

 

jay312
Level: Powered On

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 

 
jay312
Level: Powered On

Re: Autopopulating a field or using lookups to another list

Hi @manuelstgomes  For some reason not sure if my message above is showing everything so.. will post below. Do you see anything obviously wrong with the below? E.g. is the filter City eq 'City' ok? I'm not sure where you got maincity from of if it depends. I ran the flow and it went successful but the condition length.. greater than 0 did not get picked up. 

 

List 1 same as yours. 

List 2:

image.png

I change titles to real emails when I test flow - should I change it to a group/email and not single line of text?

 

Flow:

image.pngimage.pngimage.png

 

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

jay312
Level: Powered On

Re: Autopopulating a field or using lookups to another list

Thank you very much @manuelstgomes  will check the download 🙂

 

Quick question please. I clicked on filter query but for some reason the dynamic content of the list is not showing up.

 

Only the dynamic content from the email is. I am using the list trainers that has city + name / same as yours. Any idea for this?

 

I scrolled down too and just email dynamic content from step 1

 

image.png

 

 

jay312
Level: Powered On

Re: Autopopulating a field or using lookups to another list

Hello @manuelstgomes  🙂

 

I just tried the flow you sent and it worked great!.. but just one thought please. 

 

Using your flow assumes the person enters their City in the record, for you city in city field. I entered a city field in my record and your flow worked perfectly (trainer was assigned :))

 

In my scenario the city field is blank at the start, I need to look this value up, when the user submits the record, so how would I go about comparing the blank city field, to one on a separate list and if the user exists in list?

 

Could I compare the email of list 1 to list 2, if the same then update the item with City from list 2?

 

I have made a separate list called 'Datebase of users' this would be a list of all users.

What I want to do is look up the persons name on that list, if yes, pull the city field, (then later I can add in your pulling in trainer flow)

 

Do you have any idea how to edit the flow you sent to fulfill this? I have made a city field 

 

image.pngimage.png

Helpful resources

Announcements
firstImage

Better Together Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

firstImage

Incoming: New and improved badges!

Look out for new contribution recognition badges coming SOON!

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!

sixthImage

Community Summit North America

The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (6,855)