rolling date filter

Aug 25, 2011 at 9:11 PM

how can I setup my caml to filter by last month or last quarter? I have several calculated columns that return month as "MMYYYY" and quarter as "QTRYYYY". I just cant figure out how to make this dynamic and default to the last month. Any suggests or ideas?

I have added a var to return date, and I can parse this any way I need to. I guess I need to add some logic to find out what current month is to filter by last, same for quarter. any help with the logic would be appreciated.

Coordinator
Aug 26, 2011 at 2:52 PM

Date arithmetic is problematic in any language because of the wacky way we keep track of things with different number bases, etc.

You might want to look for other JavaScript libraries which would help with this part of your work. Otherwise, it's brute force coding.

M.

Sep 6, 2011 at 2:45 PM
Edited Sep 6, 2011 at 2:47 PM

Thanx Marc. I was actually able to create a work around. my problem now is figuring out how to requery the list. I have a calculated column that returns a value  Qtr1-YYYY, Qtr2-YYYY,Qtr3-YYY and Qtr4-YYYY. In the jquery script using your SPServices I have added  a calculation for current and last quarter. I am also building a dropdown using these values. The dropdown defaults to current qtr and on page load the view is filtered to current quarter. I can't figure out how requery when the dropdown changes. I don't think I want to reload the entire script, but I am not sure. here is a sample of my code. Thanks for for any insight

<!-- qtrs ddl -->
<div class="quarters" id='qtrs'><select id='items'></select></div>
<div id="TimeKeepingAll"></div>
<script type="text/javascript">

