Querying all weekly notes within a month, and all monthly notes within a year

Help
,
1

What I’m trying to do

Hello! I’m trying to make a dataview to search through all of my weekly notes (tagged #weekly) for a given month for times where I define “event”.

I have examples for my weekly notes (searching through #daily for the week) and my yearly notes (searching through #monthly for the year), but haven’t figured out how to get it working for my monthly notes yet.

Searching through #daily for the given week:

TABLE without ID
	file.link AS "Daily Log",
	dateformat(file.day, "EEEE") AS "Day",
	event AS "Details"
FROM #daily 
WHERE event != null
	AND event != ""
	AND file.day >= date(<% moment(tp.file.title).startOf('isoweek').add(-1, 'day').format("YYYY-MM-DD") %>)
	AND file.day <= date(<% moment(tp.file.title).startOf('isoweek').add(5, 'day').format("YYYY-MM-DD") %>)
SORT file.name ASC

image
image1412×292 31.2 KB

Searching through #monthly for the given year:

TABLE without ID
	file.link AS "Monthly Log",
	event AS "Details"
FROM #monthly  
WHERE event != null
	AND event != ""
	AND contains(file.name, "<% tp.file.title %>")
SORT file.name ASC

The overall goal is that I want to push logged events from my daily notes to my weekly notes. From there I’ll re-log events I consider more important and push those to my monthly notes, and from there I’ll re-log them to push to my yearly notes. I also want to use whatever the solution is in order to push weekly summaries to my monthly logs.

Things I have tried

So far I’ve only managed to have the dataview search through my #daily notes accurately, using these:

AND file.day >= date(<% moment(tp.file.title, 'YYYY-MM').startOf('month').format('YYYY-MM-DD') %>)

AND file.day <= date(<% moment(tp.file.title, 'YYYY-MM').endOf('month').format('YYYY-MM-DD') %>).

I’m guessing the solution might involve using the following Templater string, or something similar:

<% moment(tp.file.title, 'YYYY-MM').startOf('month').format('gggg-[W]ww') %>)

But I’m not sure exactly how to use it for my intended purpose, as that and the endOf version of the Templater string only grabs the first and last week of the month. The problem would be getting the weeks in-between.

Any help would be appreciated! I’m pretty sure I’m missing a simpler solution here.

Date formats

Daily: YYYY-MM-DD
Weekly: gggg-[W]ww
Monthly: YYYY-MM
Yearly: YYYY

2

After a very quick readthrough, so sorry if I missed some parts, I’ve got three main comments:

  • Do you store the (start) date of the week or the month in your respective notes?
  • It seems like you want to pull the result of a query from another query. That is hard, and you should rather redo the query for a larger scope.
  • Are you using dv.view() to call this queries? Or do you copy the query into each and every note?

The reason I ask whether you store the date in your weekly and monthly notes, is that it enables better date handling in later queries. If you only store either the week number or month, it’s often a little harder to do the other scripts.

The second comment when you say to review the weekly notes, that goes into the monthly notes for reviewing, for eventually to be propagated to the yearly notes. What do you move into the next level of notes? Is that a rewritten copy of the event, or are you aiming to use the query result somehow all the way?

Lastly, having specific queries in daily, weekly or monthly notes could very easily cause you grief down the line, when you want to redo parts of it. So in most cases I would advocate that you either use dv.view() to call a script containing your script, or to use some variant over dv.execute???() in combination with loading a script from a dedicated note(s). In either case would this allow you to change the query once, and the changes would be picked up the next you view a note using those queries.

1 Like
3

  • I don’t store the start date of the week or month in my respective notes; BUT I have very few weekly and monthly notes, so I can start now if that would make this easier!

  • I’ll be rewriting copies of the event rather than pulling from the existing query. Here’s an example from a weekly note:

    image
    image1438×780 70.5 KB

  • I’ve been copying the query into each and every note—I have a template set up for it, so when I want to edit it, I edit the template. Existing notes end up stuck with the old code with this method unless I manually change it, though. I’m unfamiliar with using dv.view()

I’ve definitely experienced some grief when I’ve wanted to edit the queries, and throughout fiddling with my current code I’ve just been manually editing each and every one of my dataview queries for this since I have so few, but that’ll be less convenient once I have an abundance of these.

dv.view() is DataviewJS, right? I’ll look into the docs and some tutorials so I can get started on learning it. I’ve just been using plain Dataview the whole time.

4

Even though dv.view() is dataviewjs, there is nothing in way of doing very boilerplate dataview in it.

I’ve suggested many times in the forum code ala:

```dataviewjs

const result = await dv.query(`
TABLE without ID
	file.link AS "Monthly Log",
	event AS "Details"
FROM #monthly  
WHERE event != null
	AND event != ""
	AND contains(file.name, "<% tp.file.title %>")
SORT file.name ASC
`)

if ( result.successful ) {
  dv.table(result.value.headers, result.value.values)
} else 
  dv.paragraph("~~~~\n" + result.error + "\n~~~~")
```

Here it is shown as dataviewjs, so you wouldn’t have the codeblocks around when using it in a dv.view() script. But as you can see, you can use the ordinary query, and play around with that, and then just keep the other parts around to get some error handling.

Another benefit of this approach, which is also illustrated in other forum posts, is that you can rather easily add totals for your queries if that is something you’re likely to do. :slight_smile:

5

The monthly query of the weeks, is a little tricky as you either need to decide which month any given week belongs to, or include any week crossing over into the next month to belong in both.

If you do the latter, one way to limit your query would simply be to generate the query around week file names larger than the week of the first day in the month, and less than the last day of the month. That is given your week file name is in alphabetic order.

6

I think I’ve got it now! I cobbled it together based on including crossover weeks to belong in both months. Thanks so much for the help!

  1. I set the week number for each weekly note in a property named ‘week’

  2. I made a Templater string to extract the first week of a month:
    <% moment(tp.file.title, 'YYYY-MM').startOf('month').format("ww")%>

  3. Same as 2 but for the last week of the month: <% moment(tp.file.title, 'YYYY-MM').endOf('month').format("ww")%>

  4. I adjusted the existing code I had and got this for my monthly note template dataview query:

TABLE without ID
	file.link AS "Weekly Log",
	event AS "Details"
FROM #weekly  
WHERE event != null
	AND event != ""
	AND week >= <% moment(tp.file.title, 'YYYY-MM').startOf('month').format("ww")%>
	AND week <= <% moment(tp.file.title, 'YYYY-MM').endOf('month').format("ww")%>
SORT file.name ASC
1 Like
7

Quick correction! Forgot to have a line that ensured the weekly note was actually from the right year.

TABLE without ID
	file.link AS "Weekly Log",
	log-event AS "Details"
FROM #weekly  
WHERE log-event != null
	AND log-event != ""
	AND contains(file.name, "<% moment(tp.file.title, 'YYYY-MM').format("YYYY") %>")
	AND week >= <% moment(tp.file.title, 'YYYY-MM').startOf('month').format("ww")%>
	AND week <= <% moment(tp.file.title, 'YYYY-MM').endOf('month').format("ww")%>
SORT file.name ASC