Adding CAML Query results in no filtering at all

Aug 10, 2012 at 7:55 AM

Here's the code I'm trying to use:

<script src="/_layouts/jquery.js" type="text/javascript"></script>
<script src="/_layouts/jquery.SPServices-0.7.0.js" type="text/javascript"></script>
<script language="javascript" type="text/javascript">
	$(document).ready(function() {
		$().SPServices.SPCascadeDropdowns({
			relationshipList: "Projects",
			relationshipListParentColumn: "Company",
			relationshipListChildColumn: "Title",
			relationshipListSortColumn: "ID",
			parentColumn: "Company",
			childColumn: "Project Name",
			CAMLQuery: "<Eq><FieldRef Name=\"Show Active\" /><Value Type=\"Text\">Yes</Value><FieldRef /></Eq>"
			CAMLQueryOptions: "<QueryOptions><QueryOptions />",
			completefunc: null,
			debug: false
		});
	});
</script>

The Show Active field is in the Projects list and is a string. If it is Yes, then I want the item in the filtered list. If I remove the CAMLQuery line I get the list returned with what I expect. Only projects for the company are listed. However, when I add the CAMLQuery then all Projects for all companies is returned. There are no errors. It seems like it is hitting some error, but I don't know what it is. Any help is appreciated. Oh, and I'm using this on a NewForm that I've created, not the default one. I've tried adding this code straight into the form code and also as a separate file attached to a Content Editor Web Part. In both cases it works without the CAML query to filter on the ParentColumn, but no filtering happens with the CAMLQuery.

 

Any help is appreciated.

Aug 10, 2012 at 9:13 AM
I think your query filter needs to have the internal name of the field. Not the external/visible name. So 'Show Fields' is probably really 'Show_x0020_Fields'.

_____
Paul

Sent from mobile device.

Aug 10, 2012 at 11:56 AM

Thanks. I looked it up and you're right about the name. So, I tried Show_x0020_Active, but I get the same result. Nothing is filtered. I've tried it a dozen different ways and I can't get any filtering to happen.

I have three tables. Companies, Projects, and Time Entries. When you go to enter a time entry using the New Form I just want it to display "active" projects for a given company that is selected on the form. Without the CAML filter it pulls only the projects that are linked to a particular company which is great. But when I try to use the CAML Query to filter that further to only display the active projects then it displays all the projects for all companies. I'm beginning to wonder if the structure of one of the tables is wrong or something is corrupted. Any advice is appreciated.

Coordinator
Aug 10, 2012 at 12:46 PM

If it's showing all of the values without filtering when you add the CAML, then you're probably introducing a bug. A little debugging in the Developer Tools would probably show you where in your script it's happening.

In this case, I believe it's the missing comma on the CAMLQuery line.

M.

Aug 10, 2012 at 1:25 PM
Edited Aug 10, 2012 at 6:28 PM

The missing comma would certainly cause a problem.  Also, is the field "Show_x0020_Active" actually a "Text" field type or is it a "Yes/No" (Boolean) field type.  If it's actually stored in SharePoint as a "Yes/No" field type, the proper CAML syntax is...

CAMLQuery: "<Eq><FieldRef Name='Show_x0020_Active' /><Value Type='Bool'>true</Value></Eq>",

 

 

Aug 11, 2012 at 5:21 AM

Thanks for the help and the quick response. I'm getting much farther now that I have a comma on the end. I'm actually a Javascript programmer so it is kind of embarrassing that I missed that. I also corrected the Field name also so that it said "Show_x0020_Active", which I verified is correct.

However, after adding the comma and fixing the field name I now get a message from spservices stating that "relationshipListParentColumn: Company or relationshipListChildColumn: Title" is "Not found in relationshipList Projects". That's weird because it doesn't give that message when I remove the CAMLQuery. Also, I know for a fact that there are some values in the project list that have Yes for the "Show Active" field. So, it shouldn't be returning an empty list or anything.

I liked the Boolean suggestion. So, I also tried creating a new field called Active? with a Boolean value and it still has the same message.

Here's my code now with the Boolean field I created in the Projects list. Keep in mind that I get the same error message whether I use the new Boolean value or the original Text value. Also, in both cases, if I remove the CAMLQuery there is no error and a list of projects for the currently selected company is returned. With the CAMLQuery I get nothing returned and the error message. Thanks for any additional guidance you may have.

 

<script src="/_layouts/jquery.js" type="text/javascript"></script>
<script src="/_layouts/jquery.SPServices-0.7.0.js" type="text/javascript"></script>
<script language="javascript" type="text/javascript">
	$(document).ready(function() {
		$().SPServices.SPCascadeDropdowns({
			relationshipList: "Projects",
			relationshipListParentColumn: "Company",
			relationshipListChildColumn: "Title",
			relationshipListSortColumn: "ID",
			parentColumn: "Company",
			childColumn: "Project Name",
			CAMLQuery: "<Eq><FieldRef Name='Active_x003f_' /><Value Type='Text'>1</Value><FieldRef /></Eq>",
			CAMLQueryOptions: "<QueryOptions><QueryOptions />",
			debug: true
		});
	});
</script>

Aug 14, 2012 at 12:04 AM

So, I finally figured this out. I needed to remove the <FieldRef /> near the end of the CAML query. I'm not even sure why I had that in there anyway. I also re-created the boolean field to have no question mark since I didn't like the way it looked in the code. Thanks again for the help. It's doubtful I would have figured it out without a couple extra eyes on it.

            $().SPServices.SPCascadeDropdowns({
				relationshipList: "Projects",
				relationshipListParentColumn: "Company",
				relationshipListChildColumn: "Title",
				relationshipListSortColumn: "ID",
				parentColumn: "Company",
				childColumn: "Project Name",
				CAMLQuery: "<Eq><FieldRef Name='Active' /><Value Type='Boolean'>1</Value></Eq>",
                                debug: true
			});