Knowledgebase
[QODBC-Desktop] How to create a Sales Receipt using QODBC
Posted by brad waddell on 12 March 2009 05:21 PM

Example of Creating Sales Receipts for USA QuickBooks Users

Create One Sales Receipt with a Single Line

Note: This creates one SalesReceipt with a single line with all the billing address details.

Query in QODBC

insert into salesreceiptline (CustomerRefListID, TemplateRefListID, TxnDate,
IsPending, DueDate, IsToBePrinted, DepositToAccountRefListID,
BillAddressAddr1, BillAddressAddr2, BillAddressAddr3,
BillAddressCity, BillAddressState, BillAddressPostalCode,
SalesReceiptLineItemRefListID, SalesReceiptLineDesc,
SalesReceiptLineRate, SalesReceiptLineAmount,
SalesReceiptLineSalesTaxCodeRefListID)
values ('4C0000-1040154668', '80000-910479423', {d'2021-12-15'}, 'ABC01',
0, {d'2021-12-15'}, 0, '30000-896814907','Joe Mama Inc 2',
'123 Main St.', 'Suite 450', 'Chicago', 'IL', '60602',
'8C0003-1044564690', 'Priority Support Incident 10 Pack Subscription',
275.00, 275.00, '20000-1011136881')


Note:  QODBC Support Wizard is used only to test QODBC SQL queries and is not a development tool.

 

Result in QuickBooks

The query above results in the following Sales Receipt in QuickBooks 2022 Enterprise:

 

Example of Creating Sales Receipts for NON-USA QuickBooks Users

Create One Sales Receipt with Three Lines

Note: This creates one SalesReceipt with three lines. Note the FQSaveToCache field, set to True except on the last line.

First Line

INSERT INTO "SalesReceiptLine" ("CustomerRefListID", "RefNumber", "SalesReceiptLineItemRefListID", "SalesReceiptLineDesc", "SalesReceiptLineRate", "SalesReceiptLineAmount", "SalesReceiptLineTaxCodeRefListID", "FQSaveToCache") VALUES ('470001-1071525403', '1', '250000-933272656', 'Building permit 1', 1.00000, 1.00, '20000-999022286', 1)

Second Line

INSERT INTO "SalesReceiptLine" ("CustomerRefListID", "RefNumber", "SalesReceiptLineItemRefListID", "SalesReceiptLineDesc", "SalesReceiptLineRate", "SalesReceiptLineAmount", "SalesReceiptLineTaxCodeRefListID", "FQSaveToCache") VALUES ('470001-1071525403', '1', '250000-933272656', 'Building permit 2', 2.00000, 2.00, '20000-999022286', 1)

Third (Last) Line

INSERT INTO "SalesReceiptLine" ("CustomerRefListID", "RefNumber", "SalesReceiptLineItemRefListID", "SalesReceiptLineDesc", "SalesReceiptLineRate", "SalesReceiptLineAmount", "SalesReceiptLineTaxCodeRefListID", "FQSaveToCache") VALUES ('470001-1071525403', '1', '250000-933272656', 'Building permit 3', 3.00000, 3.00, '20000-999022286', 0)

Create Three Sales Receipts with One Line in Each

Note: This creates three SalesReceipts with one line each. Note the FQSaveToCache field is not specified (or can be set to false)

First SalesReceipt

INSERT INTO "SalesReceiptLine" ("CustomerRefListID", "RefNumber", "SalesReceiptLineItemRefListID", "SalesReceiptLineDesc", "SalesReceiptLineRate", "SalesReceiptLineAmount", "SalesReceiptLineTaxCodeRefListID") VALUES ('470001-1071525403', '1', '250000-933272656', 'Building permit 1', 1.00000, 1.00, '20000-999022286')

Second SalesReceipt

INSERT INTO "SalesReceiptLine" ("CustomerRefListID", "RefNumber", "SalesReceiptLineItemRefListID", "SalesReceiptLineDesc", "SalesReceiptLineRate", "SalesReceiptLineAmount", "SalesReceiptLineTaxCodeRefListID") VALUES ('470001-1071525403', '1', '250000-933272656', 'Building permit 2', 2.00000, 2.00, '20000-999022286')

Third SalesReceipt

INSERT INTO "SalesReceiptLine" ("CustomerRefListID", "RefNumber", "SalesReceiptLineItemRefListID", "SalesReceiptLineDesc", "SalesReceiptLineRate", "SalesReceiptLineAmount", "SalesReceiptLineTaxCodeRefListID") VALUES ('470001-1071525403', '1', '250000-933272656', 'Building permit 3', 3.00000, 3.00, '20000-999022286')

Create One Sales Receipt with Three Lines with Shared Header Information

Note: This creates one SalesReceipt with three lines with a specified billing address. Note the FQSaveToCache field, set to True. Saving the SalesReceipt saves the lines with it.

First Line

INSERT INTO "SalesReceiptLine" ("SalesReceiptLineItemRefListID", "SalesReceiptLineDesc", "SalesReceiptLineRate", "SalesReceiptLineAmount", "SalesReceiptLineTaxCodeRefListID", "FQSaveToCache") VALUES ('250000-933272656', 'Building permit 1', 1.00000, 1.00, '20000-999022286', 1)

