Using FetchXml in the Flows

 

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:

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:

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:

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:

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:

Screen b:

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:

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

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:

• 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:

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:

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:

Time for a test drive? Here we go: