Adding a Connection to Zoho Books

1. In the application console, navigate to the Connections page.
2. At the Add Connections panel, select the icon for the connection you want to add.
3. If the Zoho Books icon is not available, click the Add More icon to download and install the Zoho Books connector from the CData site.

For required properties, see the Settings tab.

For connection properties that are not typically required, see the Advanced tab.

Connecting to Zoho Books

You can refine the exact Zoho Books data retrieved using the following connection properties:

• OrganizationId (optional): The Id associated with the specific Zoho Books organization that you wish to connect to.
  • If the value of Organization Id is not specified in the connection string, then the Sync App automatically retrieves all available organizations and selects the first organization Id as the default.
• AccountsServer (optional): The full Account Server URL. Most of the time, the value of this property will be https://books.zoho.com, but if your account resides in a different location, then the domain should change accordingly (.eu, .in, .com.au, ...).

Authenticating to Zoho Books

Zoho Books uses the OAuth authentication standard. The Sync App supports OAuth authentication for instances of the Sync App running on your local machine, a web service, or on a headless machine.

This section details a selection of advanced features of the Zoho Books Sync App.

User Defined Views

The Sync App allows you to define virtual tables, called user defined views, whose contents are decided by a pre-configured query. These views are useful when you cannot directly control queries being issued to the drivers. See User Defined Views for an overview of creating and configuring custom views.

SSL Configuration

Use SSL Configuration to adjust how Sync App handles TLS/SSL certificate negotiations. You can choose from various certificate formats; see the SSLServerCert property under "Connection String Options" for more information.

Firewall and Proxy

Configure the Sync App for compliance with Firewall and Proxy, including Windows proxies and HTTP proxies. You can also set up tunnel connections.

Query Processing

The Sync App offloads as much of the SELECT statement processing as possible to Zoho Books and then processes the rest of the query in memory (client-side).

See Query Processing for more information.

Logging

See Logging for an overview of configuration settings that can be used to refine CData logging. For basic logging, you only need to set two connection properties, but there are numerous features that support more refined logging, where you can select subsets of information to be logged using the LogModules connection property.

Customizing the SSL Configuration

By default, the Sync App attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.

To specify another certificate, see the SSLServerCert property for the available formats to do so.

Connecting Through a Firewall or Proxy

HTTP Proxies

To connect through the Windows system proxy, you do not need to set any additional connection properties. To connect to other proxies, set ProxyAutoDetect to false.

In addition, to authenticate to an HTTP proxy, set ProxyAuthScheme, ProxyUser, and ProxyPassword, in addition to ProxyServer and ProxyPort.

Other Proxies

Set the following properties:

• To use a proxy-based firewall, set FirewallType, FirewallServer, and FirewallPort.
• To tunnel the connection, set FirewallType to TUNNEL.
• To authenticate, specify FirewallUser and FirewallPassword.
• To authenticate to a SOCKS proxy, additionally set FirewallType to SOCKS5.

Overview

This section shows the available API objects and provides more information on executing SQL to Zoho Books APIs.

Key Features

• The Sync App models Zoho Books entities like invoices, bills, and expenses as relational views, allowing you to write SQL to query Zoho Books data.
• Stored procedures allow you to execute operations to Zoho Books, including expense receipt, retainer invoice attachment and attachments.
• Live connectivity to these objects means any changes to your Zoho Books account are immediately reflected when using the Sync App.
• IncludeCustomFields connection property allows you to retrieve custom fields for supported views. Set this property to True, to enable this feature.

Tables

Tables describes the available tables. Tables are statically defined to model Zoho Books entities, such as Currencies, Journals, Users, and more.

Views

Views describes the available views. Views are statically defined to model Zoho Books entities, such as Invoices, Bills, Expenses, and more. Views are read-only.

Stored Procedures

Stored Procedures are function-like interfaces to Zoho Books. Stored procedures allow you to execute operations to Zoho Books, including expense receipt, retainer invoice attachment and attachments.

The Sync App models the data in Zoho Books as a list of tables in a relational database that can be queried using standard SQL statements.

Zoho Books Connector for CData Sync Tables

To list, add, update and delete bank and credit card accounts for your organization.

Table Specific Information

