Function

$().SPServices.SPFilterDropdown

Certification

Certified for SharePoint 2007 Certified for SharePoint 2010

Functionality

The SPFilterDropdown function allows you to filter the values available in a Lookup column using CAML against the Lookup column's source list.  This function works with all three types of "dropdown": <20 options (simple select), 20+ options (complex select), and multi-select.
 
Thanks to Alex Lee (alexlee797) and Ryan (rnshaw) for contributing their versions of this function, which I used to build the one available here. You can see their versions in these two discussion threads: Filter Dropdowns and SPCascadeDropDowns - CAML query on parent column?

Prerequisites

  • The relationship list contains the relationshipListColumn
  • The dropdown for columnName is a lookup into relationshipList's relationshipListColumn OR a list column which is a lookup into another list column ("secondary list").

Syntax

$().SPServices.SPFilterDropdown({
relationshipWebURL: "",
relationshipList: "",
relationshipListColumn: "",
  relationshipListSortAscending: true, // Added in 2013.01
relationshipListSortColumn: "",
columnName: "",
 listName: $().SPServices.SPListNameFromUrl(),
 promptText: "",
noneText: "(None)",
CAMLQuery: "",
 CAMLQueryOptions: "<QueryOptions></QueryOptions>",
completefunc: null,
debug: false
});

relationshipWebURL
The URL of the Web (site) which contains the relationshipList. If not specified, the current site is used. Examples would be: "/", "/Accounting", "/Departments/HR", etc. Note: It's always best to use relative URLs.

relationshipList
The name or GUID of the list which contains the parent/child relationships. If you choose to use the GUID, it should look like: "{E73FEA09-CF8F-4B30-88C7-6FA996EE1706}". Note also that if you use the GUID, you do not need to specify the relationshipWebURL if the list is in another site.

relationshipListColumn
The StaticName of the column in the relationshipList which is used for the lookup column

relationshipListSortColumn
If specified, sort the options in the dropdown by this column otherwise the options are sorted by relationshipListColumn

relationshipListSortAscending
Allows sorting either ascending (true) or descending (false). The default is true (ascending).

columnName
The DisplayName of the column in the form

listName
By default, set to the list name for the current context based on the URL. If your form is outside the context of the list, then you can specify the listName yourself.

promptText
Text to use as prompt. If included, {0} will be replaced with the value of childColumn. The default value is "".
NOTE: I discourage the use of this option. Yes, I put it into the function, but if the user doesn't make a choice, they get an ugly error because SharePoint doesn't understand it as an option. I've left in in for backward compatibility.
Deprecated in v0.7.1.

noneText
Text to use for the (None) selection. Provided for non-English language support. The default value is "(None)".

CAMLQuery
The CAMLQuery option allows you to specify the filter on the relationshipList. The additional filter will be <And>ed with the existing CAML which is checking for matching items based on the parentColumn selection. The CAMLQuery should contain a CAML fragment such as:

CAMLQuery: "<Eq><FieldRef Name='Status'/><Value Type='Text'>Active</Value></Eq>"

CAMLQueryOptions
This option can be used to specify additional options for retrieval from the sourceList. See the MSDN documentation for GetListItems for the syntax.

completefunc
If specified, the completefunc will be called upon completion of the filtering. Uses for the completefunc: consistent default formatting overrides, additional lookup customizations, image manipulations, etc. You can pass your completefunc in either of these two ways:

completefunc: function() {
  ...do something...
},
or
completefunc: doSomething,                  // Where doSomething is the name of your function
debug
Setting debug: true indicates that you would like to receive messages if anything obvious is wrong with the function call, like using a column name which doesn't exist. I call this debug mode.

Example

In this example, we'll filter the lookup column called "Country" so that we only include those Countries where the Active column is set to 'Yes'. In the screenshot below, you can see that Canada is not currently 'Active'.

image

In the Sales Opportunities list, we have a lookup column called Country, which gets its values from the Title column in the Countries list.

image

If we add the call to SPFilterDropdown below...

$().SPServices.SPFilterDropdown({
  relationshipList: "Countries",
  relationshipListColumn: "Title",
  columnName: "Country",
  CAMLQuery: "<Eq><FieldRef Name='Active' /><Value Type='Boolean'>1</Value></Eq>",
  completefunc: null,
  debug: false
});

