Examples

Creating a purchase order

These SQL INSERT statements create a purchase order with 3 lines.

-- Create a purchase order with 1 line:
INSERT INTO
    "PurchaseOrderLine" (
        "VendorRef_ListID",
        "RefNumber",
        "PurchaseOrderLine_ItemRef_ListID",
        "PurchaseOrderLine_Desc",
        "PurchaseOrderLine_Quantity",
        "PurchaseOrderLine_Rate",
        "PurchaseOrderLine_Amount",
        "PurchaseOrderLine_CustomerRef_ListID"
    )
VALUES
    (
        '80000077-1356973501',
        '1',
        '80000086-1546265999',
        'See Attached 1',
        1.0,
        1.0,
        1.11,
        '80000005-1356973498'
    )

-- Add second line using cached ID
INSERT INTO
    "PurchaseOrderLine" (
        "PurchaseOrderLine_ItemRef_ListID",
        "PurchaseOrderLine_Desc",
        "PurchaseOrderLine_Quantity",
        "PurchaseOrderLine_Rate",
        "PurchaseOrderLine_Amount",
        "PurchaseOrderLine_CustomerRef_ListID",
        "RQUseCachedID"
    )
VALUES
    (
        '80000086-1546265999',
        'See Attached 2',
        2.0,
        2.0,
        2.22,
        '80000005-1356973498',
        1
    )

-- Add third line using cached ID
INSERT INTO
    "PurchaseOrderLine" (
        "PurchaseOrderLine_ItemRef_ListID",
        "PurchaseOrderLine_Desc",
        "PurchaseOrderLine_Quantity",
        "PurchaseOrderLine_Rate",
        "PurchaseOrderLine_Amount",
        "PurchaseOrderLine_CustomerRef_ListID",
        "RQUseCachedID"
    )
VALUES
    (
        '80000086-1546265999',
        'See Attached 3',
        3.0,
        3.0,
        3.33,
        '80000005-1356973498',
        1
    )

Creating an invoice

In this example, customer Ernesto Natiello is invoiced for a storage shed:

-- Find out the necessary information for the invoice:
SELECT
    ListID
FROM
    Customer
WHERE
    Name = 'Natiello, Ernesto' "ListID" "240000-933272658"
SELECT
    ListID
FROM
    Account
WHERE
    Name = 'Accounts Receivable' "ListID" "40000-933270541"
SELECT DISTINCT
    InvoiceLineGroup_ItemGroupRef_ListID
FROM
    InvoiceLine
WHERE
    InvoiceLineGroup_ItemGroupRef_FullName = 'A2 Custom Storage Shed' "InvoiceLineGroup_ItemGroupRef_ListID" "410000-1071530396"

-- Invoice the customer:
INSERT INTO
    InvoiceLine (
        "CustomerRef_ListID",
        "ARAccountRef_ListID",
        "InvoiceLineGroup_ItemGroupRef_ListID",
        "InvoiceLineGroup_Quantity"
    )
VALUES
    (
        '240000-933272658',
        '40000-933270541',
        '410000-1071530396',
        1
    )
UPDATE InvoiceLine
SET
    InvoiceLine_Rate = 1200
WHERE
    ID = '21D09-1608033094|21D0C-1608033094|21D0B-1608033094'
UPDATE InvoiceLine
SET
    InvoiceLine_Rate = 799
WHERE
    ID = '21D09-1608033094|21D0D-1608033094|21D0B-1608033094'

Creating a bill

-- Create a bill with a group:
INSERT INTO
    BillItemLine (
        "VendorRef_FullName",
        "RefNumber",
        "ItemGroupLine_ItemGroupRef_FullName"
    )
VALUES
    ('C.U. Electric', 10000, 'Room Addition/Remodel')
    -- Add the line items.
INSERT INTO
    BillItemLine (
        "VendorRef_FullName",
        "RefNumber",
        "ItemGroupLine_ItemGroupRef_FullName"
    )
VALUES
    ('Bank of Anycity', 666, 'A1 Custom Storage Shed')
INSERT INTO
    BillItemLine (
        "ItemGroupLine_ItemGroupRef_FullName",
        "RQUSeCachedID"
    )
VALUES
    ('A2 Custom Storage Shed', 1)
INSERT INTO
    BillItemLine ("ItemLine_ItemRef_FullName", "RQUseCachedID")
values
    ('Hardware', 1)

Creating a blank invoice

-- Create an invoice with no line items:
INSERT INTO
    InvoiceLine (
        CustomerRef_FullName,
        InvoiceLine_ItemRef_FullName,
        RefNumber
    )
VALUES
    (
        'Melton, Johnny:Dental office',
        'Installation',
        '1079'
    )

Refunding an existing credit memo

-- The payment to be refunded:
SELECT
    TotalAmount,
    PaymentMethodRef_FullName
FROM
    ReceivePayment
where
    customerRef_FullName = 'Adwin Ko' "TotalAmount",
    "PaymentMethodRef_FullName" 373.65,
    "MasterCard"

-- Create a credit memo for a partial refund:
INSERT INTO
    CreditMemoLine (
        "CustomerRef_FullName",
        "ARAccountRef_FullName",
        "TemplateRef_FullName",
        "CreditMemoLine_ItemRef_FullName",
        "CreditMemoLine_Amount"
    )
VALUES
    (
        'Adwin Ko',
        'Accounts Receivable',
        'Custom Credit Note',
        'Service',
        50.00
    )


-- Get the transaction ID for the credit memo:
SELECT
    TxnID
FROM
    CreditMemoLine
WHERE
    CustomerRef_FullName = 'Adwin Ko' "TxnID" "6C8F-1546250338"

-- Apply the refund:
INSERT INTO
    ARRefundCreditCardRefundAppliedTo (
        "CustomerRef_FullName",
        "RefundFromAccountRef_FullName",
        "PaymentMethodRef_FullName",
        "RefundAppliedToTxn_TxnID",
        "RefundAppliedToTxn_RefundAmount"
    )
VALUES
    (
        'Adwin Ko',
        'Undeposited Funds',
        'MasterCard',
        '6C8F-1546250338',
        50.00
    )
SELECT
    TotalAmount
FROM
    ARRefundCreditCardRefundAppliedTo
WHERE
    CustomerRef_FullName = 'Adwin Ko' "TotalAmount" 50
    -- Note RQUseCacheID is only supported if AUTOCOMMIT is OFF. RefundAppliedToTxn_TxnID values must be unique within
    -- the batch in this case. If AUTOCOMMIT is ON, RQUseCacheID is not supported. Each INSERT is independent.

Creating multiple invoices with multiple line items

-- Add an inventory item:
INSERT INTO
    ItemInventory (
        "Name",
        "IncomeAccountRef_FullName",
        "COGSAccountRef_FullName",
        "AssetAccountRef_FullName"
    )
Values
    (
        'Boiler',
        'Services Income',
        'Parts and Materials',
        'Fixed Assets'
    )
SELECT
    ListID
FROM
    xCache
WHERE
    TableName = 'ItemInventory'
8000008C-1546266630
-- Add an inventory sub item:
INSERT INTO
    ItemInventory (
        "Name",
        "IncomeAccountRef_FullName",
        "COGSAccountRef_FullName",
        "AssetAccountRef_FullName",
        "ParentRef_ListID"
    )
Values
    (
        'Stay',
        'Services Income',
        'Parts and Materials',
        'Fixed Assets',
        '8000008C-1546266630'
    )

Creating a vendor credit

INSERT INTO
    VendorCreditItemLine (
        "VendorRef_Fullname",
        "ItemLine_ItemRef_FullName",
        "ItemLine_Cost",
        "ItemLine_Amount"
    )
VALUES
    (
        'Brakes by Hickey',
        'Special Order Part',
        100.00,
        45.00
    )

Creating an estimate

-- Get the ListID for the item which the estimate is being provided for:
SELECT
    ListID
FROM
    ItemService
WHERE
    Name = 'Vintage Restoration' "ListID" "8000007E-1264403363"
SELECT
    ListID
FROM
    Customer
WHERE
    FullName = 'Adwin Ko' "ListID" "80000004-1356973498"

-- Create a single line estimate for 'Vintage Restoration':
INSERT INTO
    EstimateLine (
        "CustomerRef_ListID",
        "EstimateLine_ItemRef_ListID",
        "EstimateLine_Quantity",
        "EstimateLine_Amount"
    )
VALUES
    (
        '80000004-1356973498',
        '8000007E-1264403363',
        1,
        600
    )
SELECT
    ID,
    CustomerRef_FullName,
    SubTotal,
    TotalAmount,
    EstimateLineType,
    EstimateLine_TxnLineID,
    Estimateline_ItemRef_FullName,
    Estimateline_Desc,
    Estimateline_Quantity
FROM
    EstimateLine
WHERE
    TxnID IN (
        SELECT
            ID
        FROM
            xCache
        WHERE
            TableName = 'Estimate'
    )
"ID", "CustomerRef_FullName", "Subtotal", "TotalAmount", "EstimateLineType",
"EstimateLine_TxnLineID", "EstimateLine_ItemRef_FullName", "EstimateLine_Desc", "EstimateLine_Quantity"
"6C9A-1546269526|6C9C-1546269526|", "Adwin Ko", 600, 703.95, "Line", "6C9C-1546269526", "Vintage Restoration", "Complete", 1
-- Add another line to this estimate:
INSERT INTO
    EstimateLine (
        "EstimateLine_ItemRef_FullName",
        "EstimateLine_Quantity",
        "EstimateLine_Amount",
        "RQUseCachedID"
    )
VALUES
    ('Parts Sourcing', 3, 300, 1)
"ID", "CustomerRef_FullName", "Subtotal", "TotalAmount", "EstimateLineType", "EstimateLine_TxnLineID",
"EstimateLine_ItemRef_FullName", "EstimateLine_Desc", "EstimateLine_Quantity"
"6C9A-1546269526|6C9E-1546269526|", "Adwin Ko", 899.99, 1055.91, "Line", "6C9E-1546269526", "Parts Sourcing", "Complete Care", 3
"6C9A-1546269526|6C9C-1546269526|", "Adwin Ko", 899.99, 1055.91, "Line", "6C9C-1546269526", "Vintage Restoration", "Complete", 1
-- This multi-line estimate was created with two seperate INSERT statements (independent transactions: Autocommit OFF).
-- Behind the scenes, the QuickBooks ODBC driver does an UPDATE when processing the second INSERT, and so the second item
-- appears before the first item in the EstimateLine table.

-- Create estimate with three lines with one INSERT:
-- AutoCommit is OFF
INSERT INTO
    EstimateLine (
        "CustomerRef_FullName",
        "EstimateLine_ItemRef_ListID",
        "EstimateLine_Quantity",
        "EstimateLine_Amount"
    )
VALUES
    ('Adwin Ko', '80000019-1356973474', 1, 100)
INSERT INTO
    EstimateLine (
        "EstimateLine_ItemRef_FullName",
        "EstimateLine_Quantity",
        "EstimateLine_Amount",
        "RQUseCachedID"
    )
VALUES
    ('Motorcar Detailing', 3, 300, 1)
INSERT INTO
    EstimateLine (
        "EstimateLine_ItemRef_FullName",
        "EstimateLine_Quantity",
        "EstimateLine_Amount",
        "RQUseCachedID"
    )
VALUES
    ('Oil, Filter, Lubrication', 1, 120, 1)

-- EndTransaction

SELECT
    ID,
    CustomerRef_FullName,
    SubTotal,
    TotalAmount,
    EstimateLineType,
    EstimateLine_TxnLineID,
    Estimateline_ItemRef_FullName,
    Estimateline_Desc,
    Estimateline_Quantity
FROM
    EstimateLine
WHERE
    TxnID IN (
        SELECT
            ID
        FROM
            xCache
        WHERE
            TableName = 'Estimate'
    )
Row ID                               CustomerRef_FullName Subtotal TotalAmount EstimateLineType EstimateLine_TxnLineID
EstimateLine_ItemRef_FullName EstimateLine_Desc EstimateLine_Quantity
1   69B1-1546272190|69B3-1546272190| Adwin Ko           520      611         Line             69B3-1546272190
      Service                       Repair Service    1
2   69B1-1546272190|69B4-1546272190| Adwin Ko           520      611         Line             69B4-1546272190
      Motorcar Detailing            Complete Care     3
3   69B1-1546272190|69B5-1546272190| Adwin Ko           520      611         Line             69B5-1546272190
      Oil, Filter, Lubrication                        1

Creating a credit memo

-- Create a credit memo for a partial refund:
INSERT INTO
    CreditMemoLine (
        "CustomerRef_FullName",
        "ARAccountRef_FullName",
        "TemplateRef_FullName",
        "CreditMemoLine_ItemRef_FullName",
        "CreditMemoLine_Amount"
    )
VALUES
    (
        'Adwin Ko',
        'Accounts Receivable',
        'Custom Credit Note',
        'Service',
        50.00
    )
-- See also: Refunding an Existing CreditMemo example.

Writing a deposit

SELECT
    TxnID,
    CustomerRef_FullName,
    RefNumber,
    Amount
FROM
    ReceivePaymentToDeposit "59A8-1264411556",
    "Adwin Ko",
    <null>,
    373.65
SELECT
    ListID
FROM
    Account
WHERE
    Name = 'Customer Deposits Received' "ListID" "80000019-1356973473"
INSERT INTO
    DepositLine (
        DepositLine_PaymentTxnLineID,
        DepositToAccountRef_FullName,
        TxnDate
    )
VALUES
    ('6C8F-1546250338', 'Undeposited Funds', {d'2017-01-15'})

Converting a sales order into an invoice

This example will not work with QuickBooks Pro, which returns: Error 3270 : 'Missing posting account'.

SELECT
    "CustomerRef_ListID",
    "RefNumber",
    {fn CONCAT('Estimate ', "RefNumber")} as "Memo",
    "EstimateLine_ItemRef_ListID",
    "EstimateLine_Desc",
    "EstimateLine_Rate",
    "EstimateLine_Amount",
    "EstimateLine_SalesTaxCodeRef_ListID"
FROM
    EstimateLine
WHERE
    RefNumber = 267
    AND EstimateLine_Desc = 'Granite Counter and Tile Installation Labor (by hour)' "CustomerRef_ListID",
    "RefNumber",
    "Memo",
    "EstimateLine_ItemRef_ListID",
    "EstimateLine_Desc",
    "EstimateLine_Rate",
    "EstimateLine_Amount",
    "EstimateLine_SalesTaxCodeRef_ListID" "800000B3-1197778093",
    "267",
    "Estimate267",
    "E0000-933272656",
    "Granite Counter and Tile Installation Labor (by hour)",
    25,
    450,
    "20000-999022286"
INSERT INTO
    "SalesOrderLine" (
        "CustomerRef_ListID",
        "RefNumber",
        "Memo",
        "SalesOrderLine_ItemRef_ListID",
        "SalesOrderLine_Desc",
        "SalesOrderLine_Rate",
        "SalesOrderLine_Amount",
        "SalesOrderLine_SalesTaxCodeRef_ListID"
    )
SELECT
    "CustomerRef_ListID",
    "RefNumber",
    {fn CONCAT('Estimate ', "RefNumber")} AS "Memo",
    "EstimateLine_ItemRef_ListID",
    "EstimateLine_Desc",
    "EstimateLine_Rate",
    "EstimateLine_Amount",
    "EstimateLine_SalesTaxCodeRef_ListID"
FROM
    EstimateLine
WHERE
    RefNumber = 267
    AND EstimateLine_Desc = 'Granite Counter and Tile Installation Labor (by hour)'

Creating a sales receipt

-- Create a sales receipt for items in this group:
INSERT INTO
    "SalesReceiptLine" (
        "CustomerRef_FullName",
        "RefNumber",
        "SalesReceiptLineGroup_ItemGroupRef_FullName",
        "SalesReceiptLineGroup_Quantity"
    )
VALUES
    ('Retail Homeowners', '1072', 'Service Kit', 1)
INSERT INTO
    "SalesReceiptLine" (
        "SalesReceiptLine_ItemRef_FullName",
        "SalesReceiptLine_Desc",
        "SalesReceiptLine_Rate",
        "SalesReceiptLine_Amount",
        "SalesReceiptLine_SalesTaxCodeRef_FullName",
        "RQUseCachedID"
    )
VALUES
    (
        'DIY Brake Disk',
        'DIY Brake Disk',
        109.96,
        2.00,
        'Non',
        1
    )
