Creating lookup and dependency columns in CSOM


What is a dependency column?

When you create a list in SharePoint this list can be used as a lookup. So for example (a very bad example, but gives you the idea), you have a customer list and an order list. On the order list you want to lookup to your Customer List. You would pick the column you want to point to for the lookup, in this case it would probably be the Customer column. However there might be other columns from the Customer list that you want to pull through to your orders list. (Customer ID, Date Joined etc). These extra columns are the dependency columns.

The customer you pick to associate with the order, these extra columns are pulled through. On the lookup list, if any of the data changes for the customer, this data will automatically be updated on the order list.

Not all columns can be used as a lookup/dependency column, only columns that can are:

  • Single Line of text
  • Number
  • Date
  • Calculated

The Demo

I have put together a demo, using a SharePoint hosted Add-In (SharePoint App). This SharePoint Add-in acts as a provisioning page to create my columns and lists, (similar to how OfficeDevPnp samples work) I will not be explaining in this post how to create a SharePoint Add-In.

The main point of the code that adds the dependant lookups uses the method AddDependentLookup which is part of the Microsoft.SharePoint.Client namespace.

public Field AddDependentLookup(string displayName, Field primaryLookupField, string lookupField ) 

I will be explaining the relevant sections of the code. All the methods have been written so that if anything already exists, it will not recreate it. My code also using OfficeDevpnp dlls, which I have obtained through NuGet. It is not until near the end after creating the Order List will I be adding the dependency columns.


Creating the Customer List

This is standard SharePoint CSOM code, I’m checking if the list already exists, if it doesn’t then I create it.

 
List customerList = null; 
if(!ctx.Web.ListExists("Customers")) 
{ 
  customerList = ctx.Web.CreateList(ListTemplateType.GenericList, "Customers", false, false); 
}
else { 
  customerList = ctx.Web.Lists.GetByTitle("Customers"); 
} 
customerList.Update(); 
ctx.Load(customerList); 
ctx.ExecuteQueryRetry(); 

I then change the Title display value from Title to Customer Name.

var title = customerList.Fields.GetByInternalNameOrTitle("Title"); 
title.Title = "Customer Name"; 
title.Update(); 

Lastly I create 3 fields if they don’t exist, Account ID, Address, Date Joined. Once the Date Joined column is created, I’m then ensuring that the Date column is just using Date Only, instead of Date Time values.

 if(!customerList.FieldExistsById("E99BF256-BC01-4A37-B35A-B39BCC5FB82E")) 
 { 
   var accountId = new FieldCreationInformation(FieldType.Text){ 
                           AddToDefaultView = true, 
                           DisplayName = "Account ID", 
                           Id = new Guid("E99BF256-BC01-4A37-B35A-B39BCC5FB82E"), 
                           Group="Lookups", 
                           Required=true, 
                           InternalName = "AccountID" 
                      }; 
    customerList.CreateField(accountId, true); 
 }

 if(!customerList.FieldExistsById("2A8ABC2E-B7F0-4187-ADCA-7831648AFAD3")) 
 {
   var address = new FieldCreationInformation(FieldType.Note){
                           AddToDefaultView = true, 
                           DisplayName = "Address", 
                           Id = new Guid("2A8ABC2E-B7F0-4187-ADCA-7831648AFAD3"), 
                           Group = "Lookups", 
                           Required =true, 
                           InternalName = "CustomerAddress" 
                       }; 
   customerList.CreateField(address, true); 
 } 

 if(!customerList.FieldExistsById("C73123E9-85C8-4156-B280-E7783EEB119C")) 
 { 
   var dateJoined = new FieldCreationInformation(FieldType.DateTime){
                             AddToDefaultView = true, 
                             DisplayName = "Date Joined", 
                             Id= new Guid("C73123E9-85C8-4156-B280-E7783EEB119C"), 
                             Group = "Lookups", 
                             Required = true, 
                             InternalName = "DateJoined" 
                         }; 
   customerList.CreateField(dateJoined, true); 

   var dateJoinedField = ctx.CastTo<FieldDateTime>(customerList.Fields.GetById(new Guid("C73123E9-85C8-4156-B280-E7783EEB119C"))); 
   ctx.Load(dateJoinedField); 
   ctx.ExecuteQueryRetry(); 

 if(dateJoinedField.DisplayFormat == DateTimeFieldFormatType.DateTime) 
 { 
  dateJoinedField.DisplayFormat = DateTimeFieldFormatType.DateOnly; 
  dateJoinedField.UpdateAndPushChanges(true); 
  ctx.ExecuteQueryRetry(); 
 } 
}

 

As this is just a demo, I want to ensure that my Customer list already has some data in it ready to use. Therefore I’ve added a method that just adds some data to the Customer list.

