I'd like to sort my tasks using dataview and show them in a note

[AriesOdyssey]

Things I have tried

I have tried looking up the dataview reference and sort out the code by myself. I tried using contains command and group. but nothing seems to work. (I know a bit of java, but still a beginning programmer). I also tried looking online for answers but I haven’t found it yet. Very sorry if I missed it.

What I’m trying to do

I use periodic notes plugin to get a note (title is the date) every day with a certain template. This template contains 2 tasks that are automatically made every day:

• [ ] Gym
• [ ] Play guitar

Now In my daily notes I want to be able to create additional tasks for stuff i want to do that day. for example:

-[ ] fix dataview table.

Then I have a seperate folder called ¨Good Habits¨

I want to display per day (so per note in my daily notes folder) whether I went to the gym or not and whether I played my guitar, but not the other tasks that are in the daily note.

How would i go about this? Thanks for any help in advance!

[mnvwvnm]

Three things you need to clarify:

1. what’s the real format of your daily notes title
2. “Gym” and “Play guitar” are a fixed text?
3. what the format of the wanted table? (maybe a mockup)

[AriesOdyssey]

1. Format for daily note title is ‘YYYY-MM-DD-dddd’.
   For example the title of the daily note of today is 2022-11-12-Saturday

2. ¨Gym¨ and ¨Play guitar¨ are fixed text: Everyday a new daily note is automatically created and put in the daily notes folder. This note always exactly contains
   -[ ] Gym
   -[ ] Play guitar

and I want to be able to add tasks that wont show up in the table or list.

3. Mockup would be something like this:

cross means that i did not complete the task, checkmark means i completed the task

4. Now also wondering if you could do something like this where you count the longest consecutive streak of checkmarks. Where streak is consecutive days I played guitar or went to the gym

[mnvwvnm]

Global table

About the first part (a global table), according the defined formats in your mockup (the date format and the emojis), you can explore this query:

TABLE WITHOUT ID
	dateformat(rows.file.day, "d LLLL, yyyy") AS Day,
	rows.myTasks.text AS "tasks text",
	choice(filter(rows.myTasks, (r) => r.text = "Gym").text[0], "✅", "❌") AS "Went to Gymn",
	choice(filter(rows.myTasks, (r) => r.text = "Play guitar").text[0], "✅", "❌") AS "Played Guitar"
FROM "your\folder\path"
WHERE file.tasks
FLATTEN file.tasks AS myTasks
WHERE myTasks.text = "Gym" OR myTasks.text = "Play guitar"
WHERE myTasks.completed
GROUP BY file.link
SORT rows.file.day ASC

notes:

1. replace your\folder\path by your real daily notes folder path
2. dateformat(rows.file.day, "d LLLL, yyyy") AS Day - in this case I formatted the implicit field file.day (a date field that exists when you have a title - or part of the title - with the format “YYYY-MM-DD”) according the output in your mockup. You can change the output with other luxon tokens.
3. rows.myTasks.text AS "tasks text" - this part is intended to test the filtered tasks. you can remove this expression.
4. Any future question about dataview I only reply if you demonstrate that you’ve read the plugin documentation and have tried simple dataview queries. (this one isn’t a good example to learn)

[AriesOdyssey]

Thanks man! Greatly appreciated. I think I can sort out the rest once I’ve dived further into the documentation. Got a bit overwhelmed yesterday indeed.

[mnvwvnm]

About the “longest consecutive streak” I guess you can’t do that in DQL.
It’s possible to “count” the total of “rows” or a lis/array of values. But in your case - group sequences and select only the longest - I think that implies special javascript code.
I’m not versed in JS (I’m not programmer or similar, just a curious) and I don’t know any default dataviewjs function that can do that: //(group - stop - group - stop - …)//; //select the longest//; …
But, I guess, it’s a nice challenge to you explore js logic.

[AriesOdyssey]

Thanks for letting me know, saves me some time. Half the fun of obsidian is finding out and learning stuff like this.

[AriesOdyssey]

For anyone wanting to do the same here’s a little adjustment that made it work for me (Still a major thanks to nmvwvnm for doing the heavy lifting):

TABLE WITHOUT ID
	dateformat(rows.file.cday[0], "dd LLLL, yyyy") AS Day, 
	choice(filter(rows.myTasks, (r) => r.text = "Gym").text[0], "❌", "✅") AS "Went to Gym", 
	choice(filter(rows.myTasks, (r) => r.text = "Play guitar").text[0], "❌", "✅") AS "Played Guitar"
	
FROM "your\folder\path" 
WHERE file.tasks 
FLATTEN file.tasks AS myTasks 
WHERE myTasks.text = "Gym" OR myTasks.text = "Play guitar" OR myTasks.completed
GROUP BY file.link
SORT rows.file.day ASC

What I changed

dateformat(rows.file.cday[0], “dd LLLL, yyyy”) AS Day
instead of
dateformat(rows.file.day, “d LLLL, yyyy”) AS Day.

This resulted in

instead of

Swapped and in the choice functions.

(I don’t know why but this made me get a when i completed a task and a when i didnt complete the task, instead of the other way around. )

WHERE myTasks.text = “Gym” OR myTasks.text = “Play guitar” OR myTasks.completed

instead of

WHERE myTasks.text = “Gym” OR myTasks.text = “Play guitar”
WHERE myTasks.completed

(again don’t know why, but the second one caused the table to have no data)

---

My setup if anyone is wondering how they might be different and it doesnt work for them:

“1. Daily notes” is my “your\folder\path”

An example of a daily note:

[mnvwvnm]

@AriesOdyssey

WHERE myTasks.text = "Gym" OR myTasks.text = "Play guitar" OR myTasks.completed

You have all that issues (duplications in “Day” with file.day; swapped choice functions; etc.) because you changed WHERE myTasks.completed to WHERE myTasks.text = “Gym” OR myTasks.text = “Play guitar” OR myTasks.completed.

@AriesOdyssey

again don’t know why, but the second one caused the table to have no data

WHERE myTasks.completed filter the tasks with the exact text “Gym” or “Play guitar” to only those that are checked. Any change in this filter have consequences in the choice function.

I don’t know why you have no data with the original query… only debugging concrete cases/files.

edit:
And maybe you have no data because the cases you don’t have any of the two tasks checked!!! I guess the issue is here. If you want to see also that days we need to change the code

[mnvwvnm]

this one:

TABLE WITHOUT ID
	dateformat(rows.file.day, "d LLLL, yyyy")[0] AS Day,
	choice(filter(rows.myTasks, (r) => r.text = "Gym" AND r.completed).text[0], "✅", "❌") AS "Went to Gymn",
	choice(filter(rows.myTasks, (r) => r.text = "Play guitar" AND r.completed).text[0], "✅", "❌") AS "Played Guitar"
FROM "your/folder/path"
WHERE file.tasks
FLATTEN file.tasks AS myTasks
WHERE myTasks.text = "Gym" OR myTasks.text = "Play guitar"
GROUP BY file.link
SORT rows.file.day ASC