Dataview JS tables

Help
1

What I’m trying to do

I’m trying to organize my daily notes by months, using dataview. Each day, I have tags and articles and I want to generate three columns each month, each representing File name, Tags, and Articles.

Things I have tried

Thanks to this previous post, I managed to get pretty close to what I wanted, except that all the rows are empty. The tables are generated correctly by month in a decending order, and each month has the correct number of files. But all values are empty, if that makes sense. Below is the code I’m using. I’m not familiar with the JS code at all, so I’m not sure what to fix here… Thank you in advance for your help!


const notes = await dv.query (`
TABLE 
  row.file.link as "File", 
  tags as Tags,
  articles as Articles
FROM
  "3_Periodic/2024"
GROUP BY
  dateformat(file.ctime, "yyyy-MM") AS Month
FLATTEN rows as File
SORT Month DESC
`)

console.log(notes)

if (!notes.successful) {
  dv.paragraph(`~~~~\n${ notes.error }\n~~~~\n`)
  return
}

let typeDict = {}
for (let note of notes.value.values) {
  if ( !typeDict.hasOwnProperty(note[0]) )
    typeDict[note[0]] = []

  typeDict[note[0]].push([...note.slice(1)])
}

for (let key of Object.keys(typeDict)) {
  dv.header(2, key)
  dv.table([...notes.value.headers.slice(1)],
    typeDict[key])
}
2

There might be other strangeness happening in you script, but there are two concepts I would like to start addressing, and that is the row.file.link thingy, and the GROUP BY... FLATTEN combination.

The row.file.link thingy

Have you actually tested that this pure dataview query produces what you expect it to:

```dataview
TABLE 
  row.file.link as "File", 
  tags as Tags,
  articles as Articles
FROM
  "3_Periodic/2024"
GROUP BY
  dateformat(file.ctime, "yyyy-MM") AS Month
FLATTEN rows as File
SORT Month DESC
```

I think this would give you a whole lot of nothing. Both due to the GROUP BY... FLATTEN which I’ll address soon, but also do to a missing s at the first line, which I suspect should be rows.file.link or similar. But you are also missing rows. in front of the other lines.

In short, before focusing on the rest of your dataviewjs script, you need to make sure that your base query returns the correct values, which I suspect it doesn’t.

GROUP BY … FLATTEN

This combination of grouping and flattening seems confusing, and looks like a mean to achieve the goal of having the month available for sorting. It kind of does that, but you do also get some side-effects which I don’t think you want.

Given a base query where you would display the tags and articles, using a starter line of TABLE tags, articles, if you add a GROUP BY file.folder line to that query, it’ll group all the results together based on the folder of your files. This means that now each of the individual fields/properties are moved into the rows object, so a correct starter line would be: TABLE rows.tags, rows.articles, and you’d have access to key which would hold the group value, in this case the folder.

In your query you kind of reverse the effect of this grouping by doing FLATTEN rows as File, allowing you to access each file again _if you had done something like File.tags, or File.file.name or File.articles and so on. This since FLATTEN splits up a list into its separate elements. But due to the combo of both of them, it’s not easy to predict what you get, if anything from using just tags or articles in your starter line.

The way forward

I don’t really understand why you do that combo, and I believe you should start with focusing on getting the correct output of your base query. And I suggest ditching that strange combination of grouping/flattening and rather do something like:

```dataview
TABLE WITHOUT ID Month, file.link as "File", tags as Tags, articles as Articles
FROM "3_Periodic/2024"
FLATTEN dateformat(file.ctime, "yyyy-MM") as Month
SORT Month, file.cday
```

Here I use FLATTEN to store the month for later usage, and I added the file creation day to the sorting to sort files within the month as well. Try this, or play around with your base query, and when it lists the correct stuff try inserting that query into your dataviewjs script, and see if it correctly splits it out into the various months.

1 Like
3

Thank you, @holroy for the explanation and answer. You could probably tell that I had essentially no idea what I was doing :sweat_smile:. I followed your advice and started from the second code block you suggested - it now works well! This got me exactly what I wanted. Thank you again :slight_smile:

1 Like