Member Login

Username
Password
Forget Password
New Sign Up
Search Forum

Buy Support
Incidents

If you can't find your answer in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us private or confidential information:

Click Here
If you can't login and post questions or you are having trouble viewing forum posts:
Click Here
Callback
Support

If you live in USA, UK, Canada, Australia or New Zealand, you can leave us details on your question and request us to call you back and discuss them with you personally  (charges apply).

Click Here
 
Buy Support
Incidents
If you can't find your answer in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us private or confidential information:
Click Here

Forum : How do I create a Sales Receipt using QODBC?Search Forum

Forum Home > QODBC - ODBC Driver for QuickBooks > QODBC SQL Sample Scripts Forum

 New Topic 
 
 Post Reply 
[1]  
 How do I create a Sales Receipt using QODBC? 
 Author   Message 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2006-02-21 11:16:50

USA QUICKBOOKS USERS

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

insert into salesreceiptline (CustomerRefListID, TemplateRefListID, TxnDate,
IsPending, DueDate, IsToBePrinted, DepositToAccountRefListID,
BillAddressAddr1, BillAddressAddr2, BillAddressAddr3,
BillAddressCity, BillAddressState, BillAddressPostalCode,
SalesReceiptLineItemRefFullName, SalesReceiptLineDesc,
SalesReceiptLineRate, SalesReceiptLineAmount,
SalesReceiptLineSalesTaxCodeRefListID)
values ('AC0000-1197757899', '90000-933290726', {d'2006-05-25'},
0, {d'2006-05-25'}, 0, '80000-933270541','Joe Mama Inc 2',
'123 Main St.', 'Suite 450', 'Chicago', 'IL', '60602',
'Support Incident', 'Priority Support Incident 10 Pack Subscription',
275.00, 275.00, '20000-999022286')

and results in the following Sales Receipt in QuickBooks 2006 Premier:


 NON-USA QUICKBOOKS USERS

This creates one SalesReceipt with three lines. Note the FQSaveToCache field, set to True except on the last 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)

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)

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)


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

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

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

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


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.

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

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

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

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

 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2006-06-08 15:32:21

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

This creates a new one line SalesReceipt:

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

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

select TxnID, TxnDate, RefNumber, CustomerRefFullName from SalesReceiptLine
where CustomerRefFullName='Lamb, Brad:Room Addition'  and RefNumber = '1'

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

This adds two new lines to the existing SalesReceipt:

INSERT INTO "SalesReceiptLine" ("TxnID", "SalesReceiptLineItemRefListID", "SalesReceiptLineDesc", "SalesReceiptLineRate", "SalesReceiptLineAmount", "SalesReceiptLineSalesTaxCodeRefListID")
VALUES ('5C60-1197756901', '250000-933272656', 'Building permit 2', 2.00000, 2.00, '20000-999022286')

INSERT INTO "SalesReceiptLine" ("TxnID", "SalesReceiptLineItemRefListID", "SalesReceiptLineDesc", "SalesReceiptLineRate", "SalesReceiptLineAmount", "SalesReceiptLineSalesTaxCodeRefListID")
VALUES ('5C60-1197756901', '250000-933272656', 'Building permit 3', 3.00000, 3.00, '20000-999022286')

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

 

  Top 
 New Topic 
 
 Post Reply 
[1]  

Jump to