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