Custom Sort Question

[ddinisco]

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]

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 )

[comment]

99?

[Fitch]

99 works. Use Time( 23 ; 59 ; 59 ) if you like.

[comment]

99 works.

What type is the result?

[Fitch]

It's 3 am... 4 days in the future.

[RodSierra]

Why not try sorting by a value list based on the field in question?

[Fitch]

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]

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 )

[comment]

It's 3 am... 4 days in the future.

Is it?

99.zip

[Aussie John]

Is it?

99 as text = 99hours

99 as number = 99minutes

[comment]

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]

Good news: it does work if you return the calculation result as a timestamp.

99 = 1/5/0001 3:00 AM

[comment]

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]

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]

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]

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]

What I was trying to say that it's either not so or I don't understand what you're saying (or both).

[Fitch]

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]

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]

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.

[comment]

Or:

SortEmptyLast.zip

[Fitch]

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]

Yeah, it's too bad we don't have a script trigger for that. But there's always List view.