Get list item count for a week

Feb 24, 2014 at 1:34 PM
Edited Feb 24, 2014 at 1:55 PM
Hi All

I am new to coding and as such may need lots of help.

The end goal is for the code to count the number of items in a list but for a week (Monday to Sunday) and then reset itself on the Monday morning. So as an example its Monday today and there are 10 items in the list so the code counts 10 items, Tuesday 10 more are added so it counts 20 and so on until Sunday when it counts the total added since Monday. But as soon as next Monday starts it resets back to 0 and ignores all the items added previously.

I have gleaned from here how to count lists items and show it in a dialogue box but I want the code to show the items count for the Monday to Sunday in a content editor webpart.

This is all happening on a MOSS 2007 box.

Like I said, very newbie code wise so any help would be good.

Thanks

Alan
Coordinator
Feb 24, 2014 at 2:16 PM
Alan:

Great to hear from you.

You'd need to generate a CAML query what gave the week's items. It would look something like this:
<Query>
  <Where>
    <And>
         <Geq><FieldRef Name='StartDate'/><Value Type='Date'>" + fromDate + "</Value></Geq>" +
         <Leq><FieldRef Name='EndDate'/><Value Type='Date'>" + toDate + "</Value></Leq>" +
    </And>
  </Where>
</Query>
Then you could call GetListItems to retrieve the items and the count. GetListItems actually returns the count of items, so that part is pretty easy.

So what do you have so far?

M.
Feb 24, 2014 at 3:06 PM
Hi Marc

Currently I have this code:
<script type="text/javascript" src="/scripts/jquery-1.11.0.min.js"></script>
<script type="text/javascript" src="/scripts/jquery.SPServices-2014.01.min.js"></script>
<script language="javascript" type="text/javascript">

 $().SPServices({
  operation: "GetListItems",
  listName: "Opportunities",
  async: false,
  completefunc: function (xData, Status) {
    $(xData.responseXML).find("rs\\:data").each(function() {
     itemCount = $(this).attr("ItemCount");
    });
  }
 });
 alert(itemCount);
</script>
Which works, in that it shows an alert with a count of the current number of list items.

What I need is for it to write it to the CEWP that the script is in and only show the count of items for the Monday to Sunday.

Thanks

Alan
Coordinator
Feb 24, 2014 at 3:46 PM
Edited Feb 24, 2014 at 3:47 PM
Ok, so you probably need to figure out the date range in your script as well. moment.js is a great library to use for stuff like this.

Once you have the data, you can write it into the page with jQuery using something like this:

Markup:
<div id="myCount"/>
Script:
$("#myCount").html(itemCount);
M.
Feb 25, 2014 at 1:33 AM
Another approach to using "hard" dates is to take advantage of the <Today> element and it's OffsetDays attribute... Here is a sample of code that uses the <Today> CAML element to get all items after a certain date based on an offset from "today" to the beginning of the week:

(Full disclosure: I have not tested this specific example, but have used similar one solution I have developed)
var daysFromSunday = (new Date()).getDay(),
    dateOffset = (
            daysFromSunday > 0
        ?   " OffsetDays='-" + daysFromSunday + "'"
        :   ""
    ),
    query;

var query = "<Query>" +
        "<Where>" +
            "<Gt>" +
                "<FieldRef Name='CreateDate' />" +
                "<Value Type='DateTime'>" +
                    "<Today" + dateOffset + "/>" +
                "</Value>" +
            "</Gt>" + 
        "</Where>" +
    "</Query>";
The code above, if used today - Monday - would generate a <Today> element like the following:
<Today OffsetDays='-1'/>
/Paul.
Coordinator
Feb 25, 2014 at 4:35 PM
Great approach, Paul! I hadn't reasoned that far.

