Sort and group by cascading dates [not planned - but add the custom code to docs]

[Mauwzola]

⚠️ Please check that this feature request hasn't been suggested before.

• [X] I searched previous Ideas in Discussions didn't find any similar feature requests.
• [X] I searched previous Issues didn't find any similar feature requests.
• [X] I am only requesting a single feature. Multiple changes should be split into individual requests, with links between them.
• [X] I believe my requested feature will be generally applicable to other users, not just me: it is not uniquely tied to my personal workflow.

🔖 Feature description

It would be great if there were a built-in function (similar to NVL in Oracle) that allowed results to be sorted by a secondary (or tertiary) date if the primary date hasn't been set.

✔️ Solution

In Oracle, I'd write something like SORT BY NVL(scheduled, due) or even SORT BY NVL(NVL(scheduled, due),start). Probably no need for "NVL" within tasks. Simply allowing for a comma-separated list (within parentheses if necessary) would do the trick. So in the example below, tasks scheduled for April 15 would be grouped together with tasks due on April 15 but having no scheduled date.

not done due this month group by scheduled, due, start

❓ Alternatives

At present, I can add a second sort or group by statement, but that creates a nest sort or grouping.

SORT BY SCHEDULED SORT BY DUE

...is not the same as...

SORT BY SCHEDULED, DUE

📝 Additional Context

No response

[claremacrae]

Hi, thanks for the suggestion.

Please could we have an explanation that does not require understanding SQL?

The best way to explain it would be to:

1. provide a few carefully selected markdown task lines
2. show what the desired sort order display of those tasks would be, if this facility were provided.

The above should all be in text, so that they can be copied in to Obsidian. You can put them in fenced code blocks, for readability.

Many thanks.

[claremacrae]

I've edited the wording above slightly, to make it more general than just sort order.

[Mauwzola]

Thanks for the quick reply, Clare. Thought my verbal explanation would suffice, but I'll attempt an example.

Here's four tasks:

- [ ] Write up minutes from board meeting 🛫 2024-04-07  📅 2024-04-10
- [ ] Review my Reading folder on Gmail 🔁 every week when done 🛫 2024-04-09 ⏳ 2024-04-11
- [ ] Process snail mail 🔼 🔁 every week when done 🛫 2024-04-09 🛫 2024-04-09
- [ ] Wipe down kitchen cupboards  🔽 🔁 every 3 months when done 🛫 2024-04-07 ⏳ 2024-04-09 

If I write:

> group by scheduled
> group by due
> group by start

The output looks like this:

2025-04-09 Wednesday
No due date
2025-04-07 Monday
- [ ] Wipe down kitchen cupboards 🔽 🔁 every 3 months when done 🛫 2025-04-07 ⏳ 2025-04-09
2025-04-11 Friday
No due date
2025-04-09 Wednesday
- [ ] Review my Reading folder on Gmail  🔁 every week when done 🛫 2025-04-09 ⏳ 2025-04-11

No scheduled date
2025-04-10 Thursday
2025-04-07 Monday
- [ ] Write up minutes from board meeting 🛫 2025-04-07 📅 2025-04-10

No due date
2025-04-09 Wednesday
- [ ] Process snail mail 🔼 🔁 every week when done 🛫 2025-04-09

What I'd like to be able to do is write:

> group by scheduled, due, start

And have the output look like this:

2025-04-09 Wednesday
- [ ] Wipe down kitchen cupboards 🔽 🔁 every 3 months when done 🛫 2025-04-07 ⏳ 2025-04-09
- [ ] Process snail mail 🔼 🔁 every week when done 🛫 2025-04-09

2025-04-10 Thursday
- [ ] Write up minutes from board meeting 🛫 2025-04-07 📅 2025-04-10

2025-04-11 Friday
- [ ] Review my Reading folder on Gmail  🔁 every week when done 🛫 2025-04-09 ⏳ 2025-04-11
2025-04-07 Monday

As you can see above, the output is the same as a single group by statement. However, if there's no SCHEDULED DATE (the primary grouping field), the secondary grouping field (i.e., DUE DATE) is used if it exists (e.g., Thursday above). And if neither of those fields has a value, the tertiary grouping field (i.e., START DATE) is used (second task in Wednesday above).

Would love to see this implemented for sorts as well!

[claremacrae]

Thank you.

Please also show the output for how you envisage this for sorting?

[claremacrae]

Also, please could you try these two commands out (separately) and see if they are close enough for you?

group by happens
sort by happens

[Mauwzola]

