Second Life of a Hungarian SharePoint Geek

November 22, 2014

Managing Project Server Lookup Tables via the Managed Client Object Model

Filed under: ALM, Managed Client OM, Project Server — Tags: , , — Peter Holpar @ 08:25

In my former post I described, how we can manage Project Server event handlers via the managed client object model. In the current post I show you how to do that in case of lookup tables.

As I wrote in the previous post, we can deploy Project Server entities declaratively, via SharePoint solution packages (WSP), see that post for the details. An example for lookup tables is included below:

  1. <?xml version="1.0" encoding="utf-8" ?>
  2. <ProjectServerEntities xmlns="http://schemas.microsoft.com/sharepoint/">
  3.   <LookupTables>
  4.     <LookupTable Id="E7397277-1AB0-4096-B2DD-57029A055BA4" Name="YourLookupTable">
  5.       <SortOrder>UserDefined</SortOrder>
  6.       <Mask>
  7.         <MaskLength>0</MaskLength>
  8.         <MaskType>CHARACTERS</MaskType>
  9.         <MaskSeparator>.</MaskSeparator>
  10.       </Mask>
  11.       <LookupEntry>
  12.         <Id>8D3CFDD8-566B-4FA4-8C01-E315BCF13E74</Id>
  13.         <ParentId />
  14.         <SortIndex>86</SortIndex>
  15.         <Value>
  16.           <TextValue>Value1</TextValue>
  17.         </Value>
  18.       </LookupEntry>
  19.       <LookupEntry>
  20.         <Id>8A913280-C8D5-4E85-AE73-526EF5ABC686</Id>
  21.         <ParentId />
  22.         <SortIndex>100</SortIndex>
  23.         <Value>
  24.           <TextValue>Value2</TextValue>
  25.         </Value>
  26.       </LookupEntry>
  27.       <LookupEntry>
  28.         <Id>C89A98F0-795A-49EC-9031-BA892CE0D610</Id>
  29.         <ParentId />
  30.         <SortIndex>114</SortIndex>
  31.         <Value>
  32.           <TextValue>Value3</TextValue>
  33.         </Value>
  34.       </LookupEntry>     
  35.     </LookupTable>
  36.   </LookupTables>
  37. </ProjectServerEntities>

Alternatively, we can deploy our entities (including the lookup table) via the managed client object model. I prefer this approach to the declarative option, as I feel more control on the process of what / when deployed / retracted.

First, we can list the lookup tables (including value sets) using the code below:

  1. using (var projectContext = new ProjectContext(pwaUrl))
  2. {
  3.  
  4.     projectContext.Load(projectContext.LookupTables,
  5.         lts => lts.Include(
  6.                     lt => lt.Name,
  7.                     lt => lt.Id,
  8.                     lt => lt.FieldType,
  9.                     lt => lt.Entries.Include(
  10.                         lte => lte.FullValue,
  11.                         lte => lte.Id)));
  12.     projectContext.ExecuteQuery();
  13.  
  14.     projectContext.LookupTables.ToList().ForEach(lt =>
  15.         {
  16.             Console.WriteLine("LT: Name [{0}], Id [{1}], FieldType [{2}]", lt.Name, lt.Id, lt.FieldType);
  17.             lt.Entries.ToList().ForEach(lte => Console.WriteLine("LTE: Name [{0}], Id [{1}]", lte.FullValue, lte.Id));
  18.  
  19.         });
  20. }

The next code creates a new lookup table including three entries:

  1. var lookupTableName = "YourLookupTable";
  2. var values = new List<string> {
  3.                     "Value1",
  4.                     "Value2",
  5.                     "Value3"
  6.                 };
  7.  
  8. using (var projectContext = new ProjectContext(pwaUrl))
  9. {
  10.  
  11.     var si = 0;
  12.     var entries = values.ConvertAll(v => new LookupEntryCreationInformation
  13.     {
  14.         Id = Guid.NewGuid(),
  15.         Value = new LookupEntryValue { TextValue = v },
  16.         SortIndex = 100 + (si++) * 10
  17.     });
  18.  
  19.     LookupTableCreationInformation ltci = new LookupTableCreationInformation
  20.     {
  21.         Id = Guid.NewGuid(),
  22.         Name = lookupTableName,
  23.         SortOrder = LookupTableSortOrder.UserDefined,
  24.         Masks = new List<LookupMask> { new LookupMask { Length = 0, MaskType = LookupTableMaskSequence.CHARACTERS, Separator = "." } },
  25.         Entries = entries
  26.     };
  27.  
  28.     projectContext.LookupTables.Add(ltci);
  29.     projectContext.LookupTables.Update();
  30.     projectContext.ExecuteQuery();
  31. }

The code below removes an existing entry from a lookup table and inserts a new one into the entries:

  1. using (var projectContext = new ProjectContext(pwaUrl))
  2. {
  3.  
  4.     projectContext.Load(projectContext.LookupTables, lts => lts.Include(lt => lt.Name, lt => lt.Entries));
  5.     projectContext.ExecuteQuery();
  6.  
  7.     var lookupTable = projectContext.LookupTables.First(lt => lt.Name == "YourLookupTable");
  8.  
  9.     // this value should be removed
  10.     lookupTable.Entries.Remove(lookupTable.Entries.First(pe => pe.FullValue == "ValueToRemove"));
  11.  
  12.     // and a new value has to be created
  13.     lookupTable.Entries.Add(new LookupEntryCreationInformation
  14.     {
  15.         Id = Guid.NewGuid(),
  16.         Value = new LookupEntryValue { TextValue = "ValueToCreate" },
  17.         SortIndex = 165
  18.     });
  19.  
  20.     projectContext.LookupTables.Update();
  21.     projectContext.ExecuteQuery();
  22. }

The last sample illustrates how we can remove a set of lookup tables based on their name:

  1. var tabledNames = new List<string> { "YourLookupTable", "AnotherLookupTable" };
  2.  
  3. using (var projectContext = new ProjectContext(pwaUrl))
  4. {
  5.  
  6.     projectContext.Load(projectContext.LookupTables, lts => lts.Include(lt => lt.Name));
  7.     projectContext.ExecuteQuery();
  8.  
  9.     // query made case insensitive
  10.     tabledNames.ConvertAll(tn => projectContext.LookupTables
  11.         .FirstOrDefault(lt => lt.Name.ToLower() == tn.ToLower()))
  12.         .Where(lt => lt != null).ToList()
  13.         .ForEach(lt => lt.DeleteObject());
  14.  
  15.     projectContext.ExecuteQuery();
  16. }

In my next post I describe how to work with enterprise custom fields using the managed client object model.

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

Blog at WordPress.com.

%d bloggers like this: