• Product
• Overview
• Getting started
• Release notes
• Supported platforms
• Knowledge Base
• Download

• Documentation
• User Guide
• Licensing Guide

• How tos
• Access
• Excel
• Oracle
• Perl
• SQL Server

QuickBooks Online ODBC driver release notes

QuickBooks Online tables

Tables are classed as either a parent table (P) or a line table (L). Line tables always have a parent because they are in effect sub-records within a master (parent) record.

For example, an Invoice record may contain several sales lines representing items that make up the invoice. Parent table records just hold the record header columns of the record. Line table records always include the header columns of the parent record to which it is a part, as well as the line columns for a particular line entry.

Typically, parent tables have a primary key (PK) column of either ListID or TxnID and line tables have a PK column of ID, which is made up from one of more columns including the ListID or TxnID.

The Items line tables are slightly different (apart from ItemGroupLine) in so much as records are differentiated by a type column and are all separate records and therefore only use the ListID as the PK.

Only parent tables are visible through the xCache table, which stores the ID (either ListID or TxnID) of the last record inserted or updated in that table.

The Account table

An Account object is used to record a total monetary amount allocated against a specific use.

Accounts are one of five basic types: Asset, Liability, Revenue (income), Expenses, or Equity.

To "delete" an account, set the Active attribute to false in a SQL UPDATE statement. The makes the account inactive. References to inactive objects are left intact. By setting the Active column to false, a record is not permanently deleted, it is just marked as inactive.

Examples

Example query:

SELECT TOP 10 ListID Name Active from Account

The following statement contains the minimum set of columns required to create a new Account record:

INSERT INTO Account (Name, AccountType) VALUES ('Sales - retail', 'Income')

Account records can be nested using the ParentRef_Id column.

INSERT INTO  Account
(Name, SubAccount, ParentRef_Id, FullName, Active, Classification, AccountType, AccountSubType, CurrencyRef_Id, CurrencyRef_Name )
VALUES
('Oily One', 1, '55', 'Sales - retail:Oily One', 1, 'Revenue', 'Income', 'OtherPrimaryIncome', 'AUD', 'Australian Dollar' )

INSERT INTO  Account
(Name, SubAccount, ParentRef_Id, FullName, Active, Classification, AccountType, AccountSubType, CurrencyRef_Id, CurrencyRef_Name )
VALUES
('Oily Two', 1, '55', 'Sales - retail:Oily Two', 1, 'Revenue', 'Income', 'OtherPrimaryIncome', 'AUD', 'Australian Dollar' )

Direct DELETEs are not allowed. This is the case with many QuickBooks Online tables.

Instead, you need to set the Account's Active to false. First, find the Account you want to "delete:"

SELECT ListID, Name, Active FROM Account

Set the Account record to inactive:

UPDATE Account SET Active=false WHERE ListID='136'

The Attachable table

Attachments are supplemental information linked to a Transaction or Item object. They can be files, notes, or a combination of both.

There are two tables used to support this feature: Attachable and AttachableReferences.

Examples

Attachable records do not necessarily have to have associated reference lines.

This INSERT statement creates an Attachable record without a reference line:

INSERT INTO Attachable 
(Lat, Long, Note, Tag, PlaceName)
VALUES
('25.293112341777', '-21.3253249777', 'Note 1', 'Tag Test 1', 'Place 1')

This example creates an Attachable record with one reference line:

INSERT INTO AttachableReferences
(Lat, "Long", Note, Tag, PlaceName, AttRef_EntityRef_Id, AttRef_EntityRef_Type) 
VALUES 
('25.293112341777', '-21.3253249777', 'Note 2', 'Tag Test 1', 'Place 1', '174', 'Invoice')

Note the direct INSERT into the AttachableReferences table with parent columns and line columns.

Multiple reference lines can be done in two ways:

• As a transaction using AUTOCOMMIT OFF and the RQUseCachedID column
• As separate inserts with AUTOCOMMIT ON using the primary key of the parent record.

In this example, we create four AttachableReference records with a reference to two estimates and two invoices, retrieved from the Estimates and Invoice tables.

The TxnIds for the estimates are 119 and 120.

The TxnIds for the Invoices are 127 and 129.

INSERT examples

If your application does not allow you to set autocommit mode for an ODBC-based connection, refer to this example:

