Org column with time estimate adjusted by clocked time: how to sum?

Question

I'm trying to track how much time I need for a project. Individual tasks all have a time estimate (for example below, one hour each), so my initial estimate was 6 hours for the whole project. But once I finish a task and its estimate turns wrong (according to the clocked time), I would like to have an updated estimate for the full project: if a task took more time than estimated, the total project time should increase, and the opposite.

Inspired by another answer, I used org-columns-modify-value-for-display-function to get an updated effort value for individual tasks, but the summation in parent headlines doesn't work.

Here is an example org file:

* Complete project
:PROPERTIES:
:COLUMNS:  %ITEM %TODO %EFFORT{:} %CLOCKSUM %EFFORT(Updated effort){:}
:ID:       20230203T142140.528627
:END:

** Part 1
*** DONE 1a
:PROPERTIES:
:Effort:   1h
:END:
:LOGBOOK:
CLOCK: [2023-02-03 Fri 13:20]--[2023-02-03 Fri 13:40] =>  0:20
:END:
*** DONE 1b
:PROPERTIES:
:Effort:   1h
:END:
:LOGBOOK:
CLOCK: [2023-02-03 Fri 13:20]--[2023-02-03 Fri 15:40] =>  2:20
:END:
*** TODO 1c
:PROPERTIES:
:Effort:   1h
:END:

** Part 2
*** DONE 2a
:PROPERTIES:
:Effort:   1h
:END:
*** TODO 2b
:PROPERTIES:
:Effort:   1h
:END:
:LOGBOOK:
CLOCK: [2023-02-03 Fri 13:22]--[2023-02-03 Fri 15:22] =>  2:00
:END:
*** TODO 2c
:PROPERTIES:
:Effort:   1h
:END:
:LOGBOOK:
CLOCK: [2023-02-03 Fri 13:22]--[2023-02-03 Fri 13:42] =>  0:20
:END:

And the corresponding columns view:

#+BEGIN: columnview :hlines 1 :id "20230203T142140.528627"
| ITEM             | TODO | EFFORT | CLOCKSUM | Updated effort |
|------------------+------+--------+----------+----------------|
| Complete project |      | 6:00   |     5:00 |           6:00 |
| Part 1           |      | 3:00   |     2:40 |           3:00 |
| 1a               | DONE | 1h     |     0:20 |           0:20 |
| 1b               | DONE | 1h     |     2:20 |           2:20 |
| 1c               | TODO | 1h     |          |           1:00 |
| Part 2           |      | 3:00   |     2:20 |           3:00 |
| 2a               | DONE | 1h     |          |           1:00 |
| 2b               | TODO | 1h     |     2:00 |           2:00 |
| 2c               | TODO | 1h     |     0:20 |           1:00 |
#+END:

1a and 1b are marked done, so the updated effort is the clocked time. 2a is done but I didn't clock in, so it keeps the value for the effort. 1c is not started yet, and 2c has been worked on for less than the time estimate, so their effort stays the same. 2b has been worked on for more than the time estimate, so the updated effort is the clocked time.

My problem is now that the times are not adding up correctly: if I don't exclude headlines without effort estimate, the updated effort for Part 1, Part 2 and Complete project follow the same rules as all other headlines. If I do, then they fall back to their original value, in this case the effort. Instead, I would like them to be the sum of their children (so Part 1 should be 3:40, Part 2 4:00, and Complete project 7:40), and I'm not sure how to implement that.

This is my code:

(setq org-columns-modify-value-for-display-function
      '(lambda (column-title value)
         "Apply function associated with COLUMN-TITLE on VALUE, as defined in `column-display-alist'."
         (let ((fun (cdr-safe (assoc-string column-title column-display-alist))))
           (when (functionp fun)
             (funcall fun value)))))

