4

Here I am trying to filter data based on created date. At 1st I tried in Graph Explorer and it's working.

https://graph.microsoft.com/v1.0/me/messages?$filter=createdDateTime ge 2017-09-04&$select=subject,lastModifiedDateTime

Now trying to implement same in Dell Boomi. This is resource path to pull all the items: sites/{id}.sharepoint.com:/sites/{id}:/lists/{list_id}/items it's working fine.

After that I am adding filter condition:

sites/{id}.sharepoint.com:/sites/{id}:/lists/{list_id}/items?$filter=lastModifiedDateTime ge 2017-09-04&$select=email,displayName

Here is getting error. This is the error message:

<HTML><HEAD><TITLE>Bad Request</TITLE>
<META HTTP-EQUIV="Content-Type" Content="text/html; charset=us-ascii"></HEAD>
<BODY><h2>Bad Request</h2>
<hr><p>HTTP Error 400. The request is badly formed.</p>

Can some one help on this, how to fix this issue? Here is the Sample data.

> {   "@odata.context":
> "https://graph.microsoft.com/v1.0/$metadata#Collection(microsoft.graph.list)(&#39;1334af71-5b7a-4276-a8d8-c3f3f129051d&#39;)/items",
> "value": [
>     {
>       "@odata.etag": "&quot;ef6e961c-a956-400e-a77d-f044d2e0b894,8&quot;",
>       "createdDateTime": "2018-05-24T13:38:10Z",
>       "eTag": "&quot;ef6e961c-a956-400e-a77d-f044d2e0b894,8&quot;",
>       "id": "3",
>       "lastModifiedDateTime": "2018-06-18T10:24:27Z",
>       "webUrl": "https://{id}.sharepoint.com/sites/{id}/Doc%20Interfaces/757391.pdf",
>       "createdBy": {
>         "user": {
>           "email": "abc@abc.COM",
>           "id": "173abc",
>           "displayName": "abc"
>         }
>       },
>       "lastModifiedBy": {
>         "user": {
>           "email": "xyz@abc.COM",
>           "id": "234xyz",
>           "displayName": "xyz"
>         }
>       },
>       "parentReference": {
>         "id": "03fe-16595a0da875"
>       },
>       "contentType": {
>         "id": "0x01"
>       },
>       "fields@odata.context": "https://graph.microsoft.com/v1.0/$metadata#Collection(microsoft.graph.list)(&#39;1334f71-c3f3f129&#39;)/items(&#39;3&#39;)/fields/$entity",
>       "fields": {
>         "@odata.etag": "&quot;ef6e961-f044d2e0b894,8&quot;",
>         "FileLeafRef": "757391.pdf",
Debmalya Ghosh
  • 79
  • 1
  • 2
  • 6

3 Answers3

6

Short Answer

Use the auto generated SharePoint list item fields Created or Modified

/items?expand=fields&$filter=fields/Modified gt '2018-01-01'

Important Note

To perform filter queries on these fields, you will have to either:

  • Index these columns (see how here)
  • Or Set the 'Prefer: HonorNonIndexedQueriesWarningMayFailRandomly' header on your request (not recommended by Microsoft)

Explenation

It seems like filtering on the values that are returned by the graph endpoint (such as lastModifiedDateTime, createdDateTime, etc.) is not supported, since requests like /items&$filter=lastModifiedDateTime ge '2018-01-01' will return a "Invalid filter clause" error.

JollyBrackets
  • 551
  • 5
  • 9
  • This one is working for me: /items?$expand=Fields&$filter=fields/Modified lt '2018-12-28T00:00:00Z' – rgrebski Jun 22 '18 at 08:35
  • Not sure why it's not working for me. I tried this also tired to tweak this but not working. Same error. Not sure if dell boomi handle the this in some other way. – Debmalya Ghosh Jun 29 '18 at 13:07
  • Thanks @JollyBrackets, this works for me but did you notice that 'equals' doesn't seem to work? I know the id of a list item but always get the "Invalid filter clause" error. Any ideas? – colonel_claypoo Mar 06 '19 at 09:02
1

I just solved a very similar problem in submitting an OData query to Boomi. The issue was the spaces in the filter string:

Your string: $filter=lastModifiedDateTime ge '2017-09-04' Should be: $filter=lastModifiedDateTime%20ge%20'2017-09-04'

Rachael Martino
  • 11
  • 1
0

please try 

sites/{id}.sharepoint.com:/sites/{id}:/lists/{list_id}/items?$filter=lastModifiedDateTime ge '2017-09-04'&$select=email,displayName

filter=lastModifiedDateTime ge '2017-09-04' --> single quotes

NOTE : If you are selecting any lookup values you need to use $expand query options For example:

sites/{id}.sharepoint.com:/sites/{id}:/lists/{list_id}/items?$filter=lastModifiedDateTime ge '2017-09-04'&$select=email,displayName&$expand=displayName/Name
Sreeraj Sree
  • 177
  • 10
  • Thanks for your input. I had tried this`'2017-09-04'` before but it's not working. – Debmalya Ghosh Jun 21 '18 at 08:40
  • Did you try with the $expand – Sreeraj Sree Jun 21 '18 at 09:10
  • For better understanding I have added sample data in Question. Please check. Here I am using this URL to pull the data. `sites/{id}.sharepoint.com:/sites/{id}:/lists/{list_id}/items?expand=fields`. By this I am able to get data mention above. After that I am tring to use Filter by date it's not working. This is the URL: **sites/{id}.sharepoint.com:/sites/{id}:/lists/{list_id}/items?$filter=lastModifiedDateTime ge '2017-09-04'&$expand=value/webUrl** – Debmalya Ghosh Jun 21 '18 at 13:06
  • Also doesn't work for me. I'm not able to filter on any of the "meta" data, not even the ID (e.g. ?filter=id eq '15') – JollyBrackets Jun 21 '18 at 15:33