REST, oData, and querying for null fields… Why does my head hurt?

As we all know, oData is an open standard for formatting queries to web services. Microsoft implemented oData when they created REST services in SharePoint, but in typical Microsoft fashion, they didn’t implement it completely. What they left out is the ability to query for null or non-null values. Where one might write: ?filter=Title eq null or ?filter=Title ne null the response might be all items or no items or an error without regard to whether any items actually have null Title fields.

So what do we do? It’s CAML to the rescue! So what da heck is that? CAML is the XML-based query language we used in the bad old days of SP2007 and SP2010 to query SharePoint’s SOAP-based web services. SOAP services were all that was offered in SP2007 and while REST was introduced in SP2010, it wasn’t fully fleshed out or particularly useful until SP2013. I learned SharePoint front-end development using SPServices – a wonderful library of tools built by Marc D. Anderson – and CAML was how we wrote our queries. Since we migrated to SP2013, I’ve done almost everything in 2013’s expanded REST services, but CAML is still useful in cases where Microsoft’s implementation of oData leaves something to be desired. The CAML Query Schema can be found here.

Instead of a GET, we’re going to do a POST and the data we are posting is the CAML query. Notice in the code below that we can combine the CAML query with oData as long as they don’t conflict with each other. We are asking via CAML that all returns be sorted by Title and that no returns may have a null values in the Field1 or Field2 fields. At the same time, we are asking via oData that the GetITems service only return the top 25 results that match our query.


function restCallWithCaml() {
    var requestData = {
        "query":{"__metadata":
            {"type":"SP.CamlQuery"},
            "ViewXml":"<View>" +
                          "<Query>" +
                              "<OrderBy><FieldRef Name='Title' /></OrderBy>" +
                              "<Where>" +
                                  "<And>" +
                                      "<IsNotNull><FieldRef Name='Field1' /></IsNotNull>" +
                                      "<IsNotNull><FieldRef Name='Field2' /></IsNotNull>" +
                                  "</And>" +
                              "</Where>" +
                          "</Query>" +
                      "</View>"
        }
    };
	
    $.ajax({
        url:  _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getByTitle('MyList')/GetItems" +
              "?$top=25",
        type: "POST",
        data: JSON.stringify(requestData),
        async: false,
        contentType: "application/json;odata=verbose",
        headers: {
                    "accept": "application/json;odata=verbose",
                    "X-RequestDigest": $("#__REQUESTDIGEST").val(),
                    "Content-Type": "application/json; odata=verbose"
                 },
        success: function (data) {
            try
            {
                $.each(data.d.results, function (i, item){
                    console.log("restCallWithCaml Output: " + item.Title);
                });
            }
            catch (err) {console.log("restCallWithCaml Inner: " + JSON.stringify(err))}
        },
        error: function (err) {console.log("restCallWithCaml Outer: " + JSON.stringify(err))}
    });
}

I have also had issues when querying for certain date fields, though for the life of me, I have yet to see a pattern indicating which date fields will work and which ones won’t. In cases where my date queries don’t work, CAML comes to the rescue.

Advertisements

Lions and Tigers and ListItemEntityTypes, OH MY!

It’s time we take a walk in the high weeds!

If you’ve been using SharePoint’s REST services for any length of time, then you know that in order to save data you have to do a POST operation. Part of the object that you post is a property called type and it takes the form of "type": "SP.Data.listnameListItem" where listname is the name of your list.

SP.Data.listnameListItem is basically an object reference to the internal name of your list’s content type. When list is created, a content type is inherited (usually the Item content type) and it is given a name that is referred to as the ListItemEntityType and that uniquely identifies it and the list it belongs to. Therefore, if I create a list and name it “Requirements” the ListItemEntityType is probably going to be named RequirementsListItem and we would pass it along in the post operation as "type": "SP.Data.RequirementsListItem" so the REST service knows how to reference it.

The problem is that, much like how a field’s internal name is a mangled mess if it’s created with spaces or special characters, the same thing happens to a ListItemEntityType. There is a nice article called “Create list item by restful” at SharePoint.StackExchange that describes how to find the proper title of the content type using a call to /_api/web/lists/GetByTitle('listName')/ListItemEntityTypeFullName. You paste that into your web browser and it gives you the ListItemEntityType you need to paste into your function.

That article saved my bacon on a couple of occasions, but the second time it happened, I realized that there had to be a reusable way to automate this and so I got to work. Here’s what I came up with:


function getListItemEntityType(path, listTitle) {
    var listItemType = "";
    $.ajax({
        url: path + "/_api/lists/getbytitle('" + listTitle + "')?$select=ListItemEntityTypeFullName",
        type: "GET",
        async: false,
        contentType: "application/json;odata=verbose",
        headers: {"accept": "application/json;odata=verbose"},
        success: function (data) {
            try
            {
                listItemType = data.d.ListItemEntityTypeFullName;
            }
            catch (err) {console.log("getListItemType Inner: " + JSON.stringify(err));}
        },
        error: function (err) {console.log("getListItemType Outer: " + JSON.stringify(err));}
    });
    return listItemType;
}

function saveSomethingCool(id, myCoolData) {
    var path = _spPageContextInfo.webAbsoluteUrl;
    var listTitle = "Requirements";
    $.ajax({
        url:    path + "/_api/web/Lists/getByTitle('" + listTitle + "')/items(" + id +")/",
        type: "POST",
        data: "{ '__metadata': { 'type': '" + getListItemEntityType(path, listTitle) + "' }, 'MyCoolField' : '" + myCoolData + "' }",
        async: false,
        headers: { Accept: "application/json;odata=verbose",
            "Content-Type": "application/json;odata=verbose",
            "X-RequestDigest": $("#__REQUESTDIGEST").val(),
            "X-Http-Method": "MERGE",
            "IF-MATCH": "*"
        },
        success: function(data) {
            console.log("saveSomethingCool Inner: " + JSON.stringify(err));
        },
        error: function (err) {console.log("saveSomethingCool Outer: " + JSON.stringify(err));}
    });
}

When I want to save something cool, I call the function I created and saved in the SiteAssets library in that subsite. In this fictitious case, I call it saveSomethingCool and I pass into it the id of the item I am updating and myCoolData as the field value that I want to save. This is overly simplistic because we’re assuming that I only want to save one field of data and that the function already knows which field it is, but hey, work with me here… 🙂

Notice that instead of putting _spPageContextInfo.webAbsoluteUrl directly into the url property of the $.ajax call, we dump it into a nice short variable called path because we’re going to use it twice and it also helps to make the purpose clear to others when they try to figure out what’s going on. The same goes for the listTitle variable.

The star of this show is of course the getListItemType function and this is stored in my JSUtils.js file in the SiteAssets library in the root of the site collection and it gets loaded by the master page, so it’s always available. This function makes a call to the list and ListItemEntityTypeFullName is the list property we are concerned with. We specify only that field in the select parameter of the querystring to keep the load as light as possible.

In most cases it is preferable not to make a second trip to the server when one will serve, but this will make your life easier when someone creates a list with a convoluted name and save you from having to hardcode an unreadable mess into your function. It becomes even more valuable to you if you abstract out your list calls and you need to avoid hardcoding entirely (the weeds just got a bit taller, didn’t they?). 🙂

Using _spPageContextInfo to Determine the Current SharePoint Context in Script

Before we jump into the high weeds, I want to make everyone aware of the _spPageContextInfo object that is initialized in every SharePoint page. Sahil Malik wrote a post entitled _spPageContextInfo is your new best friend and Marc D. Anderson wrote another article referencing Sahil’s entitled Using _spPageContextInfo to Determine the Current SharePoint Context in Script.

The _spPageContextInfo object. Learn it. Live it. Love it.

Send an email with a workable link from NewForm.aspx when there is not yet an ID available

In my last post, How do I send an email in JavaScript? I described how to send an email in JavaScript, but what was left unanswered was how do we send an email with a workable link from NewForm.aspx when there is not yet an ID available?

What I have done is create a text field in our Routing and Staffing list called OpenGuid.  Hide the field using JavaScript – NOT by setting the field to Hidden in the content type because we want it to be available in the form, just not visible to the user.

During the PreSaveAction event (after we make sure that this is the NewForm so that it never changes on us later) we populate OpenGuid using the createGuid function is described in How do I show a user with presence with code?.  That OpenGuid value will be inserted into the link’s query string as the ?open= parameter.


"use strict";
function PreSaveAction() {
    if (window.location.href.toLowerCase().indexOf("newform.aspx") > -1) {
        // Make sure that the OpenGuid is assigned so that the initial email can go out
        $("[title='OpenGuid']").val(createGuid());
        
        MyORG.Link = "https://intelshare.intelink.gov/sites/yourcollection/apps/rs/Pages/RoutingStaffingHome.aspx?open=" + $("[title='OpenGuid']").val();
        MyORG.Body = "*****View in HTML*****<br /><br /><br />" + 
                    "A new Routing and Staffing item, \"" + $("[title='Subject Required Field']").val() + "\" is available for review and approval. " +
                    "<br /><br /><a href='" + MyORG.Link + "'>Click here to go to the Routing item</a>";
        if (!sendNotification(MyORG.From, MyORG.To, MyORG.CC, MyORG.BCC, MyORG.Body, MyORG.Subject, true)) {
            alert("Error Sending Mail!  Please notify the Requirement Owner(s) that this item is waiting for them.");
        }
    }
}

Now create a file to inject the code depicted below into your list view or in my case, a page.

The document.ready function looks for an ?open= parameter and if it finds one, it then calls the OpenRoutingItem function which does a quick, targeted REST call to find the ID of the item of interest.

OpenRoutingItem in turn calls the OpenItem function and passes the ID so that the item of interest can be opened in a modal dialog. Take note that there is a commented line in OpenItem that was my first attempt and for the callback I passed SP.UI.ModalDialog.RefreshPage(dialogResult) which I learned the hard way causes the popup to reopen every time it’s closed! As you can see, my second attempt directly sets window.location.href to prevent the undesirable loop.


"use strict";
$(document).ready(function() {
    var openGuid = QueryParameterByName("open");
    if (openGuid != null && openGuid != "")
        OpenRoutingItem(openGuid);
});

function OpenRoutingItem(openGuid) {
    $.ajax({
        url: "/sites/yourcollection/apps/rs/_api/web/lists/getByTitle('Routing_Staffing')/items/" +
             "?$filter=OpenGuid eq '" + openGuid + "'" +
             "&$select=ID",
        type: "GET",
        async: false,
        contentType: "application/json;odata=verbose",
        headers: {"accept": "application/json;odata=verbose"},
        success: function (data) {
            //$("#restResponse").append(JSON.stringify(data));
            try {
                $.each(data.d.results, function (i, item){
                    openItem(item.ID);
                });
            }
            catch(err) {console.log("loadItemArrays Inner: " + JSON.stringify(err))}
        },
        error: function (err) {console.log("loadItemArrays Outer: " + JSON.stringify(err))}
    });
}

function openItem(id) {
    var addressTarget = "/sites/yourcollection/apps/rs/lists/Routing_Staffing/DispForm.aspx?ID=" + id;
    var addressCallback = "https://intelshare.intelink.gov/sites/yourcollection/apps/rs/Pages/RoutingStaffingHome.aspx";
    //OpenPopUpPage(address, function(dialogResult){SP.UI.ModalDialog.RefreshPage(dialogResult)}, 1360, 1300);
    OpenPopUpPage(address, function(dialogResult){window.location.href = addressCallback}, 1360, 1300);
}

Finally, a word of caution: The PreSaveAction event fires BEFORE SharePoint’s field validations. This means that if you have a required field and the user left it empty, PreSaveAction will fire, the email will go out, field validations will fire, the save is refused, the user fixes his oversight, saves again and then another email goes out. There are two ways around this: 1) Set a global variable so that if PreSaveAction gets fired more than once, the email will check for the flag before going out again, or 2) do your own field validations in PreSaveAction before the email is ever sent.

How do I send an email in JavaScript?

Did you know that you can send an email in JavaScript and not rely on a workflow? It’s not only possible, but through the use of the PreSaveAction() function, I have all but eliminated the use of clunky and unreliable workflows.

But what about not having an id yet when saving a newform.aspx you ask? That’s for the next post. For now, here’s the actual code that resides in my JSUtils.js file.

The code comments pretty well explain everything and if you want your code to take action in case the email fails, pass responseRequired as true . All global variables are added to the namespace carved out to prevent collisions.


"use strict";
var MyORG = window.MyORG || {};
MyORG.Response = false;
MyORG.From = "do-not-reply@ugov.gov";
MyORG.To = [];
MyORG.CC = [];
MyORG.BCC = [];

function PreSaveAction() {
    if (!sendNotification(MyORG.From, MyORG.To, MyORG.CC, MyORG.BCC, MyORG.Body, MyORG.Subject, true)) {
        alert("Error Sending Mail!  Please notify the Requirement Owner(s) that this item is waiting for them.");
    }
}

