Second Life of a Hungarian SharePoint Geek

June 20, 2017

Copying Flat Lookup Table Entries via the Managed Object Model

Assume you have in Project Server a flat lookup table (I mean a lookup table having a single level, without any hierarchy between the entries), and you would like to copy the entries to another (already existing!) lookup table, that may exist on the same or on another server / PWA instance. You can do the via the managed object model of Project Server, as demonstrated by the code below:

  1. private void CopyLookupTableValues(string sourcePwa, string sourceTable, string targetPwa, string targetTable)
  2. {
  3.     LookupEntryCollection ltSourceEntries = null;
  4.     using (var pcSource = new ProjectContext(sourcePwa))
  5.     {
  6.         pcSource.Load(pcSource.LookupTables, lts => lts.Where(lt => lt.Name == sourceTable).Include(lt => lt.Masks, lt => lt.Entries.Include(e => e.FullValue, e => e.Id, e => e.SortIndex)));
  7.         pcSource.ExecuteQuery();
  8.  
  9.         if (pcSource.LookupTables.Any())
  10.         {
  11.             ltSourceEntries = pcSource.LookupTables.First().Entries;
  12.         }
  13.         else
  14.         {
  15.             Console.WriteLine("Source table '{0}' not found on PWA '{1}'", sourceTable, sourcePwa);
  16.         }
  17.     }
  18.  
  19.     if (ltSourceEntries != null)
  20.     {
  21.         using (var pcTarget = new ProjectContext(targetPwa))
  22.         {
  23.             pcTarget.Load(pcTarget.LookupTables, lts => lts.Where(lt => lt.Name == targetTable).Include(lt => lt.Name));
  24.             pcTarget.ExecuteQuery();
  25.  
  26.             // target table exist
  27.             if (pcTarget.LookupTables.Any())
  28.             {
  29.                 var ltTargetEntries = pcTarget.LookupTables.First().Entries;
  30.  
  31.                 ltSourceEntries.ToList().ForEach(lte => {
  32.                     ltTargetEntries.Add(new LookupEntryCreationInformation
  33.                         {
  34.                             // instead creating a new ID, you can copy the existing ID
  35.                             // it works only if you copy the entries to another PWA instance,
  36.                             // and only if there wasn't already an entry with the same ID
  37.                             Id = Guid.NewGuid(), // lte.Id,
  38.                             Value = new LookupEntryValue { TextValue = lte.FullValue },
  39.                             SortIndex = lte.SortIndex
  40.                         });
  41.                     // if you have a lot of entries, it might be better to execute the query for each entries
  42.                     // to avoid 'The request uses too many resources' error
  43.                     // pcTarget.LookupTables.Update();
  44.                     // pcTarget.ExecuteQuery();
  45.                 });
  46.  
  47.                 pcTarget.LookupTables.Update();
  48.                 pcTarget.ExecuteQuery();
  49.             }
  50.             else
  51.             {
  52.                 Console.WriteLine("Target table '{0}' not found on PWA '{1}'", targetTable, targetPwa);
  53.             }
  54.         }
  55.     }
  56. }

The following call copies the lookup table Divisions from one PWA instance to another one:

CopyLookupTableValues("http://YourProjectServer/PWA", "Divisions", "http://AnotherProjectServer/PWA", "Divisions");

If your lookup table has not a lot of entries, you can probably copy them in a single batch, using a single call to the ExecuteQuery method. Otherwise, if thee batch size exceeds the 2 MB limit, you might have an exception like “The request uses too many resources”. In this case I suggest you to invoke the ExecuteQuery method for each entry, or create an ExecuteQueryBatch method, as described in this post.

Theoretically, you could copy the entries with their ID, but technically it is not always an option. For example, if you would like to copy the entries within the same PWA instance, you can’t have two entries sharing the same IDs. Based on my experience, if you have already an entry with the same ID, and you would like to copy it into another lookup table, although no exception is thrown, the entry won’t be copied.

The sample above works only for flat (non-hierarchical) lookup tables. You can copy hierarchical lookup tables (like RBS – resource breakdown structure) as well, but it requires a bit more coding, as I show you in the next post.

You can find further sample codes to manipulate Project Server enterprise custom fields and lookup table via the client object model in this older post.

Advertisements

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: