This project has moved and is read-only. For the latest updates, please go here.
7

Closed

Filtering a multi-select (extending cascading dropdowns)

description

I also need a way to filter the multi-select picker using a dropdown as a parent control. I tried using the cascade code as a base and make the function. It works except I can't figure out how to refresh the list once it's filtered. I tried triggering the onchange event for the select control that's rendered but it doesn't work. Any ideas?

Toby

file attachments

Closed Dec 14, 2009 at 5:32 PM by
Implemented in v0.4.6 and v0.4.7.

comments

Toadmyster wrote Sep 5, 2009 at 2:41 PM

Here's what I have for a standalone function. Debug shows that the DOM is updated correctly but my trigger doesn't refresh the list.
$.fn.SPServices.SPFilterLookupWithDropdown = function(options) {

    var opt2 = $.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
        relationshipListParentColumn: "", // The name of the parent column in the relationship list
        relationshipListChildColumn: "", // The name of the child column in the relationship list
        parentColumn: "",               // The name of the parent column in the form
        childList: ""                       // The name of the child list in the form
    }, options);

    // Get the form parent and child objects
    var childSelect = $().find("input:[id$='MultiLookupPicker_data']");
    var parentSelect = $().find("select:[Title='" + opt2.parentColumn + "']");

    if (parentSelect.html() == null) {
        parentSelect = $().find("input:[Title='" + opt2.parentColumn + "']");
        parentSelect.attr("readonly", "readonly");
        // Bind to the parent column's onchange event
        parentSelect.bind("focus", function() {
            var realSelect = parentSelect.parent().find("#_Select");
            handleEvent2(realSelect, childSelect, opt2);
            realSelect.trigger("change");
        });
        // Trigger the onchange event for the parent column to set the valid values
        parentSelect.parent().find("img").click();
        parentSelect.blur();
    }
    else {
        handleEvent2(parentSelect, childSelect, opt2);
        parentSelect.trigger("change");
    }
};

// Event handler for onchange event
function handleEvent2(ctr, childCtr, opt2) {
    var parentSelectedValue;
    var displayedOnce = false;
    var choices = "";

    ctr.bind("change", function() {
        parentSelectedValue = ctr.find("option:selected").text();
        // When the parent column's selected option changes, get the matching items from the relationship list
        $().SPServices({
            operation: "GetListItems",
            // Force sync so that we have the right values for the child column onchange trigger
            async: false,
            webURL: opt2.relationshipWebURL,
            listName: opt2.relationshipList,
            // Filter based on the currently selected parent column's value
            CAMLQuery: "" + parentSelectedValue + "",
            // Only get the parent and child columns
            CAMLViewFields: "",
            // Override the default view rowlimit and get all appropriate rows
            CAMLRowLimit: "0",
            completefunc: function(xData, Status) {
                // Add an option for each child item
                $(xData.responseXML).find("z\\:row").each(function() {
                    choices = choices + "|t" + $(this).attr("ows_ID") + "|t" + $(this).attr("ows_" + opt2.relationshipListChildColumn) + "|t |t ";
                });
                choices = choices.replace(/|t/, "");
                childCtr.attr("value", choices);
            }
        });
        // Trigger child change event to update list on screen
        $("select:[id$='SelectCandidate']").trigger("change"); // Doesn't seem to do anything helpful
    });
};

sympmarc wrote Sep 5, 2009 at 4:16 PM

Thanks, Toby. I'll take a look through this over the next few days and see what I can come up with.

M.

Kirikou wrote Sep 8, 2009 at 11:21 PM

Hi,

