How to fix ' Filter Lookup by Current User using CAML

Question

I am filtering a lookup column on SharePoint Online but I Keep on getting error if I inspect on the browser and the filtering is not working.

I have used jquery.SPServices.SPGetCurrentUser to get the Logged in user details. Then the CAML Query to filter and get the List Activity Plan.

    <script src="//code.jquery.com/jquery-1.11.1.min.js"></script>
    <script 
src="//cdnjs.cloudflare.com/ajax/libs/jquery.SPServices/2014.02/jquery.SPServices-2014.02.min.js"></script>

    <script type="text/javascript">
    let custom = {}
    custom.myQuery = jQuery;
    console.log(custom)
    console.log($().SPServices.SPGetCurrentUser({
      fieldNames: ["ID", "Name", "Title", "Email","DepartMent", "JobTitle", 
      "FirstName", "LastName", "UserName", "SIP Address"],
      debug: false
    }));
    var userId = $().SPServices.SPGetCurrentUser({
      fieldName: "ID",
      debug: false
    })

    _spBodyOnLoadFunctionNames.push("loadSP")

    function loadSP() {
      var query = `<Query><Where><And><Eq><FieldRef Name='AssignedTo' 
      LookupId='TRUE'/><Value Type='Integer'>${userId}</Value></Eq><Eq> 
      <FieldRef Name='Status' LookupId='TRUE'/><Value 
      Type='Text'>Approved</Value></Eq></And></Where></Query>`;
      custom.myQuery().SPServices({
          operation: "GetListItems",
          async: false,
          listName: "Activity Plan",
          CAMLQuery: query,
          //CAMLQueryOptions: "<QueryOptions><ViewAttributes 
          Scope='RecursiveAll' IncludeRootFolder='True' /></QueryOptions>",
          CAMLViewFields: `<ViewFields>   
          <FieldRef Name='Deliverable' />
          <FieldRef Name='Task Status' />
          <FieldRef Name='ID' />
          </ViewFields>`,
          completefunc: function (xData, Status) {
            var newSites = "(none)";
            var ID = "0";
            console.log(xData)
            $(xData.responseXML).find("z\\:row, row").each(function () {

               var DeliverableLookup = $(this).attr("Deliverable");
               var owId = $(this).attr("ID");

               if (DeliverableLookup != '') {
                  var newValue = DeliverableLookup;
                  if (newSites.indexOf(newValue) == -1) {

                     newSites = newSites + "|" + newValue;
                     ID += '| ' + owId;
                   }
                }
             });

             updateValues(newSites, ID);
          }
        });
    }

    function getField(fieldType,fieldTitle) {
       var docTags = document.getElementsByTagName(fieldType);
       for (var i=0; i < docTags.length; i++) {
          if (docTags[i].title == fieldTitle) {
              return docTags[i];
          }
       }
       return false;
    }

    function updateValues(newSites, ID) {
       lookupFieldText = getField('select','My Planned Tasks');
       //lookupFieldId = document.getElementById(lookupFieldText.id);
       $(lookupFieldText).empty();

       let arrSites = newSites.split('|');
       let arrId = ID.split('|');
       for (let i = 0; i < arrSites.length; i++){
         $(lookupFieldText).append(`<option id='${arrId[i]}' 
         value='${arrSites[i]}'> ${arrSites[i]} </option>`)
       }
       //lookupFieldText.choices = newSites;
    }

    function CustomAction(){
      console.log("done loading")
    }
</script>

I expect the lookup column My Planned Tasks to show only the Deliverables that I created and with status Approved, but instead it is returning all tasks.

Answer 1

Check your console logged data and see whats returning, if nothing is returning, check if your status is true and if not then your query is wrong.. Check for spaces in the query.