|
[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 TableExample of creating one line item InvoiceIf 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", 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 InvoicesMulti-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 invoiceExample with header information in each line item insert INSERT INTO "InvoiceLine" ("CustomerRefListID", "RefNumber", INSERT INTO "InvoiceLine" ("CustomerRefListID", "RefNumber", INSERT INTO "InvoiceLine" ("CustomerRefListID", "RefNumber", 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", INSERT INTO "InvoiceLine" ("InvoiceLineItemRefListID", "InvoiceLineDesc", INSERT INTO "InvoiceLine" ("InvoiceLineItemRefListID", "InvoiceLineDesc", INSERT INTO "Invoice" ("CustomerRefListID", "ARAccountRefListID",
Create Lines in Purchase Order TableExample of creating one line Purchase OrderNote: the FQSaveToCache field is not specified (or can be set to false) INSERT INTO "PurchaseOrderLine" ("VendorRefListID", "RefNumber", Example of creating Multi-line Purchase OrdersExample 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", INSERT INTO "PurchaseOrderLine" ("VendorRefListID", "RefNumber", INSERT INTO "PurchaseOrderLine" ("VendorRefListID", "RefNumber", 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", INSERT INTO "PurchaseOrderLine" ("PurchaseOrderLineItemRefListID", INSERT INTO "PurchaseOrderLine" ("PurchaseOrderLineItemRefListID", INSERT INTO "PurchaseOrder" ("VendorRefListID", "RefNumber", "Memo",
Create Lines in Journal TableExample of creating multiple lines in JournalNote: 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", INSERT INTO "JournalEntryCreditLine" ("RefNumber", INSERT INTO "JournalEntryDebitLine" ("RefNumber", INSERT INTO "JournalEntryDebitLine" ("RefNumber",
A VBScript Example for creating multiple linesIf you prefer to use VBScript instead of SQL commands, the following example may be helpful to you. How to execute the scriptSave 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<<>> Const adOpenStatic = 3 Dim oConnection Set oConnection = CreateObject("ADODB.Connection") oConnection.Open "DSN=Quickbooks Data;OLE DB Services=-2;" oRecordset.AddNew() oRecordset.AddNew() oRecordset.AddNew() oRecordset.Close
| |
 (194 vote(s)) Helpful Not helpful |
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