INSERT INTO
    "SalesReceiptLine" (
        "SalesReceiptLine_ItemRef_FullName",
        "SalesReceiptLine_Desc",
        "SalesReceiptLine_Rate",
        "SalesReceiptLine_Amount",
        "SalesReceiptLine_SalesTaxCodeRef_FullName",
        "RQUseCachedID"
    )
VALUES
    (
        'DIY Brake Pad',
        'DIY Brake Pad',
        49.95,
        2.00,
        'Non',
        1
    )
SELECT
    ID,
    SalesReceiptLineType,
    SalesReceiptLine_Desc,
    SalesReceiptLine_Quantity
FROM
    SalesReceiptLine
WHERE
    TxnID = '9E9B-1608030646'
Row ID                                              SalesReceiptLineType SalesReceiptLine_Desc          SalesReceiptLine_Quantity
1   9E9B-1608030646|9EA3-1608030646|                Line                 DIY Brake Pad
2   9E9B-1608030646|9EA2-1608030646|                Line                 DIY Brake Disk
3   9E9B-1608030646|9E9E-1608030646|9E9D-1608030646 GroupLine            DIY Kit for Brake Disk Change  0
4   9E9B-1608030646|9E9F-1608030646|9E9D-1608030646 GroupLine            DIY Kit for Brake Fluid Change 1
5   9E9B-1608030646|9EA0-1608030646|9E9D-1608030646 GroupLine            DIY Kit for Brake Pad Change   1

Create a sales order

-- Create SalesOrder with two item lines:
INSERT INTO "SalesOrderLine" (
  "CustomerRef_FullName", "TemplateRef_FullName", 
  "RefNumber", "SalesOrderLine_ItemRef_FullName", 
  "SalesOrderLine_Quantity", "SalesOrderLine_Rate", 
  "SalesOrderLine_Amount", "SalesOrderLine_SalesTaxCodeRef_FullName"
) 
VALUES 
  (
    'Baker, Chris', 'Sales Order with Rep', 
    '1111', 'Cabinets:Light Pine', 1, 
    1, 150.00, 'Non'
  ) 
SELECT 
  * 
FROM 
  xCache 
WHERE 
  TableName = 'SalesOrder'
Row TableName  Type   ID               EditSequence
1   SalesOrder TxnID  21CF5-1608040644 1608040644
SELECT 
  TxnID, 
  TxnNumber, 
  CustomerRef_FullName, 
  TemplateRef_FullName, 
  RefNumber, 
  TermsRef_FullName, 
  SubTotal, 
  TotalAmount, 
  "Memo" 
FROM 
  SalesOrderLine 
WHERE 
  TXnID IN (
    SELECT 
      ID 
    FROM 
      xCache 
    WHERE 
      TableName = 'SalesOrder'
  )
Row TxnID            TxnNumber CustomerRef_FullName TemplateRef_FullName RefNumber TermsRef_FullName SubTotal TotalAmount, "Memo"
1   21CF5-1608040644 1780      Baker, Chris         Sales Order with Rep 1111      Net 30            150      150
INSERT INTO SalesOrderLine (
    TxnID, EditSequence, Refnumber, SalesOrderLine_ItemRef_FullName, 
    SalesOrderLine_Quantity, SalesOrderLine_Rate, 
    SalesOrderLine_Amount, SalesOrderLine_SalesTaxCodeRef_FullName
  ) 
VALUES 
  (
    '21CF5-1608040644', '1608043435', 
    '222', 'Subs:Painting', 2, 100, 200, 
    'Non'
  )

Applying payment to an invoice

SELECT
    TxnID,
    TxnNumber,
    InvoiceLine_Amount,
    InvoiceLine_ItemRef_FullName,
    RefNumber
FROM
    InvoiceLine
WHERE
    CustomerRef_Fullname = 'Ecker Designs:Office Repairs' "TxnID",
    "TxnNumber",
    "InvoiceLine_Amount",
    "InvoiceLine_ItemRef_FullName",
    "RefNumber" "CEC-933782257",
    243,
    225,
    "Permit",
    "1086" "CEC-933782257",
    243,
    252,
    "Removal",
    "1086" "CEC-933782257",
    243,
    0,
    "Framing",
    "1086" "CEC-933782257",
    243,
    0,
    "Subs:Drywall",
    "1086" "CEC-933782257",
    243,
    0,
    "Subs:Plumbing",
    "1086" "CEC-933782257",
    243,
    0,
    "Subs:Painting",
    "1086" "CEC-933782257",
    243,
    0,
    "Subs:Insulating",
    "1086" "CEC-933782257",
    243,
    745,
    "Lumber:Rough",
    "1086" "CEC-933782257",
    243,
    175,
    "Lumber:Trim",
    "1086"