I've been working on that this afternoon. Here is the changes I made to the .js to make it work. I did not have enough time to test it and clean the code. Let me know if this works for you :
$.fn.SPServices.SPCascadeDropdowns = 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
        relationshipListParentColumn: "", // The name of the parent column in the relationship list
        relationshipListChildColumn: "", // The name of the child column in the relationship list
        parentColumn: "",               // The name of the parent column in the form
        childColumn: ""                     // The name of the child column in the form
    }, options);

    var childType = 0; //0 for lest than 20, 1 for more than twenty, 2 for multiple
    var childTypeIsMultiple = false;
    // Find the child column's select (dropdown)
    var childSelect = $().find("select:[Title='" + opt.childColumn + "']");

    //Should do something about these ifs
    if (childSelect.html() == null) {
        childSelect = $().find("input:[Title='" + opt.childColumn + "']");

        if (childSelect.html() == null) {
            childSelect = $().find("select:[Title='" + opt.childColumn + " possible values']");
            childType = 2
        }
        else {
            childSelect.attr("readonly", "readonly");
            childType = 1;
        }
    }
    // Find the parent column's select (dropdown)
    var parentSelect = $().find("select:[Title='" + opt.parentColumn + "']");

    if (parentSelect.html() == null) {
        parentSelect = $().find("input:[Title='" + opt.parentColumn + "']");
        parentSelect.attr("readonly", "readonly");
        // Bind to the parent column's onchange event
        parentSelect.bind("focus", function() {
            var realSelect = parentSelect.parent().find("#_Select");
            handleEvent(realSelect, childSelect, childType, opt);
            realSelect.trigger("change");
        });
        // Trigger the onchange event for the parent column to set the valid values
        parentSelect.parent().find("img").click();
        parentSelect.blur();
    }
    else {
        handleEvent(parentSelect, childSelect, childType, opt);
        parentSelect.trigger("change");
    }
};

function handleEvent(ctr, childCtr, childType, opt) {
    var parentSelectedValue;
    var displayedOnce = false;
    var choices = "";
    var childSelectSelected = null;
    var childSelectSelectedCtr = null;


    //Had to take this out off the Change binding or infinite loop
    switch (childType) {
        case 2:
            childSelectSelectedCtr = $().find("select:[Title='" + opt.childColumn + " selected values']");
            childSelectSelectedCtr.bind("dblclick", function() {
                ctr.trigger("change");
                childCtr.focus();
            });
            break;
        default:
            //Alert unsupported list type message
    }

    ctr.bind("change", function() {

        switch (childType) {
            case 0:
                childSelectSelected = childCtr.find("option:selected").val();
                break;
            case 1:
                childSelectSelected = childCtr.attr("value");
                break;
        }
        // Get the current child column selection, if there is one
        parentSelectedValue = ctr.find("option:selected").text();

        // When the parent column's selected option changes, get the matching items from the relationship list
        $().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: "" + parentSelectedValue + "",
            // Only get the parent and child columns
            CAMLViewFields: "",
            // Override the default view rowlimit and get all appropriate rows
            CAMLRowLimit: "0",
            completefunc: function(xData, Status) {
                var hasOption = false;
                var size = 0;
                // Add an explanatory prompt
                switch (childType) {
                    case 0:
                        childCtr.attr({ length: 0 }).append("Choose " + opt.childColumn + "...");
                        break;
                    case 1:
                        choices = "(None)|0";
                        childCtr.attr("value", "");
                        break;
                    case 2:
                        childCtr.attr({ length: 0 });
                        break;
                }

                // Add an option for each child item
                $(xData.responseXML).find("z\\:row").each(function() {
                    switch (childType) {
                        case 0:
                            var selected = ($(this).attr("ows_ID") == childSelectSelected) ? " selected='selected'" : "";
                            childCtr.append("" + $(this).attr("ows_" + opt.relationshipListChildColumn) + "");
                            break;
                        case 1:
                            if ($(this).attr("ows_" + opt.relationshipListChildColumn) == childSelectSelected) childCtr.attr("value", childSelectSelected);
                            choices = choices + "|" + $(this).attr("ows_" + opt.relationshipListChildColumn) + "|" + $(this).attr("ows_ID");
                            break;
                        case 2:
                            //Build the option
                            var optionToAdd = "" + $(this).attr("ows_" + opt.relationshipListChildColumn) + "";

                            //Add the option to the select list
                            if (childSelectSelectedCtr.find("option[value='" + $(this).attr("ows_ID") + "']").val() == undefined) {
                                childCtr.append(optionToAdd);
                            }
                            //Test if the second list has at least one 
                            else {
                                hasOption = true;
                            }
                            break;
                    }
                });
                switch (childType) {
                    case 1:
                        childCtr.attr("choices", choices);
                        break;
                    case 2:
                        //If the second select list has no  remove the options
                        if (!hasOption) {
                            childSelectSelectedCtr.find("option").each(function() {
                                $(this).attr("selected", "selected");
                                //Dirty one :)
                                var removeButton = childSelectSelectedCtr.parent().parent().parent().find("button:[id$=RemoveButton]"); //.parent().find("button:[id$='RemoveButton'");
                                //alert(removeButton.html());
                                //"Clean" remove so that SharePoint actually removes the values from the second select list.
                                removeButton.trigger("onclick");
                                childCtr.focus();
                                ctr.trigger("change");
                            });
                        }
                        break;
                }
            }
        });

        // Trigger the child column's onchange event. No need to trigger change for multiple select
        switch (childType) {
            case 0:
                childCtr.trigger("change");
                break;
            case 1:
                ctr.blur();
                childCtr.parent().find("img").click();
                childCtr.blur();
                break;
        }

        if (ctr.css("display") != "none")
            displayedOnce = true;
    });
}

