|
[QODBC-Desktop] How to create Invoices using QODBC
Posted by Juliet (QODBC Support) on 15 July 2010 09:08 AM
|
|
Examples of Creating Invoice using QODBCNote: For multi-line invoices, some may find it easier to NOT include the header information with each line item, but rather to have separate smaller commands for the Invoice Lines and a final INSERT for the Invoice Header. Create an Invoice with 3 LinesHere we create an invoice with three lines by using 3 InvoiceLine INSERT commands (with the FQSaveToCache caching flag set true) followed by the Invoice table (header) insert, which is automatically designed to pull in all cached InvoiceLine records and save immediately. Method 1: Insert line records followed by header information First Line INSERT INTO "InvoiceLine" ("InvoiceLineItemRefListID", "InvoiceLineDesc",
Second Line INSERT INTO "InvoiceLine" ("InvoiceLineItemRefListID", "InvoiceLineDesc",
Third Line INSERT INTO "InvoiceLine" ("InvoiceLineItemRefListID", "InvoiceLineDesc",
Header Information INSERT INTO "Invoice" ("CustomerRefListID", "ARAccountRefListID", "TxnDate",
Results in QuickBooks
Method 2: Header & Line information in a single SQL statement First Line INSERT INTO "InvoiceLine" ("CustomerRefListID", "ARAccountRefListID", "TxnDate",
Second Line INSERT INTO "InvoiceLine" ("CustomerRefListID", "ARAccountRefListID", "TxnDate",
Third Line INSERT INTO "InvoiceLine" ("CustomerRefListID", "ARAccountRefListID", "TxnDate",
Results in QuickBooks
To Find Related Data LocationInvoiceLineItemRefListID InvoiceLineItemRefListID is '670004-1044572237' and found like this: Select ListId, FullName from Item where FullName like 'I%'
InvoiceLineSalesTaxCodeRefListID InvoiceLineSalesTaxCodeRefListID is '10000-1011136881' and found like this: Select ListId, Name from SalesTaxCode where Name like 'T%'
CustomerRefListID CustomerRefListID is '4C0000-1040154668' and found like this: Select ListId, FullName from Customer where FullName like 'Pete%'
ARAccountRefListID ARAccountRefListID is '50001-896816252' and found like this: Select ListId, Name from Account where Name like 'Account%'
ItemSalesTaxRefListID ItemSalesTaxRefListID is '80000097-1481806775' and found like this: Select ListId, Name from ItemSalesTax where Name like 'G%'
CustomerSalesTaxCodeRefListID CustomerSalesTaxCodeRefListID is '10000-1011136881' and found like this: Select ListId, Name from SalesTaxCode where Name like 'T%'
Problem Description:Hi, I followed the example you gave in the Knowledgebase. It works, but how do I change the above code so I can enter any of my current customers instead of the example " Brad Lamb "above?
Solution:You can manually change the value in the query or insert records through the local access table; you need to write VBA scripts that will fetch data from a local access table and perform the insert operation in QuickBooks using QODBC. Please refer to the below-mentioned article for the Sample VBA code: Using QuickBooks Data with VBA
| |
 (149 vote(s)) Helpful Not helpful |
------
INSERT INTO QODBC...Invoice
(CustomerRefListID, ARAccountRefListID, TxnDate, RefNumber, BillAddressAddr1, BillAddressAddr2, BillAddressCity, BillAddressState, BillAddressPostalCode, BillAddressCountry, IsPending, TermsRefListID, DueDate,
ShipDate, ItemSalesTaxRefListID, Memo, IsToBePrinted, CustomerSalesTaxCodeRefListID)
VALUES ('80000002-1410147021', '80000007-1410138369', CONVERT(DATETIME, '2002-10-01', 102), '1', 'Brad Lamb', '1921 Appleseed Lane', 'Bayshore', 'CA', '94326', 'USA', 0, '80000005-1410138368', CONVERT(DATETIME, '2002-10-31',
102), CONVERT(DATETIME, '2002-10-01', 102), '80000002-1410138435', 'Memo Test', 0, '80000003-1410138562')
------
OLE DB provider "MSDASQL" for linked server "QODBC" returned message "[QODBC] Not supported".
Msg 7343, Level 16, State 2, Line 1
The OLE DB provider "MSDASQL" for linked server "QODBC" could not INSERT INTO table "[QODBC]...[Invoice]".
You cannot directly insert records in the header table.
For an invoice table a detail (child) record entry must be there related to Parent record.
You can use the query as below mentioned:
For Example:
INSERT INTO qremote...InvoiceLine (CustomerRefListID, RefNumber,
InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate,
InvoiceLineAmount, InvoiceLineSalesTaxCodeRefListID,FQSaveToCache)
VALUES ('440001-1044573108', '71047', '8A0003-1044570142', 'POWERTRAK-2000', 200.00000, 200.00, '20000-1011136881',1)
INSERT INTO qremote...INVOICE (CustomerRefFullName, ARAccountRefFullName, TxnDate, RefNumber, BillAddressAddr1, BillAddressAddr2, BillAddressCity, BillAddressState, BillAddressPostalCode, IsPending, TermsRefFullName, ShipDate, Memo, IsToBePrinted )
VALUES ('Regina French Homes:Wrong Way Road', 'Accounts Receivable', {d'2016-12-31'}, '71047', 'Brad Lamb', '1921 Appleseed Lane', 'Bayshore', 'CA', '94326', 0, 'Net 30', {d'2016-12-31'}, 'Memo Test', 0)
Please change your linked server name & values according to your company file.
I would like to inform you that you need to execute child row insert query & header row insert query in a same Query editor window in a single session, there should be no more time gap between this execution. First, you need to execute child row query, then execute header row query.
This question is related to QuickBooks rather than QODBC. I would suggest you to raise a this question at Intuit forum.
Thank you the response. So do you means same example codes using QODBC here should work for QuickBooks Canada version as long as the sale tax codes set up properly? Like QODBC's SalesTaxCode table will mapped the right sale tax code for a QuickBooks Canada version ?
In the example, both CustomerSalesTaxCodeRefListID and InvoiceLineSalesTaxCodeRefListID are found by Select ListId, Name from SalesTaxCode where Name ='NON' or
Select ListId, Name from SalesTaxCode where Name ='TAX'
For Quickbook Canada version I guess for non tax item the name in SalesTaxCode table should be still 'NON'. But for the taxable item, ARe you saying I don't need to change to
Select ListId, Name from SalesTaxCode where Name ='HST' or
Select ListId, Name from SalesTaxCode where Name ='GST' or
Select ListId, Name from SalesTaxCode where Name ='PST' ?
And to look up ItemSalesTaxRefListID
for Provincial sales taxes in Ontario, Is the SQL
Select ListId, Name from ItemSalesTax where Name = 'Ontario'?
Thanks.
For taxable item please use salestaxcode whatever you need (i.e. HST/GST/PST). To look up ItemSalesTax your query is correct.
If you are still facing issue, I kindly request you to please raise a support ticket to the QODBC Technical Support department from below mentioned link & provide requested information:
http://support.flexquarters.com/esupport/index.php?/Tickets/Submit
I kindly request you to share more information about the issue you’re facing, so that we can locate the problem quickly.
We may need following information, I kindly request you to attach below listed files when replying to the ticket.
1) Screenshot of QODBC Setup Screen -- > About (Start>>All Programs>> QODBC Driver for use with QuickBooks>> QODBC Setup Screen >> About Tab )
2) Screenshot of the issue you’re facing.
3) Share the SQL statement you’re using.
Share Entire Log Files as an attachment in text format from
4) QODBC Setup Screen -- > Messages -- > Review QODBC Messages
5) QODBC Setup Screen -- > Messages -- > Review SDK Messages