Update Choice and Lookup Fields in Dataverse via Power Automate #Choice #LookUp #Dataverse

hi there folks have you ever struggled with updating lookup or Choice values in dataverse using power automate how do you find the guid how do you get the unique IDs what about the plural table names Etc well in today's demonstration I show you how to handle all of that dynamically so you don't need to update your flows in the future if you update your choices or your lookups in data verse this flow will handle all so if you haven't already make sure you like And subscribe and without further Ado let's jump into the demonstration so I'm going to kick things off from my dataverse table I have a new table and within this table I have a couple of columns one of which is a color which is a multi-choice and I can choose from one of red blue and green I then also have a lookup which allows me to choose my favorite sport but that relies on another table if we have a look at our columns and we can go and have a look at our color into edit here we can see we have the labels for our choices the red red the blue and the green but we also have the values these lovely numbers now in order for us to go and update or add choices to a record in data verse using power automate we need to know what those Choice values are but we might often just have the labels the strings so I'm going to show you a rather efficient and dynamic way to convert those strings into those values and if you were to add new choices this flow would also deal with those new choices too now as well as looking at these choices if we go and have a look at my favorite sport column and go into edit we can see it's based on a related table so not only are we looking at choices we're going to have a look at a lookup and if I jump across onto this other tab I have my sports table we can see each of those records has a guid again which will need in order to create that relationship and we have our friendly or text values the cricket the football and the rugby so once more I'll show you how to convert those strings of of cricket football and rugby into the guids which are needed when we update a record in dataverse for a lookup now if we jump across onto power automate I'm going to put this flow into test mode and what it should do is it should ask me what my favorite colors are one or many and then I will also ask me what my favorite sport is so my favorite color I'm going to say is blue and green and then when it comes my favorite sport I'll say that it's cricket and we'll go ahead and run that and we can have a quick look at the history and then after we've had a look at the history I'll show you how we can build this end to end so what I'd like to call out now in both of the Scopes I have a compose and if we pop that open and have a look at the raw input you can see I have both the labels and the values in an object and it's this Json structure that I've created using the dynamic values from the list rows of that option set and this is what's going to make our life so so much easier when trying to convert these strings or the labels into the values or the IDS that we need in order to create these records similarly if I scroll down here and have a look at the second scope I have the lookup objects and if I pop that open and have a look at the raw input you can see I have the Cricut rugby and football and this time the guids again I've constructed an object which allows us to dynamically get those labels converted into guids by taking advantage of the fact that we can write expressions that uses the label or the string in order to retrieve the guid and it's also worth mentioning at this point that both of those objects are Dynamic based on list rows so if I click on the list rows here I'm looking up those labels and guids from that specific entity and again if I scroll up to the top here I have a list rows which is looking at the string Maps entity which again allows me to get those Choice values back dynamically so if at any point you're to add in a new Choice value or a new record in your lookup table those things will update automatically in your power automate Flow by constructing a new object so if I was to pop open the select at the end here and have a look at the raw input we can see we have the values blue and green which is what I selected at the beginning when I run the flow but if I have a look at the output you can see I have those unique values those IDs that are needed in order order to update those Choice values when I create a new record scrolling down to the very bottom if I have a look at the sport lookup values again look at the raw input we can see I have the guid as the lookup only requires one value all I'm doing is returning the guid based on that initial string that I've selected at the start of my flow if I finally look at add a new row and we have a look at the raw inputs we can see we have the color which is these two color IDs or color values separated by a comma and then the favorite sport is based on the construction of this lovely string here which includes the lookup table name as a plural and then in Brackets the guid which would retrieve dynamically based on the selection that was made at the start of the flow just to double check this if I jump across onto my table we can see the new row that was added there has the blue and green selection and my favorite sport it if I jump over into the flow once more and just quickly run another test I'll go ahead and select all of those colors I'll go and choose rugby as my favorite sport this time around hit done the flow as you can see completes very quickly if I jump across onto my table I can do a quick refresh and that new record is being created with the three colors red blue and green and my favorite sport of rugby so I'm going to start off the build of this flow from the classic designer but then I'll move across onto the new design designer and for anyone that wants to see the final flow in the classic designer then I'll do that at the very end now you'll be wondering why I'm starting the classic for the purpose of this demo I had two inputs I had the color and I also had the sport and unfortunately on the new designer you can't currently specify a multi- select list of options which is what I require really for this demonstration so I'm going to have my red green and blue as various options and then when it comes to the sport I'm just going to add a drop- down list of options so you can only select one so those three Sports the cricket rugby and football now the fact I'm using a manual trigger here to do this is kind of irrelevant it's all about passing across these labels Into Your solution but this is the easiest way for me to demo it as part of this video so in order for me to save I do need to add another action so I'm just going to add add in a compose we'll put in the number one two 3 I can save that and then I'm going to flick across to the new designer and get started with building this flow so with me safely into the new designer we can see I have that manual trigger with those two input parameters of color and Sport and the feature I'm I'm referencing if you look at the options available in the dropdown I just have a delete but if I go into the code view we can see I have those options that I set in the classic designer for red green and blue and also for Cricut rugby football now all we need to do is get started with building out this flow so the first thing I'll do is just delete this compos CU it's not needed right now and then I'm going to add in a list rows because what I'd like to do is get a list of all of those choices that are available for my colors so the table that I want to go into is called string Maps so if I can start typing in string Maps there we go and when it comes to parameters we want to ultimately filter based on the name of our column so if I was to run things as they are right now we'd end up with all of the choices for your various columns that you've created but what I'm going to do here is put in place a filter and that filter is based on the attribute name equaling and then in single quotes we put the name of the attribute so in my case I have a solution CRC 93 and then the name of my column is color if at any point you're unsure about these things if we jump across onto the table we can have a look at the columns we can have a look at the color and we can see there that the name is CRC 93c color and that's what I've provided in my flow so with that in place we're now going to retrieve all of the choices but we're actually going to pull back far more information than we require which is why we'll use the next property which is to select columns and with this I can put in a comma separated list of columns that I want to retrieve I want the attribute value and I also want the value so that will return back both the string and the ID as part of that lookup so at this point I'll save and test and we'll have a look at the history so with the flow successfully run if we have a look at the raw output here on the right hand side we can see in the value array that's been returned I have the attribute values so these lovely numbers and I also have the labels or the values as they being referred to here the blue and the red and the green and we now want to transform that data into an object and you'll see me using this method in quite a few of my different videos so if we jump back into edit this flow the first thing I'm going to do is I'm going to insert a select action and it's here that we're going to transform this data into an object or at least begin to so we'll take the value from the list rows and in the map we're actually going to change this into text mode and then write an expression so the aim of the expression is actually to concatenate both of those two values to construct a key value pair so we're going to type in the expression concat and then begin to build out a key value that are encapsulated in double quotes with a semicolon between it so first off we have the double quote followed by a comma followed by the expression to get our Dynamic value which is going to be our value from from the list rows then we're going to have another double quote a semicolon and a double quote and this Begins the value of our key value pair I can then put a comma at the end here and insert another expression to get our attribute value so I can type in attribute value here and then we want to finish that off with another comma to end our string with another double quote so that'll give us our key value pair as a string so next up is to turn this array of strings into adjacent object and I do that by adding in a compose action and writing another expression so into the Expression Builder here and what I'm going to do is I'm going to again use concat but also join and we'll start from the middle and work our way out so the first thing is to join the values or the strings from our select so I'll go into Dynamic content I'll choose the out put I want to join each of those strings based on a comma with them then joined what I need to do is I need to add an opening and closing curly bracket to turn that into adjacent object so this is where we bring in concat and I can add in a squiggly bracket at the beginning and if I go all the way to the end I can add a squiggly bracket at the end like so we still have a string at this point so what I need to do is I need to verify or pars it by using the Json expression and I put an opening and closing bracket there and we have our complete expression so this will join the strings that are in the select with a comma we'll then wrap that whole string in the open and closing squiggly brackets and then finally we'll parse or validate it using the Json expression if I add that we now have our object now because I've chosen to build a multi-choice I'm using a select action here in order to replace all of the values that come back in the array from our trigger if you just had a single Choice then you could just write an expression so I'll go with the select and I'll show you what you would do as well if you just had a single value so add in an action it's going to be our select again and the input to the select is going to be the dynamic value for our color which happens to be an array because we're dealing with a multi-choice and then what I'm using in the map is another text expression and what I'm relying on here is the fact that I can call the values from our composed which is an object based on the string so the red the blue the green so if I go and insert the dynamic content for the output of the compose above if I was to put in the question mark and the square brackets and the single quotes and put red that would return the red value it would give me that number that we're looking for but because we're doing this dynamically and we're using a select if I put in the expression item with the open close brackets this will Loop through the one or many colors that have been Cho Chosen and it will insert those strings into this expression and it will retrieve those ID numbers dynamically from the compose so if you were just dealing with one choice you would construct the expression outputs of compose and then in here in the square brackets rather than inserting item you would insert your string and that would be inserted using the dynamic expression that you have for whatever value and that would then allow you to retrieve that ID so I'll click on ADD and I think at this point it's worth saving in testing to see what the output looks like so the flow in test mode if I go and choose a couple of colors we'll choose red and blue I'll have to choose a sport just in order to run the flow we've not built that bit yet so with the flow run successfully if we pop open the select we can see the raw input which includes those attribute values and the values if we look at the output remember we used the concat to bring together both the values so the red the green and the blue with their numbers which are also called values getting very confusing here if we look at the compose because we've then used that Jason and again concat and join we now have an object which is the magic trick that's going to make your life a hell of a lot easier and if I move into that select what we can see is the input the red and the blue and the output are those two numbers because we've been able to retrieve the values for Red and Blue by using this object that we created in the action above so when it comes to the lookup it's pretty much the same idea we need to go and add in another list rows and of course we want that lookup to be based on our lookup table in which case mine is called a sports table for for ease of finding it and again I want to have a look at some of these Advanced properties only one this time but I want to just keep that data nice and simple I'm going to reduce the amount by selecting specific columns which are the ID and the value now I know what these values are for me they are CRC 93 and then a sports table ID and also CRC 93 sport if you're wondering where I got these from if we jump across onto our Sports table we have a look at these columns I'm trying to get the sports table ID so CRC a sports table ID it's the unique identifier as in that guid and then I also want the name of the sport which is our primary name column column in this case which is CRC 93 sport so with those two in place I've greatly simplified the data that's coming back from the list rows and very much like before if I go and add in an action I want to add in a select and we'll do exactly the same as we did before we will add in the value from our list rows one this time put it into text mode jump into our Expression Builder and build out that concat expression so again we'll start with the opening quotes we then need to construct an expression and this is going to be based on the name of the sport so in this case the CRC 93 sport this is why it's quite useful for you to be able to look up those values or the names of those columns within your t table and then we can put our comma our single quotes again our double quotes our semicolon or colon sorry and double quotes and then another comma which allows us to put on our second Dynamic value which in this case is my CRC 93 a sports table ID finishing it off with another comma single quot quot and another double quote so that will create our string we can add that in there and now we need to of course turn it into an object and we'll do that by using a compose so for the compose into the expression editor we are going to do exactly the same as before we're going to do a join that join is going to be based on the output of our select we're joining on a comma and then we're going to concat on the the start and on the end those squiggly brackets which we can put in there again I'll go to the end put in a comma and put in our squiggly brackets and finally just to make sure it's valid Json we wrap it in the expression Json with a closing bracket at the end there and hit add so in order to get the guid for this particular lookup value again I'm going to add in an expression this time a compose and in that compose I'm going to write an expression similar to the one I demonstrated in the C remember there's only one value here I'm going to go to the dynamic content I'm going to choose that compose and then a question mark square brackets if I was to put the word Cricut in there it would of course retrieve the Cricut guid but because that is dynamic we can go into our Dynamic content here and choose the value for our sport which we have here so by supplying that sport Dynamic value into this expression we'll look in the object that we've created here in this compose and then we'll retrieve back the guid and we can add that and we're all set up now to go ahead and create a new record with both that lookup and the multiple choices but before we do that let's save and test so I'll go ahead and pick a couple of colors but more importantly I'll pick football as my favorite sport we'll run that flow we've used that select action to turn the input from the list rows into output like so with the name of the sport and the guid as a string in the compose we then convert that into an object so it's now ready to dynamically select those values based on the name of the sport the cricket rugby and football and if we have a look at the football guid it's 760 if we have a look at the compose because I chose football we can see the output here begins with 760 so we've managed to dynamically get back that guid based on the selection that I made at the start of the flow now if I was to go back back into my sports table and add in horse riding and let that save we can see we got a completely different guid but because this flow is now Dynamic if I go and jump to the bottom here and add in a compose I can go and type in an expression that will get the guid for horse and the way I do that is I would use the output of my compos one I'm really regretting having not rename these now but if I put horse um riding in the middle here and add that in we should get back the guid for horse riding in the output and with that flow now run if I pop open in that compose 3 we can see we have that guid beginning with 380 based on horse and if we look at that compose one here in the Raw input we can see we now have horse riding so that object is completely Dynamic you never have to come back and update that flow again it will retrieve all those guids and all of those Choice values dynamically so when it comes to writing those values let's go and create a brand new record see how we write both a choice and a look up to a new record to add a row we'll choose our table which is my new table I need to put in a name so this is uh Damian's test and then if we look at the parameters I want to update the color but I also want to update the favorite sport now when it comes to color I could of course make a selection here it is based on the values but you'll find that if you type in the word blue or Supply the the string blue dynamically you'll get an error message so what I need to do here is change this to a custom value and I'll use an expression now because I have multi-choice I need to join together those IDs using a comma and so with join I can choose the dynamic output from our first select action so here we have the output and then if I insert a comma in single quotes that will join the one or many of the matches that we get back based on a comma and we can hit add the favorite sport is slightly different in that we need to reference both the lookup table and the guid and this is what caught me out when I was trying to build this so this is based on the sports table plural name and if I go back to my tables and have a look for my sports table we can see I've got CRC as Sports table and you can actually go and grab that via tools here and and copy The Logical name so if I jump back into my flow I can paste that in I've got my CRC a sports table it should be plural so I need to put an S on the end there and then in the brackets all I need to do is insert the guid and the guid is based on this compose here so if I go into my Dynamic content it's compose to there it is that is the guid and that is all there is to it so if I save this and test it we can go and make a selection of both colors and our favorite sport and we'll find we have a new record created based on the dynamic content that we've retrieved in our flow so I'll choose a favorite color of red and green this time and when it comes to the sport let's go for Cricut so red green and cricet will run this flow everything is completed as expected if we have a look at the raw input you can see I have those colors separated by a comma you can also see that I have my favorite sport with the guid for that sport there it's making it difficult for me to highlight but there you go the the guid and if we go and jump across onto our table we can see that brand new record that's been created Damian test we have red and green selected and Cricket so if we have a look at the flow that I've now tidied up a little bit done some naming so my select for the option set value has been renamed here and I've also given these some names so that's some some more meaning behind them but the process is ultimately the same we're taking the data from the different tables we are transforming that data into an object by using select which converts those values into a string then converts them into an object by concatenating and then we can call those values dynamically either by using just a simple expression to get that value based on a dynamic value or by using a select which will get multiple values and then we can join those values when we come to adding that new record in the add a row action now for those that still like or want to see the flow in classic I did promise I would show you it at the very end so here we are here's the classic flow we have the manual trigger with our color and our sport as the options I have my first scope which has the list rows if we have a look at some of those properties here I've selected columns based on the attribute value and value I have the filter row which is based on the attribute name name equaling the name of the column that we're looking to get those Choice values for in the select I have the body value from the action above the concat which we can see here hence why we need this new expression editor the compose which is based on that join the concatenation and Json and then our final select which is based on the color which is our input from our trigger and then the output which is based on each of those colors that have been selected the second scope very similar again list rows based on the sports table which is our lookup table again I'm selecting specific columns just to simplify things but it helps to understand when you build out the expressions in the next part of the solution in that select we again use the value but it's in the concat that we're relying on these column names so you can see I have the CRC sport there and if I nudge Along on this expression we also have the a sports table ID so it's worth understanding your data so that you can build out these Expressions these will match in the lookup again same as before the J Json with a concat and the join and then because this is only one value it's just a lookup we're using the expression the outputs of our lookup object here and then in the square brackets the dynamic value in this case from our trigger body the add a new row you can see I have um in in this case put a random number at the end of the the record that I'm creating for that required field the color is based on a join so we're joining those IDs with a comma and then the favorite sport is based on that table plural name and the output of this compose above which is our guid so for the benefit of doubt I'm going to go an add in horse riding at the top here because of course I added that into our table and let's go and also add in another color as well we can do that from our columns we can go into our color into edit and add in a new Choice which I'll call pink and we can put in a random value as well here just for the fun of it so 1 two 3 I'll hit save now of course I need to add pink in as an option the same would apply to however you're capturing those strings but I do not need to change the flow anywhere else we can put that into test I can choose my colors which I'll go for pink and blue my favorite sport I'll say is horse riding and if we run that we can see that hopefully the flow will run as expected it is added a new row if we have a look at that option set and have a look at this compose we can see that pink is now parted with that new ID that I set and also if I have a look at the lookup for those new objects horse riding of course is through from earlier but more importantly if I jump across onto this table we can see that I have that selection for blue and pink and horse riding so the only thing to note is when I made this change to add in the new color I need to hit publish for that new choice to become available in the string Maps table and that's it that is the end of my demonstration so I hope you've learned Lots there again I like to use select Etc but I myself were struggling with the look up and choice and could see hopefully how things could be simplified so if this has helped you please make sure you let me know in the comments if you haven't already make sure you like And subscribe and I hope to see you again sometime soon thanks for watching

Channel: DamoBird365

Views: 1,084

Keywords:

Id: sVRT1-VY8uI

Length: 29min 3sec (1743 seconds)

Published: Sat Feb 03 2024