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
|
|
PurchaseByItemDetail |
Author |
Message |
|
Posted : 2006-07-07 02:27:27 |
Is there a way to associate a record in the PurchaseByItemDetail report with a record in the InvoiceLine table? My objective is to maintain an Access table that lists all purchases (billed and unbilled) for all items for all customers with details of taxes and invoices numbers when billed and to have an unambigous method for updating it on a regular basis. I haven't found a unique transaction id that appears in both the report and the table.
Things I've tried: The TaxCode field in the PurchaseByItemDetail report does not work. (Yes, I'm using the latest version of the QODBC driver.) I can use an access query that matches transaction/customer/item/amount but occasionally there are more than one items in a transaction with the same customer/item/amount. |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2006-07-07 09:25:24 |
By the sounds of things you are trying to compare what you paid for an item for a customer:job to what you invoiced it for? Try looking at the Item Profitability report instead using:
sp_report ItemProfitability show AmountActualCost_Title, AmountActualRevenue_Title, AmountDifferenceActual_Title, Text, Label, AmountActualCost, AmountActualRevenue, AmountDifferenceActual parameters DateMacro = 'All', SummarizeColumnsBy = 'TotalOnly' |
|
|
|
|
Posted : 2006-07-07 11:27:51 |
No, I want a simple register of all items so that I can prepare various court documents that show total costs (billed and unbilled) for a given matter (customer:job in QuickBooks) in a format prescribed by regulation. We also produce an itemized listing by date. (Our items include services and two broad categories of other charges - taxable disbursements and non-taxable disbursements.) The other charges are passed through at cost (no profitability involved). In the past I was able to assume the tax rate based on whether or not it was taxable, eg services and taxable disbursements used a 7% tax code for the GST and non-taxable used a 0% tax code. I've added new tax codes for the new GST rate, but the business rule for determining whether the old code or the new code applies requires the date of our invoice to our client for that disbursement.
Frankly, I find it shocking that there is no backward link between an item record on an invoice and the original item record in the original transaction. I always knew it was a clumsy matter to change an invoice by deleting an item line and that I needed to reset the billing flag in the original transaction if i wanted it to reflect the actual invoicing state. I just assumed this was sloppy design of the user interface. The fact that the data structure itself does not permit the backward link represents a serious problem for us.
Sorry about the rant. I'm frustrated after spending hours trying to find some combination of report and/or table that will help me associate item transactions with item invoicing. I've decided it is easier to write my own database system for managing client disbursement details and then write summaries to QuickBooks invoices. |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2006-07-07 14:19:29 |
It is possible to track purchases and invoices for a matter, however you would need to post all the purchases, bills and invoices via QODBC using an external tracking application so you can keep records of the links. Another approch is to use a custom field with the matter reference so you can assemble all the transactions for a particular matter. |
|
|
|
|