Querying a SharePoint list with a date via CAML Query
I have this SharePoint list with some data in it. There's a start date, and an end date among other fields. The user wanted to be able to enter a specific date and then find out all the contracts that were active on that date. I could build a view to show that data, but they'd need to provide the date and create a view which is not really what they wanted.
All they wanted was output on the screen.
So... I built this .js file with a CAML Query to grab the right data. The same company could have multiple contracts an should only be counted once, so that's why you see if(contract != prevcontract) below.
<h3><p id="outputreport" >Number of Prime Companies with Afghanistan Deployments. Enter a date:</p></h3>
<input id="inputdate" type="text" name="start" >
<input type="button" value="Submit" onclick="getPrime(document.getElementById('inputdate').value);">
<script type="text/javascript" src="/Style Library/js/jquery-1.11.3.min.js"></script>
<script type="text/javascript" src="/Style Library/js/jquery.SPServices.min.js"></script>
<script type="text/javascript">
var site;
var context;
var website;
var sStyles = new Array();
function getPrime(dte)
{
var method = "GetListItems";
var listName = "Contract";
var prime = "";
var count = 0;
var contract = "";
var prevcontract = "";
var sDte = new Date(dte);
sDte = sDte.toJSON();
var camlQuery ='<Query> \
<OrderBy> \
<FieldRef Name=\'Contractor\'/> \
</OrderBy> \
<Where> \
<And> \
<Eq> \
<FieldRef Name=\'Prime\'/> \
<Value Type=\'Integer\'>1</Value> \
</Eq> \
<And> \
<Geq> \
<FieldRef Name=\'POPEndDate\'/> \
<Value Type=\'DateTime\'>'+sDte+'</Value> \
</Geq> \
<Leq> \
<FieldRef Name=\'POPStartDate\'/> \
<Value Type=\'DateTime\'>'+sDte+'</Value> \
</Leq> \
</And> \
</And> \
</Where> \
</Query>';
$().SPServices
(
{
operation: method,
//webURL: rootSite,
async: false,
listName: listName,
CAMLQuery: camlQuery,
//All the above will define what we want SPServices to do for us. This next line will execute
//our results, which is returned as XML in xData.responseXML
completefunc: function (xData, Status)
{
// alert(xData.responseText);
// the line above gives you debug info
//This next line will iterate through each row in our XML and execute a function
$(xData.responseXML).find("z\\:row").each
(
function()
{
popstart = $(this).attr("ows_POPStartDate");
contract = $(this).attr("ows_Title");
if(contract != prevcontract)
{
count = count + 1;
prevcontract = contract;
}
}
);
document.getElementById("outputreport").innerHTML = "As of " +dte+ ", the number of prime companies that have deployments in Afghanistan was " + count;
}
}
);
}
</script>
All they wanted was output on the screen.
So... I built this .js file with a CAML Query to grab the right data. The same company could have multiple contracts an should only be counted once, so that's why you see if(contract != prevcontract) below.
<h3><p id="outputreport" >Number of Prime Companies with Afghanistan Deployments. Enter a date:</p></h3>
<input id="inputdate" type="text" name="start" >
<input type="button" value="Submit" onclick="getPrime(document.getElementById('inputdate').value);">
<script type="text/javascript" src="/Style Library/js/jquery.SPServices.min.js"></script>
<script type="text/javascript">
var site;
var context;
var website;
var sStyles = new Array();
function getPrime(dte)
{
var method = "GetListItems";
var listName = "Contract";
var prime = "";
var count = 0;
var contract = "";
var prevcontract = "";
var sDte = new Date(dte);
sDte = sDte.toJSON();
var camlQuery ='<Query> \
<OrderBy> \
<FieldRef Name=\'Contractor\'/> \
</OrderBy> \
<Where> \
<And> \
<Eq> \
<FieldRef Name=\'Prime\'/> \
<Value Type=\'Integer\'>1</Value> \
</Eq> \
<And> \
<Geq> \
<FieldRef Name=\'POPEndDate\'/> \
<Value Type=\'DateTime\'>'+sDte+'</Value> \
</Geq> \
<Leq> \
<FieldRef Name=\'POPStartDate\'/> \
<Value Type=\'DateTime\'>'+sDte+'</Value> \
</Leq> \
</And> \
</And> \
</Where> \
</Query>';
$().SPServices
(
{
operation: method,
//webURL: rootSite,
async: false,
listName: listName,
CAMLQuery: camlQuery,
//All the above will define what we want SPServices to do for us. This next line will execute
//our results, which is returned as XML in xData.responseXML
completefunc: function (xData, Status)
{
// alert(xData.responseText);
// the line above gives you debug info
//This next line will iterate through each row in our XML and execute a function
$(xData.responseXML).find("z\\:row").each
(
function()
{
popstart = $(this).attr("ows_POPStartDate");
contract = $(this).attr("ows_Title");
if(contract != prevcontract)
{
count = count + 1;
prevcontract = contract;
}
}
);
document.getElementById("outputreport").innerHTML = "As of " +dte+ ", the number of prime companies that have deployments in Afghanistan was " + count;
}
}
);
}
</script>
Comments
Post a Comment