Which tables and fields show the relationship between payments holder and Projects

Question

Hello everyone, 
 I'm going to create a report using Crystal report that shows payment info for each purchase invoice including cheque#, payment date, invoice#, Project name I know that Payment info stored in tPmtHdr and was able to almost create the report, but the only thing I couldn't figure it out is how to include project name in the report :( 
 Does anybody know how to create relationship between tPmtHdr and tProject? 
 Thank you in advance.

RandyW · Accepted Answer

Hi Azsarah,

Project name is in tProject, the ID is stored in tItLu.lProjID if the allocation is not by line (titlu.bAlocToAll=1) titlu.litrecID can be found from tItRec, which will have the vendor ID, the journal type for purchases (titrec.nJournal=7), and the invoice number (titrec.sSource1) and vendor ID (titrec.lVenCusID) and the date (titrec.dtJournal)

Since invoice number + vendor ID is a 'key' field in tVenTR / tVenTRDT, looking up from just those two fields should be fairly safe, if you specify that the journal type in titrec is AP, since it's not impossible to have a customer with a matching ID and invoice number.

As far as I'm aware, there is no direct, solid relation between the invoice (titrec/titlu) and Accounts Payable (tVentr / tVentrDT) tables. The invoice tables reference G/L entries, but the AP tables don't. There are serial transaction IDs in both, but there's no connection other than they're created in order.

I hope that helps, please post back.

Azsarah · Answer

Thank you Randy, it's a huge help!

Sage 50 Canada

Which tables and fields show the relationship between payments holder and Projects

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING