[Orgmode] Re: new spreadsheet features: field coordinates, multi line TBLFM with comments

[Carsten Dominik]

Hi Michael,

On Mar 13, 2010, at 4:27 PM, Michael Brand wrote:

> Hi Carsten, hi all
>
> 1) field coordinates:
> As a proposal I implemented the following to cover one of my  
> spreadsheet use cases: Why not let address@hidden' and `$#' in Calc formulas  
> (works also for Lisp formulas) be substituted to the row or column  
> number of the formula result field?. The traditional Lisp formula  
> equivalents are org-table-current-dline and org-table-current- 
> column. I believe it does not break the TBLFM syntax for any other  
> use.
>
> My use case is an overview with the average annual relative changes  
> of a time series. Here is an example with averages for a history of  
> 1 year up to 4 years:
> | year | quote |   1 a |   2 a |   3 a |   4 a |
> |------+-------+-------+-------+-------+-------|
> | 2005 |    10 |       |       |       |       |
> | 2006 |    12 | 0.200 |       |       |       |
> | 2007 |    14 | 0.167 | 0.183 |       |       |
> | 2008 |    16 | 0.143 | 0.155 | 0.170 |       |
> | 2009 |    18 | 0.125 | 0.134 | 0.145 | 0.158 |
> #+TBLFM: $3 = if(@# + 1 > $#, ($2 / subscr(@address@hidden, @# + 1 -  
> $#)) ^ (1 / ($# - 2)) - 1, string("")); f3 :: $4 = if(@# + 1 > $#,  
> ($2 / subscr(@address@hidden, @# + 1 - $#)) ^ (1 / ($# - 2)) - 1,  
> string("")); f3 :: $5 = if(@# + 1 > $#, ($2 / subscr(@address@hidden,  
> @# + 1 - $#)) ^ (1 / ($# - 2)) - 1, string("")); f3 :: $6 = if(@# +  
> 1 > $#, ($2 / subscr(@address@hidden, @# + 1 - $#)) ^ (1 / ($# - 2)) -  
> 1, string("")); f3
>
> The patch (the files with `---' were taken from org-version 6.34c):
> ======================================================================
> --- a/doc/org.texi
> +++ b/doc/org.texi
> @@ -2098,6 +2098,21 @@ table in that entry.  REF is an absolute
> described above for example @code{@@3$3} or @code{$somename}, valid  
> in the
> referenced table.
>
> address@hidden Field coordinates
> address@hidden field coordinates
> address@hidden coordinates, of field
> address@hidden row, of field coordinates
> address@hidden column, of field coordinates
> +
> +For Calc formulas and Lisp formulas @code{@@#} and @code{$#} can be  
> used to
> +get the row or column number of the field where the formula result  
> goes.
> +The traditional Lisp formula equivalents are @code{org-table- 
> current-dline}
> +and @code{org-table-current-column}.  Example:
> +
> address@hidden
> +if(@@# % 2, $#, string(""))      @r{column number on odd lines only}
> address@hidden example
> +
> @node Formula syntax for Calc, Formula syntax for Lisp, References,  
> The spreadsheet
> @subsection Formula syntax for Calc
> @cindex formula syntax, Calc
> --- a/lisp/org-table.el
> +++ b/lisp/org-table.el
> @@ -2243,6 +2243,21 @@ not overwrite the stored one."
>         (setq form (copy-sequence formula)
> 	      lispp (and (> (length form) 2)(equal (substring form 0 2)  
> "'(")))
>         (if (and lispp literal) (setq lispp 'literal))
> +
> +       ;; Insert row number of formula result field
> +       (while (string-match "address@hidden" form)
> +         (setq form
> +               (replace-match
> +                (save-match-data
> +                  (format "%d" (org-table-current-dline)))
> +                t t form)))
> +       ;; Insert column number of formula result field
> +       (while (string-match "\\$#" form)
> +         (setq form
> +               (replace-match
> +                (save-match-data
> +                  (format "%d" (org-table-current-column)))
> +                t t form)))
>         ;; Check for old vertical references
>         (setq form (org-table-rewrite-old-row-references form))
>         ;; Insert remote references
> ======================================================================

That does look reasonable, and I am going to take that patch.


> 2) self-edited multi line TBLFM with comments:
> For me it would be great to have the now missing option to hack all  
> the formulas myself just directly, but rather into a multi line  
> TBLFM with comments than into the one line #+TBLFM:. For the use  
> case above this would look like:

Before looking further, I would like to find out if you are aware
of the existence of the formula editior, invoked the command
C-c ' (that is C-c followed by the single quote) while the
cursor is in a table.

The formula editor does not have comments, but otherwise it is more  
powerful than what you propose.

- Carsten

> [...]
> | 2009 |    18 | 0.125 | 0.134 | 0.145 | 0.158 |
> #+BEGIN_TBLFM
> $3 = if(@# + 1 > $#, ($2 / subscr(@address@hidden, @# + 1 - $#)) ^
>        (1 / ($# - 2)) - 1, string("")); f3 :: # rel. change for 1 year
> $4 = if(@# + 1 > $#, ($2 / subscr(@address@hidden, @# + 1 - $#)) ^
>        (1 / ($# - 2)) - 1, string("")); f3 :: # average for 2 years
> $5 = if(@# + 1 > $#, ($2 / subscr(@address@hidden, @# + 1 - $#)) ^
>        (1 / ($# - 2)) - 1, string("")); f3 :: # average for 3 years
> $6 = if(@# + 1 > $#, ($2 / subscr(@address@hidden, @# + 1 - $#)) ^
>        (1 / ($# - 2)) - 1, string("")); f3    # average for 4 years
> #+END_TBLFM
>
> I suggest to leave the one line #+TBLFM without the comment  
> possibility to avoid issues with the very nice write back to #+TBLFM  
> when editing with e. g. `C-c =' or moving rows and columns. In favor  
> of a complexity reduction and to avoid confusion I suggest to leave  
> the #+BEGIN_TBLFM option without write back and to unconditionally  
> reject a trial to do it. Similar to the nice reject when trying to  
> edit column 1 with `C-c =' in the following example, where #+TBLFM  
> has been upset by self-editing it:
> | 0 |
> #+TBLFM: $1 = 0 :: $1 = 0
>
> This way the implementation for the multi line TBLFM with comments  
> could on evaluation simply strip the comments from all the lines  
> between #+BEGIN_TBLFM and #+END_TBLFM, join the rest of these lines  
> into one line and pass it to where the #+TBLFM: content is passed  
> today. Of course there would still be more to do to make the whole  
> work.
>
> Surprise: #+BEGIN_TBLFM already folds by TAB without implementing  
> anything yet.
>
> <side_note on comments>
> The TBLFM comments should not be parsed as `#' but rather ` #' in  
> order to not break
> a) the new field coordinates address@hidden' and `$#' if done as in my proposal  
> above
> b) the Calc input radix
>
> Example:
> | (1, 1) | (1, 2) | 256 |
> #+BEGIN_TBLFM
> $1 = (@#, $#) :: $2 = (@#, $#) :: # a) numbers of row and column
> $3 = 16#100                       # b) Calc input radix with hash char
> #+END_TBLFM
>
> I'm not sure if adding a regexp for multi line TBLFM comments to  
> some face for highlighting would make too much trouble with breaking  
> other stuff containing ` #' outside multi line TBLFM.
> </side_note>
>
> How easy would that be to implement?
>
> Michael

- Carsten