Member Login

Username
Password
Forget Password
New Sign Up
Search Forum

Buy Support
Incidents

If you can't find your answer in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us private or confidential information:

Click Here
If you can't login and post questions or you are having trouble viewing forum posts:
 Click Here
Callback
Support

If you live in USA, UK, Canada, Australia or New Zealand, you can leave us details on your question and request us to call you back and discuss them with you personally  (charges apply).

Click Here
 
Buy Support
Incidents
If you can't find your answer in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us private or confidential information:
Click Here

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

Forum Home > QODBC - ODBC Driver for QuickBooks > QODBC SQL Sample Scripts Forum

 New Topic 
 
 Post Reply 
[1]  
 How can I create multiple lines in the Invoice, Purchase, Journal and other tables? 
 Author   Message 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2006-02-25 20:39:40

The answer varies on your situation. Below are some examples of various types of updates. 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')

The same process above is used for Purchase Orders:
This example creates a 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')

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)

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)

This example creates one Journal Entry with 2 credit lines and 2 debit lines. Note the FQSaveToCache field, set to TRUE except on the last line. Also, when saving Journal Entries, QuickBooks will reject transaction which do 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-933270541', 2.22, 'Test Memo 2', 1)

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

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

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

Save the text below between the <<>> markers and save 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.

<<>>
'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 = "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 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
<<>>

 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2006-02-25 20:40:41
Sample Tutorial 

"How do I create a multi-line invoice using QODBC with a blank line between two items for a job and specify what invoice format I would like to use?"

Step 1 - Find the Template ListID
When creating an invoice you can select which Invoice Template to use by including a "TemplateRefListID" column in the InvoiceLine insert statement. To determine what template ListIDs are available to you, run the following query in VBDemo. With your QuickBooks company file open, click on "Start" and "Programs" and locate QODBC Driver for QuickBooks program group. Click on the VB Demo icon to launch the Visual Basic Demo program supplied with QODBC. To establish a connection, from the menu bar select "Connections" / "Add a new connection", select the "Quickbooks Data" DSN, Click "OK” and enter the following query:

SELECT * FROM Template

Click on the Query button. In my case my QuickBooks 2004 sample file returned the following templates:

To use the Stadium Tax invoice template we use the ListID:'A0000-1045701421' (in Row 9 above), like this in InvoiceLine SQL insert statements:

INSERT INTO "InvoiceLine" ("CustomerRefListID", "TemplateRefListID","RefNumber", "InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate", "InvoiceLineAmount", "InvoiceLineTaxCodeRefListID","FQSaveToCache") VALUES ('300000-1081400329','A0000-1045701421','56', '90000-1045537150', '4m Steel Ladder', 1.00000, 150.00, '90000-1045536338', 0)

You can substitute the TemplateRefListID with the ListID value of whatever Invoice Template type you would like to use.

Step 2 - Find the Customer Job ListID
In QuickBooks, job invoices are created by invoicing a customer followed by the job name or number. For this example, I created job number 1000 for Acmer Pty Ltd. In QuickBooks jobs are separated by a ":", and I would use "Acmer Pty Ltd:1000" to raise invoices against the job 1000. What you need to do is find the "CustomerRefListID" for the job you want to create an invoice for by running the following query in VB Demo:

SELECT * FROM Customer

In my case, my QuickBooks 2004 sample file returned the following customers:

I'm interested in Job: "Acmer Pty Ltd:1000". For Job 1000 we use the ListID:'300000-1081400329' (in Row 2 above), like this in InvoiceLine SQL insert statements:

INSERT INTO "InvoiceLine" ("CustomerRefListID", "TemplateRefListID","RefNumber", "InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate", "InvoiceLineAmount", "InvoiceLineTaxCodeRefListID","FQSaveToCache") VALUES ('300000-1081400329','A0000-1045701421','56', '90000-1045537150', '4m Steel Ladder', 1.00000, 150.00, '90000-1045536338', 0)

Step 3 - Create the Job Invoice
Now that we know "TemplateRefListID" for Invoice Template we want to use and the "CustomerRefListID" for the job we want to create an invoice for, it's time to create the actual multi-line invoice by running three SQL insert statements. What we also need to do is find the "InvoiceLineItemRefListID" for all the items we want to invoice against the job by running the following query in VB Demo:

SELECT ListID, FullName, Description, Type FROM Item

In my case, my QuickBooks 2004 sample file returned the following items:

I'm interested in the following Inventory Items:
"4m Steel Ladder" ListID:'90000-1045537150' (in Row 17 above) and
"Paint Brush:Big" ListID:'150001-1045625669' (in Row 25 above)

Multi-line invoices require a series of SQL statements to complete a single invoice. In the example below we create a 3 line job invoice using three 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 (true) for the first two line item insert statements, and then it is set to 0 (false) for the final statement.

A true (1) 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 (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.

To create a job invoice, with a "EMPTY" InvoiceLine line between two invoice lines, run each of these insert statements in VB Demo one after each other without disconnecting like this:

1st SQL Statement
INSERT INTO "InvoiceLine" ("CustomerRefListID", "TemplateRefListID","RefNumber", "InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate", "InvoiceLineAmount", "InvoiceLineTaxCodeRefListID","FQSaveToCache") VALUES ('300000-1081400329','A0000-1045701421','56', '90000-1045537150', '4m Steel Ladder', 1.00000, 150.00, '90000-1045536338', 1)

2nd SQL Statement
INSERT INTO "InvoiceLine" ("InvoiceLineDesc", "FQSaveToCache") VALUES ('', 1)

3rd SQL Statement
INSERT INTO "InvoiceLine" ("CustomerRefListID", "TemplateRefListID","RefNumber", "InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate", "InvoiceLineAmount", "InvoiceLineTaxCodeRefListID","FQSaveToCache") VALUES ('300000-1081400329','A0000-1045701421','56', '150001-1045625669', 'Paint Brush:Big', 1.00000, 11.60, '90000-1045536338', 0)

Step 4 - View the Job Invoice in QuickBooks
After running each of the insert scripts in Step 3, when I looked up Tax Invoice# 56 in QuickBooks I found a invoice for the job "1000" with two line items with a space between them just like the user wanted!!:

Further examples of QODBC SQL Scripts for QuickBooks Transactions may be found with these FAQs. In order to create QuickBooks transactions using QODBC you must have either a 30 Day QODBC Evaluation or a QODBC Pro Read Write license.

IMPORTANT NOTE: InvoiceLineTaxCodeRefListID should be used outside USA only. In USA use InvoiceLineSalesTaxCodeRefListID instead.

The examples and screen dumps shown here are from the Australian edition of QuickBooks. USA, Canadian and UK versions of QuickBooks do differ in appearance and operation, however, the technique shown still applies.

 

  Top 
 New Topic 
 
 Post Reply 
[1]  

Jump to