# BAQ (simple?) Linking GLJrnDtl to InvoiceDtl

**Category:** [Epicor ERP 10](https://www.epiusers.help/c/e10/5)
**Created:** 2017-01-10 21:34 UTC
**Views:** 4326
**Replies:** 4
**URL:** https://www.epiusers.help/t/baq-simple-linking-gljrndtl-to-invoicedtl/36854

---

## Post #1 by @Bruce_B

Problem:  I need all the detail information for everything that has been invoiced.  Example:  I need all of the Part Numbers & Descriptions that have been invoiced for chart #1101 in our chart of accounts.  

Solution:  The GLJrnDtl table has the value for every line item in the InvoiceDtl table.  Link the GLJrnDtl table to InvoiceDtl table and voila!

Nope try again:  The GLJrnDtl table has the ARInvoiceNum field that gets me halfway.  I cannot find the Invoice Line number referenced in that table anywhere.  So...

Does someone know how to link the GLJrnDtl table to the InvoiceDtl table?  Is there an intermediary table in there?  Is there a cryptically named field in the GLJrnDtl table that translates to "InvoiceLine"?  

Thanks for your help in advance!

---

## Post #2 by @Randy

I think you'll need the TranGLC table.  RelatedToFile would be InvcDtl
(invoice line) and Key1 is the Invoice Number.  IIRC Key2 is the Invoice
Line

---

## Post #3 by @Bruce_B

Excellent.  Thank you for the response.  This is exactly what I was looking for.  Now I have a full breakdown of all of our sales that ties to our financials nicely.  Much appreciated.

---

## Post #4 by @Randy

Glad I could help Bruce

---

## Post #5 by @krum

For anyone else who comes across this, including these columns in the join makes the query optimizer use an index for about a 100x performance boost:

    select *
    from erp.GLJrnDtl gl
    join erp.TranGLC tr
    	on gl.Company = tr.Company
    	and gl.FiscalCalendarID = tr.FiscalCalendarID
    	and gl.FiscalYear = tr.FiscalYear
    	and gl.JournalCode = tr.JournalCode
    	and gl.JournalNum = tr.JournalNum
    	and gl.JournalLine = tr.JournalLine

---


**Canonical:** https://www.epiusers.help/t/baq-simple-linking-gljrndtl-to-invoicedtl/36854
**Original content:** https://www.epiusers.help/t/baq-simple-linking-gljrndtl-to-invoicedtl/36854