SPCascadeDropdowns with additonal filter

Jul 23, 2010 at 11:34 AM

Hi all,

I'm using the function SPCascadeDropdowns and it works fine. Now I want to filter the parent drop down menu. Is there an easy way to achive this? I tried the CAMLQuery but this only filters the second pull down.

To better explain everything, here are my lists:

Parent drop down list:
Title
Parent 1
Parent 2
Parent 3

Child drop down list:

Title

Parent

Typ

Value 1

Parent 1

Typ 1

Value 2

Parent 1

Typ 1

Value 3

Parent 2

Typ 2

Value 4

Parent 2

Typ 1

Value 5

Parent 3

Typ 3

Value 6

Parent 3

Typ 3

I want to filter the parent drop down list with the column "typ".

Thank you so much for your help!

Bye
Nicole

Coordinator
Jul 23, 2010 at 12:45 PM

Nicole:

I don't have a capability in SPCascadeDropdowns to filter the parentColumn.  That's sort of a different need, and perhaps an opportunity for a new function! Maybe SPFilterLookupColumn?

M.

Jul 23, 2010 at 1:51 PM
Edited Jul 23, 2010 at 1:55 PM

Hi Marc,

thanks for your response. After thinking about everything again I think I have a solution but I haven't tested it yet.

I move the coulmn "typ" up and make another parent-child connection between "typ" and "parent". When I load the new form I hide the drop down menu for "typ" and set the value with a parameter (I hope that will work). The user will see only the drop down menus for parent and child, but the parent will be filtered through the hidden drop down menu.

But the new function would be a great idea because I think this would be easier.

Bye
Nicole

 

edit:
Oh well, I knew I had an error in reasoning.... The children of one parent can have a different typ... I'll think over it again.

Jul 23, 2010 at 3:47 PM

OK, I have a different questsion regarding the cascadeDropdowns function. If I should open a new discussion I can do this.

I have three drop down menus on my new form: drop1, drop2, drop3

drop1 is the parent from drop2 and drop2 is the parent from drop3. The cascade function works perfect. Now I want to set a value for drop1 through an url parameter. That's working too. But if I do this drop2 doesn't update. I think the problem is, that drop2 is missing the event for choosing a value at drop1. Do you know what I have to do, that drop2 gets this required event? I tried to look in the javascript code but it's a bit overwhelming. My guess is that I just have to call a function to fire this event.

Bye
Nicole

P.S. I'm trying my suggestion from above with a modified solution.

 

Coordinator
Jul 24, 2010 at 1:22 AM
PGh0bWw+PGJvZHkgYmdjb2xvcj0iI0ZGRkZGRiI+PGRpdj5OaWNvbGU6PC9kaXY+PGRpdj48YnI+ PC9kaXY+PGRpdj5XaGF0IHlvdSBhcmUgZGVzY3JpYmluZyBvdWdodCB0byB3b3JrIGlmIHlvdSBz ZXQgdGhlIHZhbHVlIG9mIGRyb3AxICphZnRlciogeW91IG1ha2UgdGhlIHR3byBjYWxscyB0byBT UENhc2NhZGVEcm9wZG93bnMuIElmIHlvdSBkbyBpdCBiZWZvcmUsIHRoZSBldmVudHMgd29uJ3Qg YmUgd2lyZWQgdXAgeWV0LjwvZGl2PjxkaXY+PGJyPjwvZGl2PjxkaXY+TS48YnI+PGJyPjxkaXY+ PGJyPjwvZGl2PjwvZGl2PjxkaXY+PGJyPk9uIEp1bCAyMywgMjAxMCwgYXQgMTA6MzcsICJtaXJp ZWwiICZsdDs8YSBocmVmPSJtYWlsdG86bm90aWZpY2F0aW9uc0Bjb2RlcGxleC5jb20iPm5vdGlm aWNhdGlvbnNAY29kZXBsZXguY29tPC9hPiZndDsgd3JvdGU6PGJyPjxicj48L2Rpdj48ZGl2Pjwv ZGl2PjxibG9ja3F1b3RlIHR5cGU9ImNpdGUiPjxkaXY+IDxzdHlsZT5Cb2R5e2ZvbnQtZmFtaWx5 OiBWZXJkYW5hOyBmb250LXNpemU6IDAuNzVlbTt9I1RocmVhZE5vdGlmaWNhdGlvbkZvb3Rlcntj b2xvcjogZ3JheTsgYm9yZGVyLXRvcDogMXB4IHNvbGlkICNjY2M7fSNUaHJlYWROb3RpZmljYXRp b25Qb3N0Qm9keXtNYXJnaW4tQm90dG9tOiAyZW07fTwvc3R5bGU+PHA+RnJvbTogbWlyaWVsPC9w PiA8ZGl2IGlkPSJUaHJlYWROb3RpZmljYXRpb25Qb3N0Qm9keSI+PHA+T0ssIEkgaGF2ZSBhIGRp ZmZlcmVudCBxdWVzdHNpb24gcmVnYXJkaW5nIHRoZSBjYXNjYWRlRHJvcGRvd25zIGZ1bmN0aW9u LiBJZiBJIHNob3VsZCBvcGVuIGEgbmV3IGRpc2N1c3Npb24gSSBjYW4gZG8gdGhpcy48L3A+DQo8 cD5JIGhhdmUgdGhyZWUgZHJvcCBkb3duIG1lbnVzIG9uIG15IG5ldyBmb3JtOiBkcm9wMSwgZHJv cDIsIGRyb3AzPC9wPg0KPHA+ZHJvcDEgaXMgdGhlIHBhcmVudCBmcm9tIGRyb3AyIGFuZCBkcm9w MiBpcyB0aGUgcGFyZW50IGZyb20gZHJvcDMuIFRoZSBjYXNjYWRlIGZ1bmN0aW9uIHdvcmtzIHBl cmZlY3QuIE5vdyBJIHdhbnQgdG8gc2V0IGEgdmFsdWUgZm9yIGRyb3AxIHRocm91Z2ggYW4gdXJs IHBhcmFtZXRlci4gVGhhdCdzIHdvcmtpbmcgdG9vLiBCdXQgaWYgSSBkbyB0aGlzIGRyb3AyIGRv ZXNuJ3QgdXBkYXRlLiBJIHRoaW5rIHRoZSBwcm9ibGVtIGlzLCB0aGF0IGRyb3AyIGlzIG1pc3Np bmcgdGhlIGV2ZW50IGZvciBjaG9vc2luZyBhIHZhbHVlIGF0IGRyb3AxLiBEbyB5b3Uga25vdyB3 aGF0IEkgaGF2ZSB0byBkbywgdGhhdCBkcm9wMiBnZXRzIHRoaXMgcmVxdWlyZWQgZXZlbnQ/IEkg dHJpZWQgdG8gbG9vayBpbiB0aGUgamF2YXNjcmlwdCBjb2RlIGJ1dCBpdCdzIGEgYml0IG92ZXJ3 aGVsbWluZy4gTXkgZ3Vlc3MgaXMgdGhhdCBJIGp1c3QgaGF2ZSB0byBjYWxsIGEgZnVuY3Rpb24g dG8gZmlyZSB0aGlzIGV2ZW50LjwvcD4NCjxwPkJ5ZTxicj5OaWNvbGU8L3A+DQo8cD5QLlMuIEkn bSB0cnlpbmcgbXkgc3VnZ2VzdGlvbiBmcm9tIGFib3ZlIHdpdGggYSBtb2RpZmllZCBzb2x1dGlv bi48L3A+DQo8cD4mbmJzcDs8L3A+PC9kaXY+IDxkaXYgaWQ9IlRocmVhZE5vdGlmaWNhdGlvbkZv b3RlciI+IDxwPlJlYWQgdGhlIDxhIGhyZWY9Imh0dHA6Ly9zcHNlcnZpY2VzLmNvZGVwbGV4LmNv bS9UaHJlYWQvVmlldy5hc3B4P1RocmVhZElkPTIyMDk2MCZhbXA7QU5DSE9SI1Bvc3Q0NzE2NDQi PmZ1bGwgZGlzY3Vzc2lvbiBvbmxpbmU8L2E+LjwvcD4gPHA+VG8gYWRkIGEgcG9zdCB0byB0aGlz IGRpc2N1c3Npb24sIHJlcGx5IHRvIHRoaXMgZW1haWwgKDxhIGhyZWY9Im1haWx0bzpTUFNlcnZp Y2VzQGRpc2N1c3Npb25zLmNvZGVwbGV4LmNvbT9zdWJqZWN0PVtTUFNlcnZpY2VzOjIyMDk2MF0i PjxhIGhyZWY9Im1haWx0bzpTUFNlcnZpY2VzQGRpc2N1c3Npb25zLmNvZGVwbGV4LmNvbSI+U1BT ZXJ2aWNlc0BkaXNjdXNzaW9ucy5jb2RlcGxleC5jb208L2E+PC9hPik8L3A+IDxwPlRvIHN0YXJ0 IGEgbmV3IGRpc2N1c3Npb24gZm9yIHRoaXMgcHJvamVjdCwgZW1haWwgPGEgaHJlZj0ibWFpbHRv OlNQU2VydmljZXNAZGlzY3Vzc2lvbnMuY29kZXBsZXguY29tIj48YSBocmVmPSJtYWlsdG86U1BT ZXJ2aWNlc0BkaXNjdXNzaW9ucy5jb2RlcGxleC5jb20iPlNQU2VydmljZXNAZGlzY3Vzc2lvbnMu Y29kZXBsZXguY29tPC9hPjwvYT48L3A+IDxwPllvdSBhcmUgcmVjZWl2aW5nIHRoaXMgZW1haWwg YmVjYXVzZSB5b3Ugc3Vic2NyaWJlZCB0byB0aGlzIGRpc2N1c3Npb24gb24gQ29kZVBsZXguIFlv dSBjYW4gPGEgaHJlZj0iaHR0cHM6Ly9zcHNlcnZpY2VzLmNvZGVwbGV4LmNvbS9zdWJzY3JpcHRp b25zL3RocmVhZC9wcm9qZWN0L2VkaXQiPnVuc3Vic2NyaWJlIG9yIGNoYW5nZSB5b3VyIHNldHRp bmdzPC9hPiBvbiA8YSBocmVmPSJodHRwOi8vY29kZVBsZXguY29tIj5jb2RlUGxleC5jb208L2E+ LjwvcD4gPHA+UGxlYXNlIG5vdGU6IEltYWdlcyBhbmQgYXR0YWNobWVudHMgd2lsbCBiZSByZW1v dmVkIGZyb20gZW1haWxzLiBBbnkgcG9zdHMgdG8gdGhpcyBkaXNjdXNzaW9uIHdpbGwgYWxzbyBi ZSBhdmFpbGFibGUgb25saW5lIGF0IDxhIGhyZWY9Imh0dHA6Ly9jb2RlcGxleC5jb20iPjxhIGhy ZWY9Imh0dHA6Ly9jb2RlcGxleC5jb20iPmNvZGVwbGV4LmNvbTwvYT48L2E+PC9wPiA8L2Rpdj4g IDwvZGl2PjwvYmxvY2txdW90ZT48L2JvZHk+PC9odG1sPg= --Apple-Mail-5-975668434--
Jul 24, 2010 at 9:15 AM

