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

Подписаться 4,4 тыс.

Просмотров 10 тыс.

50% 1

Need an AI plan for your business? Check out Nick's new book, "Who's in the Copilot's Seat?" - brightideasagency.com/book - And if you need more personalized help or advice, check out new engagement options that are available too - brightideasagency.com/engage
In this video, we'll show you how to work with Dataverse lookup columns in Power Automate flows.
Power Automate is a great tool for automating your business processes. In this video, we'll show you how to use Dataverse lookup columns in Power Automate flows to automate your work. We'll give you a demo of how to use lookup columns to extract data from Dataverse tables and put it into the appropriate fields in your flows. After watching this video, you'll be able to use lookup columns in your flows to automate your work!
Chapters:
0:00 Introduction
0:43 Set-up of Dataverse tables
1:21 List Rows in Power Automate
2:38 Get data from related Dataverse tables
4:25 Using expand query option to get data from related Dataverse tables
6:10 Use $select statement in expand query to select related table columns
7:05 Add a row that includes a lookup column
9:15 Conclusion
Apart from publicly accessible information all user data or other related information shared in this video is created for demonstration purposes only. User accounts, passwords, or other data used as part of any demonstrations shown in this video have been created specifically for that purpose and are not any individual or company's private information or data.
Bright Ideas Agency is an Ohio Limited Liability Company. The content of this video is for informational and entertainment purposes only. No guarantees are given in connection with the information shared and you should seek independent technical or business advice before implementing anything you see in this video. If you want to hire us for your project, visit www.brightideasagency.com and get in touch.

Наука

Опубликовано:

22 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 27

@brightideasagency 7 месяцев назад

@kingwata1 5 месяцев назад

How do I get to insert the data dynamically? Your processed inserts the same data because of the GUID of 1 edit link. If I have Headquaters, Retail, Warehouse..., I want to insert the selected data by the user.

@brightideasagency 5 месяцев назад

Thanks for watching. My advice is to test the process you want to use including some Dataverse requests with static values and then when you are happy it works as desired fill in the dynamic values in Power Automate.

@chrisph7772 Год назад

This was useful , thank you. I struggled to find to get the plural name the first time. The solution I found is to run a flow that lists all the items and look into the result to find out how it is labelled

@brightideasagency 11 месяцев назад

Thanks for watching.

@chaitanyachakravarthyaenug9159 Год назад

Thanks for the Knowledge, it saved my time :)

@brightideasagency Год назад

Thanks for watching, and glad it was useful!

@K1eyvan Год назад

Great video, thanks for sharing the knowledge.

@brightideasagency Год назад

Thanks for watching!

@teunkruijer Год назад

Hey, Great video helped me a lot. Quick question. I have a flow that triggers on a insert,modify from MS Dynamics CRM records. I do not see an expand query option here on the trigger. So, right now I'm executing a Get Row By ID straight after to get the expanded query feature, but this seems like double the work. Any ideas on how to fix this ?

@brightideasagency Год назад

Thanks for watching. AFAIK unless you were to build your own custom connector with an appropriate use of a webhook to trigger your flow, you are limited to the information passed in this flow initiation to what is built in. You can filter your trigger to stop it from running, but not add extra content to the request as by the time the trigger is being dealt with in Power Automate, it already has the information (i.e. it has already been triggered with the content that caused that trigger to occur). Your single request back to Dataverse with appropriate column selection and expansion is already very efficient versus other techniques, so unless you have performance issues, I wouldn't worry about it too much.

@katielux357 5 месяцев назад

Thank you for this! I have a question regarding the child column you bring in at min 6:19. How do then filter by this child column? In your video this would be the country column.

@brightideasagency 5 месяцев назад

Thanks for watching. In most cases you run into a limitation of filtering in relation to anything other the linked value in the child table using this approach.

@jeffeNuts Год назад

thanks, this helped me

@brightideasagency Год назад

Glad it was useful. Thanks for watching.

@morwamokgatlasimunyerading6579 Год назад

Quick question, I want to create a flow that checks if a record already exists in a dataverse table before inserting a new row. It has to compare the new record against multiple columns. How can I achieve that?

