Resize large lookup tables in PDPs

If you’ve worked with large lookup tables in Project Server before you know how unwieldy they can get very quickly, in 2013 the page control you use now includes a nice search feature to help but the default four lines displayed really doesn’t show much! See this example where you can see the scroll bar on the right is already very cramped:

LT screenshot old

Modifying the List Size with JavaScript

To give some more flexibility with the size I have written the following JavaScript (jQuery actually) to increase the size of a list of specific lookup tables when opened on the PDP:

<script src="//ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js">
</script>
<script>
function resizeLKTables() {
	setTimeout(function () {
		var incHeight = 150;
		var ltId = ['Blueprint Deliverables', 'Definition Deliverables'];

		for (var i = 0; i < ltId.length; i++) {
			var ltDiv = $("input[title='" + ltId[i] + "']").parent().children("div");

			ltDiv.height(175 + incHeight);	
			ltDiv.find('div.outer-container').height(168 + incHeight);	
			ltDiv.find('div.outer-container').find('div.results-padder').height(88 + incHeight);	
			ltDiv.find('div.outer-container').find('div.general-results').height(86 + incHeight);
		}
	},50);
}

$('button').on("click", resizeLKTables);
</script>

 The result

LT screen big

Much better.

Script Usage

To use this script copy the script source above and save into notepad as something like “resizetables.js“, now on lines 6 and 7 you need to update the two variables used:

  • incHeight is the number of pixels to add to all of the specified lookup tables.
  • ltId is a comma separated list of Custom Field names to increase the size of.

Once updated, upload the script somewhere in PWA (Site Assets maybe) then edit your PDPs and add a content editor webpart to the bottom of the page which links to your uploaded resizetables.js file.

 

Enjoy!

Share and Enjoy !

Shares

Updating Lookup Table values using JSOM

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!)

Share and Enjoy !

Shares

Updating Project Server custom fields via JSOM

After a few busy weeks working on my first 100% JavaScript 2013 App (watch this space for more!!) I’ve come to realise that the MSDN documentation on JSOM and CSOM still is pretty sparse!

A couple of simple examples exist in the usual place (e.g. JSOM CreateProjects) but when you get to the details you’ll find a lot missing. For example updating Custom Fields; if you look at the MSDN page covering PS.DraftProject, the method you need (draftProject.setCustomFieldValue()) is not even listed! (UPDATE 7/08: It is covered here but with no detail; PS.Project.setCustomFieldValue)

 

JavaScript PS.DraftProject.setCustomFieldValue Method

Here’s the missing method definition that you’ll see when using PS.debug.js:

PS.DraftProject.setCustomFieldValue(FieldName, Value);

Hey wow, that simple hey? No, unfortunately the definition is a bit misleading; easy to assume that FieldName references the custom field name used else where like in the OData fields, but in fact this refers to the InternalName from the PS.CustomField object.

An example InternalName is: Custom_a1737ae3b4fce211940b00155d000a03

So first thing you need to do is get that name, it is just the field GUID prefixed with “Custom_”, but I like to do things more dynamically so I’ll use projContext.get_customFields(); to cache that information.

 

Example JavaScript update of Custom Field Value

Firstly lets get those InternalName values into an array for later use.

Cache the field details with a GetCustomFields Function

var projContext;
var customFields;
var customFieldData = [];


SP.SOD.executeOrDelayUntilScriptLoaded(GetCustomFields, "PS.js");


function GetCustomFields() {
    // Initialize the current client context and get the projects collection
    projContext = PS.ProjectContext.get_current();


    customFields = projContext.get_customFields();
    projContext.load(customFields);

    // Run the request on the server.
    projContext.executeQueryAsync(getCFComplete, getCFFailed);
}

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


    // Save the details of each CF for later
    while (cfEnumerator.moveNext()) {
        var cf = cfEnumerator.get_current();


        customFieldData.push({
            Id: cf.get_id(),
            Name: cf.get_name(),
            InternalName: cf.get_internalName()
        });
    }

    // Now update the project
    updateProject();
}

Note the last line there; updateProject() as this is all asynchronous you need to call the update only once you have the customFieldData array ready.

 

Update the Project Custom Field Function

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;
    });

    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
        }
    }
}

This simple example assumes the FieldType is text, but you get the idea, also to work with Lookup Tables you’ll need to look at the cf.get_lookupEntries() values in the getCFComplete() function but hopefully the above will get you started.

Share and Enjoy !

Shares