0

Adding a link(connection) in JET-NAV

Jet Reports Historic Posts

July 11, 2012 08:44

None

Follow

Hello people!

I would like to ask for an assistance on how to add another table to an existing report made by report builder? The scenario is I have this detailed AR, my main table is the Cust. Ledger Entry Table and I have to connect the VAT entry table and get the "Amount" field.

I have this in the first cell:=NL("Rows","Cust. Ledger Entry",,"Filters=",$K$8:$L$10,"Customer No.",D17,"Sell-to Customer No.",C17)———-F17
In the second cell: =NL("Link","VAT Entry",,"Document No.","=Document No.",F17) —————G17

and I have to make it appear in the column for VAT. And I have this : =NF(G17,"Amount")

But is shows the error : #VALUE!

Help for the connection please. Attached file is the excel that I'm working with. Thanks

• Ar_071012_v3.xlsx

• Facebook
• Twitter
• LinkedIn

7 comments

• 
  Jet Reports Historic Posts July 11, 2012 13:26 Official comment

  Hi,
  
  You can't use NF formulas directly with NL(Link) formulas. Notice in your report all the NF formulas created by the Report Builder reference the NL(Rows) formulas, not the NL(Link) formulas. If you only want one field from the VAT Entry, you could just use an NL(First) formula to get it like this:
  
  =NL("First","VAT Entry","Amount","Document No.",O17)
  I'm not sure what fields connect the VAT Entry to the Cust. Ledger Entry since I don't have this table, but it looked like from your formulas that it's Document No. so that is what my formula uses. If that's not right, you'll have to figure out what the linking field(s) are between those 2 tables. Does this help?
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts July 13, 2012 03:44

  What is the use of FIRST?

• 0
  
  Jet Reports Historic Posts July 13, 2012 15:21

  Hi,
  
  NL(First) or NL(1) or an NL with a blank What value all do the same thing. They return the first record or value that matches the specified filters. Does that make sense?
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts July 14, 2012 02:29

  Thank you… I just revised my report. Now I have a follow up question, the report attached is grouped by Responsibility center(branch) and customer name. I'd like to make a sum for each reponsibility center grouped. But when I copy the totalling from the other cell., the sum is placed in the bottom of the report.
  
  How could I group it for responsibility center only and all the customers are sorted alphabetical?
  How can I create a subtotal for each responsibility center grouped?

• 0
  
  Jet Reports Historic Posts July 16, 2012 15:06

  Hi,
  
  As far as the subtotal goes, it has to do with your NL(Rows=x) formula. The x stands for how many rows will be copied down when the values are replicated. Therefore if your subtotal or sum formula is below the copied rows then it will just appear below everything at the end of the report. You may need to either move the subtotal function up a row or else increase the number of rows being copied.
  
  You sort in Jet in the NL(Rows) formula by putting a + or - in front of the filter field. So the formula might be something like this:
  
  =NL("Rows","Customer",,"+Name","*")
  I think you forgot to attach the report, so I can't be super specific here.
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts July 17, 2012 00:50

  Hello.,
  
  Attached file is the sample report.. I tried it but the results return to invalid filter and '#Value!'

  • revised ar_d_c_v2.xlsx
• 0
  
  Jet Reports Historic Posts July 17, 2012 01:10

  I got the sorting.. Now my only concern is how to grouped it in one responsibility center only. As what shows in my report, it displays the the responsibility centers but it devides by customer. please see attached file.

  • revised ar_d_c_v2.xlsx