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>

Comments

Popular posts from this blog

Setting SharePoint announcements to auto delete after expiration

Updating a single field in a SharePoint List using Power Automate Flows

SharePoint driven rich text dashboard using jqueryui. (JQuery file)