Programmer puzzle solving

    最新消息：Welcome to the puzzle paradise for programmers! Here, a well-designed puzzle awaits you. From code logic puzzles to algorithmic challenges, each level is closely centered on the programmer's expertise and skills. Whether you're a novice programmer or an experienced tech guru, you'll find your own challenges on this site. In the process of solving puzzles, you can not only exercise your thinking skills, but also deepen your understanding and application of programming knowledge. Come to start this puzzle journey full of wisdom and challenges, with many programmers to compete with each other and show your programming wisdom! Translated with DeepL.com (free version)
    你的位置： HOME>matter>Insert a WHERE clause in a CAML query - using SharePoint, SPServices, JavaScript, and jQuery - Stack Overflow

    Insert a WHERE clause in a CAML query - using SharePoint, SPServices, JavaScript, and jQuery - Stack Overflow

    matteradmin6PV0评论

    I am trying to insert a WHERE clause into my CAML query to filter a date column for NULL (I want to return all rows for which there is no date in Assign Date the column).

    This is for a SharePoint List. The code is using the SPServices, jQuery, and JavaScript

    I am having quite a time pulling this off - I have very little experience doing this type of integration and less still in using CAML.

    This is the CAML query segment of the code:

    function loadPrioritizedList() {
    $("#tasksUL").empty();
    $().SPServices({
        operation: "GetListItems",    
        webURL: myURL,
        listName: targetListName,
        CAMLViewFields: "<ViewFields><FieldRef Name='Priority_x0020_Number' /><FieldRef Name='Edit_x0020_Link' /><FieldRef Name='Priority' /><FieldRef Name='Top_x0020_Item_x003f_' /><FieldRef Name='Purpose' /><FieldRef Name='Item_x002d_Task_x0020_Order' /><FieldRef Name='Mode' /><FieldRef Name='Work_x0020_Status' /><FieldRef Name='DueDate' /><FieldRef Name='Task_x0020_Type' /><FieldRef Name='DAK_x0020_Date' /><FieldRef Name='DAK_x0020_No' /><FieldRef Name='AssignedTo' /><FieldRef Name='Money_x0020_Estimate' /><FieldRef Name='ItemStatus' /><FieldRef Name='Assign_x0020_Date' /></ViewFields>",
        CAMLQuery: '<Query>' +
        '<OrderBy>' +
        '<FieldRef Name="Priority_x0020_Number" />' +
        '</OrderBy>' +
        '</Query>', 
        CAMLRowLimit: listrowlimit,  
        pletefunc: function (xData, Status) {
            $(xData.responseXML).SPFilterNode("z:row").each(function() {
                var tdHtml = "<tr class='sortable_row' id=" + $(this).attr("ows_ID") + ">";
                tdHtml = tdHtml + "<td style=\"width:60px;\">" + PriorityFormat($(this).attr("ows_Priority_x0020_Number"));  + "</td>";
                tdHtml = tdHtml + '<td style=\"width:49px;\"><a href=\"'+($(this).attr("ows_Edit_x0020_Link")).split(", ")[1] + '\">' + ($(this).attr("ows_Edit_x0020_Link")).split(", ")[1] + '</a></td>';
                tdHtml = tdHtml + "<td style=\"width:83px;\">" + $(this).attr("ows_Priority") + "</td>";
                tdHtml = tdHtml + "<td style=\"width:63px;\">" + TopItem($(this).attr("ows_Top_x0020_Item_x003f_")) + "</td>";
                tdHtml = tdHtml + "<td style=\"width:300px;\">" + StringChk($(this).attr("ows_Purpose")) + "</td>";
                tdHtml = tdHtml + "<td style=\"width:125px;\">" + StringChk($(this).attr("ows_Item_x002d_Task_x0020_Order")) + "</td>";                     
                tdHtml = tdHtml + "<td style=\"width:40px;\">" + StringChk($(this).attr("ows_Mode")) + "</td>";
                tdHtml = tdHtml + "<td style=\"width:75px;\">" + StringChk($(this).attr("ows_Task_x0020_Type")) + "</td>";
                tdHtml = tdHtml + "<td style=\"width:150px;\">" + StringChk($(this).attr("ows_Work_x0020_Status")) + "</td>";
                tdHtml = tdHtml + "<td style=\"width:100px;\">" + FormatDate($(this).attr("ows_DueDate")) + "</td>";
                tdHtml = tdHtml + "<td style=\"width:100px;\">" + FormatDate($(this).attr("ows_DAK_x0020_Date")) + "</td>";
                tdHtml = tdHtml + "<td style=\"width:100px;\">" + StringChk($(this).attr("ows_DAK_x0020_No")) + "</td>";
                tdHtml = tdHtml + "<td style=\"width:300px;\">" + StringChk($(this).attr("ows_AssignedTo")) + "</td>";
                tdHtml = tdHtml + "<td style=\"width:125px;\">" + $(this).attr("ows_Money_x0020_Estimate") + "</td>";
                tdHtml = tdHtml + "<td style=\"width:75px;\">" + StringChk($(this).attr("ows_ItemStatus")) + "</td>";
                tdHtml = tdHtml + "<td style=\"width:100px;\">" + FormatDate($(this).attr("ows_Assign_x0020_Date")) + "</td>";
                tdHtml = tdHtml + "</tr>";
                $("#tasksUL").append(tdHtml);
            });
        }
    });

    Any help would be gratefully weled, and I thank you in advance.

    UPDATE: The latest incarnation of the code is below. I have added the "WHERE" clause as: 

    "<Where><Eq><FieldRef Name=Assign_x0020_Date' /><Value Type='Date and Time'>IsNull</Value></Eq></Where>" +

    But it still returns no results. I'm not sure at this point what I'm missing here.

    The query with the above addition looks like this:

    function loadPrioritizedList() {
        $("#tasksUL").empty();
        $().SPServices({
            operation: "GetListItems",    
            webURL: myURL,
            listName: targetListName,
            CAMLViewFields: "<ViewFields><FieldRef Name='Priority_x0020_Number' /><FieldRef Name='Edit_x0020_Link' /><FieldRef Name='Priority' /><FieldRef Name='Top_x0020_Item_x003f_' /><FieldRef Name='Purpose' /><FieldRef Name='Item_x002d_Task_x0020_Order' /><FieldRef Name='Mode' /><FieldRef Name='Work_x0020_Status' /><FieldRef Name='DueDate' /><FieldRef Name='Task_x0020_Type' /><FieldRef Name='DAK_x0020_Date' /><FieldRef Name='DAK_x0020_No' /><FieldRef Name='AssignedTo' /><FieldRef Name='Money_x0020_Estimate' /><FieldRef Name='ItemStatus' /><FieldRef Name='Assign_x0020_Date' /></ViewFields>",
            CAMLQuery: '<Query>' +
    "<Where><Eq><FieldRef Name='Assign_x0020_Date' /><Value Type='Date and Time'>IsNull</Value></Eq></Where>" +
            '<OrderBy>' +
            '<FieldRef Name="Priority_x0020_Number" />' +
            '</OrderBy>' +
            '</Query>',

    I am trying to insert a WHERE clause into my CAML query to filter a date column for NULL (I want to return all rows for which there is no date in Assign Date the column).

    This is for a SharePoint List. The code is using the SPServices, jQuery, and JavaScript

    I am having quite a time pulling this off - I have very little experience doing this type of integration and less still in using CAML.

    This is the CAML query segment of the code:

    function loadPrioritizedList() {
    $("#tasksUL").empty();
    $().SPServices({
        operation: "GetListItems",    
        webURL: myURL,
        listName: targetListName,
        CAMLViewFields: "<ViewFields><FieldRef Name='Priority_x0020_Number' /><FieldRef Name='Edit_x0020_Link' /><FieldRef Name='Priority' /><FieldRef Name='Top_x0020_Item_x003f_' /><FieldRef Name='Purpose' /><FieldRef Name='Item_x002d_Task_x0020_Order' /><FieldRef Name='Mode' /><FieldRef Name='Work_x0020_Status' /><FieldRef Name='DueDate' /><FieldRef Name='Task_x0020_Type' /><FieldRef Name='DAK_x0020_Date' /><FieldRef Name='DAK_x0020_No' /><FieldRef Name='AssignedTo' /><FieldRef Name='Money_x0020_Estimate' /><FieldRef Name='ItemStatus' /><FieldRef Name='Assign_x0020_Date' /></ViewFields>",
        CAMLQuery: '<Query>' +
        '<OrderBy>' +
        '<FieldRef Name="Priority_x0020_Number" />' +
        '</OrderBy>' +
        '</Query>', 
        CAMLRowLimit: listrowlimit,  
        pletefunc: function (xData, Status) {
            $(xData.responseXML).SPFilterNode("z:row").each(function() {
                var tdHtml = "<tr class='sortable_row' id=" + $(this).attr("ows_ID") + ">";
                tdHtml = tdHtml + "<td style=\"width:60px;\">" + PriorityFormat($(this).attr("ows_Priority_x0020_Number"));  + "</td>";
                tdHtml = tdHtml + '<td style=\"width:49px;\"><a href=\"'+($(this).attr("ows_Edit_x0020_Link")).split(", ")[1] + '\">' + ($(this).attr("ows_Edit_x0020_Link")).split(", ")[1] + '</a></td>';
                tdHtml = tdHtml + "<td style=\"width:83px;\">" + $(this).attr("ows_Priority") + "</td>";
                tdHtml = tdHtml + "<td style=\"width:63px;\">" + TopItem($(this).attr("ows_Top_x0020_Item_x003f_")) + "</td>";
                tdHtml = tdHtml + "<td style=\"width:300px;\">" + StringChk($(this).attr("ows_Purpose")) + "</td>";
                tdHtml = tdHtml + "<td style=\"width:125px;\">" + StringChk($(this).attr("ows_Item_x002d_Task_x0020_Order")) + "</td>";                     
                tdHtml = tdHtml + "<td style=\"width:40px;\">" + StringChk($(this).attr("ows_Mode")) + "</td>";
                tdHtml = tdHtml + "<td style=\"width:75px;\">" + StringChk($(this).attr("ows_Task_x0020_Type")) + "</td>";
                tdHtml = tdHtml + "<td style=\"width:150px;\">" + StringChk($(this).attr("ows_Work_x0020_Status")) + "</td>";
                tdHtml = tdHtml + "<td style=\"width:100px;\">" + FormatDate($(this).attr("ows_DueDate")) + "</td>";
                tdHtml = tdHtml + "<td style=\"width:100px;\">" + FormatDate($(this).attr("ows_DAK_x0020_Date")) + "</td>";
                tdHtml = tdHtml + "<td style=\"width:100px;\">" + StringChk($(this).attr("ows_DAK_x0020_No")) + "</td>";
                tdHtml = tdHtml + "<td style=\"width:300px;\">" + StringChk($(this).attr("ows_AssignedTo")) + "</td>";
                tdHtml = tdHtml + "<td style=\"width:125px;\">" + $(this).attr("ows_Money_x0020_Estimate") + "</td>";
                tdHtml = tdHtml + "<td style=\"width:75px;\">" + StringChk($(this).attr("ows_ItemStatus")) + "</td>";
                tdHtml = tdHtml + "<td style=\"width:100px;\">" + FormatDate($(this).attr("ows_Assign_x0020_Date")) + "</td>";
                tdHtml = tdHtml + "</tr>";
                $("#tasksUL").append(tdHtml);
            });
        }
    });

    Any help would be gratefully weled, and I thank you in advance.

    UPDATE: The latest incarnation of the code is below. I have added the "WHERE" clause as: 

    "<Where><Eq><FieldRef Name=Assign_x0020_Date' /><Value Type='Date and Time'>IsNull</Value></Eq></Where>" +

    But it still returns no results. I'm not sure at this point what I'm missing here.

    The query with the above addition looks like this:

    function loadPrioritizedList() {
        $("#tasksUL").empty();
        $().SPServices({
            operation: "GetListItems",    
            webURL: myURL,
            listName: targetListName,
            CAMLViewFields: "<ViewFields><FieldRef Name='Priority_x0020_Number' /><FieldRef Name='Edit_x0020_Link' /><FieldRef Name='Priority' /><FieldRef Name='Top_x0020_Item_x003f_' /><FieldRef Name='Purpose' /><FieldRef Name='Item_x002d_Task_x0020_Order' /><FieldRef Name='Mode' /><FieldRef Name='Work_x0020_Status' /><FieldRef Name='DueDate' /><FieldRef Name='Task_x0020_Type' /><FieldRef Name='DAK_x0020_Date' /><FieldRef Name='DAK_x0020_No' /><FieldRef Name='AssignedTo' /><FieldRef Name='Money_x0020_Estimate' /><FieldRef Name='ItemStatus' /><FieldRef Name='Assign_x0020_Date' /></ViewFields>",
            CAMLQuery: '<Query>' +
    "<Where><Eq><FieldRef Name='Assign_x0020_Date' /><Value Type='Date and Time'>IsNull</Value></Eq></Where>" +
            '<OrderBy>' +
            '<FieldRef Name="Priority_x0020_Number" />' +
            '</OrderBy>' +
            '</Query>',
    • javascript
    • jquery
    • sharepoint
    • caml
    • spservices
    Share Improve this question edited Jun 27, 2013 at 19:52 WCS asked Jun 26, 2013 at 16:00 WCSWCS 931 gold badge3 silver badges17 bronze badges
    Add a ment  | 

    2 Answers 2

    Reset to default 1

    Since I use my framework I don't really care about the CAML, but it should be something like :

    <query>
  <Query>
    <Where>
      <IsNull><FieldRef Name="Assign_x0020_Date"></FieldRef></IsNull>
    </Where>
  </Query>
</query>

    BTW you may want to try my framework : http://aymkdn.github.io/SharepointPlus/ The syntax will be (for the same thing) :

    function loadPrioritizedList() {
  $("#tasksUL").empty();
  $SP().list(targetListName).get({
    fields:"Priority_x0020_Number,Edit_x0020_Link,Priority,Top_x0020_Item_x003f_,Purpose,Item_x002d_Task_x0020_Order,Mode,Work_x0020_Status,DueDate,Task_x0020_Type,DAK_x0020_Date,DAK_x0020_No,AssignedTo,Money_x0020_Estimate,ItemStatus,Assign_x0020_Date",
    where:"Assign_x0020_Date = ''",
    orderby:"Priority_x0020_Number DESC",
    rowlimit:listrowlimit
  }, function (xData) {
    var tdHTML="";
    for (var i=xData.length; i--;) {
      tdHtml += "<tr class='sortable_row' id=" + xData[i].getAttribute("ID") + ">";
      tdHtml += "<td style=\"width:60px;\">" + PriorityFormat(xData[i].getAttribute("Priority_x0020_Number"));  + "</td>";
      tdHtml += '<td style=\"width:49px;\"><a href=\"'+(xData[i].getAttribute("Edit_x0020_Link")).split(", ")[1] + '\">' + (xData[i].getAttribute("Edit_x0020_Link")).split(", ")[1] + '</a></td>';
      tdHtml += "<td style=\"width:83px;\">" + xData[i].getAttribute("Priority") + "</td>";
      tdHtml += "<td style=\"width:63px;\">" + TopItem(xData[i].getAttribute("Top_x0020_Item_x003f_")) + "</td>";
      tdHtml += "<td style=\"width:300px;\">" + StringChk(xData[i].getAttribute("Purpose")) + "</td>";
      tdHtml += "<td style=\"width:125px;\">" + StringChk(xData[i].getAttribute("Item_x002d_Task_x0020_Order")) + "</td>";                     
      tdHtml += "<td style=\"width:40px;\">" + StringChk(xData[i].getAttribute("Mode")) + "</td>";
      tdHtml += "<td style=\"width:75px;\">" + StringChk(xData[i].getAttribute("Task_x0020_Type")) + "</td>";
      tdHtml += "<td style=\"width:150px;\">" + StringChk(xData[i].getAttribute("Work_x0020_Status")) + "</td>";
      tdHtml += "<td style=\"width:100px;\">" + FormatDate(xData[i].getAttribute("DueDate")) + "</td>";
      tdHtml += "<td style=\"width:100px;\">" + FormatDate(xData[i].getAttribute("DAK_x0020_Date")) + "</td>";
      tdHtml += "<td style=\"width:100px;\">" + StringChk(xData[i].getAttribute("DAK_x0020_No")) + "</td>";
      tdHtml += "<td style=\"width:300px;\">" + StringChk(xData[i].getAttribute("AssignedTo")) + "</td>";
      tdHtml += "<td style=\"width:125px;\">" + xData[i].getAttribute("Money_x0020_Estimate") + "</td>";
      tdHtml += "<td style=\"width:75px;\">" + StringChk(xData[i].getAttribute("ItemStatus")) + "</td>";
      tdHtml += "<td style=\"width:100px;\">" + FormatDate(xData[i].getAttribute("Assign_x0020_Date")) + "</td>";
      tdHtml += "</tr>";
    }
    $("#tasksUL").append(tdHtml);
  }
});

    Ah, the answer was in the way the IsNull has to be used. I modified the code:

    "<Where><Eq><FieldRef Name=Assign_x0020_Date' /><Value Type='Date and Time'>IsNull</Value></Eq></Where>" +

    To:

    "<Where><IsNull><FieldRef Name=Assign_x0020_Date' /></IsNull></Where>" +

    Thanks to this SO post: https://stackoverflow./a/1341301/2190871

    continue to browse articles about articles of

    Articles related to this article

    Post a comment

    comment list (0)

    1. No comments so far