Here are some queries to be used with a CRM workflow.
The queries assume the following.
People have their own pages
People pages start with @
People pages have a page property contact which lists a contact interval. E.g. weekly.
Last time contacted
#+BEGIN_QUERY
{:title [:h2 "Last contact"]
:query [:find ?name ?interval (max ?day)
:keys name interval day
:where
[?jp :block/journal-day ?day]
[?mention :block/page ?jp]
[?mention :block/refs ?person]
[?person :block/original-name ?name] ; gets the name of the page as displayed in Logseq.
[?person :block/properties ?prop] ; I'm assuming the use of a page-property here. (i.e. first block on the page)
[(get ?prop :contact) ?interval]
]
:result-transform (fn [result]
(sort-by
(juxt
(fn [i] (get i :interval))
(fn [d] (get d :day))
) result)
)
:view (fn [rows]
[:table
[:thead [:tr
[:th "Name"]
[:th "Interval"]
[:th "Date"]
] ]
[:tbody (for [r rows] [:tr
[:td [:a {:href (str "#/page/" (get r :name))} (get r :name)]]
[:td (get r :interval)]
[:td (get r :day)]
] ) ]
]
)
}
#+END_QUERY
People that haven’t been contacted longer than x days
#+BEGIN_QUERY
{:title [:h2 "Longer than 7 days"]
:query [:find (pull ?person [*])
:in $ ?dl
:where
; all people with a weekly contact interval
[?person :block/original-name ?name]
[?person :block/properties ?prop]
[(get ?prop :contact) ?interval]
[(= ?interval "weekly")]
; who are not mentioned on journal pages of the last 7 days
(not [?mention :block/refs ?person]
[?mention :block/page ?jp]
[?jp :block/journal-day ?day]
[(>= ?day ?dl)])
]
:inputs [:7d] ; only accepts dynamic days
}
#+END_QUERY
List of all people pages
#+BEGIN_QUERY
{:title [:h2 "Persons list"]
:query [:find (pull ?p [*])
:where
[?p :page/name ?name]
[(clojure.string/starts-with? ?name "@")]
; [?p :block/properties ?prop]
; [(get ?prop :contact)] ; optional, only needed if you have @ pages without property contact that you want to exclude.
]
}
#+END_QUERY
Hi Siferiax, thanks for sharing your CRM setup.
In the comments you wrote that the input only accepts dynamic dates. I’m not entirely sure what that means. Ideally I would like to set the interval to a number and use that as the input. I haven’t been able to make that work. Is that because of the dynamic date?
I don’t understand what you mean by interval number?
Do you mean that the contact property has as a value a number which is the number of days or weeks or…?
That’s complicated as we do not have “real” date values. We have integers we can compare, but we cannot do math with them.
20230228 - 28 days is 20230200 instead of 20230131.
You can do it less dynamically, meaning the value you compare against isn’t “weekly”, but “12” for example and your input is then :12d.
I hope this makes sense?
I just tried it , but it did not work for some pages as the status of the block is not TODO, but DOING or WAITING. Even better would probably be all blocks with a marker that is not DONE or CANCELLED.
How should I adapt this code to ensure this ( not DONE or CANCELLED).