Query D365 ce using fetch xml in azure logic app (without cds connector)

In azure logic app we have oob connector for cds (common data services) which does most of the task but have certain limitations.

We know that fetchxml is much more powerful in terms of aggregation and joins.

Also using below approach you can handle the cases where you just want to update dynamics fields in ce (where fields to be updated are not predefined) whereas in cds connector you have to pre-define the field to be updated.set.

We’ll see how we can execute a raw fetch xml in logic app in a d365 ce instance using http call.

Prerequisites :-

1. Azure ad application with secret generated (Azure Subscription should be with same Office 365 account as of your D365 instance-you can login to azure using your ce trail instance credentials) (Ref ms docs)

2. Application user created in ce with same application id

Step 1:- Go to your azure subscription and create a new logic app (http triggere in our case):

Step 2:- Save the logic app and click on next step and initialize a variable to store your fetch xml:

Step 3:- Name your variable and paste your fetch xml in the value:

Step 4:- Click next step and select http action:-

This slideshow requires JavaScript.

Step 5:- Now select values as follows:

Method: Get

URI: https://<yourd365ceinstance&gt;.api.crm8.dynamics.com/api/data/v9.1/<entitypluralname>?fetchXml=

Step 6:- Now we have to pass encoded fetch xml declared in variable in step above.

For that click after = in URI and paste “uriComponent(variables(‘fetchxmltoexecute’))” in dynamics content section and click ok.

What it does?

This uriComponnet method encode the fetch xml stored in variable fetchxmltoexecute.

Your step should look like this:

Step 7:- Now select authentication method as Active Directory OAuth:

Step 7:- Set the rest of the parameters as follows:

Tenant : Azure tenant id

Audience : your CE url

Client Id: Azure ad application id/ Clientid

Credential Type: Secret

Secret : A valid secret generated for above ad app

Step 8:- Now save the logic app and click on run button and wait for few seconds on same screen.

Looks like all steps got succeeded..!!

Step 9:- Let’s click on last http step and see the output. Data from account record is visible in body of the action:

You can write complex fetch and pass filters dynamically and execute all those request using this way.

Similarly you can directly fo CURD operation on ce/cds using such http action and without using cds connector.  It’ll be useful in scenario like where you want to update only delta on a record in ce/cds. Then you can build your json and post the json using post/patch method on desired entity.

Hope it was helpful..!!