Dynamically get related Dataverse tables to show related rows

[sammysword]

I am trying to build a flow which will take a GUID from a row, dynamically list out the related tables to the table the initial row appears in, and checks to see if there are any rows related to it.

So for example if I have Table A which has a record with ID 1, the flow would get the GUID from ID 1, list out the related tables which are Table B and Table C, then it checks both Table B and Table C to see if there are any rows which depend on ID 1.

I know I can do this by using a List Rows for Table B and Table C, but I want to do this dynamically so if I relationship is created or deleted then the flow would pick up that the relationship doesn't exist anymore.

Does anyone know if this is possible?

[FLMike]

Hello,

The only way that I suggest to do this is leveraging the HTTP Connector, to call Dataverse and get back the metadata about a table. Use that to loop through all fields (might be able to use a select), and get their metadata to see if its a lookup.

If its a lookup then you would use that information to populate the List Rows Action.

Here is a good Learn document you should read: Retrieve table definitions by name or MetadataId (Microsoft Dataverse) - Power Apps | Microsoft Lear...

Here is a good (but a little outdated) article on getting the metadata using Flow. Its not tough really.: How to Retrieve Dynamics 365 CE Entity/Table Metadata through Power Automate (FLOW) - Microsoft Dyna...

I am sure there is a straight HTTP Connector example versus the above.

Cheers
If you like my answer, please Mark it as Resolved, and give it a thumbs up, so it can help others
Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey

[LinnZawWin]

@sammysword 

I am not exactly sure why you need to make the query dynamic enough to run it properly even if the relationship between the tables is deleted. The flows and all the automation should be updated when there is such a metadata change.

For any reason, if it is a requirement, here's another solution in addition to @FLMike's metadata solution.

• Add x2 List Rows actions (one with relationships and one without relationships)
• Configure 2nd List Rows action to run after the 1st List Rows action is failed
• After each List Rows action, set the object array variable with the values required
• Process the data based on the object array variable (so that you can have the process step in common without branching)

[sammysword]

The reason I want it to be dynamic is so I can have 1 flow which will serve all my tables. I just need to pass in my Table Set Name and the List Rows will be able to pick up exactly what I need. I have around 30 tables in my solution so sitting mapping out Flows for these which are hard coded and need updated every time a new relationship is added, doesn't feel like the most efficient way to work.

It's probably worth mentioning that the main reason I want this, is because I am trying to come up with an approach where a user tries to delete a record through a Canvas App, and then Power Automate will check to see if there are any dependencies on this before doing so. If there are, it will pass back what the dependencies are to the user to fix, using a friendly bit of text

Thanks for your suggestion. When you're listing your rows, I'm confused as to what you mean by 'with relationships' and 'without relationships'. How do you list a row including the relationships?

[LinnZawWin]

@sammysword 

You can use the Expand Query parameter in the List Rows action to query data including the related table.

[sammysword]

I know but the problem with this is it isn’t dynamic like I initially said. I would need to know the relationships for each table and then hard code it into my flow. I want a way to search the table for all relationships, then do the expand or even just a list rows to check if dependencies exists and to see if the initial row can be deleted. 

[LinnZawWin]

Based on your requirements, I am not sure low code cloud flow would be a good fit for it.

Maybe you might want to write a custom API and call it from the cloud flow.