Because the dategrouping attribute in FetchXML supports a few more options than the standard T-SQL DATEPART function, I’ve extended it in SQL 4 CDS to allow some additional values as the first parameter. You can also use fiscalperiod and fiscalyear to group your data by your fiscal periods instead of calendar periods, e.g.:
SELECT datepart(fiscalyear, createdon) AS year,
datepart(fiscalmonth, createdon) AS month,
count(*) AS leads
GROUP BY datepart(fiscalyear, createdon), datepart(fiscalmonth, createdon)
ORDER BY 1, 2
FROM contact AS c
account AS a
ON c.parentcustomerid = a.accountid
ORDER BY a.name, c.firstname
If you use an order that isn’t supported by FetchXML you’ll get the error Order already applied to later link-entity
UPDATE & DELETE Batch Sizes
You know you can write more than just SELECT statements in SQL 4 CDS? You can now control the number of records that are processed for UPDATE and DELETE in each batch, which can help avoid timeouts if each one takes a long time because of plugins etc. To control the batch size, click on the Settings button in the toolbar:
As well as these new features there are various bug fixes included:
Fixed “Unhandled INSERT optimizer hints” error when running INSERT queries
Fixed UPDATE query to set values to NULL
Fixed IN & NOT IN criteria
Fixed converting FetchXML to SQL (e.g. in FetchXML Builder) when the query contains a <link-entity> with no content and aliases that aren’t valid SQL identifiers
Grouping works quite simply for data types such as text, picklist or lookup values, but a simple grouping of date/time values wouldn’t work very usefully. They are stored with second (or in some cases, millisecond) precision, so in most cases no two records will have exactly the same value and each record will end up in its own group.
More typically you’ll want to group records that have a value in the same day or month to generate summary charts or reports, and that’s exactly what FetchXML does for you using the dategrouping attribute:
This example will get the number of contacts created each month.
Date/time values can be grouped by:
If you are grouping by a date/time column, you must use one of these groupings. You can’t group by the raw date/time value – if you try you’ll get the error Invalid value '' for dategrouping.
You’ll notice that there’s no support for anything more precise than a day – you can’t group by hours for example. If you have a need for that you’ll need to retrieve each individual record and implement your own grouping logic.
When you group by day/week/month/quarter, you get the appropriate day/week/month/quarter number. For example, when grouping by month you get a month number 1 – 12. Note that this means records in the same month from different years will be included in the same month. If this isn’t the behaviour you want, e.g. you want to keep January 2019 separate from January 2020, you need to include a separate grouping by year as well:
For week grouping you get a number 1 – 53, quarter is 1 – 4 and day is 1 – 31.
These date/time grouping options are equivalent to using the DATEPART function in SQL:
SELECT DATEPART(month, createdon) AS month,
count(*) AS count
GROUP BY DATEPART(month, createdon)
There’s no direct SQL equivalent for the fiscal period / year grouping options as these are application specific. However, in SQL 4 CDS I’ve extended the DATEPART function to allow fiscalperiod or fiscalyear to be specified as the part type name parameter.
You’ll need SQL 4 CDS 1.0.5 (which I should be releasing soon) or later to use the DATEPART function.
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 you.
Let’s take a look at an example to get the most common first names of your active contacts:
There are 4 main points to note that are different from a regular query:
The <fetch> element has the aggregate="true" attribute
All the <attribute> elements have an alias. This is required for all aggregate queries, and is the one exception to my “don’t use attribute aliases” rule.
All the <attribute> elements also have EITHER a groupby="true" or aggregate="xxx" attribute
The <order> element uses an alias="xxx" attribute to refer to the previously aliased <attribute> instead of the normal attribute="xxx"
Run this query and you’ll get a list of different firstnames along with the number of active contacts that have that name. The list will be sorted with the most popular name at the top.
There are only a few aggregate functions you can apply:
If you’re familiar with SQL these will mostly be familiar as the ways of retrieving the number of records, and the lowest, highest, total and average values in a column.
The one that’s not so obvious is the distinction between count and countcolumn. The only reference I could find for this was a blog dating back to CRM 4.0. In brief, count will give the total number of records, while countcolumn gives the number of records that have a value in the attribute you apply it to. Note that it doesn’t matter which column you apply count to, you’ll get the same total number of records either way.
The countcolumn aggregate also supports a distinct attribute. This lets you get the number of different values in that column, e.g.
This will get a list of countries and the number of different cities in each country.
What “different” means in this context depends on the collation settings for your organisation. For the English installations that I have access to this defaults to ignoring case and accents, so “Münich” and “MUNICH” would be counted as being the same.
You can use <filter> specifications in aggregate queries in the same way as normal. See part 3 of this series for more details about filters. The filter will be applied before the aggregate. There is no way to filter the results of the aggregation, e.g. show results where the count is greater than 1, as helpful as that would be.
You can run an aggregate query without including any groupby="true" attributes. In this case, all the records that match your filter will be aggregated into a single result record. This is useful for finding the total number of records or highest/lowest values across all your records, e.g.
This query gives the number of open opportunities, along with the minimum and maximum values of those opportunities.
However, it is often more useful to group the records to see the differences in the summary values in different groups. For example, you can see the pipelines of different sales people by grouping opportunities by their owners and getting the total value of the opportunities in each group:
This is the first example we’ve looked at that uses a <link-entity>, but they work in aggregate queries in exactly the same way as in regular queries.
It’s very useful to sort the results of aggregate queries. For producing reports such as the opportunity pipeline query earlier, the results could be sorted by user to make it easy to find a particular individual, or by the pipeline value to highlight the best or worst achievers.
With the first example to find the number of contacts with each first name, sorting this to find the most and least popular names can often highlight data quality issues. Try it on your data and see what you find!
There are a few limitations to aggregate queries you should be aware of
Maximum of 50,000 records. If you try to run an aggregate query that uses more than 50,000 records you’ll get the error AggregateQueryRecordLimit exceeded
No paging support. As for normal queries you can get back up to 5,000 records from your query, but if you have more groups than that there is no way to move on to the next page.
To get around these limitations you’ll need to break up your query with more restrictive filters and combine the results. In SQL 4 CDS I worked around these limits by retrieving all the individual records and applying the aggregation in code when necessary.
For one special case of getting the total number of records in an entity without any filters, i.e.
there is a new RetrieveTotalRecordCountRequest message available since version 9 to get this more efficiently and bypassing the 50,000 record limit. SQL 4 CDS will use this optimisation where possible.
These options are equivalent to the GROUP BY clause and aggregate functions in SQL.
The count aggregate is equivalent to count(*) in SQL, and countcolumn is equivalent to count(attributename).
The SQL HAVING clause doesn’t have a FetchXML equivalent – this is one improvement to FetchXML I’d love to see.
RetrieveTotalRecordCountRequest is equivalent to SELECT count(*) FROM entity, with no option to apply a filter. I have a feeling it may actually map to the sp_spaceused SQL Server stored procedure under the hood.
By adding more <order> elements, the results will be sorted by each attribute in sequence. The orders are applied, well, in order. The results will be sorted by the first <order> attribute, then the second one, and so on.
If you run this query however, you’ll see that the results are actually ordered according to the full name of the primary contact.
This is true of any lookup field – the results are sorted according to the primary name attribute of the related entity. This is even true for polymorphic lookup fields such as customer fields. This makes sense from the perspective of the user interface – click on the header of the lookup column and you’d expect it to sort by the name. But this does not guarantee that records with the same guid will be retrieved consecutively, which can cause problems if you are using these IDs in integrations.
If you have code that relies on getting records with the same guid from a lookup field next to each other, order by the primary key of the related record instead of the foreign key lookup field. For example, rewriting the previous query as follows will order the results by the contact ID instead of the name:
As we’ve just seen in the last example, an <order> can be placed within a <link-entity> as well as within the main <entity> to apply the ordering to attributes within the linked entity.
Bear in mind there is no way to specify the sequence that orderings are applied across multiple entities. Orders from the main <entity> are always applied first, then the first <link-entity>, then any sub-link-entities, and so on. In this example:
When you execute any query, by default you will only get up to 5,000 records back. To get any more you need to use paging to get the next 5,000, and the next, and so on.
For paging to work correctly, the data must be sorted so that CDS can work out where the next page should start from.
Because a sort on any field apart from the primary key can result in two records with the same value being next to each other, it would be impossible to use just those values to determine where the next page should start. If you sort by the name of contacts and the last record in the first page has a name of Mark Carrington, the next page could start with another Mark Carrington record as well. There is no way of telling exactly which of these records should be the start of the next page.
To resolve this problem, CDS automatically adds an extra sort on the primary key field. This makes sure there is a unique value for the system to use to tell exactly where the next page should start.
Remember though that a sort can’t be applied to the top-level entity after one that was applied to a link entity. This means that if you’ve sorted the query by a link entity, this automatic sort by the primary key of the main entity can’t be added without giving a different sort order to what the user asked for, so it’s not added and you won’t get a paging cookie back. You can still ask for the next page, but it will be retrieved less efficiently.
Sorting & Aliases
If you choose to give aliases to the attributes in your query (I looked at this in part 1 of this series, and recommended against it), be careful about sorting by it. If you execute this query:
you will get the expected results, but when you try to move on to the next page you’ll get the same records again. If you keep moving on to the next page you’ll get stuck in an infinite loop as you never actually move on to the next page.
If you have a look into the contents of the paging cookie you receive, you’ll see it shows that the last record in the page has a value of null for the fullname attribute, so the first record of the next page should be the next one that also has a null name or the first non-null value. This will obviously give the first page of data again.
I can see why this would happen – the record that was retrieved doesn’t have a fullname attribute, as it’s been aliased to name instead.
To avoid this problem, see my earlier advice and don’t use aliases unless you’re doing aggregate queries! If you must, add the attribute again without an alias, e.g.
As you edit your query, everything else now updates in real-time. No more clicking away to see your changes reflected in the query builder, FetchXML view or any other tool windows! Check it out in this video:
SQL 4 CDS Integration
If you’ve ever used the SQL Query window in FetchXML Builder, you might have noticed it missed various aspects of more complex queries. With the work I’ve done lately on the SQL 4 CDS tool it made sense to use that to enhance the SQL support in FXB too.
To get this to work you need to have SQL 4 CDS version 1.0.4 or later installed, then enable the integration in FXB under Options > Use SQL 4 CDS for SQL conversion. SQL 4 CDS will then be used to generate the SQL query instead of the built-in FXB logic:
As well as showing the query, you can also use the new Edit in SQL 4 CDS button. Edit your query as SQL, then bring it back as FetchXML to continue using all the other great FXB features like converting the query to Power Automate parameters.
So far we’ve looked at using the <attribute> and <link-entity> elements to control what columns to include in your query, but for a truly useful query you’ll likely want to select which rows to include instead of taking everything. Enter <filter>!
Filter or Condition?
A <filter> is a group of individual <condition>s. Each condition applies a filter on a single field. The job of the filter is to combine those conditions with an AND or OR.
The most common operator you’ll use will be eq, short for equals. However, there’s a huge range of operators you can use, especially if you’re filtering on date or user fields. I won’t list them all here, but you can find the full list in the Microsoft documentation. The main ones you’re likely to use are:
It’s worth highlighting that the like operator can be used on lookup fields as well as text fields – in this case it is applied to the primary name attribute of the related record, while eq will be applied to the unique identifier stored in the lookup field itself.
For date values there are many operators [olderthan/last/next]-x-[hours/days/weeks/months], e.g. last-x-days. The value you use in the condition becomes the “x”, e.g. to find records created in the last 3 days:
For user lookup fields, such as ownerid, there are various operators to check if the user matches the current user (eq-user), or any of the same teams the user is part of (eq-userteams, eq-useroruserteams). It can also look through the user hierarchy to match users below the current user in the user hierarchy (eq-useroruserhierarchy, eq-useroruserhierarchyandteams). You can also filter by the same business unit as the current user (eq-businessid)
While we’re talking about hierarchies, remember that any other entity type can also have a hierarchy defined, and you can use the above, under and similar operators to match any record in them rather than having to navigate them manually yourself.
There’s some more good information about querying date and hierarchy data in the Microsoft documentation.
All conditions compare a field to a specific value. Importantly, there is no condition to compare one field to another field.
Most conditions require a single value to be specified in the value attribute, such as the equals operator:
Conditions on lookup fields require the primary key guid value to filter on. If you build a query in Advanced Find and download the FetchXML you’ll also see the uiname and uitype attributes included in the condition to identify the name and type of the record respectively, but these are not used when the query is actually executed.
We saw some of the more advanced operators earlier, especially the date and user operators. These are particularly important when building views for your users to use on a regular basis.
For example, a view that shows active leads owned by the current user created in the last 7 days is probably more useful than one that shows leads created since a fixed date and owned by you, the author of the view.
Filtering Linked Entities
There are two ways filters can be applied to linked entities – within the <link-entity> element, or within the main <entity> element using the entityname attribute on the conditions. Both are valid, but do subtly different things.
When the filter is applied within the <link-entity>, the filter is applied to the entity before it is joined. By adding an entityname attribute to a condition within the main filter, the filter is applied after the join.
When using inner joins there is no practical difference in the results, but with outer joins these approaches can be combined to generate a query to find records that do not have any related records that match some criteria, e.g.:
This will retrieve all the accounts that do not have an active contact called Mark.
The list of contacts is filtered to only active Marks, and the list of accounts is linked to that filtered list using an outer join. As we looked at in the previous part of this series, the outer join type will include all the records from the first list (the accounts). If there are any matches from the second list (the contacts) then those are included, otherwise, all the contact fields in the result are left blank (null). The outer filter then restricts the final result to only those joined records that have a null contactid, and therefore didn’t find a matching contact.
Note that, if you give the link entity an alias, it is that alias you should use in the entityname attribute, not the actual logical name of the entity.
The main filter is equivalent to the SQL WHERE clause. For example, the first query in this post would be equivalent to:
WHERE statecode = 0
AND firstname = 'Mark'
The from and to attributes on a <link-entity> form an ON clause for a join, and a filter within the link entity extend that ON clause. The filter created by the from and to attributes will always be combined to the additional filter with an AND. For example, the query from earlier:
LEFT OUTER JOIN
ON account.accountid = contact.parentcustomerid
AND contact.statecode = 0
AND contact.firstname = 'Mark'
WHERE contact.contactid IS NULL;
The more advanced operators do not have any direct equivalent in SQL. Many of the date conditions compare values only on the date part and ignore the time part to make it easier for the user to build useful queries, but would require a range comparison in SQL. The hierarchical operators would likely require more complex CTEs, and are a great example of how FetchXML delivers a much more powerful application-level query language rather than just a thin veneer over SQL.
In SQL 4 CDS these operators can be used through the pattern of attribute = operator(value), e.g.
WHERE ownerid = equseroruserteams()
OR createdon = lastxweeks(4);
The part that always confuses me is the naming of the to and from attributes on the <link-entity> element – in my head they should be other way around, which is one reason why a tool such as SQL 4 CDS or FetchXML Builder is so useful to me.
Unlike SQL, which can have very flexible join criteria, FetchXML requires exactly one attribute on the source entity to exactly match one attribute on the target entity.
Although joins would normally be done on attributes that form a relationship, this isn’t a requirement. You can join on any attributes that are compatible, e.g. you could use the following to find leads that have the same email address as a contact:
For a great example of putting this type of join into practise, take a look at Jonas Rapp’s blog on creating a view to show contacts in the same state as the current user.
If you have a many-to-many (N:N) relationship you can’t join the entities directly, you have to join via the intermediate “intersect” entity. This is a hidden entity type that is created for you automatically and isn’t available for you to view directly in your app or even look at in Advanced Find, but you need to be aware of the details of these when you are building queries that navigate these relationships.
For example, contacts can be part of many marketing lists, and each marketing list can contain many contacts, so this forms a many-to-many relationship. The contact entity itself can’t hold a list of all the marketing lists it’s part of in a field of its own, as there’s no way for it to store a potentially unlimited number of lists. Similarly, the marketing list entity can’t store all its members directly. Instead, a third intersect entity is used to join the two, and a record of this entity type is created for each contact/marketing list pair:
With this data we can see there are two contacts and two marketing lists. The first contact is a member of one list, and the second contact is a member of both.
Querying this relationship to find a list of all contacts that are part of a marketing list, and the names of the lists they are part of, would look like:
To build this query you need to know the names of the intersect entity and the attributes in it to join on. You can find the intersect entity name from the relationship properties in the customisation screens, for example here in the Maker Portal:
The intersect entity is shown here in the “Relationship entity name” field. Unfortunately it doesn’t show the foreign key fields on this entity that link to the two main entities. To find those names you’ll need to use a tool such as SQL 4 CDS or Metadata Explorer.
With SQL 4 CDS, you can use the Object Explorer pane to help add the correct syntax for a join. Double-click on a relationship to add the join into your query. If you select an N:N relationship the join will include all three required entities. For example, the contact to marketing list relationship includes the listmember entity:
FetchXML supports two link types – inner and outer.
An inner join will include only records where there is a match across both entities. By comparison, outer join will include all the records from the first entity even if there is no match in the second entity.
We can modify the first query to include all accounts even when they don’t have a primary contact by adding the link-type="outer" attribute:
Each query can contain a maximum of 10 links. Hopefully this should be enough for most queries. There is no way (that I know of) to increase this limit, so if you do need more you’ll need to execute multiple separate queries and join the results in your own code.
Because the links are ultimately converted to SQL joins, having more joins than this would likely result in some inefficient queries as the database engine would start hitting query optimizer timeouts, so this seems like a reasonable limit.
The inner and outer link types are equivalent to the INNER JOIN and LEFT OUTER JOIN join types in SQL.
The from and to attributes in the link are equivalent to the ON clause of the join, and the alias attribute is equivalent to the AS table alias clause.
However, the FetchXML syntax is more restrictive than SQL as you can only use a single exact field match for the join criteria, rather than the complex criteria that you can use in a SQL ON clause (although it is possible to extend this a little further using a <filter> element which I’ll look at next time). Aliases can also only be applied to a <link-entity> and not the root <entity>.
In SQL 4 CDS, the first entity you reference in the FROM clause will be used as the main <entity> and any following joins will become <link-entity> elements. Although you can apply table aliases at any point, they will only be reflected in the generated FetchXML in the <link-entity> elements, e.g.:
FROM account AS a
LEFT OUTER JOIN
contact AS c
ON a.primarycontactid = c.contactid;
This will return the name and telephone number of accounts, and the name and number of the related primary contact.
Including all attributes
Because nothing is free, you should always include only the attributes you need in the result. It’s easy to include everything, just in case, but your queries will run noticeably slower.
If you really do want to include all the attributes, you can use the <all-attributes> element instead of lots of individual <attribute> elements to make your query easier to read. This will include the attributes from the <entity> or <link-entity> you add it to, not all the different entities in your query. If you have a query that is joining two entities and want all the attributes from both entities you’ll need an <all-attributes> element within the top-level <entity> and the <link-entity>
This will return the name and telephone number of accounts, and all attributes of the related primary contact.
The results returned by a FetchXML query will return a column for each attribute. By default, those columns have the same name as the attribute. For example, the first query would produce an output like:
0151 355 4555
+44 151 355 4555
You can choose to change these names using aliases. This becomes more useful when we look at aggregate queries, but you can still use them for simple queries too.
Without the aliases, the attributes retrieved from the linked entity were prefixed with the name of the entity. When you use an alias that prefix isn’t used, so you need to make sure that the alias names are unique across your query, not just within the individual entity.
Although you can apply aliases in this way, I wouldn’t recommend it simply because it makes it less obvious where the data has come from. When you execute the query the value will be wrapped in an AliasedValue object which gives this information, but why make life harder?
Aliases also don’t work well with the deprecated ExecuteFetchRequest message. If you’re using that, please look at moving to RetrieveMultiple instead. If you can’t for any reason you’ll find that aliased columns generally don’t appear in your results. The only time I’ve got them to appear is when the alias name matches another attribute name in the query, which is pretty useless. If you run a query in FetchXML Builder with the “Raw fetch result” view it uses this method, so don’t be surprised if you don’t get the expected results in there.
In SQL 4 CDS, attributes are included using the standard SELECT clause in your query. For example, the first query would be written in SQL as:
FROM account AS a
contact AS c
ON a.primarycontactid = c.contactid;
Aliases can be added using the AS keyword, e.g.:
SELECT a.name AS account,
a.telephone1 AS phone,
c.fullname AS name,
c.telephone1 AS tel
FROM account AS a
contact AS c
ON a.primarycontactid = c.contactid;
All attributes can be included using *:
FROM account AS a
contact AS c
ON a.primarycontactid = c.contactid;
You can mix and match selecting individual attributes from some entities and all attributes from others in a join as well:
FROM account AS a
contact AS c
ON a.primarycontactid = c.contactid;
Although SQL can support much more complex SELECT clauses, performing calculations and calling functions, these are not supported in FetchXML and therefore in SQL 4 CDS. If you try to write a query in SQL 4 CDS such as:
SELECT 1 + 1
SELECT dateadd(d, createdon, 1)
If you’re using it, please give it a rating while you’re there 🙏
The two main focus areas of this update are extended SQL compatibility and integration with FetchXML Builder.
Each FetchXML view now has an “Edit in FetchXML Builder” button at the top which allows you to switch to everyone’s favourite FetchXML editor and make use of all the power there of editing the query further or converting it into various other formats.
The SQL to FetchXML conversion now also understands a wider range of queries, including:
Additional filter criteria on joins
Improved aggregate support
Wider support for aliases
For the core query language of CDS, FetchXML seems poorly documented and understood. Many things I’ve picked up from old blogs or simply trial and error.
I’ve learned a lot about FetchXML while writing this tool, so look out for a mini-series on some of the SQL 4 CDS features and the weird and wonderful things that have driven me nuts over the past few months!
If you’re anything like me you probably find querying the data in CDS a pain, either using the Advanced Find interface or writing FetchXml. Tools such as FetchXMLBuilder help a lot, but I still think about the query I want to write in good-old SQL and then have to translate it into FetchXML.
To make my life easier I created a tool to do the translation for me, and now I’ve released it as an XmlToolBox tool for you to use too – SQL 4 CDS!
Write your query as SQL and either execute it straight away or see it converted to FetchXml for you to use elsewhere.