Record Sort on Formula

[Former Member]

I'm trying to sort records based on the formula I used to customize a date/time field.  The user wanted the date to appear as "Day, Month Date" or "Monday, December 9" so I created a formula of

CSTR({DriveMaster.FromDateTime}, "dddd, MMMM dd")

But I would like to have the column sorted by date.

Any suggestions?

Thanks,

[abhilash_kumar · Accepted solution]

Hi Trey,

Go to the Record Sort Expert > Move the {DriveMaster.FromDateTime} field to the panel on the right and set the sort in the order you wish.

-Abhilash

[Former Member · Accepted solution]

Thanks Abhilash,

I did try that but unfortunately, the option to set the order is not available when I add {DriveMaster.FromDateTime} to the Record Sort Expert.

[abhilash_kumar · Accepted solution]

I'm sorry, what do you mean you can't add the field to the Sort List?

I can already see that field right at the end.

Are you trying to move the FromDateTime field to the top of that list? That is Not possible because a 'Group' takes priority when sorting.

There is a workaround, however I need to understand what you're trying to do before assuming anything.

-Abhilash

[Former Member · Accepted solution]

Sorry, I was not clear.  Still trying to learn CR!

Yes, I was able to add DriveMaster.FromDateTime to the Record Sort, but like you said, "Group" takes priority.

I am grouping on DriveMaster.ID to keep running running totals.  But I also have a column that displays the date the drive occurs and this is the field I'd like to be listed in chronological order (Dec 1, Dec, 2, etc...)

I think the best way to explain it is that I would like the report to be grouped by DriveID, but sorted on the report by FromDateTime.

From the screenshot below, the dates are not appearing in order.  Hopefully that makes sense.

I am using a formula to format the date to appear like this:

CStr({DriveMaster.FromDateTime},"dddd, MMMM dd")

And I was thinking I might could create an additional formula to sort DriveMaster.FromDateTime in ascending order and then call this new formula in my date modification formula.  If that makes sense at all?

[abhilash_kumar · Accepted solution]

Here's an easy way out:

1) Place the FromDateTime field on the Details Section

2) Right-click the field > Select Insert > Summary > Choose 'Maximum' as the Summary operation > Place the summary on the "DriveMaster.FromDateTime" group which is Group#2 I guess.

3) Go to Group Sort Expert > Choose Group #2 > Sort based on this Summary field

Hope this helps.

-Abhilash

[Former Member · Accepted solution]

Thank you kindly,

This appears to work!

Thanks,

[Former Member]

Hi, Trey

I think you can try create a new formula Day({DriveMaster.FromDateTime}) and then sort by this formula field.

[Former Member]

Thanks for the reply Rong Tan,

Maybe I don't fully understand what you are saying, but if I change my formula to only use

Day({DriveMaster.FromDateTime}) then that only returns the date of the month and nothing else when I need the date to appear as "Wednesday, December 11" not "11".

Thanks,