How To: Work with Dataverse Lookup columns in Power Automate flows

hi everyone it's Nick with bright ideas agency here this video is just going to be a couple of pointers on how to deal with uh related tables when using dataverse and power automate I've been deep in a project this week where I have been working with some of this stuff and reminding myself of how to to do some of these things and there's a couple of idiosyncrasies that I thought it would be useful just to make a quick video on with some tips on how you can work with related tables and lookup columns when you are doing dataverse actions in power automate so enjoy okay so I've just jumped into one of my Dev environment accounts here that has a dataverse database available and inside a new solution I've just set up a couple of really straightforward tables just to demonstrate interacting with dataverse so I've got one employee table which has got the name of employee their role and then a look up to a location table and then my location table has a bunch of other information the name of the location the city the country and the capacity so if I jump over to power ultimate I can just create a really simple flow here just with a list rows I'm limiting this list rows to one row back and I'm looking at the employees table and let's just take a look at what we get out so for ease of reading I've pasted this response back over to vs code and you can see you get a very standard response that you will be familiar with if you've done any work with dataverse or SharePoint or anything else but you can see we get a lot more information back than what we showed you in that table to begin with and really there are two types of information that we're getting back we're getting back these columns which are the ones that we have created in the table so for example the name the role and then we're getting these columns with underscores and these are the related tables so for example for my location table I have a value here which is the grid of the related location and that's all the information that I've got back here so in many circumstances you're going to want to grab information related to the table that you're querying so one approach that you might take now that you've got your rows out of dataverse for your employees is to come back in here and create another action we can have a dataverse action and that can be get row by ID and we're going to choose our location table and then the ID because we've already grabbed this information from um from the employees table we can actually use this location value and this puts it in an applies to each because always that value is a um an array that we're getting back from the list row so even though we've just limited this to one row right now this could be 5 000 rows so um Power ultimate helpfully puts this in apply to each and so what we're going to do here is just exactly the same I'm going to put a a compose action in here and I'm just going to grab the output from my get a row by ID and here we go I've pasted this response back into Visual Studio code again so that we can take a look at it and you can see we've got the city the name the country Etc there but this is actually a very inefficient way of laying this out because you get a lot more steps in power automate than you actually need and you're taking a lot more actions on the dataverse database than you need so what we're going to do is come back over to our original output and we're going to look through this with this location and what we want to find is this Associated navigation property for the location the table name and you want the navigation property one because the case does actually make a difference we're going to copy this now jumping back over to power automate let's edit this we're going to get rid of this step here and we're going to come into our list rows and what we're going to do here is we're going to use this expand query option and we're just going to dump that table name into expand query and what this is going to do is it's going to grab the data related to the row that I'm getting back from the related table so let's just run this so here we are back in vs code and we've got the response to that last query to the dataverse database and we can see up here at the top we've got basically the same information that we had on our first query but if we keep scrolling down you can see that we now have an object which is the location table and all of the information from the location table has been brought over into our query our list Rose query for the employee table so this is a really useful way of getting all the information that you may need um without doing two actions in power automate however you might be in a situation where all that you need is actually say the country so let's copy the name of that country field and back in power ultimate we're going to edit this again we're going to come down here to our expand query and we're going to add some brackets here and then we're going to say select equals and we're going to put the name of the field that we want there so now jumping back over to vs code again we've got our output from our query and if we scroll down you can see that I still have this object for location here but the only data that I'm getting back there's a couple of extra things here but the the main column that I'm getting back is country so it's possible to tailor your uh your query in such a way that you're just getting back the related data that you want so you might also be interested in how do lookups impact adding new data into your database so I've got a add new row here in power automate and I'm just going to add an employee this employee is going to be called Nick and then I'm going to put a location in here and I want the location to be my headquarters I know that's one of the options that I've got in my location table and you can see I've ended up with an error it is not managed to add in that uh table for me like Row for me um and that's because of this related table so how do you add in a related item you can see there isn't a drop down list here so if we jump back over to vs code we're going to take a look at the edit link that we have for our location you can see the location here is the one with the United Kingdom um uh location but we have an edit link here of the table and then the grid of the item so we're just going to copy this jump back over to power automate and we're going to put a slash and then we're going to put in that edit link that we just grabbed and let's save this test it and here we go our flow has run successfully and we've added our item into our dataverse database so when you're dealing with adding in rows that include those lookups you need to make sure that you know the table that that uh is related to and the grid of the item that you are looking to add in and then that gives you all the information you need to be successful with dealing with adding lookups into your dataverse using power automate so hopefully this has given you a couple of pointers on some things that may be a little bit confusing when it comes to using dataverse with power automate if it has been useful to you then please do give the video a like and do subscribe to the channel to see more content like this until next time bye

Channel: Bright Ideas Agency

Views: 5,712

Keywords: power apps, power automate, dataverse, microsoft power apps, power automate dataverse lookup, power automate dataverse filter, dataverse powerapps tutorial, dataverse power apps, power automate ideas, microsoft flow, microsoft power automate, sharepoint lookup column, dataverse power apps connector, powerapps patch dataverse

Id: nlVz_ISDF2k

Length: 9min 35sec (575 seconds)

Published: Fri Dec 02 2022