Estimates

Handles QuickBooks Estimates, including creation, updates, deletion, and queries for tracking potential sales.

Table Specific Information

Estimates may be inserted, queried, or updated via the Estimates or EstimateLineItems tables. Estimates may be deleted by using the Estimates table.

This table has a Custom Fields column. See the Custom Fields page for more information.

Select

The following filters support server-side execution. Other filters are executed client-side.

QuickBooks allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can typically be used with only the equals or = comparison. The available columns for Estimates are Id, Date, TimeModified, ReferenceNumber, CustomerName, and CustomerId. TimeModified and Date may be used with the >, >=, <, <=, or = conditions and may be used twice to specify a range. ReferenceNumber may be used with the = or LIKE conditions to establish a starts-with, ends-with, or contains syntax. For example:

SELECT * FROM Estimates WHERE TimeModified > '1/1/2011' AND TimeModified < '2/1/2011' AND ReferenceNumber LIKE '%12345%'

Insert

To add an Estimate, specify a Customer and at least one Line Item. The ItemAggregate column may be used to specify an XML aggregate of Line Item data. The columns that may be used in these aggregates are defined in the EstimateLineItems table and it starts with Item. For example, the following will insert a new Estimate with two Line Items:

INSERT INTO Estimates (CustomerName, ItemAggregate) 
VALUES ('Abercrombie, Kristy', 
'<EstimateLineItems>
<Row><ItemName>Repairs</ItemName><ItemQuantity>1</ItemQuantity></Row>
<Row><ItemName>Removal</ItemName><ItemQuantity>2</ItemQuantity></Row>
</EstimateLineItems>')

To insert subitems, set the ItemName field to the FullName of the item; for example, '<Row><ItemName>Subs:Carpet</ItemName><ItemQuantity>0</ItemQuantity></Row>'

Columns


Name	Type	ReadOnly	References	ColumnSize	Description
ID [KEY]	String	True		255	A unique identifier for the estimate.
ReferenceNumber	String	False		21	A user-defined reference number for the estimate transaction.
TxnNumber	Integer	True			A Quickbooks assigned transaction number for this estimate, distinct from its internal ID.
CustomerName	String	False		1000	The name of the customer associated with this estimate. This is required for creating an estimate.
CustomerId	String	False	Customers.ID	255	The internal ID of the customer linked to this estimate. This is required for creating an estimate.
Date	Date	False			The date when the estimate was created.
Memo	String	False		5000	Additional notes or information about this estimate.
TotalAmount	Decimal	True			The total monetary value of the estimate, including all items and charges.
Message	String	False		101	A custom message or note directed to the customer.
MessageId	String	False	CustomerMessages.ID	255	The internal ID of the customer message.
Class	String	False		1000	The classification assigned to this estimate for tracking or reporting purposes.
ClassId	String	False	Class.ID	255	The unique ID of the class assigned to this estimate.
SalesRep	String	False		5	The initials or reference to the sales representative associated with this estimate.
SalesRepId	String	False	SalesReps.ID	255	The unique ID of the sales representative linked to this estimate.
FOB	String	False		13	Freight on board location, indicating the shipping origin or transfer point.
BillingAddress	String	True			The complete billing address associated with this estimate.
BillingLine1	String	False		500	The first line of the billing address.
BillingLine2	String	False		500	The second line of the billing address.
BillingLine3	String	False		500	The third line of the billing address.
BillingLine4	String	False		500	The fourth line of the billing address.
BillingLine5	String	False		41	The fifth line of the billing address.
BillingCity	String	False		255	The city listed in the billing address.
BillingState	String	False		255	The state or region listed in the billing address.
BillingPostalCode	String	False		30	The postal or ZIP code for the billing address.
BillingCountry	String	False		255	The country listed in the billing address.
BillingNote	String	False		41	Additional notes or details about the billing address.
ShippingAddress	String	True			The complete shipping address associated with this estimate.
ShippingLine1	String	False		500	The first line of the shipping address.
ShippingLine2	String	False		500	The second line of the shipping address.
ShippingLine3	String	False		500	The third line of the shipping address.
ShippingLine4	String	False		500	The fourth line of the shipping address.
ShippingLine5	String	False		41	The fifth line of the shipping address.
ShippingCity	String	False		255	The city listed in the shipping address.
ShippingState	String	False		255	The state or region listed in the shipping address.
ShippingPostalCode	String	False		30	The postal or ZIP code for the shipping address.
ShippingCountry	String	False		255	The country listed in the shipping address.
ShippingNote	String	False		41	Additional notes or details about the shipping address.
Subtotal	Decimal	True			The total amount of all line items before taxes or discounts are applied.
Tax	Double	True			The total sales tax applied to this estimate.
TaxItem	String	False		100	The name of the tax item applied to this estimate.
TaxItemId	String	False	SalesTaxItems.ID	255	The unique ID of the tax item applied to this estimate.
TaxPercent	Double	True			The percentage rate of the sales tax applied to this estimate.
IsActive	Boolean	False			Indicates whether this estimate is currently active or not.
IsToBeEmailed	Boolean	False			Indicates whether this estimate is set to be emailed to the customer.
IsTaxIncluded	Boolean	False			Indicates whether the sales tax is included in the total amount.
PONumber	String	False		25	The purchase order number associated with this estimate.
Terms	String	False		100	The payment terms predefined in QuickBooks and applied to this estimate.
TermsId	String	False		255	The unique ID of the payment terms applied to this estimate.
Template	String	False		100	The name of the template applied to this estimate for formatting.
TemplateId	String	False	Templates.ID	255	The unique ID of the template applied to this estimate.
CustomerSalesTaxName	String	False		3	The name of the sales tax item associated with the customer linked to this estimate.
CustomerSalesTaxId	String	False	SalesTaxCodes.ID	255	The unique ID of the sales tax item associated with the customer.
ExchangeRate	Double	False			The exchange rate applied to this estimate, if dealing with foreign currency.
DueDate	Date	True			The date when payment for this estimate is due.
Other	String	False		29	Additional data or notes associated with this estimate.
ItemCount	Integer	True			The total number of line items in this estimate.
ItemAggregate	String	False		5000	A collection of all line items included in this estimate, represented as an aggregate.
TransactionCount	Integer	True			The total number of related transactions associated with this estimate.
TransactionAggregate	String	True		5000	A collection of related transaction data linked to this estimate.
CustomFields	String	False			Custom fields defined in QuickBooks for this estimate, formatted as XML.
EditSequence	String	True		16	A versioning identifier for tracking changes to this estimate.
TimeModified	Datetime	True			The date and time when this estimate was last updated.
TimeCreated	Datetime	True			The date and time when this estimate was initially created.

Pseudo-Columns

Pseudo Column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.