Two Most Ignored features of FetchXml

By | July 25, 2017

What would you choose if you were given an option to use SQL or FetchXml to run a query? I’m sure that 99.9% of us would go with SQL in that situation since FetchXml tends to limit what we can do.

However, FetchXml is, actually, more powerful than you would think if you simply continued to use “Download FetchXml” option in the advanced find.

For example, there are two limitations we have in the advanced find:

  • If we had am account and two contacts, and if we used Advanced Find to look for the accounts having those contact, we would get only one row for that account in the list. Even though, in SQL we would be able to get two rows – one per each valid combination. With the Advanced Find, we are only getting “unique” records
  • We cannot use outer joins(which would be especially useful if we could define “not exists” conditions), and we cannot join in the same related entity more than once

However, both of those are exclusively Advanced Find limitations. FetchXml is perfectly capable of handling either of those.

Consider the following query:

Have you ever noticed that distinct=”true” attribute gets added to the FetchXml when you have a linked entity added to the query? For example, for the query displayed on the screenshot above, here is how downloaded FetchXml looks like:

That’s exactly why Dynamics will be displaying one row per account in the results. Even if there are multiple contacts linked to that account through the parentcustomerid field. Replace that part of FetchXml with distinct=”false”, and you’ll get multiple rows. Of course you can’t do it in the Advanced Find, but.. you can use XrmToolBox to update a saved view, or you can do it in the report. Point being, it’s not a FetchXml limitation at all.

And what about those outer joins?

For the same FetchXml above, I can re-write it like this:

Notice how I’ve added link-type=”outer” to the link-entity. And, also, notice how I moved the filter from the link-entity section up to the main entity.

The first change turned this whole query into an outer join. If I did not move the filter, that would produce a list of all accounts then (an outer join does not require a related record to be there).

If I removed the filter completely, I would still be getting all accounts.

However, since I moved the filter, and since I’m using entityname in the condition now, I can apply that condition to the entity joined through the “outer” join. Which basically turns the whole thing into the same original query – I will only see the accounts which have contacts with the fullname like “%Test%”.

That’s not a lot of progress so far, but wait.. here is what I can do next:

I can keep adding those outer joins and related conditions. This time, my FetchXml will return all accounts where there is a contact with the fullname like “%Test$” linked to that account OR if that account has a primary contact with the fullname like “%Main%”.

I can add more conditions. I can add linked entities to the linked entities (and still use filter conditions). For example, I might add opportunities to that fetch under the primary contacts and add a filter for the opportunity title. That would add accounts with the primary contacts having linked opportunities with such titles (and it would still be “OR” for all those conditions, not “AND” – you can do “AND” out of the box)

Interesting, isn’t it?

This last feature of FetchXml, in combination with RetrieveMultiple plugins, can help us achieve some interesting results:

Quick Find on Your Terms

 

5 thoughts on “Two Most Ignored features of FetchXml

  1. Pingback: Dynamics V9: Accounts that have no opportunities (almost there) - Microsoft Dynamics CRM Community

  2. Alex Yeoman

    Fantastic, just found this article and it helped me achieve exactly what I wanted, thanks Alex!

    Reply
  3. Beck0Fish

    Hi Alex, thanks for this post. When you say “We cannot use outer joins (**which would be especially useful if we could define “not exists” conditions**)… FetchXml is perfectly capable of handling either of those.”, does that mean we still CANNOT define ‘not exists’ in any form of FetchXML?
    (Sorry for misunderstanding and thank you for clarifying)

    Reply
  4. Niels

    This is brilliant, Alex. Thanks for posting.

    I’ve used this approach to find all activities that are set regarding an account, any accounts under it in the hierarchy – or any opportunities related to those accounts. I have been struggling to find a way to do that until now.

    Now, if there was only a simple way to use the FetchXML dynamically in a sub-grid and use a parameter from the form such as the accound GUID.

    Reply

Leave a Reply

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