ddinisco Posted July 14, 2011 Posted July 14, 2011 I have a group of records in which some have data in the 'time' field and some do not. I would like to sort the records in order of time, with all on the empty time field records at the bottom of the list. I am guessing this can be accomplished with custom sort? Thanks David
Fitch Posted July 14, 2011 Posted July 14, 2011 If you sort descending, it's not a problem, but for ascending it obviously is. I'd probably create a calculated field and sort on it instead of the time field: Case( IsEmpty( time ) ; 99 ; time )
RodSierra Posted July 14, 2011 Posted July 14, 2011 Why not try sorting by a value list based on the field in question?
Fitch Posted July 14, 2011 Posted July 14, 2011 Why not try sorting by a value list based on the field in question? I have used a value list in the past to sort a list of numbers, but only when there were maybe 20 max. No reason you couldn't go higher, though. However... 12:00 12:01 12:02 ... 23:59 Ouch. At least you'd only need it for one direction (descending works as is, as noted earlier). Still, I might consider a 1440-line value list if indeed we're only dealing with minutes, which might be the case if these times are manually entered. If seconds are involved, forget it. It would be a cool feature to have the option in the Sort dialog for blanks to go to the top or bottom.
ddinisco Posted July 14, 2011 Author Posted July 14, 2011 Thanks this worked great. Such a simple solution. If you sort descending, it's not a problem, but for ascending it obviously is. I'd probably create a calculated field and sort on it instead of the time field: Case( IsEmpty( time ) ; 99 ; time )
Aussie John Posted July 15, 2011 Posted July 15, 2011 Is it? 99 as text = 99hours 99 as number = 99minutes
comment Posted July 15, 2011 Posted July 15, 2011 To the best of my knowledge, 99 as Number = 99 99 as Text = "99" 99 as Time = 99 seconds = 0:01:39 If the result of the calculation is Text, then the field will be sorted alphabetically and "99" will be placed after all time-of-day entries, but before "99:00:00" - which is a perfectly valid non-empty entry. If that is the logic here, then the calculation should be something like = Case ( IsEmpty ( Time ) ; "z" ; Time )
Fitch Posted July 15, 2011 Posted July 15, 2011 Good news: it does work if you return the calculation result as a timestamp. 99 = 1/5/0001 3:00 AM
comment Posted July 15, 2011 Posted July 15, 2011 Good news: it does work if you return the calculation result as a timestamp. 99 = 1/5/0001 3:00 AM Who are you and what have you done with the real Tom Fitch?
Fitch Posted July 15, 2011 Posted July 15, 2011 Bah. I was looking at my test file cross-eyed, too many "copy of copy" of fields. Yes, if you enter 99 in a time field, and you have a calculated timestamp field that simply equals the time field, you get the result I posted. However, you don't get that result when you use Case ( IsEmpty ( Time ) ; 99 ; Time ). But, GOOD NEWS: Case( IsEmpty Time ) ; 99999 ; Time ) as timestamp = 1/2/0001 3:46:39 AM
comment Posted July 15, 2011 Posted July 15, 2011 Timestamp or time (or number), it makes no difference: you are adding 99,999 seconds to the same starting point that the Time field is using. If the Time field contains a value > 99,999 seconds (27:46:39), it will be sorted AFTER the empty records.
Aussie John Posted July 17, 2011 Posted July 17, 2011 99 as text = 99hours 99 as number = 99minutes A bit cryptic I know but what I was trying to say if you use time (99) where 99 is a calculation it will give the results above if the calculation is of the types shown.
comment Posted July 17, 2011 Posted July 17, 2011 What I was trying to say that it's either not so or I don't understand what you're saying (or both).
Fitch Posted July 18, 2011 Posted July 18, 2011 I would guess that most (not all) people use the time field to track the time of day, and so would never enter 99 or 99999. Since the "99" calc worked for the OP, he must be one of them, and also must have returned a text result. 99 as text, or 99999 (or 99999999 etc.) as timestamp, will do the job for those who are entering time in a 24-hr range, and I was sort of operating under that assumption. Like you said though, Michael, if we're going to return a text result, why not return something that will work for any time value, "z" (or perhaps "empty").
comment Posted July 18, 2011 Posted July 18, 2011 why not return something that will work for any time value It's not just that - it's also that 99 (or "99") is meaningless in this context. It just happens to be one of many values that will sort later than any time-of-day, so it is not apparent what the calc does - as opposed to: Case ( IsEmpty ( TimeField ) ; 86401 ; TimeField ) for example.
Fitch Posted July 18, 2011 Posted July 18, 2011 Exactly. (And I see what you did there.) Which is why I now think I'd advocate for "empty" // with a comment that this needs to return a text result -- unless the client's business rules dictated otherwise.
Fitch Posted July 20, 2011 Posted July 20, 2011 I like it. The only (small) downside is not being able to use table column heads for direct sorting, but that may not be a requirement.
comment Posted July 20, 2011 Posted July 20, 2011 Yeah, it's too bad we don't have a script trigger for that. But there's always List view.
Recommended Posts
This topic is 4894 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now