This ought to be very close:
$(document).ready() {

    var itemCount;
    var daysFromSunday = (new Date()).getDay(),
        dateOffset = (
            daysFromSunday > 0 ? " OffsetDays='-" + daysFromSunday + "'" : ""
        ),
        query;
    var daysToSunday = 7 - daysFromSunday;

    var query = "<Query>" +
        "<Where>" +
        "<And>" +
        "<Geq>" +
        "<FieldRef Name='CreateDate' />" +
        "<Value Type='DateTime'>" +
        "<Today OffsetDays='-" + daysFromSunday + "'/>" +
        "</Value>" +
        "</Geq>" +
        "<Leq>" +
        "<FieldRef Name='CreateDate' />" +
        "<Value Type='DateTime'>" +
        "<Today OffsetDays='" + daysToSunday + "'/>" +
        "</Value>" +
        "</Leq>" +
        "</And>" +
        "</Where>" +
        "</Query>";

    $().SPServices({
        operation: "GetListItems",
        listName: "Opportunities",
        async: false,
        completefunc: function(xData, Status) {
            itemCount = $(xData.responseXML).SPFilterNode("rs:data").attr("ItemCount");
        }
    });
    alert(itemCount);
});
M.
Feb 26, 2014 at 11:46 AM
Edited Feb 26, 2014 at 11:51 AM
Hi Thanks for this.

I have tried putting the code in but it returns nothing.

The list has a field called CreateDate which is just a date field set to default value of today.

From Marc's original example I created this script which has an animated button on it whoch launches a newform.aspx and then the code for counting the total number of items and placing it in a table.
<html>
<table>
<tr>
<td>

<a href="/lists/opportunities/newform.aspx?source=/" onMouseOver="return changeImage()" onMouseOut= "return changeImageBack()" onMouseDown="return handleMDown()"
onMouseUp="return handleMUp()"><img name="jsbutton" src="/AnimFrog/AnimInitial.jpg" width="110" height="260" border="0"
alt="javascript button"></a>
 
<script language="JavaScript">

upImage = new Image();
upImage.src = "/AnimFrog/AnimHover.jpg";
downImage = new Image();
downImage.src = "/AnimFrog/AnimClick.jpg"
normalImage = new Image();
normalImage.src = "/AnimFrog/AnimInitial.jpg";
 
function changeImage()
{
  document.images["jsbutton"].src= upImage.src;
  return true;
}
function changeImageBack()
{
   document.images["jsbutton"].src = normalImage.src;
   return true;
}
function handleMDown()
{
 document.images["jsbutton"].src = downImage.src;
 return true;
}
function handleMUp()
{
 changeImage();
 return true;
}
</script>
</td>

<td>
Total number of items:<span id="frogItemCount"></span>
</td>

<td>
<script type="text/javascript" src="/scripts/jquery-1.11.0.min.js"></script>
<script type="text/javascript" src="/scripts/jquery.SPServices-2014.01.min.js"></script>
<script language="JavaScript">
$().SPServices({
  operation: "GetListItems",
  listName: "Opportunities",
  async: false,
  completefunc: function (xData, Status) {
    $(xData.responseXML).find("rs\\:data").each(function() {
     itemCount = $(this).attr("ItemCount");
    });
  }
 });
 $("#frogItemCount").html(itemCount); 
</script>
</td>
</tr>
</table>
So then I tried to alter the code with the piece above about getting the date but it doesn't return any data at all. Am I missing something obvious. The MOSS site is set to UK regional settings.
<html>
<table>
<tr>
<td>

<a href="/lists/opportunities/newform.aspx?source=/" onMouseOver="return changeImage()" onMouseOut= "return changeImageBack()" onMouseDown="return handleMDown()"
onMouseUp="return handleMUp()"><img name="jsbutton" src="/AnimFrog/AnimInitial.jpg" width="110" height="260" border="0"
alt="javascript button"></a>
 
<script language="JavaScript">

upImage = new Image();
upImage.src = "/AnimFrog/AnimHover.jpg";
downImage = new Image();
downImage.src = "/AnimFrog/AnimClick.jpg"
normalImage = new Image();
normalImage.src = "/AnimFrog/AnimInitial.jpg";
 
