Power Automate with Dataverse: Saving API calls to Dataverse. Expand or Link-Entity?

Within Power Automate I am trying to make SINGLE(paginated) call to Dataverse to get the list of Work Orders with the particular status linked to Account but not directly but via extra layer. Also, I can retrieve extra lookup tables which are required by logic.

All in a single call because making extra API calls is expensive and evil! 😈

The FetchXML query looks like one below:

I build my queries in XrmToolBox. It’s free community tools but don’t forget to support people who build tools and donate money to say Thank You!

If you try to use Web API query instead you get the error like one below:

{“error”:{“code”:”0x0″,”message”:”The navigation property ‘msdyn_account_msdyn_workorder_ServiceAccount’ cannot be expanded. Only many-to-one relationships are supported for nested expansion.”}}

It’s easier to understand if you look at the tree structure:

What do we do then?

Make sure you used alias for each “link-entity”:

 <link-entity name="msdyn_workorder" from="msdyn_serviceaccount" to="accountid" link-type="inner" alias="ag" >

We use FetchXML with Power Automate like this:

The result will contain all your 1:M and M:1 data in the flat kind of shape:

{"body": {
    "value": [
{
  "@odata.type": "#Microsoft.Dynamics.CRM.account",
  "@odata.id": "https://tetttt.crm6.dynamics.com/api/data/v9.1/accounts(xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx)",
  "@odata.etag": "W/\"000000000\"",
  "@odata.editLink": "accounts(xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx)",
  "accountid@odata.type": "#Guid",
  "accountid": "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxx",
  "emailaddress1": "test@mail.com",
  "accountnumber": "C11111111",
  "name": "X Client",
  "ag.msdyn_substatus@OData.Community.Display.V1.AttributeName": "msdyn_substatus",
  "ag.msdyn_substatus@OData.Community.Display.V1.FormattedValue": "Status Status",
  "ag.msdyn_substatus@Microsoft.Dynamics.CRM.lookuplogicalname": "msdyn_workordersubstatus",
  "ag.msdyn_substatus@odata.type": "#Guid",
  "ag.msdyn_substatus": "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxx",
  "ag.msdyn_workordertype@OData.Community.Display.V1.AttributeName": "msdyn_workordertype",
  "ag.msdyn_workordertype@OData.Community.Display.V1.FormattedValue": "Type",
  "ag.msdyn_workordertype@Microsoft.Dynamics.CRM.lookuplogicalname": "msdyn_workordertype",
  "ag.msdyn_workordertype@odata.type": "#Guid",
  "ag.msdyn_workordertype": "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxx",
  "ag.msdyn_name@OData.Community.Display.V1.AttributeName": "msdyn_name",
  "ag.msdyn_name": "WO-1000003",
  "af.name@OData.Community.Display.V1.AttributeName": "name",
  "af.name": "Name ",
  "ag.msdyn_workorderid@OData.Community.Display.V1.AttributeName": "msdyn_workorderid",
  "ag.msdyn_workorderid@odata.type": "#Guid",
  "ag.msdyn_workorderid": "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxx"
}
]
}}

If you have 1 account with 3 Work Orders you will get 3 records in the result JSON with the account info repeating 3 times in each record.

Let’s say you iterate through the list via Apply for each.

Each column regardless of table and expansion level you will query the same way in Apply for each prefixing column names with the aliases specified in FetchXML:

For level 1 account column:

items('Apply_for_each')?['accountnumber']

For level 2 account column:

items('Apply_for_each')?['af.name']

For level 3 work order column:

items('Apply_for_each')?['ag.name']

Happy days!😎