SPCascadeDropDowns - CAML query on parent column?

Nov 22, 2010 at 1:31 PM

First off: Fantastic library. At first I was leary about using javascript to fulfill client requirements, but seeing how swiftly you can achieve results ( which is 90% of what my client looks for, how fast can we do it ), and how well this library is supported, I am converted!

Now on to my questions (2) / recommendation:

1) Is there a way to apply a CAML query to the Parent Column drop down  similar to the way the the current CAML parameter effects the child cascaded drop down?

Real Life Example of what I am attempting to do:

I have a List called "Line of Business"

Title    Department

EFG    Credit

TGS    Credit

TU      Accounting

QW     Accounting

etc,etc

 

I have a relation list called "LobCategories" columns Title, LOB (Lookup).

I want to filter the parent column drop down to only show the Lines of Business available to the user logged in by filtering it by their profile's Department that has been synchronized using SP's UPS from Active Directory.

So I was thinking I could use : $().SPServices.SPGetCurrentUser to get the Department and if I could then use a CAML query to filter the parent dropdown, that would be all it took.

 

That brings me to a related question:

2) Is there a way to filter the "Line of Business" dropdown if it was on its own using the $().SPServices.SPGetCurrentUser to get the Department? or should I be doing it another way?

 

Thank you very much for any feedback you have!!

- Ryan

Nov 22, 2010 at 6:18 PM

Well, I dug into your library and added a Function to SPServices called SPFilterDropdown: It worked for my purposes, I would definately appreciate some refinement and maybe you could turn it into a more useful function for your library. I grabbed most of the functionality from the sub function of SPCascadeDropdown :  cascadeDropdown(opt) and modified it for my purposes.

    // Function to filter a dropdown by a value
    $.fn.SPServices.SPFilterDropdown = function (options) {
        var opt = $.extend({}, {
            dropdownColumn: "",
            dropdownListColumn: "",
            dropdownListFilterColumn: "",
            dropdownList: "",
            CAMLQuery: "",
            listName: $().SPServices.SPListNameFromUrl(), 	// The list the form is working with. This is useful if the form is not in the list context.
            debug: false
        }, options);



        var choices = "";
        var dropdownToFilterSelected = null;
        //var parentSelectSelected = [];
        var master;
        var MultiLookupPickerdata;
        var newMultiLookupPickerdata;
        var dropdownColumnRequired;

        
        // If the selection hasn't changed, then there's nothing to do right now.  This is useful to reduce
        // the number of Web Service calls when the parentSelect.Type = "C" or "M", as there are multiple propertychanges
        // which don't require any action.  The attribute will be unique per child column in case there are
        // multiple children for a given parent.
        var dropdownListColumnStatic = $().SPServices.SPGetStaticFromDisplay({
            listName: opt.dropdownList,
            columnDisplayName: opt.dropdownListColumn
        });
       

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

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

       
        // Get the list items which match the current selection
        var sortColumn = opt.dropdownListColumn; //(opt.dropdownListSortColumn.length > 0) ? opt.dropdownListSortColumn : opt.dropdownListColumn;
        var camlQuery = "<Query><OrderBy><FieldRef Name='" + sortColumn + "'/></OrderBy><Where>";
        //if (opt.CAMLQuery.length > 0) camlQuery += "<And>";

        //camlQuery += "<Eq><FieldRef Name='" + opt.dropdownListColumn + "'/><Value Type='Text'></Value></Eq>";

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

        // Get information about the dropdownColumn from the current list
        $().SPServices({
            operation: "GetList",
            async: false,
            listName: opt.listName,
            completefunc: function (xData, Status) {
                $(xData.responseXML).find("Fields").each(function () {
                    $(this).find("Field").each(function () {
                        // Determine whether dropdownColumn is Required
                        if ($(this).attr("DisplayName") == opt.dropdownColumn) {
                            dropdownColumnRequired = ($(this).attr("Required") == "TRUE") ? true : false;
                            // Stop looking;we're done
                            return 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.dropdownList,
            // Filter based on the currently selected parent column's value
            CAMLQuery: camlQuery,
            // Only get the parent and child columns
            CAMLViewFields: "<ViewFields><FieldRef Name='" + opt.dropdownListFilterColumn + "' /><FieldRef Name='" + opt.dropdownListColumn + "' /></ViewFields>",
            // Override the default view rowlimit and get all appropriate rows
            CAMLRowLimit: 0,
            // Even though setting IncludeMandatoryColumns to FALSE doesn't work as the docs describe, it fixes a bug in GetListItems with mandatory multi-selects
            CAMLQueryOptions: "<QueryOptions><IncludeMandatoryColumns>FALSE</IncludeMandatoryColumns></QueryOptions>",
            completefunc: function (xData, Status) {
                $(xData.responseXML).find("faultcode").each(function () {
                    if (opt.debug) errBox("SPServices.SPFilterDropdown",
						"dropdownListFilterColumn: " + opt.dropdownListFilterColumn + " or " +
						"dropdownListColumn: " + opt.dropdownListColumn,
						"Not found in dropdownList " + opt.dropdownList);
                    return;
                });

                // Add an explanatory prompt
                switch (dropdownToFilter.Type) {
                    case "S":
                        dropdownToFilter.Obj.attr({ length: 0 })
                        // If the column is required or the promptText option is empty, don't add the "(None) option
                        //if (!dropdownColumnRequired && (opt.promptText.length > 0)) dropdownToFilter.Obj.append("<option value='0'>" + opt.promptText.replace(/\{0\}/g, opt.dropdownColumn) + "</option>");
                        break;
                    case "C":
                        // If the column is required, don't add the "(None)" option
                        choices = dropdownColumnRequired ? "" : "(None)|0";
                        dropdownToFilter.Obj.attr("value", "");
                        break;
                    case "M":
                        dropdownToFilter.Obj.attr({ length: 0 });
                        newMultiLookupPickerdata = "";
                        break;
                    default:
                        break;
                }
                // Add an option for each child item
                $(xData.responseXML).find("[nodeName=z:row]").each(function () {

                    // If dropdownListColumn is a Lookup column, then the ID should be for the Lookup value,
                    // else the ID of the dropdownList item
                    var thisOptionId = ($(this).attr("ows_" + opt.dropdownListColumn).indexOf(";#") > 0) ?
							$(this).attr("ows_" + opt.dropdownListColumn).split(";#")[0] :
							$(this).attr("ows_ID");
                    // If the dropdownListColumn 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 dropdownList item.
                    if (isNaN(thisOptionId)) thisOptionId = $(this).attr("ows_ID");

                    // If dropdownListColumn is a Lookup column, then strip off the leading ID;# on the value
                    var thisOptionValue = ($(this).attr("ows_" + opt.dropdownListColumn).indexOf(";#") > 0) ?
							$(this).attr("ows_" + opt.dropdownListColumn).split(";#")[1] :
							$(this).attr("ows_" + opt.dropdownListColumn);

                    switch (dropdownToFilter.Type) {
                        case "S":
                            var selected = ($(this).attr("ows_ID") == dropdownToFilterSelected) ? " selected='selected'" : "";
                            dropdownToFilter.Obj.append("<option" + selected + " value='" + thisOptionId + "'>" + thisOptionValue + "</option>");
                            break;
                        case "C":
                            if (thisOptionValue == dropdownToFilterSelected) dropdownToFilter.Obj.attr("value", dropdownToFilterSelected);
                            choices = choices + ((choices.length > 0) ? "|" : "") + thisOptionValue + "|" + thisOptionId;
                            break;
                        case "M":
                            dropdownToFilter.Obj.append("<option value='" + thisOptionId + "'>" + thisOptionValue + "</option>");
                            newMultiLookupPickerdata += thisOptionId + "|t" + thisOptionValue + "|t |t |t";
                            break;
                        default:
                            break;
                    }
                });

                switch (dropdownToFilter.Type) {
                    case "S":
                        dropdownToFilter.Obj.trigger("change");
                        break;
                    case "C":
                        dropdownToFilter.Obj.attr("choices", choices);
                        dropdownToFilter.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");
                            $(dropdownToFilter.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
                        $(dropdownToFilter.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;
                }
            }
        });

    };
Regards,
-Ryan
Coordinator
Nov 22, 2010 at 8:40 PM

Great stuff, Ryan! Thanks for pitching this back in. It's funny, but I actually added building this function to my mental list over the weekend after reading a blog post about something where they could have used it.

Did you solve the full problem for yourself or do you still need help? Looks like you're doing fine on your own!

M.

Coordinator
Nov 22, 2010 at 8:41 PM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.
Nov 23, 2010 at 12:45 PM

Marc,

Thanks! I'm very happy to contribute where I can. This is an amazing and useful library. Looks like I'm doing fine for now, great job commenting the code, I would taken 3 times as long to figure it out without it. I will see if I can accomplish the question #1 enhancement once I am no longer under the gun by my client. The above function gets me to an amicable position in the project timeline.

Coordinator
Apr 4, 2011 at 11:23 AM

Ryan:

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.