A customer had a request last week that on the surface seemed straightforward. So straightforward that I assumed this was a design I had implemented before. Maybe it will sound familiar to you too.

The challenge was to design a form for logging issues that not only tracked the usual stuff like Assigned To and Assigned From but also had to contain which role the assignee and the assigner belonged so issues could be categorized and sent to a group or an individual.

The Role column that was created for this customer was being used in many other lists in their templates. It was set up as a site column at the site collection level and added where appropriate. But for the issue list, this idea had the following limitations:

1. A site column can only be added once per list (and I needed "Assignee Role" and "Logged By Role" fields reading this same information)
2. Lookup columns cannot reference choice columns so creating a lookup column that referenced the site column in another list was unworkable.
3. Creating a second, identical column as a local or local site column would work, but require additional maintenance if the business units were ever to change
   1. Furthermore, realizing this and how to fix is something that is likely to happen after the fact as these little "refinements" seldom get documented, unfortunately.

I knew all roles were used at least once in the customer's Project Roles list. My solution to this was to create a new column in the Project Roles list named "RoleLookup" as a single line of text. In the Default Value field I selected Calculated Value and set this to "Role".

It's important that this field remain hidden from humans; remove it from all views by unchecking the all the columns in the column visibility settings next to it.

Then in the Issue list, I created a new lookup column that referenced this new RoleLookup column in the Project Roles list. As long as the Project Roles list remains complete, attaching two identical columns to an Issues list will not be an issue.