EstimateDetails

To list, add, update and delete details of an estimate.

Table Specific Information

Select

The connector uses the Zoho Books API to process WHERE clause conditions built with the following column and operator:

EstimateId supports the '=' and IN operators.

NOTE: EstimateId is required to query EstimateDetails.

The rest of the filter is executed client-side in the connector.

For example:

    SELECT * FROM EstimateDetails WHERE EstimateId = '1894553000000077244'
	SELECT * FROM EstimateDetails WHERE EstimateId IN (SELECT EstimateId FROM Estimates)
	SELECT * FROM EstimateDetails WHERE EstimateId IN ('1894553000000077244','1894553000000077245')

Insert

INSERT can be executed by specifying the Customerid and lineitems columns. The columns that are not read-only can be inserted optionally. The following is an example of how to insert into this table.

INSERT INTO EstimateLineItems#TEMP (Name, itemid) VALUES ('Cloth-Jeans1', '3285934000000104097')

INSERT INTO EstimateDetails (Customerid, lineitems) VALUES ('3285934000000104002', EstimateLineItems#Temp)

INSERT can also be executed by specifying the LineItems column as a JSON array. The following is an example of how to insert using JSON array into this table.

INSERT INTO EstimateDetails (CustomerId, LineItems) VALUES ('3255827000000081003', '[{"Name":"Cloth-Jeans3", "ItemId":"3285934000000104097"}]')

Update

UPDATE can be executed by specifying the EstimateId in the WHERE Clause. The columns that are not read-only can be updated. For example:

INSERT INTO EstimateLineItems#TEMP (Name, itemid) VALUES ('Cloth-Jeans12', '3285934000000104097')

UPDATE EstimateDetails SET Customerid = '3285934000000104002', lineitems = 'EstimateLineItems#Temp'  WHERE EstimateId = '3285934000000263048'

UPDATE can also be executed by specifying the LineItems column as a JSON array. The following is an example of how to update using JSON array into this table.

UPDATE EstimateDetails SET CustomerId = '3285934000000085043', LineItems = '[{"Name":"Cloth-Jeans", "ItemId":"3285934000000104097"}]' WHERE EstimateId = '3285934000000263048'

Delete

DELETE can be executed by specifying the EstimateId in the WHERE Clause For example:

DELETE FROM EstimateDetails WHERE EstimateId = '3285934000000263048'

Columns


Name	Type	ReadOnly	References	SupportedOperators	Description
EstimateId [KEY]	String	True	Estimates.EstimateId		Id of an estimate.
Adjustment	Decimal	False			Adjustments made to the estimate.
AdjustmentDescription	String	False			Description of adjustments made to the estimate.
AllowPartialPayments	Boolean	True			Check if estimate allows partial payments.
ApproverId	String	True	Users.UserId		Id of an approver.
AttachmentName	String	True			Name of the attachment.
AvataxUseCode	String	False			Used to group like customers for exemption purposes. It is a custom value that links customers to a tax rule.
AvataxExemptNo	String	False			Exemption certificate number of the customer.
BcyAdjustment	Decimal	True			Adjustment made for base currency.
BcyDiscountTotal	Decimal	True			Total amount get on discount for base currency.
BcyShippingCharge	Decimal	True			Shipping charge applied for base currency.
BcySubTotal	Decimal	True			Sub total of base currency.
BcyTaxTotal	Decimal	True			Total tax applied for the base currency.
BcyTotal	Decimal	True			Total Base Currency.
BillingAddress	String	True			Billing address of a estimate.
BillingAddressAttention	String	True			Name of a person in billing address.
BillingAddressCity	String	True			City of a billing address.
BillingAddressCountry	String	True			Country of a billing address.
BillingAddressFax	String	True			Fax of a billing address.
BillingAddressPhone	String	True			Phone number of a billing address.
BillingAddressState	String	True			State of a billing address.
BillingAddressStreet2	String	True			Street two of a billing address.
BillingAddressZip	String	True			ZIP code of a billing address.
CanSendInMail	Boolean	True			Check if the estimate can be send in mail.
ClientViewedTime	Datetime	True			Time when client viewed the estimate.
ColorCode	String	True			Color code for estimate.
ContactPersons	String	False			Contact persons of a contact.
ContactCategory	String	True			Category for contact.
CreatedById	String	True	Users.UserId		Id of a user who has created estimate.
CreatedTime	Datetime	True			Time at which the estimate was created.
CustomFields	String	False			Custom fields of the contact.
CurrencyCode	String	True			Currency code of the customer's currency.
CurrencyId	String	True	Currencies.CurrencyId		Currency Id of the customer's currency.
CurrencySymbol	String	True			Currency symbol of the customer's currency.
CurrentSubStatus	String	True			Current sub status of an estimate .
CurrentSubStatusId	String	True			Current sub status Id of an estimate .
CustomerDefaultBillingAddress	String	True			Customer default billing address of an estimate.
CustomerDefaultBillingAddressCity	String	True			City of a customer default billing address.
CustomerDefaultBillingAddressCountry	String	True			Country of a customer default billing address.
CustomerDefaultBillingAddressFax	String	True			Fax of a customer default billing address.
CustomerDefaultBillingAddressPhone	String	True			Phone number of a customer default billing address.
CustomerDefaultBillingAddressState	String	True			State of a customer default billing address.
CustomerDefaultBillingAddressStateCode	String	True			State code of a customer default billing address.
CustomerDefaultBillingAddressStreet2	String	True			Street two of a customer default billing address.
CustomerDefaultBillingAddressZip	String	True			ZIP code of a customer default billing address.
CustomerId	String	False	Contacts.ContactId		Id of the customer or vendor.
CustomerName	String	True			Name of the customer or vendor.
Date	Date	False			Date of an estimate.
Discount	String	False			Discount applied for estimate.
DiscountAppliedOnAmount	Decimal	True			Discount applied on amount for estimate.
DiscountPercent	Decimal	True			Percent of discount applied for estimate.
DiscountTotal	Decimal	True			Total discount applied for estimate.
DiscountType	String	False			Type of discount applied for estimate.
EstimateNumber	String	False			Number of estimate.
EstimateUrl	String	True			URL of estimate.
ExchangeRate	Decimal	False			Exchange rate of the currency.
ExpiryDate	Date	False			Expiration date of estimate.
InvoiceConversionType	String	True			Conversion type of an invoice in estimate.
IsConvertedToOpen	Boolean	True			Check if the estimate is converted to open.
IsDiscountBeforeTax	Boolean	False			Check if the discount is applied before tax. .
IsInclusiveTax	Boolean	False			Check if the expense is inclusive tax.
IsPreGst	Boolean	True			Check if estimate includes pre GST.
IsTransactionCreated	Boolean	True			Check if the transaction os created for estimate.
IsViewedByClient	Boolean	True			Check if the estimate is viewed by client.
LastModifiedById	String	True	Users.UserId		Id of the user last modified.
LastModifiedTime	Datetime	True			The time of last modification of the estimate.
LineItems	String	False			Line items of an estimate.
Notes	String	False			Notes of Estimate.
Orientation	String	True			Orientation of page.
PageHeight	String	True			Height of page.
PageWidth	String	True			Width of page.
PricePrecision	Integer	True			The precision for the price.
ProjectId	String	False			Id of a project.
ProjectName	String	True			Name of a project.
ProjectCustomerId	String	True			Id of a customer.
ProjectCustomerName	String	True			Name of a customer.
ProjectDescription	String	True			Details about the project.
ProjectStatus	String	True			Status of the project.
ProjectBillingType	String	True			Type of billing.
ProjectRate	Decimal	True			Overall rate of the project.
ReferenceNumber	String	False			Reference number of estimates.
PlaceOfSupply	String	False			The place of supply is where a transaction is considered to have occurred for VAT purposes.
TaxSpecification	String	True			Working of tax when specifying special tax options and tax methods for earnings codes.
VatTreatment	String	False			VAT treatment for the estimates.
IsTaxable	Boolean	True			Check if estimate is taxble.
GstNo	String	False			GST number.
GstTreatment	String	False			Choose whether the estimate is GST registered/unregistered/consumer/overseas. .
TaxTreatment	String	False			VAT treatment for the Estimate.
ReverseChargeTaxTotal	String	True			Total amount of tax reverse charge.
CanSendEstimateSms	String	True			Check if the estimate can send the estimate SMS.
RetainerPercentage	String	True			Percentage of the retainer in estimate.
AcceptRetainer	Boolean	True			Check if estimate can accept the retainer.
RoundoffValue	Decimal	True			Rounding off the values to precise number.
SalespersonId	String	False			Id of a sales person.
SalespersonName	String	False			Name of a sales person.
ShippingAddress	String	True			Shipment Address.
ShippingAddressAttention	String	True			Name of a person of shipping address.
ShippingAddressCity	String	True			City of a shipping address.
ShippingAddressCountry	String	True			Country of a shipping address.
ShippingAddressFax	String	True			Fax of a shipping address.
ShippingAddressPhone	String	True			Phone number of a shipping address.
ShippingAddressState	String	True			State of a shipping address.
ShippingAddressStreet2	String	True			Street two of a shipping address.
ShippingAddressZip	String	True			Zip of a shipping address.
ShippingCharge	Decimal	False			Shipping charge of estimates.
Status	String	True			Status of the estimate.
SubTotal	Decimal	True			Sub total of estimates.
SubTotalExclusiveOfDiscount	Decimal	True			Subtotal amount which are exclusive of discount.
SubTotalInclusiveOfTax	Decimal	True			Subtotal amount which are inclusive of tax.
SubmittedBy	String	True			Detail of the user who has submitted the estimate.
SubmittedDate	Date	True			Date when estimate was submitted.
SubmitterId	String	True	Users.UserId		Id of the submitter.
TaxId	String	False	Taxes.TaxId		Id of the tax
TaxTotal	Decimal	True			Total amount of tax.
TemplateId	String	False			Id of a template.
TemplateName	String	True			Name of a template.
TemplateType	String	True			Type of a template.
Terms	String	False			Terms and Conditions apply of a estimate.
Total	Decimal	True			Total of estimates.
TransactionRoundingType	String	True			Type of round off used for transaction.
Unit	String	False			Unit of the line item e.g. kgs, Nos.
ApproversList	String	True			List of approvers for the estimate.
BcyShippingChargeTax	Decimal	True			Shipping charge tax for base currency.
ContactPersonsAssociated	String	True			Contact persons associated with the estimate.
ContactPersonsDetails	String	True			Details of contact persons for the estimate.
CreatedDate	Date	True			Date when the estimate was created.
DispatchFromAddress	String	True			Address from which the estimate is dispatched.
Documents	String	True			Documents associated with the estimate.
EstimateType	String	True			Type of estimate.
InvoiceIds	String	True			IDs of associated invoices.
InvoicedAmount	Decimal	True			Amount that has been invoiced.
IsProgressiveQuote	Boolean	True			Check if the estimate is a progressive quote.
IsViewedInMail	Boolean	True			Check if the estimate was viewed in mail.
MailFirstViewedTime	Datetime	True			Time when the estimate was first viewed in mail.
MailLastViewedTime	Datetime	True			Time when the estimate was last viewed in mail.
PaymentOptions	String	True			Payment options for the estimate.
RetainerInvoices	String	True			Retainer invoices associated with the estimate.
SalesOrders	String	True			Sales orders associated with the estimate.
ShippingChargeAccountId	String	True			ID of the shipping charge account.
ShippingChargeAccountName	String	True			Name of the shipping charge account.
ShippingChargeExclusiveOfTax	Decimal	True			Shipping charge amount exclusive of tax.
ShippingChargeExclusiveOfTaxFormatted	String	True			Formatted shipping charge amount exclusive of tax.
ShippingChargeInclusiveOfTax	Decimal	True			Shipping charge amount inclusive of tax.
ShippingChargeInclusiveOfTaxFormatted	String	True			Formatted shipping charge amount inclusive of tax.
ShippingChargeTax	Decimal	True			Shipping charge tax amount.
ShippingChargeTaxExemptionCode	String	True			Shipping charge tax exemption code.
ShippingChargeTaxExemptionId	String	True			Shipping charge tax exemption ID.
ShippingChargeTaxFormatted	String	True			Formatted shipping charge tax amount.
ShippingChargeTaxId	String	True			ID of the shipping charge tax.
ShippingChargeTaxName	String	True			Name of the shipping charge tax.
ShippingChargeTaxPercentage	Decimal	True			Percentage of the shipping charge tax.
ShippingChargeTaxType	String	True			Type of the shipping charge tax.
SubStatuses	String	True			Sub-statuses of the estimate.
SubjectContent	String	True			Content of the estimate subject.
SubmittedByEmail	String	True			Email of the user who submitted the estimate.
SubmittedByName	String	True			Name of the user who submitted the estimate.
SubmittedByPhotoUrl	String	True			Photo URL of the user who submitted the estimate.
SubscriptionIds	String	True			IDs of associated subscriptions.
TaxRounding	String	True			Tax rounding type for the estimate.
Taxes	String	True			Taxes applied to the estimate.
TdsAmount	Decimal	True			TDS amount.
TdsCalculationType	String	True			Type of TDS calculation.
TdsOverridePreference	String	True			TDS override preference.
TdsPercent	Decimal	True			TDS percentage.
TdsSection	String	True			TDS section.
TdsSummary	String	True			Summary of TDS.
TdsTaxId	String	False			TDS tax ID.
TdsTaxName	String	True			TDS tax name.
UninvoicedAmount	Decimal	True			Amount that is uninvoiced.
ZcrmPotentialId	String	True			ID of the Zoho CRM potential.
ZcrmPotentialName	String	True			Name of the Zoho CRM potential.
CustomBody	String	False			Custom body content for the estimate.
CustomSubject	String	False			Custom subject for the estimate.
TaxExemptionId	String	False			ID of the tax exemption.
TaxAuthorityId	String	False			ID of the tax authority.
IsReverseChargeApplied	Boolean	True			Check if reverse charge is applied.
LineItems	String	True			Line Item of the Estimate.
IgnoreAutoNumberGeneration	Boolean	False			Flag to ignore auto number generation.

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.


Name	Type	Description
Send	Boolean	Send the invoice to the contact person(s) associated with the invoice. Allowed values true and false.

Tableau Connector for Zoho Books