Get Distinct Lookup Values

Here's another code example using HubKey's Client-side API for SharePoint. In this case 2 lists are created, a document library and a generic list. A lookup field (Subject) is added to the document library which looks up values in the Title column on the second list. The example also demonstrates getting distinct rows (in this case lookup values) by using the DataView.ToTable() method.

using System;

using System.Collections.Generic;

using System.Text;

// using HubKey's Remote SharePoint API instead of the native object model so we can update a remote server

using HubKey.Web.Services.SharePoint;

//using Microsoft.SharePoint;

using System.Data;

namespace DevHoleDemo

{

    class Program

    {

        static void Main(string[] args)

        {

            //always use a test server and site with this beta API

            SPWeb web = new SPSite("http://remoteserver/testsite001/").OpenWeb();

            Guid subjectsListId = web.Lists.Add("HubKey Subjects", "A temporary list.", SPListTemplateType.GenericList);

            SPList subjectsList = web.Lists[subjectsListId];

            // note that using the SPList.Items property here with the HubKey API returns all the items in the list

            // which can be expensive if the list contains many items already. See the following code example for a 

            // better way to add items to a list specific to the HubKey API:

            // http://www.hubkey.com/Documents/HubKeySPAPI/html/60759825-9963-20e4-c31e-ae93ea608330.htm

            SPListItemCollection subjects = subjectsList.Items;

            SPListItem item1 = subjects.Add();

            item1["Title"] = "SharePoint";

            item1.Update();

            SPListItem item2 = subjects.Add();

            item2["Title"] = "Microsoft CRM";

            item2.Update();

            SPListItem item3 = subjects.Add();

            item3["Title"] = "Microsoft CRM";

            item3.Update();

            Guid booksDocLibId = web.Lists.Add("HubKey Books", "A temporary document library.", SPListTemplateType.DocumentLibrary);

            SPList booksDocLib = web.Lists[booksDocLibId];

            booksDocLib.Fields.AddLookup("Subject", subjectsListId, false);

            booksDocLib.Update();

            SPFieldLookup subjectLookup = new SPFieldLookup(booksDocLib.Fields, "Subject");

            subjectLookup.LookupField = "Title";

            subjectLookup.Update();

            // build the query to get a list of subjects

            // (pretend we are starting from scratch)

            SPFieldLookup subjectLookup2 = (SPFieldLookup)booksDocLib.Fields["Subject"];

            SPList subjectsList2 = web.Lists[new Guid(subjectLookup2.LookupList)];

            SPQuery query = new SPQuery();

            query.ViewFields = "<FieldRef Name='" + subjectLookup2.LookupField + "'/>";

            // here we use a data table so that we can get distinct titles (subjects)

            // if you're not woried about distinct values you could ignore this, but it's

            // also a convenient way to query the list items (e.g. see the RowFilter below)

            DataTable subjectsTable = subjectsList2.GetItems(query).GetDataTable();

            DataView dataView = new DataView(subjectsTable);

            DataTable groupedSubjectsTable = dataView.ToTable(true, "Title");

            SPFieldLookupValue lookupValue = null;

            // here we're looping through 2 distinct title rows ('SharePoint' and 'Microsoft CRM')

            foreach (DataRow row in groupedSubjectsTable.Rows)

            {

                string title = (string)row[0];

                // get the ID of the first row that matches the title

                dataView.RowFilter = "Title = '" + title + "'";

                int firstId = (int)dataView.ToTable(true, "ID").Rows[0][0];

                // create a new lookup value (we'll use the 2nd row's lookupValue below)

                lookupValue = new SPFieldLookupValue(string.Format("{0};#{1}", firstId, title));

            }

            SPFile file = booksDocLib.RootFolder.Files.Add("test.txt", Encoding.ASCII.GetBytes("A test document."));

            // update the subject with the 2nd lookup value;

            file.Item["Subject"] = lookupValue;

            file.Item.Update();

            // clean up - pause here if you want to view the results on the server.

            subjectsList.Delete();

            booksDocLib.Delete();

        }

    }

}