private void CreateCustomerData(ClientContext ctx) 
{ 
  List customerList = ctx.Web.Lists.GetByTitle("Customers"); 
  ctx.Load(customerList); 
  ctx.ExecuteQueryRetry(); 
  if(customerList.ItemCount == 0) { 
      //Add Data. 
      Microsoft.SharePoint.Client.ListItem cust1 = customerList.AddItem(new ListItemCreationInformation()); 
      cust1["Title"] = "Customer A"; 
      cust1["AccountID"] = "A12345"; 
      cust1["CustomerAddress"] = "85 Abbott Close, \r\nLondon"; 
      cust1["DateJoined"] = new DateTime(2015, 6, 4).ToString("o"); 
      cust1.Update(); 

      Microsoft.SharePoint.Client.ListItem cust2 = customerList.AddItem(new ListItemCreationInformation()); 
      cust2["Title"] = "Customer B"; 
      cust2["AccountID"] = "B26554"; 
      cust2["CustomerAddress"] = "745 Rose Drive, \r\nLondon"; 
      cust2["DateJoined"] = new DateTime(2014, 8, 14).ToString("o"); 
      cust2.Update(); 
      
      Microsoft.SharePoint.Client.ListItem cust3 = customerList.AddItem(new ListItemCreationInformation()); 
      cust3["Title"] = "Customer C"; 
      cust3["AccountID"] = "C44575"; 
      cust3["CustomerAddress"] = "547 Cooper Way, \r\nLondon"; 
      cust3["DateJoined"] = new DateTime(2011, 1, 24).ToString("o"); 
      cust3.Update(); 

      ctx.ExecuteQueryRetry(); 
 } 
} 

 

Now we can move onto creating the Orders list. I have written this code very similar to how I started to write the Customer list, where I’m first checking if it exists first before creating it. I’m then changing the Title display value from Title to Orders. So that I have access to the Customers list columns I’m loading the list and columns.

 List orderList = null; 
 if (!ctx.Web.ListExists("Orders")) 
 { 
   orderList = ctx.Web.CreateList(ListTemplateType.GenericList, "Orders", false, false); 
 } 
 else 
 { 
   orderList = ctx.Web.Lists.GetByTitle("Orders"); 
 } 

 //Change Title 
 var title = orderList.Fields.GetByInternalNameOrTitle("Title"); 
 title.Title = "Order Item"; 
 title.Update(); 

 //Get the customer list 
 List customerList = ctx.Web.Lists.GetByTitle("Customers");
 //Load Lists, fields and views ready to add more fields and lookup fields. 
 ctx.Load(orderList); 
 ctx.Load(customerList); 
 ctx.Load(customerList.Fields); 
 ctx.ExecuteQueryRetry();

I now need to add the columns to the Order list. I’m going to start with the Cost column, as this is a standard currency column. I’m checking if the column exists first before adding it to the list.

 Field cost = null; 
 if (!orderList.FieldExistsById(new Guid("70420D11-3D40-4B53-AAF4-21B57D51C033"))) 
 { 
  FieldCreationInformation orderCost = new FieldCreationInformation(FieldType.Currency) 
  { 
    DisplayName = "Cost", 
    Id = new Guid("70420D11-3D40-4B53-AAF4-21B57D51C033"), 
    AddToDefaultView = true, 
    Group = "Lookups", 
    Required = true, 
    InternalName = "Cost" 
  }; 

 cost = orderList.CreateField(orderCost, true); 
 ctx.Load(cost); 
 } 

This is the section where the AddDependentLookup method is being used. First I need to create the lookup column from the Customers list to the Orders list. If it already exists I need to load this column as I require the Field when I call the AddDependentLookup method. Once this is created, I will check to see if the dependency column has already been added. Unfortunately when it gets added, you don’t have control over what the GUID of the column will be. Therefore you will need to check by internal name. This name will be the title of the column that you give it encoded the way SharePoint encodes spaces, punctuation etc. If the column doesn’t exist it is then added to the Order list using the AddDependentLookup method passing in the column display name, the lookupfield, and the internal name of the dependant column within the customer list.

FieldLookup customerLookupField = null; 
if (!orderList.FieldExistsById("FE9ED460-02E7-4124-A4F0-BFE5A3DDA4D0")) 
{ 
  FieldCreationInformation customerLookup = new FieldCreationInformation(FieldType.Lookup) { 
       DisplayName = "Customer", 
       Id = new Guid("FE9ED460-02E7-4124-A4F0-BFE5A3DDA4D0"), 
       Group = "Lookups",
       Required = true, 
       AddToDefaultView = true, 
       InternalName = "CustomerLookup" 
 }; 
  customerLookupField = ctx.CastTo<FieldLookup>(orderList.CreateField(customerLookup, false)); 
  customerLookupField.LookupList = customerList.Id.ToString(); 
  customerLookupField.LookupField = "Title"; 
  customerLookupField.Update(); 
  ctx.ExecuteQueryRetry(); 
} 
else 
{ 
  customerLookupField = ctx.CastTo<FieldLookup>(orderList.Fields.GetById(new Guid("FE9ED460-02E7-4124-A4F0-BFE5A3DDA4D0"))); 
  ctx.Load(customerLookupField); 
  ctx.ExecuteQueryRetry(); 
 } 
 
 //Add Dependency fields. AccountID, DateJoined 
 Field accountDependency = null; 
 if (!orderList.FieldExistsByName("Cust_x002e__x0020_Account")) 
 {
   accountDependency = orderList.Fields.AddDependentLookup("Cust. Account", customerLookupField, "AccountID"); 
   ctx.Load(accountDependency); 
 } 
  Field dateJoinedDependency = null; 
  if (!orderList.FieldExistsByName("Cust_x002e__x0020_Joined")) 
  { 
    dateJoinedDependency = orderList.Fields.AddDependentLookup("Cust. Joined", customerLookupField, "DateJoined"); 
  ctx.Load(dateJoinedDependency); 
  } 
  ctx.ExecuteQueryRetry();

After deploying my app, and loading it up, I am able to create the lookup lists.

Customer List


Creating a new Order


Orders List


Reference

https://msdn.microsoft.com/en-us/library/microsoft.sharepoint.client.fieldcollection.adddependentlookup.aspx

Link to Visual Studio Project

http://1drv.ms/1WWzyoi

Advertisements