Problem: Moving rows in a table changes vsum start and end

Problem: Moving rows in a table changes vsum start and end

[Torsten Wagner]

Hi,

I just notice a funny row-shift effect, having a table with a field
calculated by vsum.


| Nr. | value |
|-----+-------|
|   1 |     5 |
|   2 |     5 |
|   3 |     5 |
|   4 |     5 |
|   5 |     5 |
|   6 |     5 |
|-----+-------|
| Sum |    30 |
#+TBLFM: @8$2=vsum(@2..@7)

now shift (sort) the rows by using M-arrowup and M-arrowdown


| Nr. | value |
|-----+-------|
|   1 |     5 |
|   6 |     5 |
|   5 |     5 |
|   3 |     5 |
|   4 |     5 |
|   2 |     5 |
|-----+-------|
| Sum |    10 |
#+TBLFM: @8$2=vsum(@2..@3)

Check, the #+TBLM:-line changed too! This might be desired sometimes
but might also trouble people if they do not give careful attention.

I know someone could do

| Nr. | value |
|-----+-------|
|   1 |     5 |
|   6 |     5 |
|   5 |     5 |
|   3 |     5 |
|   4 |     5 |
|   2 |     5 |
|-----+-------|
| Sum |    30 |
#+TBLFM: @8$2=vsum(@I..@II)

But still I feel that people can too easily trap into wrong calculus.

One solution, I could see is then whenever forms change
"automagically" highlight this changes within the TBFM line, e.g.,  by
a change of the background color in a similar way like matching
parenthesis. That might help to make people more aware of it and shows
which equations are affected by the current operation.


All the best

Torsten

Re: Problem: Moving rows in a table changes vsum start and end

[Bastien]

Hi Torsten,

Torsten Wagner <torsten.wagner@gmail.com> writes:

> One solution, I could see is then whenever forms change
> "automagically" highlight this changes within the TBFM line, e.g.,  by
> a change of the background color in a similar way like matching
> parenthesis. That might help to make people more aware of it and shows
> which equations are affected by the current operation.

Good idea.  Patch welcome,

-- 
 Bastien

Re: Problem: Moving rows in a table changes vsum start and end

[Torsten Wagner]

Hi Bastien,

>
> Good idea.  Patch welcome,
Hehehe,
everytime you do this, I feel more embarrassed about my poor elisp knowledge. ;)
I might start very very simple and hope you are wiling enough to help
me to translate whatever mess I send you into some reasonable patch ;)

Torsten

> --
>  Bastien

Re: Problem: Moving rows in a table changes vsum start and end

[Bastien]

Hi Torsten,

Torsten Wagner <torsten.wagner@gmail.com> writes:

> everytime you do this, I feel more embarrassed about my poor elisp knowledge. ;)
> I might start very very simple and hope you are wiling enough to help
> me to translate whatever mess I send you into some reasonable patch ;)

Sure!  Actually I was not point at *you* in particular, "patch welcome"
just means "if someone wants to put a stab, please feel free..."

But thanks in advance if _you_ help with this!

-- 
 Bastien

Re: Problem: Moving rows in a table changes vsum start and end

[Torsten Wagner]

Hi Bastien,

ok,  I think it should be in org-table.el

and the function in question might be org-table-fix-formulas

I added

(defface org-table-formular-change-face
   '((t (:background "red")))
   "Used parts of tabe formulars which change by row and column moving
operations.")

to define a new face for changes in the formular.

Then I looked around how to do the face changing. By that I noticed,
org-mode uses its own set of functions (org-table-highlight-rectangle,
org-table-remove-rectangle-highlight).
Searching more around I figured out that emacs already comes with a
function for highlighting.

http://www.gnu.org/software/emacs/manual/html_node/emacs/Highlight-Interactively.html#Highlight-Interactively

Which makes me wonder why org-mode don't use that (maybe historical
reasons, the above function is not that old).
From this there are mainly two functions of interest

highlight-regexp
unhighlight-regexp (btw. a not very emacs conform naming)

