How to use OData filter in Power Automate on SharePoint lookup columns

foreign [Music] let's assume you want to get the items in a SharePoint list from Power automate obviously you use the get items that connects to SharePoint and get the items but let's say you want to filter the items in the list using a field that is person or the data type is person or group now one simple way is to get all of them bring it inside power automate and filter it using the filter action which is available inside power automate but there are limitations that's not a clean practice the second way or let's say should I be in the first way is using the odata filter in this video I want to show you how you can use odata filter to filter SharePoint list items from inside power automate using all data filter this is going to be fun let's see so let's assume we have a SharePoint list and one of the columns in that SharePoint list is lookup and we want to use get items inside power automate to filter for the items that match our search criteria based on that lookup field lucky enough we have a scenario when an employee leaves our company we would like to create an email of all inventory items assigned to him or her and send it to alireza you guessed it right we have a list inside SharePoint called employees that has a display name and the employee column which is actually a person field the two other columns it doesn't really matter but on the same side we have another list called inventory items in which we have the inventory item and another field called assign to which is a lookup from the list of employees the suggested solution should be something like that we need to create a power automate flow that accepts the leaving employee email address then the flow should use that email address to look up the employee in that list and when the record related to that employee is found it should use that ID and filter the inventory items based on the assigned to field which is our lookup which is the topic of our video so without saying you realize that it's a one-to-many relationship so we need to have two lookups one lookup to find the employee by email yes employee is a person field but in reality it's a lookup backstage it's a lookup that you can select the items from all available users in this environment and the second cup is going to be the assigned to which is the obvious but the don't rush before you go to Power automate and create flow we need to have a few things before we start first of all you need site collection administrator access to the SharePoint site that you are working with site owner is not enough why well because you need a second tool called SharePoint client browser and we need to download it to do that just go to Google search for SharePoint online client browser you will find a SharePoint client browser click on it and it takes you to GitHub scroll down and you will find the SharePoint online download just click on download it's a zip file download it and expand it and you will have something like this and to run this application and connect it to your SharePoint site you must be the site collection administrator now that we have all the tools let's start by creating our flow they go inside power automate portal I want to create a new flow and it's going to be an instant Cloud flow I want to fire it manually so I give it a name employee inventory lookup and I click on create fantastic the flow is supposed to get the employee email so I click on input and I pick email I call it employee email and as I said we need to create a get items action just be careful we need get items get item only searches by the item ID and it gives you only one single record for all data you need get items so I pick get items and I call it get employee Buy email the site address I go to the site and I right click on the icon and I copy the link address this is the site that contains both my lists one of them is employees and the other one is inventory items that you see it here so I go back to my power automate site address click on drop down click on custom values and I just stick the URL here if you're good the list name that I want to pick is going to be employees and done as soon as you save it it will complain because power automate doesn't like to bring all the items in a SharePoint let's say you know what there is something called odata you better use it sure I got it we want to use it so I click on show Advanced options filter query now we need the field name to use it in our filter query and that's where we go to SharePoint online client browser so when you run spcb you can come here and click on site Collections and I want to add a site if any the URL of the site I just paste it here and I click on OK if it's the first time that you're running it it's going to prompt you for the username and password just go with that and connect again if you are not the site collection administrator this application will not connect so we have the site just let's expand it and go and find the lists the list that we want to connect to is called employees so let's scroll down and find employees here now if I expand employees you will find fields there are lots of fields here that most of them are internal fields or the system Fields but if I scroll down here you will find a field called employee which it says it's a custom column we need the name of this field and not the title that we see here because this title can change what we are looking for is static name let me just scroll down for this field there's a property called Static name which is employee we are lucky that the title and static name are the same but quite often it happens that someone changes the title or the title that you have as a space in it so in that case static name is different so always pick the value from the static name I bring it back to Power automate under filter query I put the column name there but this employee is a person field and the person field has a lot of properties inside it one of which is email and you're specifically looking for that email all data is not something new related to power automate it goes back to Old ages that we were using rest API so if you want to get a list of available items inside this odata query you need to look into the rest API I made your life a whole lot easier so for the person or group field this is the list of all the fields here and we're looking for email just be careful it's case sensitive so I bring it back here and the syntax is forward slash email and we are looking for the records that the email is equal to put two single quotes and you want to insert the value of the email that you're searching for in between these two codes so I scroll down here and I pick employee email and I stick it here just make sure it sits between the two single codes now I can save it and we are good so the first part of the lookup is done and we have the ID of this field let's try it and we should have the ID of the record containing the employee that we provide the email let's test it so here I just add a variable initialize variable and I give it a name as employee record ID I copy this and I also rename this variable so we know what it is the data type is integer there we go and for the value because this git employee by email returns a set of Records while we are only looking for one record let's come back here and instead of directly picking up the values from here I come back here and go to the expression by the way if you just add the field directly here the flow assumes that get items returns multiple records so it wraps this initialize variable written apply to each Loop which we really don't need it because we know that this get items with our filter should return only one record so to get only one record which is the very first one I click on this guy I click on expressions and I say use First okay open the bracket I need the first record of the get employee by email so I click on Dynamics I scroll down and whatever the value it returns which is the list of items I push it in between the brackets for the first but we don't need the entire record I just need the ID we are good I click on OK and if you wonder where this ID is let me bring you back to the list of employees and if I modify this view edit current View I can scroll down and I can find the ID and I will add it here it's a good idea to make it the first one so we can easily refer to that I click on OK and you will see this is the ID we are looking for now let's try it and see if it works so I'm looking for James Smith's ID so I go back here save test manually test continue we are looking for James Smith so I go back here and I look for James Smith and I click on run flow done okay let's see what we got here we got id2 and we go back here James Smith has the ID too so we are good lookup one is done perfectly now it's time to build the lookup 2 which is a lot easier because we have the ID we don't care about those weird fields that may or may not exist I come back here to my flow while I have the ID I click on edit I add a new Step this time I say get items again I click on get items I need to rename it I call it get inventory items we need a URL of the site again copy and here I paste it list name is gonna be inventory items and again when we want to write oh data query we go back to our SharePoint online client browser this time we are looking for inventory items so I close this employees list and I'm looking for inventory items expand Fields expand and this time we are looking for assigned to and I click on this one scroll down and again we're looking for static name assigned to is there and static name is also assigned to so just make sure you get the static name I bring it back here and just like before for the filter query I just stick that assigned to here but we are looking for the ID field because we do have the ID and I can say it's equal to the employee record ID we do not put a single code here because employee ID is a number and we are searching for a number single code is four strings so let me just save it and assuming that this one returns all the records related to the employee that we provided the email address I can simply create another variable initialize variable and I want to say rename it's going to be employee inventory items let me just copy the same thing and provide the name for the variable this time it's going to be a string let me just save it and right after that for every item that it returns I want to append it to this variable so append to string there we go let me just rename it and I call it append inventory item and the name of the variable is there is only one item the value that we're gonna add is going to be the items that get inventory items returns so you click on it if I scroll down get inventory items I need the title for the inventory item as soon as you add it it wraps it using an apply to each which is exactly what we want because we have more than one item but we want every single item be in one line so right after the title we press enter so it will add the next item to the new line which is great now I just need to send the email send email V2 and I want to send it to myself and subject is going to be employee items and for the body I can put the employee items and send it to Ali Rosa let me just save it and we test it test and I want to say test continue and I want to go for James Smith and I say run flow done apparently done so let's go back to my mailbox and there we go water hose that we have here there is only one item assigned to James Smith let me add one more item here so let's say inventory items and I want to call it new test item item and I want to assign it to James Smith and I click on Save let me run it again and see if it returns two items because now we have two items assigned to James so edit test again test it's going to be James Smith run flow done now let's go back to my mailbox click on it and there we go water hose test item the reason that it actually doesn't put it in the second line because send email V2 accepts HTML not just a regular enter so if you want you to appear in two different items you're gonna come back here and expand the sky expand oops should be under edit so you go to append instead of enter you can put a new line in HTML language it's going to be BR slash let me just save it and test it again test and I'm going to add James Smith run flow and then so let me go back to my mailbox this time it shows every item in a separate line easy wasn't it I hope you enjoyed this video thank you for watching don't forget to like And subscribe and I'll see you soon in the next video thank you thank you foreign

Channel: Alireza Aliabadi

Views: 3,404

Keywords: How to use OData filter in Power Automate on SharePoint lookup columns, power automate, microsoft flow, power automate odata multiple filters, lookup column, power automate odata filter query null, power automate odata filter contains, sharepoint get items odata filter query, power automate odata filter query sharepoint, microsoft flow tutorial, power automate examples, Power automate OData filter, Power automate sharepoint lookup field, get items lookup field, OData person field

Id: 8Ngtu-W-ITA

Length: 17min 49sec (1069 seconds)

Published: Tue Aug 29 2023