Power Automate Flow odata filter dataverse lookup and choice columns

in today's show we're talking about filtering data versus data using power automate flow and more specifically we're going to get into the complex columns so we're going to talk about how to filter a lookup column and a choice column because both of those are way harder than you think they are and way harder they should be so we're going to do is we're just going to run through how to get to the data on both those and if you're not into dataverse don't worry we're going to talk about some of my flow troubleshooting and learning steps so you'll learn about flow even if you don't care about databurst but first here's our intro hi my name is shane young with powerapps 911 those guys and today it's about filtering odata and dataverse and so this is something we've kind of touched on a little bit before but a friend of mine this week said hey how do i filter a lookup column i was like oh it's easy blah blah blah blah and then she's like and i was like oh and so after a little trial and error like way more trial and error than i wanted i finally remembered how to do it again so i thought if nothing else i'll make this video so that if i forget again how to filter these things i can look it up but really you know it's nothing too complex it's just about understanding how to see the raw data we have to talk a little bit about virtual columns which is i didn't know that was a thing so anyway let's switch over to my desktop and let's build a couple of real quick flows to show you how this would go flow go anyway over my desktop okay so over here on the desktop first thing i actually want to go do is look at the data real quick so i'm going to jump into powerapps so tables and so i have an entity or table called chewy tracker and so the chewy tracker one it's got a couple columns real cat one is the color of his mood and the other is the toy that he wanted to play with so the toy is a lookup column and the color of his mood is a choice column and so we go over here to data and then remember i always use a little trick here to say custom columns and so it just shows me the columns i created instead of all that other hot garbage and so we can see that you know we've got some different ones for dogs activities color of his mood we got some of these and then the toy these are actual lookups to a different entity and so a couple of them are penguins a couple of tennis balls so we've got enough sample data here to do some querying which a reminder when you're doing this type of testing trying to figure these things out use small data sets you don't need to return 10 000 items to prove whether or not this works it just takes longer some use small data sets so you can fail fast and then get back and figure it out okay so with that so let's go to columns so the first one i actually want to filter by we're going to start easy is we're just going to filter by the primary one so dog's activity so cr662 so i'm going to copy this name because i'm going to show you where i started right so if we copy that and if you look you should see that i got a bunch of these they're named flow tests so we're going to do this and we should get four results if i type in flow test like that so go to flow let's create a new flow anytime i'm testing working with flow i just do an instant cloud flow and then manually trigger it and then uh video odata dataverse filtering i don't know something like that and we'll create this and so the reason i use manually triggers because i'm just trying to understand this one thing how do i filter the data i'll go put that into the bigger flow that i needed into later but doing it this way it makes iterative testing so much faster instead of trying to do it in line in a big flow so now we're going to do some new step and then under microsoft dataverse we're going to say list rows so if you just list rows right you go to table name and so we can choose a chewy tracker and i think we can search even yep so chewy so there's the chewy trackers so if we did this this would return all the rows up to the uh pagination limits where i think it's 5000 or something i don't know we're not worried about that today but if you go down here to show advanced options i can do things like turn it down but more specifically what i want is to filter rows so when you go to filter rows with this this is the same like with sharepoint as well we go to filter the data we use an odata query and so in his most simple form is going to be column name and it's the fancy column name right i said cr 662 underscore dogs activity which we just copied from over here right dogs activity right there so it's going to be that column name and then instead of an equal sign is eq and then in flow remember your text is a single quote so single quote and then what do we say we said flow test and close our signal quote so just like that that is going to be when you're filtering a simple column and so this could be like a number column um i think date columns work pretty straightforward text columns we're about to show here a lot of your less complex columns they just work this way so then what i'm going to do as well just make my life easier i'm going to do another new step and we're going to do a compose all i can do in powerapps where i throw labels on the screen flows i throw composers on the screen and so then here i'm just going to say i want to use the expression called length this is the same as a powerapps count rows so you do length and then now you just make sure you kind of click out or get that to go away put the cursor back in here go to dynamic content and we want to click the rows or we want to count the number of values so this will be the number of rows that come back total and so in our case we're looking for a four so just a great way to validate you're getting back the nut right amount of data there you go we'll hit save cool and now we'll hit test and then we want to manually perform it so we'll click test we'll say run the flow and finally done for the simple portion of the process that's a lot of clicking there you go it ran faster i can make fun of it and so something happened compose said four yay so that tells us that that simple one works right so that's for your simple columns right we didn't come here to do simple columns we came here to do complex columns so now let's go and try to do our friend let's go down here to the toy so your first instinct is we'll shane now let's grab that and search for the toy equals penguin that does not work i'm not even going to bother doing a test of it it doesn't it doesn't work it just explodes it has no idea what you're talking about so we're going to do is we're going to talk about the troubleshooting step so how do i figure out well what does it want if it doesn't want the actual name of the column which would make so much more sense so what you're going to do is when you have a good run like this so it could have been a list rose where you got back all the records or you got back you know a subset whatever it doesn't matter you want to see what does the data look like when it comes back so in the test interface here when you do list rows it says outputs it says show raw outputs so flow actually gave us this cute new little interface that's brand new i just saw this there today now if you hit control f just like that it brought up the browser's fine me don't want that so x out of this one make sure you're inside this box now do a ctrl f see how the find is right here inside right it looks a little bit different because it's one driven by the flow interface so now if we start searching for p e and q oh so there is no penguin in here so right my filter data set none of the test ones had a pink one all right no big deal penguins what i'm looking for i could probably find other ones let's just go find all of them so we're going to edit this flow real quick we're going to go back to list rows we're going to expand this out and we're going to make sure now that we don't have anything in filter rows so delete out any oh data now rerun it so test automatically boom boom boom and after what felt like forever for me it came in it gave me a warning that i got back all the rows oh no who cares um but notice to compose i got nine this is why i told you to use small data sets right i got nine back when i got the whole data set but this is how i can iteratively test and find what i want faster so show raw outputs again put the cursor in here again control fine and now we search for penguin which is his toy and so in here what you're going to see you're going to see a lot of stuff let's kind of pull this up a bit so in here you go away we have penguin and so you can see that it's like hey i am this column name oh clicking causes bad things to happen i am this column name equals penguin what in the world is that so this portion is what we call a virtual column so notice that there's a bunch of these so there's the formatted value there's the i don't know navigation project the logical name the odata type and the actual value so in reality uh dataverse is storing the grid for all of those but we don't want to use the grid we want to use penguin so how do we do that well we can't sorry spoiler alert so what we're going to have to do we can't use any of these things on the right of the at signs so we've got to use the raw one so we're going to have to do this control copy equals this control copy again so now what we would do is we'd go back over here we could edit we'd say filter by i'm going to use ctrl v if you haven't used this before this is windows 10 and so you have the clipboard history it's a cool feature and so in here i can say underscore cr this is what i didn't understand the underscore so saying the virtual column name eq single quote windows v again paste in that grid and single v or single quote like that so notice that underscore cr6222 if we go back over here and we went back to columns we're going to see that the toy column right this cr662 underscore the two to the toy easy for me to say and this they have almost nothing to do with each other i mean they do you see the similarities but you had to go and find the virtual version of it so now if we do a test test test save and test i just sit here staring into the camera waiting on this thing to finish oh there he goes oh so compose we got two records and we know if we go back over to our data set and went to data and all the columns penguin we got two records so that is how you filter you've got to go get that nasty nasty one right but so it was underscore cr662 underscore the toy underscore value eq and the grid there is no way to filter for ping one directly i've tried all the different iterations combinations that i could think of if you know something i don't put it below but i couldn't figure out a way to do it for ping one i had to go find out that penguin is the grid right here so 0 6 ends with 31. now in the case of a lookup column it is worth noting that if we just go back over right so this is doing a lookup where does the toy do right so we go to relationships the toy is looking up in called chewy's toys so if we went to the table called chewie's toys right here and then we went to data and then we said show all columns we could see that penguin is right there well it's not going to expand sometimes it does sometimes it doesn't but so this right here is the same one so this is the grid for his record so that's what's actually matching so if you wanted to go get it directly this way instead of getting it the way we just did that'd be fine but you either way you have to go the way we did to make sure that you're getting the right virtual name i'd like to tell you they're always the same maybe they're always the same but i if i know anything about dataverse i know that they're going to find a reason for one not to be the same you're going to yell at me so just go get it the wrong way okay so the other one i want to talk about just for a second if we go here to show more so our show raw outputs again so if we do control find inside here again and search for color so color of his mood so we can see that red is equal to 2 1 3 blah blah blah so if i copy this and copy very very delicately copy that if we go back and edit if we want to find all of the red ones then we could say where color of his mood eq windows v that number right so that in the case of choices or what they used to call option sets it uses that type of it uses integers right i don't think you actually don't even want the single quotes around it if i remember right let's try it so this should give me back all the ones where it equals red so test test i'm doing the awkward thing where i'm staring into the camera again just waiting on the finish thank goodness it did said have to make small talk and so list rows we got one if we go back over here to our data set again chewie's chewy tracker data and then custom columns there's only one person with red perfect so that particular interface right so option or choices option sets they used to call them those are going to be the integer once again there's no way to filter directly on red you've got to go get the thing go get the thing go get the integer that ties to your option now it is technically possible to get those from a really long roundabout way let's see if i can let's see if i can do it i'll be brave so if we go here and we go to choices and then i'm going to control find for color color of his mood options and so then there's red and i think if we do like view more so there you go it knows it is 213 million 10 000. right why they put commas there who knows but but that would be a way if you want to go directly fine red green blue those type of things now you will also notice if we go back i guess i should have showed you this before i left oh x out of some stuff shane x x and then if we go back over here to chewie's tracker you will notice that color of his mood so 6r662 color is mood notice that one is not a virtual um column so that one does use the same name but you need the fancy numbers are you confused yet me too that's why i made this video so we could all watch it back together multiple times but this is how you filter on odata filter dataverse when it is complex data so if you need help with any of this right remember we've got different options powerapps 9-1-1 we have consulting options we have training classes you go to training.powerapps911.com you can download this video you can take a full class we got lots of ways to help you you just got to reach out or of course if you just want a little quick little nudge leave a comment below and i try to respond to as many of those as possible i've i've given up on all that i get too many i finally switched to as many as possible so cool all right well that i'm gonna say thanks and have a great day before you go be sure to click on the subscribe button over here so that way you'll be notified when new videos come out if you need any help or you want to work together whether your problem is big or small check us out at powerapps911 we do it all at rhymes or if you're looking for more formal training offerings we have those linked up here somewhere so check them out thanks and have a great day

Channel: Shane Young

Views: 41,106

Keywords: Shane Young, powerapps911, Power Automate Flow odata filter dataverse lookup and choice columns, dataverse odata filter, flow dataverse, flow dataverse list rows, flow dataverse column filter, power automate dataverse connector, power automate dataverse column filter, power automate dataverse lookup, power automate dataverse search rows, power automate dataverse list rows, power automate dataverse filter, power automate dataverse lookup column, power automate dataverse row filter

Id: kftk2buVG2Y

Length: 15min 17sec (917 seconds)

Published: Mon Jun 14 2021