they take a regular expression as input and optional the face to be set.
It works great in interactive mode.

So I was looking how to modify the function org-table-fix-formulas
using the above commands

I ended up with a very small change

(defun org-table-fix-formulas (key replace &optional limit delta remove)
  "Modify the equations after the table structure has been edited.
KEY is \"@\" or \"$\".  REPLACE is an alist of numbers to replace.
For all numbers larger than LIMIT, shift them by DELTA."
  (save-excursion
    (goto-char (org-table-end))
    (when (let ((case-fold-search t)) (looking-at "[ \t]*#\\+tblfm:"))
      (let ((re (concat key "\\([0-9]+\\)"))
	    (re2
	     (when remove
	       (if (or (equal key "$") (equal key "$LR"))
		   (format "\\(@[0-9]+\\)?%s%d=.*?\\(::\\|$\\)"
			   (regexp-quote key) remove)
		 (format "@%d\\$[0-9]+=.*?\\(::\\|$\\)" remove))))
	    s n a)
	(when remove
	  (while (re-search-forward re2 (point-at-eol) t)
	    (unless (save-match-data (org-in-regexp "remote([^)]+?)"))
	      (if (equal (char-before (match-beginning 0)) ?.)
		  (error "Change makes TBLFM term %s invalid.  Use undo to recover."
			 (match-string 0))
		(replace-match "")))))
	(while (re-search-forward re (point-at-eol) t)
	  (unless (save-match-data (org-in-regexp "remote([^)]+?)"))
	    (setq s (match-string 1) n (string-to-number s))
	    (cond
	     ((setq a (assoc s replace))
	      (replace-match (concat key (cdr a)) t t))
	     ((and limit (> n limit))
	      (replace-match (concat key (int-to-string (+ n delta)))
	      t t)))

;Added the single line below
	    (highlight-regexp re 'org-table-formular-change-face)
; really only the above line

	    ))))))


However, it  does not work.
Defining the face and using the above line works correct in the scratch buffer,
However, calling the function within org-mode (moving rows in a table)
I do not get an error message and do not get a highlight...
I made sure my modified version but no luck yet.

I greatly lack elisp knowledge, would be glad if you could give me
some advice why it doesn't work.

Furthermore, you might like to check about the highlight functions.
You might be able to simplify some of the org-table.el code by
relying on those functions instead of defining own functions. If we
get this started, I dream of
slightly different face different parts of org-tables:
* cells base on a formula and not been updated, despite changes were
made in the table,
* cells base on a formula but good overwritten by the user (this is
another dangerous situation I found myself in)
* highlight the formulas for the cell in which the pointer is located
(kind of reveres of the current fomular  editor highlighting)
* highlight changes after recalculation of an table

As for the last point. I also found the minor mode "highlight-changes-mode"
This is a great mode and calling it before starting to manipulate a
org-table, already ticks some of the above points. Please try if you
are not aware of it (create a table with some forms, call
highlight-changes-mode, do some operations on the table).

Greetings

Torsten





On 28 September 2012 22:53, Bastien <bzg@altern.org> wrote:
> Hi Torsten,
>
> Torsten Wagner <torsten.wagner@gmail.com> writes:
>
>> everytime you do this, I feel more embarrassed about my poor elisp knowledge. ;)
>> I might start very very simple and hope you are wiling enough to help
>> me to translate whatever mess I send you into some reasonable patch ;)
>
> Sure!  Actually I was not point at *you* in particular, "patch welcome"
> just means "if someone wants to put a stab, please feel free..."
>
> But thanks in advance if _you_ help with this!
>
> --
>  Bastien

Re: Problem: Moving rows in a table changes vsum start and end

[Bastien]

Hi Torsten,

Torsten Wagner <torsten.wagner@gmail.com> writes:

> ok,  I think it should be in org-table.el
>
> and the function in question might be org-table-fix-formulas

Right.

> I added
>
> (defface org-table-formular-change-face
>    '((t (:background "red")))
>    "Used parts of tabe formulars which change by row and column moving
> operations.")

Faces are for visual clues that are here to stay, for transitory
highlighting, you'd better use overlays.  (See "Overlays" in the
Elisp manual.)

