Create Unique Keys Using Calculated Fields
This little trick is something that should have struck me long ago, but I just recently hit upon it. I had a situation where I needed to create a column in a list that looked up data from another list. Normally, lookup columns use the Title field as the text displayed in the lookup dropdown. In my case, this wouldn’t work because the Title field wasn’t unique in the lookup list.
Using a little bit of database design thinking, I decided that the combination of the Title and a Date field presented a unique business key. In the lookup list, I created a calculated field using the following formula (assume field names of Title and Date):
=TEXT([Date],"yyyy-mm-dd")&" - "&Title
I then created the lookup column in the other list to point to this calculated column instead of the Title column.
The beauty of calculated fields is that they dynamically change, so you don’t have to worry about your data getting all out of whack.