$(document).ready(function(){
	$("#TimeKeepingAll").html("");

	var currentTime = new Date();
	var month = currentTime.getMonth() + 1;
	var day = currentTime.getDate();
	var year = currentTime.getFullYear();
	var date = (month + "/" + day + "/" + year);
	var qtr;
	var lstqtr;
	var mmyyyy = (month + "" + year);

	if (month > 0 && month < 4 ) {
		qtr = ("Qtr1 - " + year);
		lstqtr = ("Qtr4 - " + (year-1));
	}
	if (month > 3 && month < 7 ) {
		qtr = ("Qtr2 - " + year);
		lstqtr = ("Qtr1 - " + year);
	}
	if (month > 6 && month < 10 ) {
		qtr = ("Qtr3 - " + year);
		lstqtr = ("Qtr2 - " + year);
	}
	if (month > 9 && month < 13 ) {
		qtr = ("Qtr4 - " + year);
		lstqtr = ("Qtr3 - " + year);
	}

// ddl array
var quarters = [qtr,lstqtr] 
// populate ddl
$.each(quarters, function(val, text) { 
            $('#items').append( $('<option></option>').val(val).html(text) ) 
});


// Company name 
var company = "Buckeye";
var qspan = $("#items :selected").text();  //qtr; // or lstqtr;
var wtype;
// Type of Query set hours = A or M
var hours = "A"; // "M";
var cap;



// Do not change here
var wtypeM = "<Eq><FieldRef Name='Work_Type' /><Value Type='Text'>Maintenance</Value></Eq>";
var wtypeA = "<IsNotNull><FieldRef Name='Work_Type' /></IsNotNull>";

if( hours == "M") {
	wtype = wtypeM;
}
else if ( hours == "A") { 
	wtype = wtypeA;
}
else {
	wtype = wtypeA;
}


if (wtype == wtypeM) { 
	cap = "Billable Maintenance Hours";
} 
else {
	cap = "All Billable Hours";
}



$().SPServices({
    operation: "GetListItems",
    webURL: "/sites/CustomerPortal/admin",
    async: false,
    listName: "Timekeeping",
	    CAMLQuery: "<Query><Where><And><And><Contains><FieldRef Name='Company' /><Value Type='Text'>" + company + "</Value></Contains><Eq><FieldRef Name='Quarter' /><Value Type='Calculated'>" + qspan + "</Value></Eq></And>"+ wtype +"</And></Where><OrderBy><FieldRef Name='WorkDate' Ascending='False' /></OrderBy></Query>",	
	CAMLViewFields: "<ViewFields><FieldRef Name='Month' /><FieldRef Name='Hrs_Worked' /><FieldRef Name='Emp' /><FieldRef Name='Emp 2' /><FieldRef Name='Work_Type' /><FieldRef Name='Company' /><FieldRef Name='Issue_x0020_No_x002e_' /><FieldRef Name='RoutingRuleDescription' /></ViewFields>", 
     completefunc: function (xData, Status) {

	  var strHtml = "<table id='tkAll' class='timekeeping'>";
	  strHtml += "<caption>" + cap + " - " + qspan + "</caption>";
	  strHtml += "<tbody>";
	  var hoursWorked = 0;
	  var recNum = 0;
	
	 	strHtml += "<tr class='timekeeping-header'>";
		strHtml += "<th>#</th>";
		strHtml += "<th>Work Date</th>";
		//strHtml += "<th>Month</th>";
		strHtml += "<th id='hoursworked'>Hrs Worked</th>";
		strHtml += "<th>Emp</th>";
		
		strHtml += "<th>Type</th>";
		strHtml += "<th>Company</th>";
		strHtml += "<th>Issue Number</th>";
		strHtml += "<th>Description</th>";
		strHtml += "</tr>";

      $(xData.responseXML).find("[nodeName='z:row']").each(function(i) {
			var month = $(this).attr("ows_Month");
			var hrs = parseFloat($(this).attr("ows_Hrs_Worked"));
			var hours = hrs.toFixed(2);
			var emp = $(this).attr("ows_Emp");
			var desc = $(this).attr("ows_RoutingRuleDescription"); 
			var workday = $(this).attr("ows_WorkDate");
			var worktype = $(this).attr("ows_Work_Type");
			var comp = $(this).attr("ows_Company");
			var issues = $(this).attr("ows_Issue_x0020_No_x002e_");
						
			recNum = (recNum +1);

			strHtml += "<tr class='timekeeping-item'>";	
			strHtml += "<td class='rec'>" + recNum + "</td>";
			/*
			strHtml += "<td class='month'>";
			if(month != undefined)
			{
				var tempm = month.split('#');
				strHtml += "<div>" + tempm[1] + "</div>";			
			}
			strHtml += "</td>";
			*/
			strHtml += "<td class='workday'>";
			if(workday != undefined)
			{
				var tempw = workday.split(' ');
				strHtml += "<div>" + tempw[0] + "</div>";			
			}
			
			strHtml += "</td>";
			strHtml += "<td class='hours'>" + hours + "</td>";
			

			strHtml += "<td class='emp'>"; 
			if(emp != undefined) 
			{
				strHtml += "<div>" + emp + "</div>"; 
			}
			strHtml +="</td>";
			
			
			strHtml += "<td class='worktype'>" + worktype + "</td>";
			strHtml += "<td class='comp'>" + comp + "</td>";
			strHtml += "<td class='issues'>";
			if(issues != undefined)
			{
				strHtml += "<div>" + issues + "</div>";
			}
			strHtml += "</td>";
			strHtml += "<td class='desc'>";
			if(desc != undefined)
			{
			strHtml += "<div>" + desc + "</div>";
			}
			strHtml += "</td>";
			strHtml += "</tr>";

			hoursWorked = (hoursWorked + hrs);			
			
      });

		hoursWorked = hoursWorked.toFixed(2);
		
	strHtml += "</tbody><tfoot><tr class='timekeeping-footer'>";
	strHtml += "<td></td>";
	strHtml += "<td colspan='2' class='hrsfooterlbl'>Total hours: <span class='hrsfooter'>" + hoursWorked + "</span></td>";
	strHtml += "<td></td>";
	strHtml += "<td></td>";
	strHtml += "<td></td>";
	strHtml += "<td></td>";
	strHtml += "<td class='recfooter'>" + recNum + ": Records</td></tr></tfoot>";
	
      strHtml += "</table>";
		
	$("#tkAll tfoot td:last").text($("tkAll tbody tr").length + ' Records');
   	$("#TimeKeepingAll").append(strHtml);
	
    }
  });
  
		$('tr.timekeeping-item:even').addClass('even'); 
		$('tr.timekeeping-item:odd').addClass('odd'); 
 
  

});
</script>