If you need to create a one line item invoice for example, you can use a format similar to this:
INSERT INTO "InvoiceLine" ("CustomerRefListID", "RefNumber",
"InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate",
"InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID") VALUES
('470001-1071525403', '1', '250000-933272656', 'Building permit 1',
1.00000, 1.00, '20000-999022286')
The above transaction inserts all required data in the InvoiceLine table to create a complete record in the InvoiceLine table, as well as the Invoice header table, and saves the completed invoice record immediately. This is the simplest form of invoice creation.
Multi-line invoices require a series of SQL statements to complete a single invoice. In the example below we are creating a 3 line invoice using 3 SQL INSERT commands in sequence. The key to this process is the field named "FQSaveToCache". This field is not part of the table, but is used as a flag to the QODBC driver. In the sequence below, you should note that the value of "FQSaveToCache" is set to 1 or TRUE for the first two line item insert statements, and then it is set to 0 or FALSE for the final statement.
A TRUE setting of "FQSaveToCache" instructs QODBC to take the values from your INSERT statement and hold them for later processing, but not to save them to QuickBooks yet. When QODBC receives the final transaction where the cache is set to 0 or FALSE, the contents of the current INSERT statement will be combined with all of the previous INSERT statements held in the cache for this connection, and saved as a batch into QuickBooks.
QODBC maintains a connection for each application using the driver, and this cache is specific for each connection, so multiple applications or users of QODBC will not interfere with the cached transactions of other users. There is no set limit to the number of lines that can be cached for a single transaction, other than what QuickBooks would limit you to.
Since the data on the INSERT statement is being cached and not written to QuickBooks, some application tools (most notably Microsoft Access) will re-check that the data was saved properly to the target database by running a SELECT statement following a successful INSERT statement. In this example, this re-check will fail.
To get around this, use a pass-thru query (Append Query) and ignore any errors in the processing of the transaction, except for the last one. After the final line has been saved and the record is inserted into QuickBooks, then you can do your own validation by SELECTing the results of the Invoice number you just created in the Invoice and InvoiceLines tables to see that it was inserted correctly.
INSERT INTO "InvoiceLine" ("CustomerRefListID", "RefNumber",
"InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate",
"InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID", "FQSaveToCache")
VALUES ('470001-1071525403', '1', '250000-933272656', 'Building permit 1',
1.00000, 1.00, '20000-999022286', 1)
INSERT INTO "InvoiceLine" ("CustomerRefListID", "RefNumber",
"InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate",
"InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID", "FQSaveToCache")
VALUES ('470001-1071525403', '1', '250000-933272656', 'Building permit 2',
2.00000, 2.00, '20000-999022286', 1)
INSERT INTO "InvoiceLine" ("CustomerRefListID", "RefNumber",
"InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate",
"InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID", "FQSaveToCache")
VALUES ('470001-1071525403', '1', '250000-933272656', 'Building permit 3',
3.00000, 3.00, '20000-999022286', 0)
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. This will also work as in the example below.
Here we create an invoice with 3 lines by using 3 InvoiceLine INSERT commands (with the 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.
INSERT INTO "InvoiceLine" ("InvoiceLineItemRefListID", "InvoiceLineDesc",
"InvoiceLineRate", "InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID",
"FQSaveToCache") VALUES ('250000-933272656', 'Building permit 1', 1.00000,
1.00, '20000-999022286', 1)
INSERT INTO "InvoiceLine" ("InvoiceLineItemRefListID", "InvoiceLineDesc",
"InvoiceLineRate", "InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID",
"FQSaveToCache") VALUES ('250000-933272656', 'Building permit 2', 2.00000,
2.00, '20000-999022286', 1)
INSERT INTO "InvoiceLine" ("InvoiceLineItemRefListID", "InvoiceLineDesc",
"InvoiceLineRate", "InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID",
"FQSaveToCache") VALUES ('250000-933272656', 'Building permit 3', 3.00000,
3.00, '20000-999022286', 1)
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')
If it's not possible to know what will be the last line and you don't want to do a header insert, you can also insert a blank line like this to commit the transaction:
INSERT INTO "InvoiceLine" ("InvoiceLineDesc", "FQSaveToCache") VALUES ('', 0) |