You're iPhone definitely doesn't like this forum, does it? :o)

Coordinator
Jul 24, 2010 at 1:50 PM
Or vice versa. Sorry about that! Here's what I sent: Nicole: What you are describing ought to work if you set the value of drop1 *after* you make the two calls to SPCascadeDropdowns. If you do it before, the events won't be wired up yet. M.
Coordinator
Jul 24, 2010 at 2:03 PM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.
Jul 26, 2010 at 4:21 PM

That's no problem :o) Also I hope you had a great weekend!

You were correct I set the value before I made my calls to SPCascadeDropdowns. But If I put it after these calls it don't work too. For setting the value I use the code from this site: http://blogs.msdn.com/b/sharepoint/archive/2007/06/21/using-javascript-to-manipulate-a-list-form-field.aspx

I skimed over the spservices documentation, but couldn't find a similar function here. If the above code isn't good, maybe you could suggest a good one?

Coordinator
Jul 28, 2010 at 1:35 AM

The only thing I can think of is that a change event isn't triggered. You can manually trigger it with .trigger("change") on the dropdown.  If there are fewer than 20 options, then it's a simple SELECT, and that should be pretty easy.

M.

Jul 28, 2010 at 10:08 AM

Yes, I got it! Thank you so much for your help!

I just needed to add the trigger in the code, that preselects the first drop down menu.

