An SpsDev Featured Product:
Filter Field
Tools for Microsoft SharePoint Technologies Users and Developers
Skip Navigation Links
Home
ProductsExpand Products
DownloadsExpand Downloads
DocumentationExpand Documentation
Contact Us

SpsDev.Com's Filter Field is new for Microsoft SharePoint 2007.  It is a custom field type that does filtering.  You can add one to many filter fields to your list or library, and each one can be filtered based on another filtered field in the list.  Pick a state in one field, and the list of cities in the next field is filtered to only show items from that state, for example.  In this release we support SharePoint lists, SQL Server 2000 and 2005, and Xml as sources for the field data.  As you change a selection in any of the drop downs, all of the drop downs below it are each filtered to show only the appropriate choices based on the selection that's been made.

NOTE: Because of conflicting changes Microsoft has made in the Infrastructure Update for SharePoint, Filter Field will only work in farms where the Infrastructure Update has been applied.  Changes they made broke previous versions of our code, and their preferred way of coding for certain situations that Filter Field employs was broken prior to their release of the Infrastructure Update.  As a result we are forced to target either farms with the Infrastructure Update or without, and since some of our customers have already moved to the Infrastructure Update and it is being strongly advocated by Microsoft, we have chosen to target the coding methodology that only works with the Infrastructure Update or later.

Setup

In order to support the functionality in Filter Field, there are a number of setup scopes and steps required.  If you are upgrading from a previous version of Filter Field there is an additional step you need to do – make sure you see the Upgrade Notes section below.  Following are the details installing Filter Field.

Scope – Farm

You must run the setup program included with your Filter Field download on one web front end server in your farm.

NOTE: If you are using the trial version you MUST run the setup program on every web front end in your farm.

After you have completed the setup program, you must add and deploy the sdFilterField solution. Do so as follows:

  1. Add solution: stsadm -o addsolution -filename sdFilterField.wsp
  2. Deploy solution: stsadm -o deploysolution -name sdfilterfield.wsp -immediate -allowGacDeployment

Scope – Site Collection

For each site collection in which you want to use this you must activate the feature for that site collection.  You can do this with stsadm.exe by using a command line that looks like this:

stsadm -o activatefeature -name "SpsDevFilterField" -url http://mySiteCollectionUrl

When you activate the feature some entries will be added to the SafeControls section of the web.config file.  However, because of the way that feature activation works, those entries will only be added on the server on which you activated the feature.  On every other web front end in your farm you will need to manually add the following entries to the SafeControls section for the web.config file; this needs to be done for every web application in which you are using this feature:  

Scope – User Rights

Filter Field makes an assumption that all users have at least Read rights in the site collection.  If they do not have that level of rights then the Filter Field may not work.

Upgrade Notes

If you are upgrading from a previous version of Filter Field, you can get the new features of the upgrade without losing your existing configuration for each Filter Field. First deactivate the feature from each site collection in which it’s being used, retract the solution from the farm, and then uninstall the existing product. Reinstall the new version and deploy the solution to the farm again. Then just go back and activate the Filter Field feature again in each site collection where you had been using Filter Field previously.

NOTE:  You MUST do an IISRESET before using the SpsDev.Com Filter Field for the first time!

Uninstall

Uninstalling also requires a number of scopes and steps to remove the application safely.

Scope – Site Collection

To uninstall Filter Field you must deactivate the Filter Field feature in each site collection in which the feature has been installed. You can do this with stsadm.exe by using a command line that looks like this: stsadm -o deactivatefeature -name SpsDevFilterField -url http:// siteCollectionUrl

Please note that if you have lists or libraries that are using Filter Field it will allow you to deactivate it, but it will tell you the lists and webs where it is being used in the site collection. The reason for that is because if people try to add new Filter Field columns or modify existing ones, they will get an error message that says the Filter Field feature is not activated. All of the existing columns will continue to work. This allows the administrator to turn off the ability to make any further modifications to Filter Field columns, but existing function so that work is not interrupted.

Also note that if you use the –force parameter when you run the deactivatefeature command with stsadm you will not get the list of locations where Filter Field columns are being used.

Scope – Farm

Once you have deactivated the feature from all web applications that are using it, you can retract and delete the solution from the farm. Run the following on one web front end server in the farm:

  1. Retract solution: stsadm -o retractsolution -name sdfilterfield.wsp -immediate
  2. Delete solution: stsadm -o deletesolution -name sdfilterfield.wsp

Once you’ve done that you can run uninstall the SpsDev.Com Filter Field program from whatever web front end server(s) you originally installed it on. 

Configuring A New Filter Field Column

