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

• 0
• Question
• Updated 4 years ago

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

• 60 Points

Posted 4 years ago

• 0

QuickBaseCoach App Dev./Training, Champion

• 64,860 Points
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.
• 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)

QuickBaseCoach App Dev./Training, Champion

• 64,860 Points
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.
• 60 Points
Okay, but will it display 99 as the "priority" value on my reports? I'd like to avoid that if possible

QuickBaseCoach App Dev./Training, Champion

• 64,860 Points
:)  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].
• 60 Points
OH! duh.. Thanks for your help, works great :D