INSERT INTO Attachable (Lat, "Long", Note, Tag, PlaceName) VALUES ('25.293112341888', '-21.3253249888', 'Note 3X', 'Tag Test 3Y', 'Place 3Z')
INSERT INTO AttachableReferences (AttRef_EntityRef_Id, AttRef_EntityRef_Type, RQUseCachedID) VALUES ('119', 'Estimate', 1)
INSERT INTO AttachableReferences (AttRef_EntityRef_Id, AttRef_EntityRef_Type, RQUseCachedID) VALUES ('127', 'Invoice', 1)
INSERT INTO AttachableReferences (AttRef_EntityRef_Id, AttRef_EntityRef_Type, RQUseCachedID) VALUES ('120', 'Estimate', 1)
INSERT INTO AttachableReferences (AttRef_EntityRef_Id, AttRef_EntityRef_Type, RQUseCachedID) VALUES ('129', 'Invoice', 1)

Here the ListID is cached in the xCache table following the creation of the parent record.

Each subsequent INSERT INTO AttachableReferences statement retrieves the record whose ID is cached in the xCache table and adds a new reference line to it before updating the record.

To add a new reference line at a later date, where the ID of the parent record is no longer cached in the xCache table, you can use the ListID of the parent record directly. For example:

INSERT INTO AttachableReferences
(ListID, SyncToken, AttRef_EntityRef_Id, AttRef_EntityRef_Type) 
VALUES 
('5100000000000046004','11', '130', 'Invoice')

SELECT * FROM AttachableReferences WHERE ListID '5100000000000046004'

If your application allows you to set autocommit mode for an ODBC-based connection, refer to this example:

autocommit off
INSERT INTO Attachable (Lat, "Long", Note, Tag, PlaceName) VALUES ('25.293112341888', '-21.3253249888', 'Note 3X', 'Tag Test 3Y', 'Place 3Z')
INSERT INTO AttachableReferences (AttRef_EntityRef_Id, AttRef_EntityRef_Type, RQUseCachedID) VALUES ('119', 'Estimate', 1)
INSERT INTO AttachableReferences (AttRef_EntityRef_Id, AttRef_EntityRef_Type, RQUseCachedID) VALUES ('127', 'Invoice', 1)
INSERT INTO AttachableReferences (AttRef_EntityRef_Id, AttRef_EntityRef_Type, RQUseCachedID) VALUES ('120', 'Estimate', 1)
INSERT INTO AttachableReferences (AttRef_EntityRef_Id, AttRef_EntityRef_Type, RQUseCachedID) VALUES ('129', 'Invoice', 1)
end transaction

Here, multiple INSERTs are committed in a single INSERT statement to create the record. This is the most efficient way of creating a record with multiple lines.

Note the use of the RQUseCachedId column in the AttachableReferences INSERTs. When used within a transaction with AUTOCOMMIT OFF, the Easysoft QuickBooks Online ODBC driver associates the AttachableReference with the current parent record, which in this case is the record that will result from the initial INSERT INTO statement.

UPDATE example

Updating an Attachable record directly:

UPDATE Attachable SET Note='Note Updated 1' WHERE ListID='5100000000000046004'

The most efficient method to update an AttachableReference is always to use the ID. For example:

UPDATE AttachableReferences SET AttRef_EntityRef_ID='123' WHERE ID='5100000000000046004|Invoice|130'

The Bill table

A Bill object is a transaction representing a request-for-payment from a third party for goods and services rendered, received, or both.

There are four tables used to support and represent the bill data: Bill, BillExpenseLine, BillLinkedTxn, and BillTaxDetail.

For each Bill record, there can be one or more optional BillExpense, BillLinkedTxn, or BillTaxDetail lines.

As a minimum, one BillExpenseLine is needed.

BillLinkedTxn table

BillTaxDetail table

Examples

The current Bill record is either the last record that was either inserted into or updated in the Bill table or the one currently being constructed within a transaction.

Suppose you need to create a Bill with one Expense line. This can be done in one of two ways, either directly (AUTOCOMMIT ON) or by using a transaction (AUTOCOMMIT OFF).

With AUTOCOMMIT ON, run an INSERT INTO BillExpenseLine statement that includes both the BillHeader and BillExpenseLine columns. RQUseCachedID is not relevant here and should not be used.