...then 'Canada' is filtered out of the available values because it is not 'Active'.

image

Last edited Jun 18 at 5:05 PM by sympmarc, version 19

Comments

SarahNity Aug 21 at 11:49 PM 
Any ideas on the multi-select errors?
Thanks in advance!

SarahNity Jun 10 at 12:53 AM 
The SPServices has been such an invaluable help! Thank you so much.

I am finding that there is a special case when I am trying to apply the SPFilterDropDown. The documentation states that it supports multi-select but I can't seem to get it to work. I have two identical fields set up one above the other. One is a single select drop-down, the other is a multi-select. Otherwise both set ups are the same. Both are "Lookup" fields into reference tables.

The error message in the web browser (Internet Explorer 8, this is a corporate environment so I can’t upgrade or modify anything in the browser):
Webpage error details
User Agent: Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1)
Message: 'removeControl' is null or not an object
Line: 2
Char: 3431
Code: 0
URI: http://*******.com/_layouts/groupeditempicker.js?rev=Cb56qvuNNklZSLDmzYmusA%3D%3D

Version info:
Chrome Version 34.0.1847.137 m also fails silently.
SharePoint 2010 with Designer on Standard Edition.
Library versions: jquery-1.11.0.min.js and jquery.SPServices-2014.01.js

Any ideas?
Thanks!

Field 1 (single select): find the available project status labels in the "LU_Status" table where the LI is "Active" and present it in ascending sequence according to the "SortOrder" column. Provide the "Title" from the lookup table to the "Status" column in the custom form.

Field 2 (multi-select): find the available names of groups that require training. This may be one or more of roughly 6 user types (Sales Agents, Managers etc). Get the “Title” from the reference table “LU_CommRole”, where the LI is “Active” and sort the list by the column “SortOrder”.

Field 1 works perfectly in the custom data entry form.
Field 2 generates a Java error and all code execution stops.

Here is the JavaScript:
$().SPServices.SPFilterDropdown({
relationshipWebURL: "",
relationshipList: "LU_Status",
relationshipListColumn: "Title",
relationshipListSortAscending: true,
relationshipListSortColumn: "SortOrder",
columnName: "Status",
listName: $().SPServices.SPListNameFromUrl(),
promptText: "",
CAMLQuery: "<Eq><FieldRef Name='Active'/><Value Type='Boolean'>1</Value></Eq>",
CAMLQueryOptions: "<QueryOptions></QueryOptions>",
completefunc: null,
debug: false
});
alert ("END Status");

$().SPServices.SPFilterDropdown({
relationshipWebURL: "",
relationshipList: "LU_CommRole",
relationshipListColumn: "Title",
relationshipListSortAscending: true,
relationshipListSortColumn: "SortOrder",
columnName: "Roles Impacted",
listName: $().SPServices.SPListNameFromUrl(),
promptText: "",
CAMLQuery: "<Eq><FieldRef Name='Active'/><Value Type='Boolean'>1</Value></Eq>",
CAMLQueryOptions: "<QueryOptions></QueryOptions>",
completefunc: null,
debug: false
});
alert ("END Communications Role");

Here is the HTML from the custom ASPX form:
<tr>
<td width="25%" class="ms-formlabel"><SharePoint:FieldDescription runat="server" id="ff5description{$Pos}" FieldName="Status" ControlMode="Edit" /></td>
<td width="75%" class="ms-formbody"><xsl:comment>FieldName="Status"</xsl:comment><SharePoint:FormField runat="server" id="ff5{$Pos}" ControlMode="New" FieldName="Status" __designer:bind="{ddwrt:DataBind('i',concat('ff5',$Pos),'Value','ValueChanged','ID',ddwrt:EscapeDelims(string(@ID)),'@Status')}" /></td>
</tr>

<tr>
<td width="25%" class="ms-formlabel"><SharePoint:FieldDescription runat="server" id="ff22description{$Pos}" FieldName="Roles Impacted" ControlMode="Edit" /></td>
<td width="75%" class="ms-formbody"><SharePoint:FormField runat="server" id="ff22{$Pos}" ControlMode="New" FieldName="Roles Impacted" __designer:bind="{ddwrt:DataBind('i',concat('ff22',$Pos),'Value','ValueChanged','ID',ddwrt:EscapeDelims(string(@ID)),'@Roles_x0020_Impacted')}" /></td>
</tr>