> to define a new face for changes in the formular.
>
> Then I looked around how to do the face changing. By that I noticed,
> org-mode uses its own set of functions (org-table-highlight-rectangle,
> org-table-remove-rectangle-highlight).
> Searching more around I figured out that emacs already comes with a
> function for highlighting.
>
> http://www.gnu.org/software/emacs/manual/html_node/emacs/Highlight-Interactively.html#Highlight-Interactively
>
> Which makes me wonder why org-mode don't use that (maybe historical
> reasons, the above function is not that old).

I haven't closely looked at hi-lock.el but I think this is more for
ad-hoc highlighting rather than for mode highlighting.  I guess it's 
faster to set the face text property than to use these functions.

> From this there are mainly two functions of interest
>
> highlight-regexp
> unhighlight-regexp (btw. a not very emacs conform naming)
>
> they take a regular expression as input and optional the face to be set.
> It works great in interactive mode.
>
> So I was looking how to modify the function org-table-fix-formulas
> using the above commands
>
> I ended up with a very small change
>
> (defun org-table-fix-formulas (key replace &optional limit delta remove)
>   "Modify the equations after the table structure has been edited.
> KEY is \"@\" or \"$\".  REPLACE is an alist of numbers to replace.
> For all numbers larger than LIMIT, shift them by DELTA."
>   (save-excursion
>     (goto-char (org-table-end))
>     (when (let ((case-fold-search t)) (looking-at "[ \t]*#\\+tblfm:"))
>       (let ((re (concat key "\\([0-9]+\\)"))
> 	    (re2
> 	     (when remove
> 	       (if (or (equal key "$") (equal key "$LR"))
> 		   (format "\\(@[0-9]+\\)?%s%d=.*?\\(::\\|$\\)"
> 			   (regexp-quote key) remove)
> 		 (format "@%d\\$[0-9]+=.*?\\(::\\|$\\)" remove))))
> 	    s n a)
> 	(when remove
> 	  (while (re-search-forward re2 (point-at-eol) t)
> 	    (unless (save-match-data (org-in-regexp "remote([^)]+?)"))
> 	      (if (equal (char-before (match-beginning 0)) ?.)
> 		  (error "Change makes TBLFM term %s invalid.  Use undo to recover."
> 			 (match-string 0))
> 		(replace-match "")))))
> 	(while (re-search-forward re (point-at-eol) t)
> 	  (unless (save-match-data (org-in-regexp "remote([^)]+?)"))
> 	    (setq s (match-string 1) n (string-to-number s))
> 	    (cond
> 	     ((setq a (assoc s replace))
> 	      (replace-match (concat key (cdr a)) t t))
> 	     ((and limit (> n limit))
> 	      (replace-match (concat key (int-to-string (+ n delta)))
> 	      t t)))
>
> ;Added the single line below
> 	    (highlight-regexp re 'org-table-formular-change-face)
> ; really only the above line
>
> 	    ))))))

This cannot work because `re' is the regular expression you are
searching for -- thus the string matched by the re will be replaced.

What we want is to add an overlay on the part of the formula that
has been updated, namely the string in (replace-match STRING ...)
constructs.

> However, it  does not work.
> Defining the face and using the above line works correct in the scratch buffer,
> However, calling the function within org-mode (moving rows in a table)
> I do not get an error message and do not get a highlight...
> I made sure my modified version but no luck yet.

Another thing(y): I'd like the overlay to be transient... 
So maybe overlaying the replacement with a timer to fade it away
is the way to go.  If you didn't already, you can have a look at
how `org-table-edit-formulas' does its job.

> Furthermore, you might like to check about the highlight functions.
> You might be able to simplify some of the org-table.el code by
> relying on those functions instead of defining own functions. If we
> get this started, I dream of
> slightly different face different parts of org-tables:
> * cells base on a formula and not been updated, despite changes were
> made in the table,
> * cells base on a formula but good overwritten by the user (this is
> another dangerous situation I found myself in)
> * highlight the formulas for the cell in which the pointer is located
> (kind of reveres of the current fomular  editor highlighting)
> * highlight changes after recalculation of an table
>
> As for the last point. I also found the minor mode "highlight-changes-mode"
> This is a great mode and calling it before starting to manipulate a
> org-table, already ticks some of the above points. Please try if you
> are not aware of it (create a table with some forms, call
> highlight-changes-mode, do some operations on the table).

Thanks for the tip!  I'm aware of it but don't use it that much in
tables because a simple M-<right> will highlight the whole table...
but yes, this is one of the great libraries in Emacs.  (Did I already
acknowledge Emacs is great?)

If you want to dig further for the temporary overlay, you may have
a look at this library:

http://sachachua.com/notebook/emacs/highlight-tail.el

Best,

-- 
 Bastien

Re: Problem: Moving rows in a table changes vsum start and end

[Bastien]

Hi Torsten,

Torsten Wagner <torsten.wagner@gmail.com> writes:

> I might start very very simple and hope you are wiling enough to help
> me to translate whatever mess I send you into some reasonable patch ;)