With AUTOCOMMIT OFF, you can split the INSERT into two separate INSERTs with the header record first (for example, INSERT INTO Bill ...), followed by an INSERT INTO BillExpenseLine with just the ExpenseLine columns. Include RQUseCachedId=1 to indicate that this line is for the current record, which in this case is the one being constructed by using a transaction.

The second method allows for several lines to be added at the same time the record is created.

You can then commit the transaction using END TRANSACTION.

With either method, the ID of the record created (in this case the TxnID) is stored in the xCache table and can be retrieved using SELECT ID FROM xCache WHERE TableName='Bill'. Note the xCache table is memory-based and values do not persist across sessions.

Once the Bill exists, you can add further lines by using the TxnID of the Bill directly, or if the xCache is still current, use RqUseCachedID=1 to indicate that the line applies to the Bill record whose TxnID is saved in the xCache.

INSERT examples

If your application does not allow you to set autocommit mode for an ODBC-based connection, refer to this example:

INSERT INTO BillExpenseLine
(CurrencyRef_Id, CurrencyRef_Name, ExchangeRate, VendorRef_Id, VendorRef_Name, APAccountRef_Id, APAccountRef_Name, TotalAmt, TotalTax, GlobalTaxCalculation, SalesTermRef_Id, DueDate, Balance, HomeBalance, 
 Line_Id, Line_Description, Line_Amount, Line_DetailType, Line_AcctExpense_AccountRef_Id, Line_AcctExpense_AccountRef_Name, Line_AcctExpense_BillableStatus, Line_AcctExpense_TaxCodeRef_Id )
VALUES
('AUD', 'Australian Dollar', 1, '31', 'City Water Co', '92', 'Accounts Payable (A/P)', 280.92, 9.38, 'TaxInclusive', '2', '2017-02-28', 280.92, 280.92,
 '1', 'Monthly water service fee.', '93.77', 'AccountBasedExpenseLineDetail', '84', 'Utilities - Water', 'NotBillable', '2')

If your application allows you to set autocommit mode for an ODBC-based connection, refer to this example:

autocommit off

INSERT INTO Bill
(CurrencyRef_Id, CurrencyRef_Name, ExchangeRate, VendorRef_Id, VendorRef_Name, APAccountRef_Id, APAccountRef_Name, TotalAmt, TotalTax, GlobalTaxCalculation, SalesTermRef_Id, DueDate, Balance, HomeBalance)
VALUES
('AUD', 'Australian Dollar', 1, '31', 'City Water Co', '92', 'Accounts Payable (A/P)', 213.15, 19.38, 'TaxInclusive', '2', '2017-02-28', 213.15, 'HomeBalance')

INSERT INTO BillExpenseLine
(Line_Id, Line_Description, Line_Amount, Line_DetailType, Line_AcctExpense_AccountRef_Id, Line_AcctExpense_AccountRef_Name, Line_AcctExpense_BillableStatus, Line_AcctExpense_TaxCodeRef_Id, RQUseCachedId)
VALUES
('1', 'Monthly water service fee.', '193.77', 'AccountBasedExpenseLineDetail', '84', 'Utilities - Water', 'NotBillable', '2', 1)

INSERT INTO BillTaxDetail
(TaxLine_Amount, TaxLine_DetailType, TaxLine_Detail_TaxRateRef_Id, TaxLine_Detail_PercentBased, TaxLine_Detail_TaxPercent, TaxLine_Detail_NetAmountTaxable, RQUseCachedId)
VALUES
(19.38, 'TaxLineDetail', '1', true, 10, 193.77, 1)
   
end transaction

These are cached and executed as a single transaction.

UPDATE example

UPDATE BillExpenseLine SET Line_Amount='88.88' WHERE ID='259|4'

The BillPayment table

A BillPayment object represents the payment transaction for a bill that the business owner receives from a vendor for goods or services purchased from the vendor.

QuickBooks Online supports bill payments through a credit card or a checking account.

BillPayment > TotalAmt is the total amount associated with this payment.

This includes the total of all the payments from the payment line details.

If TotalAmt is greater than the total on the lines being paid, the overpayment is treated as a credit and exposed as such on the QuickBooks user interface.

