Power Automate – Retrieve Related Entities

In today’s blog, I am sharing two different ways how to retrieve related entities from Dataverse.

The use case considered for the blog is to retrieve all attachments associated with the contact entity.

I will be using a manual trigger for this blog, but the trigger will vary depending upon your use case could be a Power Apps trigger or any other trigger from the connectors list.

Let us get started with the Power Automate flow.

Method 1 – Use OData

You can read more about Odata (Open data protocol) here

Let us get all contacts with attachments columns. Use Expand query and mention the column names you want to retrieve from the related entity.

Contact_Annotation($select=annotationid,filename, documentbody)

Use Notepad++ or VS Code to find the  Annotation section in your JSON or you can also use JSON formatter and validator available online for free to format it.

I used ‘Generate from Sample’, and paste the Odata & this is what was returned as schema –

{

{
    "type": "object",
    "properties": {
        "@@odata.context": {
            "type": "string"
        },
        "value": {
            "type": "array",
            "items": {
                "type": "object",
                "properties": {
                    contactid": {
                        "type": "string"
                    },
                    "Contact_Annotation@odata.type": {
                        "type": "string"
                    },
                    "Contact_Annotation": {
                        "type": "array",
                        "items": {
                            "type": "object",
                            "properties": {
                                "annotationid": {
                                    "type": "string"
                                },
                                "filename": {
                                    "type": "string"
                                },
                                "documentbody": {
                                    "type": "string"
                                }
                            },
                            "required": [
                                "annotationid",
                                "filename",
                                "documentbody"
                            ]
                        }
                    }
                },
                "required": [
                    "contactid",
                    "Contact_Annotation@odata.type",
                    "Contact_Annotation"
                ]
            }
        }
    }
}

Once we have the JSON parsed, we can then get the related entity data by using Get a row by ID

Method 2 – Use Expand Query & Filter Rows actions of Dataverse

Let us get all the contact rows, use Expand Query section for retrieving related entities and their columns.

I am retrieving annottaionid (unique identifier), filename or display name & documentbody which is the actual attachment

Let us now get all attachments and then filter them with associated contact

_objectid_value will give us the value of contactid. You can now use the data to perform the required steps.

Once you test the flow, you will notice that a warning is thrown at the flow checker

Read the official blog here

You can use both methods if the data in the tables are low but I will always recommend going for Odata to avoid performance issues.

Hope this helps!

Keep reading, keep enjoying, keep exploring!