|
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
|
|
| Query relating invoice details to JE distributions |
| Author |
Message |
|
|
| Posted : 2008-11-11 01:59:33 |
Quickbooks database question
I am looking to write an update query to update the class id field of Invoice Details (InvoiceLine Table) based off the account used for the Journal Entry distribution and I am having an issue determining how to find a link to join the data on. Does anyone have a tip as to what the relationship fields are between the relevant tables to get at the data?
Thanks
|
|
|
|
| Tom |
 |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2008-11-11 08:23:17 |
The actual SQL Update Statement is:
UPDATE InvoiceLine Set ClassRefListID='30000-933272658'
where TxnID = '2B55-1071523067' and InvoiceLIneTxnLineID = '2B57-1071523067'
where ClassRefListID is the ListID seen in the Class table by doing:-
SELECT * from Class
See: Using ClassRefListID when inserting invoice lines for more. |
|
|
|
|
|
| Posted : 2008-11-13 00:25:22 |
HI Tom,
Thanks for the reply. The update query you have there is essentially what I would like to do. I do need to add another piece to it. Hope fully you can point me in the right direction on this.
I need to do one of the following to retrieve the account number associated with the InvoiceLine record to use as the class id. The last three digits of the account number (COGS, the account attached to the item). The corresponding classes are already in the system. What I really need help on is the following.
Join the InvoiceLine table to the JournalEntryLine Table to retrieve the account number(or the Account ID).
or
Join the InvoiceLine table with the item table based on the item used in the invoice line record. The retrieve the account info from there.
I have been unable to find the relationship between the tables to achieve this.
Thanks Again for the help.
|
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2008-11-13 07:40:39 |
Try starting with:
SELECT InvoiceLine.InvoiceLineItemRefFullName as Type,
InvoiceLine.TxnDate as Date, InvoiceLine.DueDate,
InvoiceLine.RefNumber as Num, InvoiceLine.CustomerRefFullName as Name,
InvoiceLine.InvoiceLineQuantity as Qty,
InvoiceLine.InvoiceLineAmount as Amount,
InvoiceLine.Memo, Item.COGSAccountRefListID,
Item.COGSAccountRefFullName
FROM InvoiceLine, Item
where InvoiceLine.InvoiceLineItemRefListID = Item.ListID
and InvoiceLine.RefNumber = '1' and Item.COGSAccountRefListID is not null
and changing InvoiceLine.RefNumber = '1' to the Invoice No you're insterested in. |
|
|
|
|
|
| Posted : 2008-11-18 07:22:06 |
Hi Tom,
Thanks Again. The issue that I am finding is that some line items do not have an item associated with it in the invoiceline table? is there a way to get the account that is associated with those invoice lines when I do not have an item. Maybe get it from another table or maybe the journalentryline table.
Sorry to come back at you with this one but I am finding the QB table structure to be somewhat difficult to navigate.
Thanks
Ern |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2008-11-18 10:30:43 |
The previous example was for STOCK only, this will pick up all ITEMs:-
SELECT InvoiceLine.InvoiceLineItemRefFullName as Type,
InvoiceLine.TxnDate as Date, InvoiceLine.DueDate,
InvoiceLine.RefNumber as Num, InvoiceLine.CustomerRefFullName as Name,
InvoiceLine.InvoiceLineQuantity as Qty,
InvoiceLine.InvoiceLineAmount as Amount,
InvoiceLine.Memo, Transaction.AccountRefFullName
FROM InvoiceLine, Transaction
where InvoiceLine.TxnID = Transaction.TxnID
and InvoiceLine.InvoiceLineTxnLineID = Transaction.TxnLineID
and InvoiceLine.RefNumber = '1'
Just change InvoiceLine.RefNumber = '1' to the Invoice No you're insterested in. |
|
|
|
|