The total amount cannot be negative.

Examples

Adds a payment to an existing BillPayment:

INSERT INTO BillPaymentLinkedTxn
(TxnID, Line_Amount, Line_LinkedTxn_TxnID, Line_LinkedTxn_TxnType)
VALUES
('274', 100.00, '151', 'Bill')

To make another payment to this bill:

INSERT INTO BillPaymentLinkedTxn
(TxnID, Line_Amount, Line_LinkedTxn_TxnID, Line_LinkedTxn_TxnType)
VALUES
('274', 125.00, '151', 'Bill')

The Budget table

The Budget table allows you to retrieve the current state of budgets already set up in the user's company file.

A budget allows for an amount to be assigned on a monthly, quarterly, or annual basis for a specific account or customer. A budget is created to give a business measurable expense goal.

This amount represents how much should be spent against that account or customer in a given time period.

The CompanyInfo table

The CompanyInfo object contains basic company information.

In QuickBooks, company information and preferences are displayed in the same place under Preferences, so it may be confusing to figure out from the user interface which fields belong to this object. In general, properties such as company addresses or name are considered company information. Some attributes may exist in both CompanyInfo and Preferences tables.

Example

UPDATE CompanyInfo SET CompanyAddr_Line1='12345A Sierra Way', CompanyAddr_PostalCode='88000'

CompanyCurrency table

Applicable only for those companies that turn on multicurrency, a CompanyCurrency record defines a currency that is active in the QuickBooks Online company.

The Class table

Class table records provide a way to track different segments of the business so they are not tied to a particular client or project.

For example, you can define classes to break down the income and expenses for each business segment.

Classes are available to the entire transaction or to individual detail lines of a transaction.

The CreditMemo table

The CreditMemo table is a financial transaction representing a refund, credit, or part of a payment for goods or services that have been sold.

A CreditMemo record consists of Header section and one or more lines.

Lines are individual line items of a transaction. Lines types are: Sales Items, Group Lines, DescriptionOnly Lines, Discount Lines, SubTotal Lines, or TaxDetail Lines.

Item Groups also have sublines that represent individual Items, which make up the Item Group.

Lines are stored in the following tables:

Notes

Examples

UPDATE CreditMemo SET PrivateNote='Private Note' WHERE TxnID='381'

CreditMemoDescriptionLine table

Notes

Examples

An INSERT into the CreditMemoDescriptionLine table either creates a new CreditMemo record with a description line or adds a description line to an existing record.

If your application does not allow you to set autocommit mode for an ODBC-based connection, refer to this example:

SELECT * FROM xCache WHERE TableName='CreditMemo'

Row TableName  IDType ID  SyncToken
1   CreditMemo TxnID  379 0

SELECT CustomerRef_Id, CustomerRef_Name, Line_Description, Line_DetailType FROM CreditMemoDescriptionLine WHERE TxnID IN (SELECT ID FROM xCache WHERE TableName='CreditMemo')

Row CustomerRef_Id CustomerRef_Name Line_Description Line_DetailType
1   3              Cool Cars        Description      DescriptionOnly

INSERT INTO CreditMemoDescriptionLine (CustomerRef_Id, CustomerRef_Name, Line_Description, Line_DetailType) VALUES ('3', 'Cool Cars', 'Non Transaction Insert', 'DescriptionOnly')

SELECT ID, TxnId, CustomerRef_Id, CustomerRef_Name, Line_Id, Line_Description, Line_DetailType FROM CreditMemoDescriptionLine WHERE TxnID IN (SELECT ID FROM xCache WHERE TableName='CreditMemo')

Row ID    TxnID CustomerRef_Id CustomerRef_Name Line_Id Line_Description       Line_DetailType
1   381|1 381   3              Cool Cars        1       Non Transaction Insert DescriptionOnly

Add an additional Description Line to an existing CreditMemo.

INSERT INTO CreditMemoDescriptionLine (TxnID, Line_Description, Line_DetailType) VALUES ('381', 'Additional Insert', 'DescriptionOnly')

SELECT ID, TxnId, CustomerRef_Id, CustomerRef_Name, Line_Id, Line_Description, Line_DetailType FROM CreditMemoDescriptionLine WHERE TxnID='381'