INSERT INTO
    ReceivePaymentLine (
        CustomerRef_FullName,
        DepositToAccountRef_FullName,
        TotalAmount,
        AppliedToTxn_TxnID,
        AppliedToTxn_PaymentAmount,
        TxnDate,
        "Memo"
    )
VALUES
    (
        'Ecker Designs:Office Repairs',
        'Checking',
        225.00,
        'CEC-933782257',
        225.00,
        {d'2017-01-25'},
        'Permit'
    )
SELECT
    *
FROM
    xCache
WHERE
    TableName = 'ReceivePayment' "TableName",
    "IDType",
    "ID",
    "EditSequence" "ReceivePayment",
    "TxnID",
    "21CF0-1608041008",
    "1608041008"
SELECT
    CustomerRef_FullName,
    TotalAmount
FROM
    ReceivePayment
WHERE
    TxnID = '21CF0-1608041008' "CustomerRef_FullName",
    "TotalAmount" "Ecker Designs:Office Repairs",
    225

Inserting an opening balance in the Customer table

INSERT INTO
    customer (
        "Name",
        "FirstName",
        "LastName",
        "CompanyName",
        "Contact",
        "AccountNumber",
        "BillAddress_Addr1",
        "BillAddress_Addr2",
        "BillAddress_Addr3",
        "BillAddress_City",
        "BillAddress_State",
        "BillAddress_PostalCode",
        "Phone",
        "Fax",
        "Email",
        "TermsRef_ListID",
        "SalesTaxCodeRef_ListID",
        "PreferredPaymentMethodRef_FullName",
        "CreditCardInfo_CreditCardNumber",
        "CreditCardInfo_ExpirationMonth",
        "CreditCardInfo_ExpirationYear",
        "CreditCardInfo_NameOnCard",
        "CreditCardInfo_CreditCardAddress",
        "CreditCardInfo_CreditCardPostalCode",
        "OpenBalance",
        "JobStatus",
        "JobStartDate",
        "JobEndDate",
        "JobDesc"
    )
VALUES
    (
        'Amy''s Bird Sanctuary',
        'Amy',
        'Lauterbach',
        'Amy''s Bird Sanctuary',
        'Amy Lauterbach',
        '123456',
        'Amy''s Bird Sanctuary',
        '4581 Finch St.',
        'Suite 456',
        'Bayshore',
        'CA',
        '94326',
        '(650) 555-3311',
        '(650) 555-3312',
        'Birds@Intuit.com',
        '10000-933272658',
        '10000-999022286',
        'Visa',
        '4111111111111111',
        06,
        2020,
        'Amy Lauterbach',
        '4581 Finch St. Suite 456',
    )
-- After inserting a value in the OpenBalance field, the value is moved to Balance field.
"TableName", "IDType", "ID", "EditSequence"
"Customer", "ListID", "800000DC-1608027680", "1608027680"
SELECT
    Name,
    OpenBalance,
    Balance
FROM
    Customer
WHERE
    ListID = '800000DC-1608027680' "Name",
    "OpenBalance",
    "Balance" "Amy's Bird Sanctuary",
    <Null>,
    0

Apply discounts to existing invoices

SELECT
    ListID,
    Name
From
    Item
WHERE
    Name LIKE '%iscount%' "2A0000-933272656",
    "Discount"
INSERT INTO
    InvoiceLine (
        TxnID,
        InvoiceLine_ItemRef_ListID,
        InvoiceLine_Desc
    )
VALUES
    ('3F45-1071505743', '2A0000-933272656', 'Discount')
SELECT
    TOP 1 InvoiceLine_RatePercent
FROM
    InvoiceLine
WHERE
    TxnID = '3F45-1071505743' "InvoiceLine_RatePercent" -10

Getting a list of voided invoices

-- Get a list of voided invoices:
SELECT
    RefNumber,
    CustomerRef_FullName,
    "Memo"
FROM
    Invoice
WHERE
    "Memo" like 'VOID:%'