none
SharePoint 2013 - Rest API Lookup Columns RRS feed

  • Question

  • Hello Community,

    I am using JQuery and REST API for a SharePoint View.  The query returns the data I need but unfortunately the lookup columns all return undefined (the three lookup columns are ORG, LOE, and AO Assigned. 

    Here is my code:

    <script type="text/javascript" src="//code.jquery.com/jquery-1.12.0.min.js"></script>
    <script type="text/javascript" src="/prgm/provost/CampaignPlan/SiteAssets/JQuery/jquery-dateFormat.min.js"></script>
    <script type="text/javascript">
        function getListDetails() {
            $.ajax({
                url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('Master Tasks')/items",
                method: "GET",
                headers: {
                    "Accept": "application/json; odata=verbose"
                },
                success: function(data) {
                    var listItemInfo = "<table><tbody><tr class='Header'><td>Status</td><td>Task #</td>"
    					+ "<td>Org</td><td>LOE</td><td>AO Assigned</td><td>Priority</td><td>MOP Goal</td>"
    					+ "<td>MOP Status</td><td>MOE Goal</td><td>MOE Status</td><td>Last Mod</td></tr>";
    
                    $.each(data.d.results, function(key, value) {
                        listItemInfo += "<tr><td>" + value.Status 
    					+ "</td><td>" + value.Task_x0020__x0023_ 
    					+ "</td><td>" + value.Organization
    					+ "</td><td>" + value.LOE
    					+ "</td><td>" + value.AO_x0020_Assigned
    					+ "</td><td>" + value.Priority
    					+ "</td><td>" + Math.floor((value.MoP_x0020_Goal) * 100) + "%" 
    					+ "</td><td>" + Math.floor((value.MoP_x0020_Status) * 100) + "%" 
    					+ "</td><td>" + Math.floor((value.MoE_x0020_Goal) * 100) + "%" 
    					+ "</td><td>" + Math.floor((value.MoE_x0020_Status) * 100) + "%"  
    					+ "</td><td>" + $.format.date(value.Last_x0020_Modified1, "d MMM yyyy, h:mm a") 
    					//+ "</td><td>" + value.Last_x0020_Modified1 
    					+ "</td></tr>";					
    			
                    });
    				
    				listItemInfo += "</tbody></table>"
    
                    $("#listItems").html(listItemInfo);
                },
                error: function(data) {
                    alert('An Error Has Occured');
                }
            });
        }
        _spBodyOnLoadFunctionNames.push("getListDetails");
    </script>
    

    Please provide guidance and code examples.

    Thanks!

    Tom


    Tom Molskow - Senior SharePoint Architect - Microsoft Community Contributor 2011 and 2012 Award - Linked-In - SharePoint Gypsy

    Friday, March 17, 2017 9:40 PM

Answers

  • I created a prototype, I have a list "Company" with column Title and a main list "test", I added the lookup Company in the list test, this is my code and I'm able to get the lookup value:

    <script src="https://code.jquery.com/jquery-1.12.4.min.js"></script>
    <script>
    var App = window.App || {};
    
    App.initializePage = function () {
        // This code runs when the DOM is ready and creates a peoplepicker
        $(document).ready(function () {
            App.Read();
        });
    }
    App.Read = function () {
        var listName = "test";
        var url = _spPageContextInfo.webAbsoluteUrl;
    
        getListItems(listName, url, function (data) {
            var items = data.d.results;
    
            // Add all the new items
            for (var i = 0; i < items.length; i++) {
                $('#_results').append(items[i].Id + " - " + items[i].Title + " - " + items[i].Company.Title + "<br>")
            }
        }, function (data) {
            alert("Ooops, an error occured. Please try again");
        });
        // READ operation
        // listName: The name of the list you want to get items from
        // siteurl: The url of the site that the list is in. 
        // success: The function to execute if the call is sucesfull
        // failure: The function to execute if the call fails
        function getListItems(listName, siteurl, success, failure) {
            $.ajax({
                url: siteurl + "/_api/web/lists/getbytitle('" + listName + "')/items?$select=Id,Title,Company/Title&$expand=Company/Id",
                method: "GET",
                headers: { "Accept": "application/json; odata=verbose" },
                success: function (data) {
                    success(data);
                },
                error: function (data) {
                    failure(data);
                }
            });
        }
    }
    
    ExecuteOrDelayUntilScriptLoaded(App.initializePage, "sp.js");
    </script>
    <div Id="_results"></div>


    Giuliano De Luca - MCSD SharePoint, MCSD Web Applications, MCPS, MS. www.delucagiuliano.com Twitter: @giuleon Please Mark As Answer if my post solves your problem or Vote As Helpful if the post has been helpful for you.


    Friday, March 17, 2017 11:42 PM
  • Hi,

    this behaviour it's correct, in the $expand you have to put only the lookup field and not "Priority" and "Status", add these fields in the $select.

    At the end in the $expand query string you will have LOE/Id, ORG/Id, AO/Id and into $select all the others fields $select=Priority,Status,LOE/Title,ORG/Title,AO/Title..........

    Kind regards,


    Giuliano De Luca - MCSD SharePoint, MCSD Web Applications, MCPS, MS. www.delucagiuliano.com Twitter: @giuleon Please Mark As Answer if my post solves your problem or Vote As Helpful if the post has been helpful for you.

    • Marked as answer by Tom Molskow Monday, March 20, 2017 6:16 PM
    Monday, March 20, 2017 6:04 PM

All replies

  • Hi,

    you need to specify the $expand in your Rest API, please check the following link:

    http://www.andrewconnell.com/blog/Applying-Filters-to-Lookup-Fields-with-the-SP2013-REST-API

    Kind regards,


    Giuliano De Luca - MCSD SharePoint, MCSD Web Applications, MCPS, MS. www.delucagiuliano.com Twitter: @giuleon Please Mark As Answer if my post solves your problem or Vote As Helpful if the post has been helpful for you.

    Friday, March 17, 2017 10:26 PM
  • Tom—

    You need to expand the lookup columns in your REST query

     _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('Master Tasks')/items?$select=Title,LookupField1/FieldToBeExpanded1,LookupField2/FieldToBeExpanded2&$expand=LookupField1,LookupField2
    


    ---
    Rajesh
    rjesh.com| @rjesh
    You don't need to buy me a beer, if helpful just smile, vote, and mark it as answer.

    Friday, March 17, 2017 10:26 PM
  • @Rajesh and Giuliano,

    I have changed my code but I'm still getting an error, here's my current code:

    <script type="text/javascript" src="//code.jquery.com/jquery-1.12.0.min.js"></script>
    <script type="text/javascript" src="/prgm/provost/CampaignPlan/SiteAssets/JQuery/jquery-dateFormat.min.js"></script>
    <script type="text/javascript">
        function getListDetails() {
            $.ajax({
                url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('Master Tasks')/items"
    				+ "?$select=Status,Task_x0020__x0023_,Organization/ID&$expand=Organization,"
    				/* + "LOE/Title&$expand=LOE/Title,AO_x0020_Assigned,Priority,MoP_x0020_Goal," */
    				+ "MoP_x0020_Status,MoE_x0020_Goal,MoE_x0020_Status,Last_x0020_Modified1" 
    				,
                method: "GET",
                headers: {
                    "Accept": "application/json; odata=verbose" 
                },
                success: function(data) {
                    var listItemInfo = "<table><tbody><tr class='Header'><td>Status</td><td>Task #</td>"
    					+ "<td>Org</td><td>LOE</td><td>AO Assigned</td><td>Priority</td><td>MOP Goal</td>"
    					+ "<td>MOP Status</td><td>MOE Goal</td><td>MOE Status</td><td>Last Mod</td></tr>";
    
                    $.each(data.d.results, function(key, value) {
                        listItemInfo += "<tr><td>" + value.Status 
    					+ "</td><td>" + value.Task_x0020__x0023_ 
    					+ "</td><td>" + value.Organization
    					/* + "</td><td>" + value.LOE
    					+ "</td><td>" + value.AO_x0020_Assigned
    					+ "</td><td>" + value.Priority
    					+ "</td><td>" + Math.floor((value.MoP_x0020_Goal) * 100) + "%"  */
    					+ "</td><td>" + Math.floor((value.MoP_x0020_Status) * 100) + "%" 
    					+ "</td><td>" + Math.floor((value.MoE_x0020_Goal) * 100) + "%" 
    					+ "</td><td>" + Math.floor((value.MoE_x0020_Status) * 100) + "%"  
    					+ "</td><td>" + $.format.date(value.Last_x0020_Modified1, "d MMM yyyy, h:mm a") 
    					+ "</td></tr>";					
    			
                    });
    				
    				listItemInfo += "</tbody></table>"
    
                    $("#listItems").html(listItemInfo);
                },
                error: function(data) {
                    alert('An Error Has Occured');
                }
            });
        }
        _spBodyOnLoadFunctionNames.push("getListDetails");
    </script>
    

    Thanks!

    Tom


    Tom Molskow - Senior SharePoint Architect - Microsoft Community Contributor 2011 and 2012 Award - Linked-In - SharePoint Gypsy

    Friday, March 17, 2017 10:49 PM
  • Hi,

    you have to add $expand=Organization/Id and change in the select Organization/Title if you want the Title of your lookup field, keep in mind that your data result for the lookup field will be something like that:

    data.d.results[i].Organization.Title

    then in your case should be value.Organization.Title

    Kind regards,


    Giuliano De Luca - MCSD SharePoint, MCSD Web Applications, MCPS, MS. www.delucagiuliano.com Twitter: @giuleon Please Mark As Answer if my post solves your problem or Vote As Helpful if the post has been helpful for you.






    Friday, March 17, 2017 11:05 PM
  • @Giuliano - Thanks for that reply, here is my new code, but still gets an error - but I think we're getting closer.

    <script type="text/javascript" src="//code.jquery.com/jquery-1.12.0.min.js"></script>
    <script type="text/javascript" src="/prgm/provost/CampaignPlan/SiteAssets/JQuery/jquery-dateFormat.min.js"></script>
    <script type="text/javascript">
        function getListDetails() {
            $.ajax({
                url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('Master Tasks')/items"
    				+ "?$select=Status,Task_x0020__x0023_,Organization/Title&$expand=Organization/ID,"
    				/* + "LOE/Title&$expand=LOE/Title,AO_x0020_Assigned,Priority,MoP_x0020_Goal," */
    				+ "MoP_x0020_Status,MoE_x0020_Goal,MoE_x0020_Status,Last_x0020_Modified1" 
    				,
                method: "GET",
                headers: {
                    "Accept": "application/json; odata=verbose" 
                },
                success: function(data) {
                    var listItemInfo = "<table><tbody><tr class='Header'><td>Status</td><td>Task #</td>"
    					+ "<td>Org</td><td>LOE</td><td>AO Assigned</td><td>Priority</td><td>MOP Goal</td>"
    					+ "<td>MOP Status</td><td>MOE Goal</td><td>MOE Status</td><td>Last Mod</td></tr>";
    
                    $.each(data.d.results, function(key, value) {
                        listItemInfo += "<tr><td>" + value.Status 
    					+ "</td><td>" + value.Task_x0020__x0023_ 
    					+ "</td><td>" + value.Organization.Title
    					/* + "</td><td>" + value.LOE
    					+ "</td><td>" + value.AO_x0020_Assigned
    					+ "</td><td>" + value.Priority
    					+ "</td><td>" + Math.floor((value.MoP_x0020_Goal) * 100) + "%"  */
    					+ "</td><td>" + Math.floor((value.MoP_x0020_Status) * 100) + "%" 
    					+ "</td><td>" + Math.floor((value.MoE_x0020_Goal) * 100) + "%" 
    					+ "</td><td>" + Math.floor((value.MoE_x0020_Status) * 100) + "%"  
    					+ "</td><td>" + $.format.date(value.Last_x0020_Modified1, "d MMM yyyy, h:mm a") 
    					+ "</td></tr>";					
    			
                    });
    				
    				listItemInfo += "</tbody></table>"
    
                    $("#listItems").html(listItemInfo);
                },
                error: function(data) {
                    alert('An Error Has Occured');
                }
            });
        }
        _spBodyOnLoadFunctionNames.push("getListDetails");
    </script>
    


    Tom Molskow - Senior SharePoint Architect - Microsoft Community Contributor 2011 and 2012 Award - Linked-In - SharePoint Gypsy

    Friday, March 17, 2017 11:22 PM
  • I also tried this, I still get an error:

    <script type="text/javascript" src="//code.jquery.com/jquery-1.12.0.min.js"></script>
    <script type="text/javascript" src="/prgm/provost/CampaignPlan/SiteAssets/JQuery/jquery-dateFormat.min.js"></script>
    <script type="text/javascript">
        function getListDetails() {
            $.ajax({
                url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('Master Tasks')/items"
    				+ "?$select=Status,Task_x0020__x0023_,Organization/Title&$expand=Organization/ID,"
    				/* + "LOE/Title&$expand=LOE/Title,AO_x0020_Assigned,Priority,MoP_x0020_Goal," */
    				+ "MoP_x0020_Status,MoE_x0020_Goal,MoE_x0020_Status,Last_x0020_Modified1" 
    				,
                method: "GET",
                headers: {
                    "Accept": "application/json; odata=verbose" 
                },
                success: function(data) {
                    var listItemInfo = "<table><tbody><tr class='Header'><td>Status</td><td>Task #</td>"
    					+ "<td>Org</td><td>LOE</td><td>AO Assigned</td><td>Priority</td><td>MOP Goal</td>"
    					+ "<td>MOP Status</td><td>MOE Goal</td><td>MOE Status</td><td>Last Mod</td></tr>";
    
                    $.each(data.d.results, function(key, value) {
                        listItemInfo += "<tr><td>" + value.Status 
    					+ "</td><td>" + value.Task_x0020__x0023_ 
    					+ "</td><td>" + data.d.results.Organization.Title //value.Organization.Title
    					/* + "</td><td>" + value.LOE
    					+ "</td><td>" + value.AO_x0020_Assigned
    					+ "</td><td>" + value.Priority
    					+ "</td><td>" + Math.floor((value.MoP_x0020_Goal) * 100) + "%"  */
    					+ "</td><td>" + Math.floor((value.MoP_x0020_Status) * 100) + "%" 
    					+ "</td><td>" + Math.floor((value.MoE_x0020_Goal) * 100) + "%" 
    					+ "</td><td>" + Math.floor((value.MoE_x0020_Status) * 100) + "%"  
    					+ "</td><td>" + $.format.date(value.Last_x0020_Modified1, "d MMM yyyy, h:mm a") 
    					+ "</td></tr>";					
    			
                    });
    				
    				listItemInfo += "</tbody></table>"
    
                    $("#listItems").html(listItemInfo);
                },
                error: function(data) {
                    alert('An Error Has Occured');
                }
            });
        }
        _spBodyOnLoadFunctionNames.push("getListDetails");
    </script>
    


    Tom Molskow - Senior SharePoint Architect - Microsoft Community Contributor 2011 and 2012 Award - Linked-In - SharePoint Gypsy

    Friday, March 17, 2017 11:24 PM
  • Can you go in debug on your browser and explore the value with a breakpoint you should see the value of your lookup field ?

    Giuliano De Luca - MCSD SharePoint, MCSD Web Applications, MCPS, MS. www.delucagiuliano.com Twitter: @giuleon Please Mark As Answer if my post solves your problem or Vote As Helpful if the post has been helpful for you.

    Friday, March 17, 2017 11:26 PM
  • I see this value:

    <select title="Organization" id="Organization_9d33246c-0a30-42d7-9937-6e7b46e6d338_$LookupField">


    Tom Molskow - Senior SharePoint Architect - Microsoft Community Contributor 2011 and 2012 Award - Linked-In - SharePoint Gypsy



    • Edited by Tom Molskow Friday, March 17, 2017 11:31 PM
    Friday, March 17, 2017 11:28 PM
  • In your list Master Tasks the lookup fields are filled out, that's right?

    can you try to insert "data.d.results[key].Organization.Title" ?


    Giuliano De Luca - MCSD SharePoint, MCSD Web Applications, MCPS, MS. www.delucagiuliano.com Twitter: @giuleon Please Mark As Answer if my post solves your problem or Vote As Helpful if the post has been helpful for you.

    Friday, March 17, 2017 11:35 PM
  • I created a prototype, I have a list "Company" with column Title and a main list "test", I added the lookup Company in the list test, this is my code and I'm able to get the lookup value:

    <script src="https://code.jquery.com/jquery-1.12.4.min.js"></script>
    <script>
    var App = window.App || {};
    
    App.initializePage = function () {
        // This code runs when the DOM is ready and creates a peoplepicker
        $(document).ready(function () {
            App.Read();
        });
    }
    App.Read = function () {
        var listName = "test";
        var url = _spPageContextInfo.webAbsoluteUrl;
    
        getListItems(listName, url, function (data) {
            var items = data.d.results;
    
            // Add all the new items
            for (var i = 0; i < items.length; i++) {
                $('#_results').append(items[i].Id + " - " + items[i].Title + " - " + items[i].Company.Title + "<br>")
            }
        }, function (data) {
            alert("Ooops, an error occured. Please try again");
        });
        // READ operation
        // listName: The name of the list you want to get items from
        // siteurl: The url of the site that the list is in. 
        // success: The function to execute if the call is sucesfull
        // failure: The function to execute if the call fails
        function getListItems(listName, siteurl, success, failure) {
            $.ajax({
                url: siteurl + "/_api/web/lists/getbytitle('" + listName + "')/items?$select=Id,Title,Company/Title&$expand=Company/Id",
                method: "GET",
                headers: { "Accept": "application/json; odata=verbose" },
                success: function (data) {
                    success(data);
                },
                error: function (data) {
                    failure(data);
                }
            });
        }
    }
    
    ExecuteOrDelayUntilScriptLoaded(App.initializePage, "sp.js");
    </script>
    <div Id="_results"></div>


    Giuliano De Luca - MCSD SharePoint, MCSD Web Applications, MCPS, MS. www.delucagiuliano.com Twitter: @giuleon Please Mark As Answer if my post solves your problem or Vote As Helpful if the post has been helpful for you.


    Friday, March 17, 2017 11:42 PM
  • OK, I will try that on Monday

    Tom Molskow - Senior SharePoint Architect - Microsoft Community Contributor 2011 and 2012 Award - Linked-In - SharePoint Gypsy

    Sunday, March 19, 2017 3:04 AM
  • Still no luck, is there something about how the lookup field itself should be configured?

    Tom Molskow - Senior SharePoint Architect - Microsoft Community Contributor 2011 and 2012 Award - Linked-In - SharePoint Gypsy

    Monday, March 20, 2017 4:25 PM
  • Hi,

    I tried your code as is with some modification and works well for me:

    <script type="text/javascript" src="//code.jquery.com/jquery-1.12.0.min.js"></script>
    <script type="text/javascript">
        function getListDetails() {
            $.ajax({
                url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('test')/items?$select=Title,Company/Title&$expand=Company/Id",
                method: "GET",
                headers: {
                    "Accept": "application/json; odata=verbose"
                },
                success: function(data) {
                    var listItemInfo = "<table><tbody><tr class='Header'><td>Title</td><td>Company</td></tr>";
    
                    $.each(data.d.results, function(key, value) {
                        listItemInfo += "<tr><td>" + value.Title 
    					+ "</td><td>" + value.Company.Title
    					+ "</td></tr>";					
    			
                    });
    				
    				listItemInfo += "</tbody></table>"
    
                    $("#listItems").html(listItemInfo);
                },
                error: function(data) {
                    alert('An Error Has Occured');
                }
            });
        }
        _spBodyOnLoadFunctionNames.push("getListDetails");
    </script>
    <div id="listItems"></div>

    Definitely there is something of strange in your list, this is my list configuration:

    Let me know


    Giuliano De Luca - MCSD SharePoint, MCSD Web Applications, MCPS, MS. www.delucagiuliano.com Twitter: @giuleon Please Mark As Answer if my post solves your problem or Vote As Helpful if the post has been helpful for you.

    Monday, March 20, 2017 5:09 PM
  • OK, I tried what you suggested in Postman for my actual site, so the error is not the parsing of the lookup, it's something else, still looking at it

    Tom Molskow - Senior SharePoint Architect - Microsoft Community Contributor 2011 and 2012 Award - Linked-In - SharePoint Gypsy

    Monday, March 20, 2017 5:10 PM
  • OK, so I see in Postman that this works:

    https://projects.dliflc.edu/prgm/provost/CampaignPlan/_api/web/lists/getbytitle('Master Tasks')/items?$select=Priority,Status,LOE/Title&$expand=LOE/ID


    But this does not:

    https://projects.dliflc.edu/prgm/provost/CampaignPlan/_api/web/lists/getbytitle('Master Tasks')/items?$select=LOE/Title&$expand=LOE/ID,Priority,Status

    And I get the error: The field or property 'Priority' does not exist

    Same kind of error if I put status right after LOE:

    https://projects.dliflc.edu/prgm/provost/CampaignPlan/_api/web/lists/getbytitle('Master Tasks')/items?$select=LOE/Title&$expand=LOE/ID,Status

    So the Query string works just as long as I have only one lookup field, and that lookup field is called at the end of the string.  Anything coming after the lookup throws an error.

    Any thoughts on how to resolve this?


    Tom Molskow - Senior SharePoint Architect - Microsoft Community Contributor 2011 and 2012 Award - Linked-In - SharePoint Gypsy

    Monday, March 20, 2017 5:18 PM
  • Hi,

    this behaviour it's correct, in the $expand you have to put only the lookup field and not "Priority" and "Status", add these fields in the $select.

    At the end in the $expand query string you will have LOE/Id, ORG/Id, AO/Id and into $select all the others fields $select=Priority,Status,LOE/Title,ORG/Title,AO/Title..........

    Kind regards,


    Giuliano De Luca - MCSD SharePoint, MCSD Web Applications, MCPS, MS. www.delucagiuliano.com Twitter: @giuleon Please Mark As Answer if my post solves your problem or Vote As Helpful if the post has been helpful for you.

    • Marked as answer by Tom Molskow Monday, March 20, 2017 6:16 PM
    Monday, March 20, 2017 6:04 PM