How to sort a column where blanks are at the bottom?

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

I have a Text - multi-line field, the options are numbers between 1 and 20 (used to indicate a priority) but when I sort a report base on that field's column, it places the records that have a blank for that field before the record that has "1" selected. I tried changing it to a Numeric - multi-line and unchecked "Treat blanks as 0 in calcs" and it didn't make a difference. I got it to work before but can't seem to remember what I did.


Thanks

Photo of Matthew

Matthew

  • 60 Points

Posted 3 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,944 Points 50k badge 2x thumb
So, your question is that when sorted from low to high, blanks float to the top and you want them at the bottom.

No problem.  Make a new field called [Sort Order] with the formula,

IF([Priority]=0,99, [Priority])  (set the Priority field treat to blanks as zeros).

Then use that field as your Sort.
Photo of Matthew

Matthew

  • 60 Points
If I'm reading that right, the formula says: if [Priority]=blank, change it to 99, if not, leave it [Priority]?
(sorry for the super delayed response)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,944 Points 50k badge 2x thumb
yes, that way a blank priority (which will be treated as a zero) will be priority 99 which is higher than 20 so it will sort last.
Photo of Matthew

Matthew

  • 60 Points
Okay, but will it display 99 as the "priority" value on my reports? I'd like to avoid that if possible
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,944 Points 50k badge 2x thumb
:)  If you do not want to display that [Sort Order]  as a column then do not display that field as a column.  Just display the Priority column, but sort on the [Sort Order Column].
Photo of Matthew

Matthew

  • 60 Points
OH! duh.. Thanks for your help, works great :D