Zoho Books Connection Details

Introduction

Connector Version

This documentation is based on version 23.0.8804 of the connector.

Get Started

Zoho Books Version Support

The connector leverages the Zoho Books API V3 to enable bidirectional access to Zoho Books data.

Establish a Connection

Connect 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 connector 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, ...).

Authenticate to Zoho Books

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

Desktop Applications

An embedded OAuth application is provided that simplifies OAuth desktop Authentication. Alternatively, you can create a custom OAuth application. See Creating a Custom OAuth App for information about creating custom applications and reasons for doing so.

Get and Refresh the OAuth Access Token

After setting the following, you are ready to connect:

• InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
• OAuthClientId (custom applications only): Set this to the client ID assigned when you registered your application.
• OAuthClientSecret (custom applications only): Set this to the client secret assigned when you registered your application.
• CallbackURL (custom application only): Set this to the redirect URI defined when you registered your application.

When you connect, the connector opens Zoho Books's OAuth endpoint in your default browser. Log in and grant permissions to the application. The connector then completes the OAuth process:

1. The connector obtains an access token from Zoho Books and uses it to request data.
2. The OAuth values are saved in the path specified in OAuthSettingsLocation. These values persist across connections.

The connector refreshes the access token automatically when it expires.

Create a Custom OAuth App

When To Create a Custom OAuth Application

embeds OAuth Application Credentials with branding that can be used when connecting via a desktop application .

You may choose to use your own OAuth Application Credentials when you want to

• control branding of the Authentication Dialog
• control the redirect URI that the application redirects the user to after the user authenticates
• customize the permissions that you are requesting from the user

Create and Configure a Custom OAuth App

To obtain an OAuthClientId, OAuthClientSecret, CallbackURL, and OrganizationId you first need to create an application linked to your Zoho Books account. You must then register your application with Zoho's Developer console to get your Client ID and Client Secret.

Follow these steps to create an application linked to your Zoho Books:

1. To register your application, go to https://accounts.zoho.com/developerconsole.

2. Click Add Client ID and specify a client Name.

   l

3. Fill the required details in the form.

   • Set the Auth Callback URL to https://localhost:33333 or a port of your choice.
   • Choose Client Type as WEB Based/Javascript/Mobile, then click on Create.

4. In Zoho Books, your business is termed as an organization. If you have multiple businesses, set each of those up as individual organizations. Each organization is an independent Zoho Books Organization with its own organization Id, base currency, time zone, language, contacts, reports, etc.

   1. The parameter organization_id along with the organization ID should be sent in with every API request to identify the organization.
   2. Click Organization Name, on the top right section. Choose the Organization ID from the list of organizations.
   3. If the value of Organization ID is not specified in the connection string, the connector makes a call to get all the available organizations and selects the first organization ID as the default.

5. After completing the changes the browser prompts you to save your configuration.

After you are done creating a new client, it is displayed on your screen. From there, click More Options > Edit to reveal your newly created application's Client ID and Client Secret. Use these credentials to connect to Zoho Books by setting the following connection properties:

• OAuthClientId: Set to the Client Id.
• OAuthClientSecret: Set to the Client Secret.
• CallbackURL: Set to the Auth Callback URL.

Important Notes

Configuration Files and Their Paths

• All references to adding configuration files and their paths refer to files and locations on the Jitterbit agent where the connector is installed. These paths are to be adjusted as appropriate depending on the agent and the operating system. If multiple agents are used in an agent group, identical files will be required on each agent.

Advanced Features

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

User Defined Views

The connector 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 connector handles TLS/SSL certificate negotiations. You can choose from various certificate formats; see the SSLServerCert property under "Connection String Options" for more information.

Proxy

To configure the connector using private agent proxy settings, select the Use Proxy Settings checkbox on the connection configuration screen.

Query Processing

The connector 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).

User Defined Views

The Zoho Books connector allows you to define a virtual table whose contents are decided by a pre-configured query. These are called User Defined Views, which are useful in situations where you cannot directly control the query being issued to the driver, e.g. when using the driver from Jitterbit. The User Defined Views can be used to define predicates that are always applied. If you specify additional predicates in the query to the view, they are combined with the query already defined as part of the view.

There are two ways to create user defined views:

• Create a JSON-formatted configuration file defining the views you want.
• DDL statements.

Define Views Using a Configuration File

User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The connector automatically detects the views specified in this file.

You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the connector.

This User Defined View configuration file is formatted as follows:

• Each root element defines the name of a view.
• Each root element contains a child element, called query, which contains the custom SQL query for the view.

For example:

{
    "MyView": {
        "query": "SELECT * FROM INVOICES WHERE MyColumn = 'value'"
    },
    "MyView2": {
        "query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
    }
}

