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?
+) Are there other options available to solve this (what I have to assume is pretty common) problem?
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...