Toadmyster wrote Sep 9, 2009 at 4:27 PM

This is great, Kirikou! It seems to work fine although I have only tested it on a multi-picker and not the regular list. I did make a change to account for my lookup being populated from a calculated field. Apparently when you use a calc field, it stores the type along with the value. So, I changed this line . . .

var optionToAdd = "" + $(this).attr("ows_" + opt.relationshipListChildColumn) + "";

where the filtered option list is built to . . .

var optionToAdd = "" + $(this).attr("ows_" + opt.relationshipListChildColumn).split(";#").pop() + "";

which seems to work good for both calc and non-calc columns. e.g. string;#Bob Fred -> Bob Fred

I'd like to propose 2 options for this function. One is a noSelection option with a default of true that, when set to false, would allow the unfiltered list to show in the child control when no selection is made. A second option that would be helpful is an allowMultipleCategories that would prevent the list of selected items from clearing when the parent control changes. The basic idea here is to filter the choices but not restrict them to just one parent category.

Thanks!
Toby

sympmarc wrote Sep 9, 2009 at 8:58 PM

Toby and Kirikou:

Thanks for your additional work on this! We'll take a look at what you've posted and try to get it into an upcoming release.

M.

meidianto wrote Sep 25, 2009 at 9:37 PM

Hi Kirikou,
The multi-select code is working (I tried with both regular and multi-select at one form), but there is a bug with the "Remove" button of multi-select.
Thanks.

meidianto wrote Sep 25, 2009 at 9:37 PM

Hi Kirikou,
The multi-select code is working (I tried with both regular and multi-select at one form), but there is a bug with the "Remove" button of multi-select - it repopulated the whole options at left ListBox.
Thanks.

meidianto wrote Sep 25, 2009 at 9:37 PM

Hi Kirikou,
The multi-select code is working (I tried with both regular and multi-select at one form), but there is a bug with the "Remove" button of multi-select - it repopulated the whole options at left ListBox.
Thanks.

meidianto wrote Sep 25, 2009 at 9:38 PM

Hi Kirikou,
The multi-select code is working (I tried with both regular and multi-select at one form), but there is a bug with the "Remove" button of multi-select - it repopulated the whole options at left ListBox.
Thanks.

meidianto wrote Sep 25, 2009 at 9:46 PM

(sorry guys, I was not spamming by posting a lot of comments, it was network issue, please remove the duplicated comments - thanks)

nicklhw wrote Oct 20, 2009 at 7:22 PM

