How to expand lookup field when listing items ?

Piotr Kochanowski 26 Reputation points
2022-07-06T13:06:47.003+00:00

Hello

Graph api does not return lookup columns when listing items even if requested in expand/select statement.

I have a lookup column a named "Client"

Everything works fine when I try to call graph api to get single item by its ID (https://graph.microsoft.com/beta/sites/{site-id}/lists/{list-id}/items/{item-id}?$expand=fields(select=Client,ClientLookupId). I get Client column text value and Client column LookupId value, no problem here.

But when I want to list all items in the list (https://graph.microsoft.com/beta/sites/{site-id}/lists/{list-id}/items?$expand=fields(select=Client,ClientLookupId) - it doesn't work, the field is not included in the results.

Is there any way I can get that field value returned while listing items ?

Regards
Piotr Kochanowski

Microsoft Graph
Microsoft Graph
A Microsoft programmability model that exposes REST APIs and client libraries to access data on Microsoft 365 services.
9,936 questions
0 comments No comments
{count} vote

2 answers

Sort by: Most helpful
  1. AtulThorat-MSFT 391 Reputation points
    2022-07-06T15:48:36.887+00:00

    Hello @Piotr Kochanowski ,

    Hope you are doing well.

    I have sharepoint list named "Employee" and Lookup column "Company Details" with 2 items as shown below.

    218159-employee-list.png
    I tried below graph API endpoint,

    GET : https://graph.microsoft.com/V1.0/sites/{site-id}/lists/{list-id}/items?$expand=fields(select=Client,ClientLookupId)

    GET : https://graph.microsoft.com/beta/sites/{site-id}/lists/{list-id}/items?$expand=fields(select=Client,ClientLookupId)

    With my Sharepoint Site Id, List Id I tried below Graph API Endpoint

    GET https://graph.microsoft.com/v1.0/sites/raiinfy.sharepoint.com,37e3215c-e146-448d-9703-d885abebd366,e0247e7e-4c0c-4d50-903c-06581b278f9c/lists/9d67d52a-b7af-474d-8123-cbaaca8b90c2/items?expand=fields($select=Company_x0020_Details,Company_x0020_DetailsLookupId)

    GET https://graph.microsoft.com/beta/sites/raiinfy.sharepoint.com,37e3215c-e146-448d-9703-d885abebd366,e0247e7e-4c0c-4d50-903c-06581b278f9c/lists/9d67d52a-b7af-474d-8123-cbaaca8b90c2/items?expand=fields($select=Company_x0020_Details,Company_x0020_DetailsLookupId)

    I could able to get All items field values as shown below

    {  
        "@odata.context": "https://graph.microsoft.com/v1.0/$metadata#sites('raiinfy.sharepoint.com%2C37e3215c-e146-448d-9703-d885abebd366%2Ce0247e7e-4c0c-4d50-903c-06581b278f9c')/lists('9d67d52a-b7af-474d-8123-cbaaca8b90c2')/items",  
        "value": [  
            {  
                "@odata.etag": "\"4615ef9d-71f8-4a24-9bd8-6a89b3952825,1\"",  
                "createdDateTime": "2022-07-06T13:50:23Z",  
                "eTag": "\"4615ef9d-71f8-4a24-9bd8-6a89b3952825,1\"",  
                "id": "1",  
                "lastModifiedDateTime": "2022-07-06T13:50:23Z",  
                "webUrl": "https://raiinfy.sharepoint.com/sites/TestSite2/Lists/Employee/1_.000",  
                "createdBy": {  
                    "user": {  
                        "email": "Atul1@raiinfy.onmicrosoft.com",  
                        "id": "93feaf8f-b55f-476c-97ed-7817e2f69ca5",  
                        "displayName": "Atul"  
                    }  
                },  
                "lastModifiedBy": {  
                    "user": {  
                        "email": "Atul1@raiinfy.onmicrosoft.com",  
                        "id": "93feaf8f-b55f-476c-97ed-7817e2f69ca5",  
                        "displayName": "Atul"  
                    }  
                },  
                "parentReference": {  
                    "siteId": "raiinfy.sharepoint.com,37e3215c-e146-448d-9703-d885abebd366,e0247e7e-4c0c-4d50-903c-06581b278f9c"  
                },  
                "contentType": {  
                    "id": "0x0100856E8A8C34906344ACBBBEF7C67EFA27",  
                    "name": "Item"  
                },  
                "fields@odata.context": "https://graph.microsoft.com/v1.0/$metadata#sites('raiinfy.sharepoint.com%2C37e3215c-e146-448d-9703-d885abebd366%2Ce0247e7e-4c0c-4d50-903c-06581b278f9c')/lists('9d67d52a-b7af-474d-8123-cbaaca8b90c2')/items('1')/fields/$entity",  
                "fields": {  
                    "@odata.etag": "\"4615ef9d-71f8-4a24-9bd8-6a89b3952825,1\"",  
                    "Company_x0020_Details": "mno",  
                    "Company_x0020_DetailsLookupId": "1"  
                }  
            },  
            {  
                "@odata.etag": "\"c47fa1ef-09f1-4bf7-ad89-070b53498505,1\"",  
                "createdDateTime": "2022-07-06T14:25:43Z",  
                "eTag": "\"c47fa1ef-09f1-4bf7-ad89-070b53498505,1\"",  
                "id": "2",  
                "lastModifiedDateTime": "2022-07-06T14:25:43Z",  
                "webUrl": "https://raiinfy.sharepoint.com/sites/TestSite2/Lists/Employee/2_.000",  
                "createdBy": {  
                    "user": {  
                        "email": "Atul1@raiinfy.onmicrosoft.com",  
                        "id": "93feaf8f-b55f-476c-97ed-7817e2f69ca5",  
                        "displayName": "Atul"  
                    }  
                },  
                "lastModifiedBy": {  
                    "user": {  
                        "email": "Atul1@raiinfy.onmicrosoft.com",  
                        "id": "93feaf8f-b55f-476c-97ed-7817e2f69ca5",  
                        "displayName": "Atul"  
                    }  
                },  
                "parentReference": {  
                    "siteId": "raiinfy.sharepoint.com,37e3215c-e146-448d-9703-d885abebd366,e0247e7e-4c0c-4d50-903c-06581b278f9c"  
                },  
                "contentType": {  
                    "id": "0x0100856E8A8C34906344ACBBBEF7C67EFA27",  
                    "name": "Item"  
                },  
                "fields@odata.context": "https://graph.microsoft.com/v1.0/$metadata#sites('raiinfy.sharepoint.com%2C37e3215c-e146-448d-9703-d885abebd366%2Ce0247e7e-4c0c-4d50-903c-06581b278f9c')/lists('9d67d52a-b7af-474d-8123-cbaaca8b90c2')/items('2')/fields/$entity",  
                "fields": {  
                    "@odata.etag": "\"c47fa1ef-09f1-4bf7-ad89-070b53498505,1\"",  
                    "Company_x0020_Details": "xyz",  
                    "Company_x0020_DetailsLookupId": "2"  
                }  
            }  
        ]  
    }  
    

    Hope this helps.

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".


  2. Dobby Spencer 26 Reputation points
    2024-01-10T22:22:51.7333333+00:00

    I had the same issue and this post helped me. One of the items in the list was being returned as "Name0LookupId" with a numerical id. In order to get the actual name, I added "Name0" in the expand list (basically taking everything before LookupId). My API call became:

    "https://graph.microsoft.com/v1.0/sites/<the name of your tenant>:/sites/<Name of your SharePoint site>:/lists/<Name of the list within the site>/items?expand=fields($select=Position,Name0)" If you are using IDs instead of the names of the tenant, site and list then it would be: "https://graph.microsoft.com/v1.0/sites/<Sharepoint site id which includes tenant and 2 comma separated ids>/lists/<Sharepoint list id>/items?expand=fields($select=Position,Name0)"

    0 comments No comments