David Fraticelli raised an interesting bug that became apparent in SQL 4 CDS. Many thanks to David for all his help in tracking down what was happening. I’ve mentioned before the many ways that paging in FetchXML can give rise to unexpected results. One key thing to ensure you get Continue Reading
Identifying merged records
You can use duplicate detection rules to find duplicate records in Dynamics 365 (or do it even better with Data8 Duplicare!), but how do you go back and find what records have been merged, or by whom?
Integrating SQL 4 CDS with your own XrmToolBox tool
One for the XrmToolBox developers: if you want to make use of the power of SQL 4 CDS from your own tool, how can you do it?
MSDyn365 Internals: Paging Gotchas
I’ve recently been looking at a few cases where I’ve got seemingly incorrect results from my FetchXML queries. This can manifest itself as the results limited to 50,000 records or records being skipped. I’ve narrowed these down to some behaviour I wasn’t aware of when you retrieve multiple pages of Continue Reading
Dataverse Paging with Distinct
I hit a strange situation yesterday. I was retrieving data from Dataverse with a FetchXML query but some values were missing from my results, while others were duplicated. Turns out it’s an easy situation to get into without noticing.
FetchXML Late Materialize
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.
Querying Metadata with FetchXML
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:
Multi-select Picklist Filtering
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.
FetchXML Date Filtering
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.
Column Comparisons in 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.