Knowledgebase
[QODBC-Desktop] How to create multiple lines in the Invoice, Purchase and Journal table
Posted by Juliet (QODBC Support) on 29 October 2009 07:26 AM

Note: The primary rule is first to save the data to the child record. The child record for each parent/child pair has all the data required by the parent record.

          Below are some SQL Commends examples of various types of updates.

Creating Lines in Invoice Table

Example of creating one line item Invoice

      If you need to create a one-line item invoice, for example, you can use a format that is the simplest form of invoice creation 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')

Note: The above transaction inserts all required data in the InvoiceLine table to create a complete record in the InvoiceLine table and the Invoice header table and immediately saves the completed invoice record.

Instructions on the working principle of creating Multi-line Invoices

       Multi-line invoices require a series of SQL statements to complete a single invoice. In the example below, we are producing 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 saved as a batch into QuickBooks.

       QODBC maintains a connection for each application using the driver. This cache is specific for each link, so multiple applications or users of QODBC will not interfere with the cached transactions of other users. There is no limit to the number of lines 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 correctly saved 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, you can do your validation by Selecting the results of the Invoice number you just created in the Invoice and InvoiceLine tables to see that it was inserted correctly.

Examples of creating multiple lines in the invoice

Example with header information in each line item insert

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)

Example with header information in Invoice table insert

       For multi-line invoices, some may find it easier not to include the header information with each line item but to have separate 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 three lines by using 3 InvoiceLine INSERT commands (with the caching flag set to 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-93327265 6', 'Memo Test', 0,
'10000-999022286')

 

Create Lines in Purchase Order Table

Example of creating one line Purchase Order

Note: the FQSaveToCache field is not specified (or can be set to false)

INSERT INTO "PurchaseOrderLine" ("VendorRefListID", "RefNumber",
"PurchaseOrderLineItemRefListID", "PurchaseOrderLineDesc",
"PurchaseOrderLineQuantity", "PurchaseOrderLineRate",
"PurchaseOrderLineAmount", "PurchaseOrderLineCustomerRefListID") VALUES
('10000-933272655', '1', '250000-933272656', 'See Attached 1', 1.0, 1.0,
1.11, '580000-1071526281')

Example of creating Multi-line Purchase Orders

Example with header information in each line item insert

Note: This example creates one PurchaseOrder with three lines. Note the FQSaveToCache field, set to TRUE except on the last line.

INSERT INTO "PurchaseOrderLine" ("VendorRefListID", "RefNumber",
"PurchaseOrderLineItemRefListID", "PurchaseOrderLineDesc",
"PurchaseOrderLineQuantity", "PurchaseOrderLineRate",
"PurchaseOrderLineAmount", "PurchaseOrderLineCustomerRefListID",
"FQSaveToCache") VALUES ('10000-933272655', '1', '250000-933272656', 'See Attached 1', 1.0, 1.0, 1.11, '580000-1071526281', 1)

INSERT INTO "PurchaseOrderLine" ("VendorRefListID", "RefNumber",
"PurchaseOrderLineItemRefListID", "PurchaseOrderLineDesc",
"PurchaseOrderLineQuantity", "PurchaseOrderLineRate",
"PurchaseOrderLineAmount", "PurchaseOrderLineCustomerRefListID",
"FQSaveToCache") VALUES ('10000-933272655', '1', '250000-933272656', 'See Attached 2', 2.0, 2.0, 2.22, '580000-1071526281', 1)

INSERT INTO "PurchaseOrderLine" ("VendorRefListID", "RefNumber",
"PurchaseOrderLineItemRefListID", "PurchaseOrderLineDesc",
"PurchaseOrderLineQuantity", "PurchaseOrderLineRate",
"PurchaseOrderLineAmount", "PurchaseOrderLineCustomerRefListID",
"FQSaveToCache") VALUES ('10000-933272655', '1', '250000-933272656', 'See Attached 3', 3.0, 3.0, 3.33, '580000-1071526281', 0)

Example with header information in PurchaseOrder table insert

Note: This example creates one Purchase Order with three lines. Note the FQSaveToCache field, set to True. Saving the Purchase Order header saves the lines with it.

INSERT INTO "PurchaseOrderLine" ("PurchaseOrderLineItemRefListID",
"PurchaseOrderLineDesc", "PurchaseOrderLineQuantity",
"PurchaseOrderLineRate", "PurchaseOrderLineAmount",
"PurchaseOrderLineCustomerRefListID", "FQSaveToCache") VALUES
('250000-933272656', 'See Attached 1', 1.0, 1.0, 1.11, '580000-1071526281', 1)

INSERT INTO "PurchaseOrderLine" ("PurchaseOrderLineItemRefListID",
"PurchaseOrderLineDesc", "PurchaseOrderLineQuantity",
"PurchaseOrderLineRate", "PurchaseOrderLineAmount",
"PurchaseOrderLineCustomerRefListID", "FQSaveToCache") VALUES
('250000-933272656', 'See Attached 2', 2.0, 2.0, 2.22, '580000-1071526281', 1)

INSERT INTO "PurchaseOrderLine" ("PurchaseOrderLineItemRefListID",
"PurchaseOrderLineDesc", "PurchaseOrderLineQuantity",
"PurchaseOrderLineRate", "PurchaseOrderLineAmount",
"PurchaseOrderLineCustomerRefListID", "FQSaveToCache") VALUES
('250000-933272656', 'See Attached 3', 3.0, 3.0, 3.33, '580000-1071526281', 1)

INSERT INTO "PurchaseOrder" ("VendorRefListID", "RefNumber", "Memo",
"IsToBePrinted") VALUES ('10000-933272655', '1', 'Memo Test', 0)

 

