Dataview - Display Frontmatter of a Note Referenced in Table

What I’m trying to do

I want to have a table that lists the front matter of various notes. I also wish to have a column which contains frontmatter from the notes listed in one of the other columns.

Things I have tried

Currently, I have the code below:

TABLE WITHOUT ID People AS "People",
     join(rows.file.link, ", ") AS "Documents"
WHERE contains(file.frontmatter.places_mentioned, this.file.name) 
    AND contains(file.frontmatter.type, "document")
FLATTEN file.frontmatter.people AS People
GROUP BY People

This code makes a table that looks like this:

I would like to add another column which includes frontmatter (file.frontmatter.date) from the files listed under “People”.

What I have tried:

Firstly, simply alluding to “People” as if it were a file in my column section under Table:

TABLE WITHOUT ID People AS "People",
	People.file.frontmatter.date AS "Date",
     join(rows.file.link, ", ") AS "Documents"
WHERE contains(file.frontmatter.places_mentioned, this.file.name) 
    AND contains(file.frontmatter.type, "document")
FLATTEN file.frontmatter.people AS People
GROUP BY People

This results in the error:

I’ve also tried “People.frontmatter.date” and “link(file.frontmatter.people, “file.frontmatter.date”)”.

Any help would be appreciated!

Just something to try with this line People.file.frontmatter.date … had similar problem and this solved:

date(People.file.frontmatter.date)

I needed to tell him that is a date even that was logic, that is a date :smile:

Cheers, Marko :nerd_face:

Apologies! I should have clarified that the file.frontmatter.date is not actually a date field, but a text field (I work with a lot of BCE dates as well as date ranges and so date formatting is somewhat unhelpful). I tried this, but changing it to string(People.file.frontmatter.date) AS “Date”. This did not help, though…

OK, FLATTEN is another headache for me :smile: I think that the problem could be due to it. The People variable is no longer an object, it’s each individual entry from the array in file.frontmatter.people, likely just a string.

What about just like this file.frontmatter.date AS "Date"?

```dataview
TABLE WITHOUT ID People AS "People",
	file.frontmatter.date AS "Date",
	join(rows.file.link, ", ") AS "Documents"
WHERE contains(file.frontmatter.places_mentioned, this.file.name) 
    AND contains(file.frontmatter.type, "document")
FLATTEN file.frontmatter.people AS People
GROUP BY People
```

Cheers, Marko :nerd_face:

I suspect that you’re right about it being related to Flattening the people variable. Each item under People is a link, though. So, I’m confused as to why I cannot simply use it as a file. I’ve tried file.frontmatter.date and it still doesn’t work. I get an empty column:

Perhaps it’s worth mentioning that file.frontmatter.people is a yaml property which is in list form. I flattened it and Grouped by it so as to create a table that uses it as the main grouping. Each item in the ‘people’ yaml property is a link, though.

So, file.frontmatter.people is a list of strings in the YAML frontmatter (like ["John Doe", "Jane Smith"]).

Than, let’s try and join also dates … join(rows.file.frontmatter.date, ", ") AS "Dates",

Cheers, Marko :nerd_face:

Tried this and it gave me dates, but the wrong ones:

The dates listed here are the file.frontmatter.date associated with the items in the ‘Documents’ column. I need the file.frontmatter.date associated with the ‘People’ Column. This is why I kept trying People.frontmatter.date earlier because it is not actually the date frontmatter which is called by file.frontmatter.date (that gets the frontmatter from “Documents”).

I don’t know what to call this, but it’s a more indirect reference. Without the Flatten and Group By expressions, the data displayed would be the data on the right column (“Documents”). Each of those notes (under “Documents”) has a YAML frontmatter list of links with the property title of ‘people’. I have Flattened this ‘people’ list and Grouped By it. Now I wish to obtain frontmatter data within each of those items on the ‘people’ list. Hence, my attempt of People.frontmatter.date because ‘People’ refers to file.frontmatter.people.