Once you have completed all of the setup steps described above, you should be able to create new columns in lists and libraries that are based on the SpsDev.Com Filter Field.  Just go to your list or library, click on the link to create a new column, give it a name, and then click on the SpsDev.Com Filter Field radio button as the column data type.  The page layout will change to display all of the configuration fields that can be used for a single Filter Field column:

For any given field you will need to configure two of the three main sections – SQL data, Xml data, or SharePoint List Name, and data field information.

Data Considerations

The first consideration for data in a Filter Field is the data format.  The Filter Field denormalizes data to an extent to make it more malleable in SharePoint.  This just means that while your data source may contain a series of foreign and primary keys to identify relationships, when you select the data to be used in Filter Field it needs to be delivered in a format that is only the friendly human readable format of the data.  Consider this example – suppose you have a States table and a Cities table in SQL Server.  Your data structure might look like this:

StateID

StateName

1

Washington

2

Oregon

3

Idaho

CityID

StateID

CityName

1

1

Seattle

2

1

Tacoma

3

2

Portland

From a database perspective, you would typically join the records in the State table to the records in the City table based on the value of the StateID column; that’s how we would know that the city “Seattle” belongs to the state “Washington”.  When the rendering is done in Filter Field however those ID fields that are used to describe the relationship between the tables must instead be the friendly names – in this case that would mean that StateName would be the field on which we would filter a column based on values in the Cities table.  So the data that is used in the Filter Field would need to look like this:

StateName

CityName

Washington

Seattle

Washington

Tacoma

Oregon

Portland

This is true irrespective of your data source.  We do this for a number of reasons:

SQL Data

The following are the columns used to describe a connection to a SQL Server data source.

Database Server Name:

The name of the database server.  If you want to use a specific SQL instance then you should use the format ServerName\InstanceName in this field.

Database Name:

The name of the database that contains the data which the Filter Field will be using.

Stored Procedure Name:

If you are going to retrieve data using a stored procedure, put the stored procedure name here.  If you are using a SQL statement then you do not need to fill in this field.

Param Names:

If you are using a stored procedure and it requires parameters, you can enter one or more parameter names here separated by semi-colons.  For example, if your stored procedure has one parameter called “@CompanyID”, then you would enter @CompanyID in this field.  If your stored procedure had two parameters called “@CompanyID” and “@CostCenter”, then you would enter @CompanyID;@CostCenter in this field.  If your stored procedure does not require parameters you can leave this field blank.

Param Values:

If you are using a stored procedure and it requires parameters, you can enter one or more parameter values here separated by semi-colons.  For example, to enter a value of “119” for one parameter and “United States” for the second parameter you would enter 119;United States in this field.  If your stored procedure does not require parameters you can leave this field blank.

Sql:

If you want to use a SQL statement to retrieve data you can enter it in this field.  If you are using a stored procedure you can leave this field blank.

Sql Security Type:

There are three different choices that you can choose from here to define what type of security will be used when connecting to SQL Server to retrieve the data. The choices are:

Sql User:

If you chose Sql as the security type, then you must enter the name of the SQL account in this field that will be used to retrieve the data.

Sql Password:

If you chose Sql as the security type, then you must enter the password for the SQL account in this field that will be used to retrieve the data.

Data Source Type:

There are different choices here that you can choose to configure which type of data you are going to be using:

Xml Data

The following are the columns used to describe a connection to a SQL Server data source.

Data Source Type:

See the description above.

Xml:

If you chose Xml as the Data Source Type then you would put your raw Xml data into this field.  This is primarily intended to be used for testing purposes.

Xml File:

If you chose XmlFile as the Data Source Type then you would put the fully-qualifed path to an Xml file in this field.  For example, http://myserver/mydirectory/myFile.xml.  Note that if you want to put your Xml file in the same folder on the file system as SharePoint this can be done; however you would need to make sure you copy it to the same folder on every SharePoint web front end server in your farm.  For example, if you have a web application configured in SharePoint for your default IIS virtual server, and that virtual server is configured to use C:\Inetpub\wwwroot, you could make a folder under wwwroot called FieldData and put your Xml file called StateData.xml in there.  Then, in the Xml File field you could type in http://mySharePointServer/FieldData/StateData.xml.

XPath:

This is the XPath statement that should be used to retrieve the data.  For example, if you had a simple Xml file that looked like this:

<States>
<State Name=”Washington”/>
</States>

Then your XPath property would be /States/State.

Xml Namespace:

If your Xml contains namespaces that are used in your XPath statement, you can enter them here as semi-colon delimited values.  For example, supposed your Xml contains two namespaces:

xmlns:a=”http://foo”
xmlns:b=”http://bar”

In this property you would enter a;http://foo;b;http://bar.

Xml Value Type:

There are different choices you can select from here to configure the data source so that Filter Field looks for values as Attributes or as Elements.  Note that if you choose Element, then your Xml must be formed such that when the XPath is executed, the resulting nodes will each have a child element.  The name of that child element is the value you type in the Data Field Name field, and it’s inner text is what will be used for the value.

