Approach to performant display from a SPList with multiple Lookups + Person fields

Issue: business users complain that the page loading of a SharePoint listview takes a long time, up to a minute. During page loading + rendering, the browser is totally unresponsive (Chrome even pops up a dialog about page unresponsive).

In the analysis for the root cause of this structural slow performance I observe that the list contains multiple Lookup fields to other lists in the site, and also a 'Person or Group' field - which is a specific type of Lookup, to the hidden UserInformationList.

This multitude of Lookups in the list is the most significant cause for the slow performance. However, also on user experience / functional level, it is wrong designed: the usage of the page is to first load + display all the items from the list, and next the end-user must filter to select the relevant items.

I therefore first aligned with business owner on another functional approach: let the end-user start with specifying the relevant filter(s) [can be a combination of filters for multiple columns], and then select + retrieve only the items from the list that satisfy the filter-conditions. For the technical design, the Search tool must be setup future-proof, aka cloud ready. Thus interoperating with SharePoint either via CSOM or via REST services. I decided to utilize REST, so that data is returned in JSON data-format, and can be directly data-binded in client-side UI (I used Knockout.js; but same holds for other clientside UI frameworks as Angular, Ember).

In SharePoint REST one uses the $expand parameter to include referred lookup values in the result set. But similar as for XsltListView, this quickly destroys any performance:

So I needed a way to avoid the Lookup-expands, while still being able to filter on and select the values of the Lookup fields. Totally getting rid of the Lookup columns is not an option: for consistent data-management it is a Must-Have that one can select only from the values maintained in the Lookup list. And it holds even more for the Person field: selecting via the PeoplePicker ensures a user-friendly selection + validation.

The approach I decided to is to 'flatten' the lookup values in additional columns. Functional management can still manage the data-items using the lookup functionality, and on data-selection + retrieval I avoid the need to $expand.

The elements of the approach

1. Per lookup value that is needed in the selection and/or display, add a single-line of text column to the list. Set them to hidden in the Item content type, so that their existense is invisible for functional management in the New/Edit/View list-forms;
   For the 'Person or Group' column, include 4 new fields, for
   1. Person Name,
   2. Photo,
   3. Department,
   4. and the UserId in the UserInformationList
2. Create a SharePoint Designer Workflow on the list that activates on ItemCreated + ItemModified events. Design the workflow to propagate ('flatten') the lookup values to their respective flattened counter-field;
3. Realize the bulk flattening of the existing list items through the same workflow, via javascript start the workflow on every item.

Impression of the solution setup + result

---

Some handy resources:

• Hide list column on new, edit item-OOTB
• Extract the department from a Person/Group field
• Handling Lookup Columns in a SharePoint Designer Workflow
• Start Workflow using JavaScript or Ribbon Button

Tip - Provision a Functional Management page for application data

One of the faces/capabilities of the SharePoint platform is that of Web/Enterprise Content Management System (WCM, ECM). When we architect and setup a WCM-enabled SharePoint (publishing) site, there are actually 2 different types of users to take into consideration:

1. The end-users browsing and using the website;
2. The content managers, who have the responsibity for maintaining the content on the website up-to-date

When designing the website, the primary focus is almost naturally on the end-users. Understandable, after all a site which does not attract and keeps the interest of end-users is in fact useless for the company. However, it certainly is also beneficial to give appropriate attention to the usage model for the content manager role. If they can do their content work in a more user-friendly and efficient manner, the site will most likely benefit from this by being more and more often kept up-to-date. Content management is a task difficult enough on the functional level, without the technical / usage model burden of SharePoint content management.

Content in a publishing website comes in various forms. Most known are of course the publishing / web pages, as the entities to which the end-user navigates. But there are more type of content-entities possible:

• downloadable documents (administered in document libraries)
• pictures displayed somewhere in the site (administered in picture libraries)
• data displayed somewhere in the site. Examples are news items, events calendar, links to related information (administered in SharePoint lists)

When the SharePoint is based on one of the publishing site templates, content management of the individual pages is already to a certain amount enabled for the site contributors. An authorized contributor can start the creation of a new page, or the modification of an existing one directly from the siteactions menu. However, if the contributor needs to change one of the other types of content (document, picture, basic list data), there is no such direct usage model. Instead one has to follow the path of Site Actions --> Site Settings --> List and Libraries --> identify and then select the appropriate list ... This is rather cumbersone, and involves a lot of mouseclicks (irritating) + the retrieval and rendering of multiple application pages before arriving at the final destination (time-costly for the user). Another disadvantage is that the content owner must self identify in the listed overview of all the SharePoint lists and libraries in the current site, the particular list/library which holds the content type.

Wouldn't it be nice to apply a comparable usage model as for page-modification to non-page content items ? I thought so, and have realized it within various SharePoint CMS-projects. The basic usage model is:

1. A contributor selects the Site Actions menu
2. The Site Actions menu is extended with a custom action for functional data management

   
3. The functional data management menu entry navigates the browser to a functional data management page. On this page the different relevant content types are enumerated in list form, with direct recognizable identifications.

   
4. Clicking on one of the content type links navigates the browser to a page on which the data items of the related list are displayed. The contributor can here manage the content of this specific type, for instance news messages.

   

This usage pattern improves the user experience for the content management role:

• The usage model for non-page content applies the same pattern as for publishing page content
• The navigation is direct, and orientated at functional level
• Lesser mouseclicks and page retrievals
• Contributor is shielded of from confrontation with the total collection of SharePoint lists and libraries in the site

At SharePoint technical level the following is done to enable this usage pattern:

1. Entry on Site Actions menu: Provision via a feature a custom action, to add an entry to the Site Actions menu, with it's action set to navigate to functional CMS management page.
2. Functional CMS Management page: Provision a web page with on it a Content Editor webpart (CWEP), and add htlm to render a list of the different content types. Each item in the list hyperlinks to the same edit page for editing the contents of the selected list. The listname is propagated via the QueryString.
3. Editable list view: Provision a second web page with on it a ListView webpart. The ListView webpart is used to display the different items in the SharePoint list or library, and to enter new item or edit existent item. This web page is reused to display the contents of the different content lists and libraries. For this it is required to runtime connect the contained ListView to a specific list or library, following the selection the content manager made from the overview. The ListView webpart itself does not exhibit QueryString behaviour. Therefore a hidden technical webpart is added to the page which parses the QueryString to detect the requested list, and next runtime push this setting onto the ListView webpart on same page.

And that's basically it. Your site's content managers will thank you for delivering this CMS functionality. At least, that has been my experience on several occassions/projects...