I just pushed this: Org now sends a message when the formulas
have been updated.  Something less intrusive like a temporary 
overlay would be nice, though.

-- 
 Bastien

Re: Problem: Moving rows in a table changes vsum start and end

[Torsten Wagner]

Hi Bastien,

my  last mail overlapped, with yours. Maybe some of the stuff I said
is redundant now.

Greetings
Torsten


On 29 September 2012 01:00, Bastien <bzg@altern.org> wrote:
> Hi Torsten,
>
> Torsten Wagner <torsten.wagner@gmail.com> writes:
>
>> I might start very very simple and hope you are wiling enough to help
>> me to translate whatever mess I send you into some reasonable patch ;)
>
> I just pushed this: Org now sends a message when the formulas
> have been updated.  Something less intrusive like a temporary
> overlay would be nice, though.
>
> --
>  Bastien

Re: Problem: Moving rows in a table changes vsum start and end

[Carsten Dominik]

On 28.9.2012, at 18:00, Bastien wrote:

> Hi Torsten,
> 
> Torsten Wagner <torsten.wagner@gmail.com> writes:
> 
>> I might start very very simple and hope you are wiling enough to help
>> me to translate whatever mess I send you into some reasonable patch ;)
> 
> I just pushed this: Org now sends a message when the formulas
> have been updated.  Something less intrusive like a temporary 
> overlay would be nice, though.

But I don't think an overlay would work anyway, because the change could be easily off the screen beyond the right edge.  A message is better - lets see if it gets annoying.

Thorsten, if you look at the manual, there are ways to write this limits of vsum etc in a way that they are relative to the table boundaries or to horizontal lines.  This is robust agains changes of rows.

- Carsten

> 
> -- 
> Bastien
> 

Re: Problem: Moving rows in a table changes vsum start and end

[Achim Gratz]

Carsten Dominik writes:
>> I just pushed this: Org now sends a message when the formulas
>> have been updated.  Something less intrusive like a temporary 
>> overlay would be nice, though.

> Thorsten, if you look at the manual, there are ways to write this
> limits of vsum etc in a way that they are relative to the table
> boundaries or to horizontal lines.  This is robust agains changes of
> rows.

This would be easier to do if the formula editor didn't have the bug
I've reported some time ago.  Any chance that you or someone else could
have a look at it?  I've tried to find the culprit, but there's just too
much going on at the same time in that part of the code and whatever
I've fixed in one area would break something else in another… :-(


