Knowledgebase
[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 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 separate 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 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",
"InvoiceLineRate", "InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID",
"FQSaveToCache") VALUES ('670004-1044572237', 'Building permit 1', 1.00000,
1.00, '10000-1011136881', 1)


Note: 
 QODBC Support Wizard is used only to test QODBC SQL queries and is not a development tool.

 

Second Line

INSERT INTO "InvoiceLine" ("InvoiceLineItemRefListID", "InvoiceLineDesc",
"InvoiceLineRate", "InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID",
"FQSaveToCache") VALUES ('670004-1044572237', 'Building permit 2', 2.00000,
2.00, '10000-1011136881', 1)

Third Line

INSERT INTO "InvoiceLine" ("InvoiceLineItemRefListID", "InvoiceLineDesc",
"InvoiceLineRate", "InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID",
"FQSaveToCache") VALUES ('670004-1044572237', 'Building permit 3', 3.00000,
3.00, '10000-1011136881', 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 ('4C0000-1040154668',
'50001-896816252', {d'2021-12-22'}, '1', 'Brad Lamb', '1921 Appleseed
Lane', 'Bayshore', 'CA', '94326', 'USA', 0, '50000-898307888',
{d'2021-12-22'}, {d'2021-12-31'}, '80000097-1481806775', 0,
'10000-1011136881')

Results in QuickBooks

 

Method 2: Header & Line information in a single SQL statement 

First Line

INSERT INTO "InvoiceLine" ("CustomerRefListID", "ARAccountRefListID", "TxnDate", 
"RefNumber", "BillAddressAddr1", "BillAddressAddr2", "BillAddressCity", 
"BillAddressState", "BillAddressPostalCode", "BillAddressCountry", 
"IsPending", "TermsRefListID", "DueDate", "ShipDate", "ItemSalesTaxRefListID", 
"Memo", "IsToBePrinted", "CustomerSalesTaxCodeRefListID","InvoiceLineItemRefListID", "InvoiceLineDesc",
"InvoiceLineRate", "InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID",
"FQSaveToCache") VALUES ('4C0000-1040154668', 
'50001-896816252', {d'2021-12-22'}, '1', 'Brad Lamb', '1921 Appleseed 
Lane', 'Bayshore', 'CA', '94326', 'USA', 0, '50000-898307888', 
{d'2021-12-22'}, {d'2021-12-31'}, '80000097-1481806775', 'Memo Test', 0, 
'10000-1011136881','670004-1044572237', 'Building permit 1', 1.00000,
1.00, '10000-1011136881', 1)

Second Line

INSERT INTO "InvoiceLine" ("CustomerRefListID", "ARAccountRefListID", "TxnDate", 
"RefNumber", "BillAddressAddr1", "BillAddressAddr2", "BillAddressCity", 
"BillAddressState", "BillAddressPostalCode", "BillAddressCountry", 
"IsPending", "TermsRefListID", "DueDate", "ShipDate", "ItemSalesTaxRefListID", 
"Memo", "IsToBePrinted", "CustomerSalesTaxCodeRefListID","InvoiceLineItemRefListID", "InvoiceLineDesc",
"InvoiceLineRate", "InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID",
"FQSaveToCache") VALUES ('4C0000-1040154668', 
'50001-896816252', {d'2021-12-22'}, '1', 'Brad Lamb', '1921 Appleseed 
Lane', 'Bayshore', 'CA', '94326', 'USA', 0, '50000-898307888', 
{d'2021-12-22'}, {d'2021-12-31'}, '80000097-1481806775', 'Memo Test', 0, 
'10000-1011136881','670004-1044572237', 'Building permit 2', 2.00000,
2.00, '10000-1011136881', 1)

 

Third Line

INSERT INTO "InvoiceLine" ("CustomerRefListID", "ARAccountRefListID", "TxnDate", 
"RefNumber", "BillAddressAddr1", "BillAddressAddr2", "BillAddressCity", 
"BillAddressState", "BillAddressPostalCode", "BillAddressCountry", 
"IsPending", "TermsRefListID", "DueDate", "ShipDate", "ItemSalesTaxRefListID", 
"Memo", "IsToBePrinted", "CustomerSalesTaxCodeRefListID","InvoiceLineItemRefListID", "InvoiceLineDesc",
"InvoiceLineRate", "InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID",
"FQSaveToCache") VALUES ('4C0000-1040154668', 
'50001-896816252', {d'2021-12-22'}, '1', 'Brad Lamb', '1921 Appleseed 
Lane', 'Bayshore', 'CA', '94326', 'USA', 0, '50000-898307888', 
{d'2021-12-22'}, {d'2021-12-31'}, '80000097-1481806775', 'Memo Test', 0, 
'10000-1011136881','670004-1044572237', 'Building permit 3', 3.00000,
3.00, '10000-1011136881', 0)

Results in QuickBooks

 

 

To Find Related Data Location

InvoiceLineItemRefListID

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

 

 

(151 vote(s))
Helpful
Not helpful

Comments (10)
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?
Jermaine Gray
27 September 2014 12:26 PM
Hello am getting the following error when trying to insert the invoice header.
------
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]".
Jack
29 September 2014 07:06 AM
Hi Jermaine,

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.
Jui-Pin Jao
06 August 2015 04:04 AM
In Canada sales taxes are applied in three different ways - at the federal level through the Goods and Services Tax (GST), at the provincial level through a provincial sales tax (PST), sometimes called the retail sales tax, or through the Harmonized Sales Tax (HST), a single tax combining the GST and PST. The rates vary by province and territory, as do the goods and services on which the tax is applied and the way the tax is applied. Does anyone know what need to be changed in this example to accommodate that?
Jack
06 August 2015 08:46 AM
Hi,

This question is related to QuickBooks rather than QODBC. I would suggest you to raise a this question at Intuit forum.
Jui-Pin Jao
07 August 2015 02:54 AM
Hi Jack:

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 ?
Jack
07 August 2015 08:26 AM
Yes.
Jui-Pin Jao
10 August 2015 02:40 AM
Hi Jack:
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.
Jack
10 August 2015 07:57 AM
Hi,

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
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).