Row ID    TxnID CustomerRef_Id CustomerRef_Name Line_Id Line_Description       Line_DetailType
1   381|1 381   3              Cool Cars        1       Non Transaction Insert DescriptionOnly
2   381|2 381   3              Cool Cars        2       Additional Insert      DescriptionOnly

If your application allows you to set autocommit mode for an ODBC-based connection, refer to this example:

autocommit off

INSERT INTO CreditMemo
(CustomerRef_Id, CustomerRef_Name)
VALUES
('3', 'Cool Cars')

INSERT INTO CreditMemoDescriptionLine  
(Line_Description, Line_DetailType, RQUseCachedID)
VALUES
('Description', 'DescriptionOnly', 1)

end transaction

CreditMemoDiscountLine table

Notes

Examples

This INSERT statement creates a new record:

INSERT INTO CreditmemoDiscountLine 
(CustomerRef_Id, CustomerRef_Name, Line_Amount, Line_DetailType, Line_PercentBased, Line_DiscountPercent, Line_TaxCodeRef_Id)
VALUES
('3', 'Cool Cars', 10.00, 'DiscountLineDetail', true, 5, 'NON')

This INSERT statement updates an existing record:

INSERT INTO CreditmemoDiscountLine
(TxnID, Line_Amount, Line_DetailType, Line_Detail_PercentBased, Line_Detail_DiscountPercent, Line_Detail_TaxCodeRef_Id)
VALUES
('324', 10.00, 'DiscountLineDetail', true, 5, 'NON')

CreditMemoItemGroup table

Notes

CreditMemoItemGroupLine table

Notes

CreditMemoSalesLine table

Notes

CreditMemoSubtotalLine table

Notes

CreditMemoTaxDetail table

Notes

Customer table

A customer is a consumer of the service or product that your business offers.

An individual customer can have an underlying nested structure, with a parent customer (the top-level object) having zero or more sub-customers and jobs associated with it.

Sub-customer examples:

• Members of a team or league: the team itself is the parent customer and the members are sub-customers.
• Properties managed by a property management company: the management company is the parent customer and the properties are the sub-customers.

Job examples:

• Tracking a kitchen remodel: the home owner is the parent customer and individual kitchen remodel tasks are jobs.
• Tracking car repairs: the car owner is the parent customer and individual car repairs are jobs.

Use the Customer resource to create parent customer objects, sub-customer objects, and job objects according to your business requirements.

Use the ParentRef and Job attributes in the customer object to designate whether the object is a parent, nested job or nested sub-customer.

First, create parent customer objects: Set Job to false (default) and do not define ParentRef.

Then, create sub-customer and job objects: Set Job to true and set ParentRef to reference the parent customer object.

Going forward, specify an individual parent customer object, sub-customer object, or job object in sales transactions by using the transaction's CustomerRef attribute, based on your business requirements.

Notes

1 	Federal government
2 	State government
3 	Local government
4 	Tribal government
5 	Charitable organization
6 	Religious organization
7 	Educational organization
8 	Hospital
9 	Resale
10 	Direct pay permit
11 	Multiple points of use
12 	Direct mail
13 	Agricultural production
14 	Industrial production / manufacturing
15 	Foreign diplomat

INSERT INTO Customer
(Title, GivenName, MiddleName, FamilyName, CompanyName, PrintOnCheckName, Active, WebAddrURI, taxable,
BillAddr_Line1, BillAddr_City, BillAddr_CountrySubDivisionCode, BillAddr_PostalCode, BillAddr_Lat, BillAddr_Long,
Notes, Job, BillWithParent, Balance, BalanceWithJobs, CurrencyRef_Id, CurrencyRef_Name)
VALUES
('Mr', 'F', 'F', 'Smith', 'GWR', 'CME GWR Swindon', true, 'http://www.easysoft.com', false,
'My St', 'My Town', 'GLOS', 'XX1 1XX', '-37.869967', '145.093636',
'INSERT Test', false, false, 77.77, 77.77, 'GBP', 'British Pound Sterling')

UPDATE Customer SET Notes='UPDATE Test' WHERE ListID IN (SELECT ID From xCache WHERE TableName='Customer')

CustomerType table

