Working with SharePoint Lookup Columns in Knockout.js

Working with SharePoint Lookup Columns in Knockout.js

My previous post expained the basics of working with knockout in a SharePoint environment.This post explains how to work with Lookup Columns in SharePoint with Knockout.js. In the previous post we created a Custom list called company with just a Title column and displayed it in a grid using knockout.js. When we created the Company list SharePoint Automatically added a column called CreatedBy to the list that contains the user that created the item. This is a standard SharePoint lookup column that  refers to the SharePoint user information list. This post will show how to load two related SharePoint lists (Company and UserInformationList) in your view-model and relate them with knockout ‘computed’ field.

If you browse to https://yourserverurl/KOTest/_vti_bin/listdata.svc/Company you’ll see the actual data that is returned to the browser when we call getJSON with the URL “../_vti_bin/listdata.svc/Company”

 

If your view does not resemble the screenshot above go to Tools–> Internet Options, then under the Content Tab in the section entitled Feeds and Websites, click Settings. Uncheck the Turn on FeedReading View checkbox

In the example above I have minimized all the  nodes which contain information on individual list items. You see that the header area contains some useful information like the list Title, URL, and a field called updated (which I think is the time SharePoint sent the information to the browser –if you refresh the page you’ll see this value get updated)

The Screenshot below is of a single expanded  tag from the screenshot above that represents a single item in the Company list. You’ll see that it has a link tag with an id attribute of ‘CreatedBy’ and an href attribute. If you properly format the contents of that href (by preceding it with https://yourservername/KOTest/_vti_bin/listdata.svc) it will, in fact, retrieve the information about the user that created this list item. That’s great, but more importantly for us is the under the  tag.  The reason this is more important is that in Knockout we probably want to load all of the Companies into one observableArray and all of the users into a second observableArray and then link the two using this CreatedByID.

 

It would seem safe to assume that that the CreatedByID field is the internal ID SharePoint uses for the user that created the list, but how do we figure out what list the CreatedByID points to? For that we’ll need to view the metadata of the site. Enter the following URL in your browser and you will see the metadata that SharePoint provides about the content on this site.

http://yourservername/KOTest/_vti_bin/listdata.svc/$metadata

The metadata document displayed is an xml schema definition of the contents of your site. A full description of this schema can be found here.Scroll down to the EntityContainer tag. Within the EntityContainer tag you will see an EntitySet for each list in your site.  You’ll see an EntitySet like this that refers to your Company list:

<EntitySetName=”Company” EntityType=”Microsoft.SharePoint.DataService.CompanyItem“/>

This EntitySet element tells us that the data definition for items stored in the Company s list can be found in EntityType named Microsoft.SharePoint.DataService.CompanyItem.So now scroll to the top of the page and under the  tag look for the EntityType tag with a name of Company Item (the Microsoft.SharePoint.DataService namespace is implied from the tag).

The and  elements in this EntityType element define the columns in the list, and the fields that can be requested in our getJSON call. Notice the NavigationProperty whose name is CreatedBy. It refers to a relationship called Microsoft.SharePoint.DataService.CompanyItem_CreatedBy and has a FromRole attribute with a value of CompanyItem.  Notice also that it’s immediately followed by Property Tag whose name is CreatedById. I think the fact that this property tag immediately follows the NavigationProperty indicates that the CreatedByID Property should be used to navigate the CreatedBy NavigationProperty.

 

Now, Under the EntityContainer Tag look for the AssociationSet with the name CompanyItem_CreatedBy you see that it has two  tags. The End with the Role named CompanyItem points to the EntitySet named company, which we just looked at. You can see that the other End, with the Role named CreatedBy references an EntitySet named UserInformationList

So now you can see that we can use the CreatedById in the Company List to lookup User Information in the UserInformationList.  Go ahead and find the ntitySet whose name is UserInformationList and then the corresponding Entity Type to see the fields available in the UserInformationList.The following rough outline shows how we determined how to navigate  the link.

 

Now that we’ve got an understaning of how the lookup lists work, we can start working on some Knockout code that uses them . This document does not attempt to explain how to use knockout. For that you should start with the knockout tutorial. 

Update the test.js file you created in the previous post with this:

$(document).ready(function () { LoadModel(); });

function LoadModel() {

    var vm = new ViewModel();

    ko.applyBindings(vm);

}

function ViewModel() {

    var self = this;

    self.companies = ko.observableArray([]);

    self.userInformationList = ko.observableArray([]);

 

    $.getJSON(“../_vti_bin/listdata.svc/UserInformationList”,

       function (data, textstatus, jqXHR) {

           debugger;

           for (index in data.d.results) {

               var result = data.d.results[index];

               var koItem = {};

               koItem.Name = ko.observable(result.Name);

               koItem.Id = ko.observable(result.Id);

               self.userInformationList.push(koItem);

           }

       });

    $.getJSON(“../_vti_bin/listdata.svc/Company”,

       function (data, textstatus, jqXHR) {

           for (index in data.d.results) {

               var result = data.d.results[index];

               var koItem = {};

               koItem.Title = ko.observable(result.Title);

               koItem.CreatedById = ko.observable(result.CreatedById);

               koItem.CreatedBy = ko.computed(function () { //  create a knowckout coumputed that will return the User

                   for (idx in self.userInformationList())  // loop through the entries in the userInformationList

                   {

                       if (self.userInformationList()[idx].Id() === this.CreatedById()){ // If the ID in the List matches the ID for this Item

                           return self.userInformationList()[idx];// return ity

                       }

                   }

                   return null;

               }, koItem);// the second parameter passed in to ko.computed() will be the ‘this’ value when the function is executed

               self.companies.push(koItem);

           }

       });

}

 

You see that we added another getJSON call to get the contents of the UserInformationList and load it into a new observableArray in the ViewModel called UserInformationList.

You can also see that we added two new fields to our company observableArray. The CreatedById field is a standard ko.observable that gets its value from the CreatedById you saw in the metadata. The CreatedBy field  is a ko.Computed field that loops through the entries in the UserInformationList and returns the entry whose ID is equal to the value of the CreatedByID field above.

 

 

Now update the test.html file you created in the previous post with this:

 

<div>

    <h2>companies</h2>

      <table data-bind=”template: {name: ‘documents’, foreach: companies}”>

    </table>

    <script id=’documents’ type=”text/html”>

        <tr>

            <td style=”width: 250px”>

                <span data-bind=”text:Title” />

            </td>

            <td style=”width: 250px”>

                <div data-bind=”if:CreatedBy”>

                    <span data-bind=”text:CreatedBy().Name” />

                </div>

            </td>

 

        </tr>

    </script>

 

</div>

 

You see there is a new tag that has an innerDiv with the databind syntax data-bind=”if:CreatedBy”. This tells knockout to only display the content within this div if we have a valid CreateBy object for this Company. Inside that div there is a span whose databind syntax is data-bind=”text:CreatedBy().Name. This causes knockout to call our ko.computedMethod (CreatedBy) to get the user and then display the Name value of that user.