Dataverse, Power Automate

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:

Query WO for Clients

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:

Nested expansion

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:

FetchXml query in Power Automate.

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!😎

1 thought on “Power Automate with Dataverse: Saving API calls to Dataverse. Expand or Link-Entity?”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s