I have a list that that looks up another table to get the value for that data. I can query the table directly, and it returns the data I want. I have another table that uses the previous table as a selection for a field in it. I have queried all the fields in the table that uses the lookup table, and the fields that correspond to that lookup are two of the data fields of the lookup table separated by a semicolon and hash symbol. I've been trying to format a CAML query to filter the data from the table that uses the lookup table based upon the field that is in that table, and get another field from it. Sorry, this really hard to explain, but perhaps after you see my code, you will see what I'm trying to get.
If I do a query on the lookup table like this:
<body>
<script type="text/javascript" src="jquery-1.7.2.min.js"></script>
<script type="text/javascript" src="jquery.SPServices-0.7.1a.js"></script>
var ViewFields = "<ViewFields>" +
"<FieldRef Name='Title' />" +
"<FieldRef Name='Column_x0020_Number' Type='Number' />" +
"</ViewFields>";
$(document).ready(function() {
$().SPServices({
operation: "GetListItems",
async: false,
listName: "Categories",
CAMLViewFields: ViewFields,
completefunc: function (xData, Status) {
$(xData.responseXML).SPFilterNode("z:row").each(function() {
var categoryHtml = "<td>" + $(this).attr("ows_Title") + "</td>";
var columnNumHtml = "<td>" + Math.round($(this).attr("ows_Column_x0020_Number")) + "</td>";
var addRow = "<tr>" + categoryHtml + columnHtml + "</tr>";
$("#Categories").append(addRow);
});
}
});
});
</script>
<table border="1" id="Categories" />
</body>
I get returns like:
Cat1 1 Cat2 2 ....
So, It's working. That's the table that gets the lookup from.
Next we have the code to try and get data form the question table:
var ViewFields = "<ViewFields>" +
"<FieldRef Name='Title' />" +
"<FieldRef Name='Category_x0020_Name' />" +
"<FieldRef Name='Amount' Type='Number' />" +
"<FieldRef Name='Question' />" +
"<FieldRef Name='Answer' />" +
"</ViewFields>";
$().SPServices({
operation: "GetListItems",
async: false,
listName: "Categories",
CAMLViewFields: ViewFields,
completefunc: function (xData, Status) {
$(xData.responseXML).SPFilterNode("z:row").each(function() {
var titleHtml = "<td>" + $(this).attr("ows_Title") + "</td>";
var categoryNameHtml = "<td>" + $(this).attr("ows_Category_x0020_Name")) + "</td>";
var nameHtmlSplitString = categoryNameHtml.split(";#");
var categoryNumString = "<td>" + nameHtmlSplitString[0] + "</td>";
var categoryNameString = "<td>" + nameHtmlSplitString[1] + "</td>";
var amount = "<td>" + Math.round($(this).attr("ows_Amount)) + "</td>";
var question = "<td>" + $(this).attr("ows_Question) + "</td>";
var categoryNumString = "<td>" + $(this).attr("ows_Answer) + "</td>";
var addRow = "<tr>" + titleHtml + categoryNameHtml + categoryNameString + categoryNumString + amountHtml + questionHtml + answerHtml + "</tr>";
$("#Questions").append(addRow);
});
}
});
});
</script>
<table border="1" id="Questions" />
That comes back fairly fine with:
Cat1 Question 1|1;#Cat1|1|Cat1|100|(question 1.1)|(answer 1.1)
Cat1 Question 2|1;#Cat1|1|Cat1|200|(question 1.2)|(answer 1.2)
Cat1 Question 3|1;#Cat1|1|Cat1|300|(question 1.3)|(answer 1.3)
Cat1 Question 4|1;#Cat1|1|Cat1|400|(question 1.4)|(answer 1.4)
Cat1 Question 5|1;#Cat1|1|Cat1|500|(question 1.5)|(answer 1.5)
Cat2 Question 1|2;#Cat2|2|Cat2|100|(question 2.1)|(answer 2.1)
Cat2 Question 2|2;#Cat2|2|Cat2|200|(question 2.2)|(answer 2.2)
Cat2 Question 3|2;#Cat2|2|Cat2|300|(question 2.3)|(answer 2.3)
Cat2 Question 4|2;#Cat2|2|Cat2|400|(question 2.4)|(answer 2.4)
Cat2 Question 5|2;#Cat2|2|Cat2|500|(question 2.5)|(answer 2.5)
Cat3 Question 1|3;#Cat3|3|Cat3|100|(question 3.1)|(answer 3.1)
Cat3 Question 2|3;#Cat3|3|Cat3|200|(question 3.2)|(answer 3.2)
......
So that goes fine almost, except for the "1;#Cat1" returns. I can obviously parse them fine, but now, when I try to write a query:
var Query = "<Query>" +
" <Where>" +
" <Eq>" +
" <FieldRef Name='Category_x0020_Name' />" +
" <Value Type='Text'>" +
" 2;#Cat2" +
" </Value>" +
" </Eq>" +
" </Where>" +
"</Query>";
and I apply it in the SPServices
...
listName: "Categories",
CAMLViewFields: ViewFields,
CAMLQuery: Query,
completefunc: function (xData, Status) {
....
I get nothing.
Any suggestions how to handle the dropdown data?
I did find a way to do it, and I think this may have been a question for http://spservices.codeplex.com/ , or, at least, that's where I finally got an answer. I'm just posting it here, in case anyone else needs help.
It has to do with a item in the query of the "lookup" flag. The query needed to be formatted like this:
var Query = "<Query>" +
"<Where>" +
"<Eq>" +
"<FieldRef Name='Category_x0020_Name' LookupId='True' />" +
"<Value Type='Lookup'>" +
categoryNum +
"</Value>" +
"</Eq>" +
"</Where>" +
"</Query>";
Note: the categoryNum is just a variable that was passed into the function. To get more information on the LookupId, I can't exactly find anything. It has to do with CAML. My understanding is that the LookupId='true' tells the query to use the id of the dropdown menu, where as, if you wanted to just use the value you are looking for, then you can leave the LookupId out. Thus, with LookupId='True', you use the ID, otherwise, just query the data your looking for. Oh, be sure to use the Value Type='Lookup'.
I hope this helps someone.
Thanks