How to Display Number of Days Since a SharePoint List Item was Last Opened

Caroline Sosebee is a Software Engineer at ThreeWill. She comes to us with 20+ years of software development experience and a broad scope of general IT support skills.

For a recent SharePoint 2013 on-premises project I needed to show the number of days elapsed between the date opened and the current date anytime the item was displayed, whether it was on a list view or when viewing or editing the list item. I also had the twist that once the item was marked as ‘closed’, the number of days should be set permanently and not changed again, so that the user will have that value available when exporting to Excel without having to recalculate.

In order to accomplish this, I had to do two things. The first was to show the running days open whenever that field is shown in the UI. I determined that the easiest way to do the this was to use Client Side Rendering (CSR) and a custom JavaScript file that I hooked in by updating the JSLink directly on my field. I found this to be pretty cool as I had no idea it could be added directly to a list column. This allowed me to add the link reference in just one place and then let SharePoint magically load the script and handle showing the field correctly via CSR, regardless of the page it is shown on. For more information on JSLink, which was introduced in SharePoint 2013, see this link (one of many).

This was also my first exposure to Client Side Rendering (CSR), so I claim no great depth of understanding. One of the best sites I found for explaining some of how it works is here and it helped me work out the pieces I needed in order to accomplish this task. By using CSR, I was able to override the default SharePoint behavior for the field and have it render my new value to the DOM.

The second part was to save the final elapsed number of days value back to the SharePoint list when the item was marked as closed. I did this via a workflow that I already had running for updating statuses and such. I just added this calculation to that workflow so that the list item was updated. I’m not going into further details on this piece as this post is primarily about how to render the running total to the UI.

Here are the pieces needed to get the elapsed days to work. I first added a Number field (ElapsedDays) to my list. I did not have to make this a calculated column or set it to read-only since the code that will be rendering the calculated value will overlay the display of the field everywhere, making it a read-only view by default. The only exception to this is in Quick Edit mode, where you will still be able to update this value if so desired. If you do NOT want the user to be able to ever enter a value or don’t have a need to save the final value back to the list, then you might want to consider making the field read-only.

I next added a JSLink reference (using PowerShell and the Microsoft.SharePoint.Client.dll) to the list column I had created. I did this by getting a reference to the list and the field, then setting the JSLink property for this field to the following:


Since my script uses jquery, I had to include a link to that as well as my custom script.

I next created my script using some code I found on the web to help me get started (see links referenced above) and loaded it into SiteAssets in a scripts folder. It has two functions – the first runs on load and adds the template overrides (using CSR) to the field and the other does the calculations and returns a value.

In the function for adding the CSR overrides, I’m linking my custom function to View, NewForm, EditForm and DisplayForm because I need it to show the calculated value everywhere the field is shown. Doing this will cause my function to be run each time one of these pages with my field on it is loaded and cause it to show the value returned by my function. One thing to note is that it will render the value in a read-only format. I think it can be manipulated post-render to make it editable, but I didn’t dig into these capabilities as this was doing exactly what I needed.

In my CalcDays function, I’m calculating the elapsed number of days (not including the first day) and returning that value. I’m also handling the situation where there is an actual value already saved in this field. When this is the case, I simply return that value instead of the calculated value. There’s also a safety check just in case the item has been closed but the ElapsedDays field has not been updated yet. In that case, it will still calculate the value but will use closed date instead of current date for the calculation.

As you can see, this is a pretty basic script but does exactly what I needed. It could easily be modified to handle even more complex situations very easily.