The problem with HAPPENS in this context and others is that it always uses the earliest of the three dates. If there were a way to make it ignore the start date (which is almost always the earliest date), HAPPENS would be much more useful (to me at least).

As for a sort example, I'd like to be able to enter:

> SORT BY SCHEDULED, DUE, START

And get output that looks like this:

- [ ] Wipe down kitchen cupboards 🔽 🔁 every 3 months when done 🛫 2025-04-07 ⏳ 2025-04-09
- [ ] Process snail mail 🔼 🔁 every week when done 🛫 2025-04-09
- [ ] Write up minutes from board meeting 🛫 2025-04-07 📅 2025-04-10
- [ ] Review my Reading folder on Gmail 🔁 every week when done 🛫 2025-04-09 ⏳ 2025-04-11

As you can see above, the first task has a SCHEDULED DATE so that's what's used for the sort. Because the second task has no SCHEDULED or DUE DATE, its START DATE is used for sorting. Since the third task has a DUE DATE, that's what is used for sorting.

[claremacrae]

You can put them in fenced code blocks, for readability.

Transparency note: I have put the samples provided in to fenced code blocks, so they are easier to see.

[claremacrae]

... and easy to copy ...

[claremacrae]

Thanks again for the suggestion.

Thanks to your supplying text examples, I have been able to experiment and find that this is already possible in Tasks.

```tasks
group by function \
    {{! Get the dates, in order of priority: }} \
    const dates = [task.scheduled, task.due, task.start]; \
    {{! Find the first non-null date. Returns undefined if no date set: }} \
    const first = dates.find((date) => date.moment != null); \
    if (first) return first.formatAsDate(); \
    return '';
```

Make sure you Shift-paste this in to Obsidian, to avoid stray spaces being added at the end of each line, which will break the \ facility.

This gives me:

[Mauwzola]

Thanks for this, Clare. Aside from the dropping of the day's name, this appears to be the precise output I'm seeking. That said, Tasks has served me well to this point without me having to construct anything near as elaborate as this and I suspect many user of the Plugin wouldn't know where to start in trying to write this piece of code. So in an effort to make this functionality available to more people, I hope you'll at least consider implementing something similar to what I've suggested.

[claremacrae]

And here is how you could do it for sorting:

```tasks
sort by function \
    {{! Get the dates, in order of priority: }} \
    const dates = [task.scheduled, task.due, task.start]; \
    {{! Find the first non-null date. Returns undefined if no date set: }} \
    const first = dates.find((date) => date.moment != null); \
    if (first) return first; \
    {{! None of the dates has a value, so just return the first one, to ensure correct sorting }} \
    return dates[0];
```

I added a task with no date, to confirm the behaviour for that scenario:

[claremacrae]

Thanks for this, Clare. Aside from the dropping of the day's name, this appears to be the precise output I'm seeking.

Sure. The formatting is because I used first.formatAsDate().

The docs show how you can control the formatting of dates.

That said, Tasks has served me well to this point without me having to construct anything near as elaborate as this and I suspect many user of the Plugin wouldn't know where to start in trying to write this piece of code. So in an effort to make this functionality available to more people, I hope you'll at least consider implementing something similar to what I've suggested.

I understand what you are saying, and I sympathise.

And in fact, since releasing the custom grouping, filtering and sorting facilities, I have wanted to make some of their capabilities available without the need for scripting.

But that has got nowhere near the top of the priority list, given the huge number of requests in Issues and Discussions.

In the meantime, a handful of times a month I receive rather specific requests from users, such as this one, for which there would just never be enough time for me to make them all available. And it's often not completely obvious that large enough numbers of users would to use them, to make it worth the required effort to develop, test, document and maintain each new feature.

So the current compromise is to spend a few minutes experimenting with text supplied by users to show them how to use the powerful scripting capabilities.

I will add these examples to the documentation.

[claremacrae]

I found some simpler ways to do this (well, fewer lines...)

grouping

```tasks
group by function \
    const dates = [task.scheduled.formatAsDate(), task.due.formatAsDate(), task.start.formatAsDate()]; \
    return dates.find((date) => date !== "") ?? "";
```

sorting

```tasks
sort by function \
    const dates = [task.scheduled.formatAsDate(), task.due.formatAsDate(), task.start.formatAsDate()]; \
    return dates.find((date) => date !== "") ?? "9999-99-99";
```

[Mauwzola]

Thanks for this, Clare! Much more concise than your last solution (which continues to serve me well)...

...m

[claremacrae]

@Mauwzola you probably want to delete that comment as it contains personal info.