0

My CAML query seems to be ignored as I'm getting the same result no matter which dates I send.

I found an JavaScript example which seemed to work fine until I started to edit my query

function getListItems(webUrl, listTitle, viewXml) {
    var url = webUrl + "/_api/web/lists/getbytitle('" + listTitle + "')/getitems";
    var queryPayload = {
        'query': {
            '__metadata': { 'type': 'SP.CamlQuery' },
            'ViewXml': viewXml
        }
    };
    return executeJson(url, "POST", null, queryPayload);
}

function executeJson(url, method, headers, payload) {
    method = method || 'GET';
    headers = headers || {};
    headers["Accept"] = "application/json;odata=verbose";
    if (method == "POST") {
        headers["X-RequestDigest"] = $("#__REQUESTDIGEST").val();
    }
    var ajaxOptions =
    {
        url: url,
        type: method,
        contentType: "application/json;odata=verbose",
        headers: headers
    };
    if (typeof payload != 'undefined') {
        ajaxOptions.data = JSON.stringify(payload);
    }
    return $.ajax(ajaxOptions);
}

And this is the CAML I'm sending

<View>
    <ViewFields>
        <FieldRef Name="Title"/>
        <FieldRef Name="Location"/>
        <FieldRef Name="EventDate"/>
        <FieldRef Name="EndDate"/>
    </ViewFields>
    <OrderBy>
        <FieldRef Name="EventDate"/>
    </OrderBy>
    <Where>
        <And>
            <Geq>
                <FieldRef Name="EventDate"/>
                <Value Type="DateTime">2016-12-31T23:00:00.000Z</Value>
            </Geq>
            <Lt>
                <FieldRef Name="EventDate"/>
                <Value Type="DateTime">2017-01-31T22:59:59.999Z</Value>
            </Lt>
        </And>
    </Where>
    <QueryOptions>
        <RowLimit>100</RowLimit>
    </QueryOptions>
</View>

No matter which dates I enter I'm always getting the same five events between 2016-11-22 and 2016-11-25.

Thew 'ViewFields' seem to be ignored as well. Is this some kind of "dumb user isn't able to query correctly" fallback?

The SharePoint-Version I'm using is SharePoint Foundation 2013

Thanks!

  • I find that SP returns unexpected results when it gets confused with the CAML. Have you tried including a IncludeTimeValue='TRUE' attribute on your Value node? – Jeff Freeman Nov 28 '16 at 19:17
0

There's most probably a syntax error in your CAML query. What was the original CAML you used (the one that worked fine)?
I think you're missing a parent <Query> tag arround Where and OrderBy. your query should look like:

<View>
    <ViewFields>
        <FieldRef Name="Title"/>
        <FieldRef Name="Location"/>
        <FieldRef Name="EventDate"/>
        <FieldRef Name="EndDate"/>
    </ViewFields>
    <Query>
        <OrderBy>
            <FieldRef Name="EventDate"/>
        </OrderBy>
        <Where>
            <And>
                <Geq>
                    <FieldRef Name="EventDate"/>
                    <Value Type="DateTime">2016-12-31T23:00:00.000Z</Value>
                </Geq>
                <Lt>
                    <FieldRef Name="EventDate"/>
                    <Value Type="DateTime">2017-01-31T22:59:59.999Z</Value>
                </Lt>
            </And>
        </Where>
    <Query>
    <QueryOptions>
        <RowLimit>100</RowLimit>
    </QueryOptions>
</View>

(reference for the View element: https://msdn.microsoft.com/en-us/library/office/ms438338.aspx; as you can see, View does have Where as a possible child element, only Query).

  • Oh thank you! I've copied so many different sources that I've lost track of the correct ones. – Ben Nov 28 '16 at 20:22
0

As Evariste said, you should add the Query node. I'd also add the IncludeTimeValue property in order to avoid time issues.

<View>
  <ViewFields>
    <FieldRef Name="Title"/>
    <FieldRef Name="Location"/>
    <FieldRef Name="EventDate"/>
    <FieldRef Name="EndDate"/>
  </ViewFields>
  <Query>
    <OrderBy>
      <FieldRef Name="EventDate"/>
    </OrderBy>
    <Where>
      <And>
        <Geq>
          <FieldRef Name="EventDate"/>
          <Value IncludeTimeValue="TRUE" Type="DateTime">2016-12-31T23:00:00.000Z</Value>
        </Geq>
        <Lt>
          <FieldRef Name="EventDate"/>
          <Value IncludeTimeValue="TRUE" Type="DateTime">2017-01-31T22:59:59.999Z</Value>
        </Lt>
      </And>
    </Where>
  </Query>
  <QueryOptions>
    <RowLimit>100</RowLimit>
  </QueryOptions>
</View>

On the other hand, to avoid the usage of CAML queries, you can use rest services like this:

var select = "$select=Title,EventDate,EndDate";
var filter = "$filter= (EventDate ge datetime'2016-12-31T23:00:00.000Z') and (EventDate lt datetime'2017-01-31T22:59:59.999Z')";
var orderBy = "$orderby=EventDate asc";


var url = webUrl + "/_api/web/lists/GetByTitle('" + listTitle + "')/items?" + select + "&" + filter  + "&" + orderBy;
$.ajax({
    url: url,
    method: "GET",
    headers: { "Accept": "application/json; odata=verbose" },
    success: function (data) {
        if (data.d.results.length > 0) {
            // do something with ata.d.results
        }
    },
    error: function (data) {
        alert("Error: " + data);
    }
});
  • Thank you! Can you explain what "Time issues" could be? – Ben Nov 28 '16 at 21:35
  • I have to stop pressing enter to add a new Line ... Thank you for the second part of your post! I'm not a big fan of CAML to be honest ... – Ben Nov 28 '16 at 21:36
  • A Time Issue could be: if EventDate is 31/01/2017 and the IncludeTimeValue property is not present, then the query will not include items with EventDate = 31/01/2017. If we use that property the query will return items with EventDate < 31/01/2016 22:59:59. – Cecilia Nov 28 '16 at 21:47
  • Thank you! After testing the filter you posted I remember why I switched to CAML... {"readyState":4,"responseText":"{\"error\":{\"code\":\"-1, Microsoft.SharePoint.SPException\",\"message\":{\"lang\":\"en-US\",\"value\":\"The field 'EventDate' of type 'DateTime' cannot be used in the query filter expression.\"}}}","responseJSON":{"error":{"code":"-1, Microsoft.SharePoint.SPException","message":{"lang":"en-US","value":"The field 'EventDate' of type 'DateTime' cannot be used in the query filter expression."}}},"status":400,"statusText":"Bad Request"} – Ben Nov 28 '16 at 21:53

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.