Two Most Ignored features of FetchXml

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