SQL 4 CDS 1.0.9 Released!

I’ve just released SQL 4 CDS 1.0.9. Please update it when XrmToolBox prompts you to take advantage of these latest features:

Open Source!

I’m very pleased to be able to release SQL 4 CDS as open source, thanks to the support of Data8. You can now grab all the code on GitHub, submit your bug reports or suggestions as issues or even open a pull request with your own changes!

As well as the XrmToolBox tool, I’ve also separated out the core SQL ⇔ FetchXML conversions into the MarkMpn.Sql4Cds.Engine NuGet package you can include in your own projects. A simple code example to convert from SQL to FetchXML would look like:

public string ConvertSqlToFetchXml(IOrganizationService org, string selectStatement)
{
  var metadataCache = new AttributeMetadataCache(org);
  var converter = new Sql2FetchXml(metadataCache, true);
  var queries = converter.Convert(selectStatement);

  // queries could contain many queries, including SELECT, INSERT, UPDATE or DELETE
  // We'll assume it's just a single SELECT statement for now
  var select = (SelectQuery) queries[0];

  // The FetchXML is returned as an object model, convert it to a string using XmlSerializer
  using (var writer = new StringWriter())
  {
    var serializer = new XmlSerializer(typeof(FetchType));
    serializer.Serialize(select.FetchXml);

    return writer.ToString();
  }
}

XrmToolBox 1.2020.2.36

If you get an error when opening SQL 4 CDS:

An error occured when trying to display this tool: Method not found: 'McTools.Xrm.Connection.ConnectionDetail ConnectionUpdatedEventArgs.get_ConnectionDetail()'.

after installing the latest version of XrmToolBox, please update SQL 4 CDS to 1.0.9 as well to fix it.

Cancel running queries

If you need to stop a query while it’s running, just click the 🟥 button in the toolbar to stop it.

Improved results display

The grid view showing the results of a query is improved with:

  • total record count at the bottom
  • record numbers on the left
  • right-click options to copy data with or without headers
  • click on lookup guid values to open the related record or quickly create a SELECT query to get the details of the record
  • columns in a SELECT * query are sorted by name to make it easier to find what you’re looking for
  • option to show lookup values as the name of the related record instead of the GUID
  • option to show times as your local time zone instead of UTC

Improved query support

You will now get the expected results when running queries that combine * and a field name in the SELECT clause, e.g. SELECT name, * FROM account, and when ordering a SELECT * query, e.g. SELECT * FROM account ORDER BY name

Double quotes

By default, SQL treats double quotes as enclosing the name of a table or column, and single quotes for strings, so you can’t write:

SELECT name
FROM   account
WHERE  telephone1 = "01513554555"

as the account entity doesn’t contain a field called 01513554555

You can now switch this behaviour so double quotes and single quotes are both used to enclose strings by unticking the new “Quoted Identifiers” option in the settings window.

Feedback

Please open an issue in the GitHub repository to let me know of any problems you encounter or with any suggestions you might have for improvement.

Please also use the rating feature in XrmToolBox to let me know what you think!

FetchXML Multiple Links

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 the “thank you” page?

Multiple Links

The key with these sorts of queries is that each question you want to ask of the related entity requires a separate link. Taking the first example:

Invoices that include product A AND product B?

If we write this as:

Advanced Find

Finding invoices with two products – attempt 1!

FetchXML

<fetch xmlns:generator="MarkMpn.SQL4CDS">
  <entity name="invoice">
    <link-entity name="invoicedetail" to="invoiceid" from="invoiceid" alias="id" link-type="inner">
      <link-entity name="product" to="productid" from="productid" alias="p" link-type="inner">
        <all-attributes />
      </link-entity>
      <all-attributes />
    </link-entity>
    <all-attributes />
    <filter>
      <condition attribute="productnumber" entityname="p" operator="eq" value="A" />
      <condition attribute="productnumber" entityname="p" operator="eq" value="B" />
    </filter>
  </entity>
</fetch>

SQL

SELECT *
FROM   invoice AS i
       INNER JOIN
       invoicedetail AS id
       ON i.invoiceid = id.invoiceid
       INNER JOIN
       product AS p
       ON id.productid = p.productid
