Most recent journal reference

Hey-oh!

Summary: I am basically turning LogSeq into my own personal CRM for details project tracking and referencing detailed notes of customer engagements. What would be extremely helpful is to have a way to track when I last interacted with a customer.

Currently, I have a list of all my active customers on a page called ‘myCustomers’ which has the following simple query.

{{query (and (page-property :tags "myCustomers"))}}

This currently shows me the associated properties of each page I have tagged as myCustomers, however I’d like to be able to also show the last time a customer was “tagged” (using their page name or associated alias) from a Journal page AND it also includes at least one of the following tags: #email #call #Called #vmail #meeting.

Example:

page alias last-activity
Acme Corporation Acme [[2024-AUG-12]]
Bloopers Inc. Bloopers [[2024-JUL-08]]
Integrity Farms IF [[2024-AUG-26]]
Star Fuel Star [[2024-AUG-23]]

Example Journal page:

- ## Scratchpad
	- #Called [[Bob Jones]] at [[ACME]], left vmail re: #FilesComCon
		- Sent follow-up #email as well
	- #Called [[Rich Holmes]] at [[Bloopers Inc.]] re [[Bloopers/Child Site]]
		- no answer, left #vmail -> followed up with #email
	- #Called [[Jamie Lynn]] at [[Star Fuel]]
		- Introduced myself as their SA
		- Redirected me to [[Jesse McBee]] as main point of contact
			- #Called [[Jesse McBee]] at [[Star Fuel]]
				- no answer, left #vmail -> followed up with #email
	- Prep for [[IFG/Password Protected External Shared Folder]] w/ [[Mark P.]] on [[2024-08-27]]
		- Slides ready for tomorrow
		- ...
	- [[Integrity Farms]] - New Customer Onboarding with [[Jim Marsh]] #meeting -> rescheduled for this Friday, [[2024-08-30]]
		- Participants: Josh, Jim Marsh (Sponsor)
		- Topics:
			- Introductions / Agenda
			- CS Overview
			- Verily Overview
			- Go Live
			- Next Steps
		- Blockers:
			-
		- Decisions:
			-
		- Action Items:
			-
		- Notes:
			- Prep:
				- Support Case: Password recovery emails not sent -> #triage/4855
					- New Extraordinary user created
					- #[[Custom Domain]] questions -> CAA in place...
						- Are we gonna use Let's Encrypt or their preferred CA?
			- Call rescheduled for this Friday -> [[2024-08-30]]
	- Check-in with [[Elizabeth]]
		-

All customer pages are just normal pages and use the following properties:

icon:: 🏢
tags:: Customers, myCustomers (i.e., just Customers or both)
alias:: xxx

All people pages have the following properties with the page name being their full name (i.e., Bob Jones):

type:: [[People]]
color:: turquoise
icon:: 👤
tags:: People
position:: Senior Cloud Engineer
organization:: [[ACME]] 
phone:: tel://+1-888-123-4545
email:: bobjones@acme.co
role:: Sponsor
alias:: Bob J.

Thank you in advance!

2 Likes

Hello!
This could help you as a pretty similar task.

1 Like

Hey, @stdword -

Appreciate the response. I did see your plug-in, but I am trying to avoid using one where possible. I may have to look at it more closely, but will need to also do a code review before we can even consider using it, hence it was just easier to try and create my own query for it :joy: … but thus far, I’ve not had much luck.

Still hopeful though! :crossed_fingers:

Something like this:

#+BEGIN_QUERY
{
 :query [:find ?d (pull ?p [*])
   :keys date page
   :where
     (page-property ?p :tags "myCustomers")
     (j-ref ?p ?d)
     (not
       (j-ref ?p ?other-d)
       [(< ?d ?other-d)]
     )
 ]
 :rules [
   [(j-ref ?p ?d)
     (or-join [?p ?b]
       [?b :block/refs ?p]
       (and
         [?p :block/alias ?a]
         [?b :block/refs ?a]
       )
     )
     [?b :block/page ?j]
     [?j :block/journal-day ?d]
   ]
 ]
 :result-transform (fn [result]
   (map (fn [r]
     (update (:page r) :block/properties (fn [p]
       (assoc p "last-activity" (:date r) )
     ) )
   ) result)
 )
}
#+END_QUERY