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:
This is brilliant. Thanks!
Hi Sara, sorry, but… why I can’t see the Fetch Xml Query field on my “List records” form?
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? 🙂
You know what, I didn’t realise how useful fetch is in that regard. Thanks, Sara!
LikeLiked by 1 person
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?
Are you doing a modeldriven App or a Canvas App? I want to know because of what interface you have access to 🙂
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.
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 :))
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!!)
LikeLiked by 1 person
Glad it worked!
I think we all trip on things all the time so never feel alone 🙂
This also works for overcoming the error in Power Automate List Row calculated field limit of 50 when using Dynamics. Brilliant.