Re: Table column formula with remote reference

[Fraga, Eric]

On Sunday, 17 Mar 2024 at 10:29, Wu Ming wrote:
> Haven’t tried Org export options yet. What is your organization system
> with tables?

I don't have a system!  Often, tables in my documents are the output of
some other code and the tables are the results that need further
processing (or visualising).  Most of my tables are standalone.  As I
think I mentioned earlier, I have seldom (but not never) made use of
remote references.

I do however often have multiple tables as inputs to the post-processing
or visualisation codes.

-- 
: Eric S Fraga, with org release_9.6.19-1215-g67d937 in Emacs 30.0.50

Re: Table column formula with remote reference

[Ihor Radchenko]

Wu Ming  writes:

> Very clear now. Thank you. But I was mostly confounded by references
> $0 and #0 versus the @@# (and $$#) you just described the processing
> of. Don’t want to abuse your time. I can figure it out when needed.
> But if you feel inclined to unravel this little detail of the manual
> as well I would clearly appreciate the effort.

The main difference is that @# always refer to the original table, while
$0 may refer to other tables as well.

(Generally, reference expansion process is not well documented,
unfortunately; it would be nice if somebody wrote a documentation
explaining the process - things can get tricky in some edge cases)

>> Normally, if you use org-table-* commands, the formulas get updated when
>> you move the columns.
>
> One side effect of using remote formulas is re-organizing columns doesn’t 
> update them automatically. I should find the balance of readability and 
> formulas maintenance cost. But you may have suggested the solution below 
> already with named columns.

In theory, we might try to update such remote references at least in
current buffer. Contributions welcome.

>> To make things more readable, you can also assign names to columns:
>> 
>> | ! | | P1 | P2 | P3 |   Tot |  |
>> |   | Maximum | 10 | 15 | 25 |50 | 10.0 |
>> 
>> Then, you can write $P1 = ... instead of $3 = ...
>> See "3.5.10 Advanced features" section of the manual.
>
> Clever. And we are at the “Advanced“ features already. Are advanced-advanced 
> in the realm of Calc? 

> Asking because was also wondering how to optimize parameters (“solver”) and 
> deal with locales (“,” vs “.” separators). For the latter I could possibly 
> ‘tr’ them before sharing the output. But will possibly mess the alignment. 
> Happened while trialling groff’s tbl.

AFAIK, GNU calc does not support comma as decimal point as _input_. For
output, I am not sure.

-- 
Ihor Radchenko // yantar92,
Org mode contributor,
Learn more about Org mode at .
Support Org development at ,
or support my work at 

Re: Table column formula with remote reference

[Wu Ming]

