SQL 4 CDS 3.0.0 Released

I’m pleased to announce that I’ve just released version 3 of SQL 4 CDS. The main updates in this release are:

• Metadata queries
• Support for virtual (___name) attributes

Metadata queries

Since it’s initial release, SQL 4 CDS has queried standard entity data by converting your SQL to a FetchXML request. This release adds support for queries against metadata too, so you can write a query like:

SELECT entitylogicalname,
       logicalname
FROM   attribute
WHERE  description IS NULL
       AND attributeof IS NULL
       AND iscustomattribute = 1

This will show you the names of any of your custom attributes where you haven’t filled in the description – a useful quality check before you deploy your customizations.

Another useful example might be:

SELECT attr1.entitylogicalname,
       attr1.logicalname,
       attr2.logicalname,
       attr1.displayname
FROM   attribute AS attr1
       INNER JOIN
       attribute AS attr2
       ON attr1.entitylogicalname = attr2.entitylogicalname
WHERE  attr1.displayname = attr2.displayname
       AND attr1.logicalname <> attr2.logicalname
       AND attr1.iscustomattribute = 1
       AND attr1.displayname <> ''

to show one of my pet hates – two attributes with the same name.

As well as attribute, you can also get details on specific types of attributes. For example, to find all long text fields you could use:

SELECT   entitylogicalname,
         logicalname,
         maxlength
FROM     stringattribute
WHERE    maxlength >= 4000
ORDER BY maxlength DESC

You can also access metadata of entities, one-to-many and many-to-many relationships, translations and optionsets.

Translations

For text that can be translated, such as the display name of an attribute, you can get your own localized version by accessing the displayname attribute:

SELECT displayname
FROM   attribute

To get details of different translations you can join the localizedlabel table to the corresponding displaynameid attribute:

SELECT attr.entitylogicalname,
       attr.logicalname,
       l.languagecode,
       l.label
FROM   attribute AS attr
       LEFT OUTER JOIN
       localizedlabel AS l
       ON attr.displaynameid = l.labelid

Limitations

Each query must have a single data source, which can be either:

• FetchXML Query
• RetrieveMetadataChangesRequest
• RetrieveAllOptionSetsRequest

You can’t mix and match within a single query.

As this is the first release of the metadata querying feature I’m expecting there to be a few bugs – please let me know if you encounter any queries you’d expect to work but don’t!

Virtual Attributes

You might have noticed that you can apparently retrieve ___name fields for optionset and lookup fields, which look like they should contain the display name of the selected value but always appear blank. This release makes these available to select, filter and sort by. You can now correctly run a query like:

SELECT   accountid,
         name,
         industrycodename
FROM     account
WHERE    industrycodename IN ('Accounting', 'Brokers')
ORDER BY industrycodename

and it will be converted to:

<fetch xmlns:generator="MarkMpn.SQL4CDS">
  <entity name="account">
    <attribute name="accountid" />
    <attribute name="name" />
    <attribute name="industrycode" />
    <filter>
      <condition attribute="industrycode" operator="in">
        <value>1</value>
        <value>4</value>
      </condition>
    </filter>
    <order attribute="industrycode" />
  </entity>
</fetch>

With this you can now see both the display name and underlying value for optionset and lookup fields without having to switch your options in the Settings dialog and re-run the query each time, as well as being easier to write the filter without having to look up the optionset values yourself.

Where possible the SQL will be converted to a native FetchXML query like this, but you can also run queries like:

SELECT   accountid,
         name,
         industrycodename
FROM     account
WHERE    industrycodename LIKE 'Account%'
ORDER BY industrycodename

that needs some extra processing within SQL 4 CDS itself.

Filters on the display name of lookup attributes are also supported, e.g.

SELECT name
FROM   account
WHERE  primarycontactidname = 'Mark Carrington'

is converted to

<fetch xmlns:generator="MarkMpn.SQL4CDS">
  <entity name="account">
    <attribute name="name" />
    <link-entity name="contact" to="primarycontactid" from="contactid" alias="account_primarycontactid" link-type="outer" />
    <filter>
      <condition attribute="fullname" entityname="account_primarycontactid" operator="eq" value="Mark Carrington" />
    </filter>
  </entity>
</fetch>

Again, please let me know if you find any queries where this doesn’t work as expected!