Concatenate Dates & Lookups

Question

I'm building a work assignment trading system with Sharepoint. I have two lists: Available Shifts, and Shift Trade Request. When creating a new item in Shift Trade Request I want to have a lookup to identify the ____ being requested (from the Available Shifts list). The Available Shifts list doesn't have a string of text in the title field that would work well. I wanted to concatenate a Date field and a Lookup field in Available shifts. For example the field would be 9/7/2009 - Route 5. Route 5 is a lookup field from a third list called Routes. The issue I'm having is Sharepoint won't let me concatenate a lookup field in a formula, and date fields get returned as a number instead of a date.

How do I concatenate these fields, or what would be another suitable workaround?

Answer 1

Hi Logan,

To display date fields as dates in a calculated field, you should use the TEXT function with the field name and the date pattern you need. For example, in your case you should use this one for the creation date to get 9/7/2009 for items created today:
=TEXT(Created,"d/M/yyyy")

The lookup column is a more serious problem, as out of the box SharePoint does not support using lookup fields in a calculated field.

I see two possible solution (or let's call them workaround) for this issue:
- You can create a custom field for lookup AND concatenation
- You create a custom lookup field and use its value in a calculated column for concatenation

Since I have doubts that one can use a custom lookup field (one that is derived from the lookup field) in a calculated column, I would vote for the first option.

You can find the following resources useful if you have no experience with custom fields (search for the "sharepoint custom field" term on the Internet to find dozens of other articles):

How to: Create a Custom Field Control
http://msdn.microsoft.com/en-us/library/aa981226.aspx
Query Based Look Up Field Control
http://www.codeplex.com/CustomFieldControls

Peter

Answer 2

Hi Logan,

It's me again. :-)

Forgot to mention, that you can create an event receiver (for ItemAdded and ItemUpdated methods, see http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.splisteventreceiver_methods.aspx) that update (compute, concatenate, etc.) your field value when the item is changed. In this case you can use a standard lookup field, and read its value using the SPFieldLookupValue class (see the constructor and the LookUpValue property here: http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spfieldlookupvalue.aspx).

For event receivers see Dave's excelent article here:
Delving into SharePoint 2007 Event Receivers
http://www.davehunter.co.uk/Blog/Lists/Posts/Post.aspx?List=f0e16a1a%2D6fa9%2D4130%2Dbcab%2Dbaeb97ccc4ff&ID=69 

Peter

Answer 3

The second answer was marked at an answer rather than the first because the question had been asked in the Programming forum so a solution with event receivers fits that.

Logan, if you want a solution based on OOB possibilities (as outlined in the first reply), post to the Admin forum, not here.

---

WSS FAQ sites: http://wssv2faq.mindsharp.com and http://wssv3faq.mindsharp.com
Total list of WSS 3.0 / MOSS 2007 Books (including foreign language) http://wssv3faq.mindsharp.com/Lists/v3%20WSS%20FAQ/V%20Books.aspx