WHERE  p.productnumber = 'A'
       AND p.productnumber = 'B';

This is a common mistake I see, and one you can make easily if you try to build this query in Advanced Find. When the query is run, it tries to find invoices that have an invoice item that has a product that has the product number “A” and “B”. Since it’s impossible for a product to have two different product numbers in the same field, you’ll never get any results for this query.

The key change we need to make is to have two separate links to the invoicedetail entity, one filtered for product A and the other for product B:

FetchXML

<fetch xmlns:generator="MarkMpn.SQL4CDS">
  <entity name="invoice">
    <link-entity name="invoicedetail" to="invoiceid" from="invoiceid" alias="id_a" link-type="inner">
      <link-entity name="product" to="productid" from="productid" alias="p_a" link-type="inner">
        <filter>
          <condition attribute="productnumber" operator="eq" value="A" />
        </filter>
        <all-attributes />
      </link-entity>
      <all-attributes />
    </link-entity>
    <link-entity name="invoicedetail" to="invoiceid" from="invoiceid" alias="id_b" link-type="inner">
      <link-entity name="product" to="productid" from="productid" alias="p_b" link-type="inner">
        <filter>
          <condition attribute="productnumber" operator="eq" value="B" />
        </filter>
        <all-attributes />
      </link-entity>
      <all-attributes />
    </link-entity>
    <all-attributes />
  </entity>
</fetch>

SQL

SELECT *
FROM   invoice AS i
       INNER JOIN
       invoicedetail AS id_a
       ON i.invoiceid = id_a.invoiceid
       INNER JOIN
       product AS p_a
       ON id_a.productid = p_a.productid and p_a.productnumber = 'A'
       INNER JOIN
       invoicedetail AS id_b
       ON i.invoiceid = id_b.invoiceid
       INNER JOIN
       product AS p_b
       ON id_b.productid = p_b.productid and p_b.productnumber = 'B';

With this change the query will now find invoices that have an invoice item that has a product that has the product number “A”, and also an invoice item that has a product that has the product number “B”. It’s more long-winded to write but gets us to the right answer.

Unfortunately it’s not possible to build this query in Advanced Find, so if you need to use a query like this in your app you’ll need to get the query ready in FetchXML Builder and save it from there as a view or marketing list (but see the workaround at the end).

Combining with “Not In” Queries

Taking another example:

Visits that have a page view of the checkout page BUT NOT the “thank you” page?

If you’re using ClickDimensions you get a lot of powerful web tracking information recorded in custom entities. Two of these are Page View, which records an individual web page that someone has viewed, and Visit, which represents a session that can cover multiple pages. Each Page View is linked to a Visit. We can find abandoned shopping carts by querying these to find sessions which include a visit to the shopping cart page but not to the page that it shown after a purchase is complete. This makes use of the “not in” pattern of using a left outer join and null query.

FetchXML

<fetch xmlns:generator="MarkMpn.SQL4CDS">
  <entity name="cdi_visit">
    <link-entity name="cdi_pageview" to="cdi_visitid" from="cdi_visitid" alias="checkout" link-type="inner">
      <filter>
        <condition attribute="cdi_uri" operator="eq" value="https://example.com/checkout" />
      </filter>
      <all-attributes />
    </link-entity>
    <link-entity name="cdi_pageview" to="cdi_visitid" from="cdi_visitid" alias="finished" link-type="outer">
      <filter>
        <condition attribute="cdi_uri" operator="eq" value="https://example.com/checkout/thankyou" />
      </filter>
      <all-attributes />
    </link-entity>
    <all-attributes />
    <filter>
      <condition attribute="cdi_pageviewid" entityname="finished" operator="null" />
    </filter>
  </entity>
</fetch>

SQL

SELECT *
FROM   cdi_visit AS v
       INNER JOIN
       cdi_pageview AS checkout
       ON v.cdi_visitid = checkout.cdi_visitid
          AND checkout.cdi_uri = 'https://example.com/checkout'
       LEFT OUTER JOIN
       cdi_pageview AS finished
       ON v.cdi_visitid = finished.cdi_visitid
          AND finished.cdi_uri = 'https://example.com/checkout/thankyou'
WHERE  finished.cdi_pageviewid IS NULL;

“All” / “Only” Queries

