I was tasked with with writing a utility for setting Enterprise Custom Fields on User Resources in Microsoft Project Online. The fields got their values from Lookup Tables. Since is was Project Online, the client side object model, CSOM, had to be used. Without previous experience of MS Project, this proved to be tricky.
Here I present a C# class that should help anyone in the same situation to get started. It is a simple console application that show various ways to interact with MS Project, and in particular how to extract available custom field values and and set on user resources. Below are some interesting snippets of the code showing how to work with Project and CSOM. The complete class can do more and is available at the bottom as well as on GitHub.
Project Online is a (very) customized SharePoint site collection. We can use same techniques when working with it as with any SharePoint Online site. As always, we need to begin with getting a context. In this case we request a ProjectContext which is just a normal SharePoint context object with additional properties:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
var username = "user@mytenant.onmicrosoft.com"; var password = "12345"; var site = "https://mytenant.sharepoint.com/sites/PROJ"; var securePassword = new SecureString(); foreach (var ch in password.ToCharArray()) { securePassword.AppendChar(ch); } context = new ProjectContext(site); context.Credentials = new SharePointOnlineCredentials(username, securePassword); |
As always in CSOM, we need to explicitly load all resources we need to access:
|
1 2 3 |
context.Load(context.Web); context.ExecuteQuery(); Console.WriteLine(" Connected to '" + context.Web.Title + "'"); |
Listing enterprise custom fields and lookup table entries is simply a matter of iterating over the respective properties:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
CustomFieldCollection customFields = context.CustomFields; LookupTableCollection lookupTables = context.LookupTables; context.Load(customFields); context.Load(lookupTables); context.ExecuteQuery(); Console.WriteLine("Custom Fields:"); foreach (CustomField cf in customFields) { Console.WriteLine(cf.Name + " {" + cf.Id + "} (" + entry.InternalName + ")"); } Console.WriteLine("Lookup Tables"); foreach (LookupTable lt in lookupTables) { Console.WriteLine(lt.Name + " {" + lt.Id + "}"); context.Load(lt.Entries); context.ExecuteQuery(); foreach (LookupEntry entry in lt.Entries) { Console.WriteLine(" " + entry.FullValue + " {" + entry.Id + "} (" + entry.InternalName + ")"); } } |
A user resources is not the same as the user object. It seems that we can’t trust that all users emails are synced to Project Online. To get around this, I load user resource using the login name instead of just the email, by simply appending the claims prefix to the email:
|
1 2 3 4 5 6 7 |
var claimsPrefix = "i:0#.f|membership|"; var loginName = claimsPrefix + userEmail; User user = context.Web.SiteUsers.GetByLoginName(loginName); EnterpriseResource userRes = context.EnterpriseResources.GetByUser(user); context.Load(userRes); context.ExecuteQuery(); Console.WriteLine("Got user resource: " + userRes.Name); |
Now we can get the custom fields from the user resource. Note that fieldValue is a string array, because it can be multi valued.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
var fieldValues = userRes.FieldValues; if (fieldValues.Count == 0) { Console.WriteLine("User has no custom fields"); } foreach (var fieldValue in fieldValues) { Console.WriteLine(fieldValue.Key + " = " + fieldValue.Value); foreach (var value in (string[])fieldValue.Value) { Console.WriteLine("\t{" + value + "}"); } } |
To set a custom field with the value of a lookup table we need to understand the internal structure:
- Each value in both the custom fields and lookup tables have a GUID, an internal name and a display name (also called simply name or full value). (The internal name is actually a concatenation of the GUID and a word like “custom” or “entry”.)
- A custom field may be bound to a lookup table. We use the internal name of the lookup table entries to set such custom fields.
- Since these are custom fields, the compiler is not aware of them. In order to set such a field we need to access it using an [indexer] together with the internal name.
- Since field values can be multi-valued, we need to set it using a string array.
- Finally, to persist the changes we need to update the EnterpriseResources collection, because this is where the resources are stored.
Now that we know all this, it is actually quite easy to set the field! Assuming that we already have the internal names of the custom field and lookup table entry we wish to set it to:
|
1 2 3 |
userRes[customFieldInternalName] = new string[] { lookupTableEntryInternalName }; context.EnterpriseResources.Update(); context.ExecuteQuery(); |
Putting all of this together, I made a class that can be used to read and write custom fields, and also shows how to list a bunch of information from Project sites and its users. Use it as a template to make your own solution. You need to modify it to use your own login information and GUID:s before you can use it. Again, the full Visual Studio solution can be downloaded from GitHub.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 |
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using Microsoft.ProjectServer.Client; using Microsoft.SharePoint.Client; using Microsoft.SharePoint.Client.UserProfiles; using System.Security; namespace UserPropsDemo { class UserProps { private ProjectContext context; // Custom Enterprise Fields private const string CustomFieldGuid = "618D383A-CCBD-11E5-9FC3-966EB9876117"; // must be a valid guid for a custom field in your MS Project Online private Dictionary<string, CustomFieldEntity> AllCustomFields = null; // Internal classes for keeping data private class LookupEntryEntity { public string Id { get; set; } public string InternalName { get; set; } public string Value { get; set; } } private class CustomFieldEntity { public string Id { get; set; } public string InternalName { get; set; } public string Name { get; set; } public Dictionary<string, LookupEntryEntity> LookupEntries { get; set; } public CustomFieldEntity() { LookupEntries = new Dictionary<string, LookupEntryEntity>(); } } // Constructor. Will connect to Project Online with mandratory credentials. public UserProps(string site, string username, string password) { try { if (site == "" || username == "" || password == "") { throw (new Exception("Must supply site, username and password!")); } Console.WriteLine("Connecting to Project Online @ " + site + "..."); var securePassword = new SecureString(); foreach (var ch in password.ToCharArray()) { securePassword.AppendChar(ch); } context = new ProjectContext(site); context.Credentials = new SharePointOnlineCredentials(username, securePassword); context.Load(context.Web); context.ExecuteQuery(); Console.WriteLine(" Connected to '" + context.Web.Title + "'"); } catch (Exception ex) { Console.WriteLine("Error: " + ex.Message); } } // Public facing function to set the custom enterprise resource fields public void SetCustomField(string userEmail, string newValue = "") { var user = GetUserResource(userEmail); if (user != null) { // Get all custom fields LoadCustomFields(); // Set the custom enterprise resource field try { Console.WriteLine("Setting custom enterprise resource property for user..."); newValue = newValue.ToLower(); var fieldInternalName = AllCustomFields[CustomFieldGuid].InternalName; var entryInternalName = AllCustomFields[CustomFieldGuid].LookupEntries[newValue].InternalName; // not that we are doing a string match here to figure out the internal name of the value in the lookup table user[fieldInternalName] = new string[] { entryInternalName }; // note that it should be a string array Console.WriteLine("\t" + AllCustomFields[CustomFieldGuid].Name + " >> " + AllCustomFields[CustomFieldGuid].LookupEntries[newValue].Value); } catch (Exception ex) { Console.WriteLine("Could not set custom field " + CustomFieldGuid + " to " + newValue + ": " + ex.Message); } // Persist changes (note than the resource object is in the EnterpriseResources collection) try { Console.WriteLine("\tSaving changes..."); context.EnterpriseResources.Update(); context.ExecuteQuery(); } catch (Exception ex) { Console.WriteLine("Error saving: " + ex.Message); } } } // Load all custom enterprise resource fields and package in an easy to access dictionary. private void LoadCustomFields() { if (AllCustomFields == null) { Console.WriteLine("Loading custom fields and lookup entries..."); // In this example I am only using one custom field. You can add more custom field guids to this list to handle multiple fields var customFields = new List<CustomField> { context.CustomFields.GetByGuid(new Guid(CustomFieldGuid)) }; foreach (var field in customFields) { context.Load(field); context.Load(field.LookupEntries); } context.ExecuteQuery(); // Package custom fields in an easy to access format AllCustomFields = new Dictionary<string, CustomFieldEntity>(); foreach (var field in customFields) { //Console.WriteLine(field.InternalName + " = " + field.Name); var cfe = new CustomFieldEntity() { Id = field.Id.ToString(), InternalName = field.InternalName, Name = field.Name }; foreach (var entry in field.LookupEntries) { //Console.WriteLine("\t" + entry.InternalName + " = " + entry.FullValue); cfe.LookupEntries.Add( entry.FullValue.ToLower(), new LookupEntryEntity() { Id = entry.Id.ToString(), InternalName = entry.InternalName, Value = entry.FullValue } ); } AllCustomFields.Add(field.Id.ToString(), cfe); } } } // Loads a user as an enterprise resouce private EnterpriseResource GetUserResource(string userEmail) { try { Console.WriteLine("Loading user resource for '" + userEmail + "'"); // Since we can't trust that email is synced to project, get user by login name instead string claimsPrefix = "i:0#.f|membership|"; var loginName = claimsPrefix + userEmail; User user = context.Web.SiteUsers.GetByLoginName(loginName); EnterpriseResource res = context.EnterpriseResources.GetByUser(user); context.Load(res); context.ExecuteQuery(); Console.WriteLine(" Got resource: " + res.Name + " {" + res.Id + "}"); return res; } catch (Exception ex) { Console.WriteLine("Error loading user: " + ex.Message); return null; } } // --------------- Below are some other examples on how to get data from Project --------------- // This shows how to get all custom fields present on a user public void ListUserResourceCustomFields(string email) { var userRes = GetUserResource(email); // iterate custom fields Console.WriteLine("Loading custom fields..."); var allFields = new Dictionary<string, Dictionary<string, string>>(); var customFields = userRes.CustomFields; context.Load(customFields); context.ExecuteQuery(); foreach (var field in customFields) { context.Load(field); context.Load(field.LookupEntries); context.ExecuteQuery(); //Console.WriteLine("\t" + field.Name + " {" + field.InternalName + "}"); var entries = new Dictionary<string, string>(); entries.Add("KEYNAME", field.Name); foreach (var entry in field.LookupEntries) { //Console.WriteLine("\t " + entry.FullValue + " {" + entry.InternalName + "}"); entries.Add(entry.InternalName, entry.FullValue); } allFields.Add(field.InternalName, entries); } Console.WriteLine("-----------------User Custom Fields-----------------"); var fieldValues = userRes.FieldValues; if (fieldValues.Count == 0) { Console.WriteLine("User has no custom fields..."); } foreach (var fieldValue in fieldValues) { Console.WriteLine(allFields[fieldValue.Key]["KEYNAME"] + " {" + fieldValue.Key + "}"); foreach (var value in (string[])fieldValue.Value) { Console.WriteLine("\t" + allFields[fieldValue.Key][value] + " {" + value + "}"); } } } // This function shows how to list facts about the MS Project site public void ListContextData() { Console.WriteLine("\nListing context data...\n"); Console.WriteLine("---------------All Projects---------------"); context.Load(context.Projects); context.ExecuteQuery(); foreach (PublishedProject proj in context.Projects) { Console.WriteLine(proj.Name); } Console.WriteLine("---------------All Site Users---------------"); UserCollection siteUsers = context.Web.SiteUsers; context.Load(siteUsers); context.ExecuteQuery(); var peopleManager = new PeopleManager(context); foreach (var user in siteUsers) { try { PersonProperties userProfile = peopleManager.GetPropertiesFor(user.LoginName); context.Load(userProfile); context.ExecuteQuery(); Console.WriteLine(userProfile.DisplayName + " (" + userProfile.Email + ")"); } catch (Exception ex) { Console.WriteLine("Error loading user: " + user.LoginName + " --> " + ex.Message); } } Console.WriteLine("---------------All Site User Resources---------------"); EnterpriseResourceCollection resources = context.EnterpriseResources; context.Load(resources); context.ExecuteQuery(); foreach (EnterpriseResource res in resources) { Console.WriteLine(res.Name + " {" + res.Id + "}"); } Console.WriteLine("---------------Custom Fields---------------"); CustomFieldCollection customFields = context.CustomFields; context.Load(customFields); context.ExecuteQuery(); foreach (CustomField cf in customFields) { Console.WriteLine(cf.Name + " {" + cf.Id + "}"); } Console.WriteLine("---------------Lookup Tables---------------"); LookupTableCollection lookupTables = context.LookupTables; context.Load(lookupTables); context.ExecuteQuery(); foreach (LookupTable lt in lookupTables) { Console.WriteLine(lt.Name + " {" + lt.Id + "}"); context.Load(lt.Entries); context.ExecuteQuery(); foreach (LookupEntry entry in lt.Entries) { Console.WriteLine(" " + entry.FullValue + " {" + entry.Id + "}"); } } } } } |
So how do you update a custom field that is not associated with a lookup table?