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

[@brightideasagency]

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

[@รถพรวนดินมิตซู]

Thanks so much for sharing this useful method for this purpose.

[@brightideasagency]

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!

[@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]

Thanks for watching.

[@katielux357]

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]

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.

[@GadiantonsRobber]

How did you quickly format it this nicely in VSCode?

[@brightideasagency]

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.

[@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.

[@StephCoul]

Thx so much!

[@brightideasagency]

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 😀

[@kingwata1]

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]

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.

[@ShamNaseer-k8c]

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.

[@henrybroadcast]

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

[@brightideasagency]

What are you missing?

[@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.