T0mplex Apr 26 at 4:41 PM 
Mark, you've done some terrific work on this. It's greatly appreciated.

I don't know whether SharePoint Online is just having a bad day but I've been getting an error with this function. Here are some details: (I'm using SPServices-2014.01 and jquery-1.10.1.min.js)

At Line 3397 //else if (currentContext.thisList.length > 0) {
Error thrown is "Unable to get property 'length' of undefined or null reference".

It looks like _spPageContextInfo.pageListId is returning null in
function SPServicesContext()
Line 4043 //this.thisList = _spPageContextInfo.pageListId;

The other properties all look fine. It's just a standard NewForm. The URL is along the lines of https://<host>/sites/<site>/_layouts/15/start.aspx#/Lists/MyList/NewForm.aspx etc.

I commented out a few lines, supplied listName in the options and it is working fine again :-)

I wasn't able to locate up to date documentation on MSDN to see if there is a difference in SharePoint Online. The 2010 SDK information is the best I could find, at http://msdn.microsoft.com/en-us/library/office/ee552069(v=office.14).aspx

JSdream Jan 29 at 5:29 PM 
Will this to work with a content type, used in a document library, using a site column lookup to another list. I get no errors, following the documentation. The lookup replaces its possible selections with (None), after running the error free script. The Title column in the child list holds a match for the Query. You mention that the Query gets 'Anded'. I surmise the the intial lookup with all its values, is what gets my Query 'anded' too.

//alert (BranchFolder); //this is a previous variable I get in my script
var TransitNo = BranchFolder.substring(0,5); //I then parseout the number from the name
//alert(TransitNo);
$().SPServices.SPFilterDropdown({
relationshipList: "Transit Unit Information",
realtionshipListColumn: "Adjudicator_x0020_Names",
columnName: "TestSelectAdjudicator",
CAMLQuery: "<Eq><FieldRef Name='Title' /><Value Type='Text'>" + TransitNo +"</Value></Eq>",
completefunc: null,
debug: false
});

JSdream Jan 28 at 2:49 PM 
Will the CAMLQuery Fragment allow?: "<Contains><FieldRef Name = 'thecolumnname' /><Value Type='Text'>" + variable + "</Value></Contains>"

TFerraresi Aug 5, 2013 at 7:39 PM 
Hello someone could do in sharepoint 2013 foundacion ??

thamera May 22, 2013 at 3:54 PM 
Is there an easy way of getting options such as "columnName" in this function to allow choosing between static or display name? We are deploying a system in which there is a business requirement to allow users to change the display name on any column to suit their project needs but we want to maintain a single consistent implementation of spservice utilizaiton through a global js on all sites. This means we must be able to use static name "everywhere" as opposed to the display name. I know we can get the static name through other api calls but I am hoping to get this capability within the actual functions rather than additional overhead from needing to always "get" the static name.

thamera Apr 17, 2013 at 9:08 PM 
Has anyone looked at this in 2013 yet? I am using SPServices pretty heavily on a 2013 onprem and this is my first issue. I am getting an error at line 2120 (unminified vesion 0.7.2). It looks like it is looking for a button in the multi-select lookup element but in 2013 there doesn't appear to be a button. Rather, they styled an input element. I would love to see a fix for this against the current stable version (0.7.2) so that I could use it right away. Any thoughts?

AndreyBizin Nov 30, 2012 at 12:56 PM 
wassim87, look for CALM query description.
my example:
<And><Or><Eq><FieldRef Name='Supervisor' /><Value Type='Text'>"+userTitle+"</Value></Eq><Eq><FieldRef Name='EscalatedTo' /><Value Type='Text'>"+userTitle+"</Value></Eq></Or><And><Neq><FieldRef Name='Status' /><Value Type='Text'>Resolved</Value></Neq><Neq><FieldRef Name='Status' /><Value Type='Text'>Rejected</Value></Neq></And></And>

wassim87 May 25, 2012 at 5:23 PM 
This function is amazing, but it only works with me when I have one condition in the query. I tried to do OR operator in the query on the same column but it didn't work, this was my syntax "<OR><Eq><Eq><Eq></Eq></OR>" ... isn't that right?

jaxkookie Aug 25, 2011 at 8:51 PM 
is there a way to just create a dropdown with this method that only return unique items. I want to populate a drop down from a SP column