MSCRM ColumnSet XSD: fields, sorting & filtering

Many of the MSCRM .Retrieve*() methods take an optional ColumnSet string parameter that is normally set to an empty string (""). Most of the samples in the SDK never uses or explains what this parameter is used for, and you might easily think that it is used only for specifying which columns (fields) that should be returned in the result set.

It was not until I needed to sort the result set, that I started to look for how to make the MSCRM services perform sorting, to avoid sorting the result set with a DataView connected to our typed DataSet. By chance, I browsed to the schema of the ColumnSet parameter, and found out that it allows you to specify several clauses for the retrieve operation: select fields, specify sorting, apply filters, etc.

This is an example of how to retrieve a light-weight, sorted and filtered list of sub-accounts:

//fields, sorting, filter
string colset = "<columnset>";
colset += "<column>accountid</column>";
colset += "<column>name</column>";
colset += "<column>emailaddress1</column>";
colset += "<column>ownerid</column>";
//sort on name
colset += "<ascend>name</ascend>";
//only active accounts
colset += "<filter column='statecode' operator='eq' value='" + Microsoft.Crm.Platform.Types.ACCOUNT_STATE.AS_ACTIVE + "' />";
colset += "</colset>";

//retrieve sub accounts
_crmAccount.RetrieveSubAccounts(_crmUserAuth, accountId, colset)

Note that you must specify one or more <column> elements to get data back, as the MSCRM services will return no fields if you do not specify any.

The services do, however, behave very nicely when specifying a column that is a relation to e.g. the biz user that owns an account (ownerid), as not only the owner GUID is returned, but it is also annotated with XML attributes containing the full name of the owner, etc. These extra attributes saves you from doing extra lookups in MSCRM to present human-readable data to the user.

Refer to the SDK for more information about the ColumnSet XML Schema.