(function () {

// Create an object that will have the context information for the field we want to render differently

var fieldContext = {};

fieldContext.Templates = {};

fieldContext.Templates.Fields = {


"ElapsedDays": {

"View": CalcDays,

"NewForm": CalcDays,

"EditForm": CalcDays,

"DisplayForm": CalcDays



// Now register the changes to the field



// This function will calculate and return the number of days

function CalcDays(ctx) {

// If the item is already closed (duration has been saved to the list), just return this saved value

if (ctx.CurrentItem.ElapsedDays > 0) {

return ctx.CurrentItem.ElapsedDays;


// If we have no open date we can't calculate so return 0

if (ctx.CurrentItem.DateOpened == "") {

return 0;


// If we get here, the final elapsed days has not been saved yet, so we need

// to calculate it for display

var openDate = new Date(ctx.CurrentItem.DateOpened);

// Default closed date to current date

var endDate = new Date(new Date().format('yyyy-MM-dd'));

// If we find a closed date use this instead of current

if (ctx.CurrentItem.DateClosed) {

endDate = new Date(ctx.CurrentItem.DateClosed);


// Now calculate the number of days, rounding to no decimals.

// If you need to include both beginning and ending date, add 1 to the below output

var dateDiff = Math.round(((endDate - openDate) / 1000 / 60 / 60 / 24));

return dateDiff;


So in summary, by using JSLinks, Client Side Rendering and some custom JavaScript, you can show the actual number of elapsed days since something was opened (or another date) in SharePoint. Pretty cool, huh?

read more
Caroline SosebeeHow to Display Number of Days Since a SharePoint List Item was Last Opened

Automate Testing SharePoint Lists with PnP, Angular 2.0 and TypeScript

Bo is a Principal Consultant for ThreeWill. He has 18 years of full lifecycle software development experience.

One thing I’ve never felt like I’ve done very well on SharePoint List centric projects is plan for or support automated testing scenarios.  You know the kind of project where you build an awesome UI and use one or more SharePoint lists as your database. Often these projects have been so quick moving that there just hasn’t been time or budget for those sorts bells and whistles.

Recently I decided to make time and see if I could come up with a solution.  My goal was fairly simple; I wanted to be able to run my Angular 2.0 app locally or as part of an automated test scenario without SharePoint in the mix at all. Then be able to deploy it to SharePoint and have it run there with no code or configuration changes.

One additional consideration I had was that I am using PnP JS Core to wrap the rest API calls rather than going with straight http.  I’m a huge fan of this library for simplifying common SharePoint operations. I also love the PnP PowerShell Commands. Both are basically in my tool belt at all times.  If you are not using PnP just search for Angular 2 MockBackEnd Examples and you should easily find examples where you can replace Angular Http with one of your own that allows you to mock data.  Although I didn’t follow the example all the way through the post Look Ma, No Server: Developing Apps with Angular 2 Mockbackend from Vildan Softic looked promising.

When using PnP JS Core, the implementation offers a way to have your own Custom HttpClientImpl that you can simply plug in when you setup the PnP configuration specifics for your project.  In the main AppModule of my angular project I typically have a call within the constructor to perform this setup.  In the code below I’m setting the fetchClientFactory to my own custom fetchclient that really is only going to play the role of traffic cop for my scenario.

export class AppModule { 

  constructor() {

  setupPnp(): void {
    let url = getBaseHref();
        baseUrl: url,
        headers: { "Accept": "application/json;odata=verbose" },
        fetchClientFactory: () => {
          return new CustomFetchClient();

The customfetchclient is the main reason no configuration is needed to determine if I am running locally or within SharePoint. It simply looks at the Url and if it’s running on localhost it will redirect to the operations workhorse, the MockResponse class, otherwise I simply uses PnP’s fetch client. A key thing to mention here is that unlike the examples provided on Custom HttpClientImpl I decided to inherit their fetchclient rather than fully implement my own based on their interface. I honestly didn’t want to have to redo all that work they accomplish behind the super.fetch call in my code below.

import { FetchOptions, FetchClient } from 'sp-pnp-js';
import { MockResponse } from './mocks/mockresponse';

export class CustomFetchClient extends FetchClient {
    fetch(url: string, options: FetchOptions): Promise {
        if (url.startsWith("http://localhost")) {
            return new MockResponse().fetch(url, options);
        else {
            return super.fetch(url, options);

MockResponse is where things get messy and in my experiment it’s still not as clean, configurable and magical as I would like it to be. This is version .1 of my code so this is to be expected I guess. However, it works and will just require some “upkeep” for our project to plug in additional mock data and other mocking constructs as needed along the way. Below is the only public method of the class and just deterimes which private method needs to handle the request, do the work and return a mocked response. You’ll notice things such as looking for a post where the url ends with api/contextinfo which is part of the “dance” when posting to SharePoint. I decided it deserved its own method to keep the normal post method more clean and consistent.

fetch(url: string, options: FetchOptions): Promise<Response> {
    let response;

    if (options.method.toUpperCase() === "GET") {
        response = this.Get(url);
    else if (options.method.toUpperCase() === "POST" && url.toLowerCase().endsWith("_api/contextinfo")) {
        response = this.PostContextInfo();
    else {
        response = this.Post(url, options);

    return Promise.resolve(response);

Since I thought the simplest type of request to mock is was going to be an HTTP GET lets start there. Note I later learned POSTing an item is probably simpler to mock. Before I show the mock code I just wanted to step back and show the service code that is utilizing PnP to issue that get request. The code below is part of a DocumentService injected into a UI component. The method below, getAllDocs, looks EXACTLY as it would have if I had no mock data. It gets a list of items and then loops those items and maps them to internal data models used to bind to the UI. No mock anything here.

async getAllDocs(buildParent: boolean): Promise<BaseDocument[]> {

    let items = await pnp.sp.web.lists.getByTitle(this.listTitle).items
        .catch(e => { super.logPnpError(e); return false });

    if (this.documents == null) {
        this.documents = [];
        for (let item of items) {
    return this.documents;

When the above code calls pnp.sp.web.lists.getByTitle(“Documents”).items.get() it will go through the customfetchclient and while running locally realize it needs to pass responsibly to MockResponse and ultimately end up in the Get method below. As I’m building this method up, you’ll see that I inspect the url and when a call is going against the Documents library I grab a local copy of an array of items that has been mocked to look like data from that SharePoint list. I then have methods that may “reshape” that array based on any select parameters. For example if $select=ID came in, then that would be the only item property I return for each item in the array. The applyFilter will also attempt to filter the array values based on query params like $filter=Value eq ‘something’ and so on. The applyFilter is where much work still remains for me but I’ve included the start of the pattern in the code below.

private Get(urlString: string): Response {

    let url = parse(urlString, true, true);
    let body: string;
    let items: any[] = [];

    if (urlString.indexOf("getByTitle('Releases')") != -1) {
        items = ReleaseList.Items;
    else if (urlString.indexOf("getByTitle('Projects')") != -1) {
        items = ProjectList.Items;
    else if (urlString.indexOf("getByTitle('Documents')") != -1) {
        items = DocumentList.Items;

    // apply select, filter, top, etc...
    items = this.applyFilter(items, url.query.$filter);
    items = this.applySelect(items, url.query.$select);

    if (url.pathname.endsWith("/items")) {
        body = JSON.stringify(items);
    else if (url.pathname.endsWith(")")) {
        let index = url.pathname.lastIndexOf("(");
        let id = url.pathname.slice(index + 1, url.pathname.length - 1);

        let item = items.find(i => { return i.ID === +id });
        body = JSON.stringify(item);
    else {
        //not sure what might hit here yet

    return new Response(body, { status: 200 });

private applySelect(items: any[], select: string): any[] {
    let newItems: any[] = [];
    if (select != null && select.length > 0) {
        let keys = select.split(",");
        for (let item of items) {
            let newItem = {};
            for (let key of keys) {
                newItem[key] = item[key];
        return newItems;
    else {
        return items;

private applyFilter(items: any[], filter: string): any[] {
    let newItems: any[] = [];
    if (filter != null && filter.length > 0) {
        //assumes this is always 3 parts currently, will need refactoring for startswith etc.
        // (e.g. ZipLookup/Id eq "1")
        let filterParts = filter.split(" ");  
        let simpleFilter = this.createSimpleFilter(filterParts);

        for (let item of items) {
            let propertyValue = item;
            for(let property of{
                propertyValue = propertyValue[property];
            let filterValue: any;
            if(typeof(propertyValue) === "number") {
                filterValue = +simpleFilter.value;
            else {
                filterValue = simpleFilter.value;

            let match: boolean = false;
            switch(simpleFilter.operator.toLowerCase()) {
                case "eq":
                    if(propertyValue === filterValue) {
                        match = true;
            if(match) {
        return newItems;
    else {
        return items;

private createSimpleFilter(filterParts: string[]) : SimpleFilter {
    let simpleFilter = new SimpleFilter();
    if(filterParts.length == 3) {
        simpleFilter.operator = filterParts[1];
        simpleFilter.value = filterParts[2];

        if(filterParts[0].indexOf("/") != -1) {
            let lookupParts = filterParts[0].split("/");
            for(let part of lookupParts){
        else {

    return simpleFilter;

There really isn’t much to the arrays that mock the list items. The are just simple classes I create for each list with a static Items property and then a collection of items. Note that I do use imports to other arrays as a way for me to bind lookups.

export class DocumentList {
    static Items: any[] = [
            ID: 1,
            ProjectLookup: ProjectList.Items[0],
            DocumentType: "BR",
            Author: "George",
            Version: 1.0,
            StatusLookup: StatusList.Items[2],
            CreatedDate: "6/10/2017",
            UpdatedDate: "6/11/2017",
            BaselinedDate: "6/12/2017"

If you’ve made it this far in the post I’m sure you are wondering about HTTP POST. In some respects, this is easier than the GET because a post is almost always for a single item and there aren’t as many other parameters to account for (filter, select, top, etc…). In this case we just need to take a body and either create an item or update an existing item. Again for completeness my ReleaseService might have a save method like below that is called from the UI component.

async save(release: Release) {
    super.logInfo(`save: [id:${}] `);

    if ( != null && !== 0) {
        await pnp.sp.web.lists.getByTitle(this.listTitle).items
    else {
        await pnp.sp.web.lists.getByTitle(this.listTitle).items

    this.releases = null;

In the MockResponse class the internal Post method handler will first do similar work as the GET just to figure out which list the user is going against and get the appropriate list of mock items to work with. Depending on the url format we can determine if this is new /items or an update /items(1) and add to the array of items or update an existing one.

    let url = parse(urlString, true, true);
    let body: string;
    let items: any[] = [];

    if (urlString.indexOf("getByTitle('Releases')") != -1) {
        items = ReleaseList.Items;
    else if (urlString.indexOf("getByTitle('Projects')") != -1) {
        items = ProjectList.Items;
    else if (urlString.indexOf("getByTitle('Documents')") != -1) {
        items = DocumentList.Items;
    if (url.pathname.endsWith("/items")) {
        let item: any = {};
        item["ID"] = items.length + 1;
        let requestBody = JSON.parse(options.body);
            (e) => item[e] = requestBody[e]


        let result: ItemAddResult = {
            item: item,
            data: {}

        body = JSON.stringify(result);
    else if (url.pathname.endsWith(")")) {
        let index = url.pathname.lastIndexOf("(");
        let id = url.pathname.slice(index + 1, url.pathname.length - 1);

        let item = items.find(i => { return i.ID === +id });
        let requestBody = JSON.parse(options.body);
            (e) => item[e] = requestBody[e]

        let result: ItemUpdateResult = {
            item: item,
            data: { "odata.etag": "" }

        body = JSON.stringify(result);
    else {
        //not sure what might hit here yet

    return new Response(body, { status: 200 });

I hope the code, patterns and approach I’ve began helps someone else as they explore options for mocking SharePoint list data. I’m still a few weeks out from actually starting the project where I plan to use this approach and I’m sure when things GET REAL I will have modifications and refactoring driven by more and more data and GET/POST scenarios. I’ll try to update this post with those lessons and look forward to any thoughts and recommendations others have based on this or other approaches for mocking SharePoint list data.

read more
Bo GeorgeAutomate Testing SharePoint Lists with PnP, Angular 2.0 and TypeScript

Free ThreeWill Webinars for 2017

Danny serves as Vice President of Marketing at ThreeWill. His primary responsibilities are to make sure that we are building partnerships with the right clients and getting out the message about how we can help clients.

We’re excited to announce our Webinar Schedule for 2017 (all times in EST)…

  1. Moving from SharePoint Online Dedicated to Multi-Tenant – 1/26/17 @ 1:00pm – Listen Now
  2. Migrating from Jive to Office 365 – 2/23/17 @ 1:00pm – Listen Now
  3. Complex SharePoint Online/2016 Migrations – 3/30/17 @ 1:00pm – Listen Now
  4. Creating Award-Winning SharePoint Intranets – 4/27/17 @ 1:00pm – Watch Now
  5. Find Anything in SharePoint with Amazon-Like Faceted Search – 6/29/17 @ 1:00pm – Watch Now
  6. Budgeting for 2018 SharePoint Initiatives – 10/26/17 @ 1:00pm – Register
  7. Successful SharePoint Farm Assessments – 11/30/17 @ 1:00pm – Register

The schedule is subject to change (especially if presenters get overloaded on projects). Let us know in the comments if you have other topics that you would like us to cover.

Sign up below to get notified about upcoming events or follow us on twitter.

SharePoint is a web application platform in the Microsoft Office server suite. Launched in 2001, SharePoint combines various functions which are traditionally separate applications: intranet, extranet, content management, document management, personal cloud, enterprise social networking, enterprise search, business intelligence, workflow management, web content management, and an enterprise application store. SharePoint servers have traditionally been deployed for internal use in mid-size businesses and large departments alongside Microsoft Exchange, Skype for Business, and Office Web Apps; but Microsoft’s ‘Office 365’ software as a service offering (which includes a version of SharePoint) has led to increased usage of SharePoint in smaller organizations.

While Office 365 provides SharePoint as a service, installing SharePoint on premises typically requires multiple virtual machines, at least two separate physical servers, and is a somewhat significant installation and configuration effort. The software is based on an n-tier service oriented architecture. Enterprise application software (for example, email servers, ERP, BI and CRM products) often either requires or integrates with elements of SharePoint. As an application platform, SharePoint provides central management, governance, and security controls. The SharePoint platform manages Internet Information Services (IIS) via form-based management tooling.

Since the release of SharePoint 2013, Microsoft’s primary channel for distribution of SharePoint has been Office 365, where the product is continuously being upgraded. New versions are released every few years, and represent a supported snapshot of the cloud software. Microsoft currently has three tiers of pricing for SharePoint 2013, including a free version (whose future is currently uncertain). SharePoint 2013 is also resold through a cloud model by many third-party vendors. The next on-premises release is SharePoint 2016, expected to have increased hybrid cloud integration.

Office 365 is the brand name used by Microsoft for a group of software plus services subscriptions that provides productivity software and related services to its subscribers. For consumers, the service allows the use of Microsoft Office apps on Windows and OS X, provides storage space on Microsoft’s cloud storage service OneDrive, and grants 60 Skype minutes per month. For business and enterprise users, Office 365 offers plans including e-mail and social networking services through hosted versions of Exchange Server, Skype for Business Server, SharePoint and Office Online, integration with Yammer, as well as access to the Office software.

After a beta test that began in October 2010, Office 365 was launched on June 28, 2011, as a successor to Microsoft Business Productivity Online Suite (MSBPOS), originally aimed at corporate users. With the release of Microsoft Office 2013, Office 365 was expanded to include new plans aimed at different types of businesses, along with new plans aimed at general consumers wanting to use the Office desktop software on a subscription basis—with an emphasis on the rolling release model.

read more
Danny RyanFree ThreeWill Webinars for 2017

CSOM GetItemById Results in “The property or field has not been initialized”

Kirk Liemohn is a Principal Software Engineer at ThreeWill. He has over 20 years of software development experience with most of that time spent in software consulting.

I was having a problem where I was trying to migrate list content from SharePoint 2010 to SharePoint 2013 but a couple of fields were not making it over.  I was using a migration tool that I like to use, but had to resort to connecting to both SharePoint 2010 and SharePoint 2013 using CSOM due to environment restrictions.  I know there are limitations with CSOM and likely more with SharePoint 2010 than there are with SharePoint 2013, but I was still surprised the tool couldn’t get values for these two fields.

I thought I could get around this if I wrote the code myself so I gave it a shot.  I wrote some PowerShell to first query for the list item IDs from the source list.  The code uses a CAML query and only has the ID field in the ViewFields for the query.  I then iterate across the results of the query.  For each result I get the entire source item by ID as follows:

$sourceItem = $sourceList.GetItemById($sourceItemFromCAMLQuery.ID)

GetItemById is the key method above as I thought it would do a good job of getting all fields for that list item.  However, for the two fields that gave me problems with the migration tool, it failed with my code.  The line of code that gave me an error was:

 $sourceValue = $sourceItem[$fieldName]

The exception thrown was of type PropertyOrFieldNotInitializedException.  The exception message was:

The property or field has not been initialized. It has not been requested or the request has not been executed. It may need to be explicitly requested.

I immediately thought this might be a row ordinal issue based on problems it has caused me in the past.  In retrospect I discovered that according to the schema XML for the fields, they are still in row ordinal 0 (which is good) so that didn’t explain my issue, but my fix was the same regardless.

To fix this problem I simply added these two problem fields to the ViewFields for my initial CAML query and subsequently get the values from the CAML query instead of the list item obtained using GetItemById.  It added a little complexity since I had to pay attention to which field values I obtained from the CAML query and which I obtained from GetItemById, but it solved my problem!  I hope it helps others as well.

read more
Kirk LiemohnCSOM GetItemById Results in “The property or field has not been initialized”

Reset a SharePoint 2013 Service Account Password

Caroline Sosebee is a Software Engineer at ThreeWill. She comes to us with 20+ years of software development experience and a broad scope of general IT support skills.

Most companies these days have fairly strict password reset rules which can wreak havoc on a smoothly running SharePoint farm, if not planned for properly. Here are the steps to take in order to get the password for a SharePoint service account reset cleanly.

  1. Active Directory – Change the password for the appropriate service account.
  2. IIS (Internet Information Services) – Update the password for each Application Pool that is using the account. The password is buried within Advanced Settings, under the ‘Identity’ field. Click on the ellipsis and then the ‘Set …’ button to enter the new password.
  3. Services – Open Services on the appropriate server(s) and find all that use the specified service account (you can sort by the ‘Log On As’ column to find them all). Update each to use the new password (Properties / Log On tab) and then restart the service to make sure the password change is active.
  4. Scheduled Tasks – Open Task Scheduler on the appropriate server(s) and check for any jobs that use the service account, updating the password for each job.
  5. Central Administration – Go into Security / General Security, click on the ‘Configure managed accounts’ link. Find the account to update and click the Edit link to reset the password.
  6. Central Administration – Go into General Application Settings / Search, click on the ‘Farm Search Administration’ link. Click on the ‘Search Service Application’ link to bring up the Search Administration screen. Check the account being used for the ‘Default content access account’. Verify that it is using the service account being updated, click on the name and enter the new password, then click OK.

That should do it for resetting a service account password being used by SharePoint.

read more
Caroline SosebeeReset a SharePoint 2013 Service Account Password

How to Fix SqlClient.SqlException (0x80131904) for Very Large SharePoint Farms

Caroline Sosebee is a Software Engineer at ThreeWill. She comes to us with 20+ years of software development experience and a broad scope of general IT support skills.

Did You Get SqlClient.SqlException (0x80131904)?

We recently ran across a very interesting and NOT (or barely) documented problem on one of our client’s on-premises SharePoint 2013 farms which was a doozy to solve.

The site with the problem is in a very large SharePoint farm being used as a central repository for reports that are accessed by employees throughout the company. Many of the reports are published automatically via Business Objects while many others are created and uploaded manually. The net of it is that there are thousands and thousands of files being added/manipulated daily (or weekly or monthly) within the document libraries in this site. All this activity creates a lot of churn in the databases and ultimately caused SharePoint to hit what would seem to be an impossible to reach value.

The problem first manifested itself as an inability to upload a new document or to edit the properties of an existing document. When searching the ULS logs, we found that it was triggering the below SQL error:

Error: SqlClient.SqlException (0x80131904): Arithmetic overflow error converting IDENTITY to data type int.

Definition: “An arithmetic overflow is caused by a calculated column value that exceeds the column’s specified size. When this error occurs, the calculation stops and the remainder of the results pane is not filled.”

Finding the Culprit

So the next step was to figure out which SQL table was throwing this arithmetic overflow error. We didn’t have direct access to the SharePoint SQL database, so we had someone on site who did have access to run a set of queries for us. The first one was used to determine which tables had an ‘ID’ column that could be throwing this error:












,c.COLUMN_NAME,'IsIdentity') = 1 AND

t.TABLE_TYPE = 'Base Table' AND



t.TABLE_NAME NOT LIKE 'syncobj_%'



We then ran a query on each of the tables in order to check their current values:

as SiteQuota from

as Versions from

as AllFileFragments from

SolutionResourceUsageDaily from

as EventCache from

as SolutionResourceUsageWindowed from

as SolutionResourceUsageLog from

as SiteDeletion from

as SiteVersions from

As you can see from the results, it was pretty obvious which table was probably causing the error. What wasn’t so obvious is what this table did and what we could do about it.

IDENTITY columns are of type ‘int’ which allows for a maximum value of 2,147,483,647 (2 billion +) – a really huge number. Even though the current max value of the identity column on the SiteQuota table wasn’t this exact number, it definitely showed us that this one was the likely culprit. We confirmed by running the below query, which is used to return the last identity value generated for the table:

Select ident_current('SiteQuota') 

Returned value was 2,147,483,647

Do No Harm

Unfortunately, we had no idea what this table did so weren’t sure if we could reseed it or not. We only knew that the ‘SiteQuota’ table was a SharePoint system table that Microsoft does not publicly document (that we could find).

By running a series of tests on a local system though, we could tell that it was used every time a SharePoint item was touched (added / removed / updated) and that the IDENTITY column was being incremented by 2 each time one of these actions happened. We could also see that this was apparently a temporary ‘working’ table, meaning that items are added to it for the duration of the transaction then deleted.

The Solution

Given this, we were able to successfully reseed the IDENTITY column so that the system could get back online. We ran the following commands to do this and confirm success:

dbcc checkident ('sitequota', reseed, 0) 

– this reset the identity column back to 0

select ident_current('sitequota') 

– this verified that it was reset correctly

After doing this, all was now right in this particular world!

For a typical SharePoint site, we think this limit should never even come close to being reached, which is probably why we had such a hard time finding any information about it out there. But this is such a huge SharePoint farm that this particular client will need to continue to monitor this value on an ongoing basis in order to keep the system from grinding to a halt again.

I hope this helped you – if it did, please leave a comment below and, as always, feel free to ask me questions.

read more
Caroline SosebeeHow to Fix SqlClient.SqlException (0x80131904) for Very Large SharePoint Farms

Building Responsive SharePoint Sites

Eric Bowden has over 19 years of software development experience around enterprise and departmental business productivity applications.

Danny Ryan:                       Hi, this is Danny Ryan, and this is the ThreeWill Podcast. Today I’ve got Eric Bowden here with me. Eric is a Principal Consultant for ThreeWill. Did I get that right?

Eric Bowden:                     You got it.

Danny Ryan:                       Awesome. Thank you for taking the time to do this, Eric. I know we are going to talk about an important subject here today, which is how do you do responsive when it comes to SharePoint. I look forward to learning more about how you actually make this happen. Let’s just get this kicked off with what do we need to know to get started with this?

Eric Bowden:                     I think to get started with the responsive design in SharePoint my recommendation is really to start with the requirements. Some folks may decide that, okay, I want the entire SharePoint experience to do Responsive, and may decide that there are only certain pages that I want to be able to render on a mobile device. Because there are only certain things that your mobile user are going to want to do. For example, they’re not going to commonly upload content in SharePoint from their phone.

Danny Ryan:                       Mm-hmm (affirmative), very nice, and so you’re taking a look at those requirement, and then maybe talk me through the first could of steps that you want to start thinking about.

Eric Bowden:                     Yup, so once you have an idea of where you’re headed with your responsive design or a.k.a. supporting mobile and tablet devices in SharePoint, so once you have an idea of where you’re going, you’re going to start into planning the implementation phase. At the highest level what you’re going to need to do in SharePoint is really disable SharePoint’s default handling for mobile devices.

Out of the box, SharePoint includes a feature that’s a mobile redirection site collection scope feature, and what it does is redirects mobile browsers to what is really a constrained presentation of the SharePoint site pages. You’re going to need to disable that, because if you don’t and you create a mobile design, it will really ultimately ignore your mobile design. It will just go to that redirection page.

Danny Ryan:                       Is this the same, I remember you usually could do like slash mobile, and it would bring you to a page?

Eric Bowden:                     Yup, yup, very similar.

Danny Ryan:                       Got you.

Eric Bowden:                     Same thing. Another aspect of SharePoint when people are thinking mobile, and Responsive is a new feature in SharePoint 2013 called device channels. Device channels allow you to target specific master pages and CSS for specific devices. What you do is you configure a master page in CSS for specific user agent strings, which are presented.

This is different than a responsive design. Responsive design is one where the site is really built to present itself on a variety of screen sizes, so as the SharePoint site shrinks up for say a tablet, or a mobile phone, it’s actually morphing. It’s changing as the screen size reduces, and that’s different than device channels, which are really discreet, specific experiences for specific devices.

Danny Ryan:                       Got you, so we disable what’s built in, starting to think about what types of devices we’re trying to target with this?

Eric Bowden:                     That’s right. Yeah, and that is important. On a recent project that I worked on, we decided that we would target, of course desktop, and then you’re targeting tablet and mobile phones. We chose to go try and find the extremes, so on the mobile phone you’re thinking … We targeting the iPhone 4, because that’s the smallest screen size, in the middle were tablets. You have to keep in mind that tablets are both landscape and portrait, so those may have a different experience. Your contents going to run separately, and then of course desktop.

Danny Ryan:                       Got you, so once you’ve disabled it, and you’re starting to make some changes, what are some of the technologies involved with this? Any further steps we need to make to get ready for this?

Eric Bowden:                     Yes. It’s good at this point to think about or to be aware of what is going to be changed in your SharePoint environment. What are the scope of appdev changes that I’m going to make in the SharePoint environment to support responsive design? Those are not entirely limited to, but include master page updates. Unlike the device channels, you’re not going to have a number of master pages, you’re going to have one master page. There’s also going to be layout pages, changes to those. I highly recommend the use of publishing sites with layout pages versus team sites with web part pages. The reason for that is because it’s a lot easier for us to change the layout of a layout page than it is a web part page.

Danny Ryan:                       Got you.

Eric Bowden:                     On our most recent project 99% of the sites were publishing sites with layout pages. Now we did have some team sites. I’ll talk a little bit later about some of the ways you can poke into those, and continue to make those responsive. The third piece that you’re going to be updating of course are CSS. Really what you’re going to be doing there is overriding in large part CSS styles that are provided by default from SharePoint.

Danny Ryan:                       Got you. Let me back up here just a second, whereas, as far as we’ve said you use SharePoint in general. We’re talking about on-premise … I guess there’s a couple different versions that we’re dealing with. I know it depends on the project, but you’re focusing primarily on a SharePoint 2013 on-premise is that typically what we’re dealing with?

Eric Bowden:                     It could really be either one from a technology standpoint. The most recent project that I’m referring to was an on-premise version of SharePoint 2013. These changes could be made in Office 365, the only caveat to that is you do need to be aware of Microsoft’s guidance for upgrades. They may push an upgrade out at any time. If you have your own master page, you made a copy, and now you’re using your own master page, and any updates to master pages that they may have pushed out, you’re going to miss those. The ideal case-

Danny Ryan:                       As soon as you do a custom master page, then that’s the one you move forward with, and if they make any changes, you don’t get those changes.

Eric Bowden:                     You don’t get those.

Danny Ryan:                       Shut the front door.

Eric Bowden:                     Right. They will have upgraded the default master page for that publishing site. In SharePoint online our recommendation is if at all possible, keeping that master page the same, you’re more focusing on CSS updates. You’re more focusing on something that can be accomplished through a theme. You’re a little bit more limited, but you’re being more friendly to the free upgrades that come with Office 365.

Danny Ryan:                       Have you talked at all about Bootstrap and jQuery?

Eric Bowden:                     Yeah, Bootstrap-

Danny Ryan:                       I’m going to bring this up like I know what I’m talking about.

Eric Bowden:                     That’s good, that’s good. It sounds like you do. When we talk responsive and mobile, Bootstrap is usually the first thing that come to mind. Bootstrap is the first place to start with making a SharePoint site responsive. For those who aren’t familiar with Bootstrap, the good news is you don’t have to become an expert with it to be productive. There are many simple features that are provided in Bootstrap. The simplest is is describing that Bootstrap is designed to provide a 12 column layout for your site. Imagine that you’re looking at a desktop presentation of your site, and imagine dividing it up into 12 columns.

You can combine those columns together. For example, you could have four columns which are really three columns wide each. Then what you’re saying is that for different screen widths, I want those columns to stack on top of each other. As the screen becomes more narrow and you have these say three columns at some point, and you decide what that point is, they stack on top of each other. Bootstrap provides a handful of other handy utilities. You can hide and show certain columns. Let’s say we have a rendering of a web part, which has certain data on it.

Maybe it’s a listing of users, and it has the name, title, I don’t know, phone number, and then for a mobile maybe it’ll just have their name and phone number, and we’ll leave the title off. That’s the simple Bootstrap, and that’s really the best place to start. What you’re going to be doing there is open your master page, add a reference to Bootstrap, and then you’re going to start decorating HTML elements that are in your master page with Bootstrap classes.

Danny Ryan:                       Decorating, how festive.

Eric Bowden:                     Bootstrap, that’s going to get you started. That’s a real quick easy way to get really any site responsive. It’s a lot faster than you might see with a standard application, but with SharePoint you’re going to have to go a few steps further. The reason for that is because we don’t really have control over everything that’s being rendered. The next step is we’re going to have to get in and override CSS. SharePoint may be saying a column is a specific width.

One of the things that we battled with a lot was the padding, so as the screen width becomes more narrow, we want to constrain that padding between elements to give it a little bit more space. Another aspect are what are called meteor rules. Meteor rules allow us to specify specific CSS attributes for certain screen widths, so that comes into play. Padding is a pretty good example. For desktop widths, maybe we’ll have the default padding, but then for phone widths we’ll constrain that padding.

Danny Ryan:                       Got you, and then jQuery, have you talked about that?

Eric Bowden:                     Yes, so jQuery and JavaScript … I have to admit, that’s not something I was expecting to get into when making a SharePoint site responsive, but it ended up being really a key, and highly useful tool. The net of it is that because we don’t have control over everything that’s being rendered in SharePoint, we use jQuery to select elements that are on the page and maybe add Bootstrap classes. Maybe we’re adjusting the width. jQuery was used to constrain images. Images end up being a challenge, because images will tend to push out the boundaries of an HTML element, so you’ve got to find some way to get those images to constrain, and jQuery and JavaScript is one way to do it.

We had one interesting application of jQuery and JavaScript was with this particular site design we decided … You’re familiar with the quick Launch in SharePoint, that’s on the left hand side of the page normally. For our mobile design, we decided to do two things. Take that quick launch and instead of on the left hand side of the screen, we put it to the top. The second thing we decided was that anything that was in the quick launch, except for sub-sites, would be removed.

What we’re doing there is we’re allowing users who are on a mobile device, we’re saying, “Well, let’s not clutter them up with lists and libraries. We’ll just let them navigate from site-to-site. They can see the home page and dashboard style information that’s on each home page.” That was accomplished with jQuery because we need some of that JavaScript logic to figure out, okay, here’s this link, well, what is it? Is this a sub-site, or is it a list, or a library?

Danny Ryan:                       My goodness. It sounds like there’s things in SharePoint that you’re having to, I call it, fight against and make change around, and then there’s a couple of technologies. Were there certain tools that you were using, like browser tools to go inspect who had control and those types of things? I know I deal with it on our public site, just trying to figure out who’s setting what and digging into that, so are there certain tools that you end up using when you’re doing responsive design with SharePoint?

Eric Bowden:                     Yeah, absolutely. Chrome developer tools is a great place to start. All of the browsers have developer tools, but Chrome is the best one to start with because it has the ability to render for mobile devices. There’s a little icon there, you click and it goes into a mobile view. Then you can actually select from maybe a dozen of different devices. The iPhone is one of them.

Danny Ryan:                       They just change the user agent. Is that what it’s doing?

Eric Bowden:                     It does. It changes the user agent, and it also changes, of course, the screen size to be the appropriate screen size. We found I would say maybe 90% of issues on mobile devices we found through testing with the Chrome developer tools, but highly recommend that folks are sure to have a sampling of the actual devices, preferable across the three major OS’s: iPhone, Android, and Windows phone.

For us, iPhone was the friendliest. We had the fewest issues on that one, or at least the fewest issues that were not discovered through the Chrome developer tools. Android ended up being the second friendliest, and funny enough we had the most issues on Windows phone. You have some issues like one of the basics and fairly easy ones is that on a desktop experience you’re used to the hover, well there’s no hover on your mobile devices. You have to changes those to be click, so they actually touch those things instead of hovering over them.

Danny Ryan:                       For the testing it seems like you want to dedicate some good time towards testing. Did we have somebody come in in particular? Did Brandon come in and help out with the testing for this?

Eric Bowden:                     Yes, so Brandon was a key part definitely in the testing effort. We had him configured with his own local, of course, SharePoint 2013 environment. He had testing across those devices. We had iPads and the Galaxy tablets and so forth. Probably can’t say enough good things about the testing effort there, and really being sure to … You can’t really make assumptions on it, or you can’t make too many assumptions. I’ll put it that way.

Danny Ryan:                       Interesting. Anything to wrap this up at all? Anything more you want to add? Besides, if you’re doing something crazy like doing responsive with SharePoint you should contact us, right?

Eric Bowden:                     Absolutely, absolutely. Yeah, so we’ve been through it a few times, and can definitely save you some time. I think one thing that I’ll add is on this most recent project, having good site analytics and site usage data was really important. It was really important for two reasons. One is constraining the scope of the project, so we can find out what are people really using. If nobody goes to a particular page, let’s not put the work into making it mobile and able.

Now what might end up happening is, and largely it does through your work, the site may end up being mobile and able, because it just is by default of the other work that you’re doing, and of course, lucky you it works. If nobody’s going to that site, recognize that and don’t put the effort into testing it and appdev on a site that nobody’s visiting. The next aspect of having that site-

Danny Ryan:                       I’m sorry to interrupt. What did you use for analytics?

Eric Bowden:                     This site was using Google Analytics.

Danny Ryan:                       Okay, so you had just the embedded in there code and looked at Google Analytics?

Eric Bowden:                     Yup.

Danny Ryan:                       Okay.

Eric Bowden:                     Yeah, and interesting story about that is that there’s a CodePlex project for embedding Google Analytics into SharePoint. These folks have been using it for quite some time and with great results. However, we found an issue with that project, and it only showed itself on Windows phone. We had to make a little update and work around that by taking what was that Google Analytics JavaScript, taking it out of the site collection feature that comes with this Google Analytics solution by default, and adding it ourselves to the master page. That worked around what was really an authentication issue only on Windows phone.

The last thing to mention about Google Analytics or any analytics that you choose, a benefit is that when this project is complete, and you have your launch and you do some marketing around the site, you can find out are people really using this site with their mobile devices, and which pages are they using. Maybe they branched out beyond the site usage patterns that you’d imagine that they would use.

Danny Ryan:                       Cool. Thank you for taking the time to share this. I appreciate your sharing with folks some insights on how to make your SharePoint sites responsive. That’s neat that you mentioned this works for both on-premise and Office 365.

Eric Bowden:                     That’s right.

Danny Ryan:                       That’s very cool.

Eric Bowden:                     Thanks for having me.

Danny Ryan:                       You betcha. Thanks so much for listening. Take care. Bye bye.

read more
Eric BowdenBuilding Responsive SharePoint Sites

Populating a Multi-Value People Picker with PowerShell

Will Holland is a Senior Software Engineer at ThreeWill. Will has proven to be adept at understanding a client’s needs and matching them with the appropriate solution. Recently he’s developed a passion for working with .NET, MVC, and cloud-based solutions such as Microsoft Azure and Office 365.

On my current project, a large scale migration from a SharePoint dedicated environment to SharePoint Online, my team is using a “Site Inventory” list to keep track of the over 52,000 site collections in our client’s source. Since the source is “live” and things are constantly evolving, we get periodic CSVs containing the most recent data regarding the source.

Naturally, 52,000+ rows is a LOT of data to go look through, so we created a PowerShell script that would parse the updated information, compare it to the Site Inventory list, and update any changes we find. Among the columns in our list are a few “Owner” columns (primary and secondary) and an “All Administrators” column. All three of the columns are just text fields that contain the login names of users in the dedicated environment, and we wanted to aggregate the three fields into one multi-value people picker.

Sounds easy, right? I ended up having quite a struggle, spending more time than I felt necessary dredging the depths of the internet for answers.

I knew that I had to use the Web.EnsureUser method to turn my username into a User object so I could grab the ID. I also knew that I needed to turn that ID into a lookup value since a people picker is, more or less, a special kind of lookup column. Finally, I knew that my “AllOwners” column needed an array of lookup values. That last part was where the issue came in.

$userName = "whd\wholland"
$spuser = EnsureUser $context
$user if($spuser -ne $null){
     $spuserValue = New-Object Microsoft.SharePoint.Client.FieldUserValue 
     $spuserValue.LookupId = $
     $listItem["AllOwners"] = @($spuserValue)

After going through the steps of turning a username into a FieldUserValue object (the ‘special’ lookup value I mentioned earlier), I would simply wrap that FieldUserValue in an array and attempt to set it as the value of the “AllOwners” field. My expectation was that, in doing so, I was creating an array of FieldUserValues. Expectations and reality don’t always line up.

As it turns out, unless you specify a type, PowerShell will create an array of the most generic type it can. In my case, I was getting an array of generic Objects.

Before I tried to set the value of my field, I needed to cast my array to be, specifically, an array of FieldUserValue objects. Below you’ll find the code snippet that sorted the issue for me.

$userName = "whd\wholland"  
$spuser = EnsureUser $context $user  
$lookupValueCollection = @()  
if($spuser -ne $null){  
     $spuserValue = New-Object Microsoft.SharePoint.Client.FieldUserValue                 
     $spuserValue.LookupId = $  
     $lookupValueCollection += $spuserValue  
$userValueCollection = [Microsoft.SharePoint.Client.FieldUserValue[]]$lookupValueCollection 
$listItem["AllOwners"] = $userValueCollection  
read more
William HollandPopulating a Multi-Value People Picker with PowerShell

Switching SharePoint Views Defaults from Standard/HTML to Edit/Datasheet/GRID and Back

Kirk Liemohn is a Principal Software Engineer at ThreeWill. He has over 20 years of software development experience with most of that time spent in software consulting.

I recently created a new list by uploading an Excel spreadsheet to SharePoint 2013. I’m sure there are several blogs telling you how to do this, but here is one that you may find useful.

The problem I encountered, however, is that after uploading the spreadsheet to Excel, the view that was created was automatically in edit mode (a.k.a. Datasheet view). I guess the thought was that people using Excel would like to be in that mode by default. I didn’t want that so I figured out how to switch it back. It wasn’t entirely obvious, so I thought I would share.

I knew I could create a new view from scratch that was not in edit mode by default, but this view had been updated to have many of columns in a specific order, and I really didn’t want to do it manually. Plus, I knew there had to be a way. I started out looking at the view in the browser and didn’t see anything. Then I edited the page and looked at the web part properties for the view, but that didn’t help either.

The solution was to open the site in SharePoint Designer 2013 and navigate to the view itself. Upon opening the view I saw the following HTML (I have seen this on line 37 and 38 for me depending on the view):

<View Name="{24C927B3-0768-4129-9A41-73961AA48508}" DefaultView="TRUE" Type="GRID" DisplayName="All Items" Url="..." [a bunch more attribues]>[a bunch of child elements]</View>

I simply had to change GRID to HTML and save the page:

<View Name="{24C927B3-0768-4129-9A41-73961AA48508}" DefaultView="TRUE" Type="HTML" DisplayName="All Items" Url="..." [a bunch more attribues]>[a bunch of child elements]</View>

I got a notice about the page being customized (unghosted) which was fine for me. You can change these back and forth and it works fine. SharePoint Designer shows a summary of all views for a list and their type (HTML or GRID). That summary is cached so it doesn’t update after you save the changes to the ASPX page directly.

read more
Kirk LiemohnSwitching SharePoint Views Defaults from Standard/HTML to Edit/Datasheet/GRID and Back

Pointers on How to Get an App into the Office App Store

Danny serves as Vice President of Marketing at ThreeWill. His primary responsibilities are to make sure that we are building partnerships with the right clients and getting out the message about how we can help clients.

I’m working with Eric Bowden on getting an integration (called Trove) between Salesforce and Office 365 in place. Basically, we are helping Salesforce users store their documents for Opportunities and Account in a document library in Office 365. Sounds pretty basic (and pretty high value). Yes, I want this for my own – but so would everyone else who uses the world’s leading CRM with the world’s leading productivity platform.

What’s different about our Office App is it’s in place primarily for security reasons and to make the maintenance and set up much more straightforward. This may (and probably will) change in the future when we find out from initial adopters about features they would want on the Office 365 side of things.

Ok, enough about that. Here are some links to get you started:

  1. Publish Office and SharePoint Add-ins and Office 365 web apps –
  2. Upload Office and SharePoint Add-ins and Office 365 web apps to the Office Store –

Getting Started

Here’s a link to the Seller Dashboard – Sign in with your Microsoft account and bookmark the site. Trust me on this one.

Creating a Seller Account and Payout Information

Even though Trove is a free app, we still needed to fill out info on us as a seller and where to send the check (when we do create a paid-for app). You’ll first create a marketing profile that includes your company name, your logo, and a brief description of your company. As part of this process, you’ll also need to provide a reference of someone inside your company. Give the person a heads up (I used Tommy) so they can keep an eye out for the email. It comes pretty quickly.

You’ll need to talk to the folks in Finance to get the right info on your bank account or PayPal account for payout. You’ll also need your Tax ID, if you don’t know that already. If you don’t have all this information handy, you can save the information and submit it later on when complete.

Creating Client IDs and Secrets

Ok, this is where I really leaned on Eric Bowden. We started by creating a Client ID – you need to do this if you are a provider hosted app or a connector (we are the latter). The important field for us was the length that the client secret is valid for – we wanted this to be as long as possible so we selected three years. Another tip is that the App Redirect URL must be https:// and refer to a specific page in your app built for OAuth authentication. After clicking GENERATE CLIENT ID, I copied all the info and stored in a secure place and shared with Eric. This will be the only time you will see the client secret – so make sure you put it someplace safe that you can come back to.

Submitting the SharePoint Add-in

There’s a checklist here. Here are some pointers. You want to make sure the version exactly matches the version number in the add-in manifest file. We tested this out and this really does matter. For our SharePoint Add-In, we needed a 96 by 96 version of the Trove logo. My Photoshop skills include resizing images, so I had this covered. I had Eric send me the app file (he has the easy parts) and I used the Client ID that I just created. There is a section for Testing notes – here is where you want to include any special instructions for testing the app. Since our app has two parts – one part in the AppExchange and the other in the Office Store—we needed to provide info on logging into a Test environment for Salesforce along with a Test environment for Office. Finish up with providing a short and long description with some screenshots. I don’t have more to add about the other fields.

The Fun Part – Getting Approval

The app first goes through a scan and then the testing begins. We were pleasantly surprised with how quickly the process happened and the level of detail in the testing feedback. I’m hoping that Eric will write a post on what this is – hopefully this will be a blog post we put out later today or this week.

Posting Updates to Apps

One last tip – you want to make sure you specify the correct Client ID that you created for this version of the app. I had Eric look over my shoulder when doing this.

Do You Have Any Pointers to Add?

Leave your pointers in the comments below.  Of course, if you want some help to get your app into either the Office App Store or the AppExchange please reach out to us. Or maybe you have an app like ours and want build something that goes into both stores-we’d love to hear from you.

read more
Danny RyanPointers on How to Get an App into the Office App Store

Solving SharePoint 2013 Usage Report Problems

Caroline Sosebee is a Software Engineer at ThreeWill. She comes to us with 20+ years of software development experience and a broad scope of general IT support skills.

Recently, I had to dig in and figure out how SharePoint 2013 Usage Analytics Reporting works since one of our clients was actively using this feature and started having issues with it due to a server that decided to whack out. This blog details the things I did in order to get it working again. It, in no way, makes me an expert on it, though. I’m only hoping this will be of some help to someone else who suddenly finds themselves struggling to figure out what went wrong with features they know nothing about. J

Fun Stuff, Part 1 – Usage Reports Showing All Zeros

Our client uses the built in Usage Analytics reporting found in SharePoint 2013 in order to determine how popular certain reports are with the users. Overall, this has worked well for them.

But recently they had a major problem with their web front end and ended up having to rebuild the box. Once the new front end was up and had been running for a few days, we checked the usage reports and found that they were showing zeros across the board. Apparently it hadn’t accumulated anything since the new box was turned on.

To troubleshoot, I began with the basics of verifying that all the settings and services needed to handle collecting the usage data (a part of Web Analytics) were on and had the appearance of working. I then checked the directory used for the .usage log files and found that they were being created and consumed. At this point, I could have checked the WSS_Usage database to be 100% sure that the data from the .usage files were being successfully stored there, but decided that that was way above my pay grade and just assumed the data was getting there ok. J

Since all seemed ok at this point, I turned to trusty Mr. Google and found this very helpful blog that explained the problem and solution very clearly. It turns out that the receivers that are usually registered to the usage definitions had been ‘disconnected.’ These usage receivers are what send the data from the WSS_Usage database along to the analytics engine of the Search Service Application (SSA) process which then turns the data into consumable bits for us. I found this link to be another great source of information on how this part of the process works.

After following the instructions to reattach the receivers and waiting the requisite 24-36 hours, there was now data again. Yay! To confirm this, I navigated to Site Settings / Popularity and Search Reports and then opened the ‘Usage’ report which showed data beginning to collect again.

Fun Stuff, Part 2 – Usage Reports No Longer Collecting Data

And then I went to sleep. When I woke up the next day, we had more front end problems which caused the server to basically stop responding to any and all requests. There were a couple of different problems that caused this, but they are an aside to this post and better left un-delved. Needless to say, a little scrambling got the server responding again, but for some reason the usage reports had stopped collecting data. Of course.

So I went down the same path as before, trying to figure out what was wrong. This time, the receivers were still attached but when I went to the logging directory, I saw that the .usage files were no longer being consumed for some of the selected events. The files were being created but were simply piling up in the log directory even though the Import timer job indicated it was running successfully.

You can determine which events are actively having usage data logged by navigating to Central Administration / Monitoring and clicking on Usage and Health Data Collection. Within the Event Selection section is a list of ‘Events to log.’ For each of the items checked, there will be a corresponding log directory created holding both the .usage files and an ImportProgress.ini file.




What I finally found was that some of the ImportProgress.ini files used by the Import Timer job appeared to be corrupted. And here’s where known fact is replaced with what worked for me, as I could find no confirmation that what I’m about to state is in fact true. But my usage data is collecting again!

Usage log files accumulate in their corresponding directories until the timer job runs (default is every 5 minutes) at which point they are processed and deleted. Each .usage file name is composed of the server name, the date, the UTC time then the date again and a time with a sequential ‘version’ number appended to the end. Each file seems to have a 1 megabyte size limit before a new .usage file is created. If multiple files are created in a single minute, they are differentiated by the trailing sequential number. (You can see in the illustration above that there were several files created during the 1723 minute.)

Inside the ImportProgress.ini file there are only two lines. The first line shows the last file that was processed by the timer job (for that event) and the second line seems to indicate the success/failure of the import. A value of -1 indicates success.

Correct .ini File

But after the server freeze, when I looked at the ImportProgress.ini files, several of them did not have a -1, but instead had some random number.

Incorrect .ini File

As I didn’t think this looked correct and the logs weren’t processing anyway, I decided that I would delete all the .usage files piled up in the directory (I actually saved them elsewhere, just in case) and updated the .ini file to have a file reference on line 1 that showed a date and time that was before the current time and a -1 on line 2. I then recycled the timer service by triggering the Timer Service Recycle job (the correct way to do this) to ensure that the new changes were picked up.

After all this, I started watching the log directories again and found that the files were back to being consumed correctly. So the next day, I checked my data and was ecstatic to see real numbers in place of zeros for the day before, telling me that all was well again.

And I’m happy to report that we haven’t had usage problems since. (I’ll continue to cross my fingers on this one, though!)

read more
Caroline SosebeeSolving SharePoint 2013 Usage Report Problems

Using JavaScript IIFE’s and Promise Chaining with Lookup Fields

Lane is a Senior Software Engineer for ThreeWill. He is a strong technology expert with a focus on programming, network and hardware design, and requirements and capacity planning. He has an exceptional combination of technical and communication skills.

After reading Will’s excellent blog post about bootstrapping an Angular app with server side data, I wanted to share a trick that I recently used for bootstrapping a pure client side SharePoint app that dealt with lookup fields and promise chaining. While in this post I will also be using Angular, this technique should work with any framework that uses JavaScript promises (such as jQuery’s Defer or Kris Kowal’s Q).

In both SharePoint 2010 and 2013, the REST API will not return lookup field display values (unless you use the $expand query operator, but that only works if you know ahead of time which of the fields are lookups). Instead, the API will just return the ID of the lookup value, meaning you have to make another trip to the server to get the display value for the lookup field. However, when you are working with JavaScript promises, this can get messy quickly because of the asynchronous nature of promises and the need for following a synchronous series of steps to load list data from multiple lists.

In the JavaScript world, there is the notion of an IIFE (Immediately Invoked Function Expression). As it’s name implies, IIFE’s are a mechanism for immediately executing a function block without needing to have a function declaration (Ben Alman does a much better and far more detailed explanation of IIFE’s if you are interested). Using an IIFE inside of a promise’s .then() callback allows your code to be schema-agnostic while being much easier to read and (in my humble opinion) cleaner.

So let’s use a simple,. slightly contrived, example. Say we have two lists: Parent and Child. Parent has a lookup field that is bound to a field on the Child list. Using an IIFE inside a .then() callback to make the second trip to the server to get the Child’s lookup value would look something like this:

That’s it. Now you may have noticed that I contradicted myself because in the above code I have hard coded the ChildColumnId field, so it would have actually been much easier to just use the $expand keyword and avoid the need for the second round trip in the first place. I did this to make the code easier to understand. A more detailed example would be something like this:

You might have noticed that I also adding the result onto the parent object differently. In the first example, I added the ChildColumn field as a child object off the Parent;  In the second example, I added Child Columns as a property on Parent. Frankly, I just did this because it made the code on the second example a little easier to read (and write) but either approach is perfectly fine depending on your situation and how you are binding your view.

A working demo project for SharePoint 2013 can be downloaded here.

read more
Lane GoolsbyUsing JavaScript IIFE’s and Promise Chaining with Lookup Fields

Defensive Coding in SharePoint Event Receivers

Tim is a Senior Consultant at ThreeWill. He has 15 years of consulting experience designing and developing browser-based solutions using Microsoft technologies. Experience over the last 8 years has focused on the design and implementation of SharePoint Intranets, Extranets and Public Sites.

As you design an Application solution in SharePoint that involves the use of SharePoint event receivers, you have to consider that event receivers can fire in situations that you may not anticipate. Therefore, you need to code defensively as the object you are expecting to be available in the Event Receiver may be null. Here are two such examples:

Update of a List form (i.e. Newform.aspx, Editform.aspx, etc)

When you update a list form that is tied to a list that contains an event receiver, the “ItemUpdating” event will be fired on the event receiver when you save the changes. In this case, the properties.ListItem will be null. So, in our situation, we check for this condition and return immediately when it is true.

Restore of a deleted item from the Recycle Bin

When you restore a deleted item from the Recycle Bin, the event receiver on the list where the item is being restored will fire. In this scenario, there may be properties that are null or you may want to treat restored items differently than newly created items. In our scenario, we did not want to take any additional action on restored items, so we did an immediate return when we determined it was an item from the Recycle Bin. The ItemId for a restored item is greater than 0 so we used this information to determine this was an item being restored as opposed to a new item where the ItemId would be 0.

So, next time you include an Event Receiver as part of your SharePoint solution, be sure and test as many “corner cases” as possible to ensure your Event Receiver doesn’t break your application or produce unexpected results.

read more
Tim CoalsonDefensive Coding in SharePoint Event Receivers

ThreeWill Gold Sponsor for Upcoming SharePoint Saturday Atlanta

Danny serves as Vice President of Marketing at ThreeWill. His primary responsibilities are to make sure that we are building partnerships with the right clients and getting out the message about how we can help clients.

​ThreeWill is proud to be a Gold Sponsor for this year’s SharePoint Saturday in Atlanta.

Atlanta’s 7th Annual SharePoint Saturday is Saturday, May 30th, 2015 at the Georgia State University Alpharetta Center!

What is SharePoint Saturday? SharePoint Saturday is a free community‐focused SharePoint event dedicated to educating and engaging members of the local SharePoint community. SharePoint Saturday draws upon the expertise of local SharePoint IT professionals, developers, architects, and users who come together to share their real world experiences, lessons learned, best practices, and general knowledge with other interested individuals.

This event is unique in that it is “for the community, by the community” and is free for all that desire to attend.

This yearly event is the only time that Atlanta’s SharePoint experts come together with companies interested in, or currently using, Microsoft’s SharePoint solution.

read more
Danny RyanThreeWill Gold Sponsor for Upcoming SharePoint Saturday Atlanta