ODBC Driver for Zoho Books

Build 24.0.9060

SalesOrderDetails

List, add, update and delete a sales order.

Table Specific Information

Select

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

  • SalesorderId supports the '=' and IN operators.

NOTE: SalesorderId is required to query SalesOrderDetails.

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

For example: 

    SELECT * FROM SalesOrderDetails WHERE SalesorderId = '1894553000000077349'
	SELECT * FROM SalesOrderDetails WHERE SalesorderId IN (SELECT SalesorderId FROM SalesOrders)
	SELECT * FROM SalesOrderDetails WHERE SalesorderId IN ('1894553000000077349','1894553000000077350')

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 SalesOrderLineItems#TEMP (Name, itemid, rate, quantity) VALUES ('Cloth-Jeans' , '3285934000000104097' , '1700' , '1')

INSERT INTO SalesorderDetails (CustomerId, LineItems) VALUES ('3285934000000104002', SalesorderLineItems#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 SalesorderDetails (CustomerId, LineItems) VALUES ('3285934000000104023', '[{"Name":"Cloth-Jeans3", "ItemId":"3285934000000104097", "Rate":"1700", "Quantity":"1"}]')

Update

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

INSERT INTO SalesOrderLineItems#TEMP (Name, itemid, rate, quantity) VALUES ('Cloth-Jeans', '3285934000000104097', '1700', '1')

Update SalesorderDetails SET CustomerId = '3285934000000104002', LineItems = 'SalesorderLineItems#TEMP' WHERE SalesorderID = '3285934000000259151'

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 SalesorderDetails SET CustomerId = '3285934000000085043', LineItems = '[{"Name":"Cloth-Jeans", "ItemId":"3285934000000104097", "Rate":"1700", "Quantity":"1"}]' WHERE SalesorderID = '3285934000000259151'

Delete

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

DELETE FROM SalesorderDetails WHERE SalesorderID = '3285934000000259151'

Columns

Name Type ReadOnly References SupportedOperators Description
SalesorderId [KEY] String True

SalesOrders.SalesorderId

Id of sales order.

AccountIdentifier String True

Account identifier for sales order.

Adjustment Decimal False

Adjustments made to the sales order.

AdjustmentDescription String False

Description of adjustments made to the sales order.

ApproverId String True

Users.UserId

Id of an approver.

ApproversList String True

Approvers list.

AttachmentName String True

Name of the attachment.

Balance String True

Balance.

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 in Base Currency.

BcyDiscountTotal Decimal True

Total amount of discount in Base Currency.

BcyShippingCharge Decimal True

Shipping charge applied in Base Currency.

BcySubTotal Decimal True

Sub total of Base Currency.

BcyTaxTotal Decimal True

Total tax of Base Currency.

BcyTotal Decimal True

Total Base Currency.

BillingAddressId String False

Id of the Billing Address.

ShippingAddressId String False

Id of the Shipping Address.

BillingAddress String True

Billing address of a sales order.

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 sales order can be send in mail.

ColorCode String True

Color code for sales order.

ContactCreditLimit Decimal True

ContactCreditLimit.

ContactCustomerBalance Decimal True

ContactCustomerBalance.

ContactIsCreditLimitMigrationCompleted Boolean True

ContactIsCreditLimitMigrationCompleted.

ContactUnusedCustomerCredits Decimal True

ContactUnusedCustomerCredits.

ContactCategory String True

Category of a contact.

ContactPersonDetails String False

Contact details of persons of a contact.

ContactPersons String False

Contact persons of a contact.

CreatedById String True

Users.UserId

Id of a user who has created sales order.

CreatedByName String True

Name of a user who has created sales order.

CreatedByEmail String True

Email of a user who has created sales order.

CreatedDate Date True

Date at which the sales order was created.

CreatedTime Datetime True

Time at which the sales order was created.

CurrencyCode String True

Currency code of the customer's currency.

CurrencyId String False

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 a sales order.

CurrentSubStatusId String True

Current sub status Id of a sales order.

CustomerId String False

Contacts.ContactId

Id of the customer or vendor.

CustomerName String True

Name of the customer or vendor.

CustomFields String False

Custom fields of the contact.

Date Date False

Date of a sales order.

DeliveryMethod String False

Method of a delivery.

DeliveryMethodId String True

Method Id of a delivery.

Discount String False

Discount given to specific item in sales order.

DiscountAppliedOnAmount Decimal True

Amount in which discount was applied.

DiscountPercent Double True

Percentage applied for discount.

DiscountTotal Decimal True

Total amount get on discount.

DiscountType String False

Type of discount applied in sales order.

EntityTags String True

EntityTags.

EstimateId String False

Estimates.EstimateId

Id of an estimate.

ExchangeRate Decimal False

Exchange rate of the currency.

GstNo String False

GST number.

GstTreatment String False

Choose whether the estimate is GST registered/unregistered/consumer/overseas. .

HasDiscount Boolean True

Check if the sales order quantity has discount.

HasQtyCancelled Boolean True

Check if the sales order quantity has been cancelled.

HasShippingAddress Boolean True

Check if the sales order quantity has shipping address.

IntegrationId String True

Id of sales order integration.

InvoiceConversionType String True

Type of invoice conversion applied for sales order.

InvoicedStatus String True

Status of invoiced sales order.

IsDiscountBeforeTax Boolean False

Check if the sales order can be applied discount before tax.

IsUpdateCustomer Boolean False

Boolean to update billing address of customer.

IsEmailed Boolean True

Check if the sales order is emailed.

IsInclusiveTax Boolean False

Check if the sales order is inclusive tax.

IsPreGst Boolean True

Check if pre GST is applied.

LastModifiedById String True

Users.UserId

Id of the user last modified.

LastModifiedTime Datetime True

The time of last modification of the sales order.

LineItems String False

Line items of an estimate.

MerchantId String False

Id of the merchant.

MerchantName String True

Name of the merchant.

Notes String False

Notes of sales order.

NotesDefault String False

Default Notes for the Sales Order.

OfflineCreatedDateWithTime Datetime True

OfflineCreatedDateWithTime.

OrderStatus String True

Status of order.

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.

PlaceOfSupply String False

The place of supply is where a transaction is considered to have occurred for VAT purposes.

ReferenceNumber String False

Reference number of a sales order.

RoundoffValue Decimal True

Round Off value of sales order.

SalesorderNumber String False

Number of sales order.

SalespersonId String False

Id of a sales person.

SalespersonName String False

Name of the sales person.

ShipmentDate Date False

Date when shipment was done for sale order.

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 details of a shipping address.

ShippingAddressZip String True

Zip code of a shipping address.

ShippingCharge Decimal False

Shipping charge.

Status String False

Status of the sales order.

SubTotal Decimal True

Sub total of sales orders.

SubTotalInclusiveOfTax Decimal True

Subtotal amount which are inclusive of tax.

SubmittedBy String True

Detail of the user who has submitted the sales order.

SubmittedDate Date True

Date when submission was made of sales order.

SubmitterId String True

Users.UserId

Id of a submitter.

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 template.

Terms String False

Terms and Conditions apply of a sales order.

TermsDefault String False

Default Terms of the Sales Order.

Total Decimal True

Total of sales order.

TransactionRoundingType String True

Type of round off used for transaction.

VatTreatment String False

VAT treatment for the estimates.

TaxTreatment String False

VAT treatment for the Estimate.

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
TotalFiles Integer

Total number of files.

Doc String

Document that is to be attached.

Copyright (c) 2024 CData Software, Inc. - All rights reserved.
Build 24.0.9060