Create Lines in Journal Table

Example of creating multiple lines in Journal

Note: This example creates one Journal Entry with two credit lines and two debit lines. Note the FQSaveToCache field, set to TRUE except on the last line. Also, when saving Journal Entries, QuickBooks will reject transaction which does not balance the Credit and Debit sides.

INSERT INTO "JournalEntryCreditLine" ("RefNumber",
"JournalCreditLineAccountRefListID", "JournalCreditLineAmount",
"JournalCreditLineMemo", "FQSaveToCache") VALUES ('1', '120000-933270541', 1.11, 'Test Memo 1', 1)

INSERT INTO "JournalEntryCreditLine" ("RefNumber",
"JournalCreditLineAccountRefListID", "JournalCreditLineAmount",
"JournalCreditLineMemo", "FQSaveToCache") VALUES ('1', '120000-933270542', 2.22, 'Test Memo 2', 1)

INSERT INTO "JournalEntryDebitLine" ("RefNumber",
"JournalDebitLineAccountRefListID", "JournalDebitLineAmount",
"JournalDebitLineMemo", "FQSaveToCache") VALUES ('1', '120000-933270543',
1.11, 'Test Memo 1', 1)

INSERT INTO "JournalEntryDebitLine" ("RefNumber",
"JournalDebitLineAccountRefListID", "JournalDebitLineAmount",
"JournalDebitLineMemo", "FQSaveToCache") VALUES ('1', '120000-933270544',
2.22, 'Test Memo 2', 0)

 

A VBScript Example for creating multiple lines

      If you prefer to use VBScript instead of SQL commands, the following example may be helpful to you.

How to execute the script

      Save the text below between the <<>> markers and save it as the filename "InvoiceAdd.vbs" then, you can click on the script while QuickBooks is running, and it will work. Similar code will be used inside Visual Basic language compilers.

A VBScript Sample for creating multi-line Invoices

<<>>
'This creates one invoice with three lines.
'Note the FQSaveToCache field, set to TRUE except on the last line.

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3

Dim oConnection
Dim oRecordset
Dim sLastVendor
Dim dTotalApplied
Dim dAmountDue

Set oConnection = CreateObject("ADODB.Connection")
Set oRecordset = CreateObject("ADODB.Recordset")

oConnection.Open "DSN=Quickbooks Data;OLE DB Services=-2;"
oRecordset.CursorLocation = adUseClient
oRecordset.Open "SELECT * FROM InvoiceLine WHERE TxnId = 'X'" ,
oConnection, adOpenStatic, adLockOptimistic

oRecordset.AddNew()
oRecordset.Fields("RefNumber").Value = "1"
oRecordset.Fields("CustomerRefListID").Value = "470001-1071525403"
oRecordset.Fields("InvoiceLineItemRefListID").Value = "250000-933272656"
oRecordset.Fields("InvoiceLineDesc").Value = "Building permit 1"
oRecordset.Fields("InvoiceLineRate").Value = 1
oRecordset.Fields("InvoiceLineAmount").Value = 1
oRecordset.Fields("InvoiceLineSalesTaxCodeRefListID").Value = "2 0000-999022286"
oRecordset.Fields("FQSaveToCache").Value = True
oRecordset.Update()

oRecordset.AddNew()
oRecordset.Fields("RefNumber").Value = "1"
oRecordset.Fields("CustomerRefListID").Value = "470001-1071525403"
oRecordset.Fields("InvoiceLineItemRefListID").Value = "250000-933272656"
oRecordset.Fields("InvoiceLineDesc").Value = "Building permit 2"
oRecordset.Fields("InvoiceLineRate").Value = 2
oRecordset.Fields("InvoiceLineAmount").Value = 2
oRecordset.Fields("InvoiceLineSalesTaxCodeRefListID").Value = "20000-999022286"
oRecordset.Fields("FQSaveToCache").Value = True
oRecordset.Update()

oRecordset.AddNew()
oRecordset.Fields("RefNumber").Value = "1"
oRecordset.Fields("CustomerRefListID").Value = "470001-1071525403"
oRecordset.Fields("InvoiceLineItemRefListID").Value = "250000-933272656"
oRecordset.Fields("InvoiceLineDesc").Value = "Building permit 3"
oRecordset.Fields("InvoiceLineRate").Value = 3
oRecordset.Fields("InvoiceLineAmount").Value = 3
oRecordset.Fields("InvoiceLineSalesTaxCodeRefListID").Value = "20000-999022286"
oRecordset.Fields("FQSaveToCache").Value = False
oRecordset.Update()

oRecordset.Close
oConnection.Close
<<>>


(194 vote(s))
Helpful
Not helpful

Comments (2)
Don Short
11 March 2015 05:18 PM
Please correct me if I'm wrong but i I wanted to update an individual InvoiceLine where, say 5 exist under the same Invoice Header, would I would need to include/reference all 5 in the update? My understanding is those not included would be deleted. Again if I am wrong ... If so, would I use the FQSaveToCache set to 1 for the first 4 updates, with the last one set to zero. Thanks.
Jack
12 March 2015 08:43 AM
Hi Don,

You can update individual InvoiceLine by using InvoiceLineTxnLineID. In InvoiceLine table InvoiceLineTxnLineID column will identify single row. InvoiceLineTxnLineID contains unique ID for line items using this column you can update single line item.

For Exmaple:

UPDATE InvoiceLine SET InvoiceLineQuantity = 12 WHERE RefNumber='123' and InvoiceLineTxnLineID='2E72-1071523805'

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

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
2) Screenshot of the issue you’re facing.
Share Entire Log Files as an attachment in text format from
3) QODBC Setup Screen -- > Messages -- > Review QODBC Messages
4) 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).