Hi all,
This turned into a long post! The TL;DR is at the bottom.
Thanks for your question. It looks like you’re trying to track events by defining them inline with your notes. I do the same thing to highlight events in histories so I can see all the events in a single timeline. However, I approach it a little differently than you do.
The Problem
I think the problem you’re running into is that you’re defining your What
and When
as separate fields defined multiple times in the same page. As you guessed, Dataview turns them into lists, but there’s no association between them; that is, Dataview has no idea which When
goes with which What
. Even if you tried to FLATTEN them, you’d have to play some games with row numbers in order to try keep the times and text together.
Craig’s Solution: Single-Field Events
What I do is combine both both attributes into a single Event
field, which always begins with a sortable timestamp and is followed by the text of the event.
For example:
Event:: 2022-06-10 Joe and the party finally reach the Spire
Event:: 2022-06-11 The party is taken out by the Guardian
Using this approach, the event date and text always stay together, and it’s easy to collect and sort events that happen on different pages. Because each event starts with a timestamp, they’re easy to sort in date order. Here’s an example of a regular DQL query that collects all your events in a given folder and turns them into a single, unified timeline (you can see the results of this query at the bottom of the post):
```dataview
TABLE WITHOUT ID
split(Event, " ", 1)[0] as Date,
regexreplace(Event, "^\S+", "") + " " + file.link as Event
FROM "Notes"
WHERE Event
FLATTEN Event
SORT Event
```
So what’s going on with this query? Let’s look at it a piece at a time and break down what’s going on here.
Get Pages with Events
TABLE WITHOUT ID
FROM "Notes"
WHERE Event
This part is simple. Grab all notes from the “Notes” folder, and grab only the ones that have at least one Event field.
But we have a problem; the individual events are tucked away as a list inside the Event field of each page. How do we get them out?
FLATTEN
FLATTEN Event
This part’s a little tricky. FLATTEN Event
changes how the rows are broken up, changing them from page-based to field-based.
After FLATTEN Event
, we now have one row for each Event:
SORT
SORT Event
The SORT re-orders the events by date instead of the order they are on the page. This works because each Event field starts with a sortable timestamp:
Extract Fields
Finally, now that we’ve got one event per row sorted in timestamp order, we can pull the “When” and “What” fields back out of it.
split(Event, " ", 1)[0] as Date,
This uses the split() function to pull out the text before the first space and put it in a column called “Date”.
regexreplace(Event, "^\S+", "") + " " + file.link as Event
This does three things:
- Uses the regexreplace() function to replace the date with an empty string,
- appends a link to the file the event appeared in so that the user can click it, and
- finall puts the text into a column named “Event”.
Example
Finally, here’s a practical example from my reference vault:
Final Thoughts
This isn’t the only way to do this, of course. You can do some real magic with dataviewjs and turn these Events into lists, group them by date, and more. But I thought this might help you understand what Dataview is doing under the hood and inspire you to even better ideas!
Hope this helps,
Craig