Formulas for bases sort/grouping order

Feature requests
1

Use case or problem

I’m using a bases view to manage tasks. I’m using a formula to group tasks into due date ranges. Examples include “in progress”, “today”, “Friday”, “next week”. I’d like to sort them in chronological order, not alphabetically.

Proposed solution

In the “group by” and “sort by” fields in a Base, the options are A>Z and Z<A, the proposal is to add the ability to enter a formula, or a comparison function, similar to JS array.sort().

Current workaround (optional)

Currently I prefix my groups with a number, so “00 Today”, “10 Next week”, etc. This works ok.

Sharing my formula here.

if(formula.Contexts.isEmpty(),
"00 Inbox",

if(due < today(),
"01 Overdue",

if(formula.Scheduled < today(),
"02 In Progress",

if(formula.Scheduled == today(),
"03 Today",

if(formula.Scheduled.format("W") == today().format("W") && formula.Scheduled == today() + "1d",
"04 Tomorrow",

if(formula.Scheduled.format("W") == today().format("W") && formula.Scheduled == today() + "2d",
"05 " + formula.Scheduled.format("dddd"),

if(formula.Scheduled.format("W") == today().format("W") && formula.Scheduled == today() + "3d",
"06 " + formula.Scheduled.format("dddd"),

if(formula.Scheduled.format("W") == today().format("W") && formula.Scheduled == today() + "4d",
"07 " + formula.Scheduled.format("dddd"),

if(formula.Scheduled.format("W") == today().format("W") && formula.Scheduled == today() + "5d",
"08 " + formula.Scheduled.format("dddd"),

if(formula.Scheduled.format("W") == today().format("W") && formula.Scheduled == today() + "6d",
"09 " + formula.Scheduled.format("dddd"),

if(formula.Scheduled.format("W") == (number(today().format("W")) + 1).toString(),
"10 Next Week",

if(formula.Scheduled.format("YYYY") == today().format("YYYY") && formula.Scheduled.format("M") == today().format("M"),
"11 This Month",

if(formula.Scheduled.format("YYYY") == today().format("YYYY") && formula.Scheduled.format("M") == (number(today().format("M")) + 1).toString(),
"12 Next Month",

if(formula.Scheduled.format("YYYY") == today().format("YYYY"),
"13 This Year",

if(formula.Scheduled.format("YYYY") == (number(today().format("YYYY")) + 1).toString(),
"14 Next Year",

if(formula.Scheduled.format("YYYY") > (number(today().format("YYYY")) + 1).toString(),
"15 Later",

"16 Someday"
))))))))))))))))
8 Likes
2

I agree, this would be a great feature. I have lots of song notes that I would like to group by album, sorted chronologically, but I can only group by album and sort alphabetical. Currently I group them by year, but that groups multiple albums together if they were released in the same year. I’d rather not add a number prefix to all my notes.

3

Hasn’t this feature request in fact been fulfilled? Using 1.10.3 , I was able to create a formula and select it as the property to group by. In fact, after creating the property, a column for it was automatically added, and clicking the header suggested “Group by” as an option.

4

Yes, you can do that, but the groups are always sorted alphabetically. The request is to support a custom comparison function for the sort.

1 Like
5

The more I think about this, the more it makes sense to me.

Can we rename the thread topic to be more clear? “Custom comparison functions for sorting order in bases” would be my suggestion.

6

It doesn’t look like I can edit it.

7

I would also really like this.

The proposed workaround of adding a prefix doesn’t work with link groups, as their formatting breaks and they aren’t clickable anymore:
image

8

I found a workaround for ordering without showing a number infront: Use an invisible html span.

So instead of doing:

if(topics == "Chess", 1+" Foo", 2+" Bar")

You could do:

if(topics == "Chess",
  html("<span style='display:none'>1</span>"+" Foo"),
  html("<span style='display:none'>2</span>"+" Bar"))

I still can’t get it to play nicely with wikilinks though.

1 Like
9

Brilliant, that works pretty well!

10

I had a similar problem recently and a working solution was to output the wikilink in a list with the other item. Perhaps that could work here?

11

Oh right that works, thanks!

Here is my full formula for invisible custom group order and working wikilinks:

[
  formula.topicOrder
    .map(
      if(
        topics.contains(value),
        [
          html("<span style='display:none'>" + index + "</span>"),
          link(value)
        ]
      )
    )
    .filter(value),
  topics
    .filter(!formula.topicOrder.contains(value))
    .map([html("<span style='display:none'>9999</span>"), link(value)])
].flat()

This requires another property (topicOrder) that returns a list of all the topics. The group order comes implicitly from the position in that list. Non-existent topics in the list are considered to come as the last group.

1 Like