function changeImage()
{
  document.images["jsbutton"].src= upImage.src;
  return true;
}
function changeImageBack()
{
   document.images["jsbutton"].src = normalImage.src;
   return true;
}
function handleMDown()
{
 document.images["jsbutton"].src = downImage.src;
 return true;
}
function handleMUp()
{
 changeImage();
 return true;
}
</script>
</td>

<td>
Total number of items:<span id="frogItemCount"></span>
</td>

<td>
<script type="text/javascript" src="/scripts/jquery-1.11.0.min.js"></script>
<script type="text/javascript" src="/scripts/jquery.SPServices-2014.01.min.js"></script>
<script language="JavaScript">
{   var itemCount;
    var daysFromSunday = (new Date()).getDay(),
        dateOffset = (
            daysFromSunday > 0 ? " OffsetDays='-" + daysFromSunday + "'" : ""
        ),
        query;
    var daysToSunday = 7 - daysFromSunday;

    var query = "<Query>" +
        "<Where>" +
        "<And>" +
        "<Geq>" +
        "<FieldRef Name='CreateDate' />" +
        "<Value Type='DateTime'>" +
        "<Today OffsetDays='-" + daysFromSunday + "'/>" +
        "</Value>" +
        "</Geq>" +
        "<Leq>" +
        "<FieldRef Name='CreateDate' />" +
        "<Value Type='DateTime'>" +
        "<Today OffsetDays='" + daysToSunday + "'/>" +
        "</Value>" +
        "</Leq>" +
        "</And>" +
        "</Where>" +
        "</Query>";

    $().SPServices({
        operation: "GetListItems",
        listName: "Opportunities",
        async: false,
        completefunc: function(xData, Status) {
            itemCount = $(xData.responseXML).SPFilterNode("rs:data").attr("ItemCount");
        }
    });
});
 $("#frogItemCount").html(itemCount); 
</script>
</td>
</tr>
</table>
Coordinator
Feb 26, 2014 at 2:31 PM
Alan:

I think the issue is that you're poking the value into the frogItemCount before you've got it. Change the SPServices call to this:
    $().SPServices({
        operation: "GetListItems",
        listName: "Opportunities",
        async: false,
        completefunc: function(xData, Status) {
            itemCount = $(xData.responseXML).SPFilterNode("rs:data").attr("ItemCount");
            $("#frogItemCount").html(itemCount); 
        }
    });
});
M.
Feb 26, 2014 at 3:20 PM
Hi Marc

Thanks for this, problem is it still didn't return a value.

I had a look at moment.js and have worked out how to get it to store a value for the date of the Sunday of the current week. Was wondering if you had any idea how I could then tell SPServices to only count items added after that date as the variable will change every Monday.
Coordinator
Feb 26, 2014 at 4:22 PM
When you say "it still didn't return a value", what are you seeing? Did you try alerting itemCount?

M.
Feb 26, 2014 at 5:55 PM
Hi Marc

Tried this code with an alert, but the alert doesn't even appear.
<html>
<table>
<tr>
<td>

<a href="/lists/opportunities1/newform.aspx?source=/" onMouseOver="return changeImage()" onMouseOut= "return changeImageBack()" onMouseDown="return handleMDown()"
onMouseUp="return handleMUp()"><img name="jsbutton" src="/AnimFrog/AnimInitial.jpg" width="110" height="260" border="0"
alt="javascript button"></a>
 
<script language="JavaScript">

upImage = new Image();
upImage.src = "/AnimFrog/AnimHover.jpg";
downImage = new Image();
downImage.src = "/AnimFrog/AnimClick.jpg"
normalImage = new Image();
normalImage.src = "/AnimFrog/AnimInitial.jpg";
 
function changeImage()
{
  document.images["jsbutton"].src= upImage.src;
  return true;
}
function changeImageBack()
{
   document.images["jsbutton"].src = normalImage.src;
   return true;
}
function handleMDown()
{
 document.images["jsbutton"].src = downImage.src;
 return true;
}
function handleMUp()
{
 changeImage();
 return true;
}
</script>
</td>

<td>
Total number of items:<span id="frogItemCount"></span>
</td>

