Working around the "Lookup column threshhold"

Question

To level-set, I'm working in a large enterprise with no Farm Admin access or access to SP Designer. I have out-of-the-box capabilities only as owner of a team site.

I'm building a roster/schedule management system for my team of 23 (19 Incident Managers plus four managers). We do production support for the enterprise and so maintain "full escalation" on-call list: everybody is assigned a rank each night and calls for assistance descend down the list until somebody is reached. During the day we use the reverse of this list as an engagement roster to assign out incoming incidents interactively (ensuring that those most likely to be called at night are last up during the day).

The current system for this uses 20 simple text fields - "Associate1", "Associate2", Associate3", etc. - each field representing a rank and populated with the name of an associate. One of the issues we have is that, for other aspects (such as presence awareness) to function the name here must be EXACTLY the same as the name assigned in Active Directory. When people edit the list, however, they often use a variation which breaks other features.

To eliminate this I began by replacing these fields with People-picker fields constrained to a custom SP group. This works great but I've, of course, run into the limit on the number of Lookup columns allowed in a query/view. So while SharePoint will let me view and edit single entries I can't actually display them (in full) or access all of the data via the web services. At best I can access a curtailed view.

Obviously replacing the People-picker with another lookup column doesn't solve the problem.

It wouldn't be as elegant, but using multi-select columns would also serve the need, except I really don't want to have to edit all 20 fields whenever we have a personal change.

I could create multiple, sequential views (say, three - each returning a third of the fields) and stitch the data together but that feels... stupid.

So, I guess I have a few related questions:

+) Is there any way to standardize the selection options of multiple plain multi-select fields (not lookup fields) so that edits made to the master list are applied to all?

+) Is there any way to trick SP into returning the full data of a list item with too many lookup columns without changing server settings (preferably via JavaScript the web services)?

+) Are there other options available to solve this (what I have to assume is pretty common) problem?

The data for the month is bulk loaded from an Excel spreadsheet using VBA and the Lists web service. It's then presented on the site using client-side JavaScript and the Lists web service.

I'm toying with the idea of adding another macro to the spreadsheet that would automatically update the select options of all of the related fields. I'm not sure how to do that - yet - but it would allow for single-point editing of the associate list...

Answer 1

I am not sure that I completely follow, but here is some advice:

-The OOB list view threshold of 8 lookup columns is a good one to stick with. If you google around you can find studies done by Microsoft that show how going beyond this exponentially slows down SQL. This is because a bunch of joins are being done by the database. Here is an article from technet

-Unless you are bringing in other data with the lookup column, then you can probably do without using a lookup column... and should. Lookup columns arent as resuable, and are much slower then both choice columns and managed metadata columns

-Use a people picker instead of "looking" a column that then uses a people picker.

-If you are only creating the list for the sake of "looking up" information from it, and aren't bringing in other columns from the list, then use a choice column. It is much faster and provides the exact same functionality. (use a site column so it's reusable)

-Lastly, and most importantly, try to use Manged Metadata/term sets in place of lookup lists. They are faster and provide better functionality. Metadata navigation in lists/libraries are a nice bit of functionality

Answer 2

You could write a javascript form that would use list entries or group members and then apply modifications to the multi-select columns' xml definition (XPath: Field/CHOICES/CHOICE) either via Lists web service or JSOM given that the users have Manage Lists/Design permissions at least. Not very elegant, but probably viable.

1. see sample xml schema
2. see sample Lists.UpdateList call with jQuery.SPServices

Answer 3

You could use InfoPath to create a custom create/edit form for the List. Then you could base the "lookup" data on the same source, use it for the dropdowns and have them actually fill text field values in the List. Although, since you don't have designer privileges I don't know whether you could use the InfoPath method either.