Regards,
Achim.
-- 
+<[Q+ Matrix-12 WAVE#46+305 Neuron microQkb Andromeda XTk Blofeld]>+

Wavetables for the Terratec KOMPLEXER:
http://Synth.Stromeko.net/Downloads.html#KomplexerWaves

Re: Problem: Moving rows in a table changes vsum start and end

[Bastien]

Hi Achim,

Achim Gratz <Stromeko@nexgo.de> writes:

> This would be easier to do if the formula editor didn't have the bug
> I've reported some time ago.  Any chance that you or someone else could
> have a look at it?  I've tried to find the culprit, but there's just too
> much going on at the same time in that part of the code and whatever
> I've fixed in one area would break something else in another… :-(

Can you point at this bug again?  I might have a quick look.  

-- 
 Bastien

Re: Problem: Moving rows in a table changes vsum start and end

[Achim Gratz]

Bastien writes:
>> This would be easier to do if the formula editor didn't have the bug
>> I've reported some time ago.  Any chance that you or someone else could
>> have a look at it?  I've tried to find the culprit, but there's just too
>> much going on at the same time in that part of the code and whatever
>> I've fixed in one area would break something else in another… :-(
>
> Can you point at this bug again?  I might have a quick look.  

http://permalink.gmane.org/gmane.emacs.orgmode/52744


Regards,
Achim.
-- 
+<[Q+ Matrix-12 WAVE#46+305 Neuron microQkb Andromeda XTk Blofeld]>+

Factory and User Sound Singles for Waldorf rackAttack:
http://Synth.Stromeko.net/Downloads.html#WaldorfSounds

Re: Problem: Moving rows in a table changes vsum start and end

[Carsten Dominik]

On 29.9.2012, at 11:57, Achim Gratz wrote:

> Carsten Dominik writes:
>>> I just pushed this: Org now sends a message when the formulas
>>> have been updated.  Something less intrusive like a temporary 
>>> overlay would be nice, though.
> 
>> Thorsten, if you look at the manual, there are ways to write this
>> limits of vsum etc in a way that they are relative to the table
>> boundaries or to horizontal lines.  This is robust agains changes of
>> rows.
> 
> This would be easier to do if the formula editor didn't have the bug
> I've reported some time ago.  Any chance that you or someone else could
> have a look at it?  I've tried to find the culprit, but there's just too
> much going on at the same time in that part of the code and whatever
> I've fixed in one area would break something else in another… :-(

I did have a look, and it is very difficult to fix, unfortunately.  If I find enough time, I will rewrite the whole range parser, but that is totally non-trivial.  Sorry about that.

- Carsten

> 
> 
> Regards,
> Achim.
> -- 
> +<[Q+ Matrix-12 WAVE#46+305 Neuron microQkb Andromeda XTk Blofeld]>+
> 
> Wavetables for the Terratec KOMPLEXER:
> http://Synth.Stromeko.net/Downloads.html#KomplexerWaves
> 
> 

Re: Problem: Moving rows in a table changes vsum start and end

[Bastien]

Carsten Dominik <carsten.dominik@gmail.com> writes:

> I did have a look, and it is very difficult to fix, unfortunately.  If I
> find enough time, I will rewrite the whole range parser, but that is
> totally non-trivial.  Sorry about that.

Er... Achim, forget about my own proposal then :)  

(Still, the link would be useful for archiving's sake.)

-- 
 Bastien

Re: Problem: Moving rows in a table changes vsum start and end

[Achim Gratz]

Carsten Dominik writes:
> I did have a look, and it is very difficult to fix, unfortunately.  If
> I find enough time, I will rewrite the whole range parser, but that is
> totally non-trivial.  Sorry about that.

That explains my lack of luck… :-)

Any chance that org-element might be helpful in a re-implementation?
I'm still having that tableheadings branch un-merged since I decided
that I didn't want to implement it for all the old exporters when they
were already deprecated.  But it is surely something I'd want to push
for 8.0 and that will have to be taken into account for formulas just as
well, so I'll have to familiarize myself with that part of the code
anyway.


Regards,
Achim.
-- 
+<[Q+ Matrix-12 WAVE#46+305 Neuron microQkb Andromeda XTk Blofeld]>+

SD adaptation for Waldorf Blofeld V1.15B11:
http://Synth.Stromeko.net/Downloads.html#WaldorfSDada

Re: Problem: Moving rows in a table changes vsum start and end

[Carsten Dominik]

On 29.9.2012, at 12:11, Achim Gratz wrote:

> Carsten Dominik writes:
>> I did have a look, and it is very difficult to fix, unfortunately.  If
>> I find enough time, I will rewrite the whole range parser, but that is
>> totally non-trivial.  Sorry about that.
> 
> That explains my lack of luck… :-)
> 
> Any chance that org-element might be helpful in a re-implementation?
> I'm still having that tableheadings branch un-merged since I decided
> that I didn't want to implement it for all the old exporters when they
> were already deprecated.  But it is surely something I'd want to push
> for 8.0 and that will have to be taken into account for formulas just as
> well, so I'll have to familiarize myself with that part of the code
> anyway.

This time I do not remember what this was.  Do you have a reminder link for me?

Regards

- Carsten

> 
> 
> Regards,
> Achim.
> -- 
> +<[Q+ Matrix-12 WAVE#46+305 Neuron microQkb Andromeda XTk Blofeld]>+
> 
> SD adaptation for Waldorf Blofeld V1.15B11:
> http://Synth.Stromeko.net/Downloads.html#WaldorfSDada
> 
> 

Re: Problem: Moving rows in a table changes vsum start and end

[Achim Gratz]

Carsten Dominik writes:
> On 29.9.2012, at 12:11, Achim Gratz wrote:
>> Any chance that org-element might be helpful in a re-implementation?
>> I'm still having that tableheadings branch un-merged since I decided
>> that I didn't want to implement it for all the old exporters when they
>> were already deprecated.  But it is surely something I'd want to push
>> for 8.0 and that will have to be taken into account for formulas just as
>> well, so I'll have to familiarize myself with that part of the code
>> anyway.
>
> This time I do not remember what this was.  Do you have a reminder link for me?

The discussion is here:
http://thread.gmane.org/gmane.emacs.orgmode/31402/focus=31443


Regards,
Achim.
-- 
+<[Q+ Matrix-12 WAVE#46+305 Neuron microQkb Andromeda XTk Blofeld]>+

Wavetables for the Waldorf Blofeld:
http://Synth.Stromeko.net/Downloads.html#BlofeldUserWavetables

Re: Problem: Moving rows in a table changes vsum start and end

[Torsten Wagner]

Hi Carsten,

> Thorsten, if you look at the manual, there are ways to write this limits of vsum etc in a way that they are relative to the table boundaries or to horizontal lines.  This is robust agains changes of rows.

Yes I know, thanks for pointing to it. It is just an dangerous culprit
since people might not be aware of the fact that the formular changes
along with row-moving operations.  Esp. if you use vsum or other range
based operations (with a start and end), one might expect that there
should be no change.

I can only speak for myself, however, as great as it is to use
org-tables, I always have this little paranoid fear that certain cells
did not get updated correctly, might it be due to wrong inputs from my
side or because of a hidden bug. For really critical parts, esp.
during the set-up of a new table, I find myself checking the numbers
by hand again to make sure its going to be ok.
Another problem I faced sometimes is the fact that after a while I
forgot that a certain cell is addressed by a formular. I do changes by
hand and just the next press of C-u C-c C-c might overwrite them
accidentally without my notice.

Having overlays or any sort of highlighting might be very helpful (and
as the nature of emacs, people might just decide to turn them on and
off if they get annoyed).
I was thinking of:
+ Mark all cells/numbers which depend or are part of a formular (kind
what we have already in the formular editor but for all and every
forms)
+ Mark all cells which were updated by  C-u C-c C-c (as the above but
in addition being now different compared to the previous result)
+ Mark the parts of the formulars which using the cell in which the
pointer is currently placed (reverse compared to the already existing
formular highlighting)
+ Mark all cells which have by formulars some relation to the cell in
which the pointer is currently placed (use two colors to indicate
inputs and outputs)

Those would give me a much more confidence, e.g., that all the fields
are updated correctly, that I did not overwrite by accident an
calculated value and it would help me to understand quickly the
relation of cells even months after writing down the formulars.

Greetings

Torsten