///////////////////////////////////////////////////////////////////////////////////
// All addresses must be present in the User Information List.                   //
// From, To, Body and Subject are manadatory fields.                             //
// To must be an array even if only one value is included.                       //
// CC and BCC must be arrays even if empty.                                      //
// The code below attempts to correct deficiencies, but the onus is on the user. //
//                                                                               //
// If a response is required, pass responseRequired as true.  This introduces a  //
// one second delay to capture a response, so use it sparingly.  The function    //
// does set console.log to true or false for troubleshooting purposes regardless //
// of responseRequired setting.                                                  //
///////////////////////////////////////////////////////////////////////////////////
function sendNotification(from, to, cc, bcc, body, subject, responseRequired) {
    if (typeof(from) == "undefined" || from == null || from == "" ||
        typeof(to) == "undefined" || to == null || to == "" ||
        typeof(body) == "undefined" || body == null || body == "" ||
        typeof(subject) == "undefined" || subject == null || subject == "")
    {
        return false;
    }
    if (typeof(cc) == "undefined" || cc == null || cc == "") {cc = []}
    if (typeof(bcc) == "undefined" || bcc == null || bcc == "") {bcc = []}
    if (typeof(to) == "string") {to = [to]}
    if (typeof(cc) == "string") {cc = [cc]}
    if (typeof(bcc) == "string") {bcc = [bcc]}
    
    if (responseRequired) {
        // Like all async functions in SP.js, SP.SOD.executeFunc will not return a value,
        // so we will use a global variable and setTimeout to get around the problem.
        // It's butt-ugly, but it works...
        MyORG.Response = false;
        setTimeout(SP.SOD.executeFunc('sp.js', 'SP.ClientContext', sendNotificationEmail(from, to, cc, bcc, body, subject)), 1000);
        return MyORG.Response;
    } else {
        SP.SOD.executeFunc('sp.js', 'SP.ClientContext', sendNotificationEmail(from, to, cc, bcc, body, subject));
    }
}
function sendNotificationEmail(from, to, cc, bcc, body, subject) {
    var siteurl = _spPageContextInfo.siteServerRelativeUrl;
    $.ajax({
        contentType: 'application/json',
        url: "/sites/yoursitecollection/_api/SP.Utilities.Utility.SendEmail",
        type: "POST",
        async: false,
        data: JSON.stringify({
            'properties': {
                '__metadata': {
                    'type': 'SP.Utilities.EmailProperties'
                },
                'From': from,
                'To': {
                    'results': to
                },
                'CC': {
                    'results': cc
                },
                'BCC': {
                    'results': bcc
                },
                'Body': body,
                'Subject': subject,
                "AdditionalHeaders":
                {"__metadata":
                    {"type":"Collection(SP.KeyValue)"},
                    "results":
                    [ 
                        {
                            "__metadata": {"type": 'SP.KeyValue'},
                            "Key": "content-type",
                            "Value": "text/html",
                            "ValueType": "Edm.String"
                        }
                    ]
                }
            }
        }),
        headers: {
            "Accept": "application/json;odata=verbose",
            "content-type": "application/json;odata=verbose",
            "X-RequestDigest": $("#__REQUESTDIGEST").val()
        },
        success: function(data) {
            MyORG.Response = true;
            console.log('Email Sent Successfully');
        },
        error: function(err) {
            console.log('Error in sending Email: ' + JSON.stringify(err));
        }
    });
}

How do I show a user with presence in code?

For a first posting of sample code, I thought I would cover what has been a question I see asked a lot, and that is “How do I show a user with presence with code?” It’s actually quite easy!

First, I have a file that I store in the SiteAssets library in the root of our site collection called JSUtils.js and that file along with jquery.js is loaded in the master page, thus all of my most useful functions are always loaded and ready to be called.

The createPresence function is my code, but createGuid and the supporting S4 functions were lifted and I cannot remember the source.

When the enterprise version of Office is installed on a user’s machine, it installs an Active-X control in the browser and is triggered by the presence of the ms-imnSpan class. All you need to supply is a Title and Email from a user object.


"use strict";
// Creates the standard person with presence display
function createPresence(title, email) {
    var presence = "";
    try {
        presence = '<nobr>' +
                        '<span>' +
                            '<span class="ms-imnSpan">' +
                                '<a class="ms-imnlink" href="#">' +
                                    '<span class="ms-spimn-presenceWrapper ms-imnImg ms-spimn-imgSize-10x10">' +
                                        '<img id="imn{' + createGuid() + '}, type=sip" class="ms-spimn-img ms-spimn-presence-online-10x10x32" title="" src="/sites/yourcollection/_layouts/15/images/spimn.png" alt="Away" name="imnmark" />' +
                                    '</span>' +
                                    title +
                                '</a>' +
                            '</span>' +
                        '</span>' +
                  '</nobr>';
    } catch(err) {}
    return presence;
}

// Useful for many things, but primarily for creating Presence bubbles.
function createGuid() {
    return (S4() + S4() + "-" + S4() + "-4" + S4().substr(0,3) + "-" + S4() + "-" + S4() + S4() + S4()).toLowerCase();
}
function S4() {
    return (((1+Math.random())*0x10000)|0).toString(16).substring(1); 
}