Second Line

INSERT INTO "SalesReceiptLine" ("SalesReceiptLineItemRefListID", "SalesReceiptLineDesc", "SalesReceiptLineRate", "SalesReceiptLineAmount", "SalesReceiptLineTaxCodeRefListID", "FQSaveToCache") VALUES ('250000-933272656', 'Building permit 2', 2.00000, 2.00, '20000-999022286', 1)

Third Line

INSERT INTO "SalesReceiptLine" ("SalesReceiptLineItemRefListID", "SalesReceiptLineDesc", "SalesReceiptLineRate", "SalesReceiptLineAmount", "SalesReceiptLineTaxCodeRefListID", "FQSaveToCache") VALUES ('250000-933272656', 'Building permit 3', 3.00000, 3.00, '20000-999022286', 1)

Shared Header Information

INSERT INTO "SalesReceipt" ("CustomerRefListID", "TxnDate", "RefNumber", "BillAddressAddr1", "BillAddressAddr2", "BillAddressCity", "BillAddressCounty", "BillAddressPostalCode", "BillAddressCountry", "IsPending", "DueDate", "ShipDate", "ItemSalesTaxRefListID", "Memo", "IsToBePrinted", "CustomerTaxCodeRefListID") VALUES ('470001-1071525403', {d'2021-10-01'}, '1', 'Brad Lamb', '1921 Appleseed Lane', 'Bayshore', 'CA', '94326', 'USA', 0, {d'2021-10-31'}, {d'2021-10-01'}, '2E0000-933272656', 'Memo Test', 0, '10000-999022286')

 

Special Instructions about Add Lines to a Single Line Sales Receipt

      FileMaker Pro and some other products connect to and disconnect from QODBC after running every SQL statement, so it's impossible to do multiple Sales Receipt lines using our standard FQSaveToCache method. Where this is the case, you will have to use Plan B to create a single-line Sales Receipt and add additional lines to it afterward, as per the following example.

Create a New Line SalesReceipt

INSERT INTO "SalesReceiptLine" ("CustomerRefListID", "RefNumber", "SalesReceiptLineItemRefListID", "SalesReceiptLineDesc", "SalesReceiptLineRate", "SalesReceiptLineAmount", "SalesReceiptLineSalesTaxCodeRefListID", "FQSaveToCache") VALUES ('460000-1040154385', 'XYZ01', '8C0003-1044564690', 'Building permit 1', 1.00000, 1.00, '20000-1011136881', 0)

Locate the TxnID of the Sales Receipt

      We can add two new lines to the SalesReceipt using the TxnID of the SalesReceipt we just created. The TxnID is found by searching the SalesReceiptLine table for the SalesReceipt by doing the following:

      select TxnID, TxnDate, RefNumber, CustomerRefFullName from SalesReceiptLine
      where CustomerRefListID='460000-1040154385' and RefNumber = 'XYZ01'

Where 1 is the RefNumber for the Sales Receipt, we just created.

Add Two New Lines to the Existing SalesReceipt

First Line

INSERT INTO "SalesReceiptLine" ("TxnID", "SalesReceiptLineItemRefListID", "SalesReceiptLineDesc", "SalesReceiptLineRate", "SalesReceiptLineAmount", "SalesReceiptLineSalesTaxCodeRefListID")
VALUES ('8BFF-1481801824', '8B0001-1044564368', 'Building permit 2', 2.00000, 2.00, '20000-1011136881')

Second Line

INSERT INTO "SalesReceiptLine" ("TxnID", "SalesReceiptLineItemRefListID", "SalesReceiptLineDesc", "SalesReceiptLineRate", "SalesReceiptLineAmount", "SalesReceiptLineSalesTaxCodeRefListID")
VALUES ('8BFF-1481801824', '520004-1044568031', 'Building permit 3', 3.00000, 3.00, '20000-1011136881')

Result in QuickBooks

      We now have the following Sales Receipt with three lines in QuickBooks:

 

An Example of Sum all Sales Receipts for a Selected Date Range

Query in QODBC

SELECT SalesReceiptLine.CustomerRefFullName, Sum(SalesReceiptLine.TotalAmount) AS SumOfTotalAmount
FROM SalesReceiptLine unoptimized
WHERE (((SalesReceiptLine.TxnDate)>={d'2021-12-01'})) and (((SalesReceiptLine.TxnDate)<{d'2021-12-31'}))
GROUP BY SalesReceiptLine.CustomerRefFullName

And change the greater than or equal (>=) and the less than (<) dates to the date range you wish to use.

(173 vote(s))
Helpful
Not helpful
Comments (2)
Barb Gustin
11 September 2012 07:39 PM
I cliked on "Create Sales Invoice" but it went to "Create Sales Receipt"! Weird. I need to know how to create not one but multiple invoives from a database table.
Jack
08 July 2014 11:14 AM
Hi Barb,

Please refer below mentioned article for creating Invoices:

http://support.flexquarters.com/esupport/index.php?/Default/Knowledgebase/Article/View/2389/0/how-to-create-invoices-using-qodbc

http://support.flexquarters.com/esupport/index.php?/Knowledgebase/Article/View/1007/61/how-to-create-multiple-invoices-with-multiple-line-items-using-access-2003-and-qodbc
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).