SharePoint2007 query a list with a field based off another list

Sep 26, 2014 at 1:30 PM
Edited Sep 26, 2014 at 1:30 PM
I have a list that has one field built off another lists data. I'm trying to base a query where the field that is tied to the dropdown is one of the fields in it with another in the query from where I want to get the data. i.e. (for example if the tied field is like 2;#Cat2 as the data to query)

var ViewFields = "<ViewFields>" +
                           "<FieldRef Name='Title' />" +
                           "<FieldRef Name='Category_x0020_Name' />" +
                           "<FieldRef Name='Amount' Type='Number' />" +
                           "<FieldRef Name='Question' />" +
                           "<FieldRef Name='Answer' />" +

var Query = "<Query>" +
                    "    <Where>" +
                    "        <Eq>" +
                    "            <FieldRef Name='Category_x0020_Name' />" +
                    "                <Value Type='Text'>" +
                    "                    2;#Cat2" +
                    "                </Value>" +
                    "         </Eq>" +
                    "    </Where>" +
                    " </Query>";

$(document).ready(function() {
        operation: "GetListItems",
        async: false,
        listName: "Categories",
        CAMLViewFields:  ViewFields,
        CAMLQuery: Query;
        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 amountHtml = "<td>" + Math.round($(this).attr("ows_Amount")) + "</td>";
                var questionHtml = "<td>" + $(this).attr("ows_Question") + "</td>";
                var answerHtml = "<td>" + $(this).attr("ows_Answer") + "</td>";
                var addRow = "<tr>" + titleHtml + categoryNameHtml + amountHtml + questionHtml + answerHtml + "</tr>";
<table border="1" id="Questions">
I don't seem to be getting anything. Any suggestions, or more so, how do you query one list based upon another field tied to another list?
Sep 26, 2014 at 1:40 PM
Edited Sep 26, 2014 at 1:41 PM
Your query criteria is including a leading space (though unintended). There are a lot of leading spaces in the string you created before the actual value but still within the quotes. All but one of those spaces will be trimmed automatically. I recommend you trim them all out and you will likely get the response you're looking for.

Sep 26, 2014 at 1:51 PM
Edited Sep 26, 2014 at 2:39 PM
Thanks for the reponse.

I tried changing it to:
var Query = "<Query>" +
                    "<Where>" +
                    "<Eq>" +
                    "<FieldRef Name='Category_x0020_Name' />" +
                    "<Value Type='Text'>" +
                    "2;#Cat2" +
                    "</Value>" +
                    "</Eq>" +
                    "</Where>" +
                    " </Query>";
But unfortunately that does not appear to help. I've also tried Unicode for the special characters with no luck. Am I needing a cascaded drop down or anything like that? I'm a little lost. Thanks.
Sep 26, 2014 at 2:39 PM
I think I'm getting close with this:

Get lookup column value of sharepoint list item using jquery SPServices

I'll keep looking, but anymore help would be appreciated.
Sep 26, 2014 at 3:21 PM
Would "valuepairs" have anyhting to do with it? I seem to be having trouble finding documentation for SPServices' format.
Sep 28, 2014 at 8:53 AM
Here's an example where I am filtering all of the items in my States list to show only those where the Region Name is "Northeast". It should get you there.

The CAML syntax isn't an SPServices thing; it's a SharePoint thing.

var Query = "<Query>" +
    "<Where>" +
      "<Eq>" +
        "<FieldRef Name='Region_x0020_Name' />" +
        "<Value Type='Lookup'>" +
          "Northeast" +
        "</Value>" +
       "</Eq>" +
    "</Where>" +
  operation: "GetListItems",
  async: false,
  listName: "States",
  CAMLQuery: Query,
  completefunc: function(xData, Status) {}
Sep 29, 2014 at 1:34 PM
Hello Marc,

Many thanks for the response. You seem to be a master around here of how to get things working. At least I was searching through a lot of your answers and posts. I guess I'm more curious how you handle the ";#" in the lookup from the other table in the query. I'm a little familiar with how to form the queries for a standard list. The "2;#Cat2" is a lookup from one of the lookup table's responses to a query for the entry in a title of 'Cat2' and an 'Id' of 2. These are placed in the field that is selected for the lookup in the list I'm querying. Do you know how to do the query to lookup the full list's entries, looking at the field of the lookup entry?

Thank You