Here is some example Xml and Filter Field configuration settings to illustrate:

Using Xml with Elements and Namespaces:

Using Xml with Attributes:

Validate Your Data

We have a simple application that you can use to validate your Xml data. If for some reason you data is not appearing in the Filter Field list, you can use this tool to try out your Xml file, XPath, Xml Namespace and Name. It will load your Xml file and try to extract the data based on the parameters you provide. This gives you an opportunity to test out your parameters and modify your Xml or Xml parameters to get the results you are looking for. You can download the XmlValidator tool from here.

SharePoint List Data

Beginning with version 1.1 of Filter Field, you can now use a SharePoint list as the source of your data. To do so, just type in the name of the list into the SharePoint List Name field.  Use the actual name of the list, like "Contacts" - not the Guid or Url to the list.  The SharePoint list must be located either in the root web of the site collection, or in the same web where you are using the Filter Field column.

Data Field Information

The final fields contain configuration about the field itself, and its relationship to other fields in the list.  Following is a description of each field.

Data Field Name:

This is the name of the field in the data that is going to be shown in the Filter Field drop down control. If you are using a SQL data source then it is the name of a field in the dataset that is returned.  If you are using an Xml Element it is the name of the Element; if it’s an Xml Attribute it is the name of the Attribute.  If you are using a SharePoint list data source, then it’s the name of the field in the source list that contains the data for this column.

NOTE:  If you are using a SharePoint list for your data source and you are using the Title field in the Data Field Name or Filter Field Name properties, you must enter Title as the field name in these properties even if you have renamed the field.  For any other field in a SharePoint list you should use whatever the field name is, even if you rename it.  The Title field is the first field added to every list and library by default and is a special case.  Also note that SharePoint field names are case-sensitive!

Parent Column Name:

If this field is the child of another Filter Field column in the list then enter the name of that column here; this column is not  related to a data source.  Note that if you change the name of the parent column you will have to change it here as well.

Child Column Name:

If this field is the parent of another Filter Field column in the list then enter the name of that column here; this column is not  related to a data source. Note that if you change the name of the child column you will have to change it here as well. Starting in version 1.2 Filter Field supports having multiple child columns. In this property you can enter a semi-colon delimited list of columns that are children of this column . For example, if you had two child Filter Fields called "Contact" and "Country", in the Child Column Name property you would enter "Contact;Country".

NOTE:  If you enter an invalid Child Column Name and then try to add or edit data in the list you will see an error message in the status bar of the browser window that looks like this:

Where "SubMod" is from the value you entered in the Child Column Name property.  If you see this message then you need to change the Child Column Name property to a valid SpsDev.Com Filter Field column name in the list.

Filter Field Name:

If this field has a Parent Filter Field column, then you need to enter the name of the field in the datasource that both the child column and parent column have in common.  It should be the same value as the Data Field Name of the Parent field.  Supposed you had data that looked like this:

State Column Data:

StateName

Washington

Oregon

Idaho

City Column Data:

StateName

CityName

Washington

Seattle

Washington

Tacoma

Oregon

Portland

Your field configuration would look like this:

State Column Configuration:

Data Field Name – “StateName” (from the parent data source)
Child Column Name – “City” (the name of the column in this list that is a child of this column)

City Column Configuration:

Data Field Name – “CityName” (from the data source)
Parent Column Name – “State” (the name of the column in this list that is a parent of this column)
Filter Field Name – “StateName” (from the child data source)

As another example, you could have a SharePoint list called "States" that has one column called "State". It could have data that looks like this:

State

Washington

Oregon

Idaho

You could have a second list called "Cities"; it would have two columns – State and City. Note that the State column CANNOT BE A LOOKUP type column; you must enter the values into the State column as text. So it would look like this:

State

City

Washington

Seattle

Washington

Tacoma

Oregon

Portland

Your field configuration would look like this:

States Column Configuration:

Data Field Name – “State” (field from the "States" list datasource)
Child Column Name – “Cities” (the name of the column in this list that is a child of this column)

Cities Column Configuration:

Data Field Name – “City” (field from the "Cities" list datasource)
Parent Column Name – “States” (the name of the column in this list that is a parent of this column)
Filter Field Name – “State” (field from the "Cities" list datasource)

Here's another example:

Source for State column:

Source for Color column:

Here are the list of columns in the list in which this data will be used in Filter Fields:

Here is how the State column is defined:

Here is how the Color column is defined:

IMPORTANT: Here are the keys to remember when setting up Filter Field relationships:

IF you are creating a column that is a parent to another field, it MUST:

IF you are creating a column that is a child of another field, it MUST:

NOTE:  You can also use Filter Field as a way in which to easily retrieve external data from SQL or Xml – it doesn’t have to be a filter!  You can leave the Parent Column Name, Child Column Name and Filter Field Name fields blank and Filter Field will just display data from your data source.

Cache Duration:

This is a value, in minutes, which the data for this field should be cached.  For example, suppose you are retrieving data from SQL Server and you don’t want to query the server every time a user edits a record in the list; instead you want the data to be retrieved once and cached for four hours.  In that case, enter 240 in this field, which is four hours times sixty minutes in an hour, or 240 minutes total.

Show Data Access Errors:

This field is designed to be used primarily when first setting the field up.  If you check this box then if an error is encountered while retrieving the data, the error message will be displayed in the browser.  For example, suppose the path you put to an Xml file was incorrect, or you forgot to add a stored procedure parameter.  If this field is checked then an error message will be displayed so that you know that an error occurred along with as much details as has been provided about the error.  This can be an invaluable trouble shooting technique.

Show Empty Value on New:

This is a new feature in Filter Field 1.1. The Show Empty Value on New property only works if the field does not have a parent field. Otherwise, the items in the dropdown will be automatically populated based on the selection in the parent and the empty value disappears. To use this feature, you should a) make the top field in the Filter Field hierarchy required, and b) check the Show Empty Value on New checkbox. That way the top value will be empty and the user will not be allowed to save changes until they make a selection. If you try to use a field other than the first one in the Filter Field hierarchy, you can force it to have an empty value at the top by removing the Parent Column and Filter Field Name properties, but the column will no longer be automatically filtered by selections in a Filter Field higher in the hierarchy. That is why you can only use the Show Empty Value on New property in the Filter Field highest in the hierarchy.

Show No Items Alert:

This is a new feature in Filter Field 1.2. The default behavior in prior versions has been that after a parent field is selected, if the child had no matches a dialog was displayed stating that no matches were found and filtering any other child fields would stop. Starting with version 1.2, if you check this box that dialog will not be displayed. Stopping the filtering process could also lead to undesired results, such as the scenario where three Filter Fields all contain data, and a selection is made in the parent that resulted in no matches in the first child. In that scenario the second child field would still contain data. This behavior has now been changed so that all fields are filtered, whether a field’s parent contains any values or not.

Special Character Support

Filter Field supports a number of different characters in the field values.  All of the standard characters are supported: A-Z, a-z and 0-9.  In addition, the following special characters are supported: !  @  #  $  %  ^  &  *  (  )  _  -  +  =  {  [  }  ]  |  \  :  ;  "  '  <  ,  >  .  ?  /.  Any characters other than those described above may not work and are considered unsupported.

NOTE:  Filter Field does have a limit in its support for special characters: you cannot have more than one value in your data in which the only difference is one of four special characters: ' & < >.  For example, it is not supported to have a Filter Field that contains both "One & Two" and "One < Two" as values, because they differ only by the characters "&" and "<".  This limitation only impacts Filter Fields that are parents to other Filter Fields.

General Notes

You must install and activate the SpsDev.Com Filter Field Feature in each site collection before you can use the filter field.

All users that are creating columns in a list that uses a Filter Field must have at least READ rights to the site collection.

Filter Field CANNOT be used as a Site Column because it depends upon a hidden list in each site collection where the field is used.  You can use the InspectFilterField.exe program that is installed with Filter Field to examine the list and its contents.

If you use SQL Security the SQL user name and password are NOT encrypted.  They are stored in a list that is hidden so you cannot browse to find this information, but you could programmatically obtain it.

We have tested this with both Windows authentication sites (both NTLM and Kerberos), as well as Forms Authentication sites where forms auth is the only provider, the site is not extended at all with a windows web application.

Filter Field WILL NOT be shown in the information panel of Microsoft Office applications, where other document library fields appears.  This is not a problem with Filter Field, it is a limitation inherent in all SharePoint custom fields and Microsoft Office client applications.

Filter Field columns are not editable when you choose to Edit in Spreadsheet from a SharePoint list.  You will be able to see the data but not change it.  This is not a problem with Filter Field, it is a limitation inherent in all SharePoint custom fields and the datasheet view.

If you're using a SharePoint List as the data source and you use item level security on the list of items that populates the Filter Field, the items in the field will always be security trimmed according to the current user whether or not you configure the column to use the ProcessAccount for security.

Troubleshooting

Here are some things to check if you aren't getting data back:

For Xml:

For SQL:

For SharePoint Lists:

If fields are not being filtered when you change selections, make sure that:

Known Issues

Before Going to Production

SpsDev.Com strongly recommends that before you consider using Filter Fields in production, please test it thoroughly in your own test environment for usability and performance.  Make sure that it meets your own usability bar before deploying it into your production environment.