I have content that contains the following frontmatter:
character_one
character_two
relationship
They are all text fields. The “relationship” field will be one of 4 values (family, friend, enemy, collaborator)
The file name is automatically generated to be “character_one & character_two - relationship”.
(example: Suzi Smith & John Smith - Family)
I want to create a report using dataview. I want it to show a table grouped by character 1, with column 2 being character_two, and column 3 being relationship.
I know how to do group by dataview queries, but have only done it using the filename in the past. This time I want to create it without ID.
I hope this makes sense!
Things I have tried
I’ve tried many times, and this one seems to get me close:
TABLE WITHOUT ID character_one AS Character, character_two AS “2nd Character”, relationship AS Relationship
FROM “Writing/Character Relationships”
WHERE contains(character_one, “”)
FLATTEN character_one
GROUP BY character_one
Problem is, it only shows the character_one field. The rest remain blank.
I’ve searched for grouping without file.name to no avail.
Whenever you use GROUP BY all the other fields are moved into the rows pocket, which explains why you don’t see any other input. This also makes me question whether you actually want it grouped by or rather just sorted by a given character.
So try the following:
```dataview
TABLE WITHOUT ID character_one AS Character, character_two AS “2nd Character”, relationship AS Relationship
FROM “Writing/Character Relationships”
WHERE character_one
SORT character_one, character_two
```
Due to how Dataview currently works you can’t ensure the correlation of multiple column after grouping you’ll need to pick what matter the most to you. A correct connection between character_two and the relationship, or a single display of the character_one name.
If you do something like the following you’ll lose that conndction, but have only one row for each character_one:
```dataview
TABLE rows.character_two AS "2nd Character:, rows.relationship AS Relationship
FROM "Writing/Character Relationships"
WHERE character_one
GROUP BY character_one
```
You’ll most likely see that this would not align character_two with the relationship all the way.
This can to some extent be mitigated by combining those fields before grouping by doing something like:
```dataview
TABLE rows.combo AS "Character (Relationship)"
FROM "Writing/Character Relationships"
WHERE character_one
FLATTEN character_two + " (" + relationship + ")" as combo
GROUP BY character_one
```
In some cases this’ll work, but if either of those fields contain multiple values stuff will not look nice very fast.
I often rather want the first character repeated, instead of losing precision in the other columns. But your mileage may vary. As of current version, dataview (as in DQL) can’t do rowspans natively. With some coding you can simulate it using dataviewjs, and examples of such should be possible to find in this forum.
