Why does all but the first `org-lookup-first` returns nil

Question

I have an org file in which I lookup for some values in different tables. The problem is that in two of them, only the first row gets its value, while in the rest of the rows the value returned is nil.

        #+tblname: L1A
    |   |    | Last Name | First Name | Alias | Sex |
    | ! | no | ln        | fn         | alias | sex |
    |---+----+-----------+------------+-------+-----|
    | # |  1 | Doe       | Eduard     | Ed    |     |
    | # |  2 | Foo       | Samuel     | Sam   |   0 |
    | # |  3 | Bar       | Alexia     | Ale   |   1 |
    | # |  4 | Lol       | Nadya      | Nadya |   1 |
    |---+----+-----------+------------+-------+-----|
    | * |    |           |            | Men   |   2 |
    | * |    |           |            | Women |   2 |
    | * |    |           |            | Total |   4 |
    #+TBLFM: $2=@#-2 :: @>>>$2=string("") :: @>>$2=string("") :: @>$2=string("")
    #+TBLFM: @>>>$4=string("") :: @>>$4=string("") :: @>$4=string("")
    #+TBLFM: @>>>$5=string("Men") :: @>>$5=string("Women") :: @>$5=string("Total")
    #+TBLFM: @>>>$6=vcount(map(<if(eq(#1,0),1,[])>, @I..@II)) :: @>>$6=vcount(map(<if(eq(#1,1),1,[])>, @I..@II)) :: @>$6=vcount(@I..@II)

    #+tblname: E1Ap1
    |   | No. | Name  | Total | Exm. | Prd. 1 | Prd. 2 | Prd. 3 | Prd. 4 | Prd. 5 | Prd. 6 | Prd. F |
    | ! |  no | almn  |   tot |   ex |     P1 |     P2 |     P3 |     P4 |     P5 |     P6 |     PF |
    |---+-----+-------+-------+------+--------+--------+--------+--------+--------+--------+--------|
    | # |   1 | Ed    |     8 |   10 |      5 |     10 |        |      5 |     10 |      5 |     10 |
    | # |   2 | Sam   |     7 |    7 |      8 |      8 |      5 |      4 |      9 |      5 |      5 |
    | # |   3 | Ale   |     8 |    8 |      9 |      7 |      6 |      9 |      8 |      7 |      6 |
    | # |   4 | Nadya |    10 |    9 |     10 |     10 |     10 |     10 |     10 |     10 |     10 |
    |---+-----+-------+-------+------+--------+--------+--------+--------+--------+--------+--------|
    |   |     |       |       |      |        |        |        |        |        |        |        |
    #+TBLFM: $2=@#-2 :: $4=round((vsum($P1..$PF)+1.0-1.0+($ex*3))/(vcount($P1..$PF)+3.0))
    #+TBLFM: $3='(org-lookup-first $no '(remote(L1A, @I$no..@II$no)) '(remote(L1A, @I$alias..@II$alias))) :: 

    #+tblname: E1Ap2
    |   | No. | Nombre | Total | Exm. | Prd. 1 | Prd. 2 | Prd. 3 | Prd. 4 | Prd. 5 | Prd. 6 | Prd. F |
    | ! |  no | almn   |   tot |   ex |     P1 |     P2 |     P3 |     P4 |     P5 |     P6 |     PF |
    |---+-----+--------+-------+------+--------+--------+--------+--------+--------+--------+--------|
    | # |   1 | Ed     |     7 |    5 |      6 |      7 |      8 |      9 |     10 |      5 |      6 |
    | # |   2 | Sam    |    10 |   10 |     10 |     10 |     10 |     10 |     10 |     10 |     10 |
    | # |   3 | Ale    |     7 |    8 |      7 |      6 |      5 |      8 |      7 |      6 |      5 |
    | # |   4 | Nadya  |     9 |    9 |      9 |      9 |      9 |      9 |      9 |      9 |     10 |
    |---+-----+--------+-------+------+--------+--------+--------+--------+--------+--------+--------|
    |   |     |        |       |      |        |        |        |        |        |        |        |
    #+TBLFM: $2=@#-2 :: $4=round((vsum($P1..$PF)+1.0-1.0+($ex*3))/(vcount($P1..$PF)+3.0))
    #+TBLFM: $3='(org-lookup-first $no '(remote(L1A, @I$no..@II$no)) '(remote(L1A, @I$alias..@II$alias))) :: 

    #+tblname: E1A1
    |   | No. | Name | Fails | Pr1 | Pr2 | Pr3 | Pr4 | Pr5 | Ex  | AP  | PE | SUM | AVERAGE | GRADE | LEVEL | RISK |
    | ! | no  | al   |    in | p1  | p2  | p3  | p4  | p5  | ex  | ap  | pe | sum |    prom |   cal |   lvl | R    |
    | / | <   | >    |    <> | <   |     |     |     | >   | <   | >   |    |   < |       > |     < |     > |      |
    |---+-----+------+-------+-----+-----+-----+-----+-----+-----+-----+----+-----+---------+-------+-------+------|
    | # | 1   | Ed   |     1 | 8   | 7   |     |     |     |     |     |    |   8 |     1.1 |     5 |     1 | !!   |
    | # | 2   | Sam  |     2 | nil | nil |     |     |     |     |     |    |   0 |       0 |     5 |     1 | !!   |
    |---+-----+------+-------+-----+-----+-----+-----+-----+-----+-----+----+-----+---------+-------+-------+------|
    |   |     |      |     3 | 8   | 0   | 0   | 0   | 0   | 0   | 0   |    |   8 |     1.1 |    10 |     2 |      |
    |   |     |      |   1.5 | 3   | 0/0 | 0/0 | 0/0 | 0/0 | 0/0 | 0/0 |    |   4 |     0.6 |  12.5 |   2.5 |      |
    #+TBLFM: $3='(org-lookup-first $no '(remote(L1A, @I$no..@II$no)) '(remote(L1A, @I$alias..@II$alias))) :: 
    #+TBLFM: $5='(org-lookup-first $no '(remote(E1Ap1,@I$no@II$no)) '(remote(E1Ap1, @I$tot..@II$tot))) :: $6='(org-lookup-first $no '(remote(E1Ap2,@I$no@II$no)) '(remote(E1Ap2, @I$tot..@II$tot)))


    #+TBLNAME: E1A
    |   | No. | I1  | I2 | I3 | IT  | P1  | R1 | P2 | R2 | P3 | R3 | SUM | GRADE | LEVEL | RISK |
    | / |  <> | <   |    |    | >   | <   |  > |  < | >  |  < | >  |   < |       |     > |      |
    |---+-----+-----+----+----+-----+-----+----+----+----+----+----+-----+-------+-------+------|
    | # |   1 | 1   |    |    | 1   | 5   |  6 |  7 |    |  6 |    |  19 |   6.3 |     2 |      |
    | # |   2 | nil |    |    | nil | 5   |    |  7 |    |  6 |    |  18 |   6.0 |     2 |      |
    | # |   3 | nil |    |    | nil | nil |  6 |  7 |    |  6 |    |  19 |   6.3 |     2 |      |
    | # |   4 | nil |    |    | nil | nil |    |  7 |    |  5 |    |  17 |   5.7 |     1 | 6.15 |
    |---+-----+-----+----+----+-----+-----+----+----+----+----+----+-----+-------+-------+------|
    |   |     |     |    |    |     |     |    |    |    |    |    |     |       |       |      |
    |   |     |     |    |    |     |     |    |    |    |    |    |     |       |       |      |
    #+tblfm: $3='(org-lookup-first $2 '(remote(E1A1, @I$no..@II$no)) '(remote(E1A1, @I$in@II$in))) :: $7='(org-lookup-first $2 '(remote(E1A1, @I$no..@II$no)) '(remote(E1A1, @I$cal..@II$cal)))