I have been using the cascading drop down functionality through out my current project, it has saved me a ton of time! But now I've hit a new requirement that requires the filtering of a child multi-select picker based on a parent multi-select picker. Is it possible to extend the SPCascadeDropdowns function to meet this requirement? If so, can anyone give me any pointers in doing so? Thanks!

sympmarc wrote Oct 20, 2009 at 8:27 PM

nicklhw:

As you can see from this thread, several people want the functionality you're talking about. It's on my list to add as an enhancement. If your timeframe is short, then the post here from Kirikou seems to have the right bits in it, but I ahven't tested it.

If you do work it out, please post back what you add in!

M.

Kirikou wrote Oct 20, 2009 at 8:28 PM

You could try to use the code Toadymaster and I posted below and give some feedback.

nicklhw wrote Oct 21, 2009 at 3:16 PM

Hi Kirikou,

I tried tinkering with the code that you provided below but I am new to jquery so I am hitting some roadblocks. I thought I would start experimenting with some fairly simple stuff first - transferring items between the multi-select picker based on what's selected in the parent when the add button is clicked. I cannot manage to get the options that is selected in the parent multi-select, I will always get a null value returned. It seems that the native sharepoint javascript for the add button would execute first before my custom jquery code. Below is the code, can anyone give me some pointers please?
$.fn.SPServices.SPCascadeMultiSelect = 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
    relationshipListParentColumn: "", // The name of the parent column in the relationship list
    relationshipListChildColumn: "", // The name of the child column in the relationship list
    parentColumn: "", // The name of the parent column in the form
    childColumn: "" // The name of the child column in the form
    }, options);

    // Find the child column's multi-select possible values
    var childSelectCtr = $().find("select:[Title='" + opt.childColumn + " possible values']");
    // Find the parent column's multi-select possible values
    var parentSelectCtr = $().find("select:[Title='" + opt.parentColumn + " possible values']");               
    // Find the child column's multi-select selected values
    var childSelectedCtr = $().find("select:[Title='" + opt.childColumn + " selected values']");
    // Find the parent column's multi-select selected values
    var parentSelectedCtr = $().find("select:[Title='" + opt.parentColumn + " selected values']");

    multiSelectPicker(parentSelectCtr, childSelectCtr, parentSelectedCtr, childSelectedCtr, opt);
    //parentSelect.trigger("change");
}

function multiSelectPicker(parentSelectCtr, childSelectCtr, parentSelectedCtr, childSelectedCtr, opt){

    var displayedOnce = false;
    var choices = "";

var addButton = parentSelectedCtr.parent().parent().parent().find("button:[id$=AddButton]");

addButton.bind("click", function(){
    alert(parentSelectCtr.find("option:selected").text());
    parentSelectCtr.find("option:selected").remove().appendTo(childSelectCtr);          
});     

}

nicklhw wrote Oct 21, 2009 at 4:38 PM

Found the answer.

http://groups.google.com/group/jquery-en/browse_thread/thread/1b2af5ea09471312

It seems that the jquery click() doesn't override the inline sharepoint onclick. I guess I'll just have to work around it.

nicklhw wrote Oct 22, 2009 at 4:04 AM

I think I managed to get the cascaded multi-select working (see attached screen shot). In the screen shot, "Third Party Software Name" is the parent of "Third Party Software Version" and its values are filtered by what's added for "Third Party Software Name". Below is my code which is based on the cascading dropdown function. I am sure that it could be cleaned up and improved, but it does what I need for now.

