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>',
2 Answers
Reset to default 1Since 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
发布者：admin，转转请注明出处：http://www.yc00.com/questions/1745133108a4613068.html
评论列表（0条）