Power automate Dataverse Lookup - How to write to the hard column

in today's show we're going to talk about databurst lookup columns and more specifically we're going to learn how to write to them using power automate which is about a million times harder than it really should be so it took me hours to figure it out hopefully this little quick 10 minute ish video will explain to you exactly how to do it so you don't have to spend days and weeks and hours figuring out should be fun should be fast but first here's our intro hi my name is shane young with powerapps 911. those guys and today we're going to dive into powerautomate and dataverse lookup columns keep in mind this will work for dataverse or dataverse for teams which we used to call cds or common data service all those things it's all the same this is going to be how to use flow to write to a lookup column which you're a shame that should be super easy which i agree it should be but it is like rocket science so we're going to go through how to do it and more importantly because a lot of people are like oh i know how to do it i saw this blog well those blogs always kind of skip all the hard stuff they're like oh just use this really easy column so i'm going to show you how to get all the specifics that you need from a very nasty place but it should be fun let's just switch over to my desktop and daily okay so over here my desktop and making sure that there's no magic there's no confusion because like i said it took me hours of reading and figuring out and deciphering what people meant i want to show you all the pieces so what we're going to do first is we're going to go over here to dataverse so data and tables and i want to create a new uh entity or table as they now call them so we're going to say new table we're going to give it a display name it does not matter but chewie's routine sure something like that we're going to take the defaults so we created this entity or table and now we're just going to add two columns so i can walk you through the the hard part right you know how to write this stuff already so we're going to add a lookup column here and we're going to call this dog walker so this is the person whose job will be to walk chewie and for this one we're going to do a look up wherever lookups are right here and then we're going to do from the system table users wherever users is right because this is a very common one you want to relate you know things to the project manager sales person that type of thing so this is a very common scenario and this one's not terrible to do so i want to kind of start with this one so we're going to do that look up then we're going to add a second column though this is where we get a little gnarlier and we're going to look up for um favorite toy and so in my data set right we're doing another lookup i have a table i've already created called chewie's toys somewhere in here chewie's toys it's got an apostrophe it's got s's this is about as nasty of a table name as i could find and this is why i had so many problems so i wanted to use this one as well to kind of walk you guys to it so we're done we say save the table all right and what after it seemed like it forever was probably only 30 seconds it is created my table for me now something to note if you're like whoa whoa shane go explain all the lookup columns and what what are they why do i care about all these things if you don't understand any of that no big deal i've got a video up there um that i've already taught lookup columns like how to create them how they're used how to how they make sense inside your powerapps i've already done that video so go watch that one if you've got questions on that this is just about using the power automate so there you go we've created this and if we just go over here like one of my tricks is i often just change this to custom we always have three columns that care about the name column which was a primary key or the primary name column and then favorite toy and dog walker both of which are look ups dog walker to our uh users entity easier and then favorite toy to the hardest entity i could find great we're done over here so now what i want you to do is we're going to jump over here to power automate and we're going to say i want to create a flow and we're going to create an instant cloud flow and we're going to manually trigger the flow create this flow now remember i use manually trigger because it's easy for me to demo and test from but everything i'm going to show you all the actions would be the same no matter how you are triggering the flow one of the most common ones right people like hey i want to import a bunch of data and make the relationships on the fly that's what drove me to write this video the first time so manually trigger flow no big deal new step the first thing we need to do if we jump straight to data version like add a new row let's just go show you that and so if we choose um chewy something i forget what i called it already honestly so chewie's routines so we choose that now it'll show us here the different fields so there's his name field and then under show advanced options it's like dog walker users and favorite toy chewie's toys and some weird parentheses notice that unlike a sharepoint lookup column which i covered earlier in the week there are no drop downs there is no way to pick any values it doesn't even tell you what it wants it's like give me something all right so i've done the homework and knowing the way that dataverse worked i knew it wanted the grid and so i've tried just to put in the grid straight up that does not work and i've tried to put in things like uh you know users and then gwy like there's a lot of weird combinations you can come up with so really what i want to do before we worry about patching or patching this updating this is i actually want to delete this and we want to go and get we need we need the grid we need the id of a user so i'm going to start here i'm going to new step dataverse and i've i could cheat and get the row by id before i had the id i wouldn't use the id to get the id so we're going to do is we're going to say we want to list rows in the users entity because that's where the first one is going to come from the easier one right so you go down here and there's users and then what you want to do once this is here is you're actually going to uh filter the rows so we don't want all the users back we just want the specific user that we want to set as a dog walker so we can get their user id in the process so to do that what we're going to do is we're going to do an odata query now to do an o data query you have to know the column name and the underlying system format of that so we're going to jump back over here we're going to go back to tables remember i told you i'm showing you everything there we know shortcuts and we'll go to the users entity and then it's like oh nothing to show remember we did the uh the filter earlier there's no custom settings here so just remove the filter oh there's all the columns and so then now down here i'm going to use the email address which is i believe called preferred email uh so there's preferred email and there's primary email i'm going to use primary email so what i'm really telling myself though i can't do an odata query for primary email i need this fancy name right here which is internal email address so i'm gonna copy that go back over here and say filter internal email address equals and then it's text so it's gonna be single quote shane at powerapps911.com just like that so that is the way to filter it's an odata filter for just my record awesome possum so that would get back that now list rows will return data in a table we can't use table data without getting all those weird apply to each hits i just want that record and unfortunately there's not a lookup function in power automate like there is in power apps but what i did discover today i actually didn't know this until this morning if we do compose there is actually a first function i mean i kind of knew but i never used it so i used it for the first time this morning so if you go right here and we go over and we say expressions kind of scroll up we'll say i want to do the first function which will get the first record from a array or table which was what this spit out so that we would be having just a single record so now if you go over here you want to use from your list rows action the value not the body the value and if you plug that in it just put it between the parentheses i don't have to understand what happened i don't want to understand what happened so we go to the end here so that gives you the whole row i don't want the whole row i just want that id so now i'll go back over here real quick and say all right the user id so down here it is called system user id this is the unique right it tells me right here this is the unique identifier so that's what i want so go back over here now that we've got this portion written against the whole row do a question mark do a square bracket do a single quote and then paste inside there the system user id now i'm going to grab this whole thing ctrl a ctrl c we're going to say okay and then right here i'm going to just add a comment so that way you guys can more easily see it so there you go so that's the formula that gets the system user id of the first record list rows we queried for one record we were expecting one we got one now we can use this as a singular thing so now that we've seen that now we can go down here to dataverse we can add a new row and if we say choose a table right we want to i think can i search for chewy there we go so then they're chewies chewy's routines and now that it's happy again and the name phil we're just going to put garbage where do literally put the word garbage why not and so now for the chewie's um for the dog walker this is now the magic you're waiting on right so what this is going to look like is this is forward slash and then this is going to be the uh plural entity name you're right so that can be very tricky to get and now in the case of users it's not too bad i i know what it is because all the blog posts love to use this one example because everyone knows that one but how do we figure it out that's what i had to learn so i could help you so what you're going to do is you're going to use this crazy url up here now notice when you look at this url though there's a couple things one this is my org id right i am org7c48 you are not so but if you go back over here the easiest way to get this is going to be to then right we're in here yay we're going to go up here and we're going to click on admin center so after a minute the admin center is going to load we're going to scroll all the way down to the bottom and you're going to find your environment you're in i'm currently in the shane default upgrade and we're over here right you can see as in the shane default upgrade so find your environment if you click in here on this page then now you have the environment url so you need this right you need your org7c481 or yours is different than mine once you've got that though you go to that url and then you append all this craziness which i will put down in below right so if you look in the description i will put this url down there for you and then inside this url what i need you to do is right here i need you to put the logical name of the table what i agree go back over here again go find your table and so right now we're trying to do users let's x out of that so if we scroll down here for user from this screen right the table screen not inside the users but the table screen yc user right here this is the logical name in this case it is system user you copy that so i hit control c you paste that up here it knows it's between single quotes and then you hit enter and this will return a bunch of stuff which you probably don't care about but what you're after is the entity set name and so you can see that this one is system users so that's a lot of work that's that was hard that that was difficult right but once you understand it you just plug it in right once you've got this url working you just pop in things and it's not so bad but the days i spent figuring out is rough so i'm going to copy system users finally back to our flow paste in that whatever you got from there so i did system users do a um open parenthesis close parenthesis and then in here in the middle goes the grid not not in parentheses i kept wanting to put it inside or not sorry not inside a single quote i kept wanting to put quotes around it because i always put quotes around it you do not so no single quotes is going to be forward slash system users open parentheses and then what we're going to do for right now to make our lives easier is that compose has the value we want so it's going to shove the compose in there so whatever gets put into here we'll go in here if it is correct it will create it so let's try this uh video update look up in dataverse so i remember what it was later we're going to give it a name we're going to hit save all right it's saved let's be brave we're going to hit test auto oh we can't do automatic we're going to do a manual test we'll hit test we'll say run the flow and then done because we haven't clicked the button enough times hey our flow ran successfully so now we can go back over here if we go back to our friend a chewie's routine and then if i go to data and then if i say show me custom columns dog walker shane young that was difficult okay now i want to right that was that was not fun i i get it now i want to do one with a custom entity not that it's the steps are any different but i want to show you why it gets a little a little gnarly and how to kind of cut it down a little bit so let's go back over here awesome we're going to edit this and so now we need to get the grid for so this is where we got the wood for shane we want to get the grid out of the toys so we're going to add an action dataverse we are going to list rows again and this time we're going to do it from chewie's toys right there and i said with the apostrophe and the s this was about as crazy one as i could come up with and we're going to have to filter again so we know how to do this but we're going to go through it again anyway we're we'll go back over here to tables we're gonna go to chewy's toys and we know that we want to filter based off of i think it's toy name there you go so there is my primary key this is my this is my primary name column this is where i want to search from so i'm going to copy that and then just show you the data real quick and you can see toy name penguin shoes squeaker tennis ball so we're going to search for penguin right my youngest kid loves penguins penguin so we'll go down here nope nope right here geez repeats so what are you going to do same thing again filter cr underscores or cr662 underscore toy name remember yours will be different that's why i'm showing you how to get all these instead of just baking it all and then we're going to put in single quotes and then ping one and so that's the odata query that will get that one record but it still comes out as a table but this time i'm going to show you we're not going to use compose we could do the same compose but really the compose was just a place for us to visualize the formula so why don't we just go down here and say all right favorite toys well it's a forward slash now what goes in here oh more things to figure out back over here tables chewie's toys grab this notice it's a completely different format this time you're going to go back though to this same url you don't have to think at all because you've already got it working go to system user and delete that out and just paste in my case cr662 underscore chewy toys hit enter and now you can see that this is the thing you had to try to figure out this is what took me days hours weeks months years to figure out and not really but it took a while copy that back over here you paste that in right there now you do a uh one of those one of those so we know that's the thing and here we need the grid you can write your expression right here all right we should be able to do this again we've done it before we're smart people what is it it's first then dynamic content list rows 2 value go to the end because we know that that's tight that's the whole row question mark uh square brackets single quote what goes in here i don't know so we'll go back over here again lots of back and forth and then we're going to go here to chewy's toys this time and we're going to find its unique identifier which is nine times out of a hundred nine times nine times out of whatever you know what i mean it's it should be the same as the table name so table name is chewy's choice there's a column called chewy's toys which is a unique identifier which is right here is what we need so chewy's toy id makes sense now we go back down here to fr flow and we say we just put that in the spot we will control a control c that again just so you can see it we'll say okay i'll just add the uh comment up here so it's on the screen but there you go so this is just showing you that you just compose we just did this to learn which is sometimes great even when you're doing this and testing for yourself because you want to visualize you're getting what you think you're getting but now if we did all this right we're going to say save and now we're going to say test run that last test so i don't have to press the button so many times cross my fingers oh it says success woo i was a little nervous not a lie okay and then if we go back over here to powerapps again what do we want to do we want to go to chewy's toys data and then oh no not chewie's toys we want to go to chewy's routine and we're going to go here to data and then show me the custom columns and garbage the second time has penguin waddle waddle waddle sorry it's a kid song anyway there you go we have done it so this video got way longer than i wanted but i'm telling you guys this was hours of painstaking work for me and then i waited three weeks to make this video and it took another half hour to remember how to do it again so i made this video as much for you as i made it for me because this thing this is just terrible i don't know how to put it right this is not good that this is how hard it is flow knows what you want what it wants but for some reason flow right why can't flow just tell us that this is the values that would go right here right they're showing us the entity name which seems helpful other than it's not so this api call that i found on somewhere this was the key for me this is how we were able to get to what we wanted cool all right if you have any questions comments leave them below remember to hit that subscribe button right especially these these dataverse videos don't get as many views so i don't make as many of them if you want to see more of this stuff i got to get more people viewing and active participating on these that show me there's demand because right now there's not a lot of demand for it so anyway i guess for that i'm just going to 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 i rhymed 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: 43,853

Keywords: Shane Young, powerapps911, PowerApps, Power Apps, power automate dataverse, power automate dataverse lookup, dataverse entity, dataverse entity reference, EntitySetName, flow lookup field, power automate dataverse filter, power automate dataverse actions, microsoft dataverse power automate

Id: EsRWGw-KYtA

Length: 20min 43sec (1243 seconds)

Published: Mon May 17 2021