Using FetchXml in the Flows

By | March 13, 2019

 

Having established (for myself.. and not without the help from other folks) that CDS Connector is the way to go when working with Dynamics or CDS data, I quickly found that ListRecords action does not support fetchXml. Or, at least, I don’t see a parameter for that:

image

That said, WebAPI endpoint does support fetch execution:

https://docs.microsoft.com/en-us/dynamics365/customer-engagement/developer/webapi/web-api-query-data-sample#bkmk_fetchxml

So this seems to be current limitation of the CDS Connector(intentional or not).

Technically, fetchXml is more powerful  than OData when it comes to building complex queries, when traversing the relationships, etc. I am not sure what the future holds for FetchXml, since it’s a proprietary “query language” developed and maintained by Microsoft, but, for now at least, it’s a legitimate way of querying data from Dynamics and/or from CDS.

So, what if I wanted to use FetchXml to validate some advanced conditions in my Flow?

Not to go too far into the complexities of FetchXml, let’s try adding a condition that verifies if a primary contact on the account that’s just been created has associated cases. The query would look more or less like this:

image

And, if that query returns any cases at all for the account, I’d like my flow to add a note to the account description field.

So, to give you an idea of how my flow will look like, eventually, here is a screenshot:

image

The first step is straightforward – it’s a CDS Connector trigger which will kick in whenever an account record is created.

The second step is where I’ll actually run FetchXml.

The third and forth steps are all about parsing the results and verifying the condition.

For the second step, even though it’s probably possible to do the same with a pure HTTP connector, I figured I’d use an Http with Azure AD (Preview) connector instead. Turned out it does take care of the authentication already, so I don’t need to worry about that (with a note, though, that I am not exactly sure what’s going to happen when/if I add this flow to a solution and export/import to another Dynamics CE instance.. will try it later).

There seem to be two tricks about that connector, and it took me a little while to figure them out (I’ve almost given up to tell the truth). When you are adding it to the flow, you’ll be presented with this screen:

image

I used “Invoke an HTTP request” action in my flow(and you’ll see below how that action was set up), so, let’s say you’ve selected that action.

Depending on something in your environment (and I am not sure what it is exactly), you will see one of these two screens after that:

Screen a:

image

Screen b:

image

If you see screen a right away, that means your HTTP with Azure connector has picked up a connection, and it’s not necessarily the right connection. In my case, I quickly discovered (once I tried running the flow) that my action was permitted to access Sharepoint but not Dynamics – that must have something to do with Azure AD OAuth, although I am not 100% sure of what’s happening behind the scene yet.

So, if, somehow, you run into this issue, make sure to verify the connection your action has picked up, and, if it’s not the right one, create a new connection:

image

This will bring you back to Screen B from above. Once you are there, fill in the textboxes like this:

image

Do not put “.api.” in the url-s as you would normally do when accessing WebAPI endpoint. Just use root url for your dynamics instance.

After that, sign in, and you’ll be back to Screen A with the right connection this time.

The rest should be straightforward..

Set up the action like this:

image

  • Choose Get method
  • Make sure to use root instance url for the request (do not add “.api.” in the middle)
  • Add FetchXML to the url (download it form the advanced find, update as required, etc)
  • Don’t forget to update filter condition in the FetchXML so that the request is using correct account id

 

Next, add Parse JSON action like this:

image

You can just run the same url that you put into the Http action directly in the browser to generate sample data, and, then, you can feed that sample data to the “use sample payload to generate schema” tool of the “Parse JSON” action.

And the last step – just add a condition:

image

I used an expression to get the total # of records in the result set (length function) – you can see it in the tooltip on the screenshot. But, in retrospect, fetchXml aggregation might work even better for this scenario.

Do something when the condition evaluates to true (or false), and the flow is ready:

image

Time for a test drive? Here we go:

image

image

8 thoughts on “Using FetchXml in the Flows

  1. omartin

    Thanks so much Alex! I’ve struggled to get an authentication token to pull FetchXml data from CRM directly for a Logic App (which is similar to Flow), and you’ve nailed it with the HTTP request with authentication!

    Reply
  2. Kaustubh

    How does the get request manage Paging. In the sense if the record count is more than 5k.

    Reply
  3. John Liu

    Whether you get A or B is probably related to whether you’ve previously set up a Microsoft Graph based connection – which talks to https://graph.microsoft.com resource.

    If you have one of those – then the existing connection, which Flow will pick, only has auth credentials for MSGraph, and not for Dynamics.

    Reply
    1. Alex Shlega Post author

      Thanks, and, yes, even a few months is a lot of time for PowerPlatform:)

      Reply
  4. jake

    i cannot get the url + fetch to return in the browser to capture the payload schema. is there a trick to this?

    Reply

Leave a Reply to Alex Shlega Cancel reply

Your email address will not be published. Required fields are marked *