Dataview: Can it search for a backlinked note within a backlinked note?

Hi, I’ve been using Obsidian for a little while now, but recently I’ve started trialling using it for a creative writing project and going a bit deeper into its features. I like my vault as vanilla as possible, so I’m only adding plugins if I actively have a problem and the plugin solves it. And right now I have a problem, and I’ve been told that Dataview might be able to solve it.

I’ve looked at a ‘Beginners Guide’ to Dataview online, as well as having a look at the query builder tool, but I haven’t found anything that’s helped so far. I suspect I’m probably asking for something quite complicated, because that’s what tends to happen to me. Also looked at the documentation but just got rather confused.

I have three types of notes that are relevant here: event notes, character notes and chapter notes.

  • Event notes are basically a note that describes the overarching plot event. E.g. the current event note is basically a summary of the 1805 Austerlitz campaign. These are tagged as #event.
  • Character notes just contain information about characters in the story that appear enough to get one. These are tagged with either #major-character or #minor-character. These also contain backlinks to other characters that they know well, although I’m considering removing those and just using non-linked lists for that instead.
  • Chapter notes are where I store the chapters. At the top of each one I have a properties table with the properties ‘year’, ‘main characters’ and ‘event’. ‘Year’ is just a text field, ‘main characters’ contains backlinks to the notes of the characters that appear in that chapter, and ‘event’ contains a backlink to the relevant event note.

I do it like this so I can keep track of which characters are appearing in which chapters, and how many chapters are dedicated to each event. However, I’d like to step up the tracking more by finding a way to find out which characters have appeared during which event and how many times. The way I’ve thought of doing this is by having a query look for all chapters that backlink to the event note, and then checking those chapters and returning which characters appeared in those chapters (i.e. which characters are backlinked in the chapter note).

Ideally I’d also be able to see how many chapters within the event a character has appeared in, maybe with a list of which ones they’re in? I don’t know the limitations of Dataview so that may well be far too complicated for what the plugin can offer.

Is this something Dataview can help me accomplish, either in the way described above or by a different method? I’d rather not be stuck with just having to manually list each character in the event note every time they turn up because not only would that take time, it would also just look rather messy. While it’s not the biggest problem right now, this is a long-haul project and I anticipate this issue growing as the cast develops (read: I kill them off and replace them).

Thank you so much to anyone who’s read through my very long post, and to anyone who can help. I realise this is probably not a normal problem to have.

2 Likes

Sounds like a great writing project. Dataview can help you see links between characters if you decide to keep those in your notes, especially if you type the links into list items, à la:

- best friends with [[Claire]]
- not keen on [[Emperor Francis]]] since 1805

… but I can also see how a plain list of characters might be easier for process: less linking, more writing.

If I understand them correctly, your asks are all doable.

Try this in your event note:

```dataview
table WITHOUT ID file.link as chapters, main-characters
where event = this.file.link
sort file.name asc
```

I think you mean like this, also in the event note:

```dataview
table rows.file.link as chapters
where event = this.file.link
flatten main-characters
group by main-characters
```

Example result:

3 Likes

Hello.

There are various articles online by authors who use Obsidian (and Dataview) to write and track their novels, which you might find insightful.

As an example, the article linked below, by novelist @pdworkman, explains one approach to tracking arcs with Dataview:

3 Likes

Thank you so much, that’s really helpful and pretty much did exactly what I wanted! However, is there a way to have this query work in a separate note that isn’t the event note? I’d like to try to keep my Dataviews separate from my notes if possible to maintain the markdown portability of the vault, so that in the event I want to open it with a different editor the notes themselves are still “clean” and I can just disregard the likely unusable Dataview notes.

Hello.

You could place the second query in any note and it would identify all files that match, for example, the key–value pair of: event: "[[Event 1]]"

```dataview
table WITHOUT ID file.link as chapters, main-characters
where event = this.file.link
sort file.name asc
```

```dataview
table WITHOUT ID file.link as chapters, main-characters
where event = [[Event 1]]
sort file.name asc
```