Customer types categorise customers in ways that are meaningful to the business. For example, customer types could indicate which industry a customer represents, a customer's geographic location, or how a customer first heard about the business. The categorisation then can be used for reporting or mailings.

Only SELECT statements are available.

Department table

The Department resource provides a way to track transactions based on physical locations such as stores, sales regions, or countries.

INSERT INTO Department
(Name, SubDepartment, Active)
VALUES 
('Corporate Office', false, true)

INSERT INTO Department
(Name, SubDepartment, ParentRef_Id, ParentRef_Name, Active)
VALUES 
('Dept X', true, '23', 'Corporate Office', true)

INSERT INTO Department
(Name, SubDepartment, ParentRef_Id, ParentRef_Name, Active)
VALUES 
('Dept Y', true, '23', 'Corporate Office', true)

INSERT INTO Department
(Name, SubDepartment, ParentRef_Id, ParentRef_Name, Active)
VALUES 
('Dept Z', true, '23', 'Corporate Office', true)

The Deposit table

A Deposit object is a transaction that records one or more deposits of the following types:

• A customer payment, originally held in the Undeposited Funds Account or the Asset Account specified by the Deposit > DepositToAccountRef attribute. The Deposit > line > LinkedTxn element is used in this case to hold deposit information.
• A new, direct deposit specified by a DepositLineDetail line.

Column specific values:

Examples

There must be at least one line item included in a create request.

Create a deposit record with a single deposit line. This is with AUTOCOMMIT ser to ON:

INSERT INTO DepositLine
(CurrencyRef_Id, PrivateNote, DepositToAccountRef_Id, CashBack_AccountRef_Id, CashBack_Amount, CashBack_Memo, Line_DetailType, Line_Description, Line_Amount, Line_EntityRef_Id, Line_AccountRef_Id)
VALUES 
('USD', 'Estimate Header Note 2','35', '36', 10.99, 'Test 1', 'DepositLineDetail', 'Estimate Line Description', 77.77, '1', '82')

Note If you don't specify Line_DetailType QuickBooks returns 'Business Validation Error: Please enter at least one line item., error '6000''.

UPDATE DepositLine SET Line_Amount=99.99 WHERE ID='387|4'

Deposit records

There must be at least one line item included when creating deposits.

Any transaction that funds the Undeposited Funds account can be linked to a Deposit object with a DepositLinkedTxn element.

Tables

DepositLine table

DepositTaxDetail table

Employee table

The DisplayName, Title, GivenName, MiddleName, FamilyName, Suffix, and PrintOnCheckName columns must not contain colon, tab, or newline characters.

The DisplayName attribute must be unique across all other customer, employee, and vendor objects.

The GivenName and FamilyName attributes are required.

The PrimaryEmailAddress attribute must contain an at sign (@) and dot (.).

When QuickBooks Payroll is turned on, support for some attributes is limited:

• Title Not supported when QuickBooks Payroll is turned on.
• Suffix Not supported when QuickBooks Payroll is turned on.
• DisplayName It's read only when QuickBooks Payroll is turned on and is a concatenation of GivenName, MiddleName, and FamilyName.
• PrintOnCheckName Not supported when QuickBooks Payroll is turned on.
• BillRate Not supported when QuickBooks Payroll is turned on.
• SSN Masked SSNs, as is returned in a response, cannot be passed in a request when QuickBooks Payroll is turned on.

Query the CompanyInfo table to determine if the company is payroll turned on.

SELECT Feature_Name, Feature_Value FROM CompanyInfoPreferences WHERE Feature_Name='PayrollFeature'

Notes

City, maximum of 255 chars.
CountrySubDivisionCode, maximum of 255 chars.
PostalCode is required when QuickBooks Payroll is turned on.

Estimate table

The Estimate represents a proposal for a financial transaction from a business to a customer for goods or services proposed to be sold, including proposed pricing.

The Invoice table

An Invoice represents a sales form where the customer pays for a product or service later.

• An invoice must have at least one line for either a sales item or an inline subtotal.
• An invoice must have CustomerRef populated.
• The DocNumber attribute is populated automatically by QuickBooks if not supplied.
• If ShipAddr, BillAddr, or both are not provided, the appropriate customer address from the referenced Customer object is used to fill those values.
• If you have a large number of invoice and corresponding payment records that you want to import to QuickBooks Online, sort the invoice and payment records in chronological order and use the batch resource to send invoice and payments batches of 10, one after the other, to ensure any open invoices get credited with their payments.