Many thanks for your help so far! I’m happy to answer any questions and am terribly sorry it’s so difficult to explain.

… and also could be my understanding of English is not at the highest :smile:

My Dataview knowledge ends here, or at least I reached the point where I would say that I think this can’t be achieved with plain Dataview. It wouldn’t be the first time that some guru would show me wrong :joy:

If I understand, you need to:

  1. Preserve the original file reference as part of the flattening.
  2. Then group by People.
  3. Finally, access the frontmatter of the associated file(s) where each person appeared.

Instead of flattening the list of names, you need to create a record that holds both the person and their originating file. I think you can’t do this directly in plain Dataview, but you can do it with DataviewJS.

I would love to see someone come up with a solution for Dataview, as I love to see “complex” things to be solved with Dataview. But, if you’re open to trying DataviewJS … it’s hard to test this for me, as I don’t have a source, so it could be that this will not work at first.

```dataviewjs
// Get all notes where:
// - the YAML frontmatter has 'places_mentioned' that includes this current file name
// - the YAML 'type' includes "document"
const pages = dv.pages()
    .where(p => p.file.frontmatter?.places_mentioned?.includes(dv.current().file.name))
    .where(p => p.file.frontmatter?.type?.includes("document"));

// We'll store people as keys in this object, with an array of associated date + link objects
let peopleMap = {};

// Loop through all the matched notes
for (let page of pages) {
    // Get the list of people from the current note's frontmatter (or an empty array if missing)
    let people = page.file.frontmatter.people ?? [];

    // Loop through each person mentioned in this file
    for (let person of people) {
        // If this person isn't already in the map, initialize them with an empty array
        if (!peopleMap[person]) {
            peopleMap[person] = [];
        }

        // Push an object to this person's array that includes:
        // - the date from the file frontmatter
        // - the link to the file itself
        peopleMap[person].push({
            date: page.file.frontmatter.date,
            link: page.file.link
        });
    }
}

// Now generate a table:
// - First column: person name
// - Second column: joined list of dates they appear in
// - Third column: joined list of document links they appear in
dv.table(
    ["People", "Dates", "Documents"],
    Object.entries(peopleMap).map(([person, entries]) => [
        person,
        entries.map(e => e.date).join(", "),
        entries.map(e => e.link).join(", ")
    ])
);
```

Comments // are mainly for me when translating your Dataview into DataviewJS, but they can also help you understand what is going on.

Once again - I hope that someone will come up with a Dataview solution, or … if you go with DataviewJS - at first, this could run into a few errors :smiling_face:

Cheers, Marko :nerd_face:

In the second field, try using just People.date instead of rows.file.frontmatter.date. Like this:

```dataview
TABLE WITHOUT ID People AS "People",
	People.date AS "Dates",
	join(rows.file.link, ", ") AS "Documents"
WHERE contains(file.frontmatter.places_mentioned, this.file.name) 
    AND contains(file.frontmatter.type, "document")
FLATTEN file.frontmatter.people AS People
GROUP BY People
```

If this works I can try to explain more, but basically rows.file refers to the note that the People link originates from.

One of the key questions here is _why do you use file.frontmatter.??? ? This gives you the raw version of that property, and not the type casted version of that property.

In other words, doing file.frontmatter.date gives you a text string, but date gives you the date. Similar file.frontmatter.people gives you the list of raw string of links, but people gives you list of actual links.

This matters when you want to look at the links, and properties of that link, as doing people.date will be equal looking at the link and the date property of that link (if it exists). But doing file.frontmatter.people.date doesn’t make sense since you’re dereferencing a string with some extra stuff added onto it…


OK, so what’s the better way of doing this? Assuming that places_mentiond is also a list of links, I’d rather try something like:

```dataview
TABLE People.date as Date, join(rows.file.link, ", ") as "Documents"
WHERE contains(places_mentioned, this.file.link)
  AND contains(type, "document")
FLATTEN people as aPerson
GROUP BY aPerson as People
```