Select

The Sync App uses the Zoho Books API to process WHERE clause conditions built with the following columns and operators:

• AccountId supports the '=' comparison.
• AccountCodeAccountName supports the '=' comparison.
• AccountType supports the '=' comparison.
• Status supports the '=' comparison.

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

For example:

    SELECT * FROM BankAccounts WHERE Status = 'All'

    SELECT * FROM BankAccounts WHERE AccountId = '1894343000000085314'

Insert

INSERT can be executed by specifying the AccountName, and AccountType 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 BankAccounts (AccountName, AccountType) VALUES ('testaccount1', 'bank') 

Update

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

UPDATE BankAccounts SET AccountName = 'Test Account', AccountType = 'bank' WHERE AccountId = '3285934000000264001'

Delete

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

DELETE FROM BankAccounts WHERE AccountId = '3285934000000264001'

Columns

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.

To list, add, update and delete specified bank or credit card account Id.

Table Specific Information

Select

The Sync App uses the Zoho Books API to process WHERE clause conditions built with the following columns and operators:

• RuleAccountId supports the '=' comparison.
• RuleId supports the '=' comparison.

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

For example:

    SELECT * FROM BankRules WHERE RuleAccountId = '1894553000000085382' AND RuleId = '1894553000000085386'

Columns

To list, add, update and delete details involved in an account.

Table Specific Information

Select

The Sync App uses the Zoho Books API to process WHERE clause conditions built with the following columns and operators:

• AccountId supports the '=' comparison.
• TransactionType supports the '=' comparison.
• Amount supports the '=' comparison.
• Date supports the '=' comparison.
• ReferenceNumber supports the '=' comparison.
• Status supports the '=' comparison.
• BankTransactionFilter supports the '=' comparison.

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

For example:

    SELECT * FROM BankTransactions WHERE Status = 'All'

    SELECT * FROM BankTransactions LIMIT 5

Insert

INSERT can be executed by specifying the TransactionType, FromAccountId, ToAccountId, Amount, and CurrencyId 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 BankTransactions (TransactionType, FromAccountId, ToAccountId, Amount, CurrencyId) VALUES ('transfer_fund', '3285934000000000361', '3285934000000256009', '500', '3285934000000000099') 

Update

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

UPDATE BankTransactions SET Amount = '300', TransactionType = 'transfer_fund' WHERE TransactionId = '3285934000000269001'

Delete

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

DELETE FROM BankTransactions WHERE TransactionId = '3285934000000269001'

Columns

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.

To list, add, update and delete base currency adjustment.

Table Specific Information

Select

The Sync App uses the Zoho Books API to process WHERE clause conditions built with the following columns and operators:

• BaseCurrencyAdjustmentId supports the '=' comparison.
• BaseCurrencyAdjustmentsFilter supports the '=' comparison.

By default, response shows the base currency adjustments of the current month only.

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

For example:

    SELECT * FROM BaseCurrencyAdjustments WHERE BaseCurrencyAdjustmentsFilter = 'Date.All'

Columns

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.

To list, add, update and delete details of a bill.

Table Specific Information

Select

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

• BillId supports the the '=' and IN operators.

NOTE: BillId is required to query BillDetails.

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

For example:

    SELECT * FROM BillDetails WHERE BillId = '1894553000000085259'
	SELECT * FROM BillDetails WHERE BillId IN (SELECT BillId FROM Bills)
	SELECT * FROM BillDetails WHERE BillId IN ('1894553000000085259','1894553000000085260')

Insert

INSERT can be executed by specifying BillNumber, VendorId, 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 BillLineItems#TEMP (Name, accountid, itemid) VALUES ('Cloth-Jeans', '3285934000000034001', '3285934000000104097')