Invoice records

An Invoice record consists of Header section and one or more Lines.

Lines are individual line items of a transaction. Lines types are Sales Items, Group Lines, DescriptionOnly Lines, Discount Lines, SubTotal Lines, TaxDetail Lines, or Linked Transaction Lines.

Item Groups also have sub lines that represent individual Items, which make up the Item Group.

Examples

UPDATE Invoice SET PrivateNote='Test note.' WHERE TxnID='317'

Entire records can be deleted using a SQL statement of the form:

DELETE FROM Invoice WHERE TxnID='317'

Use with caution.

If your application does not allow you to set autocommit mode for an ODBC-based connection, refer to this example:

INSERT INTO InvoiceItemGroupLine 
(TxnId, Group_Description, Group_DetailType, Group_ItemRef_Id, Group_Quantity, Line_Description, Line_Amount, Line_DetailType, Line_ItemRef_Id, Line_Qty, Line_TaxCodeRef_Id)
VALUES
  ('323','Cutlery - SET 2', 'GroupLineDetail', '36', 6, 'Office Set - EXTRA Test 2 - GroupLine 1', 60, 'SalesItemLineDetail', '27', 10, '5')

If your application allows you to set autocommit mode for an ODBC-based connection, refer to this example:

These INSERT examples assume you are able to and have turned off autocommit mode in your application:

autocommit off

INSERT INTO Invoice
(DocNumber, TxnDate, CurrencyRef_Id, ExchangeRate, PrivateNote, CustomerRef_Id, BillAddr_Id, SalesTermRef_Id, TotalAmt, HomeTotalAmt, PrintStatus, EmailStatus, Balance, Deposit, TotalTax)
VALUES
('1120', '2019-08-01', 'AUD', 1, 'New Invoice', '17', '15', '3', 440, 440, 'NotSet', 'NotSet', 440, 0, 40)

INSERT INTO InvoiceSalesLine 
(Line_Description, Line_Amount, Line_DetailType, Line_Detail_ItemRef_Id, Line_Detail_UnitPrice, Line_Detail_Qty, Line_Detail_TaxCodeRef_Id, RQUseCachedId)
VALUES
('Some Description 2', 400, 'SalesItemLineDetail', '1', 400,  1, '10', 1)

end transaction

autocommit off

INSERT INTO Invoice
(DocNumber, TxnDate, CurrencyRef_Id, ExchangeRate, PrivateNote, CustomerRef_Id, BillAddr_Id, SalesTermRef_Id, TotalAmt, HomeTotalAmt, PrintStatus, EmailStatus, Balance, Deposit, TotalTax)
VALUES
('1120', '2019-08-01', 'AUD', 1, 'New Invoice', '17', '15', '3', 440, 440, 'NotSet', 'NotSet', 440, 0, 40)

INSERT INTO InvoiceSalesLine 
(Line_Description, Line_Amount, Line_DetailType, Line_Detail_ItemRef_Id, Line_Detail_UnitPrice, Line_Detail_Qty, Line_Detail_TaxCodeRef_Id, RQUseCachedID) 
VALUES 
('NEW CACHE Insert Test - SalesLine 11', 100, 'SalesItemLineDetail', '34', 10, 10, '10', 1)

INSERT INTO InvoiceSalesLine 
(Line_Description, Line_Amount, Line_DetailType, Line_Detail_ItemRef_Id, Line_Detail_UnitPrice, Line_Detail_Qty, Line_Detail_TaxCodeRef_Id, RQUseCachedID) 
VALUES 
('NEW CACHE Insert Test - SalesLine 21', 50, 'SalesItemLineDetail', '35', 10, 5, '10', 1)

INSERT INTO InvoiceItemGroup
(Group_Description, Group_DetailType,  Group_ItemRef_Id, Group_Quantity, RQUSeCachedID) 
VALUES 
('Cutlery - SET',  'GroupLineDetail', '36', 6, 1)

INSERT INTO InvoiceItemGroupLine 
(Line_Description, Line_Amount, Line_DetailType, Line_ItemRef_Id, Line_Qty, Line_TaxCodeRef_Id, RQUSeCachedID) 
VALUES 
('NEW CACHE Insert Test - GroupLine 1', 60, 'SalesItemLineDetail', '31', 6, '5', 1)