Nicole

Coordinator
Jul 28, 2010 at 12:34 PM

Excellent!

M.

Jul 29, 2010 at 10:45 AM

Hi Marc,

I've also had a need for a function like SPFilterLookupColumn.

I've had a play around with creating it, borrowing logic from SPAutocomplete and SPCascadeDropdowns.  I have a working version, but I'm sure you could work some magic to make it even more generic...or gain some efficiencies through code reuse.  Please let me know what you think.  This could also be a solution to the following discussion thread: http://spservices.codeplex.com/Thread/View.aspx?ThreadId=210779

In my scenario I have an Image Library called 'Feature Image Library', where I have applied a 'Choice' column called "Image Type" that can be values 'Feature', 'Promotion', both, or neither.  I'm using this Image Library as a lookup in another list in a column called 'PromotionImage', where I just want to show the values in 'Feature Image Library' where the 'Image Type' is 'Promotion'  Here is how I'm calling SPFilterLookupColumn:

		$().SPServices.SPFilterLookupColumn ({	
			lookupList: "Feature Image Library",
			lookupColumnName: "Title",
			targetColumnName: "PromotionImage",
			CAMLQuery: "<Eq><FieldRef Name='Image_x0020_Type'/><Value Type='Choice'>Promotion</Value></Eq>",
			CAMLQueryOptions: "<QueryOptions><ViewAttributes Scope='RecursiveAll'/></QueryOptions>"
		});