// Function to set up cascading multi-select picker on a SharePoint form
// (Newform.aspx, EditForm.aspx, or any other customized form.)
$.fn.SPServices.SPCascadeMultiSelect = 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
    relationshipListParentColumn: "", // The name of the parent column in the relationship list
    relationshipListChildColumn: "", // The name of the child column in the relationship list
    parentColumn: "", // The name of the parent column in the form
    childColumn: "" // The name of the child column in the form
    }, options);

    // Find the child column's multi-select possible values container
    var childSelectCtr = $().find("select:[Title='" + opt.childColumn + " possible values']");
    // Find the parent column's multi-select possible values container
    var parentSelectCtr = $().find("select:[Title='" + opt.parentColumn + " possible values']");               
    // Find the child column's multi-select selected values container
    var childSelectedCtr = $().find("select:[Title='" + opt.childColumn + " selected values']");
    // Find the parent column's multi-select selected values container
    var parentSelectedCtr = $().find("select:[Title='" + opt.parentColumn + " selected values']");
    // Find the parent column's multi-select add button
    var parentAddButton = parentSelectedCtr.parent().parent().parent().find("button:[id$=AddButton]");
    // Find the parent column's multi-select remove button
    var parentRemoveButton = parentSelectedCtr.parent().parent().parent().find("button:[id$=RemoveButton]");
    // Find the child column's multi-select remove button
    var childRemoveButton = childSelectedCtr.parent().parent().parent().find("button:[id$=RemoveButton]");

    var queryHead = null;
    var queryTail = null;
    var query = null;
    var buttonType = null;//1 for add button, 2 for remove button               

    parentAddButton.bind("click", function(){           
        //alert(parentSelectedCtr.find("option").size());
        if(parentSelectedCtr.find("option").size()>1){
            queryHead = "";
            queryTail = ""
        }
        else{
            queryHead = "";
            queryTail = "";
        }           
        query = queryHead;          
        parentSelectedCtr.find("option").each(function(){
            //alert($(this).text());
            var parentSelectedValue = $(this).text();
            query = query + "" + parentSelectedValue + "";
        });             
        query = query + queryTail;
        //alert(query);
        buttonType = 1;
        handleMultiSelect(childSelectCtr, childSelectedCtr, query, buttonType, opt);            
    });

    parentRemoveButton.bind("click", function(){            
        //alert(parentSelectCtr.find("option").size());             
        if(parentSelectCtr.find("option").size()>1){
            queryHead = "";
            queryTail = ""
        }
        else{
            queryHead = "";
            queryTail = "";
        }           
        query = queryHead;          
        parentSelectCtr.find("option").each(function(){
            //alert($(this).text());
            var parentSelectedValue = $(this).text();
            query = query + "" + parentSelectedValue + "";
        });             
        query = query + queryTail;
        //alert(query);         
        buttonType = 2;
        handleMultiSelect(childSelectCtr, childSelectedCtr, query, buttonType, opt);            
    });     

    childRemoveButton.bind("click", function(){         
        //alert(parentSelectCtr.find("option").size());             
        if(parentSelectCtr.find("option").size()>1){
            queryHead = "";
            queryTail = ""
        }
        else{
            queryHead = "";
            queryTail = "";
        }           
        query = queryHead;          
        parentSelectCtr.find("option").each(function(){
            //alert($(this).text());
            var parentSelectedValue = $(this).text();
            query = query + "" + parentSelectedValue + "";
        });             
        query = query + queryTail;
        //alert(query);
        buttonType = 2;
        handleMultiSelect(childSelectCtr, childSelectedCtr, query, buttonType, opt);        
    });  

    if(parentSelectedCtr.find("option").html() == null){
        if (childSelectCtr.find("option").html() != null) {
            childSelectCtr.find("option").each(function() {
                $(this).remove();
                childSelectCtr.trigger("change");
            });
        }
    }
    else{
        childRemoveButton.trigger("click");
    }
}

