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',
 '94326', 0, 'None', {d'2017-01-09'}, null, '£30 Weekly Gardening Service'
)

-- 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:%'