Problem: I have too many friends (it’s a good problem) and I find that I forget to stay in touch all the time (it’s a bad problem)
What I want to do: I want to create a Dataview table that displays all of my #friends. Easy enough, but I want one of the fields listed to be a Last Contacted On: (some date)
I create a diary entry in Obsidian every day. If I say for example: Today I called [[Ben]], I would like the date that is listed in that diary entry to be displayed in my #friends table for Ben. And then the next time when I say: Today I sent a message to [[Ben]] it gets updated with the new date.
Two questions: Do you want the query for all friends, or just from one friend at a time, like “Ben”? What is the file name of your diary notes, is that “YYYY-MM-DD”, or where do you get the date of that note?
This turns out to be something like the following:
```dataview
TABLE
last as "Last interaction",
first as "First interaction"
FROM #f53929
FLATTEN file.outlinks as outlink
WHERE file.outlinks AND econtains(outlink.tags, "friends")
SORT file.name desc
GROUP BY outlink as Friend
FLATTEN max(rows.file.name) as last
FLATTEN min(rows.file.name) as first
SORT first ASC
```
With the following explanation (last row explained at end):
FROM "your/journal/folder" – Narrow down the search to only search your diary notes, aka the folder having those notes. 1) OK
FLATTEN file.outlinks as outlink – split into separate links. 3) OK
WHERE file.outlinks AND ... – Only check files having links to someone/something. 2) OK
... econtains(outlink.tags, "friends")– Only links where they’re tagged as my friends. 4) OK
SORT file.name desc – Ensure a proper date sorting, to make it easier for the next step
GROUP BY outlink as Friend – Group all the entries by “outlink” aka the name of our friend. This moves all the files into one list for each friend, accessible through rows
FLATTEN max(rows.file.name) as last - Select the highest (aka newest) interaction into last. 5) OK
FLATTEN min(rows.file.name) as first - Select the lowest (aka oldest) interaction into first. These two flatten operations allows us to sort on them in the next line
SORT first ASC – Sort according to your needs, here I sort on first ASC to see who I been friends the longest. You asked for last ASC, I think, to see which one has been the longest since you had any interaction with
TABLE last as "Last interaction", first as "First interaction" - Display the friend (from the GROUP BY) statement, with last and first interaction with them
It looked so strange that the last interaction was on the 27th for all of my wannabe-friends, so I added the first interaction to the list. Feel free to remove all references to first if you feel like it. (I used to test my setup, and to count in binary… )
Update: If you rather want the link to the diary note, replace the rows.file.name with rows.file.link in the min/max lines near the end.
I am in awe. You are a legend and from the bottom of my heart I thank you.
I was to the point where I was just going to say, oh well, I will never be able to do this. And… it was true, I would have never been able to figure this out, but your expertise and guidance were not only enough to provide the solution, but for me to understand the solution.
I can’t express my gratitude for your generosity more than this, I wish you friend, the very best blessings and fortune for whatever might come your way.
)