Problem with sorting on a formula field

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered

Hello,

Need help on a sorting requirement:

I have a formula text field that take a calendar date in mm/dd/yyyy format and converts it to a MMM -YYYY (Jan-2014) and inserts TBD if no date is selected. The sort requirement is to push all records with TBD to the bottom of the list so I created a new formula field that inserts a pipe character if the value is TBD like "|TBD". Sorting on this field moves all the records with date = TBD to the bottom of the list. But the problem is that records with dates like Jan-2014, Oct-2014, etc it sorts alphabetically as opposed to calendar month wise like Jan-2014,Feb-2014 etc.

So the sort requirement is to sort records with date values populating the sort field calendar wise (as in Jan, Feb, march, etc) and those records with TBD as a value to be pushed to the bottom of the list.

How would you solve this?

thanks,

Krishna

Photo of Krishna

Krishna

  • 10 Points

Posted 4 years ago

  • 0
  • 1
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 29,924 Points 20k badge 2x thumb
You need to differentiate sorting and displaying functions so sort on one field and display another.

To get the sort order correct with regard to TBD or blank fields which you want to push to the bottom use a formula date field to map these fields to some date deep into the future say January 1, 2029 a date in the year the Terminator came back from to get Sarah Connor:

If (IsNull[date field],
Date(2029,1,1),
[date field]
)
Photo of Krishna

Krishna

  • 10 Points
Thank you Dan.That was very helpful!