Hmm, thought that the reference was easy enough but I cannot get what should be a simple thing...

In the following table: InvoiceLine

It is said (what I think is being said) that fields CustomerRefListID & CustomerRefFullName are required to be explicitly filled when doing insert operation. However, going through a little tutorial on how to add new invoices from: How do I create Invoices? 

... it is evident from the examples that fields CustomerRefListID & CustomerRefFullName are not given any value when InvoiceLine table gets new inserts.

Therefore, am I reading the documentation incorrectly? Going from their table of fields, how do I decide what is the most minimum fields should be given explicit value when inserting, say into InvoiceLine.

Thanks.

PS: Going back to the new invoice insert example, should one call SP_BATCHUPDATE after the queries in the example? Is it for fields with FQSaveToCache value or it plays a different role?

As per: How can I create multiple lines in the Invoice, Purchase, Journal and other tables? 

The answer really is that either the CustomerRefListID or CustomerRefFullName must be supplied to complete the Invoice (not both). Below are some examples of creating a Invoice. The primary rule is to save the data to the child record first. The child record for each parent/child pair has all of the data required by the parent record included in it.

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')

SP_BATCHUPDATE is only used if we have hundreds of invoices to create as it saves waiting on QuickBooks for each Invoice to be created.

QODBC Batch Stored Procedures

These allow you to start a batch for a given table and all insert/updates will be queued until the sp_batchupdate command is issued. This allows for fewer round trips to QuickBooks which increases performance when doing large transfers of records from external systems to QuickBooks.

The sp_lastinsertid stored procedure will return the ListID/TxnID plus an error message column for every row added to the batch.

Note: Each batch is limited to 500 transactions.

• sp_batchclear tablename – clears the current batch started with sp_batchstart.
• sp_batchstart tablename – starts a new batch. All inserts/update issued on this table will be batched until sp_batchupdate is issued.
• sp_batchupdate tablename – sends the batched transactions to QuickBooks. ListID/TxnIDs and error messages are available through sp_lastinsertid tablename.
  

BatchStart
sp_batchstart InvoiceLine

BatchInsert1
INSERT INTO "InvoiceLine" ("CustomerRefListID", "RefNumber",
"InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate",
"InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID", "FQSaveToCache")
VALUES ('AC0000-1197757899', 'Batch1', '250000-933272656', 'Bin Permit Renovations',
200.00000, 200.00, '20000-999022286', 0) 

..................................................................

BatchInsert499
INSERT INTO "InvoiceLine" ("CustomerRefListID", "RefNumber",
"InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate",
"InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID", "FQSaveToCache")
VALUES ('AC0000-1197757899', 'Batch2', '250000-933272656', 'Bin Permit Renovations',
200.00000, 200.00, '20000-999022286', 0) 

BatchUpdate
sp_batchupdate  InvoiceLine

See: How do I invoice item groups? 
         Can I Change the pricing and quantity of Items in a groupitems 
         How do I create Item Groups(ItemGroup)? 

To answer your question, you cannot currently change the price or quantity of individual item group lines (or add new items within the group) within an invoice using QODBC. If you want to do that, you should read the itemgroup lines as a template only and write each item seperately on seperate invoice lines within the invoice. Doing it that way lets you make any changes to the invoice lines you want later on as per:
        Can't add new InvoiceLine to existing Invoice 
         Invoice Table Updates 

You should read the itemgroup lines as a template only and write each item seperately on seperate invoice lines within the invoice. Doing it that way lets you make any changes to the invoice lines you want later using QODBC because you can't change invoices with ItemGroups using QODBC.

Another way would be to create your own QODBC use only itemgroup that you would only use and change for every invoice. 

Yes, QuickBooks (not QODBC) allows the same RefNumber to be used!!

To delete a invoice you would do:

DELETE FROM Invoice WHERE TxnID = 'xxxxxxxxxxx'

You can also use SP_VOID just like you would use DELETE. It will void the invoice instead of deleting it.

Example:
SP_VOID FROM Invoice WHERE TxnID = 'xxxxxxxxxxxx'

To delete just a InvoiceLine see: How do I delete a Invoice Line in the InvoiceLine table?