(setq column-display-alist '(("Updated effort" . effort-or-clock)))

(defun effort-or-clock (effort)
  "Get an updated effort if the task is finished or takes longer."
  (if (org-entry-get (point) "Effort") ; only if tasks itself, not parent
      (let* ((isdone (org-entry-is-done-p))
             (clocked-time (org-clock-sum-current-item (org-clock-get-sum-start)))
             (time (cond ((and isdone (> clocked-time 0)) ; done: clocked time
                          clocked-time)
                         ((and effort (> clocked-time 0)) ; not done: max of clocked and effort
                          (max (org-duration-to-minutes effort) clocked-time))
                         (t ; others: effort
                          (org-duration-to-minutes effort)))))
        (org-duration-from-minutes time))
    nil))

Answer 1

I was looking into modifying org-clock-sum and I accidentally found a solution in this answer:

(setq org-columns-modify-value-for-display-function
      #'column-display-value-transformer)

(defun column-display-value-transformer (column-title value)
  "Transforms values to display in column views.
NEW_EFFORT: show and store updated effort if the task is finished or takes longer."
  (when (equal column-title "NEW_EFFORT")
    (let* ((isdone (org-entry-is-done-p))
           (effort-str (org-entry-get nil org-effort-property))
           (effort (if effort-str (org-duration-to-minutes effort-str) 0))
           (clocked-time (org-clock-sum-this-item (org-clock-get-sum-start)))
           (clocked-time-all (org-clock-sum-current-item (org-clock-get-sum-start)))
           (time (cond ((not (or (> effort 0) (> clocked-time 0))) ; no values
                        nil)
                       ((and (not isdone) (> effort 0) (> clocked-time 0)) ; not done: max of clocked and effort
                        (org-duration-from-minutes (max effort clocked-time)))
                       ((> clocked-time 0) ; done or no effort: clocked time
                        (org-duration-from-minutes clocked-time))
                       (t ; others: effort
                        (org-duration-from-minutes effort)))))
      (org-entry-put nil "NEW_EFFORT" time) ; write in property drawer (needed for sum)
      time)))

(defun org-clock-sum-this-item (&optional tstart)
  "Return time clocked on current item, without its children."
  (save-excursion
    (save-restriction
      (org-narrow-to-subtree)
      (let ((level (org-outline-level)))
        (org-clock-sum tstart nil (lambda ()
                                    (message (number-to-string (org-outline-level)))
                                    (= (org-outline-level) level)))
        org-clock-file-total-minutes))))

The idea is that instead of using a predefined property like CLOCKSUM or EFFORT and trying to manipulate its displayed value, all there is to do is use a new property (NEW_EFFORT). With the proper summary attribute, org-mode fills the empty values by summing the values of its children.

Instead of adding the new property to each task manually, the function in org-columns-modify-value-for-display-function can calculate it on the fly, and also write it to the properties drawer. It seems that for the summation to work, the values have to be stored in the property drawer first, meaning I have to refresh the columns to update the total. Sometimes some properties refused to update, but this can be solved by removing them with org-delete-property-globally and refreshing (twice).

Also, I want column-display-value-transformer to return nil for parent headlines, which I defined as headlines with no effort estimate and no clocked time. But org-clock-sum-current-item returns the total clocked time of the headline and its children, so instead I defined org-clock-sum-this-item using the code from this answer. One caveat is that org-clock-sum doesn't return a value but instead modifies the variable org-clock-file-total-minutes, and using only org-clock-sum-this-item seems to break the CLOCKSUM column. Hence the otherwise useless call to org-clock-sum-current-item, which resets the variable.

Now with the following column definitions:

* Complete project
:PROPERTIES:
:COLUMNS:  %ITEM %TODO %EFFORT{:} %CLOCKSUM %NEW_EFFORT{:}
:END:

the column view of the example tree in the question becomes:

#+BEGIN: columnview :hlines 1 :id "20230203T142140.528627"
| ITEM             | TODO | EFFORT | CLOCKSUM | NEW_EFFORT |
|------------------+------+--------+----------+------------|
| Complete project |      | 6:00   |     5:00 |       7:40 |
| Part 1           |      | 3:00   |     2:40 |       3:40 |
| 1a               | DONE | 1h     |     0:20 |       0:20 |
| 1b               | DONE | 1h     |     2:20 |       2:20 |
| 1c               | TODO | 1h     |          |       1:00 |
| Part 2           |      | 3:00   |     2:20 |       4:00 |
| 2a               | DONE | 1h     |          |       1:00 |
| 2b               | TODO | 1h     |     2:00 |       2:00 |
| 2c               | TODO | 1h     |     0:20 |       1:00 |
#+END: