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 part of, 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/updated in that table.

TABLE	TYPE	PARENT	SELECT	INSERT	DELETE	UPDATE
ACCOUNT	p	-	Y	Y	-	Y
ATTACHABLE	P	-	Y	Y	Y	Y
ATTACHABLEREFERENCES	L	ATTACHABLE	Y	Y	Y	Y
BILL	P	-	Y	Y	Y	Y
BILLEXPENSELINE	L	BILL	Y	Y	Y	Y
BILLLINKEDTXN	L	BILL	Y	Y	Y	Y
BILLTAXDETAIL	L	BILL	Y	Y	Y	Y
BILLPAYMENT	P	-	Y	Y	Y	Y
BILLPAYMENTLINKEDTXN	L	BILLPAYMENT	Y	Y	Y	Y
BUDGET	P	-	Y	N	N	N
BUDGETDETAILLINE	L	BUDGET	Y	N	N	N
CLASS	P	-	Y	Y	-	Y
COMPANYCURRENCY	P	-	Y	Y	-	Y
COMPANYINFO	P	-	Y	N	N	Y
COMPANYINFOPREFERENCES	L	COMPANYINFO	Y	N	N	Y
CREDITMEMO	P	-	Y	Y	Y	Y
CREDITMEMOSALESLINE	L	CREDITMEMO	Y	Y	Y	Y
CREDITMEMOITEMGROUP	L	CREDITMEMO	Y	Y	Y	Y
CREDITMEMOITEMGROUPLINE	L	CREDITMEMO	Y	Y	Y	Y
CREDITMEMOGROUPITEMLINE	L	CREDITMEMO	Y	Y	Y	Y
CREDITMEMODESCRIPTIONLINE	L	CREDITMEMO	Y	Y	Y	Y
CREDITMEMODISCOUNTLINE	L	CREDITMEMO	Y	Y	Y	Y
CREDITMEMOSUBTOTALLINE	L	CREDITMEMO	Y	Y	N	Y
CREDITMEMOTAXDETAIL	L	CREDITMEMO	Y	Y	Y	Y
CUSTOMER	P	-	Y	Y	Y	Y
CUSTOMERTYPE	P	-	Y	N	N	N
DEPARTMENT	P	-	Y	Y	N	Y
DEPOSIT	P	-	Y	Y	Y	Y
DEPOSITLINE	L	DEPOSIT	Y	Y	Y	Y
DEPOSITLINKEDTXN	L	DEPOSIT	Y	Y	Y	Y
DEPOSITTAXDETAIL	L	DEPOSIT	Y	Y	Y	Y
EMPLOYEE	P	-	Y	Y	N	Y
ESTIMATE	P	-	Y	Y	Y	Y
ESTIMATESALESLINE	L	ESTIMATE	Y	Y	Y	Y
ESTIMATEITEMGROUP	L	ESTIMATE	Y	Y	Y	Y
ESTIMATEITEMGROUPLINE	L	ESTIMATE	Y	Y	Y	Y
ESTIMATEDESCRIPTIONLINE	L	ESTIMATE	Y	Y	Y	Y
ESTIMATEDISCOUNTLINE	L	ESTIMATE	Y	Y	Y	Y
ESTIMATESUBTOTALLINE	L	ESTIMATE	Y	Y	N	N
ESTIMATETAXDETAIL	L	ESTIMATE	Y	Y	Y	Y
ESTIMATELINKEDTXN	L	ESTIMATE	Y	Y	Y	Y
EXCHANGERATE	P	-	Y	N	N	Y
INVOICE	P	-	Y	Y	Y	Y
INVOICESALESLINE	L	INVOICE	Y	Y	Y	Y
INVOICEITEMGROUP	L	INVOICE	Y	Y	Y	Y
INVOICEITEMGROUPLINE	L	INVOICE	Y	Y	Y	Y
INVOICEDESCRIPTIONLINE	L	INVOICE	Y	Y	Y	Y
INVOICEDISCOUNTLINE	L	INVOICE	Y	Y	Y	Y
INVOICESUBTOTALLINE	L	INVOICE	Y	Y	N	N
INVOICETAXDETAIL	L	INVOICE	Y	Y	Y	Y
INVOICELINKEDTXN	L	INVOICE	Y	Y	Y	Y
ITEM	P	-	Y	Y	Y	Y
ITEMCATEGORY	L	ITEM	Y	Y	Y	Y
ITEMGROUP	L	ITEM	Y	N	N	N
ITEMGROUPLINE	L	ITEM	Y	N	N	N
ITEMINVENTORY	L	ITEM	Y	Y	Y	N
ITEMNONINVENTORY	L	ITEM	Y	Y	Y	Y
ITEMSERVICE	L	ITEM	Y	Y	Y	Y
JOURNALCODE	P	-	Y	Y	N	Y
JOURNALENTRY	P	-	Y	Y	Y	Y
JOURNALENTRYCREDITLINE	L	JOURNALENTRY	Y	Y	Y	Y
JOURNALENTRYDEBITLINE	L	JOURNALENTRY	Y	Y	Y	Y
JOURNALENTRYDESCRIPTIONLINE	L	JOURNALENTRY	Y	Y	Y	Y
JOURNALENTRYTAXDETAIL	L	JOURNALENTRY	Y	Y	Y	Y
PAYMENT	P	-	Y	Y	Y	Y
PAYMENTLINKEDTXN	L	PAYMENT	Y	Y	Y	Y
PAYMENTMETHOD	P	-	Y	Y	N	Y
PREFERENCES	P	-	Y	N	N	Y
PREFERENCESSALESCUSTOMFIELD	L	PREFERENCES	Y	N	N	N
PREFERENCESPOCUSTOMFIELD	L	PREFERENCES	Y	N	N	N
PREFERENCESOTHER	L	PREFERENCES	Y	N	N	N
PURCHASE	P	-	Y	Y	Y	Y
PURCHASEEXPENSELINE	L	PURCHASE	Y	Y	Y	Y
PURCHASETAXDETAIL	L	PURCHASE	Y	Y	Y	Y
PURCHASEORDER	P	-	Y	Y	Y	Y
PURCHASEORDEREXPENSELINE	L	PURCHASEORDER	Y	Y	Y	Y
PURCHASEORDERLINKEDTXN	L	PURCHASEORDER	Y	Y	Y	Y
PURCHASEORDERTAXDETAIL	L	PURCHASEORDER	Y	Y	Y	Y
REFUNDRECEIPT	P	-	Y	Y	Y	Y
REFUNDRECEIPTSALESLINE	L	REFUNDRECEIPT	Y	Y	Y	Y
REFUNDRECEIPTITEMGROUP	L	REFUNDRECEIPT	Y	Y	Y	Y
REFUNDRECEIPTITEMGROUPLINE	L	REFUNDRECEIPT	Y	Y	Y	Y
REFUNDRECEIPTDESCRIPTIONLINE	L	REFUNDRECEIPT	Y	Y	Y	Y
REFUNDRECEIPTDISCOUNTLINE	L	REFUNDRECEIPT	Y	Y	Y	Y
REFUNDRECEIPTSUBTOTALLINE	L	REFUNDRECEIPT	Y	Y	N	N
REFUNDRECEIPTTAXDETAIL	L	REFUNDRECEIPT	Y	Y	Y	Y
SALESRECEIPT	P	-	Y	Y	Y	Y
SALESRECEIPTSALESLINE	L	SALESRECEIPT	Y	Y	Y	Y
SALESRECEIPTITEMGROUP	L	SALESRECEIPT	Y	Y	Y	Y
SALESRECEIPTITEMGROUPLINE	L	SALESRECEIPT	Y	Y	Y	Y
SALESRECEIPTDESCRIPTIONLINE	L	SALESRECEIPT	Y	Y	Y	Y
SALESRECEIPTDISCOUNTLINE	L	SALESRECEIPT	Y	Y	Y	Y
SALESRECEIPTSUBTOTALLINE	L	SALESRECEIPT	Y	Y	N	N
SALESRECEIPTTAXDETAIL	L	SALESRECEIPT	Y	Y	Y	Y
SALESTERMS	P	-	Y	Y	N	Y
TAXAGENCY	P	-	Y	Y	N	N
TAXCODE	P	-	Y	N	N	N
TAXCODESALESRATES	L	TAXCODE	Y	N	N	N
TAXCODEPURCHASERATES	L	TAXCODE	Y	N	N	N
TAXRATE	P	-	Y	Y	N	Y
TAXRATELINE	L	TAXRATE	Y	Y	N	Y
TAXSERVICE	N	-	Not supported - no XML interface
TIMEACTIVITY	P	-	Y	Y	Y	Y
TRANSFER	P	-	Y	Y	Y	Y
VENDOR	P	-	Y	Y	N	Y
VENDORCREDIT	P	-	Y	Y	Y	Y
VENDORCREDITEXPENSELINE	L	VENDORCREDIT	Y	Y	Y	Y
XCACHE	P (in-memory)	-	Y	N	N	N

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.

Deleting Accounts is achieved by setting the Active attribute to false in a SQL UPDATE statement, which makes it 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, as with may QuickBooks Online tables.

However a DELETE can be achieved by setting 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.

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

Create 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 are creating 4 AttachableReference records with a reference to two Estimates and 2 Invoices, retrieved from the Estimates and Invoice tables.

Estimates TxnIds: 119, 120.

Invoices TxnIds: 127, 129.

Autocommit ON

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 call will retrieve the record whose ID is cached in the xCache table and add 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'

Autocommit OFF

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 the full 5 INSERTS are committed in a single INSERT 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, as above with AutoCommit OFF, it indicates to the Easysoft QuickBooks Online driver that the AttachableReference line should be associated 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/services rendered, received, or both.

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

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

As a minimum, 1 BillExpenseLine is needed.

Column	Notes
CurrencyRef_Id	This must be defined if multicurrency is enabled for the company. Multicurrency is enabled for the company if Preferences > MultiCurrencyEnabled is set to true.
GlobalTaxCalculation	Valid values are: `TaxExcluded`, `TaxInclusive`, `NotApplicable`.
TransactionLocationType	Valid values are: `WithinFrance`, `FranceOverseas`, `OutsideFranceWithEU`, `OutsideEU`.

BillLinkedTxn Table

