I have a column in a list I created that is using lookup and the lookup is referencing another list. The referenced list has just a list of times(1:00 AM, 1:30 AM, 2:00 AM). The problem I'm having is that when I show the referenced list in my column it is sorting it so that is shows like: 1:00 AM, 1:00 PM, 1:30 AM, 1:30 PM. The referenced list is showing the times in the correct order but when it is being referenced it appears to be doing some automatic order on it. How do I get it to show in the correct order (1:00 AM, 1:30 AM, 2:00 AM)? Thanks.
A:
What is the data type of your source column? Is it a text field? A datetime field?
Your problem here is that the values are sorted as strings, not as numeric values.
A solution I see here is to create a calculated column in the source list that would returns type text and formats the values in a way that sorts 'normally', even when sorted as strings. For instance, it could contain values like 01:00, 02:00... 23:30. Then you can make a lookup to that calculated column and your problem is gone.
naivists
2010-01-04 18:42:57
The datatype for the source column is text but I can change it if it helps. I need the times to look like this 1:00 AM, 1:30 AM, 2:00 AM. Is there anything else I can do to keep that format?
2010-01-04 19:35:01
Is it maybe so that you say "it sorts normally", but you are actually looking at the default view of the list, not trying to sort by that column?Another question - are you sure you need a lookup? Can't you just use a CHOICE column?
naivists
2010-01-04 19:43:07
Yea sorry, let me clear that up. I would like the order shown in the column to be the default order of the reference list, the order the items were added. I would rather use the lookup column if I can so that I can use the same list on mulitple columns.
2010-01-04 19:49:46
If you are doing this just for the purpose of reusing the column between lists, try using a site column (http://office.microsoft.com/en-us/sharepointtechnology/HA101577691033.aspx) and make it a choice field. Reason why choice is better than lookup - if you make it a lookup field, SharePoint renders it as a link. There would be no use for the user to click such link.
naivists
2010-01-04 19:58:58
Perfect, thank you
2010-01-04 20:17:29