Query for tracking debt (and more)

Customization Look what I built
1

I log the debt record in the journal page. Example:

#debt
person:: [[some person]]
amount:: 1000
direction:: to ; direction is "from" or "to"

Then I use the below query to track if anyone did not pay back yet or if I still borrow money from some one. I’m still struggling to format the number with comma separator. But I’m quite proud of this query. Took me 1 week to learn clojure, datomic and a lot of trials and errors for this query.

#+BEGIN_QUERY
{:title [:h2 "💴 Debt tracking"]
 :query [:find (pull ?p [:block/original-name :block/name :block/properties :block/created-at :block/updated-at]) ?amount ?sign
         :where
         (page-ref ?b "debt")
         (has-property ?b :amount)
         [?b :block/properties ?props]
         [(get ?props :person) ?person]
         [(get ?props :amount) ?amount]
         [(get ?props :direction) ?dir]
         [(get {"to" 1 "from" -1} ?dir) ?sign]
         [?p :block/original-name ?name]
         [(contains? ?person ?name)]]
 :result-transform (fn [result] 
                     (map (fn [[page amount]] (assoc-in page [:block/properties :total] amount))
                          (remove (fn[[page amount]] (zero? amount))
                                  (map (fn [group] (reduce (fn [acc n] (list (first n) (+ (second acc) (second n)))) group))
                                       (vals (group-by first (map (fn [[p a s]] (list p (* a s))) (partition 3 result))))))))}
#+END_QUERY

With a little tweak, this query can be used for other things that need accumulation over multiple records, like task effort tracking…

Untitled
Untitled1178×980 47.3 KB

2

Nice! Looks really cool :smiley:

3

It took me one week to learn Logseq alone, nevermind its db design or language it is written in. (Datalog is fun though ;D) You really did all that in one week? You must be quite talented indeed. Keep it up, and thanks for sharing your work.

Keeping track of daily medication in a tablet log (or “Tablog” if you will) is done in a very similar way. It is very helpful for reminding me when I need to refill prescriptions, or to prevent accidental double dosing or missing a dose.

4

Amazing! Did you ever figure out the comma separator formatting?

5

Yes, but I have to end up converting the number to string and add a comma every 3 characters from right to left. Here is the current query I’m using now.

#+BEGIN_QUERY
{:title [:h2 "💴 Debt tracking"]
 :query [
         :find (pull ?b [*])
         :where
         (page-ref ?b "debt")
         (has-property ?b :amount)
         (not (has-property ?b :template))
         ]
 :result-transform (fn [blocks]
                     (let [num->money (fn [num] (str (clojure.string/join "," (map (fn [arg] (apply str arg)) (map reverse (reverse (partition-all 3 (reverse (str num))))))) " $"))
                           records (map (fn [b] (list (get-in b [:block/properties :person]) (* (get-in b [:block/properties :amount]) (get {"to" 1 "from" -1} (get-in b [:block/properties :direction]))))) blocks)
                           display-data (remove (fn [d] (= 0 (second d))) (map (fn [g] (reduce (fn [acc x] (list (first x) (+ (second acc) (second x)))) g)) (vals (group-by first records))))]
                       (map (fn [d] {:block/properties {:person (first d) :amount (second d) :amount_fmt (num->money (second d))}}) display-data))
                     )
 }
#+END_QUERY
1 Like
7

The math here doesn’t seem to be mathing unforunately

image
image1250×1057 164 KB

#+BEGIN_QUERY
{:title [:h2 "💴 FOCUS Toronto Budget"]
 :query [:find (pull ?p [:block/original-name :block/name :block/properties :block/created-at :block/updated-at]) ?amount ?sign
         :where
         (page-ref ?b "focusbudget")
         (has-property ?b :amount)
         [?b :block/properties ?props]
         [(get ?props :line-item) ?line-item]
         [(get ?props :amount) ?amount]
         [(get ?props :direction) ?dir]
         [(get {"to" 1 "from" -1} ?dir) ?sign]
         [?p :block/original-name ?name]
         [(contains? ?line-item ?name)]]
 :result-transform (fn [result] 
                     (map (fn [[page amount]] (assoc-in page [:block/properties :total] amount))
                          (remove (fn[[page amount]] (zero? amount))
                                  (map (fn [group] (reduce (fn [acc n] (list (first n) (+ (second acc) (second n)))) group))
                                       (vals (group-by first (map (fn [[p a s]] (list p (* a s))) (partition 3 result))))))))}
#+END_QUERY