CODEWITHSUNDEEP
emacsorg-mode
Cristian
Cristian

Reputation: 1670

How to recalculate sum of columns in emacs org mode?

Lets say we have the following structure:

|Element    |Price          |  
|first      |1              |  
|second     |2              |  
|Total      |:=vsum(@2..@-1)|  -> this will render: 3
#+TBLFM: @4$2=vsum(@2..@-1)

Now lets say that a new row is inserted:

|Element    |Price          |  
|first      |1              |  
|inserted   |10             |  
|second     |2              |  
|Total      |:=vsum(@2..@-1)|  -> this will still render 3, but it should render 13
#+TBLFM: @5$2=vsum(@2..@-1)

So how to automatically make the sum change when a new row is inserted with a new value?

Upvotes: 2

Views: 3467

Answers (1)

D. Gillis
D. Gillis

Reputation: 670

Org-mode has limited ability to automatically recalculate tables: the first column can be used to add a special character (#) that sort of does what you want, but only when TAB, S-TAB, or RET are typed while in the row you want recalculated (note that this also adds this line to the global recalculation for the buffer which may also be useful).

The problem here is that org-mode doesn't know when it should recalculate the table unless you tell it to (e.g. using the command C-c * (org-ctrl-c-star)). One option would be to rebind some or all movement keybindings in org-mode to see if the cursor started in and has moved out of a table entry and if so to recalculate the entire table, but I'd recommend against doing that.

In my mind, the better and easier solution is to have org-mode automatically update the table when a new row is inserted (but not necessarily every time an entry changes). Here's an Emacs Lisp function (my-org-table-insert-row-and-recalculate-table) which creates a new row above the current one by prompting the user for each column and then recalculates all formulae for the table. A few notes about this function:

  • This function needs to be run at or below the first row for which the formula applies and above the last row to which the formula applies. If not, the formula may not update as you expect (e.g. the bottom row being used may drop out of the formula range). This is based on how org-mode updates formulae for inserted rows.
  • If any formulae are invalid, the updating process may not finish (e.g. the formula #+TBLFM: $2=vsum(@2..@-1) won't work for any insert since org-mode tries to apply it to row 1, column 2 for which the reference @-1 is invalid).
  • If you update a table entry manually, you'll still need to manually invoke the recalculation process for the table formulae.
(require 'subr-x)

(defun remove-empty-strings-list (list)
  (if (null list)
      '()
    (let ((rest (remove-empty-strings-list (cdr list))))
      (if (string= (car list) "")
          rest
        (cons (car list) rest)))))

(defun my-org-table--get-row-as-list ()
  (unless (org-at-table-p) (user-error "Not at a table"))
  (let ((line (buffer-substring-no-properties (line-beginning-position) (line-end-position))))
    (mapcar 'string-trim (remove-empty-strings-list (split-string line "|")))))

(defun my-org-table--get-top-row-as-list ()
  (save-excursion
    (org-table-goto-line 1)
    (my-org-table--get-row-as-list)))

(defun my-org-table--insert-string-row-and-recalculate-table (string)
  (org-table-with-shrunk-columns
   (beginning-of-line 1)
   (insert-before-markers string "\n")
   (org-table-align)
   (org-table-fix-formulas "@" nil (1- (org-table-current-dline)) 1)
   (org-table-iterate)))

(defun my-org-table--prompt-row ()
  (unless (org-at-table-p) (user-error "Not at a table"))
  (let ((top-row-list (my-org-table--get-top-row-as-list))
        (new-str "|")
        (cur-col 1))
    (dolist (top-item top-row-list)
      (let ((new-entry
             (read-string (concat "Enter entry (column #"
                                  (number-to-string cur-col)
                                  " - first entry: "
                                  top-item
                                  "): "))))
        (setq new-str (concat new-str new-entry "|")) ;; test placement
        (setq cur-col (+ 1 cur-col))))
    new-str))


(defun my-org-table-insert-row-and-recalculate-table ()
  "Interactively inserts row above point and then recalculates table"
  (interactive)
  (unless (org-at-table-p) (user-error "Not at a table"))
    (my-org-table--insert-string-row-and-recalculate-table (my-org-table--prompt-row)))

Example usage ('!!' is indication of point in buffer and '>>' is the minibuffer prompt):

| Element | Price |
| first   |     1 |
| second  |     2 |!!
| Total   |     3 |
#+TBLFM: @4$2=vsum(@2..@-1)

Run M-x my-org-table-insert-row-and-recalculate-table.

>> Enter entry (column #1 - first entry: Element): 
   inserted
>> Enter entry (column #2 - first entry: Price):
   100

Output:

| Element  | Price |
| first    |     1 |
| inserted |   100 |
| second   |     2 |
| Total    |   103 |
#+TBLFM: @5$2=vsum(@2..@-1)

Upvotes: 2

Related Questions