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
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
For text that can be translated, such as the display name of an attribute, you can get your own localized version by accessing the
SELECT displayname FROM attribute
To get details of different translations you can join the
localizedlabel table to the corresponding
SELECT attr.entitylogicalname, attr.logicalname, l.languagecode, l.label FROM attribute AS attr LEFT OUTER JOIN localizedlabel AS l ON attr.displaynameid = l.labelid
Each query must have a single data source, which can be either:
- FetchXML Query
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!
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!