Vendor Query

It depends on how you want to search. The most reliable searches use criteria from the originating table.

To create a query by ledger account start with the APDIST table and then add the invoice info from APINVOICE and the Vendor Info from the APVENDMST table. The only problem is you can't access any of the APPayment tables directly when you start at the distribution level.

If you start with the APINVOICE table to include the Payment information you can't use the ledger accounts as criteria for the search.

Tom is right that the APINVOICE table is the best way to get both the APDIST and APPAYMENT data in your query. APINVOICE has the greatest number of related tables of the most common AP tables. You can use the APDIST gl accounts as criteria if you select the APDISTRIB DIS-ACCOUNT's etc as fields in the query. (For example if looking for our income statements accounts, I have the criteria as APDISTRIB.DIS-ACCOUNT >= 400000)

Thank you for all the help. I am almost there. The last item I am looking to include is check number. Can you tell me what table/field has this informaiton?

Use the TRANS-NBR for the payment number rom the APPAYMENT table. If you are looking for the check amount use the PMT AMT from the CBCHECK.TRAN table. The PMT AMT from the APPAYMENT table only gives you the Pmt amount for that invoice. If you want the total payment amount you need to query the CB tables.