Column	Notes
Line_DetailType	Valid values are: `ItemBasedExpenseLine` or `AccountBasedExpenseLine`
Line_ItemExpense_BillableStatus	Valid values are: `Billable`, `NotBillable`, `HasBeenBilled`
Line_AcctExpense_BillableStatus	Valid values are: `Billable`, `NotBillable`, `HasBeenBilled`

BillTaxDetail Table

Column	Notes
Line_DetailType	Valid values are: `TaxLineDetail`

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 via a transaction (autocommit OFF).

With autocommit ON, you can simply do an INSERT INTO BillExpenseLine with both the Bill Header and Bill Expense Line columns needed. 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 first (e.g. INSERT INTO Bill ...), followed by an INSERT INTO BillExpenseLine with just the Expense Line columns plus RQUseCachedId=1 to indicate this line it is for the current record, which in this case is the one being contructed via a transaction.

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

The transaction can then be committed 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 the line applies to the Bill record whose TxnID is saved in the xCache.

Autocommit On

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

A Bill object must have at least one line item:

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

Autocommit Off

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 UI.

The total amount cannot be negative.

Column	Notes
CurrencyRef	Reference to the currency in which all amounts on the associated transaction are expressed. This must be defined if multicurrency is enabled for the company. Multicurrency is enabled for the company if Preferences > MultiCurrencyEnabled is set to true
CCAccountRef	Used when `PayType` is `CreditCard`.
CheckPayment	Used when `PayType` is `Check`.
TransactionLocationType	Must be set to one of these values: `WithinFrance`, `FranceOverseas`, `OutsideFranceWithEU`, `OutsideEU`
Line_LinkedTxn_TxnLineId	Required for Deposit and Bill entities. The line number of a specific line of the linked transaction. If supplied, the `TxnId` and `TxnType` attributes of the linked transaction must also be populated.
RQUseCachedID	See Invoice table for notes about RQUseCachedID usage.

