Updating Lookup Table values using JSOM

by Martin Laukkanen | Aug 23, 2013 | Apps, Development, How to, Project 2013

As follow-up to my Updating Project Server custom fields via JSOM post I figured that as I only covered part of the problem a second post was needed to cover lookup table values.

In fact working out how to update Project Server lookup table based custom fields was actually much harder than I expected, so hopefully this saves someone else out there all the time I wasted!

Overview

Updating lookup table based custom field values uses the same method as normal custom fields:

PS.DraftProject.setCustomFieldValue(FieldName, Value);

Firstly you need to follow the instructions from part 1 to get the InternalName to use for the FieldName parameter, however what is completely different when updating lookup values is the second Value parameter.

If you’ve used PSI (or even just Reporting) before you probably recall how each lookup table value has a UID which represents its value, so what we need to do for custom fields with lookup table values is actually the following;

1. Identify the internal name of the lookup table entry for the table.
2. Pass the name to setCustomFieldValue in an acceptable format.

So a quick example should look something like this:

  PS.DraftProject.setCustomFieldValue(
    'Custom_x005f_d2bb8d78a1f5e211940b00155d000a03',
    ['Entry_fea76e70a1f5e211940b00155d000a03']);

Note: For those skim-reading pay attention to the [] or read on!

Obtaining a Lookup Table Entry Name

In the previous post I wrote we used JSOM to create an array containing all of our custom field details, now for our lookup table values we need need to create a another array to store the Lookup Entry properties.

Note: Using CSOM this is actually really easy: http://msdn.microsoft.com/en-us/library/office/microsoft.projectserver.client.lookupentrycollection_di_pj14mref.aspx

However due to the asynchronous nature of JSOM it is a little more complex.

Here’s an example JavaScript method which builds on top of the previous post code:

var fieldName = 'Programme';
var lookupEntries = [];

function getCFComplete(response) {
    var cfEnumerator = customFields.getEnumerator();

    while (cfEnumerator.moveNext()) {
        var cf = cfEnumerator.get_current();

        // Is this our custom field with lookup table values?
        if (cf.get_name() === fieldName) {
            var lookupTable = cf.get_lookupTable();

            // 2nd async request - load the LookupTable data
            projContext.load(lookupTable);
            projContext.executeQueryAsync(function () {
                var ltEntries = lookupTable.get_entries();

                // 3rd async request - load the lookup table entries
                projContext.load(ltEntries);
                projContext.executeQueryAsync(function () {
                    var ltEnum = ltEntries.getEnumerator();

                    while (ltEnum.moveNext()) {
                        var ltEntry = ltEnum.get_current();

                        lookupEntries.push({
                            InternalName: ltEntry.get_internalName(),
                            Value: ltEntry.get_value(),
                            FullValue: ltEntry.get_fullValue(),
                            Description: ltEntry.get_description()
                        });
                    }

                    // Done, now do something with the values
                    var myJsonString = JSON.stringify(lookupEntries)
                }, getCFFailed);
            }, getCFFailed);
        }
    }
}

If your following that then basically what we’re doing is the following:

1. First enumerate all custom fields to find the field we want (in this example a custom field named ‘Programme’).
2. Now asynchronously load the LookupTable data for that custom field.
3. And then asynchronously load the LookupEntry data for the LookupTable and enumerate though it saving the values into our Array lookupEntries.

To give you an idea of what this looks like for the purpose of this guide in the code I have JSON’ified the result and saved it to a variable called myJsonString, which in my case looks like this:

[{"InternalName":"Entry_fda76e70a1f5e211940b00155d000a03",
"Value":"IT BAU","FullValue":"IT BAU","Description":""},
{"InternalName":"Entry_fea76e70a1f5e211940b00155d000a03",
"Value":"New Products","FullValue":"New Products","Description":""},
{"InternalName":"Entry_ffa76e70a1f5e211940b00155d000a03",
"Value":"Efficiency","FullValue":"Efficiency","Description":""}]

Setting the Custom Field Value

Now that we have our entry names we can use them in the setCustomFieldValue method. However there is one last thing to trip you up; the format. Simply passing the value as a string (e.g. ‘Entry_fea76e70a1f5e211940b00155d000a03’) will not work, and give no error. MSDN doesn’t help much either other than specifying the parameter as type “Object”.

So after a bit of trial and error I have found that you must pass the entry name value(s) in an Array, clearly this is to support multi-value custom fields, but it would be nice if MSDN mentioned this. :)

So now if we extend the updateProject() method from part 1, we get something like this;

