Jump to content
Claris Engage 2025 - March 25-26 Austin Texas ×

This topic is 4894 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Posted

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

Posted

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 )

Posted

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

Posted

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

Posted

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.

Posted

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 )

Posted

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 )

Posted

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

99 = 1/5/0001 3:00 AM

Posted

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?

Posted

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

Posted

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.

Posted

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.

Posted

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").

Posted

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.

Posted

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.

Posted

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.

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.