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 first to save the data to the child record. The child record for each parent/child pair includes all the data required by the parent record.
You cannot insert into the Estimate table without inserting the child table(s) like EstimateLine. Please do not close the connection between the Insert EstimateLine & Estimate; otherwise, you will not be able to get the last inserted ID.

First Line

     INSERT INTO "EstimateLine" ("EstimateLineItemRefListID", "EstimateLineDesc",
     "EstimateLineRate", "EstimateLineAmount", "EstimateLineSalesTaxCodeRefListID",
     "FQSaveToCache") VALUES ('670004-1044572237', 'Building permit 1',
     1.00000, 1.00, '10000-1011136881', 1)

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

Second Line

     INSERT INTO "EstimateLine" ("EstimateLineItemRefListID", "EstimateLineDesc",
     "EstimateLineRate", "EstimateLineAmount", "EstimateLineSalesTaxCodeRefListID",
     "FQSaveToCache") VALUES ('670004-1044572237', 'Building permit 2',
     2.00000, 2.00, '10000-1011136881', 1)

Third Line

     INSERT INTO "EstimateLine" ("EstimateLineItemRefListID", "EstimateLineDesc",
     "EstimateLineRate", "EstimateLineAmount", "EstimateLineSalesTaxCodeRefListID",
     "FQSaveToCache") VALUES ('670004-1044572237', 'Building permit 3',
     3.00000, 3.00, '10000-1011136881', 1)

Shared Header Information

     INSERT INTO "Estimate" ("CustomerRefListID", "TxnDate", "RefNumber",
     "BillAddressAddr1", "BillAddressAddr2", "BillAddressCity", "BillAddressState",
     "BillAddressPostalCode", "BillAddressCountry", "TermsRefListID", "DueDate",
     "ItemSalesTaxRefListID", "Memo", "CustomerSalesTaxCodeRefListID") VALUES
      ('4C0000-1040154668', {d'2021-12-01'}, '1', 'Brad Lamb', '1921 Appleseed Lane,
      'Bayshore', 'CA', '94326', 'USA', '50000-898307888', {d'2021-12-31'},
      '80000097-1481806775', 'Memo Test', '10000-1011136881')

The resulting Estimate in QuickBooks 2022 Enterprise

An Example of Creating Estimates for the USA and NON-USA QuickBooks Users (Header information in each line)

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 not to 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 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 '670004-1044572237' and found like this:

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

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

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

CustomerRefListID is '4C0000-1040154668' and found like this:

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

ItemSalesTaxRefListID is '80000097-1481806775' and found like this:

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

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

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