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…