sharepoint rest lookup value

Question

i'm having a minor issue. I'm using REST/JS to populate a dropdown box, which requires first getting the lookup of a column in a list.

//Product Model Cascade
document.getElementById("productDD").onchange = function() {
    var prod = this.options[document.getElementById("productDD").selectedIndex].text;
    var select = document.getElementById("productModelDD");
    $(select).empty();


    var call2 = $.ajax({
        url: _spPageContextInfo.webAbsoluteUrl + "/_api/Web/Lists(guid'bb392616-24ee-47e2-9365-f17400348373')/Items", //the list
        type: "GET",
        dataType: "json",
        headers: {
            Accept: "application/json;odata=verbose"
        }

    });
    call2.done(function(data, textStatus, jqXHR) {
        var select = document.getElementById("productModellDD");
        for (index in data.d.results) {
            var parent = data.d.results[index].AT_ARMATECproducts; //Lookup column 
            console.log("parent var: " + parent);
            if (parent == prod) {
                var op = document.createElement("option");
                op.text = data.d.results[index].Title;
                op.value = data.d.results[index].Title;
                select.appendChild(op);
            }

        }
    });
}

but the parent value is coming back as "undefined" and i've triple checked that this is the correct list. I've also tried .get_LookupValue() but it comes back as can't get it from an undefined field. So how do you get the value of a lookup field via rest?

EDIT:

I have done select/expand in the REST Query but have come up with a 400 bad request error:

    url: _spPageContextInfo.webAbsoluteUrl + "/_api/Web/Lists(guid'bb392616-24ee-47e2-9365-f17400348373')/items?$select=Title,AT_ARMATECproducts/Title&$expand=AT_ARMATECproducts/Title",

Where AT_ARMATECproducts is the lookup field in the ARMATEC Product Models List. AT_ARMATECproducts is a lookup to the ARMATEC Products list which grabs the titles.

So, it turns out the lookup list was under a different name for some reason, it the list it shows up as AT_ARMATECproducts but seems to actually be AT_Products1.

url: _spPageContextInfo.webAbsoluteUrl + "/_api/Web/Lists(guid'bb392616-24ee-47e2-9365-f17400348373')/items?$select=Title,AT_Products1/Title&$expand=AT_Products1/Title",
var parent = data.d.results[index].AT_Products1;

now returns [object object], when I tried .Titles it returned with the Titles of the List not the lookup list. And when I tried .AT_Products1.Title it returned with undefined.

Solution 1

When working with User/Lookup field values via SharePoint REST API it is important to differentiate between single-valued and multi-valued lookup fields.

Single-valued lookup fields

Assume Employee list with a single-valued lookup field Department

Then the query: /_api/web/lists/getbytitle('<list title>')/items returns lookup Id, for example:

getListItems(_spPageContextInfo.webAbsoluteUrl,'','Employees',
  function(items){
     if(items.length > 0)
        console.log(items[0].DepartmentId);   //returns LookupId
  },
  function(error){
     console.log(error.responseText);     
  });

The query:

 /_api/web/lists/getbytitle('Employees')/items?$select=Department/Title,Department/Id&$expand=Department

returns projected Department object as shown below:

getListItems(_spPageContextInfo.webAbsoluteUrl,'?$select=Department/Title,Department/Id&$expand=Department','Employees',
  function(items){
     if(items.length > 0)
        console.log(items[0].Department.Title);   
        console.log(items[0].Department.Id);   
  },
  function(error){
     console.log(error.responseText);     
  });

Multi-valued lookup fields

Assume Employee list with a multi-valued lookup field Departments

Then the query: /_api/web/lists/getbytitle('<list title>')/items returns an array of lookup ids, for example:

getListItems(_spPageContextInfo.webAbsoluteUrl,'','Employees',
  function(items){
     if(items.length > 0)
        console.log(items[0].DepartmentsId);   //returns an array [LookupId1, LookupId1 .. LookupIdN]
  },
  function(error){
     console.log(error.responseText);     
  });

The query:

/_api/web/lists/getbytitle('Employees')/items?$select=Departments/Title,Departments/Id&$expand=Departments

returns projected Departments array of objects as shown below:

getListItems(_spPageContextInfo.webAbsoluteUrl,'?$select=Departments/Title,Departments/Id&$expand=Departments','Employees',
  function(items){
     if(items.length > 0)
        if(items[0].Departments.results.length > 0) {
           console.log(items[0].Departments.results[0].Title);   
           console.log(items[0].Departments.results[0].Id);   
        }   
  },
  function(error){
     console.log(error.responseText);     
  });

---

SharePoint 2013 REST read operation function:

function getListItems(url, query, listName, success, failure) {
    $.ajax({
        url: url + "/_api/web/lists/getbytitle('" + listName + "')/items" + query,
        method: "GET",
        headers: { "Accept": "application/json; odata=verbose" },
        success: function (data) {
            success(data.d.results);
        },
        error: function (data) {
            failure(data);
        }
    });
}

Solution 2

below query finally worked for me

• Merged is a calculated field in Cats list

• Cat is a lookup field(which looks above Merged column) in the MainFlat list

          "?$select=Title,number,Cat/Merged&$expand=Cat" + 
          "&$filter=Cat/Merged eq '"+Merged+"'  "
  

https://sharepoint.stackexchange.com/a/152997/16880

Solution 3

Try

/_api/Web/Lists(guid'bb392616-24ee-47e2-9365-f17400348373')/items?$select=Title,AT_ARMATECproducts/Title&$expand=AT_ARMATECproducts"

as you endpoint

The expand query should only contain the title of the lookup column, and should not include the lookup field. Your select is fine, but the expand isn't.