Filter Dropdowns

Mar 31, 2010 at 5:15 PM

Having used SPCascadeDropdowns, I thought about creating a similar function to allow filtering of Dropdowns. Normal Lookups will always return the full set of entries from the Lookup List. I wanted to be able to limit the values that an end user sees by adding an "Active" flag against each row.

To achieve this, I mostly pared down the existing function, so that calling it looks like this:

 $().SPServices.SPFilterDropdowns({
  relationshipWebURL: "",
  relationshipList: "Lookup List",
  relationshipListChildColumn: "Title",
  relationshipListSortColumn: "",
  childColumn: "Lookup List Value",
  CAMLQuery: "<And><Eq><FieldRef Name='Some_x0020_Field' /><Value Type='Boolean'>1</Value></Eq><Eq><FieldRef Name='Active' /><Value Type='Boolean'>1</Value></Eq></And>",
  debug: true
 });

The new code is as follows (sorry, would have attached a file, but that's not possible in the discussions)

$.fn.SPServices.SPFilterDropdowns = function(options) {
	var opt = $.extend({}, {
		relationshipWebURL: "",		// [Optional] The name of the Web (site) which contains the relationships list
		relationshipList: "",		// The name of the list which contains the parent/child relationships
		relationshipListChildColumn: "",	// The internal name of the child column in the relationship list
		relationshipListSortColumn: "",	// [Optional] If specified, sort the options in the dropdown by this column,
						// otherwise the options are sorted by relationshipListChildColumn
		childColumn: "",			// The display name of the child column in the form
		CAMLQuery: "",			// This CAML fragment will be applied to the relatedList
		promptText: "Choose {0}...",	// [Optional] Text to use as prompt. If included, {0} will be replaced with the value of childColumn
		completefunc: null,		// Function to call on completion of rendering the change.
		debug: false			// If true, show error messages; if false, run silent
	}, options);

	filterDropdown(opt);
};

function filterDropdown(opt) {
	var choices = "";
	var childSelectSelected = null;
	//var parentSelectSelected = [];
	var master;
	var MultiLookupPickerdata;
	var newMultiLookupPickerdata;
	var childColumnRequired;

	// Find the child column's select (dropdown)
	var childSelect = new dropdownCtl(opt.childColumn);
	if(childSelect.Obj.html() == null && opt.debug) { errBox("SPServices.SPFilterDropdowns", "childColumn: " + opt.childColumn, "Column not found on page"); return; }

	// Get the current child column selection, if there is one
	switch(childSelect.Type) {
		case "S":
			childSelectSelected = childSelect.Obj.find("option:selected").val();
			break;
		case "C":
			childSelectSelected = childSelect.Obj.attr("value");
			break;
		case "M":
			MultiLookupPickerdata = childSelect.Obj.closest("span").find("input:[name$='MultiLookupPicker$data']");
			master = window[childSelect.Obj.closest("tr").find("button:[id$='AddButton']").attr("id").replace(/AddButton/,'MultiLookupPicker_m')];
			currentSelection = childSelect.Obj.closest("span").find("select:[Title='" + opt.childColumn + " selected values']");
			// Clear the master
			master.data = "";
			break;
		default:
			break;
	}

	// When the parent column's selected option changes, get the matching items from the relationship list
	// Get the list items which match the current selection
	var sortColumn = (opt.relationshipListSortColumn.length > 0) ? opt.relationshipListSortColumn : opt.relationshipListChildColumn;
	var camlQuery = "<Query><OrderBy><FieldRef Name='" + sortColumn + "'/></OrderBy><Where>";

	if(opt.CAMLQuery.length > 0) camlQuery += opt.CAMLQuery;
	camlQuery += "</Where></Query>";

	// Get information about the childColumn from the current list
	$().SPServices({
		operation: "GetList",
		async: false,
		listName: listNameFromUrl(),
		completefunc: function(xData, Status) {
			$(xData.responseXML).find("Fields").each(function() {
				$(xData.responseXML).find("Field").each(function() {
					// Determine whether childColumn is Required
					if($(this).attr("DisplayName") == opt.childColumn) childColumnRequired = ($(this).attr("Required") == "TRUE") ? true : false;
				});
			});
		}
	});
	
	$().SPServices({
		operation: "GetListItems",
		// Force sync so that we have the right values for the child column onchange trigger
		async: false,
		webURL: opt.relationshipWebURL,
		listName: opt.relationshipList,
		// Filter based on the currently selected parent column's value
		CAMLQuery: camlQuery,
		// Only get the parent and child columns
		CAMLViewFields: "<ViewFields><FieldRef Name='" + opt.relationshipListChildColumn + "' /></ViewFields>",
		// Override the default view rowlimit and get all appropriate rows
		CAMLRowLimit: 0,
		completefunc: function(xData, Status) {
			$(xData.responseXML).find("faultcode").each(function() {
				if(opt.debug) errBox("SPServices.SPFilterDropdowns",
					"relationshipListChildColumn: " + opt.relationshipListChildColumn,
					"Not found in relationshipList " + opt.relationshipList) + " - or CAML is incorrect";
				return;
			});
			// Add an explanatory prompt
			switch(childSelect.Type) {
				case "S":
					childSelect.Obj.attr({ length: 0 })
					// If the column is required or the promptText option is empty, don't add the "(None) option
					if(!childColumnRequired && opt.promptText.length > 0) childSelect.Obj.append("<option value='0'>" + opt.promptText.replace(/\{0\}/g, opt.childColumn) + "</option>");
					break;
				case "C":
					// If the column is required, don't add the "(None)" option
					choices = childColumnRequired ? "" : "(None)|0";
					childSelect.Obj.attr("value", "");
					break;
				case "M":
					childSelect.Obj.attr({ length: 0 });
					newMultiLookupPickerdata = "";
					break;
				default:
					break;
			}
			// Add an option for each child item
			$(xData.responseXML).find("[nodeName=z:row]").each(function() {
				
				// If relationshipListChildColumn is a Lookup column, then the ID should be for the Lookup value,
				// else the ID of the relationshipList item
				var thisOptionId = ($(this).attr("ows_" + opt.relationshipListChildColumn).indexOf(";#") > 0) ?
						$(this).attr("ows_" + opt.relationshipListChildColumn).split(";#")[0] :
						$(this).attr("ows_ID");
				// If the relationshipListChildColumn is a calculated column, then the value isn't preceded by the ID,
				// but by the datatype.  In this case, thisOptionId should be the ID of the relationshipList item.
				if(isNaN(thisOptionId)) thisOptionId = $(this).attr("ows_ID");
				
				// If relationshipListChildColumn is a Lookup column, then strip off the leading ID;# on the value
				var thisOptionValue = ($(this).attr("ows_" + opt.relationshipListChildColumn).indexOf(";#") > 0) ?
						$(this).attr("ows_" + opt.relationshipListChildColumn).split(";#")[1] :
						$(this).attr("ows_" + opt.relationshipListChildColumn);
				
				switch(childSelect.Type) {
					case "S":
						var selected = ($(this).attr("ows_ID") == childSelectSelected) ? " selected='selected'" : "";
						childSelect.Obj.append("<option" + selected + " value='" + thisOptionId + "'>" + thisOptionValue + "</option>");
						break;
					case "C":
						if (thisOptionValue == childSelectSelected) childSelect.Obj.attr("value", childSelectSelected);
						choices = choices + ((choices.length > 0) ? "|" : "") + thisOptionValue + "|" + thisOptionId;
						break;
					case "M":
						childSelect.Obj.append("<option value='" + thisOptionId + "'>" + thisOptionValue + "</option>");
						newMultiLookupPickerdata += thisOptionId + "|t" + thisOptionValue + "|t |t |t";
						break;
					default:
						break;
				}
			});

			switch(childSelect.Type) {
				case "S":
					childSelect.Obj.trigger("change");
					break;
				case "C":
					childSelect.Obj.attr("choices", choices);
					childSelect.Obj.trigger("propertychange");
					break;
				case "M":
					MultiLookupPickerdata.attr("value", newMultiLookupPickerdata);
					// Clear any prior selections that are no longer valid
					$(currentSelection).find("option").each(function() {
						var thisSelected = $(this);
						$(this).attr("selected", "selected");
						$(childSelect.Obj).find("option").each(function() {
							if($(this).html() == thisSelected.html()) thisSelected.attr("selected", "");
						});
					});
					GipRemoveSelectedItems(master);
					// Hide any options in the candidate list which are already selected
					$(childSelect.Obj).find("option").each(function() {
						var thisSelected = $(this);
						$(currentSelection).find("option").each(function() {
							if($(this).html() == thisSelected.html()) thisSelected.remove();
						});
					});
					GipAddSelectedItems(master);
					// Set master.data to the newly allowable values
					master.data = GipGetGroupData(newMultiLookupPickerdata);
					break;
				default:
					break;
			}
		}
	});
	// If present, call completefunc when all else is done
	if(opt.completefunc != null) opt.completefunc();
}

Coordinator
Apr 1, 2010 at 3:42 AM

Alex:

Thanks for submitting this! So the goal is to simply filter the available values based on the CAMLQuery, correct?  I'll need to think about how this might work if SPCascadeDropdowns is also in use.  At the very lest, there' needs to be a pretty strong warning against it.

M.

Coordinator
Apr 1, 2010 at 3:43 AM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.
Apr 1, 2010 at 8:44 AM

Yes, the goal is to filter the available values since a regular lookup field will always show all the values from the lookup list. It allows an administrator to see all available values, but regular users to only be able to pick from a list of "currently available" values by use of a field like "Active" on the lookup list.

Yes, it should not be used in conjunction with SPCascadeDropdowns as there will probably be unexpected behaviour.

Alex

Coordinator
Apr 4, 2011 at 12:22 PM

Alex:

FYI: I don't know why it took me so long to do so, but I've added SPFilterDropdown into v0.6.1ALPHA2.

Thanks for submitting your code, which contributed to the base for it!

M.