When I execute the formulas for lookup in tables E1A1 and E1A, the first row gets the correct value, but the others get nil.

Here is the debug output for the first nil:

Substitution history of formula
Orig:   '(org-lookup-first $no '(remote(E1Ap1,@I$no@II$no)) '(remote(E1Ap1, @I$tot..@II$tot)))
$xyz->  '(org-lookup-first $2 '(remote(E1Ap1,@I$2@II$2)) '(remote(E1Ap1, @I$tot..@II$tot)))
@r$c->  '(org-lookup-first $2 '(#("1" 0 1 (fontified t face org-table))) '(#("8" 0 1 (fontified t face org-table)) #("7" 0 1 (fontified t face org-table)) #("8" 0 1 (fontified t face org-table)) #("10" 0 2 (fontified t face org-table))))
$1->    '(org-lookup-first "2" '(#("1" 0 1 (fontified t face org-table))) '(#("8" 0 1 (fontified t face org-table)) #("7" 0 1 (fontified t face org-table)) #("8" 0 1 (fontified t face org-table)) #("10" 0 2 (fontified t face org-table))))
Result: nil
Format: NONE
Final:  nil

When I try with simpler examples (even with the same number of tables and references), all goes wright.

What I am doing wrong? How can I fix this?

I'm using emacs 26.1 with org-mode 9.2.1. on Debian GNU/Linux 10 (buster).

At first glance, I think this might be caused by known column name issue. Does this [workaround](https://emacs.stackexchange.com/a/43623/388) help? — Melioratus, Aug 13 '19 at 20:09
@Melioratus, the issue with my question is about importing values from one table to another, so I don't understand how your proposal is helpful, but I will try to use column numbers instead of column names. Thank you. — aBarocio80, Aug 13 '19 at 22:03
Thanks! My guess is you'll see the same results. If that doesn't help, try adding `;L` to end of formulas like this `#+TBLFM: @6$2='(url-file-nondirectory (format "%s" (org-lookup-first "URL" '(@I$1..@II$1) '(@I$2..@II$2) 'string= )));L`. Using `;L` helps sometimes when debugging formulas with elisp. — Melioratus, Aug 13 '19 at 22:07
@Melioratus, I have just published the solution. It was my fault, but I would not get to it so quick if you wouldn't have taken the time to read my question. Tank you, very much. — aBarocio80, Aug 13 '19 at 22:15
I just added `;L` and the debug messages make a bit more sense, `$1-> '(org-lookup-first 1 '(#) '(8 7 8 10))`. I think this is your issue `(remote(E1Ap1,@I$no@II$2))`. Notice the `@I$no` instead of expected `@I$2`. — Melioratus, Aug 13 '19 at 22:28
My pleasure!! Thank you for asking your question! I had fun troubleshooting and I hope you did too! — Melioratus, Aug 13 '19 at 22:49

aBarocio80 · Accepted Answer · 2019-08-14T03:45:29.663

OK. My fault.

When I was revising the proposal of @Melioratus, I noticed that in some formulas I was not including the two dots to indicate range, specifically in the calculation of the fields that yield =nil=.

Here are the original formulas:

    #+TBLFM: $5='(org-lookup-first $no '(remote(E1Ap1,@I$no@II$no)) '(remote(E1Ap1, @I$tot..@II$tot))) :: $6='(org-lookup-first $no '(remote(E1Ap2,@I$no@II$no)) '(remote(E1Ap2, @I$tot..@II$tot)))

Specifically in the range part:

remote(E1Ap1,@I$no@II$no) . . . remote(E1Ap1, @I$tot..@II$tot)
     Compare this ^         > > >           to this ^

So, my fault.

In the formula, a range is necessary for the org-lookup-first function. Those are defined using two cells on a given table with the following syntax: <cell1>..<cell2>, with the two dots in between. (By the way, a cell is defined as @row$column.)

But emacs has a feature that allows you to give a name to the columns and use it in the formula calculations. So, when @Melioratus referenced me to a question related to named columns... well, I decided to go back to basics and reference the columns by their number.

It was in the process of changing from names to numbers, that I realize that there was no dots between the cells in some ranges, specifically in the formulas that yielded me nil. So, I corrected the ranges with their two dots, and all went good.

I recommend updating your answer to explain why formula was broken along with the steps you used to discover and resolve the issue. That way other users will get the most benefit from your suffering ;) — Melioratus, Aug 13 '19 at 22:44

Why does all but the first `org-lookup-first` returns nil

1 Answers1