Tallan's Technology Blog

Tallan's Top Technologists Share Their Thoughts on Today's Technology Challenges

Create Unique Keys Using Calculated Fields

Craig Condon

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.

1 Comment. Leave new

[…] Create Unique Keys Using Calculated Fields […]

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>