INSERT INTO BillDetails (BillNumber, VendorId, lineitems) VALUES ('00011', '3285934000000104023', BillLineItems#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 BillDetails (BillNumber, VendorId, lineitems) VALUES ('00011', '3255827000000081003', '[{"Name":"Cloth-Jeans3", "AccountId":"3285934000000034001", "ItemId":"3285934000000104097"}]')

Update

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

INSERT INTO BillLineItems#TEMP (Name,accountid,itemid) VALUES ('Cloth-Jeans','3285934000000034001','3285934000000104097')

UPDATE BillDetails SET BillNumber = '00011', VendorId = '3285934000000104023', lineitems = 'BillLineItems#TEMP' WHERE BillId = '3350895000000089001'

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 BillDetails SET BillNumber = '00011', VendorId = '3285934000000104023', LineItems = '[{"Name":"Cloth-Jeans", "AccountId":"3285934000000034001", "ItemId":"3285934000000104097"}]' WHERE BillId = '3350895000000089001'

Delete

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

DELETE FROM BillDetails WHERE BillId = '3350895000000089001'

Columns

To list, add, update and delete chart of accounts.

Table Specific Information

Select

The Sync App uses the Zoho Books API to process WHERE clause conditions built with the following columns and operators:

• LastModifiedTime supports the '=' comparison.
• ShowBalance supports the '=' comparison.
• AccountType supports the '=' comparison.

You can also provide criteria to search for matching uncategorized transactions.

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

For example:

    SELECT * FROM ChartOfAccounts WHERE AccountType = 'All' AND ShowBalance = true

Insert

INSERT can be executed by specifying the AccountName, AccountType, and CurrencyId 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 ChartOfAccounts (AccountName, AccountType, CurrencyId) VALUES ('Cash3', 'Assets', '3285934000000000099') 

Update

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

UPDATE ChartOfAccounts SET AccountName = 'Cash4', AccountType = 'Cash', CurrencyId = '3285934000000000099' WHERE ChartAccountId = '3285934000000281053'

Delete

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

DELETE FROM ChartOfAccounts WHERE ChartAccountId = '32859340000002810531'

Columns

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.

To list, add, update and delete a contact.

Table Specific Information

Select

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

• ContactId supports the '=' and IN operators.

NOTE: ContactId is required to query ContactDetails.

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

For example:

    SELECT * FROM ContactDetails WHERE ContactId = '1894952000000071009'
	SELECT * FROM ContactDetails WHERE ContactId IN (SELECT ContactId FROM Contacts)
	SELECT * FROM ContactDetails WHERE ContactId IN ('1894952000000071009','1894952000000071010')

Insert

INSERT can be executed by specifying the ContactName column. 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 ContactDetails (ContactName) VALUES ('test4') 

Update

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

UPDATE ContactDetails SET ContactName = 'Name Change' WHERE ContactId = '3350895000000089005'

Delete

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

DELETE FROM ContactDetails WHERE ContactId = '3350895000000089001'

Columns

To list, add, update and delete a Credit Note.

Table Specific Information

Select

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

• CreditnoteId supports the '=' and IN operators.

NOTE: CreditnoteId is required to query CreditNoteDetails.

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

For example:

    SELECT * FROM CreditNoteDetails WHERE CreditnoteId = '1895452000000083136'
	SELECT * FROM CreditNoteDetails WHERE CreditNoteId IN (SELECT CreditNoteId FROM CreditNotes)
	SELECT * FROM CreditNoteDetails WHERE CreditnoteId IN ('1895452000000083136','1895452000000083137')

Insert

INSERT can be executed by specifying the CustomerId, Date, LineItems, and CreditnoteNumber 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 CreditNoteLineItems#TEMP (Name, accountid, itemid) VALUES ('Cloth-Jeans', '3285934000000034001', '3285934000000104097')

INSERT INTO CreditNoteDetails (customerid, date, lineitems, creditnotenumber) VALUES ('3285934000000085043', '2023-01-18', CreditNoteLineItems#Temp, 'CN-100')

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 CreditNoteDetails (CustomerId, Date, LineItems, CreditNoteNumber) VALUES ('3255827000000081003', '2023-01-18', '[{"Name":"Cloth-Jeans3", "AccountId":"3285934000000034001", "ItemId":"3285934000000104097"}]', 'CN-100')

Update

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

INSERT INTO CreditNoteLineItems#TEMP (Name,accountid,itemid) VALUES ('Cloth-Jeans1','3285934000000034001','3285934000000104097')

UPDATE CreditNoteDetails SET customerid = '3285934000000085043', date = '2023-01-17', lineitems = 'CreditNoteLineItems#Temp' WHERE creditnoteid = '3285934000000265005'

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 CreditNoteDetails SET CustomerId = '3285934000000085043', Date = '2023-01-17', LineItems = '[{"Name":"Cloth-Jeans", "AccountId":"3285934000000034001", "ItemId":"3285934000000104097"}]' WHERE CreditNoteId = '3285934000000265005'

Delete

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

DELETE FROM CreditNoteDetails WHERE CreditnoteId = '3285934000000265005'

Columns

To list, add, update and delete currencies configured. Also, get the details of a currency.

Table Specific Information

Select

The Sync App uses the Zoho Books API to process WHERE clause conditions built with the following columns and operators:

• CurrencyFilter supports the '=' comparison.
• CurrencyId supports the '=' comparison.

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

For example:

    SELECT * FROM Currencies WHERE CurrencyId = '1894553000000000099'

    SELECT * FROM Currencies WHERE CurrencyFilter = 'Currencies.ExcludeBaseCurrency'

Insert

INSERT can be executed by specifying the CurrencyCode, and CurrencyFormat 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 Currencies (currencycode, currencyformat) VALUES ('AUD', '1,234,567.89')

Update

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

UPDATE Currencies SET CurrencyFormat = '1,234,567.89', PricePrecision = '2' WHERE CurrencyId = '3285934000000000105'

Delete

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

DELETE FROM Currencies WHERE CurrencyId = '3285934000000000105'

Columns

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.

Create, Read, Update, Delete contact persons. Also, get the contact person details.

Table Specific Information

Select

The Sync App uses the Zoho Books API to process WHERE clause conditions built with the following columns and operators:

• ContactId supports the '=' comparison.
• CustomerContactId supports the '=' comparison.

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

For example:

    SELECT * FROM CustomerContacts WHERE ContactId = '1864553000000072009' AND CustomerContactId = '1896253000000071011'

Insert

INSERT can be executed by specifying FirstName and CONTACTID 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 CustomerContacts (FirstName, CONTACTID) VALUES ('customercontactspersons', '3285934000000085043') 

Update

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

UPDATE CUSTOMERCONTACTS SET CONTACTID = '3285934000000085043', LASTNAME = 'TEST' WHERE customercontactid = '3285934000000266024'

Delete

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

DELETE FROM CUSTOMERCONTACTS WHERE CustomerContactId = '3285934000000266024'

Columns

To list, add, update and delete details of a payment.

Table Specific Information

Select

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

• PaymentId supports the '=' and IN operators.

NOTE: PaymentId is required to query CustomerPaymentDetails.

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

For example:

    SELECT * FROM CustomerPaymentDetails WHERE PaymentId = '1894553000000083001'
	SELECT * FROM CustomerPaymentDetails WHERE PaymentId IN (SELECT PaymentId FROM CustomerPayments)
	SELECT * FROM CustomerPaymentDetails WHERE PaymentId IN ('1894553000000083001','1894553000000083002')

Insert

INSERT can be executed by specifying the CustomerId, PaymentMode, Amount, Date, InvoiceId, and AmountApplied 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 CustomerPaymentDetails (customerid, paymentmode, amount, date, invoiceid, amountapplied) VALUES ('3285934000000104002', 'cash', '1999', '2023-01-18', '3285934000000220356', '1999')  

Update

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

UPDATE CustomerPaymentDetails SET CustomerId = '3285934000000104002', PaymentMode = 'bank', Amount = '1999', Date = '2023-01-18', InvoiceId = '3285934000000220356', AmountApplied = '1999' WHERE PaymentId = '3285934000000269021'

Delete

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

DELETE FROM CustomerPaymentDetails WHERE PaymentId = '3285934000000269021'

Columns

Read, Insert and Update Vendor Credit Refunds.

Table Specific Information

Select

The Sync App uses the Zoho Books API to process WHERE clause conditions built with the following columns and operators:

• PaymentId supports the '=' comparison.
• PaymentRefundId supports the '=' comparison.

The rest of the filter is executed client-side within the Sync App.

For example, the following queries are processed server-side:

SELECT * FROM CustomerPaymentsRefund WHERE PaymentId = '3350895000000089001'

SELECT * FROM CustomerPaymentsRefund WHERE PaymentRefundId = '3285934000000441001'

Insert

INSERT can be executed by specifying the Amount, Date, FromAccountId, and VendorCreditId 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 CustomerPaymentsRefund (Date, Amount, FromAccountId, PaymentId) VALUES ('2023-02-27', '1200', 3285934000000259036, 3285934000000312117)

Update

UPDATE can be executed by specifying the Amount, Date and AccountId in the WHERE Clause. The columns that are not read-only can be updated. For example:

UPDATE CustomerPaymentsRefund SET Description = 'test2' WHERE PaymentRefundId = 3285934000000439001 AND PaymentId = 3285934000000234015

Delete

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

DELETE FROM CustomerPaymentsRefund WHERE PaymentRefundId = 3285934000000439001 AND PaymentId = 3285934000000234015

Columns

In Zoho Books, you can create a custom module to record other data when the predefined modules are not sufficient to manage all your business requirements.

Table Specific Information

Select

The Sync App uses the Zoho Books API to process WHERE clause conditions built with EntityName, which supports the '=' comparison. The rest of the filter is executed client-side in the Sync App.

NOTE: EntityName is required to query CustomModuleFields.

For example:

    SELECT * FROM CustomModuleFields WHERE EntityName = 'cm_tests_module'

    SELECT * FROM CustomModules WHERE EntityName IN ('cm_tests_module', 'cm_testingmodule')

Insert

You can execute INSERT by specifying EntityName, FieldName, IsMandatory, DataType, and ShowOnPdf 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 CustomModuleFields (EntityName, FieldName, IsMandatory, DataType, ShowOnPdf) VALUES ('cm_test1', 'fieldname23', 'fcan also be executed', 'string', 'fcan also be executed')

Insert a field of type dropdown or multiselect by specifying the Options column in addition to the above columns. The following is an example of how to insert a field of type dropdown or multiselect:

INSERT INTO CustomModuleFieldDropDownOptions#TEMP (OptionName, OptionOrder) VALUES ('option2', '2')

INSERT INTO CustomModuleFieldDropDownOptions#TEMP (OptionName, OptionOrder) VALUES ('option1', '1')

INSERT INTO CustomModuleFields (entityname, fieldname, datatype, ismandatory, showonpdf, options) VALUES ('cm_testupdate', 'field4', 'multiselect', 'fcan also be executed', 'fcan also be executed', CustomModuleFieldDropDownOptions#TEMP )

Insert a field of type autonumber by specifying AutoNumberStartingValue. The following is an example of how to insert a field of type autonumber:

INSERT INTO CustomModuleFields (entityname, fieldname, datatype, ismandatory, showonpdf, AutoNumberStartingValue) VALUES ('cm_testupdate', 'field6', 'autonumber', 'fcan also be executed', 'fcan also be executed', 3)

Update

You can execute UPDATE by specifying the FieldId in the WHERE Clause. The columns that are not read-only can be updated.

UPDATE CustomModuleFields SET FieldName = 'testingss' WHERE FieldId = 4044157000000087002

Delete

DELETE FROM CustomModuleFields WHERE FieldName = 'cf_label_1' AND entityname = 'cm_tets_module'

Columns

In Zoho Books, you can create a custom module to record other data when the predefined modules are not sufficient to manage all your business requirements.

Table Specific Information

Select

The Sync App uses the Zoho Books API to process WHERE clause conditions built with the following columns and operators:

• APIName supports the '=' comparison

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

For example:

    SELECT * FROM CustomModules WHERE APIName = 'cm_testing_module'

Insert

INSERT can be executed by specifying the ModuleName, ModulePluralName, and ModuleDescription 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 CustomModules (ModuleName, ModulePluralName, ModuleDescription) VALUES ('moduletesting', 'codetestings', 'testing insert through code')

Update

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

UPDATE CustomModules SET ModuleName = 'moduletestings', ModulePluralName = 'codetestingsedit', ModuleDescription = 'testing insert through code' WHERE apiname = 'cm_moduletesting'

Delete

DELETE FROM CustomModules WHERE apiname = 'cm_moduletesting'

Columns

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

Table Specific Information

Select

The Sync App 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 Sync App.

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

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.