OK, I know I'm asking something that should probably be disasterously obvious, but the answer seems to be escaping me.

I'm working on creating the reorder points for our products.  I need to connect the PODate (in other words, the order date), and the Receiving date to calculate the lead time.  So a preview of the data layouts yeilded me this query:

SELECT Item.Name, Item.SalesDesc, PurchaseOrderLine.RefNumber, PurchaseOrderLine.TxnDate AS "podate", ItemReceipt.TxnDate AS "rcvdate", Vendor.Name
FROM PurchaseOrderLine, Item, ItemReceiptItemLine, ItemReceipt, Vendor WHERE PurchaseOrderLine.PurchaseOrderLineItemRefListID = Item.ListID AND ItemReceiptItemLine.ItemLineItemRefListID = Item.ListID AND Item.Name LIKE '13121%' AND ItemReceiptItemLine.VendorRefListID = Vendor.ListID AND ItemReceiptItemLine.TxnID = ItemReceipt.TxnID

The only problem is that the recieving date data was not valid.  All records returned the same date.  I ran the Inventory Valuation Detail report and found that it retrieved the correct dates for receiving/  So my question is, what is the  SQL behind the Inventory Valuation Detail report?  Obviously it is pulling different data. Or better said, how do I link the PO Date and the Receiving date?

Thanks,

Andrew

PS- Environment: QBMfg&wholesale2007, qodbc v7, vb demo 32

The SQL behind a Inventory Valuation Detail report in QuickBooks like this:

is done via a QODBC stored procedure like this:

sp_report InventoryValuationDetail show Text, Blank, TxnType, Date, Name, RefNumber,
Quantity, CostPrice, QuantityOnHand, AverageCost, ValueOnHand
parameters DateMacro = 'ThisMonthToDate'

As far as the PO Date and the Receiving date, this query extracts all the date values for Cabinet Pulls :-

SELECT PurchaseOrderLine.PurchaseOrderLineDesc as Item, ItemReceiptLinkedTxn.TxnDate
as "Received Date", PurchaseOrderLine.TxnDate as "Ordered Date",
PurchaseOrderLine.DueDate, PurchaseOrderLine.ExpectedDate
FROM ItemReceiptLinkedTxn, PurchaseOrderLine
where ItemReceiptLinkedTxn.LinkedTxnTxnID = PurchaseOrderLine.TxnID
and PurchaseOrderLine.PurchaseOrderLineDesc='Cabinet Pulls'

Tom,

Thanks for the reply.  That got me a lot closer to where I want to be.  However, I have a question I need some help on.  When I run the query I get only the last couple months of data.  I do not see any date range limitations in the query so why would it limit to only the last few months.  The Inventory valuation detail shows much more information. 

Is there a setting somewhere in the vbdemo I need to change?

Thanks,

Andrew

I don't think the problem is with the number of lines.  Right now, the query returns 2 lines.  It should be returning around 10.  If I remove the link to the ItemReceiptLinkedTxn table, it shows all POs for a part.  Any other ideas?

Thanks,

Andrew

The query only shows Item Receipts only without a Bill. Could this be the issue?

Nope.  Of the two that do show up, both have been recieved, one has been paid, and the other has a bill that will be paid in two days.

Could this be a sign of currupted and/or invalid database relations etc?

Thanks for  your hep, Tom.

Andrew

Exactly, the query shows stock parts that have been received as Item Receipts only. If the part is received as a bill it's a different process.

See:
How do I convert a Purchase Order into a Bill? 
How do I receive Items against a Purchase Order? Receive Inventory without Bill? ItemReceipts 

OK.  Wait a min.  Both have been billed.  One has been paid.  If I understand you correctly, only one should have shown up as only one of the two has not been paid.  Both POs have been received in full.

Either way, how would the query change to capture the ones that are paid and closed and said and done from February (we opened for business around then) on?

Thanks,

Andrew