# Org table spreadsheet recalculate all TBLFM lines

The following answer states, that one must have all recalculating formulas in one `#+TBLFM` line: https://emacs.stackexchange.com/a/10946/10090. Indeed, when I try the following formulas only the first is recalculated automatically:

``````|   | Runde | 0 | 1 | 2 | 3 | Resultat |          % |
|---+-------+---+---+---+---+----------+------------|
| ! | name  |   |   |   |   |   result | percentage |
| # | Alice | 1 | 1 | 0 |   |        2 |      66.67 |
| # | Bob   | 0 | 0 | 1 |   |        1 |      33.33 |
#+TBLFM: \$7='(+ \$<<<..\$>>>);N
#+TBLFM: \$8='(format "%2.2f" (* (/ 100 (float (+ @3\$result..@4\$result))) \$result));N
``````

When I put them both in one `#+TBLFM` line as in the following example, it will recalculate both formulas as intended.

``````|   | Runde | 0 | 1 | 2 | 3 | Resultat |          % |
|---+-------+---+---+---+---+----------+------------|
| ! | name  |   |   |   |   |   result | percentage |
| # | Alice | 1 | 1 | 0 |   |        2 |      66.67 |
| # | Bob   | 0 | 0 | 1 |   |        1 |      33.33 |
#+TBLFM: \$7='(+ \$<<<..\$>>>);N::\$8='(format "%2.2f" (* (/ 100 (float (+ @3\$result..@4\$result))) \$result));N
``````

Is there a way to change this, so that it considers the first `#+TBLFM` line first, then the second, then the third, and so on? As I can already have multiple `#+TBLFM` lines, this would seem natural to me. The current behavior caused me some headache not understanding "why my second formula worked only sometimes".

As of org-mode 9 you can only do this manually: Place the cursor on the respective `#+tblfm:` lines and press C-c C-c. Here's the corresponding manual section, which is telling you that org considers only the first `#+tblfm:` line when otherwise updating the table formulas.

When the amount of formulas is getting too cumbersome to edit the `#+tblfm:` line itself please try the "formula editor" with C-c ' to edit all the formulas line by line.

• That's what I feared. I did not realize however, that the formulas would be on separate lines in the formula editor! So that enables me to still edit them in a readable + manageable way even if there are many. Thanks for mentioning that they will be line by line in the formula editor. – Zelphir Kaltstahl Jul 15 '18 at 15:32

Everything is possible since you can arbitrarily modify a buffer temporarily before applying a command with `cmdbufmod` from the answer to the question about a single tblfm for multiple org tables.

The following code defines an `:around` advice `org-table-multi-tblfm` for `org-table-recalculate` that joins successive `#+TBLFM:`-lines before running `org-table-recalculate`.

There is an exception: if point is placed at a `TBLFM:`-line only that formula is evaluated and if point is placed behind the last vertical line `|` only the first `TBLFM:`-line is executed. That means you must really put point in the table to get the effect of the around advice.

``````(defun cmdbufmod-prepare (bufmod-list &optional start bound)
"Prepare buffer for `cmdbufmod' with modifications BUFMOD-LIST.
See `cmdbufmod' for the format of BUFMOD-LIST.
If START is a buffer position the search for the regular expressions in BUFMOD-LIST
starts there. Otherwise it starts at `point-min'.
Optional BOUND limits the search.
Return the list of original text sections.
Each text section is a cons of an insertion marker and the old text
that needs to be restored there."
(unless start (setq start (point-min)))
(let (original-list)
(save-excursion
(dolist (bufmod bufmod-list)
(let ((place (car bufmod))
(newtext (cdr bufmod)))
(goto-char start)
(while (if (functionp place)
(funcall place bound)
(re-search-forward place bound t))
(setq original-list
(cons (cons (set-marker (make-marker) (match-beginning 0))
(match-string 0))
original-list))
(replace-match (propertize (if (functionp newtext)
(funcall newtext)
newtext)
'cmdbufmod t 'rear-nonsticky '(cmdbufmod)))))))
original-list))

(defun cmdbufmod-cleanup (original-list)
"Restore original text sections from ORIGINAL-LIST.
See the return value of `cmdbufmod-prepare' for the structure of ORIGINAL-LIST."
(cl-loop for interval being the intervals property 'cmdbufmod
if (get-text-property (car interval) 'cmdbufmod)
do (delete-region (car interval) (cdr interval)))
(cl-loop for original in original-list do
(goto-char (car original))
(insert (cdr original))))

(defun cmdbufmod (bufmod-list fun &rest args)
"After applying BUFMOD-LIST to current buffer run FUN with ARGS like `apply'.
BUFMOD is a list of buffer modifications. Each buffer modification
is a cons \(PLACE . NEWTEXT).
PLACE can be a regular expression or a function.
If PLACE is a function it should search for the next place to be replaced
starting at point. It gets the search bound as an argument,
should set match-data like `re-search-forward',
and return non-nil if a match is found.
If PLACE is a regular expression it is treated like the function
\(lambda () (re-search-forward PLACE nil t))

NEWTEXT can be a replacement string or a function.
A function should return the string for `replace-match'."
(let (original-list)
(unwind-protect
(progn
(save-excursion
(setq original-list (cmdbufmod-prepare bufmod-list)))
(apply fun args))
(save-excursion (cmdbufmod-cleanup original-list)))))

(defconst org-table-multi-tblfm-re "[[:space:]]*#\\+TBLFM:"
"Regular expression identifying \"#+TBLFM:\" at the beginning of lines.
Don't include a leading carret here!")

(defun org-table-multi-tblfm-search (&optional bound)
"Search for next \"#\\+TBLFM:\"-line that is preceded by another such line.
If BOUND is non-nil search stops there or at `point-max' otherwise.
The match-data is set to the match of \"[[:space:]]*\n[[:space:]]*#\\+TBLFM:[[:\" at the beginning of line."
(interactive) ;; for testing
(let ((re (concat "[[:space:]]*\n" org-table-multi-tblfm-re "[[:space:]]*"))
found)
(while (and (setq found (re-search-forward re bound t))
(null
(save-excursion
(forward-line -1)
(looking-at-p org-table-multi-tblfm-re)))))
found))

(defun org-table-multi-tblfm (oldfun &rest args)
"Replace buffer local table formulas when calling OLDFUN with ARGS."
(if (looking-at-p (concat "[[:space:]]*\n" org-table-multi-tblfm-re))
(apply oldfun args)
(cmdbufmod '((org-table-multi-tblfm-search . "::")) oldfun args)))

• @Zelphir You do not need to write anything up. You can just copy the stuff into your init file, restart Emacs, and use `org-table-recalculate` as you are used to it. All `#+TBLFM:` lines will be evaluated at `org-table-recalculate`. – Tobias Jul 17 '18 at 17:26