Retrieving data from a SharePoint list - Part 3
Drop Down Box Items Filtering
We have seen sorting of items, now we will see how to filter the items in a drop down. Remember, the built-in filtering feature of the drop down does not work in web enabled forms.

1. Open "user registration" form in Editor.

2. Open VSTA.

3. Modify the code. Now that you know how to use the SPQuery object, you just need to change the query to get the desired results. Filtering can be done on any column of the list but just to demo you the process, I will keep it simple. We will apply the filter on the "title" field. The query will return countries that start with alphabet "U". This is simple. You can add more filters as and when you like. Add the following to the query definition:

<Where><BeginsWith><FieldRef Name='Title' /><Value Type='Text'>U</Value></BeginsWith></Where>



"<BeginsWith>" tag is used to search the start of columns that hold Text or Node field type values. We pass "U" so the query will search for the items that start with "U".

Here is the complete function:

public void AddCountries()

{

try

{

SPSite site = SPContext.Current.Site;

SPWeb web = site.OpenWeb();

SPList list = web.Lists["Countries"];

SPQuery query = new SPQuery();

query.Query = "<Where><BeginsWith><FieldRef Name='Title' /><Value Type='Text'>U</Value></BeginsWith></Where>"

+ "<OrderBy><FieldRef Name='Title' Ascending='FALSE' /></OrderBy>";

SPListItemCollection listitems = list.GetItems(query);

XPathNavigator nav = this.CreateNavigator().SelectSingleNode("/my:myFields/my:Countries", this.NamespaceManager);

foreach (SPListItem li in listitems)

{

XPathNavigator newNode = null;

newNode = nav.Clone();

newNode.SelectSingleNode("/my:myFields/my:Countries/my:Displayname", this.NamespaceManager).SetValue(li["Title"].ToString());

newNode.SelectSingleNode("/my:myFields/my:Countries /my:Value", this.NamespaceManager).SetValue(li["Title"].ToString());

nav.InsertAfter(newNode);

newNode = null;

}

nav.DeleteSelf();

nav = null;

}

catch

{

}

}


Here is how the drop down will look like:



Drop down only shows countries that start with a "U".

Download completed InfoPath form and application code (ZIP format)