Would that suit your needs?

I’m slightly confused by your terminology. What do you mean with backlinked? If “Note A” has a link to “Note B”, that’s not a backlink as I understand it. It’s just a link. To me the backlink would be when you’re talking about “Note B”, which has a backlink from “Note A”.

Following this clarification where are your actual links written? Do the chapter notes hold links to both the characters and the events? Are these links stored in a property or just in the free text of the note?

What Guapa said :point_up_2:

So in each of the snippets I gave you, just replace

where event = this.file.link

with

where event = [[name-of-your-event-note]]

Then you can place the table in any note you want:

In the same vein, to show all chapters and all character appearances, remove the WHERE statement and add from #chapter:

## all chapters

```dataview
TABLE WITHOUT ID file.link as "chapters", main-characters as "main characters", event as "events"
FROM #chapter 
SORT file.name ASC
```

## all character appearances

```dataview
TABLE WITHOUT ID main-characters AS "main characters", rows.event AS "events", rows.file.link AS "chapters"
FROM #chapter
FLATTEN main-characters
GROUP BY main-characters
```

That version is cleaned with proper naming conventions—which I didn’t use the first time, sorry.

Output from the “all chapters” query:

1 Like

Sorry, I did try to make sense but it didn’t work.
The character notes contain links to other character notes in the text.
The chapter notes (through the properties table) contain links to character notes and the event note. There are no links in the text itself, only through the properties.
The event note has no relevant links to this situation (it contains a link to the note about the regiment in the text, but the link between that note and other notes isn’t something relevant to my problem).

For the future, how should I word something like this to make sure people can understand me on here?

Thank you so much for your help and for explaining the functions, the all chapters query is super helpful too. Thank you also to @Guapa for the explanation.
I do have a question about it, though. As you can see below it lists every instance of the event appearing in the list. Is there a way to just have the name of the event listed once with a count of how many times it’s appeared next to it (e.g. Austerlitz Campaign (3)) instead? I looked at the documentation again and saw some functions in there, but I don’t know how to use them so I don’t know if they would help.

Also, I’ve noticed that some of the files are listed in reverse number order or just incorrect order - is this something I can fix by adding a line to the query too or is this just a thing that happens? This happens in:

  • The ‘all character appearances’ section: events are in reverse number order. I tried adding ‘sort rows.file.link asc’ to force the chapter row to sort by ascending, but this seems to have made it sort from least chapters in a row to most chapters in a row (so basically character with least appearances to character with the most appearances).

  • Also, the chapters column in the character appearances tables (both in the all chapters section and in both of the event sections) will sometimes randomly be out of order regardless of how it’s trying to sort the chapters. This does go away if I close and reopen Obsidian until it sorts right, is this just a thing that happens sometimes?

1 Like

I forgot to mention that this only happens in the ‘all character appearances’ section. I also meant to write ‘chapters are in reverse number order’, not events. The individual event summaries have the chapters in ascending order as expected.

You figured right that SORT rows.file.link sorted by the length of that list. To sort by chapter name, use SORT file.name, and do it before grouping into rows. Like this:

FLATTEN main-characters
SORT file.name ASC
GROUP BY main-characters

Putting that SORT before that FLATTEN would also work.

I don’t know why a column would be in a different order after restarting Obsidian. That’s a question for someone who knows more.

I deleted the test notes I was using and can’t directly check on your other question, but if you want to try to work it out, take a look at unique() and length(). Like this:

In the “all character appearances” query, on the first line, replace

rows.event AS "events"

with

unique(rows.event) AS "events"

and see that it removes repeated values. I’m not saying that putting it there is your solution, just showing tools to try to figure it out. On that same line, replace

rows.chapter AS "chapters"

with

length(rows.chapter) AS "chapters"

and see that you get a count instead of the list items.

Those functions might help.

