x Want to participate? Join the group to interact
  • State Verified Answer
  • Replies 6 replies
  • Answers 1 answer
  • Subscribers 76 subscribers
  • Views 3852 views
  • Users 0 members are here
Browse Forums
Announcements
78
topics
166
replies
General Discussion
5k
topics
16k
replies
Installation and Upgrades
978
topics
2k
replies
Inventory, Projects, and Departments
337
topics
1k
replies
Payables and Receivables
1k
topics
4k
replies
Payroll
1k
topics
3k
replies
Reconciliation and Banking
315
topics
992
replies
Reporting and Analytics
206
topics
397
replies
Soutien Technique - Français
625
topics
1k
replies
Year End Activities
223
topics
595
replies
Azsarah

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

SOLVED
Posted By

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.

  • 0
    You need to go back through the Invoice Lookup information in the datadict.pdf to find the correct information if you need invoice information or the Journal Entry tables depending on which information you really want.

    However, I didn't think the invoice number paid was part of tPmtHdr because there can be many invoices per payment, so I would have thought you would need to go back through the tVenTr and tVenTrDt tables to get the invoice numbers. I don't usually use tPmtHdr so I might have missed something.
  • 0 in reply to Richard S. Ridings
    Thank you Richard,

    I used tpmthdr, tventrdt, and tventr, for generating the report. and seems everything is good except project name.
  • 0 in reply to Azsarah
    OK that makes sense. Hopefully the rest of my suggestions will get you where you want to go.

    Remember cash flow (payments) have nothing to do with Projects so you must get back to the area of the program that you used to identify the projects in the entries.
  • 0 in reply to Azsarah
    verified 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.

  • 0 in reply to RandyW
    Thank you Randy.
  • 0 in reply to RandyW
    SUGGESTED
    Thank you Randy, it's a huge help!

*Community Hub is the new name for Sage City