Category archives: FetchXML

I was very pleased today to see a new feature in FetchXML – column comparisons! This allows us to build queries that compares the values in one column against those in another. Previously we’ve only been able to compare a column against a constant value. So how does this look? The <condition> element has been […]

A follow-up on my previous post on link-entity, prompted by this tweet from Daryl LaBar: Was unaware of the new join operators. @jordimontana @XrmWizard have either of you used these, or know what they are supposed to do? The documentation is rather lacking…https://t.co/Nr4AyZMzb2 — Daryl LaBar (@ddlabar) April 2, 2020 I’d previously stated (incorrectly): FetchXML […]

You can ensure your query only produces each row once using the distinct attribute. For example, if you have multiple entries for the same company name you can get the list of unique names using: This option gets applied automatically whenever you create a query using Advanced Find. This is particularly useful when you add […]

By default you’ll get up to 5,000 records that match your query. The top-level <fetch> element supports a few options to help you control this: top will limit the number of records further, e.g. When you use top to limit the number of records, you don’t have the option to move on to subsequent pages. […]

If you’re using any hierarchies in your data, you’ve probably noticed the icon in your main grid views that you can click on to show the hierarchy view when records have a parent or children. It’s simple to work out if a record has a parent – just check if the parent lookup attribute contains […]

One common pattern of queries I see about FetchXML is how to write queries to ask two different questions about the same related entity. For example: Invoices that include product A AND product B? Contacts that have pending emails AND no sent emails? Visits that have a page view of the checkout page BUT NOT […]

Last time I looked at aggregates, but unforgivably I missed out how to apply grouping to date/time attributes: Not to forget – data grouping….! 😉https://t.co/pLqQo35mc3 — Jonas #LazyDev Rapp ᴹᴠᴾ 🇸🇪 (@rappen) December 18, 2019 Grouping works quite simply for data types such as text, picklist or lookup values, but a simple grouping of date/time […]

One feature of FetchXML that sets it apart from other CDS query options is aggregates. This is the option to get summary data (number of records, sum of opportunity value etc.) rather than the individual values from each record. If your only concept of how many records you have is “5,000+” then this is for […]

It’s often important to sort the results of your query, and in FetchXML you can do this with the <order> element. This is equivalent to the ORDER BY clause in SQL. Sorting by multiple fields By adding more <order> elements, the results will be sorted by each attribute in sequence. The orders are applied, well, […]

I’m very proud to have some more of my contributions included in the latest release of the popular FetchXML Builder tool! There are two main updates I’d like to show you: Live Updates As you edit your query, everything else now updates in real-time. No more clicking away to see your changes reflected in the […]