Examples

Add payment to 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 and are created to give a business measurable expense goals.

This amount represents how much should be spent against that account or customer in the give 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 may belong to this object. But, 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 enable 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 or credit of payment or part of a payment for goods or services that have been sold.

A CreditMemo record consists of Header section plus 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 which represent individual Items that make up the Item Group.

Lines are stored in the following tables:

Table	Description
CreditMemo	Each row shows just the record Header column values.
CreditMemoDescriptionLine	Each row shows the record Header column values plus the Descrition Line column values for each Description line within the record.
CreditMemoDiscountLine	Each row shows the record Header column values plus the Discount Line column values for each Discount line within the record.
CreditMemoItemGroup	Each row shows the record Header Column values plus the Group Header column values for each Item Group within the Record.
CreditMemoItemGroupLine	Each row shows the record Header column values plus the Group Header column values plus the Item column values for each Item in each Group within the Record.
CreditMemoSalesLine	Each row shows the record Header column values plus the Sales Line column values for each Sales Line within the record.
CreditMemoSubtotalLine	Each row shows the record Header column values plus the SubTotal Line column values for each SubTotal line within the record.
CreditMemoTaxDetail	Each row shows the record Header column values plus the TaxDetail Line column values for each TaxDetail line within the record.

Notes

Column	Notes
CurrencyRef_Id/Name	Reference to the currency in which all amounts on the associated transaction are expressed. This must be defined if multicurrency is enabled for the company. Multicurrency is enabled for the company if Preferences.MultiCurrencyEnabled is set to true. Required if multicurrency is enabled for the company.
BillEmailAddress	Identifies the e-mail address where the credit memo is sent. If EmailStatus is set to `NeedToSend`, BillEmailAddress is a required input.
ShipAddr_Id	Required to update the address.
EmailStatus	NotSet, NeedToSend, EmailSent
PrintStatus	NotSet, NeedToPrint, PrintComplete
TransactionLocationType	WithinFrance, FranceOverseas, OutsideFranceWithEU, OutsideEU

Examples

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

CreditMemoDescriptionLine Table

Notes

Column	Notes
Line_DetailType	Must be DescriptionOnly

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.

Creating a new CreditMemo record with one DescriptionLine:

Autocommit ON

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

Autocommit OFF

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

Column	Notes
Line_DetailType	Must be DescriptionOnly

Examples

INSERT to create 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')

You can only create Discount Line as either an update to an existing record or as an insert when the record created which also contains a line.

INSERT to update 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

Column	Notes
Group_DetailType	Must be `GroupLineDetail`.

CreditMemoItemGroupLine Table

Notes

Column	Notes
Line_DetailType	Must be `SalesItemLineDetail`.

CreditMemoSalesLine Table

Notes

Column	Notes
Line_DetailType	Must be `SalesItemLineDetail`.

CreditMemoSubtotalLine Table

Notes

Column	Notes
Line_DetailType	Must be `SubtotalLineDetail`.

CreditMemoTaxDetail Table

Notes

Column	Notes
TaxLine_DetailType	Must be `TaxLineDetail`.

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 parent customer object.

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

Notes

Column	Notes
TaxExemptionReasonId	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 is available.

Department Table

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

As you create sales and expense transactions, consistently designate the department to which they belong.

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, into 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 detail.

Column specific values:

Column	Notes
GlobalTaxCalculation	Valid values are: TaxExcluded, TaxInclusive or NotApplicable

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 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: 'Line_DetailType' needed otherwise 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 in a create request.

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

Tables