<td>
<script type="text/javascript" src="/scripts/jquery-1.11.0.min.js"></script>
<script type="text/javascript" src="/scripts/jquery.SPServices-2014.01.min.js"></script>
<script language="JavaScript">
{   var itemCount;
    var daysFromSunday = (new Date()).getDay(),
        dateOffset = (
            daysFromSunday > 0 ? " OffsetDays='-" + daysFromSunday + "'" : ""
        ),
        query;
    var daysToSunday = 7 - daysFromSunday;

    var query = "<Query>" +
        "<Where>" +
        "<And>" +
        "<Geq>" +
        "<FieldRef Name='CreateDate' />" +
        "<Value Type='DateTime'>" +
        "<Today OffsetDays='-" + daysFromSunday + "'/>" +
        "</Value>" +
        "</Geq>" +
        "<Leq>" +
        "<FieldRef Name='CreateDate' />" +
        "<Value Type='DateTime'>" +
        "<Today OffsetDays='" + daysToSunday + "'/>" +
        "</Value>" +
        "</Leq>" +
        "</And>" +
        "</Where>" +
        "</Query>";

    $().SPServices({
        operation: "GetListItems",
        listName: "Opportunities",
        async: false,
        completefunc: function(xData, Status) {
            itemCount = $(xData.responseXML).SPFilterNode("rs:data").attr("ItemCount");
        }
    });
    alert(itemCount);
});
</script>
</td>
</tr>
</table>
I am wondering if it would be easier to use the moment.js to pass the Sunday date variable to SPServices and then tell it to count items created after that date taken from the createDate field in the list, which is just a date field
Coordinator
Feb 26, 2014 at 6:02 PM
My guess is that you're getting an error somewhere. You also don't have the code wrapped in$(document).ready(), so it may be running before the elements are in the DOM. (Sorry I missed that before.)

