3
votes

I'm trying to get a collection of list items from a SharePoint through Microsoft Graph, which I want to filter by CreatedBy. Requesting: https://graph.microsoft.com/v1.0/sites/{siteid}/lists/TeamRequests/items

Returns:

{
"@odata.context": "https://graph.microsoft.com/v1.0/$metadata#sites('{url}')/lists('TeamRequests')/items",
"value": [
    {
        "@odata.etag": "\"56ad787e-bd69-464a-b5da-dd953e40d7c4,13\"",
        "createdDateTime": "2018-02-26T08:34:26Z",
        "eTag": "\"56ad787e-bd69-464a-b5da-dd953e40d7c4,13\"",
        "id": "11",
        "lastModifiedDateTime": "2018-03-22T13:20:03Z",
        "webUrl": "{url}/Lists/TeamRequests/11_.000",
        "createdBy": {
            "user": {
                "email": "{email}",
                "id": "9c9cbb67-c049-4a2d-845d-6c5ca2300041",
                "displayName": "{Name}"
            }
        },
        "lastModifiedBy": {
            "user": {
                "email": "{email}",
                "id": "9c9cbb67-c049-4a2d-845d-6c5ca2300041",
                "displayName": "{Name}"
            }
        },
        "parentReference": {},
        "contentType": {
            "id": "0x01005F15F8133495554D834FF82F187AD0630002133A9CCDE4494D8CB2206D7D6453D6"
        }
    },

Now I'd like to filter this request for createdBy, either Id, displayName or email address. I tried ?$filter=createdBy/user/email eq '{email}' and similar requests for id or displayName. They all return

{
    "error": {
        "code": "generalException",
        "message": "An unspecified error has occurred.",
        "innerError": {
            "request-id": "492e3bde-05fe-4484-a475-435ff0aa70b6",
            "date": "2018-07-23T07:41:46"
        }
    }
}

So how to accomplish this filter? Is it even supported?

2

2 Answers

4
votes

Even though it sounds like a straightforward query, i have not come up to anything more simple then the following solution:

It seems filtering by user field is not supported except the case when user id is provided, that's the reason why the solution consists of two steps:

1) First, we need to determine user Id by Email , for that purpose the following query could be utilized:

https://graph.microsoft.com/v1.0/sites/root/lists('User Information List')/items?expand=fields(select=Id,Email)

*where User Information List system list stores user properties including Id and Email properties *

2) Once the user Id is resolved, the final query to filter items by user id could be applied:

https://graph.microsoft.com/v1.0/sites/{site-id}/lists('list-name')/items?filter=fields/<user-field-name>LookupId eq '<user-id>'  

where

<user-field-name>LookupId is a field which is getting exposed in addition to user field, in case of Created field the name should be AuthorLookupId

Example:

https://graph.microsoft.com/v1.0/sites/root/lists('TeamRequests')/items?filter=fields/AuthorLookupId eq '10'

Note

In some cases the following error is returned Field '' cannot be referenced in filter or orderby as it is not indexed. Provide the 'Prefer: HonorNonIndexedQueriesWarningMayFailRandomly' header to allow this, but be warned that such queries may fail on large lists.

In that case the following request header needs to be applied:

Prefer: HonorNonIndexedQueriesWarningMayFailRandomly

0
votes

When I tried to access User Information List list. There is an error with list not found.

So since in my case, first I filtered list data based on status value and next step got the logged in user display name and filtered listitems based on display name.

I'm using @pnp/graph

Filter list data with status filter

let resultsList = await listItemsQuery.filter("fields/RequestStatus eq 'Approval Pending'").expand("fields").get<MicrosoftGraph.ListItem[]>();

Get Logged-In Profile:

const profileQueryEndPoint = new GraphQueryableCollection(graph.me, "/");

Select displayName property from above result

let profileData : User = await profileQueryEndPoint.select("displayName").get <User>(); 
console.log('displayName : '  + profileData['displayName']);

Filter ListItems with createdby Field by passing profileData['displayName']

let filterUserCreatedRequests: MicrosoftGraph.ListItem[] = resultsList.filter(ListItem => ListItem["createdBy"].user.displayName === profileData['displayName']);

Display filtered results

console.log('filterUserCreatedRequests : ' + JSON.stringify(filterUserCreatedRequests));

I'm giving all the steps for your reference. But above code can simplified more.

Hope this helps someone :)