Update Choice and Lookup Fields in Dataverse via Power Automate
Vložit
- čas přidán 25. 07. 2024
- Welcome to another game-changing tutorial on our Power Platform journey! In this video, I will tackle the traditionally challenging task of dynamically updating choice and lookup columns in Dataverse using Power Automate. Say goodbye to the cumbersome process of converting strings to IDs or GUIDs, as I unveil a dynamic and efficient method that simplifies the entire workflow.
[00:00:00] The purpose of the video
To show how to handle choice and lookup values in Dataverse using Power Automate
To make the flow dynamic and adaptable to changes in the choices or lookups
[00:00:31] The setup of the new table
Creating a new table with a name, a color, and a favorite sport column
The color column is a multi-choice with red, blue, and green options
The favorite sport column is a lookup based on another table called sports
[00:02:08] The flow for updating choice values
Using a manual trigger with two inputs: color and sport
Using a list rows action to get the choices for the color column
Using a select action to transform the data into an object
Using another select action to get the values for the selected colors
[00:15:08] The flow for updating lookup values
Using another list rows action to get the records from the sports table
Using another select action to transform the data into another object
Using a compose action to get the guid for the selected sport
Using the lookup table name and the guid to construct the lookup value
[00:20:59] The flow for creating a new record
Using an add row action to create a new record in the new table
Using the name, the color values, and the lookup value as parameters
Testing the flow with different inputs and verifying the results
🔧 Key Highlights:
🔄 Dynamic Updates: Explore the revolutionary approach to dynamically updating choice and lookup columns in Dataverse.
🤖 Power Automate Magic: Witness the power of automation as we streamline the process that was once considered intricate.
🚀 Solving the Puzzle: Demystify the complexities and challenges associated with traditional methods.
🎥 Step-by-Step Guide: Follow along as we guide you through each step, providing insights, tips, and optimizations.
📈 Performance Boost: Experience the efficiency of our dynamic method compared to conventional approaches.
📊 Topics Explored:
🔄 Dynamic Updates in Dataverse Columns
🤯 Overcoming Conversion Challenges
🚀 Simplifying Traditional Methods
🔧 Power Automate Configuration Tips
📈 Performance Metrics and Comparisons
👨💻 Join the Revolution:
Empower yourself with the knowledge to revolutionize your Dataverse workflows! Enhance your skills, boost efficiency, and optimize your Power Platform experience.
Don't forget to hit the like button, subscribe for more groundbreaking Power Platform tutorials, and ring the notification bell to stay ahead in the game. Let's redefine the way you work with Dataverse and Power Automate! 💡✨ #Dataverse #PowerAutomate #PowerPlatform #DynamicUpdates #EfficiencyHacks #DamoBird365
Feel free to support me by buying a virtual coffee at www.paypal.me/DamoBird365 ☕️ Buy me a coffee www.buymeacoffee.com/DamoBird365
#Choice #LookUp #Dataverse - Věda a technologie
Putting the plural "s" in logical name is so weird
I was stuck on it for 5+ hours. Thank you for this!
This is superb as always. Thanks Damien !
Super helpful, thanks!
Thanks, this really helped me out!
Lookups with that plural logical names is the bread and butter of working with dataverse tables. Such popular use case for patching option sets. Great demo Damo as always! 🤘PS: Love the hover effect for notes in the new designer. For demo purpose you can also put your expressions into the Notes field, So you won't cover the expression editor in classic with your profile.
Thanks Kristof 👌 I’ll have to check out the notes feature 👍
Awesome ❤
Thank you for the video, I need exactly what you are showing up. But I don't understand the use of the table 'String Maps' to get the choices of the 'choice column' colour in the table 'ANewTable' ... I miss something here and don't understand how to get these values
Wonderful Damian
Thank you 🙏
Very cool! On list rows 1, any reason you didn't use the Filter on the action (e.g. crg_name eq 'cricket')?
If I did that it would insert apply to each loops and everywhere I needed to access a lookup by string, I would need another list rows. This method means you can access your lookup values dynamically anywhere in the flow, as the values and ids are now in an object.
@@DamoBird365 I totally get you on the reusability of it elsewhere in the Flow, however I thought you only cared about a single sport record, hence the filter would get you that sport record, you use first()?['id'] and then you're good. I could be missing something :)
If it’s a single record, with a single lookup, you could use first, I don’t think you’ve missed anything 👍 you could check out my video on apply to each to learn more czcams.com/video/d99Rr5djcME/video.html
Hi Damian, How would you go about dynamically udpating the owner to match a lookup column. Eg a dataflow is populating a DV table which has a location column eg Store1 which gets applied to Location Lookup on import - We then need to update the owner to a team of the same name as the location so the Row level security works but I can't figure out the logic to make this happen? Any ideas?
You’ll want to patch to the Dataverse webapi? I’ve never done it before but there are pre reqs. learn.microsoft.com/en-us/power-platform/admin/update-record-owner
Great video Damian, im just wondering how would I go about bulk updating choice fields in dataverse table? Like I have two choices and I want to go from no to yes, without doing it manually?
How big is bulk? I don’t have a video on this one. You could ask on the forum?
Getting error as property attributename not exist, When I try to get choice value from list row action, filter ‘attributename eq ‘xyz’
try it without the filter, check the output history of the flow run and try and spot the correct attribute name. Otherwise please try the forum powerusers.microsoft.com/t5/Forums/ct-p/FL_Comm_Forums
Same issue for me also
As someone who's just starting out building Model-driven Apps with Dataverse, this was really interesting, thanks!
Is it always the case that you add an s to the table name to get the plural name, even when that would make no sense grammatically? If not, is there a way to retrieve the correct plural name dynamically in Power Automate?
It’s an area I’m learning in too in all honesty. Here is a good link learn.microsoft.com/en-us/power-apps/developer/data-platform/entity-metadata
@@DamoBird365 Thanks for the info - with that and some more googling I was able to use the "HTTP with Microsoft Entra ID (preauthorized)" connector's "Invoke an HTTP request" action to access the Entity Metadata - I found the url for the definition for the table in the Table Properties screen, under "Tools" then "API link to table definition". Then in the body of the response was the LogicalConnectionName property which I was after, so the expression I ended up with in the lookup column of my new row was: concat(body('Invoke_an_HTTP_request')['LogicalCollectionName'],'(',outputs('Compose_1'),')') 👍
@@wjameswilliams love it 👍
Thanks for exploring/presenting this. I’m also wondering whether Power Automate has the ability to create new choices in a table for a multi-select option choice column? I’ve created playlists in CZcams to categorize videos, however, I’d like to do this in Power Apps. Shane Young shows how to add a custom choice to an existing single select option choice column in SharePoint, but with his method (as I understand it) the only way to associate a video with more than one choice is to create two or more separate records for that video. Would you have any feedback, please, in order for me to achieve what I have in mind? Thank you.
I’m not fully understanding you. You want to add new choices to a record or new choice options to a column? And is this Dataverse or SharePoint? I’m always interested in use cases 👍
@@DamoBird365 Hoping to add new choice options to a column in Dataverse. So, if 'Favourite_Sports_Activity' includes Cricket, Football and Horse Racing and I decide that I want to add Golf I don't need to go into Dataverse solution, open tables, choose columns, select column, select edit option types, add an option, publish etc. Rather, I'm hoping that from within Power Apps I can be creating a record for a customer who for instance comes into a sports store, and when he tells the sales agent that his favourite sport is Golf or Water-Polo, that sales agent can use his or her Power App, the one they normally use to capture a customer's response as Cricket, Football or Horse-Racing, and from that Power App interface, kick off a flow that dynamically adds the new choice that needs to be associated with a record for this new customer. Again Shane Young does this in SharePoint however his method doesn't seem to result in an app that allows the sales agent to record two favourite sports for a customer. Thanks for this follow up question of yours - trust the week ahead will be full of more inspiration. czcams.com/video/GfY63JPdkcs/video.htmlsi=bmkCjnmxaBACa7jw&t=1
@@DamoBird365 Hoping to add new choice options to a column in Dataverse. So, if 'Favourite_Sports_Activity' includes Cricket, Football and Horse Racing and I decide that I want to add Golf I don't need to go into Dataverse solution, open tables, choose columns, select column, select edit option types, add an option, publish etc. Rather, I'm hoping that from within Power Apps I can be creating a record for a customer who for instance comes into a sports store, and when he tells the sales agent that his favourite sport is Golf or Water-Polo, that sales agent can use his or her Power App, the one they normally use to capture a customer's response as Cricket, Football or Horse-Racing, and from that Power App interface, kick off a flow that dynamically adds the new choice that needs to be associated with a record for this new customer. Again Shane Young does this in SharePoint however his method doesn't seem to result in an app that allows the sales agent to record two favourite sports for a customer. Thanks for this follow up question of yours - trust the week ahead will be full of more inspiration. czcams.com/video/GfY63JPdkcs/video.htmlsi=bmkCjnmxaBACa7jw&t=1
@DamoBird365 Hoping to add new choice options to a column in Dataverse. So, if 'Favourite_Sports_Activity' includes Cricket, Football and Horse Racing and I decide that I want to add Golf I don't need to go into Dataverse solution, open tables, choose columns, select column, select edit option types, add an option, publish etc. Rather, I'm hoping that from within Power Apps I can be creating a record for a customer who for instance comes into a sports store, and when he tells the sales agent that his favourite sport is Golf or Water-Polo, that sales agent can use his or her Power App, the one they normally use to capture a customer's response as Cricket, Football or Horse-Racing, and from that Power App interface, kick off a flow that dynamically adds the new choice that needs to be associated with a record for this new customer. Again Shane Young does this in SharePoint however his method doesn't seem to result in an app that allows the sales agent to record two favourite sports for a customer. Thanks for this follow up question of yours - trust the week ahead will be full of more inspiration. czcams.com/video/GfY63JPdkcs/video.htmlsi=bmkCjnmxaBACa7jw&t=1
@geralddahl9159 I reckon you could do it with the web api here learn.microsoft.com/en-us/power-apps/developer/data-platform/webapi/create-update-optionsets I’ve demo’d this api when duplicating a flow in a solution. Interesting use case 👍
Great, but why MS removed options: dropdown lists and multiselect list from the new designer?
For the input parameters? My belief is that it will arrive eventually. I thought it was useful to demo this to raise awareness.
Hi , is it possible to load power bi data to sharepoint list , i tried with run a query with dataset but it exports to csv not excel , can we export the data to excel with run a query with dataset in power automate
If you’re looking to export powerbi to excel, check out czcams.com/video/gtlklzi6MDg/video.htmlsi=eMyuV558TxV4QBSD
@@DamoBird365 I m new to understand this, its bit not easy for me , but thanks