Thursday, April 26, 2012

Filterable Multi-valued PeopelPicker Column using PowerShell

Recently I came across an error while filtering the multi-valued PeoplePicker column. The error reads "Cannot show the value of filter. The field may not be filterable, or the number of items returned exceeds the list view threshold enforced by the administrator." There are only four values in the column, surely it has no rleation to the list view threshold value.

Internally, SharePoint server uses "_layouts/filter.aspx" application page for filter values which are presented in an iFrame. I checked the ULS logs and copied the Request URL with filter.aspx. I pasted it in browser and hit "Enter". It returned no result.

What is multi-valued field?
The field which allows more than one values or which allows multiple selections. The multi-valued fileds are non-sortable, non-filterable. Multi-valued fields cannot be indexed.

Fix:
This behavior can be overridden. Every field has an associated schema. Multi-valued fields are marked as "Sortable =false" in the schema for performance reasons. Note: Apply the workaround wherever it is unavoidable.

If we mark the multi-valued PeoplePicker as "Sortable=true", the column turns filterable/ sortable. This can be achieved using SharePoint Server Object Model or using Windows PowerShell (I love it).

PowerShell Script to mark the column as Sortable:

$web=Get-SPWeb <weburl>
$list=$web.Lists[<list title>]
$field=$list.Fields[<field title>]
$strSchema=$field.SchemaXml
$str=Schema$strSchema.Replace("Sortable=`"FALSE`"","Sortable=`"true`"")
$field.SchemaXml = $strSchema
[Update: I noticed that multi-valued "person or group" coulmn in other web application was showing filter choices even though "Sortable=false". Googling did not help. I checked at various levels and found that "Online Presence" setting causes this error. If Online presence setting is turned OFF, SP 2010 starts showing this error for multi-valued columns when tried to filter. So turn it ON, and...no error, filter choices are shown back. The Online Presence setting is available in Web applications's General Settings.]
Post a Comment