0

How do I link 2 tables together ?

Jet Reports Historic Posts

February 17, 2011 05:27

None

Follow

Example:

Vendor name under Vendor table and Vendor Code under Vendor entry table. The queries should have the results as follow : Vendor code, Vendor Name , Amount.

Please help and thanks in advance

• Facebook
• Twitter
• LinkedIn

6 comments

• 0
  
  Jet Reports Historic Posts February 20, 2011 20:47

  Start by creating a separate row for each Vendor code in your Vender Ledger Entry Table -
  =NL("Rows","Vendor Ledger Entry","Vendor No.") (I placed this in G5)
  
  Then in G6, create a query to the Vendor table to call up the vendor name that matches the code that was returned in cell G5-
  =NL("First","Vendor","Name","No.",G5)
  
  Then in cell G7, create a query to the Vendor Ledger entry table to sum up the amounts that match the code that was returned in cell G5-
  =NL("Sum","Vendor Ledger Entry","Amount (LCY)","Vendor No.",G5)
  
  Hope this helps.

  • Vendor no name and amount.xlsx
• 0
  
  Jet Reports Historic Posts February 21, 2011 00:51

  Thanks SJL4782 , I really appreciate your effort as i give up after spending 1 day.

• 0
  
  Jet Reports Historic Posts February 21, 2011 01:01

  Hi
  
  1 more question. If i want to filter by "Posting Date", where should i include in the equation?

• 0
  
  Jet Reports Historic Posts February 21, 2011 19:12

  Hi!
  
  If you put the posting date filter in cell C2 then you would want to reference it from both the Rows and Sum formulas like this:
  
  =NL("Rows","Vendor Ledger Entry","Vendor No.","Posting Date",$C$2)
  =NL("Sum","Vendor Ledger Entry","Amount (LCY)","Vendor No.",G5,"Posting Date",$C$2)
  
  Does that make sense?
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts March 04, 2011 06:25

  Hi!
  
  If you put the posting date filter in cell C2 then you would want to reference it from both the Rows and Sum formulas like this:
  
  =NL("Rows","Vendor Ledger Entry","Vendor No.","Posting Date",$C$2)
  =NL("Sum","Vendor Ledger Entry","Amount (LCY)","Vendor No.",G5,"Posting Date",$C$2)
  
  Does that make sense?
  
  Regards,
  Hughes

  
  
  Yes , thanks Hughes. Now i wanna to further break down the amount into different " type" from a Purch. Inv. Line table. The end result should look like the attachment . Can we do that ?

  • Book11.xlsx
• 0
  
  Jet Reports Historic Posts March 04, 2011 12:01

  Hello, my problem is following:
  
  I want to get informations from 3 tables(Job Task, Step, Step ledger entry), but i want to filter everything by table Step ledger entry–>Posting Date. Can you write me a formula to get it,
  
  I wrote 3 NL functions, then i got (wrong) results by NF functions.
  NL functions are:
  -for Step ledger entry: =NL("Rows";"Step Ledger Entry";{"Posting Date"\"Description"\"No. Hours"};"+Posting Date";"3.1.2008..18.1.2008")
  -for Job Task: =NL("Vrstice";"Job Task";{"Actual Hours"\"Chargeable Hours"\"Effective Hours"\"Planned Hours"\"Quote Hours"};"Link=";"Step Ledger Entry";"Job Task No.";"=Job Task No.";"+Posting Date";"3.1.2008..18.1.2008")
  -for Step: =NL("Vrstice";"Step";{"Code"\"Due Date"\"Job No."\"Job Task No."\"Priority"\"Resource Name"\"Status"};"Link=";"Step Ledger Entry";"Step Code";"=Code";"+Posting Date";"3.1.2008..18.1.2008")
  
  
  Thank you very much