SPCascadeDropdowns - Cascading dropdown based off of two previous selections combined?

Nov 17, 2011 at 11:10 PM

Hello, I had a question about the SPCascadeDropdowns function.

As the list stands now, the user can sort from three options (A,B,and C) Choice A narrows Choice B, which then Narrows Choice C.
However, I noticed that choice A could be used to narrow choice C, or choice B could be used to narrow choice C, in my solution. Using the function, I can sort either A -> C (Independant of B), or B -> C (Independant of A). I would like to have both A+B -> C, both dependdant on each other. Is this possible?

Coordinator
Nov 17, 2011 at 11:18 PM
AmericanAl:

No, SPCascadeDropdowns doesn't support that. You might be able to kludge something together by concatenating values, but I doubt it.

I have to build my functions to handle the most common situations, and while I've had several requests like yours, it would be very hard to generalize it, I think.

M.
Nov 18, 2011 at 12:13 AM

Ah, thanks Marc. It's not a dealbreaker, and I'll most likely go with the route that narrows the final choice down the most.

Thanks for the quick answer!

Nov 18, 2011 at 10:10 AM

You could do this if you leave the first 2 selects (A+B) connected as cascade and allow the 3rd select to be a custom one. You would have to construct your own CAML from the 2 dropdown values "AND"ing them together. Your list that supplies the items for the 3rd dropdown would have to support the columns of course, but I do this a lot.

Coordinator
Nov 18, 2011 at 11:16 AM
Dan:

I'd be interested to see an example of this, if you could share one.

M.
Nov 18, 2011 at 1:43 PM

Which type? I have to pull them out of a VM I think, but I have a custom newform that adds a 3rd select and I might have another one. It might take a bit to pull them out, but I will shoot you what I can as soon as I can.

Coordinator
Nov 18, 2011 at 1:50 PM

No rush. I'm just curious to see an example of what you described above.

M.

Nov 18, 2011 at 2:36 PM

I'm also intrigued by this. Sounds very nifty!

Nov 18, 2011 at 4:40 PM
Edited Nov 18, 2011 at 4:42 PM

This is something that I think will work as an example. It was a testing example so it might not make the best sense! I used the CAMLQuery option on the second dropdown to add the first dropdown value to the query. This assumes that the list being used for SmallCategories has the correct columns "LargeCategory", "MediumCategory", and "Title" (SmallCategory).

<script language="javascript" type="text/javascript">
$(document).ready(function() {
	$().SPServices.SPComplexToSimpleDropdown({	
		columnName: "LargeCategory",
		completefunc: null,
		debug: true
	});

	$().SPServices.SPCascadeDropdowns({
		relationshipList: "MediumCategories",
		relationshipListParentColumn: "MasterCategory",
		relationshipListChildColumn: "Title",
		parentColumn: "LargeCategory",
		childColumn: "MediumCategory",
		simpleChild: true,
		debug: true
	});
	$().SPServices.SPCascadeDropdowns({
		relationshipList: "SmallCategories",
		relationshipListParentColumn: "MediumCategory",
		relationshipListChildColumn: "Title",
		relationshipListSortColumn: "ID",
		parentColumn: "MediumCategory",
		childColumn: "SmallCategory",
		CAMLQuery: "<Eq><FieldRef Name='LargeCategory'><Value Type='Text'>" + $("select[title*='LargeCategory'] option:selected").val() + "</Value></Eq>",
		simpleChild: true
	});

});
</script>
Let me know if I missed something. I have not found all the stuff yet.
Nov 18, 2011 at 10:17 PM
Edited Nov 18, 2011 at 11:20 PM

The only way I've been able to do this is to alter the CAMLQuery handling in the cascade function to allow for anonymous functions to be passed to the cascade initialization in addition to straight text.  Basically, this allows you to write dynamic CAMLQuery expressions.

In SPServices, first make sure to add a check for a non-empty string in the passed CAMLQuery in order to add the outer <And> for the query.

		var sortColumn = (opt.relationshipListSortColumn.length > 0) ? opt.relationshipListSortColumn : opt.relationshipListChildColumn;
		var camlQuery = "<Query><OrderBy><FieldRef Name='" + sortColumn + "'/></OrderBy><Where>";
		if(opt.CAMLQuery.length > 0 || opt.CAMLQuery != "") {
			camlQuery += "<And>";
		}

Then, add the case to evaluate a passed function when constructing the ANDed CAML.  Note that the function you pass must return a valid CAML string.
		if(opt.CAMLQuery.length > 0) {
			camlQuery += opt.CAMLQuery + "</And>";
		}else if(opt.CAMLQuery != "") {
                    camlQuery += opt.CAMLQuery() + "</And>";
                  }
On the markup side, this then allows you to contruct a CAMLQuery that gets evaluated everytime its associated cascade changes like so . . .
  $().SPServices.SPCascadeDropdowns({
    relationshipList: "{DD1C6120-5ACC-4607-8B14-8DC39A641456}",
    relationshipListParentColumn: "Form_x002d_IssueCategory",
    relationshipListChildColumn: "Title",
    parentColumn: "Issue Category",
    childColumn: "Issue",
    promptText: "Choose {0}...",
    CAMLQuery: function() { return "<And><Eq><FieldRef Name='Form_x002d_IssueType' /><Value Type='Text'>" + $("select[title='Issue Type']").find("option:selected").text() + "</Value></Eq><Eq><FieldRef Name='Active' /><Value Type='Boolean'>1</Value></Eq></And>" },
    completefunc: null
});

Where in AmericanAl's example, choice A is "Issue Type", choice B is "Issue Category", and choice C is "Issue".  Issue Category is a cascade that is filtered by the result from cascade Issue Type (the normal function of the cascade dropown) and the Issue is filtered by the filtered Issue Category cascade AND the Issue Type by means of the function in the CAMLQuery option.
I just threw this together so if there are any performance/funtionality improvements that you can think of, please enlighten me.  This works good in my tests (knowing that my Issue Type is a simple select), and allows me to draw from any non-cascade fields that might change on a form as well as this cascade example.
Hope this helps. If you need a data sample to make it more clear just let me know.
Toby