Problem Description:

Can you please tell me which tables and fields link the purchase order payments to purchase orders?

I can't seem to track that. Basically, I need to see how much has been paid against a purchase order and that the outstanding balance is.

Solution:

You can refer to the below sp_report or can query the PurchaseOrderLine table as described below & can get the required details.

Typical Open Purchase Orders by Job Report in QuickBooks

Report Displayed in QODBC

Stored Procedure Command

      With QODBC, the same report can be generated using stored procedure reports like this:

      sp_report OpenPOsByJob show Text as Type, Blank as Total, Date,
      delivery date as "Deliv Date," RefNumber as Num, SourceName, Item, Quantity as Qty,
      ReceivedQuantity as Rcvd, UnitPrice as "Cost Price," Amount
      parameters DateMacro = 'All'

Display in QODBC

Access the Same Data from PurchaseOrderLine Table

SELECT Command

      SELECT PurchaseOrderLineCustomerRefFullName as Type, TxnDate as Date, DueDate as "Deliv Date",
      RefNumber as "Num", VendorRefFullName as SourceName,PurchaseOrderLineItemRefFullName as Item,
      PurchaseOrderLineQuantity as Qty, PurchaseOrderLineReceivedQuantity as Rcvd,
      PurchaseOrderLineRate as Cost, TotalAmount as Amount FROM PurchaseOrderLine where IsFullyReceived = 0
      and PurchaseOrderLineCustomerRefFullName is not Null order by PurchaseOrderLineCustomerRefFullName