Having trouble logging in or locating tickets ?


 

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)

 

Note:  VB DEMO is to be used for testing of QODBC SQL queries only and is not a development tool.

 

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 (4)
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,

FYI, 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.
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