Custom Aged Vendor by Project Report

Hello,

I have created a custom report to show aged vendor payables by project.  Everything is working well, except that it is pulling all of the vendor invoices, not just the unpaid ones. 

I am not completely familiar with the database table and field structure and after searching through the DataDict.pdf that I have, I am unable to find the "paid" flag field.  I am assuming there is one that I can use to filter my results and remove the paid invoices.  I am currently working with the titrec, titluip, titlu and tproject tables, so may need to reference another table or use something a bit different.

Any help or direction at all would be greatly helpful.

Thanks

Parents
  • 0
    I am unable to find the "paid" flag field.

    That's because there isn't one. 

    A left join from tVenTr.ID to tVenTrDT.lVenTrID should be sum-able by tVenTRDT.dAmount, and grouped by tVenTrID entry will get you a sum due by Vendor Invoice.  

    Or, if you assume that all invoices have either no payments or no outstanding balance, query on that for unpaid invoices. (tVenTrDT.nTranType is zero for an invoice, 1 for a payment.)

    I am currently working with the titrec, titluip, titlu and tproject tables, so may need to reference another table or use something a bit different.

    Sage 50 / Simply Accounting is designed to be modular, so the Payables information in tVentr / tVentrDT and tPmtHdr is separate and unrelated to the information in the invoice & inventory ledgers and the Project tables.   There's no common key ID field between the AP module and the invoicing module.  (they can be used independently, and one can be cleared without affecting the other)

    And the titrec / titlu tables are shared between customer and vendor transactions.   That means there isn't a unique vendor ID in titrec.lVenCusID to use as a key.

    A relation based on the titrec table for nJournal = 7 (payables) and bReversed = 0 should work as a left outer join to the Vendor A/P tables based on titrec.lvencusID --> sSource1 to tVenTr.lVenID & titrec.lID-->titlu.lTitrecID-->tProject.

    Any help or direction at all would be greatly helpful.

    In a nutshell, build a list of outstanding invoices from the AP tables, then left join that to a list of invoices by project from the invoice / inventory tables.  If you're using Access, you'll have to use the Relationships screen to set up a multi-field join. 

    I hope that helps, please post back!

    Please feel free to contact me off-forum if you need any more specific direction.

Reply
  • 0
    I am unable to find the "paid" flag field.

    That's because there isn't one. 

    A left join from tVenTr.ID to tVenTrDT.lVenTrID should be sum-able by tVenTRDT.dAmount, and grouped by tVenTrID entry will get you a sum due by Vendor Invoice.  

    Or, if you assume that all invoices have either no payments or no outstanding balance, query on that for unpaid invoices. (tVenTrDT.nTranType is zero for an invoice, 1 for a payment.)

    I am currently working with the titrec, titluip, titlu and tproject tables, so may need to reference another table or use something a bit different.

    Sage 50 / Simply Accounting is designed to be modular, so the Payables information in tVentr / tVentrDT and tPmtHdr is separate and unrelated to the information in the invoice & inventory ledgers and the Project tables.   There's no common key ID field between the AP module and the invoicing module.  (they can be used independently, and one can be cleared without affecting the other)

    And the titrec / titlu tables are shared between customer and vendor transactions.   That means there isn't a unique vendor ID in titrec.lVenCusID to use as a key.

    A relation based on the titrec table for nJournal = 7 (payables) and bReversed = 0 should work as a left outer join to the Vendor A/P tables based on titrec.lvencusID --> sSource1 to tVenTr.lVenID & titrec.lID-->titlu.lTitrecID-->tProject.

    Any help or direction at all would be greatly helpful.

    In a nutshell, build a list of outstanding invoices from the AP tables, then left join that to a list of invoices by project from the invoice / inventory tables.  If you're using Access, you'll have to use the Relationships screen to set up a multi-field join. 

    I hope that helps, please post back!

    Please feel free to contact me off-forum if you need any more specific direction.

Children
No Data