Advertise here    

QuickBooks Forums      

Go Back   QuickBooks Forums > QuickBooks Software Support > QuickBooks Enterprise Edition Forum

Reply
 
Thread Tools Rate Thread Display Modes
  #1  
Old 09-04-2017, 07:27 PM
Albie0803 Albie0803 is offline
Registered User
 
Join Date: Sep 2017
Posts: 2
Matching A POLine with the BillLine that pays it.

I am using QODBC to read data into MS Access.

I find unpaid purchase order lines by filtering for lines that don't have a matching transaction.

(PurchaseOrderLine LEFT JOIN BillLinkedTxn ON PurchaseOrderLine.TxnID = BillLinkedTxn.LinkedTxnTxnID) where BillLinkedTxn.TxnID is null

As the price doesn't always get entered on the purchase order line, I want to link it with its Bill line so the correct costing is shown. I am using:

SELECT FROM (PurchaseOrderLine LEFT JOIN BillLinkedTxn ON PurchaseOrderLine.TxnID = BillLinkedTxn.LinkedTxnTxnID) LEFT JOIN BillItemLine ON (PurchaseOrderLine.PurchaseOrderLineItemRefListID = BillItemLine.ItemLineItemRefListID) AND (BillLinkedTxn.TxnID = BillItemLine.TxnID) AND (PurchaseOrderLine.PurchaseOrderLineSeqNo = BillItemLine.ItemLineSeqNo);

The problem I get is that this returns ALL transactions against a PO Line.

If there is one bill for the PO, there is 1 transaction and the bill amount links

BUT

Part deliveries and multiple bills create multiple entries in my results with the cost being added multiple times.

Q. How do I filter to get only the transaction that the bill was ACTUALLY paid on so that each line returns only 1 match?
Reply With Quote
  #2  
Old 09-08-2017, 06:23 PM
Lorin Browning Lorin Browning is offline
Registered User
 
Join Date: Apr 2004
Location: Landrum SC
Posts: 615
As some have said, "That is above my pay grade."

You may want to try http://qodbc.com/support.htm if you don't get your answer here.
__________________
Lorin Browning, Ph. D.
Fellow -- National Tax Practice Institute
lorin@lorinbrowning.com
Reply With Quote
  #3  
Old 09-10-2017, 05:05 PM
Albie0803 Albie0803 is offline
Registered User
 
Join Date: Sep 2017
Posts: 2
Yes I did thanks
Reply With Quote
Reply

Bookmarks
Bookmark and Share
Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is On

Forum Jump


All times are GMT -5. The time now is 06:18 AM.


 

Powered by vBulletin® Version 3.8.4
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
All contents copyright (c) by AccountingUsers Inc.