There’s also a related type of query that checks if something is true for all related entities, e.g.:

  • Accounts that only have contacts with an info@ email address?
  • Users that have sales on all their accounts this month?

It’s actually possible to re-phrase these queries into the same pattern as above of asking two separate questions of the link entity. For example:

Accounts that only have contacts with an info@ email address?

You could re-write this as:

Accounts that have a contact with an info@ email address and don’t have any contacts that don’t have an info@ email address?

Now we can apply the same pattern again:

FetchXML

<fetch xmlns:generator="MarkMpn.SQL4CDS">
  <entity name="account">
    <link-entity name="contact" to="accountid" from="parentcustomerid" alias="info" link-type="inner">
      <filter>
        <condition attribute="emailaddress1" operator="like" value="info@%" />
      </filter>
      <all-attributes />
    </link-entity>
    <link-entity name="contact" to="accountid" from="parentcustomerid" alias="noninfo" link-type="outer">
      <filter>
        <condition attribute="emailaddress1" operator="not-like" value="info@%" />
      </filter>
      <all-attributes />
    </link-entity>
    <all-attributes />
    <filter>
      <condition attribute="contactid" entityname="noninfo" operator="null" />
    </filter>
  </entity>
</fetch>

SQL

SELECT *
FROM   account AS a
       INNER JOIN
       contact AS info
       ON a.accountid = info.parentcustomerid
          AND info.emailaddress1 LIKE 'info@%'
       LEFT OUTER JOIN
       contact AS noninfo
       ON a.accountid = noninfo.parentcustomerid
          AND noninfo.emailaddress1 NOT LIKE 'info@%'
WHERE  noninfo.contactid IS NULL;

Advanced Find

I mentioned earlier that you couldn’t build these queries in Advanced Find, and that’s mostly true. If you try to create one of these queries exactly as I’ve shown you’ll get an error when you try to add the second instance of the related entity:

The relationship you are adding already exists in the query.

But with a bit of creativity we can switch the query around to something that Advanced Find can handle, although as a database person it’s not as “nice”.

Instead of having our main entity listed only once, we can join back to it from the related entity. Instead of joining from invoice to invoicedetail twice, we join from invoice to invoicedetail, back to invoice and on to invoicedetail again:


Finding invoices with two products – attempt 2. Longer but gets the right answer!

This works fine for this case, but you may not get the results you want if you try this in combination with a “not in” type query. That would also apply to all the other link entities you chain off it, and could have the effect of changing an “in” query to a “not in”.

SQL 4 CDS 1.0.6 Released!

I’m very pleased to announce the release of SQL 4 CDS version 1.0.6! Please update your existing versions in XrmToolBox to get some great new features…

COUNT(DISTINCT) Support

You can now use the count(DISTINCT col) aggregate function, e.g.:

SELECT count(DISTINCT firstname) AS distinct_names,
       count(firstname) AS names,
       count(*) AS contacts
FROM   contact
WHERE  statecode = 0

This will get the number of different first names, the number of active contacts with a first name and the total number of active contacts, and gets translated to the following FetchXML:

<fetch xmlns:generator="MarkMpn.SQL4CDS" aggregate="true">
  <entity name="contact">
    <attribute name="firstname" alias="distinct_names" aggregate="countcolumn" distinct="true" />
    <attribute name="firstname" alias="names" aggregate="countcolumn" />
    <attribute name="contactid" alias="contacts" aggregate="count" />
    <filter>
      <condition attribute="statecode" operator="eq" value="0" />
    </filter>
  </entity>
</fetch>

DATEPART support

You can also now group data using the DATEPART function, e.g. find out how many new leads you’ve received each month using:

SELECT   datepart(year, createdon) AS year,
         datepart(month, createdon) AS month,
         count(*) AS leads
FROM     lead
GROUP BY datepart(year, createdon), datepart(month, createdon)
ORDER BY 1, 2

This returns the year number, month number and the number of leads created in that month. The results are returned in date order. This translates to the following FetchXML:

<fetch xmlns:generator="MarkMpn.SQL4CDS" aggregate="true">
  <entity name="lead">
    <attribute name="createdon" alias="year" groupby="true" dategrouping="year" />
    <attribute name="createdon" alias="month" groupby="true" dategrouping="month" />
    <attribute name="leadid" alias="leads" aggregate="count" />
    <order alias="year" />
    <order alias="month" />
  </entity>
</fetch>

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
FROM     lead
GROUP BY datepart(fiscalyear, createdon), datepart(fiscalmonth, createdon)
ORDER BY 1, 2

Sorting on link entities

You can now sort your query based on a field from a joined table, e.g.:

SELECT   c.firstname,
         a.name
FROM     contact AS c
         INNER JOIN
         account AS a
         ON c.parentcustomerid = a.accountid
ORDER BY c.firstname, a.name

Remember though that you can’t order by the joined table first and then the first table, so you can’t do:

SELECT   c.firstname,
         a.name
FROM     contact AS c
         INNER JOIN
         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:

Control UPDATE and DELETE batch size

Bug Fixes

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

Inside FetchXML pt 6 – datetime grouping

Last time I looked at aggregates, but unforgivably I missed out how to apply grouping to date/time attributes:

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:

<fetch aggregate="true">
  <entity name="contact">
    <attribute name="createdon" alias="month" groupby="true" dategrouping="month" />
    <attribute name="contactid" alias="occurrences" aggregate="count" />
    <filter>
      <condition attribute="statecode" operator="eq" value="0" />
    </filter>
    <order alias="occurrences" descending="true" />
  </entity>
</fetch>

This example will get the number of contacts created each month.

Available Groupings

Date/time values can be grouped by:

  • day
  • week
  • month
  • quarter
  • year
  • fiscal period
  • fiscal year

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:

<fetch aggregate="true">
  <entity name="contact">
    <attribute name="createdon" alias="year" groupby="true" dategrouping="year" />
    <attribute name="createdon" alias="month" groupby="true" dategrouping="month" />
    <attribute name="contactid" alias="occurrences" aggregate="count" />
    <filter>
      <condition attribute="statecode" operator="eq" value="0" />
    </filter>
    <order alias="occurrences" descending="true" />
  </entity>
</fetch>

For week grouping you get a number 1 – 53, quarter is 1 – 4 and day is 1 – 31.

SQL Equivalents

These date/time grouping options are equivalent to using the DATEPART function in SQL:

SELECT   DATEPART(month, createdon) AS month,
         count(*) AS count
FROM     contact
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.

Inside FetchXML pt 5 – aggregate

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:

<fetch aggregate="true">
  <entity name="contact">
    <attribute name="firstname" alias="firstname" groupby="true" />
    <attribute name="contactid" alias="occurrences" aggregate="count" />
    <filter>
      <condition attribute="statecode" operator="eq" value="0" />
    </filter>
    <order alias="occurrences" descending="true" />
  </entity>
</fetch>

There are 4 main points to note that are different from a regular query:

  1. The <fetch> element has the aggregate="true" attribute
  2. 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.
  3. All the <attribute> elements also have EITHER a groupby="true" or aggregate="xxx" attribute
  4. 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.

Available Aggregates

There are only a few aggregate functions you can apply:

  • count
  • countcolumn
  • min
  • max
  • sum
  • avg

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.

Distinct Counts

The countcolumn aggregate also supports a distinct attribute. This lets you get the number of different values in that column, e.g.

<fetch aggregate="true">
  <entity name="contact">
    <attribute name="address1_country" alias="country" groupby="true" />
    <attribute name="address1_city" alias="cities" aggregate="countcolumn" distinct="true" />
    <filter>
      <condition attribute="statecode" operator="eq" value="0" />
    </filter>
    <order alias="cities" descending="true" />
  </entity>
</fetch>

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.

Filtering

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.

Grouping

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.

<fetch aggregate="true">
  <entity name="opportunity">
    <attribute name="opportunityid" alias="count" aggregate="count" />
    <attribute name="estimatedvalue" alias="min" aggregate="min" />
    <attribute name="estimatedvalue" alias="max" aggregate="max" />
    <filter>
      <condition attribute="statecode" operator="eq" value="0" />
    </filter>
  </entity>
</fetch>

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:

<fetch aggregate="true">
  <entity name="opportunity">
    <link-entity name="systemuser" to="ownerid" from="systemuserid" link-type="inner">
      <attribute name="fullname" alias="fullname" groupby="true" />
      <order alias="fullname" />
    </link-entity>
    <attribute name="estimatedvalue" alias="value" aggregate="sum" />
  </entity>
</fetch>

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.

Ordering

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!

Limitations

There are a few limitations to aggregate queries you should be aware of

  1. 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
  2. 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.

Alternatives

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.

<fetch aggregate="true">
  <entity name="contact">
    <attribute name="contactid" alias="count" aggregate="count" />
  </entity>
</fetch>

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.

SQL Equivalents

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.

Inside FetchXML pt 4 – order

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.

<fetch>
  <entity name="contact">
    <attribute name="fullname" />
    <order attribute="fullname" />
  </entity>
</fetch>

Sorting by multiple fields

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.

<fetch>
  <entity name="contact">
    <attribute name="fullname" />
    <order attribute="fullname" />
    <order attribute="createdon" />
  </entity>
</fetch>

This will order the results by fullname, then by createdon. In this way we can ensure that two records with the same name will be sorted so that the earliest created record comes before later records.

Reversing ordering

Each <order> is sorted in ascending order by default, so numerically lower, alphabetically earlier or earlier date values come first.

This ordering is reversible by applying the descending attribute. This can be selectively applied to individual <order> elements, so some sorts are applied in ascending order and others descending.

<fetch>
  <entity name="contact">
    <attribute name="fullname" />
    <order attribute="fullname" />
    <order attribute="createdon" descending="true" />
  </entity>
</fetch>

In this way we can reverse the previous logic and prefer a more recent record over older ones where two have the same name, but keep the alphabetical ordering.

Sorting on lookup fields

You might expect an ordering applied to a lookup field to sort by the guid value in the lookup field itself. Indeed, you can write a sort as:

<fetch>
  <entity name="account">
    <attribute name="accountid" />
    <attribute name="primarycontactid" />
    <link-entity name="contact" to="primarycontactid" from="contactid">
      <attribute name="fullname" />
    </link-entity>
    <order attribute="primarycontactid" />
  </entity>
</fetch>

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:

<fetch>
  <entity name="account">
    <attribute name="accountid" />
    <attribute name="primarycontactid" />
    <link-entity name="contact" to="primarycontactid" from="contactid">
      <attribute name="fullname" />
      <order attribute="contactid" />
    </link-entity>
  </entity>
</fetch>

Sorting on linked entities

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:

<fetch>
  <entity name="account">
    <attribute name="accountid" />
    <link-entity name="contact" to="primarycontactid" from="contactid">
      <link-entity name="systemuser" to="ownerid" from="systemuserid">
        <attribute name="fullname" />
        <order attribute="fullname" />
      </link-entity>
      <attribute name="fullname" />
      <order attribute="fullname" />
    </link-entity>
    <link-entity name="systemuser" to="ownerid" from="systemuserid">
      <attribute name="fullname" />
      <order attribute="fullname" />
    </link-entity>
    <order attribute="name" />
    <order attribute="createdon" />
  </entity>
</fetch>

CDS sorts the results by:

  • Account name
  • Account creation date
  • Primary contact name
  • Primary contact owner name
  • Account owner name

In programming terms, this is a depth first search.

Sorting & Paging

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:

<fetch>
  <entity name="contact">
    <attribute name="fullname" alias="name" />
    <order attribute="fullname" />
  </entity>
</fetch>

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.

<fetch>
  <entity name="contact">
    <attribute name="fullname" alias="name" />
    <attribute name="fullname" />
    <order attribute="fullname" />
  </entity>
</fetch>

When CDS calculates the paging cookie to give back, the final record in the page will have a fullname value to use and therefore the cookie will correctly identify where to start the next page.

FetchXML Builder Updates

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 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:

FetchXML Builder / SQL 4 CDS integration

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.

Inside FetchXML pt 3 – filter

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.

<fetch>
  <entity name="contact">
    <attribute name="fullname" />
    <filter type="and">
      <condition attribute="statecode" operator="eq" value="0" />
      <condition attribute="firstname" operator="eq" value="Mark" />
    </filter>
  </entity>
</fetch>

This query will retrieve the names of all contacts that are active (statecode = 0) and have a first name of Mark.