@brightideasagency Год назад

This is pretty easy to do. Just use a list rows action with an odata filter that applies whatever match you want on your columns (if you want to filter against related tables just use a relatedtablename/columnname reference to do this). Then use a switch action against the length() of your output to decide what to do next. Case 0 - you create a new row. Case 1 - you update the row (item 0 of the single member array). Any other case and your filter criteria retrieved more than one row, so you have a logic problem you need to fix. Hope this helps 😀

@henrybroadcast Месяц назад

you omit valuable details of how to retrieve the value of the lookup field :(

@brightideasagency 25 дней назад

What are you missing?

@GadiantonsRobber 11 месяцев назад

How did you quickly format it this nicely in VSCode?

@brightideasagency 11 месяцев назад

Thanks for watching. Where Power Automate presents its output in the web UI, you can just copy and paste this neatly into VSCode. Unfortunately, where it presents it as a file to download, it's not so neat. My normal workaround to this is to add a Compose step and present the output there. In this case PA generally pushes whatever it is into the web UI and you can pull it out into VSCode as you'd want to.

@user-ys2yb1lz8q Год назад

Hello. What about in cases of many to many relationships?

@brightideasagency Год назад

You can expand a many-to-many relationship column in the same way you would a many-to-one, and instead of retrieving a single record you get back an array of data. However, what you can do beyond this with the built-in connectors in Power Automate is rather more limited.

@nooralfar10 Год назад

Hello.. I spent a lot of time on this, I have flow that add new rows to data verse table (Table0) from excel file created on share point (trigger) I have 3 lookup columns and 2 dates columns and 1 numeric columns First I had to change the date format using compose action Then when I tried to add new row I did the following : Dates columns =compose outputs Day column =day column from excel Lookup1 =/csr_12Table1setname(lookup1 from excel) Lookup2=/csr_12Table2setname(lookup2 from excel) Lookup3=csr_12Table3setname(lookup3 from excel) Now im facing bad request- syntax error in the last step which is the add row action When i look to the input I can see the flow reads the input from excel but can't add them to data verse table ..am I missing something? Note that the primary column in data verse is ID and the unique identifier is a column called Table0 (same as table name) and im not filling both of them cause I don't have them in excel file

@brightideasagency Год назад

Thanks for watching. Without seeing the flow and the exact error, there are potentially a lot of places this could be going wrong. I would start by double checking your table names (the part after the / and before the () and that the GUIDs being referred to in the item are a correct reference to an item in the related table. If that all looks right, instead of using dynamic content in the flow step to create the row, try putting in the values for an example row manually to check that works -- if it does, then something's going wrong with how the flow is bringing across those values from Excel. Excel can be a bit weird when it comes to data types, so that might be causing some issues. If those suggestions don't help, feel free to reach back out here, on LinkedIn or through our website.

@nooralfar10 Год назад

@@brightideasagency it works but on guide Is there a way to make the guide dynamic I.e Instead of Lookup1=/csr_12Table1(guid for single column) I want Lookup1=/csr_12Table1(guid for excel column )

@brightideasagency Год назад

@@nooralfar10 I suspect that the problem you're running into is that you may not have the GUIDs in your Excel file, you perhaps have some other reference to your Dataverse row such as a name or an account code. In this case, you will need to take extra steps to match that reference against a row in your table, and then extract the GUID of that row to use in your create row action. Depending on specifics of how those Excel files are generated, you may need to really focus in on error checking and what happens on a failure to find the right row, as unless you have your data validation locked down on the Excel side, Power Automate may end up with unexpected data. If this is a new solution you are putting together, and you are simply using Excel to capture data, then a different solution using Power Apps, or even Lists, as a front end might make life easier for you in the long term. If you have another system that is routinely producing these Excel files, then you might want to look into Dataflows as an option for your solution or another Azure based mechanism for standardizing the ingestion of your data and then presenting it up to the Dataverse database. Depending on the complexity, this may offer a more robust solution in the long-term than Power Automate. Please do reply here if you need more help.