Help with GetListItems and removing duplication

Oct 3, 2012 at 9:07 AM

Hi all,

I've been tasked with using SPServices to build an attendance management system in Sharepoint, but need some help, as I am struggling over one critical issue - it's a bit complicated, so please bear with me:

I'm using an infopath form, that submits the name of the user, their ID number, the start date and end date, as well as the relevant code for the type of absence (sickness, holiday, etc). This is submitted OK into a form library, then workflowed into a standard SP list.

Once on the site, I'm using SPServices to pull out the contents from a view showing entries for the last 7 days within the list, and displaying it as a table on a page in SP. The code should either insert the relevant absence code recorded from the form entry in the SP list, or show a / if no absence was recorded for the particular day in that 7 day period. The code I have so far does this OK:

<script type="text/javascript"> 
 var myReport = new Array();
 var attendance = new Array();
 var reportdate = Date.parse("14 July");
 var data = "";
 var method = "GetListItems";
 var list = "Absence Log";
 var view = "{FCD7A14F-2006-4D94-AA56-FC4AAA27548A}";
 var employeename = "";	
 var absencereason = "";
 var fieldsToRead =  "<ViewFields>" +
                     "<FieldRef Name='Employee_x0020_Name' />" +
                     "<FieldRef Name='Start_x0020_Date' />" +
                     "<FieldRef Name='End_x0020_Date' />" +
                     "<FieldRef Name='Absence_x0020_Reason' />" +
                     "<FieldRef Name='Sickness_x0020_Reason' />" +
 // create arrays
 for (var i = 0; i < 7; i++) {
   myReport[i] = reportdate.addDays(1).toString("dd/MM/yy");
   attendance[i] = "/";
$(document).ready(function() {
    operation: method,
    async: false,
    listName: list,
    viewName: view,
    CAMLViewFields: fieldsToRead,
    completefunc: function (xData, Status) {
      $(xData.responseXML).SPFilterNode("z:row").each(function() {  
        employeename = $(this).attr("ows_Employee_x0020_Name");
        startdate = Date.parse($(this).attr("ows_Start_x0020_Date")).toString("dd/MM/yy");
        enddate = Date.parse($(this).attr("ows_End_x0020_Date")).toString("dd/MM/yy");

        if (employeename == "John Smith") {
          absencereason = $(this).attr("ows_Absence_x0020_Reason");
          for (var x = 0; x < 7; x++) {
            if ((myReport[x] >= startdate) && (myReport[x] <= enddate)) {
              attendance[x] = absencereason;

        data = data +
          "<tr>" + 
          "<td>" + employeename + "</td>" + 
          "<td>" + attendance[0] + "</td>" +
          "<td>" + attendance[1] + "</td>" +
          "<td>" + attendance[2] + "</td>" +
          "<td>" + attendance[3] + "</td>" +
          "<td>" + attendance[4] + "</td>" +
          "<td>" + attendance[5] + "</td>" +
          "<td>" + attendance[6] + "</td>" +
       $("#tasksUL").append("<table>" + data + "</table>");
<ul id="tasksUL"/>

(I've stripped out formatting CSS, and unnecessary fields from the original version to simplify it).

The code above produces the following:

John Smith / R R R R / /
John Smith / R R R R CS / should show this:

John Smith / R R R R / /
John Smith / / / / / CS /

The issue I have is two-fold:

 1. My original code assigned the absence type to specific variables, in the format of:

var attendance1 = absencereason,
var attendance2 = absencereason,

...and so on. I've tried to rationalise it by using arrays instead, but I am not sure I've gotten it right; based on the above code, what am I doing wrong?

2. The second part of this issue is around duplication - the overall table (at present), will show duplicate entries in the list, depending on whether a person has submitted more than 1 form for any specific 7 day period (the 7 day period is for finance/payroll purposes). I need to adjust the code I have so far to show 1 line per person, with the results of each line combined into 1 entry:

John Smith / R R R R CS /

I've been trying to get my head around how to achieve this, given that I can't control the number of forms submitted for any one week, and that this table will have 100+ entries. The more I look, the more I can't see the wood for the trees! I've tried a number of combinations, but every single one has failed; I've not found anything that has remotely worked, or produced something that risks becoming too much of a beast to support in the future.

Anyone have any ideas, hints, tips, etc that might help please? The original incarnation of this site used a list, which we had to periodically update, in order to build a record that Sharepoint could then update with recorded absences; I'm keen to avoid this, and use SPServices to build this report dynamically if possible...



Oct 8, 2012 at 3:16 PM


It looks like you're not clearing the array in your .each loop. That's why you'd see the Rs from the prior item.

On your second point, I'm not exactly sure. You probably will need to loop through all of the items for each person and combine them somehow based on your business rules.