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

An Example of Creating Estimates for USA QuickBooks Users

Note: This creates an Estimate with Three Lines with all shared header information. Note the FQSaveToCache field, set to True except on the last line.

Note: The primary rule is to save the data to the child record first. The child record for each parent/child pair has all the data required by the parent record included in it.
You cannot insert into Estimate table without inserting the child table(s) like EstimateLine.  Please do not close connection between the Insert EstimateLine & Estimate otherwise you will not be able to get last inserted ID.

First Line

     INSERT INTO "EstimateLine" ("EstimateLineItemRefListID", "EstimateLineDesc",
     "EstimateLineRate", "EstimateLineAmount", "EstimateLineSalesTaxCodeRefListID",
     "FQSaveToCache") VALUES ('250000-933272656', 'Building permit 1',
     1.00000, 1.00, '20000-999022286', 1)


Note: 
 VB DEMO is to be used for testing of QODBC SQL queries only and is not a development tool.

 

Second Line

     INSERT INTO "EstimateLine" ("EstimateLineItemRefListID", "EstimateLineDesc",
     "EstimateLineRate", "EstimateLineAmount", "EstimateLineSalesTaxCodeRefListID",
     "FQSaveToCache") VALUES ('250000-933272656', 'Building permit 2',
     2.00000, 2.00, '20000-999022286', 1)

Third Line

     INSERT INTO "EstimateLine" ("EstimateLineItemRefListID", "EstimateLineDesc",
     "EstimateLineRate", "EstimateLineAmount", "EstimateLineSalesTaxCodeRefListID",
     "FQSaveToCache") VALUES ('250000-933272656', 'Building permit 3',
     3.00000, 3.00, '20000-999022286', 1)

Shared Header Information

     INSERT INTO "Estimate" ("CustomerRefListID", "TxnDate", "RefNumber",
     "BillAddressAddr1", "BillAddressAddr2", "BillAddressCity", "BillAddressState",
     "BillAddressPostalCode", "BillAddressCountry", "TermsRefListID", "DueDate",
     "ItemSalesTaxRefListID", "Memo", "CustomerSalesTaxCodeRefListID") VALUES
      ('470001-1071525403', {d'2002-10-01'}, '1', 'Brad Lamb', '1921 Appleseed Lane',
      'Bayshore', 'CA', '94326', 'USA', '10000-933272658', {d'2002-10-31'},
      '2E0000-933272656', 'Memo Test', '10000-999022286')

The resulting Estimate in QuickBooks 2006 Premier

 

An Example of Creating Estimates for NON-USA QuickBooks Users

Create One Estimate with Three Lines

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

First Line

INSERT INTO "EstimateLine" ("CustomerRefListID", "RefNumber", "EstimateLineItemRefListID", "EstimateLineDesc", "EstimateLineRate", "EstimateLineAmount", "EstimateLineTaxCodeRefListID", "FQSaveToCache") VALUES ('470001-1071525403', '1', '250000-933272656', 'Building permit 1', 1.00000, 1.00, '20000-999022286', 1)

Second Line

INSERT INTO "EstimateLine" ("CustomerRefListID", "RefNumber", "EstimateLineItemRefListID", "EstimateLineDesc", "EstimateLineRate", "EstimateLineAmount", "EstimateLineTaxCodeRefListID", "FQSaveToCache") VALUES ('470001-1071525403', '1', '250000-933272656', 'Building permit 2', 2.00000, 2.00, '20000-999022286', 1)

Third Line

INSERT INTO "EstimateLine" ("CustomerRefListID", "RefNumber", "EstimateLineItemRefListID", "EstimateLineDesc", "EstimateLineRate", "EstimateLineAmount", "EstimateLineTaxCodeRefListID", "FQSaveToCache") VALUES ('470001-1071525403', '1', '250000-933272656', 'Building permit 3', 3.00000, 3.00, '20000-999022286', 0)

Create Three Estimates with One Line Each

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

First Estimate

INSERT INTO "EstimateLine" ("CustomerRefListID", "RefNumber", "EstimateLineItemRefListID", "EstimateLineDesc", "EstimateLineRate", "EstimateLineAmount", "EstimateLineTaxCodeRefListID") VALUES ('470001-1071525403', '1', '250000-933272656', 'Building permit 1', 1.00000, 1.00, '20000-999022286')

Second Estimate

INSERT INTO "EstimateLine" ("CustomerRefListID", "RefNumber", "EstimateLineItemRefListID", "EstimateLineDesc", "EstimateLineRate", "EstimateLineAmount", "EstimateLineTaxCodeRefListID") VALUES ('470001-1071525403', '1', '250000-933272656', 'Building permit 2', 2.00000, 2.00, '20000-999022286')

Third Estimate

INSERT INTO "EstimateLine" ("CustomerRefListID", "RefNumber", "EstimateLineItemRefListID", "EstimateLineDesc", "EstimateLineRate", "EstimateLineAmount", "EstimateLineTaxCodeRefListID") VALUES ('470001-1071525403', '1', '250000-933272656', 'Building permit 3', 3.00000, 3.00, '20000-999022286')

Create One Estimate with Three Lines with All Shared Header Information

Note: This creates one Estimate with three lines. Note the FQSaveToCache field, set to True. Saving the Estimate saves the lines with it.

First Line

INSERT INTO "EstimateLine" ("EstimateLineItemRefListID", "EstimateLineDesc", "EstimateLineRate", "EstimateLineAmount", "EstimateLineTaxCodeRefListID", "FQSaveToCache") VALUES ('250000-933272656', 'Building permit 1', 1.00000, 1.00, '20000-999022286', 1)

Second Line

INSERT INTO "EstimateLine" ("EstimateLineItemRefListID", "EstimateLineDesc", "EstimateLineRate", "EstimateLineAmount", "EstimateLineTaxCodeRefListID", "FQSaveToCache") VALUES ('250000-933272656', 'Building permit 2', 2.00000, 2.00, '20000-999022286', 1)

Third Line

INSERT INTO "EstimateLine" ("EstimateLineItemRefListID", "EstimateLineDesc", "EstimateLineRate", "EstimateLineAmount", "EstimateLineTaxCodeRefListID", "FQSaveToCache") VALUES ('250000-933272656', 'Building permit 3', 3.00000, 3.00, '20000-999022286', 1)

Shared Header Information

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

Instructions

     For multi-line Estimates, some may find it easier to not include the header information with each line item, but rather to have separate smaller commands for each of the Estimate Lines and a final big INSERT for the Estimate header with all the Billing address details if required. The last example above shows a billing address and memo being added to the Estimate via the Estimate header table.

Related Data Description

     All the values used are found on the tables of their name. Below are some examples.

EstimateLineItemRefListID is '250000-933272656' and found like this:

     Select ListId, FullName from Item where FullName like 'P%'

EstimateLineSalesTaxCodeRefListID is '20000-999022286' and found like this:

      Select ListId, Name from SalesTaxCode where Name like 'N%'

CustomerRefListID is '470001-1071525403' and found like this:

      Select ListId, FullName from Customer where FullName like 'Lamb%'

ItemSalesTaxRefListID is '2E0000-933272656' and found like this:

     Select ListId, Name from ItemSalesTax where Name like 'San%'

CustomerSalesTaxCodeRefListID is '10000-999022286' and found like this:

      Select ListId, Name from SalesTaxCode where Name like 'T%'

(161 vote(s))
Helpful
Not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).