function updateProject() {
    var projectId = "9C585CC0-3FC0-4133-9F2A-1FB96587CF0D";
    var project = projects.getById(projectId);
    var draftProject = project.checkOut();
    var fieldName = "My Custom Field";
    // Update custom field
    var cfData = $.grep(customFieldData, function (val) {
        return val.Name === fieldName;
    });

    // New part - get the lookup entry
    var leData = $.grep(lookupEntries, function (val) {
        return val.Name === "Some new value";
    });

    // If this value is in our lookup entry list then use it
    if (leData.length > 0 && cfData.length > 0) {
        draftProject.setCustomFieldValue(cfData[0].InternalName, leData[0].InternalName);
    }
    // Else handle the non-lookup table value
    else if (cfData.length > 0) {
        draftProject.setCustomFieldValue(cfData[0].InternalName, "Some new value");
    }

    //Publish the change
    var publishJob = draftProject.publish(true);
    //Monitor the job
    projContext.waitForQueueAsync(publishJob, 30, function (response) {
        if (response !== 4) {
            // handle errors
        }
    }
}

(Please note that code is written to demonstrate this concept and actually doesn’t make much sense as we have hard coded the custom field name and then are doing an if / else on the lookup entries being found!)

That’s it you should now be able to update any custom field including multi-value lookup tables using JSOM (or similarly with CSOM).

Is there a better way?

I set out originally to do this using purely JSOM, however it quickly becomes obvious that enumerating each custom field and caching all of the properties is quite tedious.

Fortunately there is a much better way using the REST interface, for example try the following URL on your project server:

http://[changethistoyourserverurl]/pwa/_api/ProjectServer/CustomFields

- <entry>
  <id>http://project2013test/PWA/_api/ProjectServer/CustomFields
  ('d2bb8d78-a1f5-e211-940b-00155d000a03')</id>
  <category  term="PS.CustomField"
    scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
  <link rel="edit" href="ProjectServer/CustomFields('d2bb8d78-a1f5-e211-940b-00155d000a03')"/>
  <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/EntityType"
    type="application/atom+xml;type=entry" title="EntityType"
    href="ProjectServer/CustomFields('d2bb8d78-a1f5-e211-940b-00155d000a03')/EntityType" />
  <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/LookupEntries"
    type="application/atom+xml;type=feed" title="LookupEntries"
    href="ProjectServer/CustomFields('d2bb8d78-a1f5-e211-940b-00155d000a03')/LookupEntries" />
  <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/LookupTable"
    type="application/atom+xml;type=entry" title="LookupTable"
    href="ProjectServer/CustomFields('d2bb8d78-a1f5-e211-940b-00155d000a03')/LookupTable" />
  <title  />
  <updated>2013-08-23T03:58:20Z</updated>
- <author>
    <name  />
  </author>
- <content type="application/xml">
- <m:properties>
      <d:AppAlternateId m:type="Edm.Guid">d2bb8d78-a1f5-e211-940b-00155d000a03
      </d:AppAlternateId>
      <d:Description  />
      <d:FieldType m:type="Edm.Int32">21</d:FieldType>
      <d:Formula  m:null="true" />
      <d:Id m:type="Edm.Guid">d2bb8d78-a1f5-e211-940b-00155d000a03</d:Id>
      <d:InternalName>Custom_d2bb8d78a1f5e211940b00155d000a03</d:InternalName>
      <d:IsEditableInVisibility m:type="Edm.Boolean">false</d:IsEditableInVisibility>
      <d:IsMultilineText m:type="Edm.Boolean">false</d:IsMultilineText>
      <d:IsRequired m:type="Edm.Boolean">false</d:IsRequired>
      <d:IsWorkflowControlled m:type="Edm.Boolean">false</d:IsWorkflowControlled>
      <d:LookupAllowMultiSelect m:type="Edm.Boolean">false</d:LookupAllowMultiSelect>
      <d:LookupDefaultValue m:type="Edm.Guid">fea76e70-a1f5-e211-940b-00155d000a03
      </d:LookupDefaultValue>
      <d:Name>Programme</d:Name>
      <d:RollsDownToAssignments m:type="Edm.Boolean">false</d:RollsDownToAssignments>
      <d:RollupType m:type="Edm.Int32">11</d:RollupType>
  </m:properties>
  </content>
</entry>

Note, that’s not the ODATA URL, it’s; /_api/ProjectServer/ and not /_api/ProjectData/!

Browsing the REST interface directly is a great way of learning about CSOM / JSOM, and for this example, you can quickly see the related link at the top titled ‘LookupEntries’, browse that and this is what you get:

http://[changethistoyourserverurl]/pwa/_api/ProjectServer/CustomFields(‘d2bb8d78-a1f5-e211-940b-00155d000a03’)/LookupEntries

- <entry>
      <id>http://project2013test/PWA/_api/ProjectServer/LookupTables
       ('fca76e70-a1f5-e211-940b-00155d000a03')/Entries('fda76e70-a1f5-e211-940b-00155d000a03')
      </id>
      ...
      <title  />
      <updated>2013-08-23T05:08:14Z</updated>
    - <author>
      <name  />
      </author>
    - <content type="application/xml">
    - <m:properties>
          <d:AppAlternateId m:type="Edm.Guid">00000000-0000-0000-0000-000000000000
          </d:AppAlternateId>
          <d:Description  />
          <d:FullValue>IT BAU</d:FullValue>
          <d:Id m:type="Edm.Guid">fda76e70-a1f5-e211-940b-00155d000a03</d:Id>
          <d:InternalName>Entry_fda76e70a1f5e211940b00155d000a03</d:InternalName>
          <d:SortIndex m:type="Edm.Decimal">1.0000000000</d:SortIndex>
          <d:HasChildren m:type="Edm.Boolean">false</d:HasChildren>
        - <d:Mask m:type="PS.LookupMask">
          <d:Length m:type="Edm.Int32">0</d:Length>
          <d:MaskType m:type="Edm.Int32">3</d:MaskType>
          <d:Separator>.</d:Separator>
          </d:Mask>
          <d:Value>IT BAU</d:Value>
      </m:properties>
      </content>
  </entry>

I’ve cut all but the first Entry from the data but as you can see it’s all there, in a nice XML format ready for use.

(I’ll leave that code to you!)