Here is SPFilterLookupColumn:

    $.fn.SPServices.SPFilterLookupColumn = function(options) {

        var opt = $.extend({}, {
            lookupList: "", 			// The name of the list which contains the values
            lookupColumnName: "", 			// The static name of the column which contains the values
            targetColumnName: "", 			// The display name of the column in the form
            CAMLQuery: "", 			// This CAML fragment will be Anded with the default query on the relatedList
            CAMLQueryOptions: ""			// Optional.  CAML Query Options. <QueryOptions><ViewAttributes Scope='RecursiveAll'/></QueryOptions> is beneficial when querying a document library.
            }, options);

        var columnObj = $("input[Title='" + opt.targetColumnName + "']");
        $("input[Title='" + opt.targetColumnName + "']").css("position", "");
        var columnObjId = $(columnObj).attr("ID");
        var columnObjColor = $(columnObj).css("color");

        if (columnObj.html() == null && opt.debug) {
            errBox("SPServices.SPAutocomplete",
				"targetColumnName: " + opt.targetColumnName,
				"Column is not an input control or is not found on page");
            return;
        }

        // Find the target column's select (dropdown)
        var targetSelect = new dropdownCtl(opt.targetColumnName);
        if (targetSelect.Obj.html() == null) { errBox("SPServices.SPCascadeDropdowns", "targetColumnName: " + opt.targetColumnName, "Column not found on page"); return; }

        // Get information about the targetColumn from the current list
		var targetColumnRequired;
        $().SPServices({
            operation: "GetList",
            async: false,
            listName: listNameFromUrl(),
            completefunc: function(xData, Status) {
                $(xData.responseXML).find("Fields").each(function() {
                    $(this).find("Field").each(function() {
                        // Determine whether targetColumn is Required
                        if ($(this).attr("DisplayName") == opt.targetColumnName) {
                            targetColumnRequired = ($(this).attr("Required") == "TRUE") ? true : false;
                            // Stop looking; we're done
                            return false;
                        }
                    });
                });
            }
        });

        // Get the current target column's selection, if there is one
        var targetSelectSelected = null;

        switch (targetSelect.Type) {
            case "S":
                targetSelectSelected = targetSelect.Obj.find("option:selected").val();
                break;
            case "C":
                targetSelectSelected = targetSelect.Obj.attr("value");
                break;
            case "M":
                MultiLookupPickerdata = targetSelect.Obj.closest("span").find("input[name$='MultiLookupPicker$data']");
                master = window[targetSelect.Obj.closest("tr").find("button[id$='AddButton']").attr("id").replace(/AddButton/, 'MultiLookupPicker_m')];
                currentSelection = targetSelect.Obj.closest("span").find("select[ID$='SelectResult'][Title^='" + opt.targetColumnName + " ']");
                // Clear the master
                master.data = "";
                break;
            default:
                break;
        }


        var choices = "";
        // Add an explanatory prompt
        switch (targetSelect.Type) {
            case "S":
                targetSelect.Obj.attr({ length: 0 })
                // If the column is required or the promptText option is empty, don't add the "(None) option
                if (!targetColumnRequired && (opt.promptText.length > 0)) targetSelect.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 = targetColumnRequired ? "" : "(None)|0";
                targetSelect.Obj.attr("value", "");
                break;
            case "M":
                targetSelect.Obj.attr({ length: 0 });
                newMultiLookupPickerdata = "";
                break;
            default:
                break;
        }

        // Build the appropriate CAMLQuery
		var camlQuery = "<Query><OrderBy><FieldRef Name='" + opt.lookupColumnName + "'/></OrderBy><Where>";
        if (opt.CAMLQuery.length > 0) camlQuery += "<And>";
        camlQuery += "<IsNotNull><FieldRef Name='" + opt.lookupColumnName + "'/></IsNotNull>";
        if (opt.CAMLQuery.length > 0) camlQuery += opt.CAMLQuery + "</And>";
        camlQuery += "</Where></Query>";

        // Call GetListItems to find all of the potential values
        $().SPServices({
            operation: "GetListItems",
            async: false,
            listName: opt.lookupList,
            CAMLQuery: camlQuery,
            CAMLViewFields: "<ViewFields><FieldRef Name='" + opt.lookupColumnName + "' /></ViewFields>",
            CAMLQueryOptions: opt.CAMLQueryOptions,
            CAMLRowLimit: 0,
            completefunc: function(xData, Status) {

	            // 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.lookupColumnName).indexOf(";#") > 0) ?
							$(this).attr("ows_" + opt.lookupColumnName).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.lookupColumnName).indexOf(";#") > 0) ?
							$(this).attr("ows_" + opt.lookupColumnName).split(";#")[1] :
							$(this).attr("ows_" + opt.lookupColumnName);

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

                switch (targetSelect.Type) {
                    case "S":
                        targetSelect.Obj.trigger("change");
                        break;
                    case "C":
                        targetSelect.Obj.attr("choices", choices);
                        targetSelect.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");
                            $(targetSelect.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
                        $(targetSelect.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;
                }
                
            }
        });
        
    }
I look forward to your reply, and thanks again so much for such great tools!
All the best,
David Kearfott
Coordinator
Jul 29, 2010 at 12:15 PM

David:

This is excellent!  Thanks very much for pitching this in. I'll go through it and figure out how to make it a full-fledged SPServices function as soon as I can.

M.

Coordinator
Jul 29, 2010 at 12:17 PM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.