INSERT INTO InvoiceItemGroupLine 
(Line_Description, Line_Amount, Line_DetailType, Line_ItemRef_Id, Line_Qty, Line_TaxCodeRef_Id, RQUSeCachedID) 
VALUES 
('NEW CACHE Insert Test - GroupLine 2', 60, 'SalesItemLineDetail', '32', 6, '5', 1)

INSERT INTO InvoiceItemGroupLine 
(Line_Description, Line_Amount, Line_DetailType, Line_ItemRef_Id, Line_Qty, Line_TaxCodeRef_Id, RQUSeCachedID) 
VALUES 
('NEW CACHE Insert Test - GroupLine 3', 60, 'SalesItemLineDetail', '33', 6, '5', 1)

INSERT INTO InvoiceItemGroupLine 
(Line_Description, Line_Amount, Line_DetailType, Line_ItemRef_Id, Line_Qty, Line_TaxCodeRef_Id, RQUSeCachedID) 
VALUES 
('NEW CACHE Insert Test - GroupLine 4', 60, 'SalesItemLineDetail', '34', 6, '5', 1)

INSERT INTO InvoiceItemGroupLine 
(Line_Description, Line_Amount, Line_DetailType, Line_ItemRef_Id, Line_Qty, Line_TaxCodeRef_Id, RQUSeCachedID) 
VALUES 
('NEW CACHE Insert Test - GroupLine 5', 60, 'SalesItemLineDetail', '35', 6, '5', 1)

end transaction

Notes

InvoiceSalesLine table

Notes

InvoiceItemGroup table

Example

UPDATE InvoiceItemGroup SET Group_Description='Office Set - Extra - Updated' WHERE ID='323|66'

SELECT ID, TxnId, Group_Id, Group_Description FROM InvoiceItemGroup WHERE TxnId='323'

Row ID     TxnID Group_Id Group_Description
1   323|66 323   66       Office Set - Extra - Updated

Notes

InvoiceItemGroupLine table

Examples

SELECT ID, TxnId, Group_Id, Group_Description, Line_Id, Line_description FROM InvoiceItemGroupLine WHERE TxnId='323'

Row ID TxnID Group_Id Group_Description  Line_Id Line_Description
1      323|66|96 "323", "66", "Office Set - EXTRA - UPDATES", "96", "Office Set - EXTRA Test - GroupLine 2"
2      323|66|97 "323", "66", "Office Set - EXTRA - UPDATES", "97", "Office Set - EXTRA Test - GroupLine 1"

UPDATE InvoiceItemGroupLine SET Group_Description='Office Set - EXTRA - UPDATE 2', Line_Description='Office Set - EXTRA Test - GroupLine 1 - UPDATE 1' WHERE ID='323|66|97'

SELECT ID, TxnId, Group_Id, Group_Description, Line_Id, Line_description FROM InvoiceItemGroupLine WHERE TxnId='323'

Notes

InvoiceDescriptionLine table

Notes

InvoiceDiscountLine table

Notes

InvoiceSubTotalLine table

Notes

This table allows INSERTs, UPDATEs, and DELETEs, but we would recommend leaving it to QuickBooks to maintain, because it calculates it anyway and sets it regardless.

InvoiceTaxDetail table

Notes

This table allows INSERTs and UPDATEs, but we would recommend leaving it to Quickbooks to maintain, because it calculates it anyway and sets it regardless.

InvoiceLinkedTxn table

Notes

• About Easysoft

• Contact us
• About us
• Clients
• Blog
• Careers

• Products

• ODBC drivers
• JDBC drivers
• Bridges and gateways
• In development

• Services

• Consultancy
• Training
• Custom development

• Licensing

• Product licenses
• Prices

• Support

• Support home
• Solution wizard
• Getting Started Guides
• User Guides
• Knowledge Base

• Search

• Easysoft.com
• Documentation
• Knowledge Base

• Popular Resources

• Developer area
• Client applications

• Legal

• Privacy statement

© 1993 - 2024 Easysoft Limited. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other trademarks and registered trademarks appearing on easysoft.com are the property of their respective owners.