How to: OData filter in Power Automate on Dataverse lookup field

[Music] hello in this video I want to show you how you can write an O data filter inside power automate so that you can filter the row that you're coming from data verse not any kind of field this time we are talking about lookup field which I see lots of confusions around it and everything comes from confusion between logical name and the schema name of every single column that you create inside dataverse tables without wasting time let's go ahead and do it it's going to be fun and short of course as always let's start with our setup I have two tables one of them is called executive staff and the other table is called Department inside executive staff I have a field called Department which is a lookup field and it takes all its values from the other table called Department naturally we have a one too many relationship between these two tables for old school database developers so let's go back to the concepts that they are new here there are a few things that we need to understand first of all department does not have any one of the values that we are looking for the engineering diversity and inclusion HR or whatever that you see here does not really exist list here all you have here is just this gu ID or our department ID so basically if you're searching for engineering here you failed no question about it but let's get real the only reason that we are working with these modern platforms like data verse power apps power automat and all those stuff is to avoid these stupid complexities that everybody hates it we want to focus on creativity and this is not what you're looking for now remember I told you this is not the actual data what we have here as department is actually a reference to a schema somewhere else I emphasize on it again schema remember this keyword but in reality search is happening here so whatever we want to search we have to search here inside the schema of our lookup table now before we put them together there is one more thing to remember always always when we write on o data filter we filter based on The Logical field name now let's put all of them together if you want to search something here we should say search for anything in this schema that it's Department name field is equal to something and for the Department field name we have to refer to the logical name trust me it is that easy I come back inside power automate I created a flow as manually trigger a flow it just accepts one par parameter as Department name and here I have a list rows action that is connected to my executive staff and at the moment it brings in everything and you see this warning it says are you sure you want to bring all of them say no no no no no wait hold on to your horses I will get back to you and eventually I will show all the records that my flow receives now inside the table executive staff if you expand I have quite a few rows I have I guess 15 records here and I want to search this based on the department name so I can simply come back here and write my filter click remember we need schema for the Department right so I come back here I click on this drop down and I click on edit column if you go to the advanced options you will find the schema name just copy it and bring it to your formula done now for the other field as I mentioned we need the logic iCal name for this field so we need to go to the other table which is my department and inside this one if I want to search for this field I need to pick up the logical name for this thing scroll down expand scroll down and here is my logical name as the field that I want to search I guess it's needless to say that if you want to search other fields that they are here in this lookup table you can simply pick up the logical name for the other columns so let me just say copy and bring this one here now inside o data instead of dot we use forward SL as separator and I paste this guy here and after that the rest of the job is easy it's equal to whatever that comes from the Department name so I can simply come back here and I search for Department name stick it here just make sure it puts it in between the two single codes and we are done and at the end I have a select action that only shows me the full name of the executive staff that they match the criteria for our search so let's say we are searching for engineering department I test this flow manually save and test and I go for engineering and I click on run flow done and I expand select let's see what we got here we only have Charles Johnson so if I come back here you will see under engineering department there is only one record let's try again for diversity so I click on edit and this time if I test it again test and I just stick it here run flow done and you will have a few more records here you got your answer you can like the video and leave but but if you're still here I have a little bit of bonus point that I want to cover how do we add the department name here because if I just come back here and I want to say another field called Department I can come back here and I can pick Department value which is the only thing that you get from this UI related to the department it gives you something weird so let me show you remember this field contains all only the ID so Department value manually test and I say for example HR and I click on run flow done you will get some not so beautiful result now we want to get the actual Department name here how do we do that answer is easy just copy the input or whatever that comes out of list rows select all of them stick it inside notepad no matter how many records there are just go to the top top of it you see it's a Json array starting with square bracket so this is your first record let's scroll down and try to find the HR department right so this is the field with this odd name that contains the actual name value of this let me just copy this and bring it back inside power automate now I have the golden key I can just get rid of this weird gu ID and here I go under expression and I simply write item which refers to the current item being processed in front of it opening and closing square brackets single code and inside this single code I just stick the field name that I got it from my notepad I click on okay I click on Save and now I can happily test it let's search for HR now and I click on run flow done and I can expand it and you will get the results happily ever after and if you want to learn more about the expressions and how to use them inside power automate I have another course on un me called master power automate expressions in 2 hours and if you're lucky enough to see my video within the first 5 days of the release just scan this QR code and get the course for free all right I hope you enjoyed this video if you did you know where the like button is and if today is the day that you push that subscribe button you really make my day thank you for watching and I'll see you soon in the next [Music] video

Channel: Alireza Aliabadi

Views: 4,377

Keywords: odata filter power automate dataverse lookup, odata filter pwoer automate dataverse lookup, Power automate OData, OData filter lookup, OData filter look up, Power Automate lookup get rows, Dataverse get rows filter lookup, Flow OData filter, filter dataverse odata lookup, look up field dataverse OData, Power automate dataverse get rows filter, power automate get rows lookup field, Dataverse lookup filter get rows, How to: OData filter in Power Automate on Dataverse lookup field

Id: TLqDGUQ2uh4

Length: 9min 1sec (541 seconds)

Published: Mon Jan 29 2024