How to view latest linked file in Dataview?

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.

Is there anyway to do this?

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?

But, yes, it should be possible…

The File name is always YYYY-MM-DD for my diary.
Also I have a YAML tag for the date it was created.

As for what I want it to look like, I want it to be something like this:
Name | Last interacted with |

Ben | [[2023-01-15]]
Sara | [[2022-12-14]]
Bill | [[2019-07-31]]

That way, I can sort by Last Interacted With and see the people who I have not contacted in a while.

So to rephrase your query:

  1. From the list of diary notes,
  2. those having a link to someone
  3. where one of the links
  4. is a friend of mine
  5. and select the newest entry

This turns out to be something like the following:

  last as "Last interaction", 
  first as "First interaction"
FROM #f53929
FLATTEN file.outlinks as outlink
WHERE file.outlinks AND econtains(outlink.tags, "friends")
SORT desc
GROUP BY outlink as Friend
FLATTEN max( as last
FLATTEN min( 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 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( as last - Select the highest (aka newest) interaction into last. 5) OK
  • FLATTEN min( 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

In my test setup this resulted in this table:

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… :slight_smile: )

Update: If you rather want the link to the diary note, replace the with 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.

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.