August 28, 2009

Get a list of searchable attributes from database

(The final solution is at the bottom)

Today my colleague wanted to check which attributes were marked as searchable in a specific entity.

One way to do this is to open each attribute in MSCRM and check if it is marked as searchable. A long way if you have an entity with 220 attributes.

So I went to the database and checked the MetadataSchema.Attribute table. There is a column named DisplayMask. SDK says that display masks are following (ValidForAdvancedFind = Searchable):

Name Value Description
None 1 Specifies no restrictions.
ObjectTypeCode 4 Specifies that the attribute is an entity type code. The name of the entity is displayed.
PrimaryName 2 Specifies to display the attribute as the primary name.
RequiredForForm 0x40 Specifies that the attribute must be shown on a form.
RequiredForGrid 0x80 Specifies that the attribute can be shown in a grid view.
ValidForAdvancedFind 8 Specifies that the attribute can be shown on the Advanced Find form. This attribute is shown as Searchable in the UI.
ValidForForm 0x10 Specifies that the attribute can be shown on a form.
ValidForGrid 0x20 Specifies that the attribute can be shown in a grid view.

But the value in the database is 469762048 (this attribute is searchable)! Bitwise comparison fails: 469762048 & 8 = 0 WTF!!!

After some googling and 30 minutes of using reflector if found that the former values are meant to be used only when working with metadata web service, like in this example:

Thank you Reflector!

Microsoft.Crm.Platform.Sdk.dll (found it in GAC) contains DisplayMasks enumeration in Microsoft.Crm.Metadata namespace.

This is what the reflector says:

public enum DisplayMasks
ActivityPointerRegardingName = 0x80,
ActivityRegardingName = 1,
BitMask = 0x1000,
DynamicFormatCode = 0x4000,
None = 0,
ObjectTypeCode = 0x400,
PrimaryName = 0x100,
QueueItemPriority = 0x10,
QueueItemSender = 0x20,
QueueItemState = 4,
QueueItemStatus = 8,
QueueItemTitle = 2,
QueueItemToRecipients = 0x40,
RequiredForForm = 0x20000000,
RequiredForGrid = 0x40000000,
ResourcePointerName = 0x200,
ReturnedTypeCode = 0x800,
StateCode = 0x2000,
ValidForAdvancedFind = 0x4000000,
ValidForForm = 0x8000000,
ValidForGrid = 0x10000000

This means that the ValidForAdvancedFind has value of 0x4000000! I tested it with the previous example and it works! Hooray!


So the final answer to the problem of this post is:

select * from MetadataSchema.Attribute
where DisplayMask & 0x4000000 > 0 and EntityId = @entityId

Of course you can use all other values as well.

What I haven’t tested yet is whether you can change this mask value directly in the database - definitely not supported by Microsoft :)