Table	Notes
Deposit	Each row shows just the record Header column values
DepositLine	Individual line items comprising the deposit.
DepositLinkedTxn	Specify a DepositLinkedTxn element along with DepositLine detail type if this line is to record a deposit for an existing transaction. Select UndepositedFunds account on the existing transaction to make it available for the Deposit. Possible types of transactions that can be linked to a Deposit include: Transfer, Payment (for Cash, CreditCard, and Check payment method types), SalesReceipt, RefundReceipt, JournalEntry. In addition, any expense object whose line item has AccountReceivable can be linked to a Payment and then that Payment can be linked to a Deposit object. Use DepositLinkedTxn TxnId as the ID in a separate read request for the specific resource to retrieve details of the linked object. Valid Line types include: LinkedTxn and DepositLine
DepositTaxDetail	Each row shows the record Header column values plus the TaxDetail Line column values for each TaxDetail line within the record

DepositLine Table

Column	Values
Line_DetailType	DepositLineDetail
Line_TaxApplicableOn	Sales, Purchase
Line_TxnType	APCreditCard, ARRefundCreditCard, Bill, BillPaymentCheck, BuildAssembly, CarryOver , CashPurchase, Charge, Check, CreditMemo, Deposit, EFPLiabilityCheck, EFTBillPayment, EFTRefund, Estimate, InventoryAdjustment, InventoryTransfer, Invoice, ItemReceipt, JournalEntry, LiabilityAdjustment, Paycheck, PayrollLiabilityCheck, Purchase, PurchaseOrder, PriorPayment, ReceivePayment, RefundCheck, RefundReceipt, SalesOrder, SalesReceipt, SalesTaxPaymentCheck, Transfer, TimeActivity, VendorCredit , YTDAdjustment

DepositTaxDetail Table

Column	Values
TaxLine_DetailType	TaxLineDetail

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 (.).

The full complement of read, create, delete via deactivation (active=false), and update operations are available both with and without QuickBooks Payroll enabled. However, when Payroll is enabled, support for some attributes is limited:

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

Query the CompanyInfo table to determine if the company is payroll enabled.

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

Notes

Column	Notes
PrimaryAddr_Id	Unique identifier of the QuickBooks object for the address, used for modifying the address.
PrimaryAddr	Represents the physical street address for this employee. If QuickBooks Payroll is enabled for the company, the following PhysicalAddress fields are required: City, maximum of 255 chars. CountrySubDivisionCode, maximum of 255 chars. PostalCode is required when QuickBooks Payroll is enabled.

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 the data service 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 wish to import to the QuickBooks Online company, 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 plus 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 which represent individual Items that 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.

Autocommit On

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

The following INSERT adds a new group with one groupline to an existing record. If your application does not allow you to set autocommit mode and its default behaviour is to enable autocommit mode, you need to use single INSERTs like this one.

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

Autocommit Off

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

Column Value

DocNumber

Column	Value
DocNumber	Reference number for the transaction. If not explicitly provided at create time, this field is populated based on the setting of Preferences:CustomTxnNumber as follows: If Preferences:CustomTxnNumber is true a custom value can be provided. If no value is supplied, the resulting DocNumber is null. If Preferences:CustomTxnNumber is false, resulting DocNumber is system generated by incrementing the last number by 1. If Preferences:CustomTxnNumber is false then do not send a value as it can lead to unwanted duplicates. If a DocNumber value is sent for an Update operation, then it just updates that particular invoice and does not alter the internal system DocNumber. Note: DocNumber is an optional field for all locales except France. For France, if Preferences > CustomTxnNumber is enabled it will not be automatically generated and is a required field. If a duplicate DocNumber needs to be supplied, add the query parameter name/value pair, `include=allowduplicatedocnum` to the URI.
CurrencyRef_Id	Reference to the currency in which all amounts on the associated transaction are expressed. This must be defined if multicurrency is enabled for the company.
BillEmailAddress	If the value of `EmailStatus` is `NeedToSend`, `BillEmailAddress` is a required.

Reference number for the transaction.

If not explicitly provided at create time, this field is populated based on the setting of Preferences:CustomTxnNumber as follows:

If Preferences:CustomTxnNumber is true a custom value can be provided. If no value is supplied, the resulting DocNumber is null.
If Preferences:CustomTxnNumber is false, resulting DocNumber is system generated by incrementing the last number by 1.
If Preferences:CustomTxnNumber is false then do not send a value as it can lead to unwanted duplicates.
If a DocNumber value is sent for an Update operation, then it just updates that particular invoice and does not alter the internal system DocNumber.

Note: DocNumber is an optional field for all locales except France. For France, if Preferences > CustomTxnNumber is enabled it will not be automatically generated and is a required field.

If a duplicate DocNumber needs to be supplied, add the query parameter name/value pair, include=allowduplicatedocnum to the URI.

CurrencyRef_Id Reference to the currency in which all amounts on the associated transaction are expressed. This must be defined if multicurrency is enabled for the company.

BillEmailAddress If the value of EmailStatus is NeedToSend, BillEmailAddress is a required.

InvoiceSalesLine Table

Notes

Column Notes

RQUseCachedId

Column	Notes
RQUseCachedId	This column is optional and for use with INSERTs only. It is used to indicate that the line being added should be appended to the current Invoice record. The current Invoice record is either the last record that was either inserted into or updated in the Invoice table or the one currently being constructed within a transaction. Suppose you need to create an Invoice with one Sales line. This can be done in one of two ways, either directly (Autocommit mode ON) or via a Transaction (AutoCommit mode OFF). With AutoCommit ON, you can simply do an `INSERT INTO InvoiceSalesLine` with both the Invoice Header and Sales Line columns needed. `RQUseCachedID` is not relevant here and should not be used. With Autocommit OFF, you can split the Insert into 2 separate inserts with the Header first (e.g. `INSERT INTO Invoice ...`), followed by an `INSERT INTO InvoiceSalesLine` with just the Sales Line columns plus `RQUseCachedId=1` to indicate this line is for the current record. The second method allows for several Lines to be added at the same time the record is created. The Transaction can then be committed 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='Invoice'`. Note the xCache table is memory based and values do not persist across sessions. Once the Invoice exists, you can add further lines by using the `TxnId` of the Invoice directly, or if the xCache is still current, use RqUseCachedID=1 to indicate the line applies to the Invoice record whos `TxnId` is saved in the xCache.
Line_DetailType	Must be set to `SalesItemLineDetail`

This column is optional and for use with INSERTs only. It is used to indicate that the line being added should be appended to the current Invoice record.

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

Suppose you need to create an Invoice with one Sales line. This can be done in one of two ways, either directly (Autocommit mode ON) or via a Transaction (AutoCommit mode OFF).

With AutoCommit ON, you can simply do an INSERT INTO InvoiceSalesLine with both the Invoice Header and Sales Line columns needed. RQUseCachedID is not relevant here and should not be used.

With Autocommit OFF, you can split the Insert into 2 separate inserts with the Header first (e.g. INSERT INTO Invoice ...), followed by an INSERT INTO InvoiceSalesLine with just the Sales Line columns plus RQUseCachedId=1 to indicate this line is for the current record.

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

The Transaction can then be committed 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='Invoice'. Note the xCache table is memory based and values do not persist across sessions.

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

Line_DetailType Must be set to SalesItemLineDetail

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

Column	Notes
Group_DetailType	Must be `GroupLineDetail`.

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

Column	Notes
Group_DetailType	Must be `GroupLineDetail`.
Line_DetailType	Must be `SalesItemLineDetail`.

InvoiceDescriptionLine Table

Notes

Column	Notes
Line_DetailType	Must be `DescriptionOnlyLine`.

InvoiceDiscountLine Table

Notes

Column	Notes
Line_DetailType	Must be `DiscountLineDetail`.

InvoiceSubTotalLine Table

Notes

Column	Notes
Line_DetailType	Must be `DiscountLineDetail`.

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

Column	Notes
TaxLine_DetailType	Must be `SubTotalLineDetail`.

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

InvoiceLinkedTxn Table

Notes

Column	Notes
TaxLine_DetailType	Must be `SubTotalLineDetail`.