Relating AP tables to GL tables in Shelby v.5 data

I've been working on a query to correlate General Ledger detail lines to the corresponding Accounts Payable invoice detail lines. This sounds pretty straightforward; however, it is not. The only foreign key relationship available is at the top level: AP invoice header to GL entry header. This means that tying the next level down (detail-to-detail) involves matching up multiple detail values in addition to the header foreign key value. Unfortunately, because the customer I am working on this for has data entry practices that allow for multiple identical detail lines even on the same invoice, I have no truly unique key to work with. If I resolve this, I will post the finished join relationship.

On a related note, however, I have run across an important detail regarding the use of the GLEntriesCounter column, which appears in both the APInv table and the APInvDetail table. I have learned that APInv.GLEntriesCounter is a reference to the GLEntries.HeaderCounter for the rows created when the invoice is posted. APInvDetail.GLEntriesCounter is a reference to the GLEntries.HeaderCounter for the rows created when the invoice is paid. For reporting on accural-based companies, this distinction is critical.

If the invoice has not been paid, the APInvDetail.GLEntriesCounter value will be zero (0). If a company is on a cash basis, I would assume that the APInv.GLEntriesCounter value would be zero, though I have not tested this assumption. (If someone who is on a cash AP basis would check this out and post a confirmation or correction, I'll edit this post to give the definitive answer.)