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

Critical thoughts on [yet another] new SharePoint development approach

This Wednesday May 4th, Microsoft announced GA of SharePoint Server 2016. This event is accompanied by blogposts from multiple MVP's. Waldek Mastykarz posted an interesting one "When to use the new SharePoint Framework". However, this posts holds a rather distracting message for us: Solutions built using other development models introduced in the past, such as farm solutions or SharePoint add-ins, cannot be used on modern team sites. Rather then repeating my concerns and thoughts here, I refer to the post of Waldek for full context, and the comments-thread between Waldek and myself.

Our approach to deliver aligned business solutions

In my role as Collaboration Solution Architect I align with our business stakeholders to deliver company internal collaboration solutions. In these solution alignments, the technology is of secondary nature. Its impact is both as enabler as well constraining. Our technology options are in line with the generic enterprise IT architecture: SAP and Microsoft unless. So we apply SAP platform for some dedicated collaboration scenarios, and Microsoft for almost all the rest. As most SharePoint customers came to realize, we also impose governance in the collaboration solution setups we are allowed to deliver to our business. The overarching principle is to deliver only future-proof solutions. This translates to apply standard / out-of-the-box platform capabilities where feasible, and restrain from building custom solutions just for the sake of building custom solutions. And in case custom solution is needed to deliver on the requested functionality, then comply with the Microsoft guideline to stay away from farm-based solutions and instead rely on the Add-In model.

So, how does this work in practice? Let me clarify by a true example. Few months ago I was invited for an alignment meeting with our internal finance department. The title of the meeting was ‘BI portal’. In the meeting, the business stakeholders told about their functional intent. And extended on all of their requirements. Being aware of the technology strategy, they upfront realized that the target platform would be SharePoint. And they already themselves build an image on how the solution should look like.

Next step was to map their functional vision, and detailed requirements to feasibility. Initial focus here is to discuss and challenge on the functional vision. Not that I’m the subject expert, they are, but still it makes sense to ‘walk through’ the functional vision to evaluate it on true added business value. Next is to map the vision, and the detailed requirements, to the technology. How will the generic setup be, what can be delivered out-of-the-box via SharePoint features, what can be delivered through customization, what would require custom solutions, and what is not allowed due our governance constraints? Helpful in both alignments – the functional and the technical – is to use examples of solutions delivered for others, to trigger and inspire. These example solutions can be your own’s, but also of course from anywhere. The SharePoint community is very generous in sharing knowledge and experiences.

As follow-up of the alignment meeting, I sketched a potential solution direction on high level. I deliberately use PowerPoint as format for this, as that by its nature limits you for over-extensive writings. Outline of the solution architecture is: a) Sketch of the context, b) Main requirements, c) UI impressions (mockups), d) Global Design utilizing SharePoint platform capabilities, e) the information architecture. a) and b) serve to verify whether my understanding of the request is valid, and c) is to agree on the user experience.

After fine-tuning on business aspects with the stakeholders, next step is to get technical consent: d) and e). At minimal, communicate the setup of the solution direction with technical peers and our SharePoint operations. In our company we have formalized this via ‘Templace Control Teams’ per technology platform.

If and after both functional and technical alignment, next is to ‘build’ it in agile manner. Deliver a first version, not feature-complete yet, but it must already have functional meaning and value. Demonstrate in a ‘sprint-demo’ to business, discuss on behaviour and new insights, and deliver these in next ‘sprint’.

If the solution setup is restricted to SharePoint standard only, and potential customization as 'SharePoint content' (html, CSS, javascript), it is possible to build up the application direct in the production collaboration space. Although I then typically choose to first build it in my own 'development/playground' site, and after business agreement deploy it to the target location by repeating the 'content-based' provisioning.