A-Z Sorting Incorrect for Negative Numbers

[Andrew_Enright]

When sorting by a column that contains negative numbers, in our case DAYS OVERDUE, sorting A-Z does not work as expected. We’d expect that a number like -200 would precede a number like -1. But Airtable does not sort this way…

[W_Vann_Hall]

That’s because you’re sorting them as text, not numbers. (Left-aligned vs right-aligned.) Wrap your formula in a VALUE() function, and things should work OK. (For one thing, your sort options will now be '0 → 9' and '9 → 0'.)

[Andrew_Enright]

Ah, makes sense! Thank you. Unfortunately though, it errors out.

Here’s my formula before wrapping in VALUE():

IF({Auto-Status} = "Complete", "✅",
IF({Auto-Status} = "Ghost!", "👻",
IF({Auto-Status} = "Print Job Ticket", "📠",
IF({Auto-Status} = "Cancelled", "💀",
IF({Auto-Status} = "On Hold", "💤",
IF({Auto-Status} = "Released to Supplier/Development", "🚀",
IF({Release Date} = 0, "Release Date Needed",
DATETIME_DIFF({Release Date}, TODAY(), 'days')
)))))))

Maybe I should insert VALUE just around DATETIME_DIFF?

[Andrew_Enright]

Quick work-around: second field that’s just VALUE(FIELD 1).

It’d be nice to avoid the “extra” field tho.

[W_Vann_Hall]

Yeah, I don’t think Airtable ever allows mixed results in a formula field.

…which means the output of my ‘pretty-print’ routines won’t sort correctly, either. :expressionless:

Your quick work-around is a good idea, though. I have no idea how long it would have taken me to figure that out…

[Elias_Gomez_Sai]

I would not mix Status with Days left :grinning_face_with_smiling_eyes:

[Andrew_Enright]

In this use case, we don’t want to see days left for jobs that aren’t active. So we have to mix in a non-number to filter against for all jobs that are complete, etc. If we didn’t, our status board views would be cluttered with “past due” jobs that are actually just fine.

We have many other statuses; they aren’t in the code above because they are considered active, and thus do not need to be omitted.