1

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 help? – Melioratus Aug 13 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 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 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 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 at 22:28
1

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.

  • No worries!!! It was interesting troubleshooting issue! – Melioratus Aug 13 at 22:40
  • 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 at 22:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.