Knowledgebase: Syntax
How to create Invoices using QODBC
Posted by Juliet on 15 July 2010 09:08 AM

Examples of Creating Invoice using QODBC

Note: For multi-line invoices, some may find it easier to NOT include the header information with each line item, but rather to have seperate smaller commands for the Invoice Lines and a final  INSERT for the Invoice header.

Create an Invoice with 3 Lines

     Here we create an invoice with 3 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.

First Line

INSERT INTO "InvoiceLine" ("InvoiceLineItemRefListID", "InvoiceLineDesc",
"InvoiceLineRate", "InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID",
"FQSaveToCache") VALUES ('250000-933272656', 'Building permit 1', 1.00000,
1.00, '20000-999022286', 1)

Second Line

INSERT INTO "InvoiceLine" ("InvoiceLineItemRefListID", "InvoiceLineDesc",
"InvoiceLineRate", "InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID",
"FQSaveToCache") VALUES ('250000-933272656', 'Building permit 2', 2.00000,
2.00, '20000-999022286', 1)

Third Line

INSERT INTO "InvoiceLine" ("InvoiceLineItemRefListID", "InvoiceLineDesc",
"InvoiceLineRate", "InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID",
"FQSaveToCache") VALUES ('250000-933272656', 'Building permit 3', 3.00000,
3.00, '20000-999022286', 1)

Header Information

INSERT INTO "Invoice" ("CustomerRefListID", "ARAccountRefListID", "TxnDate",
"RefNumber", "BillAddressAddr1", "BillAddressAddr2", "BillAddressCity",
"BillAddressState", "BillAddressPostalCode", "BillAddressCountry",
"IsPending", "TermsRefListID", "DueDate", "ShipDate", "ItemSalesTaxRefListID",
"Memo", "IsToBePrinted", "CustomerSalesTaxCodeRefListID") VALUES ('470001-1071525403',
'40000-933270541', {d'2002-10-01'}, '1', 'Brad Lamb', '1921 Appleseed
Lane', 'Bayshore', 'CA', '94326', 'USA', 0, '10000-933272658',
{d'2002-10-31'}, {d'2002-10-01'}, '2E0000-933272656', 'Memo Test', 0,
'10000-999022286')

Results in QuickBooks

 

Related Data Location

InvoiceLineItemRefListID

InvoiceLineItemRefListID is '250000-933272656' and found like this:

     Select ListId, FullName from Item where FullName like 'P%'

InvoiceLineSalesTaxCodeRefListID

InvoiceLineSalesTaxCodeRefListID is '20000-999022286' and found like this:

     Select ListId, Name from SalesTaxCode where Name like 'N%'

CustomerRefListID

CustomerRefListID is '470001-1071525403' and found like this:

     Select ListId, FullName from Customer where FullName like 'Lamb%'

ARAccountRefListID

ARAccountRefListID is '40000-933270541' and found like this:

     Select ListId, Name from Account where Name like 'Account%'

ItemSalesTaxRefListID

ItemSalesTaxRefListID is '2E0000-933272656' and found like this:

     Select ListId, Name from ItemSalesTax where Name like 'San%'

CustomerSalesTaxCodeRefListID

CustomerSalesTaxCodeRefListID is '10000-999022286' and found like this:

     Select ListId, Name from SalesTaxCode where Name like 'T%'

 

 


(149 vote(s))
This article was helpful
This article was not helpful

Comments (2)
fahad
04 March 2013 09:37 AM
not support error
Randy Oleksiw
29 April 2013 01:27 PM
What about the InvoiceLinkedTxn table. Do we have to update it as well?
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please enter the text you see in the image into the textbox below. This is required to prevent automated registrations and form submissions.

Help Desk Software by Kayako Resolve