Query any list item that contains a date field within this week

Help
1

What I’m trying to do

I was able to pull any list item that contains the name of the file using the code below. What I want to do now is query any list item that contains a date field within this week.

table rows.L.section as Heading, rows.L.text as Reference
from ""
flatten file.lists as L
where contains(L.text, this.file.name)
GROUP BY file.link as File

Things I have tried

I tried the query below, hardcoding the first day of the week but both only bring up the files, not the line items.

Table rows.L.section as Heading, rows.L.text as Reference
FROM ""
WHERE file.day.weekyear = date(2022-01-03).weekyear
SORT file.name

Table rows.L.section as Heading, rows.L.text as Reference
FROM ""
WHERE string(file.day.year) = split(this.file.name, "-W")[0] AND string(file.day.weekyear) = split(this.file.name, "-W")[1]
SORT file.name
2

The last two queries depends on a GROUP BY line, and a FLATTEN statement which are missing, so no wonder they don’t return anything useful.

Where and how is this date field you want to search for defined?

3

holroy thanks for the quick reply!

This is a query in a weekly note that finds all the references to the daily notes of that week. My daily note name format is YYYY-MM-DD.

4

And what’s the name of the weekly note? Our do you have the week defined in a property of the weekly note?

And finally, have you seen any of the forum post on the same subject such had been answered during the last few weeks?

5

The name of the weekly note right now is YYYY-MM-[W]ww (e.g. 2024-02-W06) but I’m open to changing it it would make life easier.

As for links, I can’t find everything I’ve searched but I have no programming background and honestly couldn’t figure out any of the answers I found. Here’s some of the types of posts I was looking at:

6

The following seems to list the correct daily notes then:

```dataview
LIST
WHERE regexreplace(this.file.name, "-[01][0-9]", "") = dateformat(file.day, "yyyy-'W'WW")
```

Here we strip out the month part of the weekly note file name (“2024-02-W06” → “2024-W06”), and compare it to a note where we use the file.day formatted into a “2024-W06” format. And voila, we see the daily notes belonging to that week.

Happy journaling! :smiley:

7

I’m not sure I’m being clear with my ask, this may be a better way of describing it. (also thanks for your help so far)

I’m looking for an expression that will dyamically pull any list item that has an outlink of every day of the week for this weekly note.

A dynamic version of this:

WHERE contains(L.text, “2022-01-02”) OR contains(L.text, “2022-01-03”) OR contains(L.text, “2022-01-04”) OR contains(L.text, “2022-01-05”) OR contains(L.text, “2022-01-06”) OR contains(L.text, “2022-01-07”) OR contains(L.text, “2022-01-08”)

8

I feel like this gets me close - but I just don’t have the knowledge to figure out how to put this together.

9

If I understand correctly, you’re talking about list items with outlinks like:

  • blah blah [[2022-01-02]]

? Using the regex and luxon format holroy gave, you can just explicitly add durations to get the 7 days of week and do a contains check for each:

table rows.L.section as Heading, rows.L.text as Reference
from ""
flatten file.lists as L
where contains(L.text, dateformat(date(regexreplace(this.file.name, "-[01][0-9]", ""), "kkkk-'W'WW"), "yyyy-MM-dd")) OR 
	contains(L.text, dateformat(date(regexreplace(this.file.name, "-[01][0-9]", ""), "kkkk-'W'WW") + dur(1 day), "yyyy-MM-dd")) OR
	contains(L.text, dateformat(date(regexreplace(this.file.name, "-[01][0-9]", ""), "kkkk-'W'WW") + dur(2 day), "yyyy-MM-dd")) OR
	contains(L.text, dateformat(date(regexreplace(this.file.name, "-[01][0-9]", ""), "kkkk-'W'WW") + dur(3 day), "yyyy-MM-dd")) OR
	contains(L.text, dateformat(date(regexreplace(this.file.name, "-[01][0-9]", ""), "kkkk-'W'WW") + dur(4 day), "yyyy-MM-dd")) OR
	contains(L.text, dateformat(date(regexreplace(this.file.name, "-[01][0-9]", ""), "kkkk-'W'WW") + dur(5 day), "yyyy-MM-dd")) OR
	contains(L.text, dateformat(date(regexreplace(this.file.name, "-[01][0-9]", ""), "kkkk-'W'WW") + dur(6 day), "yyyy-MM-dd"))
GROUP BY file.link as File

However, this isn’t checking for outlinks, it’s just checking for list items that contain the date at all. So having a list item with just “2024-02-05” would flag it as well. Also, this is going to do a check on every single list item across all your files, I would narrow it down to a folder or something otherwise that’s an extremely intensive operation.

Dataviewjs block with a for loop would definitely be cleaner (a “date > weekStart && date <weekEnd” check would be cleanest), but this should at least compile for you.

10

You do want the query in the weekly note, but extract list item with links to the daily notes of that particular week?

I can work with that, it’s not going to be as neat, but it can be done. Just want you to verify that’s the case you really want.

11

@holroy - that’s exactly right. The query lives in the weekly note and extracts the list items of the daily notes for that particular week.

Is there a way to make @TurkeyTips’s query for a range of dates rather than specific dates (just in case this could be modified for all notes in a month or a custom date range?)

I’m able to run dataviewjs if it’s easier but I’ll be super out of my league for troubleshooting.

And lastly, to address @TurkeyTips performance concern (which is super valid) could we pull onelinks for the same range in the from as well? (I’m trying to help)

I want to thank you both for working through this, if we get this to work it’s the cornerstone to a re-vamped work Vault.

12

Try the queries below, where the first query gives an overview of what the second query actually is using when it’s picking out just this weeks links.

## The list them all query

```dataview
TABLE WITHOUT ID
  item.section as "Heading", item.text as "Reference"
  , map(item.outlinks, (out) => dateformat(date(out), "yyyy'-W'WW")) as outlinks
WHERE file = this.file
FLATTEN file.lists as item
FLATTEN regexreplace(this.file.name, "-[01][0-9]", "") as thisWeek
```

## Just those from this week
```dataview
TABLE WITHOUT ID
  item.section as "Heading", item.text as "Reference"
  , map(item.outlinks, (out) => dateformat(date(out), "yyyy'-W'WW")) as outlinks
WHERE file = this.file
FLATTEN file.lists as item
FLATTEN regexreplace(this.file.name, "-[01][0-9]", "") as thisWeek
WHERE any(map(item.outlinks, (out) => dateformat(date(out), "yyyy'-W'WW") = thisWeek))
```

In my test vault this results in the following output:

image
image1217×556 66.7 KB

As can be seen in the first query, we’ve now mapped any dates to the “correct” weekly format which we are going to use for checking in the second query. Also note that in the second query we map the equality of the link formatted into the weekly format against the current weeks text. If any() of the outlinks matches that inner match we keep the entire item, and if not, we just forget it.

The same approach can be used when checking against date ranges or months or whatever from a list. Find what you want to match against, similar to doing the FLATTEN ... as thisWeek, and do a any(map( ... )) where the inner match is the match you want to execute against each item in your list.

Hope that makes sense, and is finally going to get you what you want. :smiley:

1 Like
13

@holroy
High level this is… :face_holding_back_tears: Amazing!

I’ll have to test it out when I’m done with work. Thank you for sharing your knowledge on this thread!

14

This worked out really well, thank you!

15

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.