> On 15 Mar 2024, at 2:58 AM, Ihor Radchenko  wrote:
> 
> Wu Ming  writes:
> 
>>> See "Remote references" subsection. It explains that in
>>> remote(NAME,REF), REF is inside the remote table. Relative and current
>>> column/row is ambiguous there.
>>> 
>>> In contrast, @# and $# are special - they are replaced before
>>> remote(...) is processed.
>> ...
>> I have some trouble at understanding your answer. Do you mean @# refers a 
>> row on the table where the formula belongs and @0 refers a row on the remote 
>> table? Was tempted to describe the former as “current” but remote table is 
>> also current when accessed. A better noun may be needed.
> 
> Let me elaborate.
> 
> When Org mode sees something like
> 
> #+TBLFML: $1 = $2 + remote(A,@@#$1) 
> 
> 1. it goes to every cell in column 1 and remembers current column and
>   row numbers (original cell)
> 
> 2. In the right side of the formula $2 + remote(A,@@#$1), Org replaces
>   all the instances of @# and $# with current column and row.
>   So, when we are calculating the value for @1$1, we get
>   $2 + remote(A,@1$1)
> 
> 3. Org moves to table A and replaces remote(A,@1$1) with cell contents
>   of @1$1 inside table A. At this point, it is not allowed to have
>   relative references like $1 or $-1, because "current" column and row
>   are set inside remote table A - the original cell coordinates are not
>   available.
> 
> 4. Org goes back to the original table, takes the updated formula
>   $2 + , and replaces relative reference $2
>   according to the current column - with the value stored in @1$2
>   column
> 
> 5. Org passes the resulting expression  +value A@1$1> to GNU cal and assigns the result as the value of the
>   current cell @1$1.
> 
> 6. Repeat for @2..$1 cells.
> 
> As you can see, @# and $# substitution always uses local cell
> coordinates. Any other relative reference is not allowed inside
> remote(...).
> 

Very clear now. Thank you. But I was mostly confounded by references $0 and #0 
versus the @@# (and $$#) you just described the processing of. Don’t want to 
abuse your time. I can figure it out when needed. But if you feel inclined to 
unravel this little detail of the manual as well I would clearly appreciate the 
effort. 

>> This made me worry about reliability of simple biz calculations I am trying 
>> on Org spreadsheet for the first time. Please advise.
> 
> Formula debugger is really helpful to understand the process.
> 
>> Finally I moved columns but now column numbers in formulas don’t relate to 
>> column order on display. How to understand which column formula affect which 
>> column?
> 
> Normally, if you use org-table-* commands, the formulas get updated when
> you move the columns.

One side effect of using remote formulas is re-organizing columns doesn’t 
update them automatically. I should find the balance of readability and 
formulas maintenance cost. But you may have suggested the solution below 
already with named columns.
> 
> To make things more readable, you can also assign names to columns:
> 
> | ! | | P1 | P2 | P3 |   Tot |  |
> |   | Maximum | 10 | 15 | 25 |50 | 10.0 |
> 
> Then, you can write $P1 = ... instead of $3 = ...
> See "3.5.10 Advanced features" section of the manual.

Clever. And we are at the “Advanced“ features already. Are advanced-advanced in 
the realm of Calc? 

Asking because was also wondering how to optimize parameters (“solver”) and 
deal with locales (“,” vs “.” separators). For the latter I could possibly ‘tr’ 
them before sharing the output. But will possibly mess the alignment. Happened 
while trialling groff’s tbl.

Re: Table column formula with remote reference

[Wu Ming]

> On 14 Mar 2024, at 9:40 PM, Fraga, Eric  wrote:
> 
> On Thursday, 14 Mar 2024 at 09:16, Wu Ming wrote:
>> Unrelated, but appeared on the same trial, noticed a cell was
>> mis-calculated. [...] This made me worry about reliability of simple
>> biz calculations I am trying on Org spreadsheet for the first
>> time. Please advise.
> 
> I've not seen any problems with spreadsheet/table calculations in org and use 
> it extensively.  I don't use remote access generally however.
> 
> In any case, one very nice feature of org tables is you can see exactly how 
> and what it calculates when you ask it to.  Turn on debugging by "C-c {" 
> (org-table-toggle-formula-debugger) and you can see all the information you 
> should need to identify what, if anything, is going wrong.
> 
> Turn off debugging with the same key sequence.

Thanks for the reference to formula debugger. In the heat of debugging an error 
as obvious, and worrying, as the one I saw forgot about it. Though I am still 
new to Emacs and Org so that’s not so surprising. 

I have one table retrieving data from two more. 18 columns x 7 rows total. I 
could have everything into one larger table but splitting makes them more 
readable I think. And possibly simplifies sharing end results. Haven’t tried 
Org export options yet. What is your organization system with tables?

Re: Table column formula with remote reference

[Ihor Radchenko]

Wu Ming  writes:

>> See "Remote references" subsection. It explains that in
>> remote(NAME,REF), REF is inside the remote table. Relative and current
>> column/row is ambiguous there.
>> 
>> In contrast, @# and $# are special - they are replaced before
>> remote(...) is processed.
> ...
> I have some trouble at understanding your answer. Do you mean @# refers a row 
> on the table where the formula belongs and @0 refers a row on the remote 
> table? Was tempted to describe the former as “current” but remote table is 
> also current when accessed. A better noun may be needed.

Let me elaborate.

When Org mode sees something like

#+TBLFML: $1 = $2 + remote(A,@@#$1) 

1. it goes to every cell in column 1 and remembers current column and
   row numbers (original cell)

2. In the right side of the formula $2 + remote(A,@@#$1), Org replaces
   all the instances of @# and $# with current column and row.
   So, when we are calculating the value for @1$1, we get
   $2 + remote(A,@1$1)

3. Org moves to table A and replaces remote(A,@1$1) with cell contents
   of @1$1 inside table A. At this point, it is not allowed to have
   relative references like $1 or $-1, because "current" column and row
   are set inside remote table A - the original cell coordinates are not
   available.

4. Org goes back to the original table, takes the updated formula
   $2 + , and replaces relative reference $2
   according to the current column - with the value stored in @1$2
   column

5. Org passes the resulting expression  +  to GNU cal and assigns the result as the value of the
   current cell @1$1.

6. Repeat for @2..$1 cells.

As you can see, @# and $# substitution always uses local cell
coordinates. Any other relative reference is not allowed inside
remote(...).

> This made me worry about reliability of simple biz calculations I am trying 
> on Org spreadsheet for the first time. Please advise.

Formula debugger is really helpful to understand the process.

> Finally I moved columns but now column numbers in formulas don’t relate to 
> column order on display. How to understand which column formula affect which 
> column?

Normally, if you use org-table-* commands, the formulas get updated when
you move the columns.

To make things more readable, you can also assign names to columns:

 | ! | | P1 | P2 | P3 |   Tot |  |
 |   | Maximum | 10 | 15 | 25 |50 | 10.0 |

Then, you can write $P1 = ... instead of $3 = ...
See "3.5.10 Advanced features" section of the manual.

-- 
Ihor Radchenko // yantar92,
Org mode contributor,
Learn more about Org mode at .
Support Org development at ,
or support my work at 

Re: Table column formula with remote reference

[Fraga, Eric]

On Thursday, 14 Mar 2024 at 09:16, Wu Ming wrote:
> Unrelated, but appeared on the same trial, noticed a cell was
> mis-calculated. [...] This made me worry about reliability of simple
> biz calculations I am trying on Org spreadsheet for the first
> time. Please advise.

I've not seen any problems with spreadsheet/table calculations in org and use 
it extensively.  I don't use remote access generally however.

In any case, one very nice feature of org tables is you can see exactly how and 
what it calculates when you ask it to.  Turn on debugging by "C-c {" 
(org-table-toggle-formula-debugger) and you can see all the information you 
should need to identify what, if anything, is going wrong.

Turn off debugging with the same key sequence.

-- 
: Eric S Fraga, with org release_9.6.19-1215-g67d937 in Emacs 30.0.50

Re: Table column formula with remote reference

[Wu Ming]

> On 13 Mar 2024, at 20:16, Ihor Radchenko  wrote:
> 
> Wu Ming  writes:
> 
>> My fault. Missed the line "One of the very first actions during evaluation 
>> of Calc formulas and Lisp formulas is to substitute ‘@#’ and ‘$#’ in the 
>> formula with the row or column number of the field where the current result 
>> will go to.“  So '@@#' becomes '@'. 
>> 
>> Overlooked it also because I did read the other line "‘@0’ and ‘$0’ refer to 
>> the current row and column, respectively, i.e., to the row/column for the 
>> field being computed.” and did try '@0$1'. Why is this different from the 
>> above?
> 
> See "Remote references" subsection. It explains that in
> remote(NAME,REF), REF is inside the remote table. Relative and current
> column/row is ambiguous there.
> 
> In contrast, @# and $# are special - they are replaced before
> remote(...) is processed.
> 
> I agree that the manual is somewhat confusing. Possibly, we may even
> change `org-table-get-remote-range' to use relative references according
> to the original table. Improvements welcome!
> 

I have some trouble at understanding your answer. Do you mean @# refers a row 
on the table where the formula belongs and @0 refers a row on the remote table? 
Was tempted to describe the former as “current” but remote table is also 
current when accessed. A better noun may be needed.


Unrelated, but appeared on the same trial, noticed a cell was mis-calculated. 
Could not pin-point the reason before error disappeared after running a side 
formula performing the same operation. Formula was simply copying column values 
from a remote table. Remote table values were never changed. Recalculate all 
and C-c C-c were performed multiple times before the curious fix.

This made me worry about reliability of simple biz calculations I am trying on 
Org spreadsheet for the first time. Please advise.


Finally I moved columns but now column numbers in formulas don’t relate to 
column order on display. How to understand which column formula affect which 
column?


Sorry to coalesce multiple questions in to one. They just came to me while 
typing.

Re: Table column formula with remote reference

[Ihor Radchenko]

Wu Ming  writes:

> My fault. Missed the line "One of the very first actions during evaluation of 
> Calc formulas and Lisp formulas is to substitute ‘@#’ and ‘$#’ in the formula 
> with the row or column number of the field where the current result will go 
> to.“  So '@@#' becomes '@'. 
>
> Overlooked it also because I did read the other line "‘@0’ and ‘$0’ refer to 
> the current row and column, respectively, i.e., to the row/column for the 
> field being computed.” and did try '@0$1'. Why is this different from the 
> above?

See "Remote references" subsection. It explains that in
remote(NAME,REF), REF is inside the remote table. Relative and current
column/row is ambiguous there.

In contrast, @# and $# are special - they are replaced before
remote(...) is processed.

I agree that the manual is somewhat confusing. Possibly, we may even
change `org-table-get-remote-range' to use relative references according
to the original table. Improvements welcome!

-- 
Ihor Radchenko // yantar92,
Org mode contributor,
Learn more about Org mode at .
Support Org development at ,
or support my work at 

Re: Table column formula with remote reference

[Wu Ming]

> On 12 Mar 2024, at 22:46, Ihor Radchenko  wrote:
> 
> Wu Ming  writes:
> 
>> Problem solved with
>> 
>> $1 = remote(A,@@#$1)
>> 
>> as described on the manual page 
>> https://orgmode.org/manual/References.html#Field-coordinates-in-formulas-1 
>> 
>> 
>> Where is the coordinate @@# documented?
> 
> I am confused. Isn't it documented in the linked section of the manual?
> 
> 
> -- 
> Ihor Radchenko // yantar92,
> Org mode contributor,
> Learn more about Org mode at .
> Support Org development at ,
> or support my work at 

My fault. Missed the line "One of the very first actions during evaluation of 
Calc formulas and Lisp formulas is to substitute ‘@#’ and ‘$#’ in the formula 
with the row or column number of the field where the current result will go 
to.“  So '@@#' becomes '@'. 

Overlooked it also because I did read the other line "‘@0’ and ‘$0’ refer to 
the current row and column, respectively, i.e., to the row/column for the field 
being computed.” and did try '@0$1'. Why is this different from the above?

Re: Table column formula with remote reference

[Ihor Radchenko]

Wu Ming  writes:

> Problem solved with
>
> $1 = remote(A,@@#$1)
>
> as described on the manual page 
> https://orgmode.org/manual/References.html#Field-coordinates-in-formulas-1 
> 
>
> Where is the coordinate @@# documented?

I am confused. Isn't it documented in the linked section of the manual?


-- 
Ihor Radchenko // yantar92,
Org mode contributor,
Learn more about Org mode at .
Support Org development at ,
or support my work at 

Re: Table column formula with remote reference

[Wu Ming]

Problem solved with

$1 = remote(A,@@#$1)

as described on the manual page 
https://orgmode.org/manual/References.html#Field-coordinates-in-formulas-1 


Where is the coordinate @@# documented?


> On 11 Mar 2024, at 18:52, Wu Ming  wrote:
> 
> Hello,
> 
> #+NAME: A
> Table with header row here
> 
> #+NAME: B
> Table with header row here
> #+TBLFML: $1 = remote(A,$1)
> 
> Does nothing. 
> 
> $1 = remote(A,@0) referring current row w implicit column does not work. 
> Debug shows on row 2 of current table it returns the value of row 1, the 
> header, on table A. 
> 
> Same result as above with @0$1 instead. 
> 
> $1 = remote(A,@2$1) does what expected. For every row copies the same value 
> of @2$1 from table A to table B. 
> 
> Need to refer multiple columns on different tables in a column formula for a 
> final table. 
> 
> Please advise. Thanks. 
> 
>