M.
Feb 26, 2014 at 6:12 PM
You're missing something in the code above, right around here:
<script language="JavaScript">
{   var itemCount;
What's this extra '{'? My assumption, given that the code block ends with '});' is that this should really be:
<script language="JavaScript">
$(function() {   var itemCount;
If that does not work, then you need to put in a few alert()'s along the way to test for expected flow of the piece of code.
Also,
Sorry to ask the obvious: you do have item in the list whose "CreateDate' is within the last 4 days, correct?

/Paul.
Feb 26, 2014 at 6:37 PM
Here is my altered code after the comments:
<script type="text/javascript" src="/scripts/jquery-1.11.0.min.js"></script>
<script type="text/javascript" src="/scripts/jquery.SPServices-2014.01.min.js"></script>
<script language="JavaScript">
$(document).ready()
var itemCount;
  var daysFromSunday = (new Date()).getDay(),
    dateOffset = (
            daysFromSunday > 0 ? " OffsetDays='-" + daysFromSunday + "'" : ""
        ),
        query;
    var daysToSunday = 7 - daysFromSunday;
alert(daysFromSunday);
alert(daysToSunday);
    var query = "<Query>" +
        "<Where>" +
        "<And>" +
        "<Geq>" +
        "<FieldRef Name='CreateDate' />" +
        "<Value Type='DateTime'>" +
        "<Today OffsetDays='-" + daysFromSunday + "'/>" +
       "</Value>" +
        "</Geq>" +
        "<Leq>" +
        "<FieldRef Name='CreateDate' />" +
        "<Value Type='DateTime'>" +
        "<Today OffsetDays='" + daysToSunday + "'/>" +
        "</Value>" +
        "</Leq>" +
        "</And>" +
        "</Where>" +
        "</Query>";
alert(query);

    $().SPServices({
        operation: "GetListItems",
        listName: "Opportunities",
        async: false,
        completefunc: function(xData, Status) {
            itemCount = $(xData.responseXML).SPFilterNode("rs:data").attr("ItemCount");
        }
    });
 alert(itemCount);
</script>
I put the alerts in and get sensible returns for days to and from sunday but on the alert for query it returns the whole code not a value. The last alert then returns the total amount of items in the list.

The list contains about 3 items that have been created since Sunday with a CreateDate field of 26/02/2014
Coordinator
Feb 26, 2014 at 6:43 PM
query is just a string which contains the CAML we're using to tell GetListItems what to fetch for us, so what you see there is right.

In the SPServices call, you're not passing in the CAMLQuery:
    $().SPServices({
        operation: "GetListItems",
        listName: "Opportunities",
        CAMLQuery: query,
        async: false,
        completefunc: function(xData, Status) {
            itemCount = $(xData.responseXML).SPFilterNode("rs:data").attr("ItemCount");
        }
    });
Feb 26, 2014 at 6:57 PM
Perfect, how did I not notice that. Works perfectly now and here is the final code.
<html>
<table>
<tr>
<td>

<a href="/lists/opportunities/newform.aspx?source=/" onMouseOver="return changeImage()" onMouseOut= "return changeImageBack()" onMouseDown="return handleMDown()"
onMouseUp="return handleMUp()"><img name="jsbutton" src="/AnimFrog/AnimInitial.jpg" width="110" height="260" border="0"
alt="javascript button"></a>
 
<script language="JavaScript">

upImage = new Image();
upImage.src = "/AnimFrog/AnimHover.jpg";
downImage = new Image();
downImage.src = "/AnimFrog/AnimClick.jpg"
normalImage = new Image();
normalImage.src = "/AnimFrog/AnimInitial.jpg";
 
function changeImage()
{
  document.images["jsbutton"].src= upImage.src;
  return true;
}
function changeImageBack()
{
   document.images["jsbutton"].src = normalImage.src;
   return true;
}
function handleMDown()
{
 document.images["jsbutton"].src = downImage.src;
 return true;
}
function handleMUp()
{
 changeImage();
 return true;
}
</script>
</td>

<td>
<center><b>Opportunities for this week:<p><span id="frogItemCount"></span></center></b>
<script type="text/javascript" src="/scripts/jquery-1.11.0.min.js"></script>
<script type="text/javascript" src="/scripts/jquery.SPServices-2014.01.min.js"></script>
<script language="JavaScript">
$(document).ready()
var itemCount;
  var daysFromSunday = (new Date()).getDay(),
    dateOffset = (
            daysFromSunday > 0 ? " OffsetDays='-" + daysFromSunday + "'" : ""
        ),
        query;
    var daysToSunday = 7 - daysFromSunday;
    var query = "<Query>" +
        "<Where>" +
        "<And>" +
        "<Geq>" +
        "<FieldRef Name='CreateDate' />" +
        "<Value Type='DateTime'>" +
        "<Today OffsetDays='-" + daysFromSunday + "'/>" +
       "</Value>" +
        "</Geq>" +
        "<Leq>" +
        "<FieldRef Name='CreateDate' />" +
        "<Value Type='DateTime'>" +
        "<Today OffsetDays='" + daysToSunday + "'/>" +
        "</Value>" +
        "</Leq>" +
        "</And>" +
        "</Where>" +
        "</Query>";

    $().SPServices({
        operation: "GetListItems",
        listName: "Opportunities",
        CAMLQuery: query,
        async: false,
        completefunc: function(xData, Status) {
            itemCount = $(xData.responseXML).SPFilterNode("rs:data").attr("ItemCount");
        }
    });
 $("#frogItemCount").html(itemCount);
</script>
</td>
</tr>
</table>
Big thank you for everyone's help it is much appreciated.

If you have no objection I would like to blog my experience of creating this, including of course references to this site, discussion and of course the fact that I wouldn't have got to here without your help.
Coordinator
Feb 26, 2014 at 7:01 PM
Yay! I'm really glad you got it going. Hopefully now you're an SPServices fan.

Please blog about it, warts and all. Maybe it's an SPServices Story?
http://sympmarc.com/series/spservices-stories/

M.
Feb 26, 2014 at 7:03 PM
Thanks Marc.

Shame I cant make SPC2014 I would have bought you a few drinks for this

And yes will be sitting down and learning all about SPServices from today.