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.