|
Buy Support
Incidents |
|
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
|
Click Here
|
If you can't
login and post questions or you are having trouble viewing forum posts:
Click Here
|
Callback
Support |
|
If you live in USA, UK, Canada, Australia or New
Zealand, you can leave us details on your question and request us to call you back and discuss
them with you personally (charges apply). |
|
Click Here
|
Buy Support
Incidents |
|
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
|
Click Here
|
|
| Which table do I need? |
| Author |
Message |
|
|
| Posted : 2007-11-15 09:12:05 |
Hi all,
We have a single copy of QB. I am pulling data from PurchaseOrderLine so that I can group lines by Job to show in the access database that we all use. The line values from this table are not always correct , so I would like to link to show the amount paid against each line.
Which table to I need to link to,
Which fields do I need to link or is there an existing link between the tables and
Which field do I need to reference ?
Thanks in advance,
Allan |
|
|
|
| Tom |
 |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-11-15 09:39:14 |
In QuickBooks, Bills are paid, not PurchaseOrderLines like this:
SELECT * FROM bill calldirect where IsPaid=1
the use of calldirect will force the query to run from QuickBooks and bypass any optimizer issues. |
|
|
|
|
|
| Posted : 2007-11-15 13:07:20 |
I realize that Purchase orders are not paid.
The PurchaseOrderLines table gives me all the information I need except for the cost that was actually paid for the line.
You see sometimes orders are entered with a $1 value because the total cost will not be known until the invoice is presented.
The correct value for the line is entered when the bill is entered and the purchase order taken up. I need to be able to get the correct value from the bill entry line and link it to the correct purchase order line in order to get all the information I need.
What are the tables I need and what fields need to be linked in order to get the information i am after?
Regards,
Allan |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-11-15 14:47:52 |
This isn't simple as there's no direct link between PurchaseOrderLInes and BillItemLines. In fact it gets as bad as all this:
Select * from PurchaseOrderLinkedTxn, PurchaseOrder, PurchaseOrderLine, Bill, BillItemLine
where PurchaseOrderLinkedTxn.TxnID = PurchaseOrder.TxnID
and PurchaseOrder.TxnID = PurchaseOrderLine.TxnID
and PurchaseOrderLinkedTxn.LinkedTxnTxnID = Bill.TxnID
and Bill.TxnID = BillItemLine.TxnID
and PurchaseOrderLine.PurchaseOrderLineItemRefListID = BillItemLine.ItemLineItemRefListID
and PurchaseOrderLinkedTxn.RefNumber = '17'
Subsitute the '17' with the Reference Number of your Purchase Order. |
|
|
|
|