Second Life of a Hungarian SharePoint Geek

March 24, 2015

How to Read the Values of Fields bound to Lookup Tables via REST

Filed under: JavaScript, Project Server, REST — Tags: , , — Peter Holpar @ 22:51

In my recent post I’ve illustrated how to read the values of Enterprise Custom Fields (ECT) that are bound to Lookup Tables. I suggest you to read that post first, as it can help you to better understand the relations between the custom field values and the internal names of the lookup table entries.

In this post I show you how to read such values using the REST interface. Instead of C# I use JavaScript in this example. The sample code is using the version 3.0.3-Beta4 of the LINQ for JavaScript library (version is important, as this version contains lower case function names in contrast to the former stable relases!) and the version 1.8.3 of jQuery.

Assuming that these scripts are all located in the PSRESTTest/js subfolder in the Layouts folder, we can inject them via a Script Editor Web Part using this HTML code:

<script type="text/ecmascript" src="/_layouts/15/PSRESTTest/js/jquery-1.8.3.min.js"></script>
<script type="text/ecmascript" src="/_layouts/15/PSRESTTest/js/linq.min.js"></script>
<script type="text/ecmascript" src="/_layouts/15/PSRESTTest/js/GetCustFieldREST.js"></script>

In our GetCustFieldREST.js script we define the String.format helper function first:

String.format = (function () {
    // The string containing the format items (e.g. "{0}")
    // will and always has to be the first argument.
    var theString = arguments[0];

    // start with the second argument (i = 1)
    for (var i = 1; i < arguments.length; i++) {
        // "gm" = RegEx options for Global search (more than one instance)
        // and for Multiline search
        var regEx = new RegExp("\\{" + (i – 1) + "\\}", "gm");
        theString = theString.replace(regEx, arguments[i]);
    }

    return theString;
});

Another helper function supports sending fault-tolerant REST-queries:

function sendRESTQuery(queryUrl, onSuccess, retryCount) {

    var retryWaitTime = 1000; // 1 sec.
    var retryCountMax = 3;
    // use a default value of 0 if no value for retryCount passed
    var retryCount = (retryCount != undefined) ? retryCount : 0;

    //alert($(‘#__REQUESTDIGEST’).val());
    //$.support.cors = true; // enable cross-domain query
    $.ajax({
        //beforeSend: function (request) {
        //    request.withCredentials = false;
        //},
        type: ‘GET’,
        //xhrFields: { withCredentials: false },
        contentType: ‘application/json;odata=verbose’,
        url: baseUrl + queryUrl,
        headers: {
            ‘X-RequestDigest’: $(‘#__REQUESTDIGEST’).val(),
            "Accept": "application/json; odata=verbose"
        },
        dataType: "json",
        complete: function (result) {
            var response = JSON.parse(result.responseText);
            if (response.error) {
                if (retryCount <= retryCountMax) {
                    window.setTimeout(function () { sendRESTQuery(queryUrl, onSuccess, retryCount++) }, retryWaitTime);
                }
                else {
                    alert("Error: " + response.error.code + "\n" + response.error.message.value);
                }
            }
            else {
                bgDetails = response.d;
                onSuccess(bgDetails);
            }
        }
    });
}

 

The baseUrl variable holds the root of the REST endpoint for Project Server. I assume your PWA site is provisioned to the PWA managed path.

var baseUrl = String.format("{0}//{1}/PWA/_api/ProjectServer/", window.location.protocol, window.location.host);

We call the getResCustProp method when the page is loaded:

$(document).ready(getResCustProp);

In the getResCustProp method I first query the InternalName of the custom field, as well as the InternalName and the FullValue properties of the lookup entries of the corresponding lookup table. In a second query I read the custom field value for the specified enterprise resource, and compare the value (or values) stored in the field with the InternalName property of the lookup table entries from the first query. Note, that we should escape the underscore in the InternalName property, and should use the ‘eval’ JavaScript function, as we don’t know the name of the custom field (that is the name of the property) at design time.

function getResCustProp() {
    var resourceName = "Brian Cox";
    var fieldName = "ResField";
    var fieldQueryUrl = String.format("CustomFields?$expand=LookupTable/Entries&$filter=Name eq ‘{0}’&$select=InternalName,LookupTable/Entries/InternalName,LookupTable/Entries/FullValue", fieldName);
    sendRESTQuery(fieldQueryUrl, function (fieldResponseData) {
        var field = fieldResponseData.results[0];
        var lookupTableEntries = Enumerable.from(field.LookupTable.Entries.results);
        var resourceQuery = String.format("EnterpriseResources?$filter=Name eq ‘{0}’&$select={1}", resourceName, field.InternalName)
        sendRESTQuery(resourceQuery, function (resourceResponseData) {
            var resource = resourceResponseData.results[0];
            var encodedInternalName = field.InternalName.replace(‘_’, ‘_x005f_’);
            var fieldValue = eval("resource." + encodedInternalName);
            Enumerable.from(fieldValue.results).forEach(function (fv) {
                var entry = lookupTableEntries.first(String.format(‘$="{0}"’, fv));
                alert(String.format("Value: ‘{0}’, Entry: ‘{1}’", entry.FullValue, fv));
            });
        });
    });
}

Of course, if you would like to use the code in production, you should add further data validation (if there is any resource and custom field returned by the queries, etc.) and error handling to the method.

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: