Table refuses to forget cell formula which once overrode a column formula

Table refuses to forget cell formula which once overrode a column formula

[Ruy Exel]

[-- Attachment #1: Type: text/plain, Size: 3831 bytes --]

This bug report is motivated by a question I asked at


https://emacs.stackexchange.com/questions/36855/table-refuses-to-forget-cell-formula-which-once-overrode-a-column-formula

and all of the necessary information can probably be obtained from there.
Nevertheless let me repeat it here:

---------------------------------------------------------

The following is very simple org-mode spreadsheet in which the cells in the
second column show the triple of the corresponding cell in the first column
due to the "column formula" in its TBLFM row $2=3*$1;N. However the third
row (actually row @4) is an exception because of the "cell formula"
@4$2=string("Exception") which expectedly overrides the above column
formula.

|--------+-----------|
| Number |    Triple |
|--------+-----------|
|      1 |         3 |
|      2 |         6 |
|      3 | Exception |
|      4 |        12 |
|--------+-----------|
#+TBLFM: $2=3*$1::@4$2=string("Exception")

So all is well. Below you will find a copy of the above table, except that,
after copying, I have deleted the exceptional formula affecting cell @4$2.

|--------+-----------|
| Number |    Triple |
|--------+-----------|
|      1 |         3 |
|      2 |         6 |
|      3 | Exception |
|      4 |        12 |
|--------+-----------|
#+TBLFM: $2=3*$1

One would expect that, after updating this table with C-u C-c *, or C-c C-c
in the TBLFM line, the exceptional value in cell @4$2 would revert to the
triple of '3', but instead the string "Exception" stays put. Is this a bug
or am I doing something wrong?

I noticed that if I save the file, close it (kill-buffer) and then reopen
it, the strange behaviour disapears, namely, updating the table with C-u
C-c * causes cell @4$2 to turn to '9', as expected.

I am using GNU Emacs 24.3.1 and the latest version of org-mode.

---------------------------------------------------------

The answer I got from emacs.stackexchange.com says:

---------------------------------------------------------

The persistence of the string exception has nothing to do with copying. If
you recalculate the original table and afterwards remove the field formula
from it you also have the problem that exception is not replaced by 9 at
the next recalculation.

The reason for that behavior is that the text property :org-untouchable is
put on the output of field formulas. The normal function of
:org-untouchable is to prevent the modification of cells with field
formulas during the evaluation of a column formulas.

In my opinion the :org-untouchable property should be removed at the
beginning of calls to org-table-iterate and at the beginning of interactive
calls of org-table-recalculate.

You can obtain this behavior with the advices below. These advices are
tested with org-version 9.0.5 in emacs 25.1.50.2.

I would agree that the original behavior is faulty. Therefore, I suggest to
file a bug-report at https://lists.gnu.org/mailman/listinfo/emacs-orgmode.
A proper bug-fix should probably be implemented differently.

(defun org-table-remove-untouchable (&rest _args)
  "Remove `:org-untouchable' property from org-table."
  (save-excursion
    (cl-assert (org-at-table-p) nil "Not in org-table.")
    (remove-text-properties (org-table-begin) (org-table-end)
'(:org-untouchable nil))))

(defun org-table-recalculate-remove-untouchable (&optional all _noalign)
  "Remove `:org-untouchable' property at the beginning of
`org-table-iterate'.
Use this function as :before advice of `org-table-iterate'."
  (when (and (called-interactively-p 'any)
         all)
    (org-table-remove-untouchable)))

(advice-add 'org-table-iterate :before #'org-table-remove-untouchable)
(advice-add 'org-table-recalculate :before
#'org-table-recalculate-remove-untouchable)

---------------------------------------------------------

Sincerely,
Ruy Exel

[-- Attachment #2: Type: text/html, Size: 4740 bytes --]

Re: Table refuses to forget cell formula which once overrode a column formula

[Nicolas Goaziou]

Hello,

Ruy Exel <ruyexel@gmail.com> writes:

> The following is very simple org-mode spreadsheet in which the cells in the
> second column show the triple of the corresponding cell in the first column
> due to the "column formula" in its TBLFM row $2=3*$1;N. However the third
> row (actually row @4) is an exception because of the "cell formula"
> @4$2=string("Exception") which expectedly overrides the above column
> formula.
>
> |--------+-----------|
> | Number |    Triple |
> |--------+-----------|
> |      1 |         3 |
> |      2 |         6 |
> |      3 | Exception |
> |      4 |        12 |
> |--------+-----------|
>
> #+TBLFM: $2=3*$1::@4$2=string("Exception")
>
> So all is well. Below you will find a copy of the above table, except that,
> after copying, I have deleted the exceptional formula affecting cell @4$2.
>
> |--------+-----------|
> | Number |    Triple |
> |--------+-----------|
> |      1 |         3 |
> |      2 |         6 |
> |      3 | Exception |
> |      4 |        12 |
> |--------+-----------|
>
> #+TBLFM: $2=3*$1
>
> One would expect that, after updating this table with C-u C-c *, or C-c C-c
> in the TBLFM line, the exceptional value in cell @4$2 would revert to the
> triple of '3', but instead the string "Exception" stays put.

Fixed. Thank you.

Regards,

-- 
Nicolas Goaziou

Re: Table refuses to forget cell formula which once overrode a column formula

[Ruy Exel]

[-- Attachment #1: Type: text/plain, Size: 1798 bytes --]

Hi Nicolas,

Thanks very much.  Just out of curiosity I'd be interested in understanding
how did you do it.  My (non expert) impression is that using properties to
override column formulas isn't a good idea.  It seems to me it would be
more natural to apply cell formulas AFTER all column formulas were applied,
so that each cell would be simply the output of the formula defining it.

Best,
Ruy

On Nov 15, 2017 14:42, "Nicolas Goaziou" <mail@nicolasgoaziou.fr> wrote:

Hello,

Ruy Exel <ruyexel@gmail.com> writes:

> The following is very simple org-mode spreadsheet in which the cells in
the
> second column show the triple of the corresponding cell in the first
column
> due to the "column formula" in its TBLFM row $2=3*$1;N. However the third
> row (actually row @4) is an exception because of the "cell formula"
> @4$2=string("Exception") which expectedly overrides the above column
> formula.
>
> |--------+-----------|
> | Number |    Triple |
> |--------+-----------|
> |      1 |         3 |
> |      2 |         6 |
> |      3 | Exception |
> |      4 |        12 |
> |--------+-----------|
>
> #+TBLFM: $2=3*$1::@4$2=string("Exception")
>
> So all is well. Below you will find a copy of the above table, except
that,
> after copying, I have deleted the exceptional formula affecting cell @4$2.
>
> |--------+-----------|
> | Number |    Triple |
> |--------+-----------|
> |      1 |         3 |
> |      2 |         6 |
> |      3 | Exception |
> |      4 |        12 |
> |--------+-----------|
>
> #+TBLFM: $2=3*$1
>
> One would expect that, after updating this table with C-u C-c *, or C-c
C-c
> in the TBLFM line, the exceptional value in cell @4$2 would revert to the
> triple of '3', but instead the string "Exception" stays put.

Fixed. Thank you.

Regards,

--
Nicolas Goaziou

[-- Attachment #2: Type: text/html, Size: 2763 bytes --]

Re: Table refuses to forget cell formula which once overrode a column formula

[Nick Dokos]

Ruy Exel <ruyexel@gmail.com> writes:

> Hi Nicolas,
>
> Thanks very much.  Just out of curiosity I'd be interested in understanding how did you do it.  My (non
> expert) impression is that using properties to override column formulas isn't a good idea.  It seems to
> me it would be more natural to apply cell formulas AFTER all column formulas were applied, so that each
> cell would be simply the output of the formula defining it.
>

Here's what I do in such cases (I'm assuming that the change exists in master, either because it
was applied there in the first place, or after a merge with maint):

- update my git repo: git checkout master; git remote update; git rebase
- check recent history: git log -20 --oneline
- find the commit of interest - in this case

   6059c3a05 org-table: Clean left-over `:org-untouchable' property

- show the commit: git show 6059c3a05

HTH.

>
> On Nov 15, 2017 14:42, "Nicolas Goaziou" <mail@nicolasgoaziou.fr> wrote:
>
>     Hello,
>    
>     Ruy Exel <ruyexel@gmail.com> writes:
>    
>     > The following is very simple org-mode spreadsheet in which the cells in the
>     > second column show the triple of the corresponding cell in the first column
>     > due to the "column formula" in its TBLFM row $2=3*$1;N. However the third
>     > row (actually row @4) is an exception because of the "cell formula"
>     > @4$2=string("Exception") which expectedly overrides the above column
>     > formula.
>     >
>     > |--------+-----------|
>     > | Number |    Triple |
>     > |--------+-----------|
>     > |      1 |         3 |
>     > |      2 |         6 |
>     > |      3 | Exception |
>     > |      4 |        12 |
>     > |--------+-----------|
>     >
>     > #+TBLFM: $2=3*$1::@4$2=string("Exception")
>     >
>     > So all is well. Below you will find a copy of the above table, except that,
>     > after copying, I have deleted the exceptional formula affecting cell @4$2.
>     >
>     > |--------+-----------|
>     > | Number |    Triple |
>     > |--------+-----------|
>     > |      1 |         3 |
>     > |      2 |         6 |
>     > |      3 | Exception |
>     > |      4 |        12 |
>     > |--------+-----------|
>     >
>     > #+TBLFM: $2=3*$1
>     >
>     > One would expect that, after updating this table with C-u C-c *, or C-c C-c
>     > in the TBLFM line, the exceptional value in cell @4$2 would revert to the
>     > triple of '3', but instead the string "Exception" stays put.
>    
>     Fixed. Thank you.
>    
>     Regards,
>    
>     --
>     Nicolas Goaziou
>

-- 
Nick

Re: Table refuses to forget cell formula which once overrode a column formula

[Nicolas Goaziou]

Ruy Exel <ruyexel@gmail.com> writes:

> Thanks very much.  Just out of curiosity I'd be interested in understanding
> how did you do it.  My (non expert) impression is that using properties to
> override column formulas isn't a good idea.

The property is here to prevent evaluation of a column formula on
a given field. It doesn't override anything.

> It seems to me it would be more natural to apply cell formulas AFTER
> all column formulas were applied, so that each cell would be simply
> the output of the formula defining it.

This is what happens already. The property prevents useless
computations.

Regards,

Re: Table refuses to forget cell formula which once overrode a column formula

[Ruy Exel]

[-- Attachment #1: Type: text/plain, Size: 821 bytes --]

Thanks for your reply.  I now understand and I also agree it is a good
solution.

Best,
Ruy

On Nov 15, 2017 19:54, "Nicolas Goaziou" <mail@nicolasgoaziou.fr> wrote:

> Ruy Exel <ruyexel@gmail.com> writes:
>
> > Thanks very much.  Just out of curiosity I'd be interested in
> understanding
> > how did you do it.  My (non expert) impression is that using properties
> to
> > override column formulas isn't a good idea.
>
> The property is here to prevent evaluation of a column formula on
> a given field. It doesn't override anything.
>
> > It seems to me it would be more natural to apply cell formulas AFTER
> > all column formulas were applied, so that each cell would be simply
> > the output of the formula defining it.
>
> This is what happens already. The property prevents useless
> computations.
>
> Regards,
>

[-- Attachment #2: Type: text/html, Size: 1300 bytes --]