2

I am trying to filter the list items by the year and then count those list items. This is as far as I got, but it produces an error. I guess there's an issue with the filter syntax. Can anyone help?

var year = new Date().getFullYear().toString();

function(){
$.ajax({
    url: _spPageContextInfo.webAbsoluteUrl + "/_api/Web/Lists(guid'BF20B386-CF99-4353-B889-6BBB99612420')/items?$select=Created&$filter=(Created startsWith 'year')",
    type: "GET",
    headers: { "ACCEPT": "application/json;odata=verbose" },
    success: function(data){

        console.log(data.d.results); 

        },
    error:  function(){
      alert("Failed to query SharePoint list data. Please refresh (F5).");
}});

} }

EDIT:

As it was pointed out, the Created field is an integer, and startsWith will not work. I like the solution provided using greater than 2017-01-01... but that is not dynamic. I'd have to change it next year. So instead, i found another column that had the date in a string and used the substringof function to accomplish the task:

$.ajax({
url: _spPageContextInfo.webAbsoluteUrl + "/_api/Web/Lists(guid'BF20B386-CF99-4353-B889-6BBB99612420')/items?$filter=substringof('" + year + "',Title)",
    type: "GET",
    headers: { "ACCEPT": "application/json;odata=verbose" },
    success: function(data){
        var newpermnum = data.d.results.length + 1;
        console.log(data.d.results.length + 1); 

        },
    error:  function(){
      alert("Failed to query SharePoint list data. Please refresh (F5).");
}});

Q: this is my first question! Should I mark an answer as correct even thought i found a different work around?

  • Have you tried e.g. filter=Created eq '2016' – Damien Flood Aug 9 '17 at 18:43
3

You'll need to use a greater than or less than comparison when working with a datetime field rather than starts with (used for string comparison).

$.ajax({

url: _spPageContextInfo.webAbsoluteUrl + "/_api/Web/Lists(guid'BF20B386-CF99-4353-B889-6BBB99612420')/items?$select=Created&$filter=Start_x0020_Date ge datetime'2017-01-01T00:00:01Z')",

....
  • I just found a workaround for my issue. I used a different column that contained the date in string and used the substringof function to filter out the list items using my year variable: $.ajax({ url: _spPageContextInfo.webAbsoluteUrl + "/_api/Web/Lists(guid'BF20B386-CF99-4353-B889-6BBB99612420')/items?$filter=substringof('" + year + "',Title)", type: "GET", headers: { "ACCEPT": "application/json;odata=verbose" }, success: function(data){ var newpermnum = data.d.results.length + 1; console.log(data.d.results.length + 1); }, – cor150 Aug 9 '17 at 19:13
  • also i'm really sorry, this is my first question and i'm not familiar with the correct protocol here. – cor150 Aug 9 '17 at 19:14
  • No worries. Bear in mind that you may be dealing with a unindexed column and it may be a bit slower if the list you're working with grows. Whatever works. :) – Graham Aug 9 '17 at 19:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.