|
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
|
|
| Last Purchased Cost for an inventory item? |
| Author |
Message |
|
|
| Posted : 2007-07-04 06:59:02 |
If I wished to retrieve the last purchased cost for an inventory item, which table/field should be used?
Thanks in advance! |
|
| |
Bryant Farley
www.databasedude.com
|
| | Top | |
|
|
| Tom |
 |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-07-04 08:09:12 |
|
|
|
|
|
| Posted : 2007-07-06 00:15:43 |
Hi Tom,
Thanks for your response & the example!
However, what I'm trying to extract doesn't relate to COGS or the cost of something that's being invoiced.
Maybe this will be more clear: for an inventory item, what was the most recent price I paid for it? |
|
| |
Bryant Farley
www.databasedude.com
|
| | Top | |
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-07-06 08:44:43 |
Ok, but's not how QuickBooks calculates cost of a item invoiced as far as acccounting goes. If you want to know what you last paid for an item before you invoice it at a discount price, simply do:
Select TOP 1 VendorRefFullName as Supplier, ItemLineItemRefFullName as Part,
ItemLineDesc as "Desc", ItemLineCost as Cost
from BillItemLine
Where "ItemLineItemRefFullName" = 'Window'
order by "TxnDate" DESC
and replace 'Window' with the full name of the part you're interested in.
If you want to see the last three costs, change TOP 1 to TOP 3 |
|
|
|
|
|
| Posted : 2007-07-07 00:38:04 |
Great, that gets just the data fields I'm looking for! There's some fine tuning that I can do on my own now, but you pointed me in just the direction I needed to go (thanks!!!).
Two follow-up questions ...
1. Is there a way to limit results to include only inventory parts (excluding inventory assemblies & non-inventory parts)?
2. Does QODBC support the GROUP BY statement of SQL?
Thanks!
Bryant |
|
| |
Bryant Farley
www.databasedude.com
|
| | Top | |
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-07-07 13:08:42 |
Sure, here I've grouped the parts, linked the ItemInventory table so I just get stock parts, and found the maximum cost instead:
Select BillItemLine.ItemLineItemRefFullName as Part,
BillItemLine.ItemLineDesc as "Desc", max(BillItemLine.ItemLineCost) as Cost
from BillItemLine, ItemInventory
Where BillItemLine.ItemLineItemRefListID = ItemInventory.ListID
group by BillItemLine.ItemLineItemRefFullName, BillItemLine.ItemLineDesc,
BillItemLine.ItemLineCost
|
|
|
|
|