Proposal: In-place formulas in tables

Proposal: In-place formulas in tables

[Simon Winwood]

       Hi, I recently got bitten (not badly, say nibbled then), when I
had a formula in a field to sum a column of numbers and added another
data row --- when I C-c C-c'd to update the sum, the last data row was
overwritten (i.e, not the formula field).

For example:

|---+---|
| a | 1 |
| b | 2 |
|---+---|
|   | 3 |
#+TBLFM: @3$2=vsum(@1$2..@-I$2)

add row X and recalculate (note b is overwritten by 1):
|---+---|
| a | 1 |
| X | 7 |
| b | 1 |
|---+---|
|   | 3 |
#+TBLFM: @3$2=vsum(@1$2..@-I$2)

       Obviously the code is doing the right thing according to the
TBLFM rules, it just isn't what I (and, I assume, other people) would
expect.

       So, a solution: rather than overwrite formulas in fields with
their values, use overlays to make the formula invisible and set
before-string (or after-string) to be the calculated value.  

    Issues: 
    
       * You probably want to enable toggling of invisibility, and
       somehow mark values as calc-values.  Maybe have a command to
       edit field formulas in the minibuffer?

       * The code to align tables would need to take into
       consideration the values in the overlay, not the text.
       
       * Ditto for calculating values

       * Cut-and-paste of tables doesn't give you what you expect
       (i.e., you still have formulas, not values)

    Comments?  I could probably hack this in, but someone who knows
    the code could do it in a few hours (if that) and better than I.
	 
	 Simon

Re: Proposal: In-place formulas in tables

[Carsten Dominik]

On May 7, 2007, at 12:24, Simon Winwood wrote:

>
>        Hi, I recently got bitten (not badly, say nibbled then), when I
> had a formula in a field to sum a column of numbers and added another
> data row --- when I C-c C-c'd to update the sum, the last data row was
> overwritten (i.e, not the formula field).
>
> For example:
>
> |---+---|
> | a | 1 |
> | b | 2 |
> |---+---|
> |   | 3 |
> #+TBLFM: @3$2=vsum(@1$2..@-I$2)
>
> add row X and recalculate (note b is overwritten by 1):
> |---+---|
> | a | 1 |
> | X | 7 |
> | b | 1 |
> |---+---|
> |   | 3 |
> #+TBLFM: @3$2=vsum(@1$2..@-I$2)
>
>        Obviously the code is doing the right thing according to the
> TBLFM rules, it just isn't what I (and, I assume, other people) would
> expect.
>
>        So, a solution: rather than overwrite formulas in fields with
> their values, use overlays to make the formula invisible and set
> before-string (or after-string) to be the calculated value.

You are hitting a very fundamental difference between org-mode tables
an the usual "spreadsheet" programs.  In a spreadsheet, a table field 
contains a formula, and the result is displayed.  In Org-mode tables, 
the table fieldd only contains the field value, never a formula.  This 
is because Org-mode tables are plain text that can be read as is.  A 
field never contains a formula - one of the nice aspectes of this is 
that you can write column formulas without copying a formula to each 
field in the table.

Now the problem is, that you can edit plain text any way you
like and in this way break the implicit relation between
formuals and fields.  This is a fundamental problem, ven if we would 
store formulas in fields.

What you can to do avoid problems like the one you encountered is
to use org-mode commands to create new rows or columns.  For example
to make a new row, place the cursor on a row and press S-M-down.
Then the formulas will be modified to account for this change.

- Carsten