Nesting Filters

As well as containing conditions, filters can also contain other filters. This lets you create complex criteria by combining AND and OR logic:

<fetch>
  <entity name="contact">
    <attribute name="fullname" />
    <filter type="and">
      <condition attribute="statecode" operator="eq" value="0" />
      <filter type="or">
        <condition attribute="createdon" operator="on-or-after" value="2019-01-01" />
        <condition attribute="createdby" operator="eq" value="8109eeaf-44d8-4a9b-8c7a-95209b26f6e5" />
      </filter>
    </filter>
  </entity>
</fetch>

This will retrieve only contacts that are:

  1. active, AND
  2. at least one of
    1. created no earlier than the start of 2019, OR
    2. created by a specific user

Complex Operators

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:

  • eq
  • ne
  • null
  • not-null
  • like

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:

<condition attribute="createdon" operator="last-x-days" value="3" />

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.

Condition Values

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:

<condition attribute="statecode" operator="eq" value="0" />

For some the value is implicit in the operator, so no value is required:

<condition attribute="primarycontactid" operator="null" />

A few can work with more than one value:

<condition attribute="statuscode" operator="in">
  <value>1</value>
  <value>4</value>
  <value>10</value>
</condition>

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.

Dynamic Values

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.:

<fetch>
  <entity name="account">
    <attribute name="name" />
    <link-entity name="contact" to="accountid" from="parentcustomerid" link-type="outer">
      <filter>
        <condition attribute="statecode" operator="eq" value="0" />
        <condition attribute="firstname" operator="eq" value="Mark" />
      </filter>
    </link-entity>
    <filter>
      <condition attribute="contactid" entityname="contact" operator="null" />
    </filter>
  </entity>
</fetch>

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.

Combining filters and joins to find records without related records

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.

SQL Equivalents

The main filter is equivalent to the SQL WHERE clause. For example, the first query in this post would be equivalent to:

SELECT fullname
FROM   contact
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:

<fetch>
  <entity name="account">
    <attribute name="name" />
    <link-entity name="contact" to="accountid" from="parentcustomerid" alias="contact" link-type="outer">
      <filter>
        <condition attribute="statecode" operator="eq" value="0" />
        <condition attribute="firstname" operator="eq" value="Mark" />
      </filter>
    </link-entity>
    <filter>
      <condition attribute="contactid" entityname="contact" operator="null" />
    </filter>
  </entity>
</fetch>

can be written in SQL as:

SELECT account.name
FROM   account
       LEFT OUTER JOIN
       contact
       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.

SELECT fullname
FROM   contact
WHERE  ownerid = equseroruserteams()
       OR createdon = lastxweeks(4);
<fetch xmlns:generator="MarkMpn.SQL4CDS">
  <entity name="contact">
    <attribute name="fullname" />
    <filter type="or">
      <condition attribute="ownerid" operator="eq-useroruserteams" />
      <condition attribute="createdon" operator="last-x-weeks" value="4" />
    </filter>
  </entity>
</fetch>

Inside FetchXML pt 2 – link-entity

To combine data from different records in your query you need to use the <link-entity> element. This is equivalent to a join in SQL.

Each <link-entity> needs 3 key pieces of information:

  1. The entity type to link to
  2. The attribute in the main entity to join from
  3. The attribute in the new entity to join to
<fetch>
  <entity name="account">
    <attribute name="name" />
    <link-entity name="contact" to="primarycontactid" from="contactid">
      <attribute name="fullname" />
    </link-entity>
  </entity>
</fetch>

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.

Link Attributes

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:

<fetch>
  <entity name="lead">
    <link-entity name="contact" to="emailaddress1" from="emailaddress1">
      <attribute name="fullname" />
    </link-entity>
    <attribute name="fullname" />
  </entity>
</fetch>

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.

Many-to-Many Relationships

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:

Contact-Marketing List many-to-many relationship diagram
contactidfullname
1Mark Carrington
2Joe Bloggs
entityidlistid
11
21
22
listidlistname
1Latest Contacts
2Imaginary Contacts

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:

<fetch>
  <entity name="contact">
    <link-entity name="listmember" to="contactid" from="entityid">
      <link-entity name="list" to="listid" from="listid">
        <attribute name="listname" />
      </link-entity>
    </link-entity>
    <attribute name="fullname" />
  </entity>
</fetch>

Finding the intersect details

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:

Intersect entity name shown in 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:

Generate many-to-many join in SQL 4 CDS

Link Types

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:

<fetch>
  <entity name="account">
    <attribute name="name" />
    <link-entity name="contact" to="primarycontactid" from="contactid" link-type="outer">
      <attribute name="fullname" />
    </link-entity>
  </entity>
</fetch>

Link Aliases

When a query with a <link-entity> is executed, the attributes retrieved from the link are given a prefix of the entity name, e.g.:

namecontact.fullname
Data8 LtdMark Carrington

As I showed in the post on the <attribute> element, this prefix can be removed by giving the attribute itself an alias.

Another option is to give the <link-entity> an alias, which changes the prefix, e.g.:

<fetch>
  <entity name="account">
    <attribute name="name" />
    <link-entity name="contact" to="primarycontactid" from="contactid" alias="person">
      <attribute name="fullname" />
    </link-entity>
  </entity>
</fetch>
nameperson.fullname
Data8 LtdMark Carrington

Limits

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.

SQL Equivalents

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.:

SELECT a.name,
       c.fullname
FROM   account AS a
       LEFT OUTER JOIN
       contact AS c
       ON a.primarycontactid = c.contactid;

is translated to:

<fetch xmlns:generator="MarkMpn.SQL4CDS">
  <entity name="account">
    <link-entity name="contact" to="primarycontactid" from="contactid" alias="c" link-type="outer">
      <attribute name="fullname" />
    </link-entity>
    <attribute name="name" />
  </entity>
</fetch>

Next time I’ll take a look at the <filter> element and particularly see how that can be combined with <link-entity>.

Inside FetchXML pt 1 – attribute

As promised, I’m starting a series of blog posts covering various aspects of FetchXML and the humble <attribute> element seems like a good place to start.

Simple SQL and FetchXML query retrieving attributes

The purpose of running a FetchXML query is to get some data back, and the <attribute> element defines which attributes get included in the result.

You can add as many <attribute> elements to your query as you need, and you can add them to your main <entity> and any <link-entity> elements.

<fetch>
  <entity name="account">
    <attribute name="name" />
    <attribute name="telephone1" />
    <link-entity name="contact" to="primarycontactid" from="contactid" link-type="inner">
      <attribute name="fullname" />
      <attribute name="telephone1" />
    </link-entity>
  </entity>
</fetch>

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>

<fetch>
  <entity name="account">
    <attribute name="name" />
    <attribute name="telephone1" />
    <link-entity name="contact" to="primarycontactid" from="contactid" link-type="inner">
      <all-attributes />
    </link-entity>
  </entity>
</fetch>

This will return the name and telephone number of accounts, and all attributes of the related primary contact.

Aliasing columns

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:

nametelephone1contact.fullnamecontact.telephone1
Data8 Ltd0151 355 4555Mark Carrington+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.

<fetch>
  <entity name="account">
    <attribute name="name" alias="account" />
    <attribute name="telephone1" alias="phone" />
    <link-entity name="contact" to="primarycontactid" from="contactid" link-type="inner">
      <attribute name="fullname" alias="name" />
      <attribute name="telephone1" alias="tel" />
    </link-entity>
  </entity>
</fetch>
accountphonenametel
Data8 Ltd0151 355 4555Mark Carrington+44 151 355 4555

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.

SQL equivalents

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:

SELECT a.name,
       a.telephone1,
       c.fullname,
       c.telephone1
FROM   account AS a
       INNER JOIN
       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
       INNER JOIN
       contact AS c
       ON a.primarycontactid = c.contactid;

All attributes can be included using *:

SELECT *
FROM   account AS a
       INNER JOIN
       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:

SELECT a.*,
       c.fullname
FROM   account AS a
       INNER JOIN
       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
FROM   account;

SELECT dateadd(d, createdon, 1)
FROM   account;

you’ll get an error from SQL 4 CDS:

Unhandled SELECT clause: 1 + 1
Unhandled function: dateadd(d, createdon, 1)

Next time I’ll take a closer look at the <link-entity> / JOIN syntax for linking multiple entities.