Thanks for the tip on sorting, that is fixed now. Still randomly goes out of order, but I guess that is to remain a mystery.
As for the functions, I figured I might as well post what I’ve tried so far as it’s about time I went to bed and this can serve both as a record for myself (to remember what I actually tried) and for anyone who turns up here and is willing to give some more pointers or hints overnight.

I figure that what I need to do is use unique() to remove duplicate entries of the events, and then somehow combine it with length() to stick a counter at the end of the now unique entry.

Attempts

```dataview
TABLE WITHOUT ID main-characters AS "main characters", unique(rows.event) AS "events", length(rows.event) AS "count", rows.file.link AS "chapters"
FROM #chapter
FLATTEN main-characters
SORT file.name ASC
GROUP BY main-characters

Removes duplicate entries of the event note name, and gives a “count” column of the total number of chapters a character is in. Close, and I might actually add that to the table later on, but no.

```dataview
TABLE WITHOUT ID main-characters AS "main characters", unique(rows.event) AS "events", length(rows.chapter) AS "chapters"
FROM #chapter
FLATTEN main-characters
SORT file.name ASC
GROUP BY main-characters

Removes repeated values but replaces the chapter list with a count, as you said. Just putting it here mostly so I remember I already tried it.

```dataview
TABLE WITHOUT ID main-characters AS "main characters", unique(length(rows.event)) AS "events", rows.file.link AS "chapters"
FROM #chapter
FLATTEN main-characters
SORT file.name ASC
GROUP BY main-characters

Broke it. Definitely not. Did this happen because unique() can only take an array whereas length() contains an array and an object?

```dataview
TABLE WITHOUT ID main-characters AS "main characters", length(unique(rows.event)) AS "events", rows.file.link AS "chapters"
FROM #chapter
FLATTEN main-characters
SORT file.name ASC
GROUP BY main-characters

Doesn’t break it, but replaces the event names with a count. I guess length() can handle the presence of just the array whereas the other way around doesn’t work.

```dataview
TABLE WITHOUT ID main-characters AS "main characters", unique(rows.event) AND length(rows.event) AS "events", rows.file.link AS "chapters"
FROM #chapter
FLATTEN main-characters
SORT file.name ASC
GROUP BY main-characters

Makes the event column return “true”. I’m guessing I just made it check that both of those functions can be satisfied or something, and it’s returning true because they can?


Today’s Conclusion

For tonight I am leaving the query as follows, as it’s the neatest so far and at least doesn’t look terrible even if I haven’t figured out the length() bit yet:

```dataview
TABLE WITHOUT ID main-characters AS "main characters", unique(rows.event) AS "events", rows.file.link AS "chapters"
FROM #chapter
FLATTEN main-characters
SORT file.name ASC
GROUP BY main-characters

I’m also thinking that maybe I could make use of the WHERE data command somehow, like “WHERE length(rows.event) > 1” or something, but I haven’t managed to use that in any way that doesn’t just break the table yet.
I also found this forum post, which I think is probably slightly close to what I’m trying to do but I’m not sure how I’d implement the solution from there in my case, as that solution starts a whole new table.

More hints welcome, I’ll get to them tomorrow at some point. I feel like I need some sort of equivalent to Python’s print() function that I can append to the end of the line establishing the events column or something.

Yes to the first part about unique() taking arrays. length(rows.event) is a number.

You can check an object’s type with typeof(). If you want to, try it with typeof(length(rows.event)) in that TABLE statement; it’ll return a column full of the word “number”.

Yep, you requested a boolean value. To concatenate, use the + symbol:

unique(rows.event) + " " + length(rows.event) AS "events"

Though, I think that will turn the array of unique values into a string in order to concat; not sure.

Just tried this and while it does start to accomplish the goal of having a counter within the same field, it’s a count of all the chapters the character is in listed after the last item in the events column rather than having a counter of the chapters from that event next to each event.

```dataview
TABLE WITHOUT ID main-characters AS "main characters", unique(rows.event) + " " + length(rows.event) AS "events", rows.file.link AS "chapters"
FROM #chapter
FLATTEN main-characters
SORT file.name ASC
GROUP BY main-characters