Second Life of a Hungarian SharePoint Geek

March 23, 2015

How to Read the Values of Fields bound to Lookup Tables via the Client Object Model

Filed under: Managed Client OM, Project Server — Tags: , — Peter Holpar @ 05:16

Assume you have an Enterprise Custom Field (let’s call this ECFResField’) defined for project resources, that is bound to a Lookup Table.

How can we read the textural values of the field as it is assigned to you resources? After all, what makes reading such values makes any different than getting field values without lookup tables?

If we have a look at a resource with a lookup table based custom field via an OData / REST query (for example, by http://YourProjectServer/pwa/_api/ProjectServer/EnterpriseResources), you can see, that the value is stored as a reference, like ‘’Entry_4d65d905cac9e411940700505634b541‘.

image

If we access the value via the managed client OM, we get it as a string array, even if only a single value can be selected from the lookup table. The reference value in the array corresponds to the value in the InternalName property of the lookup table entry. If we know the ID of the resource (we want to read the value from), the enterprise custom field (that means we know its internal name as well) and the related lookup table, we can get the result in a single request as shown below:

  1. using (var projectContext = new ProjectContext(pwaUrl))
  2. {
  3.     var lookupTableId = "4c65d905-cac9-e411-9407-00505634b541";
  4.     var resourceId = "f9497d1d-9145-e411-9407-00505634b541";
  5.  
  6.     var res = projectContext.EnterpriseResources.GetById(resourceId);
  7.     var lt = projectContext.LookupTables.GetById(lookupTableId);
  8.     var cfInternalName = "Custom_80bd269ecbc9e411940700505634b541";
  9.     projectContext.Load(res, r => r[cfInternalName]);
  10.     projectContext.Load(lt.Entries);
  11.     projectContext.ExecuteQuery();
  12.  
  13.     var valueEntries = res[cfInternalName] as string[];
  14.     if (valueEntries != null)
  15.     {
  16.         foreach (var valueEntry in valueEntries)
  17.         {
  18.             var lookupText = lt.Entries.FirstOrDefault(e => e.InternalName == valueEntry) as LookupText;
  19.             var ltValue = (lookupText != null) ? lookupText.Value : null;
  20.             Console.WriteLine("Value: '{0}' (Entry was '{1}')", ltValue, valueEntry);
  21.         }
  22.     }
  23. }

However, if these values are unknown, and we know only the name of the resource and the field, we need to submit an extra request to get the IDs for the second step:

  1. using (var projectContext = new ProjectContext(pwaUrl))
  2. {
  3.     var resourceName = "Brian Cox";
  4.     var fieldName = "ResField";
  5.  
  6.     projectContext.Load(projectContext.EnterpriseResources, ers => ers.Include(r => r.Id, r => r.Name));
  7.     projectContext.Load(projectContext.CustomFields, cfs => cfs.Include(f => f.Name, f => f.InternalName, f => f.LookupTable.Id, f => f.LookupEntries));
  8.  
  9.     projectContext.ExecuteQuery();
  10.  
  11.     var resourceId = projectContext.EnterpriseResources.First(er => er.Name == resourceName).Id.ToString();
  12.     var cf = projectContext.CustomFields.First(f => f.Name == fieldName);
  13.     var cfInternalName = cf.InternalName;
  14.     var lookupTableId = cf.LookupTable.Id.ToString();
  15.  
  16.     var res = projectContext.EnterpriseResources.GetById(resourceId);
  17.     var lt = projectContext.LookupTables.GetById(lookupTableId);
  18.  
  19.     projectContext.Load(res, r => r[cfInternalName]);
  20.     projectContext.Load(lt.Entries);
  21.     projectContext.ExecuteQuery();
  22.  
  23.     var valueEntries = res[cfInternalName] as string[];
  24.     if (valueEntries != null)
  25.     {
  26.         foreach (var valueEntry in valueEntries)
  27.         {
  28.             var lookupText = lt.Entries.FirstOrDefault(e => e.InternalName == valueEntry) as LookupText;
  29.             var ltValue = (lookupText != null) ? lookupText.Value : null;
  30.             Console.WriteLine("Value: '{0}' (Entry was '{1}')", ltValue, valueEntry);
  31.         }
  32.     }
  33. }

Note: although this post was about a custom field defined for the resource entity, you can apply the same technique for project and task fields as well.

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: