The latest addition to FetchXML is Late Materialize. Turning this on or off doesn’t change the results of your query, but it does change how it’s executed behind the scenes, which could lead to some performance improvements.
I’ve looked at different ways of accessing CDS metadata recently, but there’s another way I’d overlooked – using FetchXML. CDS exposes a limited amount of metadata as special virtual entities that lets you query it using standard FetchXML syntax:
I’ve been doing some work today on filtering multi-select optionset fields in SQL 4 CDS and using the T-SQL endpoint, and I came across a few interesting quirks.
One great option in FetchXML is to filter dates using a relative filter such as “this week”, “older than 2 years” etc. This is particularly helpful when you build a view, as it will automatically build the date criteria each time so your view is always up to date.
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.
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
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:
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:
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 […]