Retrieve Records in N:N relationships with FetchXML and the New CDS Connector in Microsoft Flow

Have you tried to retrieve a list of records in Flow and had to go trough a many to many relationship condition? If you have I can relate to the frustration and complication that task brought. Earlier this week I found a solution – and it was easy, really easy. Let me show you what I found.

What’s so new with this CDS Connector?

So let’s start with talking about the “new” CDS Connector. I say new but really it was released this June, I just haven’t payed attention to it’s awesomeness until now. This CDS Connector is only available when you create your flow from a solution.

It only has one trigger – but it’s pretty darn good. It reminds us CRM-folks about the classic Workflow designer when it comes to trigger conditions. It’s also no need to define what environment you want your flow to trigger from. Since it’s created from a solution, it will run from the environment the solution is deployed to.

The connector also offers a lot of actions, many of them you probably recognize from the other CDS Connector. But the List Records action has one HUGE difference compared to it’s sister – Fetch XML Query.

List records in a N:N relationship

Let’s use a classic example – a contact can speak several languages. I create a N:N relationship to a custom entity called Language and now I want to list all Contacts that speak Swedish in my Flow.

The absolute simplest way is to do that exact search in Advanced Find and download the Fetch XML.

In the action just choose what Entity you want to list, and then copy and paste the whole thing into the Fetch Xml Query field.

Insanely simple right? No more sleepless nights and nested List Records-actions with the old CDS connector. This simplifies it tremendously.

A Bug that Complicates Things

Using the actions and trigger from this Connector does not only bring good things unfortunately. Populate lookups in the actions “Update a Record” or “Create a new Record” doesn’t work with only dynamic content. I found the solution to the problem here. Workaround suggested in that forum is for example to populate Account lookup like this:

Flow away!


19 thoughts on “Retrieve Records in N:N relationships with FetchXML and the New CDS Connector in Microsoft Flow

Add yours

    1. Hi Patrick, you need to create your flow from a solution to be able to use the Common Data Service (current environment) connector. It’s only this connector that have the option to filter on FetchXML. Does that help? 🙂

      Like

  1. Hi Sara – I need this for my CDS based power app, but I can’t work out how to download the fetch XML of the filter. Your screenshot with the “Download Fetch XML” button looks like the old classic version of power apps but the only way I can get to that is via the Solutions screen. I can run an advance filter in my app, but there is no obvious option to download the fetch XML – can you help?
    Thanks!
    Simon

    Like

      1. Hi Sara
        Thanks so much for getting back to me!
        I’m doing a model driven app although this particular flow will be activated by a button in a canvas app which will be embedded in a form in the model driven app.

        Like

      2. Okay 🙂
        So if you open your modeldriven app and go to advanced find in top right corner, doesn’t your screen look like in my images?
        The query builder and with the option to download the FetchXML? (If it doesn’t, let’s continue this by e-mail so we can send screenshots :))

        Like

      3. Hi Sara

        It didn’t last night, but it does now! Last night I just got a blank window but this morning, after about a minute, the filter screen pops up.

        Now I can have a play to work out how to set the filter!

        Thank you very much for your help – it’s weird how it’s the simple things that so often trip people up (or perhaps it’s just me!!)

        Simon

        Liked by 1 person

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

Blog at WordPress.com.

Up ↑

%d bloggers like this: