Separate sorting value from display value

[kherge]

Use case or problem

When creating a formula for grouping or sorting, I want Bases to use one value for sorting and a different value for displaying.

Proposed solution

1. When editing a formula, rename the existing “Formula” field to “Display formula”.
2. Add a new field named “Sort formula” below the “Display formula” field and use “optional” as the placeholder.
3. When Bases evaluates a formula for sorting, it will first use the sort formula and then fallback to the display formula when not provided.

Current workaround (optional)

There is no workaround. For example, I cannot display only a relative time (string) but use the full date and time (numeric) when sorting or grouping by the same column.

Related feature requests (optional)

Formulas for bases sort/grouping order

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 pre…

[dawni]

You can sort by a field that isn’t displayed. No need for an extra layer on top of it, if I’m understanding your use case.

Say you want to sort by a property’s values as they’re written in the YAML but show a custom display. Enter the property name for sorting. Then create a property formula with your desired format and display that as a column.

Or vice versa.

---

I’m not sure that you’re talking about this, but just in case: One place you do need a workaround is when you want to display a group title that doesn’t suit your desired group order. For that, a bit of html() “display: none;” on your secret sorting text can give you control over both the display and the group order.

[kherge]

Sorry that wasn’t clear, I am primarily interested in this feature for grouping.

As I was working on a response, I realize that I need 3 separate things. I don’t think I properly covered that need in my OP. Using file.mtime as an example, I need:

1. the ability to group by file.mtime.format("YYYY-MM-DD")
2. the ability to sort the group by file.mtime
3. the ability to display the group as file.mtime.format("MMMM Do, YYYY")

Could using html() achieve this? If it can, could you please provide an example formula?

---

Revising my request, I would probably request a new function:

group(
  key: number | string,
  sort?: number | string,
  display?: number | string | Link
)

The sort and display values would default to key if no value is provided.

I would use something like the following formula:

formulas:
  Group: group(file.mtime.format("YYYY-MM-DD"), file.mtime, file.mtime.format("MMMM, Do, YYYY"))

[dawni]

May the muses of coding decency forgive me. A workaround for your three requirements:

```base
formulas:
  group: "[html('<span style=\"display:none;\">file.mtime.format(\"YYYY-DD-MM\")</span>'), file.mtime.format(\"MMMM Do, YYYY\")]"
views:
  - type: table
    name: Table
    groupBy:
      property: formula.group
      direction: ASC
    order:
      - file.name
      - file.mtime
    sort:
      - property: file.mtime
        direction: ASC

```

[kherge]

This workaround worked very well, thank you for the suggestion!