title: Getting SharePoint data from list that uses alternate list from a SPServices query
body: <p>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.</p>
  
  <p>If I do a query on the lookup table like this:</p>
  
  <pre><code>&lt;body&gt;
  &lt;script type="text/javascript" src="jquery-1.7.2.min.js"&gt;&lt;/script&gt;
  &lt;script type="text/javascript" src="jquery.SPServices-0.7.1a.js"&gt;&lt;/script&gt;
  
  var ViewFields = "&lt;ViewFields&gt;" +
                   "&lt;FieldRef Name='Title' /&gt;" +
                   "&lt;FieldRef Name='Column_x0020_Number' Type='Number' /&gt;" +
                   "&lt;/ViewFields&gt;";
  
  $(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 = "&lt;td&gt;" + $(this).attr("ows_Title") + "&lt;/td&gt;";
                  var columnNumHtml = "&lt;td&gt;" + Math.round($(this).attr("ows_Column_x0020_Number")) + "&lt;/td&gt;";
                  var addRow = "&lt;tr&gt;" + categoryHtml + columnHtml + "&lt;/tr&gt;";
                  $("#Categories").append(addRow);
              });
          }
      });
  });
  
  &lt;/script&gt;
  &lt;table border="1" id="Categories" /&gt;
  &lt;/body&gt;
  </code></pre>
  
  <p>I get returns like:</p>
  
  <p>Cat1    1
    Cat2    2
  ....</p>
  
  <p>So, It's working.  That's the table that gets the lookup from.</p>
  
  <p>Next we have the code to try and get data form the question table:</p>
  
  <pre><code>var ViewFields = "&lt;ViewFields&gt;" +
                   "&lt;FieldRef Name='Title' /&gt;" +
                   "&lt;FieldRef Name='Category_x0020_Name' /&gt;" +
                   "&lt;FieldRef Name='Amount' Type='Number' /&gt;" +
                   "&lt;FieldRef Name='Question' /&gt;" +
                   "&lt;FieldRef Name='Answer' /&gt;" +
                   "&lt;/ViewFields&gt;";
  
      $().SPServices({
          operation: "GetListItems",
          async: false,
          listName: "Categories",
          CAMLViewFields: ViewFields,
          completefunc: function (xData, Status) {
              $(xData.responseXML).SPFilterNode("z:row").each(function() {
                  var titleHtml = "&lt;td&gt;" + $(this).attr("ows_Title") + "&lt;/td&gt;";
                  var categoryNameHtml = "&lt;td&gt;" + $(this).attr("ows_Category_x0020_Name")) + "&lt;/td&gt;";
                  var nameHtmlSplitString = categoryNameHtml.split(";#");
                  var categoryNumString = "&lt;td&gt;" + nameHtmlSplitString[0] + "&lt;/td&gt;";
                  var categoryNameString = "&lt;td&gt;" + nameHtmlSplitString[1] + "&lt;/td&gt;";
                  var amount = "&lt;td&gt;" + Math.round($(this).attr("ows_Amount)) + "&lt;/td&gt;";
                  var question = "&lt;td&gt;" + $(this).attr("ows_Question) + "&lt;/td&gt;";
                  var categoryNumString = "&lt;td&gt;" + $(this).attr("ows_Answer) + "&lt;/td&gt;";
                  var addRow = "&lt;tr&gt;" + titleHtml + categoryNameHtml + categoryNameString + categoryNumString + amountHtml + questionHtml + answerHtml + "&lt;/tr&gt;";
                  $("#Questions").append(addRow);
              });
          }
      });
  });
  &lt;/script&gt;
  &lt;table border="1" id="Questions" /&gt;
  </code></pre>
  
  <p>That comes back fairly fine with:</p>
  
  <p>Cat1 Question 1|1;#Cat1|1|Cat1|100|(question 1.1)|(answer 1.1)</p>
  
  <p>Cat1 Question 2|1;#Cat1|1|Cat1|200|(question 1.2)|(answer 1.2)</p>
  
  <p>Cat1 Question 3|1;#Cat1|1|Cat1|300|(question 1.3)|(answer 1.3)</p>
  
  <p>Cat1 Question 4|1;#Cat1|1|Cat1|400|(question 1.4)|(answer 1.4)</p>
  
  <p>Cat1 Question 5|1;#Cat1|1|Cat1|500|(question 1.5)|(answer 1.5)</p>
  
  <p>Cat2 Question 1|2;#Cat2|2|Cat2|100|(question 2.1)|(answer 2.1)</p>
  
  <p>Cat2 Question 2|2;#Cat2|2|Cat2|200|(question 2.2)|(answer 2.2)</p>
  
  <p>Cat2 Question 3|2;#Cat2|2|Cat2|300|(question 2.3)|(answer 2.3)</p>
  
  <p>Cat2 Question 4|2;#Cat2|2|Cat2|400|(question 2.4)|(answer 2.4)</p>
  
  <p>Cat2 Question 5|2;#Cat2|2|Cat2|500|(question 2.5)|(answer 2.5)</p>
  
  <p>Cat3 Question 1|3;#Cat3|3|Cat3|100|(question 3.1)|(answer 3.1)</p>
  
  <p>Cat3 Question 2|3;#Cat3|3|Cat3|200|(question 3.2)|(answer 3.2)</p>
  
  <p>......</p>
  
  <p>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:</p>
  
  <pre><code>var Query = "&lt;Query&gt;" +
              "    &lt;Where&gt;" +
              "        &lt;Eq&gt;" +
              "            &lt;FieldRef Name='Category_x0020_Name' /&gt;" +
              "                &lt;Value Type='Text'&gt;" +
              "                    2;#Cat2" +
              "                &lt;/Value&gt;" +
              "        &lt;/Eq&gt;" +
              "    &lt;/Where&gt;" +
              "&lt;/Query&gt;";
  </code></pre>
  
  <p>and I apply it in the SPServices</p>
  
  <pre><code>        ...
          listName: "Categories",
          CAMLViewFields: ViewFields,
          CAMLQuery: Query,
          completefunc: function (xData, Status) {
          ....
  </code></pre>
  
  <p>I get nothing.</p>
  
  <p>Any suggestions how to handle the dropdown data?</p>