function handleMultiSelect(childSelectCtr, childSelectedCtr, query, buttonType, opt){     

    $().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: query,
        // Only get the parent and child columns
        CAMLViewFields: "",
        // Override the default view rowlimit and get all appropriate rows
        // Took out  node because it was causing the CAML query to return duplicate rows
        CAMLRowLimit: "",
        completefunc: function(xData, Status){
            $(xData.responseXML).find("z\\:row").each(function(){
                if(buttonType == 1){
                    //Build the option
                    var optionToAdd = "" + $(this).attr("ows_" + opt.relationshipListChildColumn) + "";
                    //Add the option to the select list
                    if ((childSelectedCtr.find("option[value='" + $(this).attr("ows_ID") + "']").val() == undefined) && (childSelectCtr.find("option[value='" + $(this).attr("ows_ID") + "']").val() == undefined)) {
                        childSelectCtr.append(optionToAdd);
                    }
                }
                if(buttonType == 2){                        
                    if(childSelectedCtr.find("option[value='" + $(this).attr("ows_ID") + "']").val() != undefined) {
                        childSelectedCtr.find("option[value='" + $(this).attr("ows_ID") + "']").remove();
                    }
                    if(childSelectCtr.find("option[value='" + $(this).attr("ows_ID") + "']").val() != undefined){
                        childSelectCtr.find("option[value='" + $(this).attr("ows_ID") + "']").remove();
                    }
                }
            });
        }
    }); 
}
Thanks Marc for this awesome library! Your blog is super helpful as well, I learned a ton about DVWP from it :).

Nick

sympmarc wrote Oct 22, 2009 at 4:45 AM

Nick:

Great that you got it working and thanks a ton for posting your code back. This enhancement is definitely on my list in the near term.

M.

LisaE wrote Nov 20, 2009 at 9:52 AM

Hi Marc,
I have a question regarding how to keep track of what a multiselected child Item belonged to... as I understand you will soon add multi select items support.

Today I'm using a Cascaded lookup that supports multiple Items from Bamboo Solutions.
(I use it where I have to… but as soon as you have added the support for multiple Items in your Cascaded Dropdown I will immediately switch, as your Cascaded lookup, is so much better for a long number of frustrating reasons...)

My example:

There is a list of Parts:
CPU board
Display
DC/DC
etc…

There is also a list of Failures:
Display broken
Soldering overlap
Water damage
etc...

If I select multiple Parts from the drop down, like:
CPU board
Display

Then of course I have the possibility to choose all the failures that are connected to both of the selected Parts. But if a failure can occur for both of the selected Parts, then I will not know which Part the failure occurred in.

With the Bamboo product I have solved this in the way that I have added the failures in the list for each Part, extending the failure with the name of the part, so the Failure list actually looks like:

Display broken :Display
Soldering overlap :CPU board
Water damage :CPU board
Water damage :Display

(I attached an Image)

Although it's working, there are some problems to this approach.
First of all this means the texts will not fit in the multi-select box (and I cannot make it wider as I cannot modify the Bamboo product), hence I have to restrict the Failure/Part text lengths so that it’s possible to “just about” read what you are selecting.
Also when creating reports in Excel, there is so much text it is difficult to read the information in the Charts.
This also means a lot of extra rows in the Failure list (one failure "copy" for each Part)

Now the question… Will this also be an issue for me when using your Cascaded lookup multiselect items?
I am not a programmer (as taking some classes in C++ more then 10 years ago doesn’t count), and so the question might be enormously stupid! Maybe there is a much better way to go about my problems (using unique keys or something like that)

Any way I wanted to put the question because of my problems using this today…

Thanks,
Lisa

sympmarc wrote Nov 20, 2009 at 2:03 PM

Lisa:

This issue you describe is less one of the controls doing what you want and more one of your "database" design. The relationship you paint is a loose one: many to many. With this construct, you are going to have fuzzy data. The way I am thinking about my version of this won't solve this for you, and the approach you've outlined may still be needed.

The good news from my end is that I've got a client situation where I'm digging into the multi-select and I've already got a better understanding of it. Interestingly, it's the "better" controls in IE that are the biggest problem, just as with the 20+ dropdowns. I've already got the basics working for the other browsers.

M.

LisaE wrote Nov 20, 2009 at 3:08 PM

Thank you Mark for your qucik response.
A big improvment for me would be if the box will be able to expand dynamically based on the with of the text. Then my users would at least be able to see what they are selecting ;o)

/Lisa

sympmarc wrote Dec 10, 2009 at 9:16 PM

Multi-select child columns was in the v0.4.6 release. Multi-select parent columns is now in the v0.4.7ALPHA7 alpha release for testing.