Issue filtering with CascadeDropdowns

Jun 12, 2014 at 12:59 AM
First off; this works -- to an extent. I just need help figuring out what I am missing.

I have (3) drop downs;
  • Department
  • Pillar
  • Measure
The issue I have is trying to get the measure drop down to show the appropriate entries based on the department. Pillar works; if I have 5 different values for a certain department is shows, if I have 3, it shows.

Here is my code; using latest SPServices and jQuery releases.
$(document).ready(function() {
$().SPServices.SPCascadeDropdowns({
relationshipList: "pillars",
relationshipListParentColumn: "Department",
relationshipListChildColumn: "Title",
parentColumn: "Department",
childColumn: "Pillar",
debug: true
});

$().SPServices.SPCascadeDropdowns({
relationshipList: "measures",
relationshipListParentColumn: "Pillar",
relationshipListChildColumn: "Title",
relationshipListSortColumn: "ID",
CAMLQuery: "<Eq><FieldRef Name='Departments'/><Value Type='Text'>" + $("select[title='Department'] option:selected").text() + "</Value></Eq>",
parentColumn: "Pillar",
childColumn: "Measure"
});
});
</script>
The one thing I have noticed is that if I run "$("select[title='Department'] option:selected").text()" and I have a department selected--it returns that department. If I replace this code with "Access" (name of department); Measure filters appropriately.

It seems like this string;
CAMLQuery: "<Eq><FieldRef Name='Departments'/><Value Type='Text'>" + $("select[title='Department'] option:selected").text() + "</Value></Eq>",
Will not work against whatever I have selected in Departments. I'm wondering if maybe I have it in the wrong place in my code?
Jun 12, 2014 at 6:18 PM
Edited Jun 12, 2014 at 6:18 PM
Did some more work; got variables setup; working; doing what I need.

Just won't run CAMLquery: dquery
<script language="javascript" type="text/javascript">
var deptnm
var query
$(document).ready(function() {
$().SPServices.SPCascadeDropdowns({
relationshipList: "pillars",
relationshipListParentColumn: "Department",
relationshipListChildColumn: "Title",
parentColumn: "Department",
childColumn: "Pillar",
completefunc: function() {
deptnm = $("select[title='Department'] option:selected").text()
dquery = "<Eq><FieldRef Name='Departments'/><Value Type='Text'>" + deptnm + "</Value></Eq>"
},
debug: true
});
$().SPServices.SPCascadeDropdowns({
relationshipList: "measures",
relationshipListParentColumn: "Pillar",
relationshipListChildColumn: "Title",
CAMLQuery: dquery,
childColumn: "Measure"
});
});
</script>
Jun 13, 2014 at 11:59 AM
You declared a variable called "query" (that isn't being used) early and outside of any function, then it looks like you've defined the variable "dquery" only within the scope of the complefunc argument for the first cascade dropdowns call (dquery won't be available outside that scope). Based on where you're using that variable, you need to define it outside of the SPServices calls. If you rename "query" at the top to "dquery", it will probably work.

Geoff
Jun 13, 2014 at 12:32 PM
Edited Jun 13, 2014 at 12:33 PM
Mm; typo -- unfortunately didn't work.
CAMLquery: dquery, 
It just doesn't do a thing; it's like it doesn't understand -- which makes no sense to me.

I can sit here and change the department drop down 100 times; pull variable deptnm from firebug and it changes. Even look at dquery and it updates the string with the right "text" I need.

Something doesn't seem right--why can I hardcode
"<Eq><FieldRef Name='Departments'/><Value Type='Text'>Access</Value></Eq>"
And it works; but yet try to use variables so it's dynamic and it doesn't.

Correct code:
<script language="javascript" type="text/javascript">
var deptnm
var dquery
$(document).ready(function() {
$().SPServices.SPCascadeDropdowns({
relationshipList: "pillars",
relationshipListParentColumn: "Department",
relationshipListChildColumn: "Title",
parentColumn: "Department",
childColumn: "Pillar",
completefunc: function() {
deptnm = $("select[title='Department'] option:selected").text()
dquery = "<Eq><FieldRef Name='Departments'/><Value Type='Text'>" + deptnm + "</Value></Eq>"
},
debug: true
});
$().SPServices.SPCascadeDropdowns({
relationshipList: "measures",
relationshipListParentColumn: "Pillar",
relationshipListChildColumn: "Title",
CAMLQuery: dquery,
parentColumn: "Pillar",
childColumn: "Measure"
});
});
</script>
Coordinator
Jun 13, 2014 at 1:11 PM
First off, semi-colons. Always end your lines with semi-colons. Otherwise the browser does a best guess, and believe me, you'll have problems.

Are you sure that
$("select[title='Department'] option:selected").text()
is getting the value you intend? Have you looked at the traffic in Firebug or Fiddler to see if you have any errors?

If there are 20+ items in the dropdown, then the value will be in an input, not a select. If you have the December CU or SP2 on SharePoint 2010 or you are on Office365 and the column is required, the Title will be "Department Required Field".

M.
Jun 13, 2014 at 1:18 PM
Hello M.

It's SP 2007 and I am certain that
$("select[title='Department'] option:selected").text()
is getting the value I need.

I can change the department drop down and the variable changes each time (in firebug you can type in variable name to get value); and also I can pull the dquery value and see the entire string with the appropriate text set.
Coordinator
Jun 13, 2014 at 1:47 PM
Look on the Net tab, XHR sub-tab, and see if the request is erroring out.

M.
Jun 13, 2014 at 3:02 PM
No errors in post through XHR; I select department, pillar, and then Measure drop down just stays with (None).

Each time I see POST Lists.asmx 200 OK.
Coordinator
Jun 13, 2014 at 3:53 PM
Edited Jun 13, 2014 at 3:55 PM
Do you see the values you'd expect coming back in the XML tab?

The dquery you have in the first completefunc isn't going to do anything. In fact, you don't need it at all. That's the whole point of the cascading idea. The function takes care of it for you after you make the call.

Pillars depend on the Department selection
Measures depend on the Pillar selection

M.
Jun 13, 2014 at 4:11 PM
I completely understand M.

However, the problem here is that "Measures" is dependent on Pillar and Department. If I run this normally; it works fine -- except that Measures ends up with every measure associated with a Pillar.

Each department can have 1-5 of the Pillars; inside of these Pillars is a number of measures--the measures are not same for every department.

If that makes sense?

What I need Measure drop down to do is to filter by both Department and Pillar (which are in the list).
Coordinator
Jun 13, 2014 at 4:24 PM
OK, the function won't do what you're looking for.

The CAMLQuery is only applied when you first make the call and things are set up. It will have no effect when you make selections.

M.
Jun 13, 2014 at 4:27 PM
OYE; I was afraid of that; it truly only handles 1-1 relationship.

Do you have any recommendations on what I could do/look at?

All I need in Measures is for it to filter "AND" by two columns; not just one.
Coordinator
Jun 13, 2014 at 4:35 PM
I've never tried to generalize the function to do what you want. I've always worried that there was too much possible variation.

You could certainly clone the function and adapt it to your needs. If you do, I'd love to see what you come up with!

M.