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: http://msdn.microsoft.com/en-us/library/cc151072.aspx

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!


Solution

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 :)

6 comments:

darksander said...

Hi,
Did you try modifying that attribute directly in the database eventually?
I don't want some of the system fields on the grid (things like the address1_% for lead, contact), and only by setting 'Searchable' on 'No' is not enough.

Dejan Dular said...

I'm sorry, but I haven't tried it yet. My guess is that it should be no problem with some display masks. Anyway... If I don't need it I don't poke ;)

darksander said...

heh, yea, fair enough. I might have to poke, tho :)

Dave said...

Thanks for the helpful post. I did try updating one and it worked like a charm:

update metadataSchema.Attribute set DisplayMask = DisplayMask | 0x10000000

where attributeId = '5F17FC98-56E5-4C76-AA06-183213366932'

Junaid said...

I found CRM SDK even though provides an interface to update entity/attribute (UpdateEntiyRequest/UpdateAttributeRequest types using Metadata service)metadata does not actually make/publish the changes. I am able to manipulate the display masks of attributes directly using this view ([dbo].[v_attributeobjecttypes]). I was able to accomplish this using following,

--Valid for AF = 67108864,
--Valid for Grid = 268435456,
--Valid for Form = 134217728
select (67108864 | 268435456 | 134217728) as V_AF_G_F, (268435456 | 134217728) as V_G_F, (134217728) as V_F

update attribute
set displaymask = 134217728
where inproduction = 1
and customizationlevel in (0, 1)
and attributeid = '040A402F-A254-4D8F-AD62-C1CE611B2C82'

So, the above would hide an attribute from any Advanced Find view (going forward)- non-searchable and not available for addition to the view. You would need to recycle IIS and CRM Async services to clear any metadata cached. This solution seems to work perfectly so far with the obvious glitch that you cannot expect to just import customizations from one tenant to other since the SDK does not support these display masks values.

Anonymous said...

Hey all,

I had to modify it on the database. These scripts work:

-- set searchable yes
update MetadataSchema.Attribute
set DisplayMask = DisplayMask | 0x4000000
from MetadataSchema.Attribute
where ...

-- set searchable no
update MetadataSchema.Attribute
set DisplayMask = DisplayMask - 0x4000000
from MetadataSchema.Attribute
where ...

Regards,
Thorsten