2

I have a list with lookup columns. When I get an item from the list and expand the lookup columns, I get an error if any of the lookup column values are empty.

For example if I have a list with the look up column: files, and I want to get the column: preview_link, I have a URL as follows:

 /_api/web/lists/getbytitle('My List Title')/Items('39')$select=*,files/Title,files/preview_link&$expand=files

That works fine, except if one of the items in files has an empty value for preview_link. In that case I get an error:

Cannot get value for projected field files_x005f_preview_link.

Is there any way around this short of having to put some dummy value for look up items that don't have a value for preview_link? Is this what Microsoft expects developers to do? Maybe I'm missing something simple, though.

Improve this question

2 Answers 2

Reset to default
1

Unfortunately this still an open issue in SP API: https://github.com/SharePoint/sp-dev-docs/issues/4478.

But there is an workaround, using RenderListDataAsStream. This way the call is going to return all the data, including the lookup values.

Javascript example (Thanks to Gautam Sheth answer):

Field1 and Field2 are single line of text column and Field3 is a person/group column

Call : 

$.ajax({
    url: _spPageContextInfo.webAbsoluteUrl + 
        "/_api/Web/Lists/GetByTitle('LISTNAME')/RenderListDataAsStream",
    type: "POST",   
    headers: {
        Accept: "application/json;odata=nometadata",
        "Content-Type": "application/json;odata=nometadata",
        "X-RequestDigest": $("#__REQUESTDIGEST").val()
    },
    success:function(data) {
        console.log(data.Row)
    },
    error:function(data) {
        console.log(data)
    }
})

Response : 

[object Object]
   [
      0: {
         [functions]: ,
         __proto__: { },
         Field1: "",
         Field2: "",
         Field3: [
            0: {
               [functions]: ,
               __proto__: { },
               email: "[email protected]",
               id: "1",
               title: "EXAMPLE"
            },
            1: { },
            2: { },
            length: 3
         ]},
      length: 1
   ]
Improve this answer
0

This is still a thing in SP2019. But there is an other workaround: define a calculated column in the lookup list and expand that column in your query. Use ISBLANK function to ensure that it always has a value.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.