Use the UserDefinedViews connection property to specify the location of your JSON configuration file. For example:

"UserDefinedViews", "C:\Users\yourusername\Desktop\tmp\UserDefinedViews.json"

Define Views Using DDL Statements

The connector is also capable of creating and altering the schema via DDL Statements such as CREATE LOCAL VIEW, ALTER LOCAL VIEW, and DROP LOCAL VIEW.

Create a View

To create a new view using DDL statements, provide the view name and query as follows:

CREATE LOCAL VIEW [MyViewName] AS SELECT * FROM Customers LIMIT 20;

If no JSON file exists, the above code creates one. The view is then created in the JSON configuration file and is now discoverable. The JSON file location is specified by the UserDefinedViews connection property.

Alter a View

To alter an existing view, provide the name of an existing view alongside the new query you would like to use instead:

ALTER LOCAL VIEW [MyViewName] AS SELECT * FROM Customers WHERE TimeModified > '3/1/2020';

The view is then updated in the JSON configuration file.

Drop a View

To drop an existing view, provide the name of an existing schema alongside the new query you would like to use instead.

DROP LOCAL VIEW [MyViewName]

This removes the view from the JSON configuration file. It can no longer be queried.

Schema for User Defined Views

User Defined Views are exposed in the UserViews schema by default. This is done to avoid the view's name clashing with an actual entity in the data model. You can change the name of the schema used for UserViews by setting the UserViewsSchemaName property.

Work with User Defined Views

For example, a SQL statement with a User Defined View called UserViews.RCustomers only lists customers in Raleigh:

SELECT * FROM Customers WHERE City = 'Raleigh';

An example of a query to the driver:

SELECT * FROM UserViews.RCustomers WHERE Status = 'Active';

Resulting in the effective query to the source:

SELECT * FROM Customers WHERE City = 'Raleigh' AND Status = 'Active';

That is a very simple example of a query to a User Defined View that is effectively a combination of the view query and the view definition. It is possible to compose these queries in much more complex patterns. All SQL operations are allowed in both queries and are combined when appropriate.

SSL Configuration

Customize the SSL Configuration

By default, the connector 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.

Data Model

Overview

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

Key Features

• The connector 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 connector.
• 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.

Tables

The connector 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 Tables

BankAccounts

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

Table Specific Information

Select

The connector 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 connector.

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.

BankRules

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

Table Specific Information

Select

The connector 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 connector.

For example:

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

Columns

BankTransactions

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

Table Specific Information

Select

The connector 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 connector.

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.

BaseCurrencyAdjustments

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

Table Specific Information

Select

The connector 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 connector.

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.

BillDetails

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

Table Specific Information

Select

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

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

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

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

ChartOfAccounts

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

Table Specific Information

Select

The connector 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 connector.

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.

ContactDetails

To list, add, update and delete a contact.

Table Specific Information

Select

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

• ContactId supports the '=' and IN operators.

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

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

CreditNoteDetails

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

Table Specific Information

Select

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

• CreditnoteId supports the '=' and IN operators.

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

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

Currencies

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

Table Specific Information

Select

The connector 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 connector.

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.

CustomerContacts

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

Table Specific Information

Select

The connector 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 connector.

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

CustomerPaymentDetails

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

Table Specific Information

Select

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

• PaymentId supports the '=' and IN operators.

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

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

CustomerPaymentsRefund

Read, Insert and Update Vendor Credit Refunds.

Table Specific Information

Select

The connector 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 connector.

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

CustomModuleFields

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

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

CustomModules

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

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

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.

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

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.

ExpenseDetails

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

Table Specific Information

Select

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

• ExpenseId supports the '=' and IN operators.

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

For example:

SELECT * FROM ExpenseDetails WHERE ExpenseId = '1894553000000077244'
SELECT * From ExpenseDetails WHERE ExpenseId IN (SELECT ExpenseId FROM Expenses)
SELECT * FROM EstimateLineItems WHERE EstimateId IN ('1894553000000077244','1894553000000077245')

Insert

INSERT can be executed by specifying the AccountId, Date, Amount, and PaidThroughAccountId 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 ExpenseDetails (AccountId, Date, Amount, PaidThroughAccountId) VALUES ('3285934000000000409', '2023-01-19', '500', '3285934000000259036')

Update

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

UPDATE ExpenseDetails SET AccountId = '3285934000000000409', Date = '2023-01-19', Amount = '300', PaidThroughAccountId = '3285934000000259036' WHERE ExpenseId = '3285934000000262014'

Delete

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

DELETE FROM ExpenseDetails WHERE ExpenseId = '3285934000000262014'

Columns