0

I have a small problem i am facing in CAML queries.
Currently i am trying to filter a Lookup column based on Current logged in User in SharePoint Online.
Here is my code and i am new to using SP services and CAML Queries.

<script type="text/javascript src="/sites/CC2344/SiteAssets/jquery.SPServices-0.6.2.min.js"></script>
<script type="text/javascript src="/sites/CC2344/SiteAssets/jquery.min.js"> 
 </script>

<script language="javascript" type="text/javascript">
 $(document).ready(function(){
var userName = $().SPServices.SPGetCurrentUser({ fieldName: "Name" });

  var query = '<Query>' +
            '<Where>' + 
                '<Eq>' +
                    '<FieldRef Name="Employee" />' +
                        '<Value Type="User">' + userName + '</Value>' +
                '</Eq>' +
            '</Where>' +
        '</Query>'; 

 $().SPServices({
operation: "GetListItems",
async: false,
listName: "TimeTracker",
CAMLViewFields: "<ViewFields><FieldRef Name='Title' /></ViewFields>",
CAMLQuery: query,
completefunc: function (xData, Status) {
  $(xData.responseXML).SPFilterNode("z:row").each(function() {
        // will return only results that match the current logged in user
        // in the 'SharePointUser' column
        console.log($(this).attr("ows_Title"));
  });

  });            
 });
</script>


Please do help me out , because i am really struggling with this.
Thank You.

2 Answers 2

0
<script language="javascript" type="text/javascript">
$(document).ready(function(){


var query = '<Query>' +
        '<Where>' + 
            '<Eq>' +
                '<FieldRef Name="Employee" />' +
                    '<Value Type="User"><UserID/></Value>' +
            '</Eq>' +
        '</Where>' +
    '</Query>'; 

$().SPServices({
operation: "GetListItems",
async: false,
listName: "TimeTracker",
CAMLViewFields: "<ViewFields><FieldRef Name='Title' /></ViewFields>",
CAMLQuery: query,
completefunc: function (xData, Status) {
$(xData.responseXML).SPFilterNode("z:row").each(function() {
    // will return only results that match the current logged in user
    // in the 'SharePointUser' column
    console.log($(this).attr("ows_Title"));
});
});            
});
</script>

Query regarding user you can refer this link

7
  • i cannot quite wrap my head around the query itself. could you possibly help me out on this one, so i can explain everything and see what works out ? Commented May 16, 2018 at 7:06
  • Its just a query for your SharePoint List "TimeTracker" where you are trying retrieve items where the filed/column "Employee" is equal to current logged in user. And adding to it the viewfields means you specify the field to return from list.
    – Sunil Sahu
    Commented May 16, 2018 at 8:20
  • What does the FieldRef Name='Title' defines here ? Commented May 16, 2018 at 8:24
  • It refer to field/column's name in list "TimeTracker" and in caml query we usually specify the internal name of the column.
    – Sunil Sahu
    Commented May 16, 2018 at 8:46
  • i am still not able to figure this out, guess i am really stuck Commented May 16, 2018 at 8:48
0

Filtering data against current userId is better.
Try this,

$(document).ready(function(){
    var userId = $().SPServices.SPGetCurrentUser({ fieldName: "ID" });

    var query = '<Query>' +
            '<Where>' + 
                '<Eq>' +
                    '<FieldRef Name="Employee" LookupId="TRUE"/>' +
                        '<Value Type="User">' + userId + '</Value>' +
                '</Eq>' +
            '</Where>' +
        '</Query>'; 

    $().SPServices({
        operation: "GetListItems",
        async: false,
        listName: "TimeTracker",
        CAMLViewFields: "<ViewFields><FieldRef Name='Title' /></ViewFields>",
        CAMLQuery: query,
        completefunc: function (xData, Status) {
            $(xData.responseXML).SPFilterNode("z:row").each(function() {
                // will return only results that match the current logged in user
                // in the 'SharePointUser' column
                console.log($(this).attr("ows_Title"));
            });
        }

    });            
 });

Assuming that "Employee" column is Person or Group type.

Edit
Since "Employee" is a Lookup column, change the CAML query like this,

var userDetails = $().SPServices.SPGetCurrentUser({
        fieldNames: ["ID", "UserName", "FirstName", "LastName"],
        debug: false
    });

    var userFullName = userDetails.FirstName + " " + userDetails.LastName;
    var query = '<Query>' +
            '<Where>' + 
                '<Eq>' +
                    '<FieldRef Name="Employee"/>' +
                        '<Value Type="Lookup">' + userFullName + '</Value>' +
                '</Eq>' +
            '</Where>' +
        '</Query>'; 

Assuming "Employee" column contains the FullName, if it contains UserName take current username & use it in the CAML query

8
  • Employee Column is a text field which is a look column. Commented May 16, 2018 at 8:20
  • you mean Employee column is a Lookup column type?
    – Vishnu S
    Commented May 16, 2018 at 8:59
  • yes, employee is a lookup column type! Commented May 16, 2018 at 9:32
  • Updated the answer, check it
    – Vishnu S
    Commented May 16, 2018 at 10:03
  • i updated the query , but unfortunately it is still not working. Does the code after this query needs to be changed too? Commented May 16, 2018 at 10:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.