I have some CRM type functionality in my notes. I take meeting notes, and have links to all the people pages for those at the meeting. A persons page has a front matter attribute called “company” so I know who works where. Then I have a company page, that describes that company, uses a dataview query to pull in a table of everyone who works there that I have had meetings with.
The tragedy occurs when I want a table below that, which is a list of all of the meetings I have had involving that company.
I have the following query,
Table without id dateformat(meeting_date, "dd-MM-yyyy") as "Date", file.link As Title
FROM "Calendar/Meetings"
FLATTEN file.outlinks as People
WHERE People.company = "Microsoft"
That works as expected and gives me a table of data. If I have multiple people from Microsoft in a single meeting, then I get one line for each of them. It makes complete sense. So a meeting with four microsoft people, gives me four entries.
So clearly the answer is a GROUP BY… except it doesn’t work…
Adding the single line
GROUP BY Title
to the end of my query, gives me a table with one entry, but the values are just - signs.
i have tried calling it file.link instead, I have tried calling it rows.file.link… I have tried calling it rows.Title… nothing changes the output. It just doesn’t show any data
I am genuinely perplexed… everything is the latest version…
Given a simple query like TABLE a, b GROUP BY a, will group all results into rows for each unique value of a which is also duplicated into key. Due to this grouping, the above query would display the - for empty results, since now you’ll find the grouped together results in rows.a and rows.b and so on.
In your particular case you also change the dateformat when display a value, and that can’t just be a simple addition of rows. in front of the variable. So I’d suggest using FLATTEN ... as Date before the GROUP BY statement, and then doing rows.Date afterwards.
So the following should start producing some results for you:
```dataview
TABLE rows.Date as Date, rows.Title as Title
FROM "Calendar/Meetings"
FLATTEN file.outlinks as People
WHERE People.company = "Microsoft"
FLATTEN dateformat(meeting_date, "dd-MM-yyyy") as Date
FLATTEN file.link As Title
GROUP BY People
```
Also notice that this will make all the single entries in a group be presented as a list, even though there is just one meeting for that person.
Thank you so much for always helping. I even think I understand… at least the flatten bit for date I understand. I now have a table of lists, so the group by is “grouping” them together, but not “unique” ing them. There are four entries for a meeting with four people, and so forth… so I dont understand that bit… Because I haven’t tried to display the People column of the table, but it has “rendered” it invisibly by default when I did the group…
I have even tried to normalise the People, as it’s clearly the problem. I tried
FLATTEN (replace(string(People), “*”, " ")) As People
Thinking if I made everyones name the same, then the group by would have to work as I want, but no luck there either…
For the bonus points, is there are qay to examine the internals to see what it is making at each step?