Multi-select Picklist Filtering

I’ve been doing some work today on filtering multi-select optionset fields in SQL 4 CDS and using the T-SQL endpoint, and I came across a few interesting quirks.

FetchXML

There are two specialised FetchXML operators for multi-select picklist fields: contain-values and not-contain-values. You can use these from Advanced Find or FetchXML Builder, and select all the values you’re searching for in one go:

In FetchXML this looks like:

<fetch>
  <entity name="account">
    <attribute name="name" />
    <attribute name="markmpn_multiselecttest" />
    <filter>
      <condition attribute="markmpn_multiselecttest" condition="contain-values">
        <value>1</value>
        <value>2</value>
      </condition>
    </filter>
  </entity>
</fetch>

Somewhat surprisingly though, this will get accounts that have either of these values selected, rather than requiring both:

OData

What, you want to write an OData query directly instead of using FetchXML Builder to convert from FetchXML to OData for you? Well, if you insist, you can use the following syntax:

https://contoso.crm.dynamics.com/api/data/v9.1/accounts?$select=name,markmpn_multiselecttest&$filter=Microsoft.Dynamics.CRM.ContainValues(PropertyName='markmpn_multiselecttest',PropertyValues=['1','2'])

SQL

Behind the scenes the data is stored as a comma-separated list of numbers, so the example above is stored as:

Account Name	Multiselect Test
Account 1	1
Account 2	2
Account 3	1,2

This isn’t efficient to search, so another copy is also stored in a separate table with a full text index. This lets CDS convert the query above to the slightly verbose but efficient SQL (adapted example):

SELECT account.name,
       account.markmpn_multiselecttest
FROM   account
       INNER JOIN
       MultiSelectAttributeOptionValuesBase AS msj
       ON account.AccountId = msj.ObjectId
          AND msj.ObjectIdTypeCode = 1
          AND msj.ObjectColumnNumber = 1234
WHERE  CONTAINS(msj.SelectedOptionValues, '1 OR 2')

This is all well and good for on-premise systems, but what about online? With the preview TDS endpoint we can get filtered access to the SQL database, but unfortunately not to the special MultiSelectAttributeOptionValuesBase table.

As the main multi-select picklist field does not have full-text indexing applied, you can’t use a query like:

SELECT name,
       markmpn_multiselecttest
FROM   account
WHERE  CONTAINS(markmpn_multiselecttest, '1 OR 2')

This will give the error:

Msg 40000, Level 16, State 1, Line 1
Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'FilteredAccount' because it is not full-text indexed.

We can get the results we want, but we need to write a less efficient version of the query:

SELECT name,
       markmpn_multiselecttest
FROM   account
WHERE  markmpn_multiselecttest = '1'
       OR markmpn_multiselecttest LIKE '1,%'
       OR markmpn_multiselecttest LIKE '%,1,%'
       OR markmpn_multiselecttest LIKE '%,1'
       OR markmpn_multiselecttest = '2'
       OR markmpn_multiselecttest LIKE '2,%'
       OR markmpn_multiselecttest LIKE '%,2,%'
       OR markmpn_multiselecttest LIKE '%,2'

For each value we want to search for we need to include 4 checks:

1. The only selected value (= '1')
2. The first value (LIKE '1,%)
3. A value in the middle (LIKE '%,1,%)
4. The last value (LIKE '%,1')

However, in the next version of SQL 4 CDS I will be including support for writing a query in this format and converting it to the corresponding FetchXML:

SELECT name,
       markmpn_multiselecttest
FROM   account
WHERE  CONTAINS(markmpn_multiselecttest, '1 OR 2')