How to expand lookup field when listing items ?

Question

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

Answer 1

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.

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".

Answer 2

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)"