CData Cloud offers access to Zoho Books across several standard services and protocols, in a cloud-hosted solution. Any application that can connect to a MySQL or SQL Server database can connect to Zoho Books through CData Cloud.
CData Cloud allows you to standardize and configure connections to Zoho Books as though it were any other OData endpoint, or standard SQL Server/MySQL database.
This page provides a guide to Establishing a Connection to Zoho Books in CData Cloud, as well as information on the available resources, and a reference to the available connection properties.
Establishing a Connection shows how to authenticate to Zoho Books and configure any necessary connection properties to create a database in CData Cloud
Accessing data from Zoho Books through the available standard services and CData Cloud administration is documented in further details in the CData Cloud Documentation.
Connect to Zoho Books by selecting the corresponding icon in the Database tab. Required properties are listed under Settings. The Advanced tab lists connection properties that are not typically required.
Zoho Books uses the OAuth authentication standard. To authenticate using OAuth, you will need to create an app to obtain the OAuthClientId, OAuthClientSecret, and CallbackURL connection properties. See Using OAuth Authentication for an authentication guide.
Use the OAuth authentication standard to connect to Zoho Books. OAuth requires the authenticating user to interact with Zoho Books using the browser.
You will need to register an app to obtain the OAuthClientId, OAuthClientSecret. See Custom Credentials for a procedure.
You may want to create a custom OAuth app to change the information displayed when users log into the Zoho Books. You can register an app to obtain the OAuthClientId, OAuthClientSecret, and OrganizationId.
See Creating a Custom OAuth App for a procedure.
You can connect without setting any connection properties for your user credentials.
When you connect the Cloud opens the OAuth endpoint in your default browser. Log in and grant permissions to the application. The Cloud then completes the OAuth process.
This section describes desktop authentication using the credentials for your custom OAuth app. See Creating a Custom OAuth App for more information.
Follow the steps below to authenticate with the credentials for a custom OAuth app. See Creating a Custom OAuth App.
After setting the following, you are ready to connect:
To create Zoho Books data sources on headless servers or other machines on which the Cloud cannot open a browser, you need to authenticate from another machine. Authentication is a two-step process.
Create a Custom OAuth App
See Creating a Custom OAuth App for a procedure. You can then follow the procedures below to authenticate and connect to data.
Obtain a Verifier Code
Set the following properties on the headless machine:
You can then follow the steps below to authenticate from another machine and obtain the OAuthVerifier connection property.
On the headless machine, set the following connection properties to obtain the OAuth authentication values:
Connect to Data
After the OAuth settings file is generated, set the following properties to connect to data:
Transfer OAuth Settings
Follow the steps below to install the Cloud on another machine, authenticate, and then transfer the resulting OAuth values.
On a second machine, install the Cloud and connect with the following properties set:
Test the connection to authenticate. The resulting authentication values are written, encrypted, to the path specified by OAuthSettingsLocation. Once you have successfully tested the connection, copy the OAuth settings file to your headless machine. On the headless machine, set the following connection properties to connect to data:
To obtain an OAuthClientId, OAuthClientSecret, CallbackURL, and OrganizationId you first need to create an app linked to your Zoho Books account.
You will have to first register your application with Zoho's Developer console in order get your Client ID and Client Secret. You can create an app linked to your Zoho Books account as follows:
Once you are done with creating a new client, it will be displayed on your screen. From there, you can click More Options > Edit to reveal your newly created app's Client ID and Client Secret. You can now use these credentials to connect to Zoho Books by setting them as the following connection properties:
You can use the following properties to gain more control over the data returned from Zoho Books:
Date | Build Number | Change Type | Description |
02/03/2022 | 8434 | Zoho Books | Added
|
12/14/2022 | 8383 | General | Changed
|
11/09/2022 | 8348 | Zoho Books | Added
|
09/30/2022 | 8308 | General | Changed
|
2022-09-20 | 8298 | Zoho Books | Added
|
08/17/2022 | 8264 | General | Changed
|
02/01/2022 | 8067 | Zoho Books | Added
|
09/02/2021 | 7915 | General | Added
|
08/07/2021 | 7889 | General | Changed
|
08/06/2021 | 7888 | General | Changed
|
07/23/2021 | 7874 | General | Changed
|
09/16/2022 | 7863 | Zoho Books | Changed
Added
|
07/08/2021 | 7859 | General | Added
|
04/23/2021 | 7785 | General | Added
|
04/23/2021 | 7783 | General | Changed
|
04/16/2021 | 7776 | General | Added
Changed
|
04/15 /2021 | 7775 | General | Changed
|
This section details a selection of advanced features of the Zoho Books Cloud.
The Cloud 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.
Use SSL Configuration to adjust how Cloud handles TLS/SSL certificate negotiations. You can choose from various certificate formats; see the SSLServerCert property under "Connection String Options" for more information.
Configure the Cloud for compliance with Firewall and Proxy, including Windows proxies and HTTP proxies. You can also set up tunnel connections.
The Cloud 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.
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.
The CData Cloud 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 a tool. 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:
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 Cloud.
This User Defined View configuration file is formatted as follows:
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"
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.
By default, the Cloud 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.
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.
Set the following properties:
For sources that do not support SQL-92, the Cloud offloads as much of SQL statement processing as possible to Zoho Books and then processes the rest of the query in memory (client-side). This results in optimal performance.
For data sources with limited query capabilities, the Cloud handles transformations of the SQL query to make it simpler for the Cloud. The goal is to make smart decisions based on the query capabilities of the data source to push down as much of the computation as possible. The Zoho Books Query Evaluation component examines SQL queries and returns information indicating what parts of the query the Cloud is not capable of executing natively.
The Zoho Books Query Slicer component is used in more specific cases to separate a single query into multiple independent queries. The client-side Query Engine makes decisions about simplifying queries, breaking queries into multiple queries, and pushing down or computing aggregations on the client-side while minimizing the size of the result set.
There's a significant trade-off in evaluating queries, even partially, client-side. There are always queries that are impossible to execute efficiently in this model, and some can be particularly expensive to compute in this manner. CData always pushes down as much of the query as is feasible for the data source to generate the most efficient query possible and provide the most flexible query capabilities.
Capturing Cloud logging can be very helpful when diagnosing error messages or other unexpected behavior.
You will simply need to set two connection properties to begin capturing Cloud logging.
Once this property is set, the Cloud will populate the log file as it carries out various tasks, such as when authentication is performed or queries are executed. If the specified file doesn't already exist, it will be created.
The verbosity level determines the amount of detail that the Cloud reports to the Logfile. Verbosity levels from 1 to 5 are supported. These are described in the following list:
1 | Setting Verbosity to 1 will log the query, the number of rows returned by it, the start of execution and the time taken, and any errors. |
2 | Setting Verbosity to 2 will log everything included in Verbosity 1 and additional information about the request. |
3 | Setting Verbosity to 3 will additionally log HTTP headers, as well as the body of the request and the response. |
4 | Setting Verbosity to 4 will additionally log transport-level communication with the data source. This includes SSL negotiation. |
5 | Setting Verbosity to 5 will additionally log communication with the data source and additional details that may be helpful in troubleshooting problems. This includes interface commands. |
The Verbosity should not be set to greater than 1 for normal operation. Substantial amounts of data can be logged at higher verbosities, which can delay execution times.
To refine the logged content further by showing/hiding specific categories of information, see LogModules.
Best Practices for Data Security
Although we mask sensitive values, such as passwords, in the connection string and any request in the log, it is always best practice to review the logs for any sensitive information before sharing outside your organization.
You may want to refine the exact information that is recorded to the log file. This can be accomplished using the LogModules property.
This property allows you to filter the logging using a semicolon-separated list of logging modules.
All modules are four characters long. Please note that modules containing three letters have a required trailing blank space. The available modules are:
LogModules=INFO;EXEC;SSL ;SQL ;META;
Note that these modules refine the information as it is pulled after taking the Verbosity into account.
See SELECT Statements for a syntax reference and examples.
See Data Model for information on the capabilities of the Zoho Books API.
Use EXECUTE or EXEC statements to execute stored procedures. See EXECUTE Statements for a syntax reference and examples.
A SELECT statement can consist of the following basic clauses.
The following syntax diagram outlines the syntax supported by the SQL engine of the Cloud:
SELECT {
[ TOP <numeric_literal> | DISTINCT ]
{
*
| {
<expression> [ [ AS ] <column_reference> ]
| { <table_name> | <correlation_name> } .*
} [ , ... ]
}
[ INTO csv:// [ filename= ] <file_path> [ ;delimiter=tab ] ]
{
FROM <table_reference> [ [ AS ] <identifier> ]
} [ , ... ]
[ [
INNER | { { LEFT | RIGHT | FULL } [ OUTER ] }
] JOIN <table_reference> [ ON <search_condition> ] [ [ AS ] <identifier> ]
] [ ... ]
[ WHERE <search_condition> ]
[ GROUP BY <column_reference> [ , ... ]
[ HAVING <search_condition> ]
[ UNION [ ALL ] <select_statement> ]
[
ORDER BY
<column_reference> [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]
]
[
LIMIT <expression>
[
{ OFFSET | , }
<expression>
]
]
}
<expression> ::=
| <column_reference>
| @ <parameter>
| ?
| COUNT( * | { [ DISTINCT ] <expression> } )
| { AVG | MAX | MIN | SUM | COUNT } ( <expression> )
| NULLIF ( <expression> , <expression> )
| COALESCE ( <expression> , ... )
| CASE <expression>
WHEN { <expression> | <search_condition> } THEN { <expression> | NULL } [ ... ]
[ ELSE { <expression> | NULL } ]
END
| <literal>
| <sql_function>
<search_condition> ::=
{
<expression> { = | > | < | >= | <= | <> | != | LIKE | NOT LIKE | IN | NOT IN | IS NULL | IS NOT NULL | AND | OR | CONTAINS | BETWEEN } [ <expression> ]
} [ { AND | OR } ... ]
SELECT * FROM INVOICES
SELECT [InvoiceNumber] AS MY_InvoiceNumber FROM INVOICES
SELECT CAST(AnnualRevenue AS VARCHAR) AS Str_AnnualRevenue FROM INVOICES
SELECT * FROM INVOICES WHERE CustomerName = 'NewTech Industries'
SELECT COUNT(*) AS MyCount FROM INVOICES
SELECT COUNT(DISTINCT InvoiceNumber) FROM INVOICES
SELECT DISTINCT InvoiceNumber FROM INVOICES
SELECT InvoiceNumber, MAX(AnnualRevenue) FROM INVOICES GROUP BY InvoiceNumberSee Aggregate Functions for details.
SELECT Customers.ContactName, Orders.OrderDate FROM Customers, Orders WHERE Customers.CustomerId=Orders.CustomerIdSee JOIN Queries for details.
SELECT InvoiceId, InvoiceNumber FROM INVOICES ORDER BY InvoiceNumber ASC
SELECT InvoiceId, InvoiceNumber FROM INVOICES LIMIT 10
SELECT * FROM INVOICES WHERE CustomerName = @param
Some input-only fields are available in SELECT statements. These fields, called pseudo columns, do not
appear as regular columns in the results, yet may be specified as part of the WHERE clause. You can use pseudo columns to access additional features from Zoho Books.
SELECT * FROM INVOICES WHERE Query = 'Column3 > 100'
Returns the number of rows matching the query criteria.
SELECT COUNT(*) FROM INVOICES WHERE CustomerName = 'NewTech Industries'
Returns the number of distinct, non-null field values matching the query criteria.
SELECT COUNT(DISTINCT InvoiceId) AS DistinctValues FROM INVOICES WHERE CustomerName = 'NewTech Industries'
Returns the average of the column values.
SELECT InvoiceNumber, AVG(AnnualRevenue) FROM INVOICES WHERE CustomerName = 'NewTech Industries' GROUP BY InvoiceNumber
Returns the minimum column value.
SELECT MIN(AnnualRevenue), InvoiceNumber FROM INVOICES WHERE CustomerName = 'NewTech Industries' GROUP BY InvoiceNumber
Returns the maximum column value.
SELECT InvoiceNumber, MAX(AnnualRevenue) FROM INVOICES WHERE CustomerName = 'NewTech Industries' GROUP BY InvoiceNumber
Returns the total sum of the column values.
SELECT SUM(AnnualRevenue) FROM INVOICES WHERE CustomerName = 'NewTech Industries'
The CData Cloud supports standard SQL joins like the following examples.
An inner join selects only rows from both tables that match the join condition:
SELECT Customers.ContactName, Orders.OrderDate FROM Customers, Orders WHERE Customers.CustomerId=Orders.CustomerId
A left join selects all rows in the FROM table and only matching rows in the JOIN table:
SELECT Customers.ContactName, Orders.OrderDate FROM Customers LEFT OUTER JOIN Orders ON Customers.CustomerId=Orders.CustomerId
The following date literal functions can be used to filter date fields using relative intervals. Note that while the <, >, and = operators are supported for these functions, <= and >= are not.
The current day.
SELECT * FROM MyTable WHERE MyDateField = L_TODAY()
The previous day.
SELECT * FROM MyTable WHERE MyDateField = L_YESTERDAY()
The following day.
SELECT * FROM MyTable WHERE MyDateField = L_TOMORROW()
Every day in the preceding week.
SELECT * FROM MyTable WHERE MyDateField = L_LAST_WEEK()
Every day in the current week.
SELECT * FROM MyTable WHERE MyDateField = L_THIS_WEEK()
Every day in the following week.
SELECT * FROM MyTable WHERE MyDateField = L_NEXT_WEEK()Also available:
The previous n days, excluding the current day.
SELECT * FROM MyTable WHERE MyDateField = L_LAST_N_DAYS(3)
The following n days, including the current day.
SELECT * FROM MyTable WHERE MyDateField = L_NEXT_N_DAYS(3)Also available:
Every day in every week, starting n weeks before current week, and ending in the previous week.
SELECT * FROM MyTable WHERE MyDateField = L_LAST_N_WEEKS(3)
Every day in every week, starting the following week, and ending n weeks in the future.
SELECT * FROM MyTable WHERE MyDateField = L_NEXT_N_WEEKS(3)Also available:
You can use the SELECT INTO statement to export formatted data to a file.
The following query exports data into a file formatted in comma-separated values (CSV):
SELECT InvoiceId, InvoiceNumber INTO [csv://INVOICES.txt] FROM [INVOICES] WHERE CustomerName = 'NewTech Industries'You can specify other formats in the file URI. The possible delimiters are tab, semicolon, and comma with the default being a comma. The following example exports tab-separated values:
SELECT InvoiceId, InvoiceNumber INTO [csv://INVOICES.txt;delimiter=tab] FROM [INVOICES] WHERE CustomerName = 'NewTech Industries'You can specify other file formats in the URI. The following example exports tab-separated values:
The Cloud provides functions that are similar to those that are available with most standard databases. These functions are implemented in the CData provider engine and thus are available across all data sources with the same consistent API. Three categories of functions are available: string, date, and math.
The Cloud interprets all SQL function inputs as either strings or column identifiers, so you need to escape all literals as strings, with single quotes. For example, contrast the SQL Server syntax and Cloud syntax for the DATENAME function:
SELECT DATENAME(yy,GETDATE())
SELECT DATENAME('yy',GETDATE())
These functions perform string manipulations and return a string value. See STRING Functions for more details.
SELECT CONCAT(firstname, space(4), lastname) FROM INVOICES WHERE CustomerName = 'NewTech Industries'
These functions perform date and date time manipulations. See DATE Functions for more details.
SELECT CURRENT_TIMESTAMP() FROM INVOICES
These functions provide mathematical operations. See MATH Functions for more details.
SELECT RAND() FROM INVOICES
SELECT CONCAT('Mr.', SPACE(2), firstname, SPACE(4), lastname) FROM INVOICES
Returns the ASCII code value of the left-most character of the character expression.
SELECT ASCII('0'); -- Result: 48
Converts the integer ASCII code to the corresponding character.
SELECT CHAR(48); -- Result: '0'
Returns the starting position of the specified expression in the character string.
SELECT CHARINDEX('456', '0123456'); -- Result: 4 SELECT CHARINDEX('456', '0123456', 5); -- Result: -1
Returns the number of UTF-8 characters present in the expression.
SELECT CHAR_LENGTH('sample text') FROM Account LIMIT 1 -- Result: 11
Returns the string that is the concatenation of two or more string values.
SELECT CONCAT('Hello, ', 'world!'); -- Result: 'Hello, world!'
Returns 1 if expressionToFind is found within expressionToSearch; otherwise, 0.
SELECT CONTAINS('0123456', '456'); -- Result: 1 SELECT CONTAINS('0123456', 'Not a number'); -- Result: 0
Returns 1 if character_expression ends with character_suffix; otherwise, 0.
SELECT ENDSWITH('0123456', '456'); -- Result: 1 SELECT ENDSWITH('0123456', '012'); -- Result: 0
Returns the number of bytes present in the file at the specified file path.
SELECT FILESIZE('C:/Users/User1/Desktop/myfile.txt'); -- Result: 23684
Returns the value formatted with the specified format.
SELECT FORMAT(12.34, '#'); -- Result: 12 SELECT FORMAT(12.34, '#.###'); -- Result: 12.34 SELECT FORMAT(1234, '0.000E0'); -- Result: 1.234E3 SELECT FORMAT('2019/01/01', 'yyyy-MM-dd'); -- Result: 2019-01-01 SELECT FORMAT('20190101', 'yyyyMMdd', 'yyyy-MM-dd'); -- Result: '2019-01-01'
Returns a representation of the unix_timestamp argument as a value in YYYY-MM-DD HH:MM:SS expressed in the current time zone.
SELECT FROM_UNIXTIME(1540495231, 1); -- Result: 2018-10-25 19:20:31 SELECT FROM_UNIXTIME(1540495357385, 0); -- Result: 2018-10-25 19:22:37
Returns the hash of the input value as a byte array using the given algorithm. The supported algorithms are MD5, SHA1, SHA2_256, SHA2_512, SHA3_224, SHA3_256, SHA3_384, and SHA3_512.
SELECT HASHBYTES('MD5', 'Test'); -- Result (byte array): 0x0CBC6611F5540BD0809A388DC95A615B
Returns the starting position of the specified expression in the character string.
SELECT INDEXOF('0123456', '456'); -- Result: 4 SELECT INDEXOF('0123456', '456', 5); -- Result: -1
Replaces null with the specified replacement value.
SELECT ISNULL(42, 'Was NULL'); -- Result: 42 SELECT ISNULL(NULL, 'Was NULL'); -- Result: 'Was NULL'
Computes the average value of a JSON array within a JSON object. The path to the array is specified in the jsonpath argument. Return value is numeric or null.
SELECT JSON_AVG('[1,2,3,4,5]', '$[x]'); -- Result: 3 SELECT JSON_AVG('{"test": {"data": [1,2,3,4,5]}}', '$.test.data[x]'); -- Result: 3 SELECT JSON_AVG('{"test": {"data": [1,2,3,4,5]}}', '$.test.data[3..]'); -- Result: 4.5
Returns the number of elements in a JSON array within a JSON object. The path to the array is specified in the jsonpath argument. Return value is numeric or null.
SELECT JSON_COUNT('[1,2,3,4,5]', '$[x]'); -- Result: 5 SELECT JSON_COUNT('{"test": {"data": [1,2,3,4,5]}}', '$.test.data[x]'); -- Result: 5 SELECT JSON_COUNT('{"test": {"data": [1,2,3,4,5]}}', '$.test.data[3..]'); -- Result: 2
Selects any value in a JSON array or object. The path to the array is specified in the jsonpath argument. Return value is numeric or null.
SELECT JSON_EXTRACT('{"test": {"data": 1}}', '$.test'); -- Result: '{"data":1}' SELECT JSON_EXTRACT('{"test": {"data": 1}}', '$.test.data'); -- Result: 1 SELECT JSON_EXTRACT('{"test": {"data": [1, 2, 3]}}', '$.test.data[1]'); -- Result: 2
Gets the maximum value in a JSON array within a JSON object. The path to the array is specified in the jsonpath argument. Return value is numeric or null.
SELECT JSON_MAX('[1,2,3,4,5]', '$[x]'); -- Result: 5 SELECT JSON_MAX('{"test": {"data": [1,2,3,4,5]}}', '$.test.data[x]'); -- Result: 5 SELECT JSON_MAX('{"test": {"data": [1,2,3,4,5]}}', '$.test.data[..3]'); -- Result: 4
Gets the minimum value in a JSON array within a JSON object. The path to the array is specified in the jsonpath argument. Return value is numeric or null.
SELECT JSON_MIN('[1,2,3,4,5]', '$[x]'); -- Result: 1 SELECT JSON_MIN('{"test": {"data": [1,2,3,4,5]}}', '$.test.data[x]'); -- Result: 1 SELECT JSON_MIN('{"test": {"data": [1,2,3,4,5]}}', '$.test.data[3..]'); -- Result: 4
Computes the summary value in JSON according to the JSONPath expression. Return value is numeric or null.
SELECT JSON_SUM('[1,2,3,4,5]', '$[x]'); -- Result: 15 SELECT JSON_SUM('{"test": {"data": [1,2,3,4,5]}}', '$.test.data[x]'); -- Result: 15 SELECT JSON_SUM('{"test": {"data": [1,2,3,4,5]}}', '$.test.data[3..]'); -- Result: 9
Returns the specified number of characters counting from the left of the specified string.
SELECT LEFT('1234567890', 3); -- Result: '123'
Returns the number of characters of the specified string expression.
SELECT LEN('12345'); -- Result: 5
Returns an integer representing how many characters into the string the substring appears.
SELECT LOCATE('sample','XXXXXsampleXXXXX'); -- Result: 6
Returns the character expression with the uppercase character data converted to lowercase.
SELECT LOWER('MIXED case'); -- Result: 'mixed case'
Returns the character expression with leading blanks removed.
SELECT LTRIM(' trimmed'); -- Result: 'trimmed'
Replaces the characters between start_index and end_index with the mask_character within the string.
SELECT MASK('1234567890','*',); -- Result: '**********' SELECT MASK('1234567890','*', 4); -- Result: '1234******' SELECT MASK('1234567890','*', 4, 2); -- Result: '1234****90'
Returns the Unicode character with the specified integer code as defined by the Unicode standard.
Returns the number of bytes present in the expression.
SELECT OCTET_LENGTH('text') FROM Account LIMIT 1 -- Result: 4
Returns the starting position of the first occurrence of the pattern in the expression. Returns 0 if the pattern is not found.
SELECT PATINDEX('123%', '1234567890'); -- Result: 1 SELECT PATINDEX('%890', '1234567890'); -- Result: 8 SELECT PATINDEX('%456%', '1234567890'); -- Result: 4
Returns the starting position of the specified expression in the character string.
SELECT POSITION('456' IN '123456'); -- Result: 4 SELECT POSITION('x' IN '123456'); -- Result: 0
Returns a valid SQL Server-delimited identifier by adding the necessary delimiters to the specified Unicode string.
SELECT QUOTENAME('table_name'); -- Result: '[table_name]' SELECT QUOTENAME('table_name', '"'); -- Result: '"table_name"' SELECT QUOTENAME('table_name', '['); -- Result: '[table_name]'
Replaces all occurrences of a string with another string.
SELECT REPLACE('1234567890', '456', '|'); -- Result: '123|7890' SELECT REPLACE('123123123', '123', '.'); -- Result: '...' SELECT REPLACE('1234567890', 'a', 'b'); -- Result: '1234567890'
Repeats the string value the specified number of times.
SELECT REPLACE('x', 5); -- Result: 'xxxxx'
Returns the reverse order of the string expression.
SELECT REVERSE('1234567890'); -- Result: '0987654321'
Returns the right part of the string with the specified number of characters.
SELECT RIGHT('1234567890', 3); -- Result: '890'
Returns the character expression after it removes trailing blanks.
SELECT RTRIM('trimmed '); -- Result: 'trimmed'
Returns the four-character Soundex code, based on how the string sounds when spoken.
SELECT SOUNDEX('smith'); -- Result: 'S530'
Returns the string that consists of repeated spaces.
SELECT SPACE(5); -- Result: ' '
Returns a section of the string between to delimiters.
SELECT SPLIT('a/b/c/d', '/', 1); -- Result: 'a' SELECT SPLIT('a/b/c/d', '/', -2); -- Result: 'c'
Returns 1 if character_expression starts with character_prefix; otherwise, 0.
SELECT STARTSWITH('0123456', '012'); -- Result: 1 SELECT STARTSWITH('0123456', '456'); -- Result: 0
Returns the character data converted from the numeric data. For example, STR(123.45, 6, 1) returns 123.5.
SELECT STR('123.456'); -- Result: '123' SELECT STR('123.456', 2); -- Result: '**' SELECT STR('123.456', 10, 2); -- Result: '123.46'
Inserts a string into another string. It deletes the specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.
SELECT STUFF('1234567890', 3, 2, 'xx'); -- Result: '12xx567890'
Returns the part of the string with the specified length; starts at the specified index.
SELECT SUBSTRING('1234567890' FROM 3 FOR 2); -- Result: '34' SELECT SUBSTRING('1234567890' FROM 3); -- Result: '34567890'
Converts the value of this instance to its equivalent string representation.
SELECT TOSTRING(123); -- Result: '123' SELECT TOSTRING(123.456); -- Result: '123.456' SELECT TOSTRING(null); -- Result: ''
Returns the character expression with leading and/or trailing blanks removed.
SELECT TRIM(' trimmed '); -- Result: 'trimmed' SELECT TRIM(LEADING FROM ' trimmed '); -- Result: 'trimmed ' SELECT TRIM('-' FROM '-----trimmed-----'); -- Result: 'trimmed' SELECT TRIM(BOTH '-' FROM '-----trimmed-----'); -- Result: 'trimmed' SELECT TRIM(TRAILING '-' FROM '-----trimmed-----'); -- Result: '-----trimmed'
Returns the integer value defined by the Unicode standard of the first character of the input expression.
Returns the character expression with lowercase character data converted to uppercase.
SELECT UPPER('MIXED case'); -- Result: 'MIXED CASE'
Extracts an XML document using the specified XPath to flatten the XML. A comma is used to separate the outputs by default, but this can be changed by specifying the third parameter.
SELECT XML_EXTRACT('<vowels><ch>a</ch><ch>e</ch><ch>i</ch><ch>o</ch><ch>u</ch></vowels>', '/vowels/ch'); -- Result: 'a,e,i,o,u' SELECT XML_EXTRACT('<vowels><ch>a</ch><ch>e</ch><ch>i</ch><ch>o</ch><ch>u</ch></vowels>', '/vowels/ch', ';'); -- Result: 'a;e;i;o;u'
Returns the current date value.
SELECT CURRENT_DATE(); -- Result: 2018-02-01
Returns the current time stamp of the database system as a datetime value. This value is equal to GETDATE and SYSDATETIME, and is always in the local timezone.
SELECT CURRENT_TIMESTAMP(); -- Result: 2018-02-01 03:04:05
Returns the datetime value that results from adding the specified number (a signed integer) to the specified date part of the date.
SELECT DATEADD('d', 5, '2018-02-01'); -- Result: 2018-02-06 SELECT DATEADD('hh', 5, '2018-02-01 00:00:00'); -- Result: 2018-02-01 05:00:00
Returns the difference (a signed integer) of the specified time interval between the specified start date and end date.
SELECT DATEDIFF('d', '2018-02-01', '2018-02-10'); -- Result: 9 SELECT DATEDIFF('hh', '2018-02-01 00:00:00', '2018-02-01 12:00:00'); -- Result: 12
Returns the datetime value for the specified year, month, and day.
SELECT DATEFROMPARTS(2018, 2, 1); -- Result: 2018-02-01
Returns the character string that represents the specified date part of the specified date.
SELECT DATENAME('yy', '2018-02-01'); -- Result: '2018' SELECT DATENAME('dw', '2018-02-01'); -- Result: 'Thursday'
Returns a character string that represents the specified date part of the specified date.
SELECT DATEPART('yy', '2018-02-01'); -- Result: 2018 SELECT DATEPART('dw', '2018-02-01'); -- Result: 5
Returns the datetime value for the specified date parts.
SELECT DATETIME2FROMPARTS(2018, 2, 1, 1, 2, 3, 456, 3); -- Result: 2018-02-01 01:02:03.456
Returns the datetime value for the specified date parts.
SELECT DATETIMEFROMPARTS(2018, 2, 1, 1, 2, 3, 456); -- Result: 2018-02-01 01:02:03.456
Truncates the date to the precision of the given date part. Modeled after the Oracle TRUNC function.
SELECT DATE_TRUNC('05-04-2005', 'YY'); -- Result: '1/1/2005' SELECT DATE_TRUNC('05-04-2005', 'MM'); -- Result: '5/1/2005'
Truncates the date to the precision of the given date part. Modeled after the PostgreSQL date_trunc function.
SELECT DATE_TRUNC2('year', '2020-02-04'); -- Result: '2020-01-01' SELECT DATE_TRUNC2('week', '2020-02-04', 'monday'); -- Result: '2020-02-02', which is the previous Monday
Returns the integer that specifies the day component of the specified date.
SELECT DAY('2018-02-01'); -- Result: 1
SELECT DAYOFMONTH('04/15/2000'); -- Result: 15
SELECT DAYOFWEEK('04/15/2000'); -- Result: 7
SELECT DAYOFYEAR('04/15/2000'); -- Result: 106
Returns the last day of the month that contains the specified date with an optional offset.
SELECT EOMONTH('2018-02-01'); -- Result: 2018-02-28 SELECT LAST_DAY('2018-02-01'); -- Result: 2018-02-28 SELECT EOMONTH('2018-02-01', 2); -- Result: 2018-04-30
SELECT FDWEEK('02-08-2018'); -- Result: 2/4/2018
SELECT FDMONTH('02-08-2018'); -- Result: 2/1/2018
SELECT FDQUARTER('05-08-2018'); -- Result: 4/1/2018
Returns the time stamp associated with the Date Modified of the relevant file.
SELECT FILEMODIFIEDTIME('C:/Documents/myfile.txt'); -- Result: 6/25/2019 10:06:58 AM
Returns a date derived from the number of days after 1582-10-15 (based upon the Gregorian calendar). This will be equivalent to the MYSQL FROM_DAYS function.
SELECT FROM_DAYS(736000); -- Result: 2/6/2015
Returns the current time stamp of the database system as a datetime value. This value is equal to CURRENT_TIMESTAMP and SYSDATETIME, and is always in the local timezone.
SELECT GETDATE(); -- Result: 2018-02-01 03:04:05
Returns the current time stamp of the database system formatted as a UTC datetime value. This value is equal to SYSUTCDATETIME.
SELECT GETUTCDATE(); -- For example, if the local timezone is Eastern European Time (GMT+2) -- Result: 2018-02-01 05:04:05
Returns the hour component from the provided datetime.
SELECT HOUR('02-02-2020 11:30:00'); -- Result: 11
Returns 1 if the value is a valid date, time, or datetime value; otherwise, 0.
SELECT ISDATE('2018-02-01', 'yyyy-MM-dd'); -- Result: 1 SELECT ISDATE('Not a date'); -- Result: 0
Returns a time stamp equivalent to exactly one week before the current date.
SELECT LAST_WEEK(); //Assume the date is 3/17/2020 -- Result: 3/10/2020
Returns a time stamp equivalent to exactly one month before the current date.
SELECT LAST_MONTH(); //Assume the date is 3/17/2020 -- Result: 2/17/2020
Returns a time stamp equivalent to exactly one year before the current date.
SELECT LAST_YEAR(); //Assume the date is 3/17/2020 -- Result: 3/10/2019
Returns the last day of the provided week.
SELECT LDWEEK('02-02-2020'); -- Result: 2/8/2020
Returns the last day of the provided month.
SELECT LDMONTH('02-02-2020'); -- Result: 2/29/2020
Returns the last day of the provided quarter.
SELECT LDQUARTER('02-02-2020'); -- Result: 3/31/2020
Returns a date value from a year and a number of days.
SELECT MAKEDATE(2020, 1); -- Result: 2020-01-01
Returns the minute component from the provided datetime.
SELECT MINUTE('02-02-2020 11:15:00'); -- Result: 15
Returns the month component from the provided datetime.
SELECT MONTH('02-02-2020'); -- Result: 2
Returns the quarter associated with the provided datetime.
SELECT QUARTER('02-02-2020'); -- Result: 1
Returns the second component from the provided datetime.
SELECT SECOND('02-02-2020 11:15:23'); -- Result: 23
Returns the datetime value for the specified date and time.
SELECT SMALLDATETIMEFROMPARTS(2018, 2, 1, 1, 2); -- Result: 2018-02-01 01:02:00
Parses the provided string value and returns the corresponding datetime.
SELECT STRTODATE('03*04*2020','dd*MM*yyyy'); -- Result: 4/3/2020
Returns the current time stamp as a datetime value of the database system. It is equal to GETDATE and CURRENT_TIMESTAMP, and is always in the local timezone.
SELECT SYSDATETIME(); -- Result: 2018-02-01 03:04:05
Returns the current system date and time as a UTC datetime value. It is equal to GETUTCDATE.
SELECT SYSUTCDATETIME(); -- For example, if the local timezone is Eastern European Time (GMT+2) -- Result: 2018-02-01 05:04:05
Returns the time value for the specified time and with the specified precision.
SELECT TIMEFROMPARTS(1, 2, 3, 456, 3); -- Result: 01:02:03.456
Returns the number of days since 0000-00-01. This will only return a value for dates on or after 1582-10-15 (based upon the Gregorian calendar). This will be equivalent to the MYSQL TO_DAYS function.
SELECT TO_DAYS('02-06-2015'); -- Result: 736000
Returns the week (of the year) associated with the provided datetime.
SELECT WEEK('02-17-2020 11:15:23'); -- Result: 8
Returns the integer that specifies the year of the specified date.
SELECT YEAR('2018-02-01'); -- Result: 2018
Returns the absolute (positive) value of the specified numeric expression.
SELECT ABS(15); -- Result: 15 SELECT ABS(-15); -- Result: 15
Returns the arc cosine, the angle in radians whose cosine is the specified float expression.
SELECT ACOS(0.5); -- Result: 1.0471975511966
Returns the arc sine, the angle in radians whose sine is the specified float expression.
SELECT ASIN(0.5); -- Result: 0.523598775598299
Returns the arc tangent, the angle in radians whose tangent is the specified float expression.
SELECT ATAN(10); -- Result: 1.47112767430373
Returns the angle in radians between the positive x-axis and the ray from the origin to the point (y, x) where x and y are the values of the two specified float expressions.
SELECT ATN2(1, 1); -- Result: 0.785398163397448
Returns the smallest integer greater than or equal to the specified numeric expression.
SELECT CEILING(1.3); -- Result: 2 SELECT CEILING(1.5); -- Result: 2 SELECT CEILING(1.7); -- Result: 2
Returns the trigonometric cosine of the specified angle in radians in the specified expression.
SELECT COS(1); -- Result: 0.54030230586814
Returns the trigonometric cotangent of the angle in radians specified by float_expression.
SELECT COT(1); -- Result: 0.642092615934331
Returns the angle in degrees for the angle specified in radians.
SELECT DEGREES(3.1415926); -- Result: 179.999996929531
Returns the exponential value of the specified float expression. For example, EXP(LOG(20)) is 20.
SELECT EXP(2); -- Result: 7.38905609893065
Evaluates the expression.
SELECT EXPR('1 + 2 * 3'); -- Result: 7 SELECT EXPR('1 + 2 * 3 == 7'); -- Result: true
Returns the largest integer less than or equal to the numeric expression.
SELECT FLOOR(1.3); -- Result: 1 SELECT FLOOR(1.5); -- Result: 1 SELECT FLOOR(1.7); -- Result: 1
Returns the greatest of the supplied integers.
SELECT GREATEST(3,5,8,10,1) -- Result: 10
Returns a the equivalent hex for the input value.
SELECT HEX(866849198); -- Result: 33AB11AE SELECT HEX('Sample Text'); -- Result: 53616D706C652054657874
Returns the least of the supplied integers.
SELECT LEAST(3,5,8,10,1) -- Result: 1
Returns the natural logarithm of the specified float expression.
SELECT LOG(7.3890560); -- Result: 1.99999998661119
Returns the base-10 logarithm of the specified float expression.
SELECT LOG10(10000); -- Result: 4
Returns the integer value associated with the remainder when dividing the dividend by the divisor.
SELECT MOD(10,3); -- Result: 1
Returns the opposite to the real number input.
SELECT NEGATE(10); -- Result: -10 SELECT NEGATE(-12.4) --Result: 12.4
Returns the constant value of pi.
SELECT PI() -- Result: 3.14159265358979
Returns the value of the specified expression raised to the specified power.
SELECT POWER(2, 10); -- Result: 1024 SELECT POWER(2, -2); -- Result: 0.25
Returns the angle in radians of the angle in degrees.
SELECT RADIANS(180); -- Result: 3.14159265358979
Returns a pseudorandom float value from 0 through 1, exclusive.
SELECT RAND(); -- This result may be different, since the seed is randomized -- Result: 0.873159630165044 SELECT RAND(1); -- This result will always be the same, since the seed is constant -- Result: 0.248668584157093
Returns the numeric value rounded to the specified length or precision.
SELECT ROUND(1.3, 0); -- Result: 1 SELECT ROUND(1.55, 1); -- Result: 1.6 SELECT ROUND(1.7, 0, 0); -- Result: 2 SELECT ROUND(1.7, 0, 1); -- Result: 1 SELECT ROUND (1.24); -- Result: 1.0
Returns the positive sign (1), 0, or negative sign (-1) of the specified expression.
SELECT SIGN(0); -- Result: 0 SELECT SIGN(10); -- Result: 1 SELECT SIGN(-10); -- Result: -1
Returns the trigonometric sine of the angle in radians.
SELECT SIN(1); -- Result: 0.841470984807897
Returns the square root of the specified float value.
SELECT SQRT(100); -- Result: 10
Returns the square of the specified float value.
SELECT SQUARE(10); -- Result: 100 SELECT SQUARE(-10); -- Result: 100
Returns the tangent of the input expression.
SELECT TAN(1); -- Result: 1.5574077246549
Returns the supplied decimal number truncated to have the supplied decimal precision.
SELECT TRUNC(10.3423,2); -- Result: 10.34
To execute stored procedures, you can use EXECUTE or EXEC statements.
EXEC and EXECUTE assign stored procedure inputs, referenced by name, to values or parameter names.
To execute a stored procedure as an SQL statement, use the following syntax:
{ EXECUTE | EXEC } <stored_proc_name>
{
[ @ ] <input_name> = <expression>
} [ , ... ]
<expression> ::=
| @ <parameter>
| ?
| <literal>
Reference stored procedure inputs by name:
EXECUTE my_proc @second = 2, @first = 1, @third = 3;
Execute a parameterized stored procedure statement:
EXECUTE my_proc second = @p1, first = @p2, third = @p3;
PIVOT and UNPIVOT can be used to change a table-valued expression into another table.
"SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days, [0], [1], [2], [3], [4]
FROM
(
SELECT DaysToManufacture, StandardCost
FROM Production.Product
) AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable;"
"SELECT VendorID, Employee, Orders
FROM
(SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
FROM pvt) p
UNPIVOT
(Orders FOR Employee IN
(Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt;"
For further information on PIVOT and UNPIVOT, see FROM clause plus JOIN, APPLY, PIVOT (Transact-SQL)
This section shows the available API objects and provides more information on executing SQL to Zoho Books APIs.
Views describes the available views. Views are statically defined to model Invoices, Bills, Expenses, and more.
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 Cloud models the data in Zoho Books into a list of tables that can be queried using standard SQL statements.
Generally, querying Zoho Books tables is the same as querying a table in a relational database. Sometimes there are special cases, for example, including a certain column in the WHERE clause might be required to get data for certain columns in the table. This is typically needed for situations where a separate request must be made for each row to get certain columns. These types of situations are clearly documented at the top of the table page linked below.
Name | Description |
BankAccounts | To list, add, update and delete bank and credit card accounts for your organization. |
BankRules | To list, add, update and delete specified bank or credit card account ID. |
BankTransactions | To list, add, update and delete details involved in an account. |
BaseCurrencyAdjustments | To list, add, update and delete base currency adjustment. |
BillDetails | To list, add, update and delete details of a bill. |
ChartOfAccounts | To list, add, update and delete chart of accounts. |
ContactDetails | To list, add, update and delete a contact. |
CreditNoteDetails | To list, add, update and delete a Credit Note. |
Currencies | To list, add, update and delete currencies configured. Also, get the details of a currency. |
CustomerContacts | Create, Read, Update, Delete contact persons. Also, get the contact person details. |
CustomerPaymentDetails | To list, add, update and delete details of a payment. |
EstimateDetails | To list, add, update and delete details of an estimate. |
ExpenseDetails | To list, add, update and delete details of an Expense. |
InvoiceDetails | To list, add, update and delete details of an invoice. |
ItemDetails | To list, add, update and delete details of an existing item. |
Journals | To list, add, update and delete journals. |
OpeningBalances | To list, add, update and delete opening balance. |
Projects | To list, add, update and delete projects. |
PurchaseOrderDetails | To list, add, update and delete details of a purchase order. |
RecurringExpenseDetails | To list, add, update and delete details of a recurring expense. |
RecurringInvoiceDetails | To list, add, update and delete details of a recurring invoice. |
RetainerInvoiceDetails | To list, add, update and delete of a retainer invoice. |
SalesOrderDetails | To list, add, update and delete a sales order. |
Tasks | To list, add, update and delete tasks added to a project. Also, get the details of a task. |
Taxes | To list, add, update and delete simple and compound taxes. Also, get the details of a simple or compound tax. |
TimeEntries | To list, add, update and delete time entries. |
Users | To list, add, update and delete users in the organization. Also, get the details of a user. |
VendorCreditDetails | To list, add, update and delete details of a vendor credit. |
VendorPaymentDetails | To list, add, update and delete details of a Vendor Payment. |
To list, add, update and delete bank and credit card accounts for your organization.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
SELECT * FROM BankAccounts WHERE Status = 'All' SELECT * FROM BankAccounts WHERE AccountId = '1894343000000085314'
Insert can be executed by specifying AccountName, AccountType columns. The columns that are not read-only can be inserted optionally. Following is an example of how to insert into this table.
INSERT INTO BankAccounts (AccountName, AccountType) VALUES ('testaccount1','bank')
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 can be executed by specifying the AccountId in the WHERE Clause
For example:
DELETE FROM BankAccounts WHERE AccountId = '3285934000000264001'
Name | Type | ReadOnly | References | SupportedOperators | Description |
AccountId [KEY] | String | True |
Id of the Bank Account. | ||
AccountCode | String | False |
Code of the Account. | ||
AccountName | String | False |
Name of the account. | ||
AccountType | String | False |
Type of the account. | ||
AccountNumber | String | False |
Number associated with the Bank Account. | ||
Balance | Decimal | True |
The unpaid amount. | ||
BankBalance | Decimal | True |
Total balance in Bank. | ||
BankName | String | False |
Name of the Bank. | ||
BcyBalance | Decimal | True |
Balance of Base Currency. | ||
CanShowInZe | Boolean | True |
Check if it can show in Zero Emission. | ||
CanShowPaypalDirectIntegBanner | Boolean | True |
Check if it can show direct integ banner. | ||
CurrencyCode | String | False |
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. | ||
Description | String | False |
Description of the Account. | ||
IsActive | Boolean | True |
Check if bank account is active. | ||
IsDirectPaypal | Boolean | True |
Check if bank account is direct by paypal. | ||
IsPrimaryAccount | Boolean | False |
Check if the Account is Primary Account in Zoho Books. | ||
IsPaypalAccount | Boolean | False |
Check if the Account is Paypal Account. | ||
PricePrecision | Integer | True |
The precision for the price. | ||
PaypalType | String | False |
The type of Payment for the Paypal Account. Allowed Values : standard and adaptive. | ||
PaypalEmailAddress | String | False |
Email Address of the Paypal account. | ||
RoutingNumber | String | False |
Routing Number of the Account. | ||
TotalUnprintedChecks | Integer | True |
Total number of unprinted checks. | ||
UncategorizedTransactions | Integer | True |
Number of uncategorized transactions. |
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 |
Status | String |
Filter bills by any status. The allowed values are All, PartiallyPaid, Paid, Overdue, Void, Open. |
To list, add, update and delete specified bank or credit card account ID.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
SELECT * FROM BankRules WHERE RuleAccountId = '1894553000000085382' AND RuleId = '1894553000000085386'
Name | Type | ReadOnly | References | SupportedOperators | Description |
RuleId [KEY] | String | True |
Id of the rule. | ||
RuleName | String | False |
Name of the rule. | ||
RuleOrder | Integer | True |
Order of rule. | ||
ApplyTo | String | False |
To whom can rule be applied. | ||
CriteriaType | String | False |
Type of criteria0. | ||
Criterion | String | False |
Criterion. | ||
RecordAs | String | False |
Entity as which it should be recorded. | ||
RuleAccountId | String | True |
BankAccounts.AccountId |
ID of the Bank Account. | |
AccountName | String | True |
Name of the account. | ||
TaxId | String | False |
Taxes.TaxId |
Id of a tax. | |
TargetAccountId | String | False |
The account on which the rule has to be applied. | ||
AccountId | String | False |
BankAccounts.AccountId |
Account which is involved in the rule with the target account. | |
CustomerId | String | False |
Contacts.ContactId |
ID of the customer or vendor. | |
CustomerName | String | True |
Name of the customer or vendor. | ||
ReferenceNumber | String | False |
Reference number of a bank rule. | ||
PaymentMode | String | True |
Mode through which payment is made. | ||
ProductType | String | False |
Product Type associated with the Rule. | ||
TaxAuthorityId | String | False |
Taxes.TaxAuthorityId |
ID of a tax authority. | |
TaxAuthorityName | String | True |
Name of a tax authority. | ||
TaxExemptionId | String | False |
ID of a tax exemption. | ||
TaxExemptionCode | String | True |
Code of a tax exemption. | ||
IsReverseCharge | Boolean | True |
Check if it is charged reverse. | ||
VatTreatment | String | False |
VAT treatment for the bank rules. | ||
GstTreatment | String | True |
Choose whether the bank rule is GST registered/unregistered/consumer/overseas. | ||
TaxTreatment | String | True |
VAT treatment for the Bank Rule. | ||
GstNo | String | True |
GST Number. | ||
HsnOrSac | String | True |
HSN Code. | ||
DestinationOfSupply | String | True |
Place where the goods/services are supplied to. |
To list, add, update and delete details involved in an account.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
SELECT * FROM BankTransactions WHERE Status = 'All' SELECT * FROM BankTransactions LIMIT 5
Insert can be executed by specifying TransactionType,FromAccountId, ToAccountId,Amount,CurrencyId columns. The columns that are not read-only can be inserted optionally. 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 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 can be executed by specifying the TransactionId in the WHERE Clause
For example:
DELETE FROM BankTransactions WHERE TransactionId = '3285934000000269001'
Name | Type | ReadOnly | References | SupportedOperators | Description |
TransactionId [KEY] | String | True |
Id of the Transaction. | ||
TransactionType | String | True |
Transaction Type of the transaction. The allowed values are deposit, refund, transfer_fund, card_payment, sales_without_invoices, expense_refund, owner_contribution, interest_income, other_income, owner_drawings, sales_return. | ||
AccountId | String | True |
BankAccounts.AccountId |
Account id for which transactions are to be listed. | |
AccountName | String | True |
Name of the account. | ||
AccountType | String | True |
Type of the account. | ||
Amount | Decimal | False |
Start and end amount, to provide a range within which the transaction amount exist. | ||
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. | ||
CustomerId | String | False |
Contacts.ContactId |
ID of the customer or vendor. | |
CustomFields | String | False |
Custom fields of the contact. | ||
Date | Date | False |
Start and end date, to provide a range within which the transaction date exist. | ||
DebitOrCredit | String | True |
Indicates if transaction is Credit or Debit. | ||
Description | String | False |
Description of the bank transactions. | ||
Documents | String | False |
List of files to be attached to a particular transaction. | ||
ExcludeDescription | String | True |
Is the description is to be excluded. | ||
ImportedTransactionId | Long | True |
ID of the Imported Transaction. | ||
IsOffsetaccountMatched | Boolean | True |
Check if Offset Account is matched. | ||
IsPaidViaPrintCheck | Boolean | True |
Check if paid via print check. | ||
IsRuleExist | Boolean | True |
Check if rule exists. | ||
OffsetAccountName | String | True |
Name of the offset account. | ||
Payee | String | True |
Information about the payee. | ||
PricePrecision | Integer | True |
The precision for the price. | ||
ReferenceNumber | String | False |
Reference Number of the transaction. | ||
RunningBalance | String | True |
Running balance in bank. | ||
Source | String | True |
Source of the bank transaction. | ||
Status | String | True |
Transaction status wise list view. The allowed values are All, uncategorized, manually_added, matched, excluded, categorized. | ||
UserId | Long | False |
Users.UserId |
ID of the User involved in the Transaction. | |
VendorId | String | True |
ID of the vendor the bank transaction has been made. This field will be populated with a value only when the Transaction Id is specified. | ||
VendorName | String | True |
Name of the vendor the bank transaction has been made. This field will be populated with a value only when the Transaction Id is specified. | ||
BankCharges | Decimal | False |
Bank charges of bank transactions. This field will be populated with a value only when the Transaction Id is specified. | ||
BcyTotal | Decimal | True |
Total Base Currency This field will be populated with a value only when the Transaction Id is specified. | ||
CustomerName | String | True |
Name of the customer or vendor. This field will be populated with a value only when the Transaction Id is specified. | ||
ExchangeRate | Decimal | False |
Exchange rate of a bank transaction. This field will be populated with a value only when the Transaction Id is specified. | ||
FromAccountId | String | False |
BankAccounts.AccountId |
Account Id from which bank transaction was made. This field will be populated with a value only when the Transaction Id is specified. | |
FromAccountTags | String | False |
From Account Tags | ||
FromAccountId | String | False |
BankAccounts.AccountId |
Account Id from which bank transaction was made. This field will be populated with a value only when the Transaction Id is specified. | |
ImportedTransactions | String | True |
Imported bank transations. This field will be populated with a value only when the Transaction Id is specified. | ||
IsInclusiveTax | Boolean | False |
Check if bank transaction is invlusive tax. This field will be populated with a value only when the Transaction Id is specified. | ||
IsPreGst | Boolean | True |
Check if bank transaction is pre GST. This field will be populated with a value only when the Transaction Id is specified. | ||
PaymentMode | String | False |
Mode through which payment is made. This field will be populated with a value only when the Transaction Id is specified. | ||
SubTotal | Decimal | True |
Sub total of bank transactions This field will be populated with a value only when the Transaction Id is specified. | ||
Tags | String | False |
Details of tags related to bank transactions. This field will be populated with a value only when the Transaction Id is specified. | ||
TaxAmount | Decimal | True |
Amount of tax. This field will be populated with a value only when the Transaction Id is specified. | ||
TaxId | String | False |
Taxes.TaxId |
Id of tax. This field will be populated with a value only when the Transaction Id is specified. | |
TaxName | String | True |
Name of tax. This field will be populated with a value only when the Transaction Id is specified. | ||
TaxPercentage | Integer | True |
Percentage of tax. This field will be populated with a value only when the Transaction Id is specified. | ||
ToAccountId | String | False |
BankAccounts.AccountId |
Account ID the transaction was made to. This field will be populated with a value only when the Transaction Id is specified. | |
ToAccountName | String | True |
Account name the transaction was made to. This field will be populated with a value only when the Transaction Id is specified. | ||
Total | Decimal | True |
Total of bank transactions. This field will be populated with a value only when the Transaction Id is specified. | ||
ToAccountTags | String | False |
To Account Tags | ||
TransactionType | String | False |
Type of the transaction. |
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 |
BankTransactionFilter | String |
Filters the transactions based on the allowed types. The allowed values are Status.All, Status.Uncategorized, Status.Categorized, Status.ManuallyAdded, Status.Excluded, Status.Matched. |
To list, add, update and delete base currency adjustment.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud. By default, response will be showing the base currency adjustments of current month only.
SELECT * FROM BaseCurrencyAdjustments WHERE BaseCurrencyAdjustmentsFilter = 'Date.All'
Name | Type | ReadOnly | References | SupportedOperators | Description |
BaseCurrencyAdjustmentId [KEY] | String | True |
Id of a base currency adjustment. | ||
CurrencyCode | String | True |
Currency code of the customer's currency. | ||
CurrencyId | String | False |
Currencies.CurrencyId |
Currency ID of the customer's currency. | |
AdjustmentDate | Date | False |
Date of currency adjustment. | ||
ExchangeRate | Decimal | False |
Exchange rate of currency adjustment. | ||
GainOrLoss | Decimal | True |
Check the amount if gain or loss. | ||
Notes | String | False |
Notes of Base cuurrency adjustments. | ||
AccountIds | String | False |
BankAccounts.AccountId |
ID of the accounts for which base currency adjustments need to be posted. |
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 |
BaseCurrencyAdjustmentsFilter | String |
Filter base currency adjustment list. The allowed values are Date.All, Date.Today, Date.ThisWeek, Date.ThisMonth, Date.ThisQuarter, Date.ThisYear. |
To list, add, update and delete details of a bill.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
Note: BillId is required in order to query BillDetails.
SELECT * FROM BillDetails WHERE BillId = '1894553000000085259'
Insert can be executed by specifying BillNumber,VendorId,LineItems columns. The columns that are not read-only can be inserted optionally. 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)
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'
Delete can be executed by specifying the BillId in the WHERE Clause
For example:
DELETE FROM BillDetails WHERE BillId = '3350895000000089001'
Name | Type | ReadOnly | References | SupportedOperators | Description |
BillId [KEY] | String | True |
Bills.BillId |
ID of a Bill. | |
BillNumber | String | False |
Number of a Bill. | ||
Adjustment | Decimal | False |
Adjustments made to the bill. | ||
Approvers | String | False |
Approvers. | ||
AdjustmentDescription | String | False |
Description of adjustments made to the bill. | ||
AllocatedLandedCosts | String | True |
Allocated landed costs of bill. | ||
ApproverId | String | True |
Users.UserId |
ID of an approver. | |
AttachmentName | String | True |
Name of an attachment. | ||
Balance | Decimal | True |
Balance of bill. | ||
BillingAddress | String | True |
Billing address of a bill. | ||
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 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 of a billing address. | ||
ClientViewedTime | String | True |
Time when client viewed. | ||
ColorCode | String | True |
Color code. | ||
ContactCategory | String | True |
Category if contact. | ||
CreatedById | String | True |
Users.UserId |
Id of a user who has created bill. | |
CreatedTime | Datetime | True |
Time at which the bill was created. | ||
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 |
Symbol of the currency. | ||
CurrentSubStatus | String | True |
Current sub status of a bill. | ||
CurrentSubStatusId | String | True |
Current sub status ID of a bill. | ||
CustomFields | String | False |
Custom fields of the contact. | ||
Documents | String | False |
List of files to be attached to a particular transaction. | ||
Date | Date | False |
Bill date. | ||
DestinationOfSupply | String | False |
Place where the goods/services are supplied to. | ||
Discount | String | True |
Discount of bills. | ||
DiscountAccountId | String | True |
BankAccounts.AccountId |
Account ID of discount. | |
DiscountAmount | Decimal | True |
Amount of discount. | ||
DiscountAppliedOnAmount | Decimal | True |
Discount applied on amount. | ||
DiscountSetting | String | True |
Setting of discount. | ||
DueByDays | Integer | True |
Number of days the bill is due by. | ||
DueDate | Date | False |
Delivery date of the bill. | ||
DueInDays | String | True |
Number of days the bill is due in. | ||
EntityType | String | True |
Entity type of the bill. | ||
ExchangeRate | Decimal | False |
Exchange rate of the currency. | ||
FiledInVatReturnId | String | True |
VAT return ID of bill which was filed. | ||
FiledInVatReturnName | String | True |
VAT return name of bill which was filed. | ||
FiledInVatReturnType | String | True |
VAT return type of bill which was filed. | ||
GstNo | String | False |
GST number. | ||
GstReturnDetailsReturnPeriod | String | True |
Return period of GST. | ||
GstReturnDetailsStatus | String | True |
Status of GST return details. | ||
GstTreatment | String | False |
Choose whether the bill is GST registered/unregistered/consumer/overseas. | ||
HasNextBill | Boolean | True |
Check if it has next bill. | ||
InvoiceConversionType | String | True |
Type of invoice conversion. | ||
IsApprovalRequired | Boolean | True |
Check of the approval required. | ||
IsDiscountBeforeTax | Boolean | True |
Check if discount should be applied before tax. | ||
IsInclusiveTax | Boolean | False |
Check if the tax is inclusive in the bill. | ||
IsItemLevelTaxCalc | Boolean | False |
Check if the item leven tax is calculated. | ||
IsLineItemInvoiced | Boolean | True |
Check if the line item is invoiced in the bill. | ||
IsPreGst | Boolean | True |
Check if pre GST is applied. | ||
IsReverseChargeApplied | Boolean | True |
Check if reverse charge is applied. | ||
IsTdsAmountInPercent | Boolean | True |
Check if the TDS amount in percent. | ||
IsTdsApplied | Boolean | True |
Check if TDS is applied. | ||
IsUpdateCustomer | Boolean | False |
Check if cutomer should be updated. | ||
IsViewedByClient | Boolean | True |
Check if bill is viewed by client. | ||
LastModifiedId | String | True |
ID when bill was last modified. | ||
LastModifiedTime | Datetime | True |
The time of last modification of the bill. | ||
LineItems | String | False |
Line items of an estimate. | ||
Notes | String | False |
Notes of the bill. | ||
OpenPurchaseordersCount | Integer | True |
Count of open purchase order. | ||
Orientation | String | True |
Orientation of the bill. | ||
PurchaseOrderIds | String | False |
Purchase Order Ids. | ||
PageHeight | String | True |
Height of the page. | ||
PageWidth | String | True |
Width o the page. | ||
PaymentExpectedDate | Date | True |
Date when the payment is expected. | ||
PaymentMade | Decimal | True |
Amount paid of this bill. | ||
PaymentTerms | Integer | False |
Net payment term for the customer. | ||
PaymentTermsLabel | String | False |
Label for the paymet due details. | ||
PricePrecision | Integer | True |
The precision for the price. | ||
PricebookId | String | False |
Enter ID of the price book. | ||
PlaceOfSupply | String | False |
The place of supply is where a transaction is considered to have occurred for VAT purposes. | ||
PermitNumber | String | False |
The permit number for the bill. | ||
RecurringBillId | String | False |
ID of a recurring bill. | ||
ReferenceBillId | String | True |
ID of a reference bill. | ||
ReferenceId | String | True |
Id of a reference. | ||
ReferenceNumber | String | False |
Number of a reference. | ||
SourceOfSupply | String | False |
Source of supply of bill. | ||
Status | String | True |
Status of the bill. | ||
SubTotal | Decimal | True |
Sub total of bills. | ||
SubTotalInclusiveOfTax | Decimal | True |
Subtotal amount which are inclusive of tax. | ||
SubmittedBy | String | True |
Detail of the user who has submitted the bill. | ||
SubmittedDate | Date | True |
Bill submitted date. | ||
SubmitterId | String | True |
Users.UserId |
Id of a submitter. | |
Taxes | String | False |
Taxes. | ||
TaxAccountId | String | True |
BankAccounts.AccountId |
Account ID of tax. | |
TaxTotal | Decimal | True |
Total amount of tax. | ||
TaxTreatment | String | False |
VAT treatment for the Bill. | ||
TdsAmount | Decimal | True |
Amount of TDS. | ||
TdsPercent | String | True |
Percent of TDS. | ||
TdsSection | String | True |
Section of TDS. | ||
TdsTaxId | String | True |
Tax ID of TDS. | ||
TdsTaxName | String | True |
Tax name of TDS. | ||
TemplateId | String | True |
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 bill. | ||
Total | Decimal | True |
Total of bills. | ||
UnallocatedLandedCosts | String | True |
Costs of unlocated land. | ||
UnusedCreditsPayableAmount | Decimal | True |
Payable amount of unused credits. | ||
VatTreatment | String | False |
VAT treatment for the bills. | ||
VendorCreditsApplied | Decimal | True |
Amount of applied vendor credits. | ||
VendorId | String | False |
ID of the vendor the bill has been made. | ||
VendorName | String | True |
Name of the vendor the bill has been made. |
To list, add, update and delete chart of accounts.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud. You can even provide criteria to search for matching uncategorised transactions.
SELECT * FROM ChartOfAccounts WHERE AccountType = 'All' AND ShowBalance = true
Insert can be executed by specifying AccountName,AccountType,CurrencyId columns. The columns that are not read-only can be inserted optionally. Following is an example of how to insert into this table.
Insert INTO ChartOfAccounts(AccountName,AccountType,CurrencyId) values('Cash3','Assets','3285934000000000099')
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 can be executed by specifying the ChartAccountId in the WHERE Clause
For example:
DELETE FROM ChartOfAccounts WHERE ChartAccountId = '32859340000002810531'
Name | Type | ReadOnly | References | SupportedOperators | Description |
ChartAccountId [KEY] | String | True |
BankAccounts.AccountId |
Id of the Bank Account. | |
AccountName | String | False |
Name of the account. | ||
AccountType | String | False |
Type of the account. The allowed values are All, Active, Inactive, Asset, Liability, Equity, Income, Expense. | ||
CanShowInZe | Boolean | False |
Check if it can show in Zero Emission. | ||
ChildCount | String | True |
Child count in chart of accounts. | ||
CreatedTime | Datetime | True |
Time at which the Chart of Accounts was created. | ||
CustomFields | String | True |
Custom Fields defined for the chart of account | ||
CurrencyId | String | False |
Currencies.CurrencyId |
Currency ID of the customer's currency. | |
Depth | Integer | True |
Depth of account. | ||
Description | String | False |
Description of the Chart of Account. | ||
HasAttachment | Boolean | True |
Check if the chart of account has attachment. | ||
IsActive | Boolean | True |
Check if chart of account is active. | ||
IsChildPresent | Boolean | True |
Check if the child is present in chart of account. | ||
IsStandaloneAccount | Boolean | True |
Check if the account is standalone account. | ||
IsUserCreated | Boolean | True |
Check if the account is created by the user. | ||
LastModifiedTime | Datetime | True |
Last Modified time associated with the entity. | ||
ParentAccountName | String | True |
Account name of parent. | ||
AccountCode | String | False |
Code of the Account. | ||
ClosingBalance | Decimal | True |
Closing balance of account. This field will be populated with a value only when the Chart Account Id is specified. | ||
IsInvolvedInTransaction | Boolean | True |
Check if this account is involved in the transaction. | ||
IsSystemAccount | Boolean | True |
Check if it is a system account. | ||
IsDebit | Boolean | True |
Check if this account is debit. This field will be populated with a value only when the Chart Account Id is specified. | ||
IncludeInVatReturn | Boolean | False |
Boolean to include an account in VAT returns. | ||
ParentAccountId | String | False |
BankAccounts.AccountId |
Id of a Parent account. | |
ShowOnDashboard | Boolean | False |
Show on dashboard. |
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 |
ShowBalance | Boolean |
Boolean to get current balance of accounts. |
To list, add, update and delete a contact.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
Note: ContactId is required in order to query ContactDetails.
SELECT * FROM ContactDetails WHERE ContactId = '1894952000000071009'
Insert can be executed by specifying the ContactName column. The columns that are not read-only can be inserted optionally. Following is an example of how to insert into this table.
INSERT INTO Contacts (ContactName) VALUES ('test4')
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 can be executed by specifying the ContactId in the WHERE Clause
For example:
DELETE FROM ContactDetails WHERE ContactId = '3350895000000089001'
Name | Type | ReadOnly | References | SupportedOperators | Description |
ContactId [KEY] | String | True |
Contacts.ContactId |
Id of a contact. | |
AchSupported | Boolean | True |
Check if ACH is supported. | ||
AssociatedWithSquare | Boolean | True |
Check if the contact is associated with square. | ||
BillingAddress | String | False |
Billing address of a contact. | ||
BillingAddressId | String | True |
ContactAddresses.AddressId |
Id of a billing address. | |
BillingAddressAttention | String | False |
Name of a person in billing address. | ||
BillingAddressCity | String | False |
City of a billing address. | ||
BillingAddressCountry | String | False |
Country of a billing address. | ||
BillingAddressFax | String | False |
Fax of a billing address. | ||
BillingAddressPhone | String | False |
Phone number of a billing address. | ||
BillingAddressState | String | False |
State of a billing address. | ||
BillingAddressStateCode | String | False |
State code of a billing address. | ||
BillingAddressStreet2 | String | False |
Street two of a billing address. | ||
BillingAddressZip | String | False |
ZIP code of a billing address. | ||
CanShowCustomerOb | Boolean | True |
Check if contact can show customer ob. | ||
CanShowVendorOb | Boolean | True |
Check if contact can show vendor ob. | ||
CompanyName | String | False |
Name of the company. | ||
ContactType | String | False |
Contact type of the contact. | ||
ContactCategory | String | True |
Category of this contact. | ||
ContactName | String | False |
Display Name of the contact. Max-length [200]. | ||
ContactPersons | String | False |
Contact persons of a contact. | ||
CustomFields | String | False |
Custom fields of the contact. | ||
ContactSalutation | String | True |
Salutation of a contact. | ||
CreatedTime | Datetime | True |
Time at which the contact was created. | ||
CreditLimit | Decimal | False |
Credit limit for a customer. | ||
CreditLimitExceededAmount | Decimal | True |
Amount if the credit limit exceeded. | ||
CurrencyId | String | False |
Currency ID of the customer's currency. | ||
CurrencyCode | String | True |
Currency code of the customer's currency. | ||
CurrencySymbol | String | True |
Currency symbol of the customer's currency. | ||
CustomerSubType | String | False |
Sub type of a customer. | ||
DefaultTemplatesBillTemplateId | String | False |
ID of a bill template in default template. | ||
DefaultTemplatesBillTemplateName | String | True |
Name of a bill template in default template. | ||
DefaultTemplatesCreditnoteEmailTemplateId | String | False |
ID of a credit note email template in default template. | ||
DefaultTemplatesCreditnoteEmailTemplateName | String | True |
Name of a credit note email template in default template. | ||
DefaultTemplatesCreditnoteTemplateId | String | False |
ID of a credit note template in default template. | ||
DefaultTemplatesCreditnoteTemplateName | String | True |
Name of a credit note template in default template. | ||
DefaultTemplatesEstimateEmailTemplateId | String | False |
ID of a estimate email template in default template. | ||
DefaultTemplatesEstimateEmailTemplateName | String | True |
Name of a estimate email template in default template. | ||
DefaultTemplatesEstimateTemplateId | String | False |
ID of a estimate template in default template. | ||
DefaultTemplatesEstimateTemplateName | String | True |
Name of a estimate template in default template. | ||
DefaultTemplatesInvoiceEmailTemplateId | String | False |
ID of a invoice email template in default template. | ||
DefaultTemplatesInvoiceEmailTemplateName | String | True |
Name of a invoice email template in default template. | ||
DefaultTemplatesInvoiceTemplateId | String | False |
ID of a invoice template in default template. | ||
DefaultTemplatesInvoiceTemplateName | String | True |
Name of a invoice template in default template. | ||
DefaultTemplatesPaymentRemittanceEmailTemplateId | String | False |
ID of a payment remittance template in default template. | ||
DefaultTemplatesPaymentRemittanceEmailTemplateName | String | True |
Name of a payment remittance template in default template. | ||
DefaultTemplatesPaymentthankyouEmailTemplateId | String | False |
ID of a payment thank you email template in default template. | ||
DefaultTemplatesPaymentthankyouEmailTemplateName | String | True |
Name of a payment thank you email template in default template. | ||
DefaultTemplatesPaymentthankyouTemplateId | String | False |
ID of a payment thank you template in default template. | ||
DefaultTemplatesPaymentthankyouTemplateName | String | True |
Name of a payment thank you template in default template. | ||
DefaultTemplatesPurchaseorderEmailTemplateId | String | False |
ID of a purchase order email template in default template. | ||
DefaultTemplatesPurchaseorderEmailTemplateName | String | True |
Name of a purchase order email template in default template. | ||
DefaultTemplatesPurchaseorderTemplateId | String | False |
ID of a purchase order template in default template. | ||
DefaultTemplatesPurchaseorderTemplateName | String | True |
Name of a purchase order template in default template. | ||
DefaultTemplatesSalesorderEmailTemplateId | String | False |
ID of a sales order email template in default template. | ||
DefaultTemplatesSalesorderEmailTemplateName | String | True |
Name of a sales order email template in default template. | ||
DefaultTemplatesSalesorderTemplateId | String | False |
ID of a sales order template in default template. | ||
DefaultTemplatesSalesorderTemplateName | String | True |
Name of a sales order template in default template. | ||
String | True |
Email ID of a contact. | |||
ExchangeRate | Decimal | False |
Exchange rate of the currency. | ||
String | False |
Facebook profile account. max-length [100]. | |||
HasTransaction | Boolean | True |
Check if this contact has transaction. | ||
IsClientReviewAsked | Boolean | True |
Check if the client review is asked. | ||
IsClientReviewSettingsEnabled | Boolean | True |
Check if the client review settings is enabled for this contact. | ||
IsSmsEnabled | Boolean | True |
Check if SMS is enabled. | ||
IsPortalEnabled | Boolean | False |
To enable client portal for the contact. Allowed value is true and false. | ||
LanguageCode | String | False |
Language code used for a contact. | ||
LastModifiedTime | Datetime | True |
The time of last modification of the contact. | ||
Mobile | String | True |
Mobile number of a contact. | ||
Notes | String | False |
Notes of a contact. | ||
OpeningBalanceAmount | Decimal | False |
Opening balance amount of a contact. | ||
OpeningBalanceAmountBcy | Decimal | True |
Base Currency of Opening balance amount of a contact. | ||
OutstandingPayableAmount | Decimal | True |
Outstanding OB payable amount of a contact. | ||
OutstandingReceivableAmount | Decimal | True |
Outstanding OB receivable amount of a contact. | ||
OwnerId | String | False |
ID of the owner. | ||
OwnerName | String | True |
Name of the owner. | ||
PaymentReminderEnabled | Boolean | True |
Check if payment reminder is enabled. | ||
PaymentTerms | Integer | False |
Net payment term for the customer. | ||
PaymentTermsLabel | String | False |
Label for the paymet due details. | ||
PortalStatus | String | True |
Status of a portal. | ||
PricePrecision | Integer | True |
The precision for the price. | ||
PricebookId | String | True |
ID of a price book. | ||
PricebookName | String | True |
Name of a price book. | ||
PrimaryContactId | String | True |
Primary ID of a contact. | ||
SalesChannel | String | True |
Channel of sales. | ||
ShippingAddress | String | False |
Shipment Address. | ||
ShippingAddressId | String | True |
ContactAddresses.AddressId |
ID of a shipping address. | |
ShippingAddressAttention | String | False |
Name of a person of shipping address. | ||
ShippingAddressCity | String | False |
City of a shipping address. | ||
ShippingAddressCountry | String | False |
Country of a shipping address. | ||
ShippingAddressFax | String | False |
Fax of a shipping address. | ||
ShippingAddressPhone | String | False |
Phone number of a shipping address. | ||
ShippingAddressState | String | False |
State of a shipping address. | ||
ShippingAddressStateCode | String | False |
State code of a shipping address. | ||
ShippingAddressStreet2 | String | False |
Street two details of a shipping address. | ||
ShippingAddressZip | String | False |
Zip code of a shipping address. | ||
Source | String | True |
Source of the contact. | ||
Status | String | True |
Status of the contact. | ||
String | True |
Twitter account. | |||
TaxAuthorityName | String | False |
Enter tax authority name. | ||
Tags | String | False |
Tags. | ||
UnusedCreditsPayableAmount | Decimal | True |
Payable amount of Unused credits of a contact. | ||
UnusedCreditsPayableAmountBcy | Decimal | True |
Base Currency Payable amount of Unused credits of a contact. | ||
UnusedCreditsReceivableAmount | Decimal | True |
Receivable amount of Unused credits of a contact. | ||
UnusedCreditsReceivableAmountBcy | Decimal | True |
Base Currency Receivable amount of Unused credits of a contact. | ||
UnusedRetainerPayments | Decimal | True |
Payment of the contact which is unused. | ||
Website | String | False |
Link of a website. |
To list, add, update and delete a Credit Note.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
Note: CreditnoteId is required in order to query CreditNoteDetails.
SELECT * FROM CreditNoteDetails WHERE CreditnoteId = '1895452000000083136'
Insert can be executed by specifying CustomerId,Date,LineItems,CreditnoteNumber column. The columns that are not read-only can be inserted optionally. 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')
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'
Delete can be executed by specifying the CreditNoteId in the WHERE Clause
For example:
DELETE FROM CreditNoteDetails WHERE CreditnoteId = '3285934000000265005'
Name | Type | ReadOnly | References | SupportedOperators | Description |
CreditnoteId [KEY] | String | True |
CreditNotes.CreditnoteId |
ID of a credit note. | |
CreditnoteNumber | String | False |
Number of a credit note. | ||
Adjustment | Decimal | True |
Adjustments made to the credit note. | ||
AdjustmentDescription | String | True |
Description of adjustments made to the credit note. | ||
ApproverId | String | True |
Users.UserId |
ID of an approver. | |
AvataxUseCode | String | False |
Used to group like customers for exemption purposes. It is a custom value that links customers to a tax rule. | ||
AvataxTaxCode | String | False |
A tax code is a unique label used to group items together. | ||
AvataxExemptNo | String | False |
Exemption certificate number of the customer. | ||
Balance | Decimal | True |
The unpaid amount. | ||
BillingAddress | String | True |
Billing address of a credit note. | ||
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. | ||
ColorCode | String | True |
Color code of a credit note. | ||
ContactCategory | String | True |
Category of a contact. | ||
ContactPersons | String | False |
Contact persons of a contact. | ||
CreatedById | String | True |
Users.UserId |
Id of a user who has created credit note. | |
CreatedTime | Datetime | True |
Time at which the credit note was created. | ||
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 a credit note. | ||
CurrentSubStatusId | String | True |
Current sub status ID of a credit note. | ||
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 credit note. | ||
Discount | String | True |
Discount given to specific item in credit note. | ||
DiscountAmount | Decimal | True |
Amount of discount. | ||
DiscountAppliedOnAmount | Decimal | True |
Discount applied on amount. | ||
DiscountType | String | True |
Type of discount. | ||
ExchangeRate | Decimal | False |
Exchange rate of the currency. | ||
FiledInVatReturnId | String | True |
VAT return ID of credit note which was filed. | ||
FiledInVatReturnName | String | True |
VAT return name of credit note which was filed. | ||
FiledInVatReturnType | String | True |
VAT return type of credit note which was filed. | ||
GstNo | String | False |
GST number used for credit note. | ||
GstReason | String | True |
Reason for GST given for credit note. | ||
GstReturnDetailsReturnPeriod | String | True |
Period for GST return. | ||
GstReturnDetailsStatus | String | True |
Status of GST return details. | ||
GstTreatment | String | False |
Choose whether the credit note is GST registered/unregistered/consumer/overseas. . | ||
HasNextCreditnote | Boolean | True |
Check if it has credit note. | ||
InvoiceId | String | True |
Invoices.InvoiceId |
Invoice ID for credit note. | |
InvoiceNumber | String | True |
Invoice number for credit note. | ||
IsDiscountBeforeTax | Boolean | True |
Check if the discount is applied before tax in credit note. | ||
IsDraft | Boolean | False |
Set to true if credit note has to be created in draft status. | ||
IsEmailed | Boolean | True |
Check if the credit note is emailed. | ||
IsEwayBillRequired | Boolean | True |
Check if eway bill is required for credit note. | ||
IsInclusiveTax | Boolean | False |
Check if the credit note is inclusive tax. | ||
IsPreGst | Boolean | True |
Check if pre GST is applied. | ||
IsTaxable | Boolean | True |
Check if this credit note is taxable. | ||
LastModifiedById | String | True |
Users.UserId |
Id of the user last modified. | |
LastModifiedTime | Datetime | True |
The time of last modification of the credit note. | ||
LineItems | String | False |
Line items of an estimate. | ||
Notes | String | False |
Notes for this credit note. | ||
Orientation | String | True |
Orientation of a page. | ||
PageHeight | String | True |
Height of a page. | ||
PageWidth | String | True |
Width of a page. | ||
PlaceOfSupply | String | False |
The place of supply is where a transaction is considered to have occurred for VAT purposes. | ||
PricePrecision | Integer | True |
The precision for the price. | ||
ReasonForCreditnote | String | True |
Any specific reason for taking credit note. | ||
ReferenceNumber | String | False |
Reference number of credit note. | ||
ReverseChargeTaxTotal | Decimal | True |
Total amount to pay the liability of tax. | ||
RoundoffValue | Decimal | True |
Rounding off the values to precise number. | ||
SalespersonId | String | True |
ID of a sales person. | ||
SalespersonName | String | True |
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 details of a shipping address. | ||
ShippingAddressZip | String | True |
Zip code of a shipping address. | ||
ShippingCharge | Decimal | True |
Shipping charge of credit note. | ||
Status | String | True |
Status of the credit note. | ||
SubTotal | Decimal | True |
Sub total of credit notes. | ||
SubTotalInclusiveOfTax | Decimal | True |
Subtotal amount which are inclusive of tax. | ||
SubmittedBy | String | True |
Detail of the user who has submitted the credit note. | ||
SubmittedDate | Date | True |
Date when credit note was submitted. | ||
SubmitterId | String | True |
Users.UserId |
Id of a submitter of credit note. | |
TaxId | String | False |
Taxes.TaxId |
Id of the tax | |
TaxSpecification | String | True |
Working of tax when specifying special tax options and tax methods for earnings codes. | ||
TaxTotal | Decimal | True |
Total amount of Tax. | ||
TaxTreatment | String | False |
VAT treatment for the Credit Note. | ||
TemplateId | String | False |
ID of a template. | ||
TemplateName | String | True |
Name of a tempalte. | ||
TemplateType | String | True |
Type of a template. | ||
Terms | String | False |
Terms and Conditions apply of a credit note. | ||
Total | Decimal | True |
Total of credit notes. | ||
TotalCreditsUsed | Decimal | True |
Total credits used for credit note. | ||
TotalRefundedAmount | Decimal | True |
Total amount refunded for a credit note. | ||
TransactionRoundingType | String | True |
Type of round off used for transaction. | ||
VatTreatment | String | False |
VAT treatment for the credit note. | ||
IgnoreAutoNumberGeneration | Boolean | False |
Set to true if you need to provide your own credit note number. |
To list, add, update and delete currencies configured. Also, get the details of a currency.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
SELECT * FROM Currencies WHERE CurrencyId = '1894553000000000099' SELECT * FROM Currencies WHERE CurrencyFilter = 'Currencies.ExcludeBaseCurrency'
Insert can be executed by specifying the CurrencyCode,CurrencyFormat columns. The columns that are not read-only can be inserted optionally. Following is an example of how to insert into this table.
INSERT INTO Currencies (currencycode,currencyformat) VALUES ('AUD','1,234,567.89')
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 CurrencyCode = 'AUD',CurrencyFormat='1,234,567.89', PricePrecision='2' WHERE CurrencyId = '3285934000000000105'
Delete can be executed by specifying the CurrencyId in the WHERE Clause
For example:
DELETE FROM Currencies WHERE CurrencyId = '3285934000000000105'
Name | Type | ReadOnly | References | SupportedOperators | Description |
CurrencyId [KEY] | String | True |
Currency Id of the customer's currency. | ||
CurrencyName | String | True |
Name of a currency. | ||
CurrencyCode | String | False |
Code of a currency. | ||
CurrencyFormat | String | False |
Format of a currency. | ||
CurrencySymbol | String | False |
Symbol of a currency. | ||
EffectiveDate | Date | True |
Date which the exchange rate is applicable for the currency. | ||
ExchangeRate | Decimal | True |
Exchange rate of the currency. | ||
IsBaseCurrency | Boolean | True |
Check of it is a base currency. | ||
PricePrecision | Integer | False |
The precision for the price. |
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 |
CurrencyFilter | String |
Filter currencies excluding base currency. The allowed values are Currencies.ExcludeBaseCurrency. |
Create, Read, Update, Delete contact persons. Also, get the contact person details.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
SELECT * FROM CustomerContacts WHERE ContactId = '1864553000000072009' AND CustomerContactId = '1896253000000071011'
Insert can be executed by specifying FirstName,CONTACTID columns. The columns that are not read-only can be inserted optionally. Following is an example of how to insert into this table.
INSERT INTO CustomerContacts (FirstName,CONTACTID) VALUES ('customercontactspersons','3285934000000085043')
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 can be executed by specifying the CustomerContactId in the WHERE Clause
For example:
DELETE FROM CUSTOMERCONTACTS WHERE CustomerContactId = '3285934000000266024'
Name | Type | ReadOnly | References | SupportedOperators | Description |
CustomerContactId [KEY] | String | True |
ID of a contact person. | ||
ContactId | String | False |
Contacts.ContactId |
Id of a contact. | |
ContactName | String | True |
Display Name of the contact. Max-length [200]. | ||
CreatedTime | Datetime | True |
Time at which the contact person was created. | ||
CurrencyCode | String | True |
Currency code used for this contact person. | ||
Department | String | False |
Department on which a person belongs. . | ||
Designation | String | False |
Designation of a person. | ||
String | False |
Email ID of contact person. | |||
EnablePortal | Boolean | False |
Option to enable the portal access. allowed values true,false | ||
Fax | String | True |
Fax ID of contact person. | ||
FirstName | String | False |
First name of the contact person. | ||
IsPrimaryContact | Boolean | False |
Check if it is a primary contact. | ||
LastName | String | False |
Last name of contact person. | ||
Mobile | String | False |
Mobile number of a contact person. | ||
Phone | String | False |
Phone number of a contact person. | ||
Salutation | String | False |
Salutation of a contact person. | ||
Skype | String | False |
Skype ID of contact person. |
To list, add, update and delete details of a payment.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
Note: PaymentId is required in order to query CustomerPaymentDetails.
SELECT * FROM CustomerPaymentDetails WHERE PaymentId = '1894553000000083001'
Insert can be executed by specifying CustomerId,PaymentMode,Amount,Date,InvoiceId,AmountApplied columns. The columns that are not read-only can be inserted optionally. 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 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 can be executed by specifying the PaymentId in the WHERE Clause
For example:
DELETE FROM CustomerPaymentDetails WHERE PaymentId = '3285934000000269021'
Name | Type | ReadOnly | References | SupportedOperators | Description |
PaymentId [KEY] | String | True |
CustomerPayments.PaymentId |
ID of a payment. | |
AccountId | String | False |
BankAccounts.AccountId |
ID of the Bank Account. | |
AccountName | String | True |
Name of the account. | ||
AccountType | String | True |
Type of the account. | ||
Amount | Decimal | False |
Amount of the customer payments. | ||
AmountApplied | Decimal | False |
Amount paid for the invoice. | ||
AttachmentName | String | True |
Name of the attachment. | ||
BankCharges | Decimal | False |
Charges of bank. | ||
CanSendInMail | Boolean | True |
Check if the customer payment can be send in mail. | ||
CanSendPaymentSms | Boolean | True |
Check if the customer payment can send payment SMS. | ||
CardType | String | True |
Type of card. | ||
CurrencyCode | String | True |
Currency code of the customer's currency. | ||
CurrencyId | String | True |
Currencies.CurrencyId |
Currency ID of the customer's currency. | |
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. | ||
ContactPersons | String | False |
Contact persons of a contact. | ||
Date | Date | False |
Date of a customer payment. | ||
Description | String | False |
Description of the customer payment. | ||
DiscountAmount | Decimal | True |
Total discount amount applied in customer payment. | ||
ExchangeRate | Decimal | False |
Exchange rate of the currency. | ||
IsClientReviewSettingsEnabled | Boolean | True |
Check if the client review settings is enabled or not. | ||
IsPaymentDetailsRequired | Boolean | True |
Check if the payment details is required. | ||
IsPreGst | Boolean | True |
Check if pre GST is applied. | ||
InvoiceId | String | False |
Invoices.InvoiceId |
Invoice ID for credit note. | |
LastFourDigits | String | True |
It store the last four digits of customer's card details. | ||
OnlineTransactionId | String | True |
ID of online transaction. | ||
Orientation | String | True |
Orientation of the page. | ||
PageHeight | String | True |
Height of the page. | ||
PageWidth | String | True |
Width of the page. | ||
PaymentMode | String | False |
Mode through which payment is made. | ||
PaymentNumber | Integer | True |
Number through which payment is made. | ||
PaymentNumberPrefix | String | True |
Prefix of the payment number. | ||
PaymentNumberSuffix | String | True |
Suffix of the payment number. | ||
ProductDescription | String | True |
Description of the product. | ||
ReferenceNumber | String | False |
Reference number of a customer payment. | ||
SettlementStatus | String | True |
Status of the settlement. | ||
TaxAccountId | String | True |
BankAccounts.AccountId |
Account ID of tax. | |
TaxAccountName | String | True |
Account name of tax. | ||
TaxAmountWithheld | Decimal | False |
Amount withheld for tax. | ||
TemplateId | String | True |
ID of a template. | ||
TemplateName | String | True |
Name of a template. | ||
TemplateType | String | True |
Type of a template. | ||
UnusedAmount | Decimal | True |
Unused amount of the customer payment. | ||
Invoices | String | False |
List of invoices associated with the payment. |
To list, add, update and delete details of an estimate.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
Note: EstimateId is required in order to query EstimateDetails.
SELECT * FROM EstimateDetails WHERE EstimateId = '1894553000000077244'
Insert can be executed by specifying the Customerid,lineitems column. The columns that are not read-only can be inserted optionally. 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)
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'
Delete can be executed by specifying the EstimateId in the WHERE Clause
For example:
DELETE FROM EstimateDetails WHERE EstimateId = '3285934000000263048'
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. | ||
Description | String | False |
The description of the line items. | ||
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. | ||
ItemId | String | False |
Items.ItemId |
ID of the customer or vendor. | |
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. | ||
LineItemId | String | False |
ID of the line item. Mandatory, if the existing line item has to be updated. If empty, a new line item will be created. | ||
Notes | String | False |
Notes of Estimate. | ||
Name | String | False |
The name of the line item. | ||
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. | ||
Quantity | Decimal | False |
The quantity of line item. | ||
Rate | Decimal | False |
Rate of the line item. | ||
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 | True |
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. |
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. |
To list, add, update and delete details of an Expense.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
Note: ExpenseId is required in order to query ExpenseDetails.
SELECT * FROM ExpenseDetails WHERE ExpenseId = '1894553000000077244'
Insert can be executed by specifying AccountId,Date,Amount,PaidThroughAccountId columns. The columns that are not read-only can be inserted optionally. 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 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 can be executed by specifying the ExpenseId in the WHERE Clause
For example:
DELETE FROM ExpenseDetails WHERE ExpenseId = '3285934000000262014'
Name | Type | ReadOnly | References | SupportedOperators | Description |
ExpenseId [KEY] | String | True |
Expenses.ExpenseId |
Id of an expense. | |
AccountId | String | False |
BankAccounts.AccountId |
ID of the Bank Account. | |
AccountName | String | True |
Name of the account. | ||
Amount | Decimal | False |
Amount of the expenses. | ||
ApproverEmail | String | True |
Email of an approver. | ||
ApproverId | String | True |
Users.UserId |
Id of an approver. | |
ApproverName | String | True |
Name of an approver. | ||
AcquisitionVatId | String | False |
This is the ID of the tax applied in case this is an EU - goods expense and acquisition VAT needs to be reported. | ||
BcySurchargeAmount | Decimal | True |
Surcharge amount of Base Currency. | ||
BcyTotal | Decimal | True |
Total Base Currency. | ||
CanReclaimVatOnMileage | String | False |
To specify if tax can be reclaimed for this mileage expense. | ||
CreatedById | String | True |
Users.UserId |
Id of a user who has created expense. | |
CreatedTime | Datetime | True |
Time at which the Expense was created. | ||
CurrencyCode | String | True |
Currency code of the customer's currency. | ||
CurrencyId | String | False |
Currencies.CurrencyId |
Currency ID of the customer's currency. | |
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 |
Expense date. | ||
Description | String | False |
Description of the expense. | ||
DestinationOfSupply | String | False |
Place where the goods/services are supplied to. (If not given, organisation's home state will be taken). | ||
Distance | String | False |
Distance travelled for a particular mileage expense where mileage_type is manual | ||
EmployeeEmail | String | True |
Email ID of an employee. | ||
EmployeeId | String | False |
Employees.EmployeeId |
ID of an employee. | |
EmployeeName | String | True |
Name of an employee. | ||
EndReading | String | False |
End Reading of the Odometer. | ||
ExchangeRate | Decimal | False |
Exchange rate of the currency. | ||
ExpenseItemId | String | True |
Item ID of an expense. | ||
ExpenseReceiptName | String | True |
Receipt name of an expense. | ||
ExpenseReceiptType | String | True |
Receipt type of an expense. | ||
ExpenseType | String | True |
Type of expense. | ||
EngineCapacityRange | String | False |
Engine capacity range for a particular mileage expense. Allowed Values: less_than_1400cc, between_1400cc_and_1600cc, between_1600cc_and_2000cc and more_than_2000cc. | ||
HSNORSAC | String | False |
Add HSN/SAC code for your goods/services. | ||
FcySurchargeAmount | Decimal | True |
Surcharge amount of Foreign Currency. | ||
FuelType | String | False |
Fuel type for a particular mileage expense. Allowed Values: petrol, lpg and diesel | ||
GstNo | String | False |
GST number used for credit note. | ||
InvoiceConversionType | String | True |
Type of invoice conversion. | ||
InvoiceId | String | True |
Invoices.InvoiceId |
ID of an invoice. | |
InvoiceNumber | String | True |
Number of an invoice. | ||
IsBillable | Boolean | False |
Check if the expense is billable. | ||
IsInclusiveTax | Boolean | False |
Check if the expense is inclusive tax. | ||
IsPersonal | Boolean | True |
Check if the expense is personal. | ||
IsPreGst | Boolean | True |
Check if the pre GST is applied in the expense. | ||
IsRecurringApplicable | Boolean | True |
Check if the recurring is applicable. | ||
IsReimbursable | Boolean | True |
Check if the expense is reimbursable. | ||
IsSurchargeApplicable | Boolean | True |
Check if the surcharge is applicable in this expense. | ||
LastModifiedById | String | True |
Users.UserId |
Id of the user last modified. | |
LastModifiedTime | Datetime | True |
The time of last modification of the expense. | ||
LineItems | String | False |
Line items of an estimate. | ||
Location | String | True |
Location of the expense. | ||
MerchantId | String | True |
ID of the merchant. | ||
MerchantName | String | True |
Name of the merchant. | ||
MileageRate | Double | False |
Mileage rate for a particular mileage expense. | ||
MileageType | String | False |
Type of Mileage. | ||
MileageUnit | String | False |
Unit of the distance travelled. | ||
PaidThroughAccountId | String | False |
BankAccounts.AccountId |
Account ID from which expense amount was paid. | |
PaidThroughAccountName | String | True |
Account name from which expense amount was paid. | ||
PaymentMode | String | True |
Mode through which payment is made. | ||
ProjectId | String | False |
Projects.ProjectId |
ID of the project. | |
ProjectName | String | True |
Name of the project. | ||
ProductType | String | False |
Type of the expense. This denotes whether the expense is to be treated as a goods or service purchase. | ||
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 expense. | ||
ReportId | String | True |
ID of the report. | ||
ReportName | String | True |
Name of the report. | ||
ReportNumber | String | True |
Number of the report. | ||
ReportStatus | String | True |
Status of the report. | ||
ReverseChargeVatId | String | False |
This is the ID of the tax applied in case this is a non UK - service expense and reverse charge VAT needs to be reported. | ||
ReverseChargeTaxId | String | False |
ID of the reverse charge tax. | ||
StartReading | String | False |
Start Reading of the Odometer. | ||
Status | String | True |
Status of the expense. | ||
SubTotal | Decimal | True |
Sub total of expenses. | ||
SourceOfSupply | String | False |
Place from where the goods/services are supplied. (If not given, place of contact given for the contact will be taken). | ||
Tags | String | True |
Details of tags related to expenses. | ||
TaxAmount | Decimal | True |
Amount of tax. | ||
TaxId | String | False |
Taxes.TaxId |
Id of tax. | |
TaxName | String | True |
Name of tax. | ||
TaxPercentage | Integer | True |
Percentage of tax. | ||
Total | Decimal | True |
Total of expenses. | ||
TransactionType | String | True |
Type of the Transaction. | ||
TripId | String | True |
ID of a trip. | ||
TripNumber | String | True |
Number of a trip. | ||
UserEmail | String | True |
Email ID of a User. | ||
UserId | String | True |
Users.UserId |
ID of a user. | |
UserName | String | True |
Name of a user. | ||
VehicleId | String | True |
ID of a vehicle. | ||
VehicleType | String | False |
Vehicle type for a particular mileage expense. Allowed Values: car, van, motorcycle and bike. | ||
VehicleName | String | True |
Name of a vehicle. | ||
VendorId | String | False |
ID of the vendor the expenses has been made. | ||
VendorName | String | True |
Name of the vendor the expenses has been made. | ||
VatTreatment | String | False |
VAT treatment for the estimates. | ||
TaxTreatment | String | False |
VAT treatment for the Estimate. |
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 |
Receipt | String |
Expense receipt file to attach. Allowed Extensions: gif, png, jpeg, jpg, bmp, pdf, xls, xlsx, doc and docx. |
To list, add, update and delete details of an invoice.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
Note: InvoiceId is required in order to query InvoiceDetails.
SELECT * FROM InvoiceDetails WHERE InvoiceId = '1864543000000078539'
Insert can be executed by specifying CustomerId,LineItems columns. The columns that are not read-only can be inserted optionally. Following is an example of how to insert into this table.
Insert INTO InvoiceLineItems#TEMP(Name,itemid) values('Cloth-Jeans12','3285934000000104097')
INSERT INTO InvoiceDetails (Customerid,lineitems) VALUES ('3285934000000104002',InvoiceLineItems#Temp)
Update can be executed by specifying the InvoiceId in the WHERE Clause. The columns that are not read-only can be Updated.
For example:
Insert INTO InvoiceLineItems#TEMP(Name,itemid) values('Cloth-Jeans2','3285934000000104097')
"UPDATE InvoiceDetails SET Customerid = '3285934000000104002',lineitems='InvoiceLineItems#Temp' WHERE InvoiceId = '3285934000000264005'
Delete can be executed by specifying the InvoiceId in the WHERE Clause
For example:
DELETE FROM InvoicesDetails WHERE InvoiceId = '3285934000000264005'
Name | Type | ReadOnly | References | SupportedOperators | Description |
InvoiceId [KEY] | String | True |
Invoices.InvoiceId |
Id of an invoice. | |
InvoiceNumber | String | False |
Number of an invoice. | ||
InvoiceUrl | String | True |
URL of an invoice. | ||
AchPaymentInitiated | Boolean | True |
Check if the Automated Clearing House payment is initiated. | ||
AchSupported | Boolean | True |
Check if Automated Clearing House is supported. | ||
Adjustment | Decimal | False |
Adjustments made to the invoice. | ||
AdjustmentDescription | String | False |
Description of adjustments made to the invoice. | ||
AllowPartialPayments | Boolean | False |
Check if invoice can allow partial payments. | ||
ApproverId | String | True |
Users.UserId |
ID of an approver. | |
AttachmentName | String | True |
Name of the attachment. | ||
Balance | Decimal | True |
The unpaid amount. | ||
BcyAdjustment | Decimal | True |
Adjustment of base currency. | ||
BcyDiscountTotal | Decimal | True |
Total discount applied in base currency. | ||
BcyShippingCharge | Decimal | True |
Shipping charge applied in base currency. | ||
BcySubTotal | Decimal | True |
Sub total of base currency. | ||
BcyTaxTotal | Decimal | True |
Tax total of base currency. | ||
BcyTotal | Decimal | True |
Total Base Currency. | ||
BillingAddress | String | True |
Billing address of an invoice. | ||
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. | ||
ContactPersons | String | False |
Contact persons of a contact. | ||
CanSendInMail | Boolean | True |
Check if invoice can be send in mail. | ||
CanSendInvoiceSms | Boolean | True |
Check if invoice can be send in SMS. | ||
ClientViewedTime | String | True |
Last time when client viewed the invoice. | ||
ColorCode | String | True |
Color code of invoice. | ||
ContactCategory | String | True |
Category of a contact. | ||
ContactPersonsDetails | String | True |
Details of a contact person. | ||
CreatedById | String | True |
Users.UserId |
Id of a user who has created invoice. | |
CreatedTime | Datetime | True |
Time at which the invoice was created. | ||
CreditsApplied | Decimal | True |
Applied credits for invoice. | ||
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 |
Symbol of currency. | ||
CurrentSubStatus | String | True |
Current sub status of an invoice . | ||
CurrentSubStatusId | String | True |
Current sub status ID of an invoice . | ||
CustomerDefaultBillingAddress | String | True |
Customer default billing address of an invoice. | ||
CustomerDefaultBillingAddressCity | String | True |
City of a billing address. | ||
CustomerDefaultBillingAddressCountry | String | True |
Country of a billing address. | ||
CustomerDefaultBillingAddressFax | String | True |
Fax of a billing address. | ||
CustomerDefaultBillingAddressPhone | String | True |
Phone number of a billing address. | ||
CustomerDefaultBillingAddressState | String | True |
State of a 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 | False |
Name of the customer or vendor. | ||
Date | Date | False |
Date of an invoice. | ||
Discount | String | False |
Discount given to specific item in invoice. | ||
DiscountAmount | Decimal | True |
Amount of discount. | ||
DiscountAppliedOnAmount | Decimal | True |
Discount applied on amount. | ||
DiscountPercent | Decimal | True |
Percent of discount applied. | ||
DiscountTotal | Decimal | True |
Total amount get on discount. | ||
DiscountType | String | False |
Type of discount. | ||
DueDate | Date | False |
Delivery date of the invoice. | ||
EcommOperatorGstNo | String | True |
GST number of the ecommerce operator. | ||
EcommOperatorId | String | True |
ID of the ecommerce operator. | ||
EcommOperatorName | String | True |
Name of the ecommerce operator. | ||
EstimateId | String | True |
Estimates.EstimateId |
ID of an estimate. | |
Ewaybills | String | True |
Electronic way bills of the invoice. | ||
ExchangeRate | Decimal | False |
Exchange rate of the currency. | ||
ExpenseId | String | False |
Expenses.ExpenseId |
Id of the tax | |
FiledInVatReturnId | String | True |
VAT return ID of bill which was filed. | ||
FiledInVatReturnName | String | True |
VAT return name of bill which was filed. | ||
FiledInVatReturnType | String | True |
VAT return type of bill which was filed. | ||
GstNo | String | False |
GST number. | ||
GstReturnDetailsReturnPeriod | String | True |
Return period of GST. | ||
GstReturnDetailsStatus | String | True |
Status of GST return. | ||
GstTreatment | String | False |
Choose whether the invoice is GST registered/unregistered/consumer/overseas. . | ||
HasNextInvoice | Boolean | True |
Check if it has next invoice. | ||
IsAutobillEnabled | Boolean | True |
Check if the autobill is enabled for this invoice. | ||
IsClientReviewSettingsEnabled | Boolean | True |
Check if the client review settings is enabled or not. | ||
IsDiscountBeforeTax | Boolean | False |
Check if the invoice is discounted before tax. | ||
IsEmailed | Boolean | True |
Check if the invoice can be emailed. | ||
IsEwayBillRequired | Boolean | True |
Check if the eway bill is required. | ||
IsInclusiveTax | Boolean | False |
Check if the invoice is inclusive tax. | ||
IsPreGst | Boolean | True |
Check if pre GST is applied. | ||
IsTaxable | Boolean | True |
Check if invoice is taxable. | ||
IsViewedByClient | Boolean | True |
Check if the invoice is viewed by client. | ||
InvoicedEstimateId | Boolean | False |
ID of the invoice from which the invoice is created. | ||
LastModifiedById | String | True |
Users.UserId |
Id of the user last modified. | |
LastModifiedTime | Datetime | True |
The time of last modification of the invoice. | ||
LastPaymentDate | Date | True |
Date when last payment was made. | ||
LastReminderSentDate | Date | True |
Date when last reminder was sent for an invoice. | ||
LineItems | String | False |
Line Items | ||
MerchantGstNo | String | True |
GST number of a merchant. | ||
MerchantId | String | True |
ID of a merchant. | ||
MerchantName | String | True |
Name of a merchant. | ||
NoOfCopies | Integer | True |
Total number of copies for invoice. | ||
Notes | String | False |
Notes for this invoice. | ||
Orientation | String | True |
Orientation of the page. | ||
PageHeight | String | True |
Height of the page. | ||
PageWidth | String | True |
Width of the page. | ||
PaymentDiscount | Decimal | True |
Discount applied on payment. | ||
PaymentExpectedDate | Date | True |
Expected date of payment. | ||
PaymentMade | Decimal | True |
Total amount of payment made. | ||
PaymentReminderEnabled | Boolean | True |
Check if the payment reminder is enabled for the invoice. | ||
PaymentTerms | Integer | False |
Net payment term for the customer. | ||
PaymentTermsLabel | String | False |
Label for the paymet due details. | ||
PlaceOfSupply | String | False |
The place of supply is where a transaction is considered to have occurred for VAT purposes. | ||
PricePrecision | Integer | True |
The precision for the price. | ||
ReaderOfflinePaymentInitiated | Boolean | True |
Check if the payment for offline reader is initiated. | ||
ReasonForDebitNote | String | True |
Description of having the debit note. | ||
RecurringInvoiceId | String | False |
RecurringInvoices.RecurringInvoiceId |
Id of a recurring invoice. | |
ReferenceNumber | String | False |
Reference number of invoice. | ||
RemindersSent | Integer | True |
Total number of reminders sent for this invoice. | ||
ReverseChargeTaxTotal | Decimal | True |
Total amount of reverse charge tax. | ||
RoundoffValue | Decimal | True |
Rounding off the values to precise number. | ||
SalesorderId | String | True |
SalesOrders.SalesorderId |
ID of a sales order. | |
SalesorderItemId | String | False |
SalesOrderLineItems.LineItemId |
Id of the tax | |
SalespersonId | String | True |
ID of a sales person. | ||
SalespersonName | String | False |
Name of a sales person. | ||
ScheduleTime | String | True |
Time scheduled for the invoice. | ||
ShipmentCharges | String | True |
Charges deducted for shipment. | ||
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 of invoice. | ||
ShippingBills | String | True |
Shipping bills of invoice. | ||
ShowNoOfCopies | Boolean | True |
Check if the invoice can show number of copies. | ||
Status | String | True |
Status of the invoice. | ||
StopReminderUntilPaymentExpectedDate | Boolean | True |
Check if reminder can be stopped untill the payment expected date. | ||
SubTotal | Decimal | True |
Sub total of the invoice. | ||
SubTotalInclusiveOfTax | Decimal | True |
Subtotal amount which are inclusive of tax. | ||
SubmittedBy | String | True |
Detail of the user who has submitted the invoice. | ||
SubmittedDate | Date | True |
Date when invoice was submitted. | ||
SubmitterId | String | True |
Users.UserId |
Id of the invoice submitter. | |
TaxAmountWithheld | Decimal | True |
Amount withheld for tax. | ||
TaxRegNo | String | True |
Registration number of tax. | ||
TaxSpecification | String | True |
Working of tax when specifying special tax options and tax methods for earnings codes. | ||
TaxTotal | Decimal | True |
Total number ot tax applied in the invoice. | ||
TaxId | String | False |
Taxes.TaxId |
Id of the tax | |
TaxTreatment | String | False |
VAT treatment for the Invoice. | ||
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 invoice. | ||
Total | Decimal | True |
Total of invoices. | ||
TransactionRoundingType | String | True |
Type of round off used for transaction. | ||
Type | String | True |
Types of invoice. | ||
UnusedRetainerPayments | Decimal | True |
Payment of the invoice which is unused. | ||
VatTreatment | String | False |
VAT treatment for the bills. | ||
WriteOffAmount | Decimal | True |
Amount to be write off. |
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. |
To list, add, update and delete details of an existing item.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
Note: ItemId is required in order to query ItemDetails.
SELECT * FROM ItemDetails WHERE ItemId = '1894553000000079049'
Insert can be executed by specifying the Name,Rate column. The columns that are not read-only can be inserted optionally. Following is an example of how to insert into this table.
Insert INTO ItemDetails(Name,Rate) values('Bottle','500')
Update can be executed by specifying the ItemId in the WHERE Clause. The columns that are not read-only can be Updated.
For example:
Update ItemDetails set Name='Bottle', Rate='550' where ItemId='3285934000000269037'
Delete can be executed by specifying the ItemId in the WHERE Clause
For example:
DELETE FROM ItemDetails WHERE ItemId = '3285934000000269037'
Name | Type | ReadOnly | References | SupportedOperators | Description |
ItemId [KEY] | String | True |
Items.ItemId |
ID of an item. | |
ItemType | String | False |
Type of an item. | ||
AccountId | String | False |
BankAccounts.AccountId |
ID of the Bank Account. | |
AccountName | String | True |
Name of the account. | ||
AvataxUseCode | String | False |
Used to group like customers for exemption purposes. It is a custom value that links customers to a tax rule. | ||
AvataxTaxCode | String | False |
A tax code is a unique label used to group items together. | ||
Brand | String | True |
Brand of the item. | ||
CreatedTime | Datetime | True |
Time at which the item was created. | ||
Description | String | False |
Description of an item. | ||
HSNORSAC | String | False |
Add HSN/SAC code for your goods/services. | ||
ImageName | String | True |
Name of the image. | ||
IsTaxable | Boolean | False |
Boolean to track the taxability of the item. | ||
InventoryAccountId | String | False |
ID of the stock account to which the item has to be associated with. Mandatory, if item_type is inventory. | ||
LastModifiedTime | Datetime | True |
The time of last modification of the item. | ||
Manufacturer | String | True |
Company that makes goods for sale. | ||
Name | String | False |
Name of an item. | ||
PricebookRate | Decimal | True |
Rate of pricebook. | ||
ProductType | String | False |
Type of the product. | ||
PurchaseAccountId | String | False |
BankAccounts.AccountId |
Account ID of purchase items. | |
PurchaseAccountName | String | True |
Account name of purchase items. | ||
PurchaseDescription | String | False |
Description of purchase items. | ||
PurchaseRate | Decimal | False |
Rate of purchase items. | ||
Rate | Decimal | False |
Rate of the item. | ||
SalesChannels | String | True |
Total channels exists for sales. | ||
SalesRate | Decimal | True |
The rate of sale in the item. | ||
Sku | String | False |
Stock Keeping Unit value of item, should be unique throughout the product. | ||
Source | String | True |
Source of the item. | ||
Status | String | True |
Status of the item. | ||
Tags | String | True |
Details of tags related to items. | ||
TaxId | String | False |
Taxes.TaxId |
Id of tax. | |
TaxName | String | True |
Name of the tax. | ||
TaxPercentage | Integer | False |
Percentage applied for tax. | ||
TaxType | String | True |
Type of tax. | ||
VendorId | String | False |
ID of the vendor the expenses has been made. | ||
Unit | String | True |
Number of quantity of item. | ||
ReorderLevel | String | False |
Reorder level of the item. | ||
InitialStock | String | False |
Opening stock of the item. | ||
InitialStockRate | String | False |
Unit price of the opening stock. | ||
ItemTaxPreferences | String | False |
Item Tax Preferences. |
To list, add, update and delete journals.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud. By default, response will be showing the journals of current month only
SELECT * FROM Journals WHERE Total < 1000 AND Total >= 119 SELECT * FROM Journals WHERE JournalFilter = 'JournalDate.All'
Name | Type | ReadOnly | References | SupportedOperators | Description |
JournalId [KEY] | String | True |
Id of a journal. | ||
JournalType | String | False |
Type of a journal. | ||
JournalDate | Date | False |
Date of a journal. | ||
BcyTotal | Decimal | True |
Total Base Currency | ||
CurrencyId | String | False |
Currencies.CurrencyId |
Currency ID of the customer's currency. | |
CustomFields | String | True |
Custom Fields defined for Journal | ||
EntityType | String | True |
Entity type of a journal. | ||
EntryNumber | String | True |
Entry number of the journal. | ||
ExchangeRate | Decimal | False |
Exchange rate of the currency. | ||
IncludeInVatReturn | String | False |
VAT treatment for the estimates. | ||
IsBasAdjustment | Boolean | False |
Check if Journal is created for BAS Adjustment. | ||
Notes | String | False |
Notes of the journal. | ||
ReferenceNumber | String | False |
Reference number of the journal. | ||
Status | String | False |
Status of the journal | ||
Total | Decimal | True | =,<,<=,>,>= |
Total of journals. Search by journal total. This field will be populated with a value only when the Journal Id is specified. | |
JournalNumberPrefix | String | True |
Prefix for journal number. This field will be populated with a value only when the Journal Id is specified. | ||
JournalNumberSuffix | String | True |
Suffix for journal number. This field will be populated with a value only when the Journal Id is specified. | ||
CreatedTime | Datetime | True |
Time at which the journal was created. This field will be populated with a value only when the Journal Id is specified. | ||
CurrencyCode | String | True |
Currency code of the customer's currency. This field will be populated with a value only when the Journal Id is specified. | ||
CurrencySymbol | String | True |
Currency symbol of the customer's currency. This field will be populated with a value only when the Journal Id is specified. | ||
ExchangeRate | Decimal | True |
Exchange rate of the currency. This field will be populated with a value only when the Journal Id is specified. | ||
LastModifiedTime | Datetime | True |
Last Modified Time of a journal. This field will be populated with a value only when the Journal Id is specified. | ||
LineItemTotal | Decimal | True |
Total number of line items included in a journal. This field will be populated with a value only when the Journal Id is specified. | ||
PricePrecision | Integer | True |
The precision for the price This field will be populated with a value only when the Journal Id is specified. | ||
ProjectId | String | True |
Projects.ProjectId |
ID of a project. This field will be populated with a value only when the Journal Id is specified. | |
ProjectName | String | True |
Name of a project. This field will be populated with a value only when the Journal Id is specified. | ||
ProductType | String | False |
Type of the journal. This denotes whether the journal is to be treated as goods or service. | ||
VatTreatment | String | False |
VAT treatment for the estimates. | ||
LineItems | String | False |
Line items of an estimate. | ||
TaxExemptionCode | String | False |
Code of a tax exemption. | ||
TaxExemptionType | String | False |
Type of the Tax Exemption. |
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 |
Date | Date |
Date of a journal. |
VendorId | String |
Vendor ID of a journal. |
JournalFilter | String |
Filter journals by journal date. The allowed values are JournalDate.All, JournalDate.Today, JournalDate.ThisWeek, JournalDate.ThisMonth, JournalDate.ThisQuarter, JournalDate.ThisYear. |
CustomerId | String |
Id of a customer |
To list, add, update and delete opening balance.
No filters are supported server side for this table. All criteria will be handled client side within the Cloud.
Name | Type | ReadOnly | References | SupportedOperators | Description |
OpeningBalanceId [KEY] | String | True |
ID of an opening balance. | ||
CanShowCustomerOb | Boolean | True |
Check if opening balance can show customer ob. | ||
CanShowVendorOb | Boolean | True |
Check if opening balance can show vendor ob. | ||
Date | Date | False |
Date of an opening balance. | ||
PricePrecision | Integer | True |
The precision for the price | ||
Total | Decimal | True |
Total of opening balances. | ||
Accounts | String | False |
Accounts. |
To list, add, update and delete projects.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
SELECT * FROM Projects WHERE CustomerId = '1894553000000078233' AND ProjectId = '1894553000000078363' SELECT * FROM Projects WHERE Status = 'Active'
Name | Type | ReadOnly | References | SupportedOperators | Description |
ProjectId [KEY] | String | True |
Id of the project. | ||
ProjectName | String | False |
Name of the project. | ||
Rate | Decimal | False |
Hourly rate for a task. | ||
Status | String | True |
Status of the project. The allowed values are All, Active, Inactive. | ||
BillableHours | String | True |
Total number of billable hours. | ||
BillingType | String | False |
Type of billing. | ||
CanBeInvoiced | Boolean | True |
Check if the projecy can be invoiced. | ||
CreatedTime | Datetime | True |
Time at which the project was created. | ||
CustomerId | String | False |
Contacts.ContactId |
ID of the customer or vendor. Search projects by customer id. | |
CustomerName | String | True |
Name of the customer or vendor. | ||
CostBudgetAmount | Decimal | False |
Budgeted Cost to complete this project. | ||
BudgetAmount | String | False |
Give value, if you are estimating total project revenue budget. | ||
Description | String | False |
Description of the projects | ||
HasAttachment | Boolean | True |
Check if the projects has attachment. | ||
OtherServiceAppSource | String | True |
Source of other service app. | ||
TotalHours | String | True |
Total hours spent in the project. | ||
ShowInDashboard | Boolean | True |
Check if the project can be shown in dashboard. This field will be populated with a value only when the Id is specified. | ||
ProjectHeadId | String | True |
ID of the project head. This field will be populated with a value only when the Id is specified. | ||
ProjectHeadName | String | True |
Name of the project head. This field will be populated with a value only when the Id is specified. | ||
BillableAmount | Decimal | True |
Amount which is billable for this project. This field will be populated with a value only when the Id is specified. | ||
BilledAmount | Decimal | True |
Total amount which was billed for the project. This field will be populated with a value only when the Id is specified. | ||
BilledHours | String | True |
Total number of billed hours. This field will be populated with a value only when the Id is specified. | ||
BudgetThreshold | Decimal | True |
To determine how much money to allocate to the reserve fund each fiscal year. This field will be populated with a value only when the Id is specified. | ||
BudgetType | String | False |
Type of budget. This field will be populated with a value only when the Id is specified. | ||
BudgetHours | String | False |
Task budget hours. | ||
CurrencyCode | String | True |
Code of currency used in the project. This field will be populated with a value only when the Id is specified. | ||
IsBudgetThresholdNotificationEnabled | Boolean | True |
Check if the budget threshold notification is enabled or not. This field will be populated with a value only when the Id is specified. | ||
IsClientApprovalNeeded | Boolean | True |
Check if the client approval is needed. This field will be populated with a value only when the Id is specified. | ||
IsExpenseInclusive | Integer | True |
Check if the expense is inclusive in the project. This field will be populated with a value only when the Id is specified. | ||
IsUserApprovalNeeded | Boolean | True |
Check if the user approval is needed. This field will be populated with a value only when the Id is specified. | ||
IsValidProjectHead | Boolean | True |
Check if the project has valid project head. This field will be populated with a value only when the Id is specified. | ||
NonBillableAmount | Decimal | True |
Amount which are non billable for the project.. This field will be populated with a value only when the Id is specified. | ||
NonBillableHours | String | True |
Hours which are non billable for the project. This field will be populated with a value only when the Id is specified. | ||
TotalAmount | Decimal | True |
Total amount for the project. This field will be populated with a value only when the Id is specified. | ||
TotalAmountExpenseInclusive | Decimal | True |
Total amount for the project including the project. This field will be populated with a value only when the Id is specified. | ||
UnBilledAmount | Decimal | True |
Total amount unbilled for the project. This field will be populated with a value only when the Id is specified. | ||
UnBilledHours | String | True |
Total number of unbilled hours. This field will be populated with a value only when the Id is specified. | ||
UserId | String | False |
Users.UserId |
ID of the user to be added to the project. | |
Tasks | String | False |
Tasks. | ||
Users | String | False |
Users. | ||
UnusedRetainerPayments | Decimal | True |
Payment of the project which is unused. This field will be populated with a value only when the Id is specified. |
To list, add, update and delete details of a purchase order.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
Note: PurchaseorderId is required in order to query PurchaseOrderDetails.
SELECT * FROM PurchaseOrderDetails WHERE PurchaseorderId = '1894553000000087078'
Insert can be executed by specifying Vendorid,lineitems columns. The columns that are not read-only can be inserted optionally. Following is an example of how to insert into this table.
Insert INTO PurchaseorderLineItems#TEMP(Name,itemid,rate,quantity,accountid) values('Cloth-Jeans','3285934000000104097','1700','1','3285934000000034001')
INSERT INTO PurchaseorderDetails (Vendorid,lineitems) VALUES ('3285934000000104023',PurchaseorderLineItems#Temp)
Update can be executed by specifying the PurchaseorderId in the WHERE Clause. The columns that are not read-only can be Updated.
For example:
Insert INTO PurchaseorderLineItems#TEMP(Name,itemid,rate,quantity,accountid) values('Cloth-Jeans','3285934000000104097','1700','1','3285934000000034001')
UPDATE PurchaseOrderDetails SET Vendorid = '3285934000000104002',lineitems='PurchaseorderLineItems#Temp' WHERE PurchaseorderId = '3285934000000264005'
Delete can be executed by specifying the PurchaseorderId in the WHERE Clause
For example:
DELETE FROM PurchaseOrderDetails WHERE PurchaseOrderId = '3350895000000089001'
Name | Type | ReadOnly | References | SupportedOperators | Description |
PurchaseorderId [KEY] | String | True |
PurchaseOrders.PurchaseorderId |
ID of a purchase order. | |
PurchaseorderNumber | String | False |
Number of purchase order. | ||
ReferenceNumber | String | False |
Reference number of purchase order. | ||
Adjustment | Decimal | True |
Adjustments made to the purchase order. | ||
AdjustmentDescription | String | True |
Description of adjustments made to the purchase order. | ||
ApproverId | String | True |
Users.UserId |
ID of an approver. | |
AttachmentName | String | True |
Name of the attachment. | ||
Attention | String | False |
Name of a person in purchase order. | ||
BilledStatus | String | True |
Status of bill. | ||
BillingAddressId | Long | False |
ID of the Billing Address. | ||
BillingAddress | String | True |
Billing address of a purchase order. | ||
BillingAddressAttention | String | True |
Name of the person of bill order. | ||
BillingAddressCity | String | True |
City of billing address. | ||
BillingAddressCountry | String | True |
Country of billing address. | ||
BillingAddressFax | String | True |
Fax number of billing address. | ||
BillingAddressPhone | String | True |
Phone number of billing address. | ||
BillingAddressState | String | True |
State of billing address. | ||
BillingAddressStreet2 | String | True |
Street two of billing address. | ||
BillingAddressZip | String | True |
Zip code of billing address. | ||
CanMarkAsBill | Boolean | True |
Check if purchase order can be mark as bill. | ||
CanMarkAsUnbill | Boolean | True |
Check if purhcase order can be mark as unbill. | ||
CanSendInMail | Boolean | True |
Check if purchase order can be sent in mail. | ||
ClientViewedTime | Datetime | True |
Last time when client viewed the purchase order. | ||
ColorCode | String | True |
Color code. | ||
ContactCategory | String | True |
Category of contacts. | ||
CreatedById | String | True |
Users.UserId |
Contact ID who have created this purchase order. | |
CreatedTime | Datetime | True |
Time at which the purchase order was created. | ||
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 a purchase order. | ||
CurrentSubStatusId | String | True |
Current sub status ID of a purchase order. | ||
ContactPersons | String | False |
Contact persons of a contact. | ||
CustomFields | String | True |
Custom Fields defined for Journal | ||
Date | Date | False |
Purchase order date. | ||
Documents | String | False |
List of files to be attached to a particular transaction. | ||
DeliveryAddress | String | True |
Delivery address. | ||
DeliveryAddress1 | String | True |
Delivery address one. | ||
DeliveryAddress2 | String | True |
Delivery address two. | ||
DeliveryAddressCity | String | True |
City of delivery address. | ||
DeliveryAddressCountry | String | True |
Country of delivery address. | ||
DeliveryAddressOrganizationAddressId | String | True |
Id or organization address of delivery address. | ||
DeliveryAddressPhone | String | True |
Phone number of delivery address. | ||
DeliveryAddressState | String | True |
State of delivery address. | ||
DeliveryAddressZip | String | True |
Zip code of delivery address. | ||
DeliveryCustomerId | String | False |
Contacts.ContactId |
ID of a customer of delivery address. | |
DueDate | Date | False |
Delivery date of purchase order.. | ||
DeliveryDate | Date | False |
Date of delivery. | ||
DeliveryOrgAddressId | String | False |
Delivery address ID of an organization. | ||
Discount | String | False |
Discount given to specific item in purchase order. | ||
DiscountAccountId | String | False |
BankAccounts.AccountId |
Account ID of discount. | |
DiscountAmount | Decimal | True |
Amount of discount. | ||
DiscountAppliedOnAmount | Double | True |
Discount applied on amount. | ||
ExchangeRate | Decimal | False |
Exchange rate of the currency. | ||
ExpectedDeliveryDate | Date | True |
Expected delivery date of purchased product. | ||
HasQtyCancelled | Boolean | True |
Check if the quantity of a purchase order has been cancelled. | ||
IsDiscountBeforeTax | Boolean | False |
Check if purchase order applied discount before tax. | ||
IsDropShipment | Boolean | True |
Check if purchase order have drop shipment. | ||
IsEmailed | Boolean | True |
Check if purchase order is emailed or not. | ||
IsInclusiveTax | Boolean | False |
Check if the purchase order is inclusive tax. | ||
IsPreGst | Boolean | True |
Check if purchase order includes pre GST. | ||
IsViewedByClient | Boolean | True |
Check if purchase order is viewed by client. | ||
IsUpdateCustomer | Boolean | False |
Check if customer should be updated. | ||
LastModifiedTime | Datetime | True |
The time of last modification of the purchase order. | ||
Notes | String | False |
Notes for this purchase order. | ||
OrderStatus | String | True |
Status of order. | ||
Orientation | String | True |
Orientation of the page. | ||
PageHeight | String | True |
Height of the page. | ||
PageWidth | String | True |
Width of the page. | ||
PricePrecision | Integer | True |
The precision for the price | ||
PricebookId | String | False |
ID of the pricebook. | ||
SalesorderId | String | False |
SalesOrders.SalesorderId |
ID of the Sales Order. | |
ShipVia | String | False |
Mode of shipping the item. | ||
ShipViaId | String | True |
ID of mode through which shipping was done of items. | ||
Status | String | True |
Status of the purchase order | ||
SubTotal | Decimal | True |
Sub total of Purhcase order. | ||
SubTotalInclusiveOfTax | Decimal | True |
Subtotal amount which are inclusive of tax. | ||
SubmittedBy | String | True |
Detail of the user who has submitted the purchase order. | ||
SubmittedDate | Date | True |
Date of the submission. | ||
SubmitterId | String | True |
Users.UserId |
ID of the submitter. | |
TaxTotal | Decimal | True |
Total amount of tax. | ||
TemplateId | String | False |
ID of the template. | ||
TemplateName | String | True |
Name of the template | ||
TemplateType | String | True |
Type of template. | ||
Terms | String | False |
Terms and Conditions apply of a purchase order. | ||
Total | Decimal | True |
Total of purchase orders. | ||
VendorId | String | False |
ID of the vendor the purchase order has been made. | ||
VendorName | String | True |
Name of the vendor the purchase order has been made. | ||
GstTreatment | String | False |
Choose whether the vendor credit is GST registered/unregistered/consumer/overseas. | ||
VatTreatment | String | False |
VAT treatment for the vendor credit. | ||
TaxTreatment | String | False |
VAT treatment for the Vendor Credit. | ||
GstNo | String | False |
GST number. | ||
SourceOfSupply | String | False |
Source of supply. | ||
PlaceOfSupply | String | False |
The place of supply is where a transaction is considered to have occurred for VAT purposes. | ||
DestinationOfSupply | String | False |
Place where the goods/services are supplied to. | ||
LineItems | String | False |
Line items of an estimate. |
To list, add, update and delete details of a recurring expense.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
Note: RecurringExpenseId is required in order to query RecurringExpenseDetails.
SELECT * FROM RecurringExpenseDetails WHERE RecurringExpenseId = '1801553000000089750'
Name | Type | ReadOnly | References | SupportedOperators | Description |
RecurringExpenseId [KEY] | String | True |
RecurringExpenses.RecurringExpenseId |
Id of a recurring expense. | |
AccountId | String | False |
BankAccounts.AccountId |
ID of the Bank Account | |
AccountName | String | True |
Name of the account. | ||
Amount | Decimal | False |
Amount of the recurring expenses. | ||
BcyTotal | Decimal | True |
Total Base Currency. | ||
CreatedTime | Datetime | True |
Time at which the recurring expense was created. | ||
CurrencyCode | String | True |
Currency code of the customer's currency. | ||
CurrencyId | String | False |
Currencies.CurrencyId |
Currency ID of the customer's currency. | |
CustomerId | String | False |
Contacts.ContactId |
ID of the customer or vendor. | |
CustomerName | String | True |
Name of the customer or vendor. | ||
Description | String | True |
Description of the recurring expense. | ||
EmployeeEmail | String | True |
Email of an employee. | ||
EmployeeId | String | True |
Employees.EmployeeId |
Id of an employee. | |
EmployeeName | String | True |
Name of an employee. | ||
EndDate | Date | False |
End date of a recurring expense. | ||
ExchangeRate | Decimal | False |
Exchange rate of a recurring expense. | ||
IsBillable | Boolean | False |
Check if recurring expense is billable. | ||
IsInclusiveTax | Boolean | False |
Check if recurring expense is inclusive tax. | ||
IsPreGst | Boolean | True |
Check if recurring expense is pre GST. | ||
LastCreatedDate | Date | True |
Last created date of a recurring expense. | ||
LastModifiedTime | Datetime | True |
The time of last modification of the recurring expense. | ||
MileageRate | Double | True |
Mileage rate for a particular mileage expense. | ||
MileageUnit | String | True |
Unit of the distance travelled. | ||
NextExpenseDate | Date | True |
Next date of expense to be paid. | ||
PaidThroughAccountId | String | True |
BankAccounts.AccountId |
Account ID from which expense is paid through. | |
PaidThroughAccountName | String | True |
Account name from which expense is paid through. | ||
ProjectId | String | False |
Projects.ProjectId |
ID of a project. | |
ProjectName | String | True |
Name of the project. | ||
RecurrenceFrequency | String | False |
Frequency of a recurrence. | ||
RecurrenceName | String | False |
Name of a recurrence | ||
RepeatEvery | Integer | False |
Recurrence time of an expense. | ||
StartDate | Date | False |
Start date of recurring expense. | ||
Status | String | True |
Status of the recurring expense. | ||
SubTotal | Decimal | True |
Sub total of recurring expenses. | ||
Tags | String | True |
Details of tags related to recurring expenses. | ||
TaxAmount | Decimal | True |
Amount of a tax. | ||
TaxId | String | False |
Taxes.TaxId |
Id of a tax. | |
TaxName | String | True |
Name of a tax. | ||
TaxPercentage | Integer | True |
Percentage of a tax. | ||
Total | Decimal | True |
Total of recurring expenses. | ||
VendorId | String | True |
ID of the vendor the recurring expense has been made. | ||
VendorName | String | True |
Name of the vendor the recurring expense has been made. | ||
GstNo | String | False |
GST number. | ||
SourceOfSupply | String | False |
Place from where the goods/services are supplied. (If not given, place of contact given for the contact will be taken). | ||
DestinationOfSupply | String | False |
Place where the goods/services are supplied to. | ||
PlaceOfSupply | String | False |
The place of supply is where a transaction is considered to have occurred for VAT purposes. | ||
LineItems | String | False |
Line items of an estimate. | ||
VatTreatment | String | False |
VAT treatment for the bills. | ||
TaxTreatment | String | False |
VAT treatment for the Bill. | ||
ProductType | String | False |
Type of the journal. This denotes whether the journal is to be treated as goods or service. | ||
AcquisitionVatId | String | False |
This is the ID of the tax applied in case this is an EU - goods expense and acquisition VAT needs to be reported. | ||
ReverseChargeVatId | String | False |
This is the ID of the tax applied in case this is a non UK - service expense and reverse charge VAT needs to be reported. |
To list, add, update and delete details of a recurring invoice.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
Note: RecurringInvoiceId is required in order to query RecurringInvoiceDetails.
SELECT * FROM RecurringInvoiceDetails WHERE RecurringInvoiceId = '1895453000000042244'
Insert can be executed by specifying RecurrenceName,CustomerId,RecurrenceFrequency, LineItems columnS. The columns that are not read-only can be inserted optionally. Following is an example of how to insert into this table.
Insert INTO RecurringInvoiceLineItems#TEMP(Name,itemid,rate,quantity) values('Cloth-Jeans','3285934000000104097','1700','1')
Insert INTO RecurringInvoiceDetails(RecurrenceName,CustomerId,RecurrenceFrequency, LineItems) values('MonthlyInvoice','3285934000000104002','weeks',RecurringInvoiceLineItems#TEMP)
Update can be executed by specifying the RECURRINGINVOICEID in the WHERE Clause. The columns that are not read-only can be Updated.
For example:
Insert INTO RecurringInvoiceLineItems#TEMP(Name,itemid,rate,quantity) values('Cloth-Jeans','3285934000000104097','1700','1')
Update RecurringInvoiceDetails set RecurrenceName='MonthlyInvoice',CustomerId='3285934000000104002',RecurrenceFrequency='weeks', LineItems='RecurringInvoiceLineItems#TEMP' WHERE RECURRINGINVOICEID='3285934000000268005'
Delete can be executed by specifying the RECURRINGINVOICEID in the WHERE Clause
For example:
DELETE FROM RecurringInvoiceDetails WHERE RECURRINGINVOICEID = '3285934000000268005'
Name | Type | ReadOnly | References | SupportedOperators | Description |
RecurringInvoiceId [KEY] | String | True |
RecurringInvoices.RecurringInvoiceId |
Id of a recurring invoice. | |
ActualChildInvoicesCount | Integer | True |
Count total number of actual child invoices. | ||
Adjustment | Decimal | False |
Adjustments made to the recurring invoices. | ||
AdjustmentDescription | String | False |
Description of adjustments made to the recurring invoices. | ||
AllowPartialPayments | Boolean | True |
Check if the recuuring invoice can allow partial payments. | ||
AvataxUseCode | String | False |
Used to group like customers for exemption purposes. It is a custom value that links customers to a tax rule. | ||
AvataxTaxCode | String | False |
A tax code is a unique label used to group items together. | ||
AvataxExemptNo | String | False |
Exemption certificate number of the customer. | ||
BcyAdjustment | Decimal | True |
Adjustment of base currency. | ||
BcyDiscountTotal | Decimal | True |
Total discount applied in base currency. | ||
BcyShippingCharge | Decimal | True |
Shipping charge applied in base currency. | ||
BcySubTotal | Decimal | True |
Sub total of base currency. | ||
BcyTaxTotal | Decimal | True |
Tax total of base currency. | ||
BcyTotal | Decimal | True |
Total Base Currency. | ||
BillingAddress | String | False |
Billing address of a recurring invoice. | ||
BillingAddressAttention | String | False |
Name of a person in billing address. | ||
BillingAddressCity | String | False |
City of a billing address. | ||
BillingAddressCountry | String | False |
Country of a billing address. | ||
BillingAddressFax | String | False |
Fax of a billing address. | ||
BillingAddressPhone | String | False |
Phone number of a billing address. | ||
BillingAddressState | String | False |
State of a billing address. | ||
BillingAddressStreet2 | String | False |
Street two of a billing address. | ||
BillingAddressZip | String | False |
ZIP code of a billing address. | ||
ChildEntityType | String | True |
Entity type of a child in recurring invoice. | ||
CompanyName | String | True |
Name of the company. | ||
ContactCategory | String | True |
Category of the contact. | ||
ContactPersons | String | False |
Contact persons of a contact. | ||
CreatedById | String | True |
Users.UserId |
Id of a user who has created recurring invoice. | |
CreatedTime | Datetime | True |
Time at which the recurring invoice was created. | ||
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 |
Symbol of the currency. | ||
CustomerEmail | String | True |
Email address of the customer. | ||
CustomerMobilePhone | String | True |
Mobile phone number of customer. | ||
CustomerId | String | False |
Contacts.ContactId |
ID of the customer or vendor. | |
CustomerName | String | True |
Name of the customer or vendor. | ||
CustomerPhone | String | True |
Phone number of a customer. | ||
Discount | String | False |
Discount given to specific item in recurring invoice. | ||
DiscountAmount | Decimal | True |
Total amount which was discounted. | ||
DiscountAppliedOnAmount | Decimal | True |
Amount from which discount was applied. | ||
DiscountPercent | Double | True |
Percentage of discount applied. | ||
DiscountTotal | Decimal | True |
Total amount get on discount. | ||
DiscountType | String | False |
Type to get discount in recurring invoice. | ||
String | False |
Email address of the customer. | |||
EndDate | Date | False |
End date for the statement. | ||
ExchangeRate | Decimal | False |
Exchange rate of the currency. | ||
IsDiscountBeforeTax | Boolean | False |
Check if the recurring invoice is discounted before tax. | ||
IsInclusiveTax | Boolean | False |
Check if the expense is inclusive tax. | ||
IsPreGst | Boolean | True |
Check if pre GST is applied. | ||
ItemId | String | False |
Items.ItemId |
ID of the customer or vendor. | |
LastModifiedById | String | True |
Users.UserId |
Id of the user last modified. | |
LastModifiedTime | Datetime | True |
The time of last modification of the recurring invoice | ||
LastSentDate | Date | True |
The date at which the last recurring invoice was sent. | ||
LineItems | String | False |
Line items of an estimate. | ||
ManualChildInvoicesCount | Integer | True |
Count of manual child invoices. | ||
NextInvoiceDate | Date | True |
Date of a next invoice. | ||
Notes | String | True |
Notes for this recurring invoice. | ||
Orientation | String | True |
Orientation of a page. | ||
PlaceOfSupply | String | False |
The place of supply is where a transaction is considered to have occurred for VAT purposes. | ||
PageHeight | String | True |
Height of the page. | ||
PageWidth | String | True |
Width of the page. | ||
PaidInvoicesTotal | Decimal | True |
Total number of paid invoices. | ||
PaymentTerms | Integer | False |
Net payment term for the customer. | ||
PaymentTermsLabel | String | False |
Label for the paymet due details. | ||
PaymentOptionsPaymentGateways | String | False |
Payment Gateway used for payment. | ||
PhotoUrl | String | True |
Photo URL for recurring invoices. | ||
PricePrecision | Integer | True |
The precision for the price. | ||
ProjectDetails | String | True |
Details of project. | ||
RecurrenceFrequency | String | False |
Type of recurrence frequency the invoice is recurring. | ||
RecurrenceName | String | False |
Name of the recurrence. | ||
ReferenceNumber | String | True |
Reference number of a recurring invoice. | ||
RepeatEvery | Integer | False |
Recurrence time of the invoice. | ||
RoundoffValue | Decimal | True |
Rounding off the values to precise number. | ||
SalespersonId | String | True |
ID of a sales person. | ||
SalespersonName | String | False |
Name of a sales person. | ||
ShipmentCharges | String | True |
Shipment charges of recurring invoice. | ||
ShippingAddress | String | False |
Shipment Address. | ||
ShippingAddressAttention | String | False |
Name of a person of shipping address. | ||
ShippingAddressCity | String | False |
City of a shipping address. | ||
ShippingAddressCountry | String | False |
Country of a shipping address. | ||
ShippingAddressFax | String | False |
Fax of a shipping address. | ||
ShippingAddressPhone | String | False |
Phone number of a shipping address. | ||
ShippingAddressState | String | False |
State of a shipping address. | ||
ShippingAddressStreet2 | String | False |
Street two details of a shipping address. | ||
ShippingAddressZip | String | False |
Zip code of a shipping address. | ||
ShippingCharge | Decimal | True |
Shipping charge. | ||
StartDate | Date | False |
Starting date of recurring invoice. | ||
Status | String | True |
Status of the recurring invoice. | ||
SubTotal | Decimal | True |
Sub total of recurring invoices. | ||
SubTotalInclusiveOfTax | Decimal | True |
Subtotal amount which are inclusive of tax. | ||
TaxId | String | False |
Taxes.TaxId |
Id of the tax | |
TaxTotal | Decimal | True |
Total amount for tax. | ||
TemplateId | String | False |
ID of a template. | ||
TemplateName | String | True |
Name of a template. | ||
Terms | String | True |
Terms and Conditions apply of a recurring invoice. | ||
Total | Decimal | True |
Total of recurring invoices. | ||
TransactionRoundingType | String | True |
Type of round off used for transaction. | ||
UnpaidChildInvoicesCount | String | True |
Count of total number of unpaid child invoices. | ||
UnpaidInvoicesBalance | Decimal | True |
Total amount of unpaid invoices. | ||
VatTreatment | String | False |
VAT treatment for the estimates. | ||
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. | ||
Name | String | False |
The name of the line item. | ||
Description | String | False |
The description of the line items. | ||
Rate | Decimal | False |
Rate of the line item. | ||
Quantity | Integer | False |
The quantity of line item. | ||
Unit | String | False |
Unit of the line item e.g. kgs, Nos. |
To list, add, update and delete of a retainer invoice.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
Note: RetainerInvoiceId is required in order to query RetainerInvoiceDetails.
SELECT * FROM RetainerInvoiceDetails WHERE RetainerInvoiceId = '1894663000000085023'
Insert can be executed by specifying CustomerId, LineItems columns. The columns that are not read-only can be inserted optionally. Following is an example of how to insert into this table.
Insert INTO RetainerInvoiceLineItems#TEMP(description,rate) values('Cloth description','1700')
Insert INTO RetainerInvoiceDetails(CustomerId, LineItems) values('3285934000000104002',RetainerInvoiceLineItems#TEMP)
Update can be executed by specifying the RetainerINVOICEID in the WHERE Clause. The columns that are not read-only can be Updated.
For example:
Insert INTO RetainerInvoiceLineItems#TEMP(description,rate) values('Cloth description updated','1700')
Update RetainerInvoiceDetails set CustomerId='3285934000000104002',LineItems='RetainerInvoiceLineItems#TEMP' WHERE RetainerINVOICEID='3285934000000268036'
Delete can be executed by specifying the RetainerINVOICEID in the WHERE Clause
For example:
DELETE FROM RetainerInvoiceDetails WHERE RetainerINVOICEID = '3285934000000268036'
Name | Type | ReadOnly | References | SupportedOperators | Description |
RetainerInvoiceId [KEY] | String | True |
RetainerInvoices.RetainerInvoiceId |
Id of retainer invoice. | |
RetainerinvoiceNumber | String | True |
Number of a retainer invoice. | ||
AllowPartialPayments | Boolean | True |
Check if the retainer invoice allows partial payments. | ||
AttachmentName | String | True |
Name of the attachment. | ||
Balance | Decimal | True |
Total amount left. | ||
BillingAddress | String | True |
Billing address of a retainer invoice. | ||
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 retainer invoice can be send in mail. | ||
ClientViewedTime | Datetime | True |
Last time when client viewed retainer invoice. | ||
ColorCode | String | True |
Color code of retainer invoice. | ||
ContactPersons | String | False |
Contact Persons | ||
CreatedById | String | True |
Users.UserId |
Id of a user who has created retainer invoice. | |
CreatedTime | Datetime | True |
Time at which the retainer invoice was created. | ||
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 |
Symbol of currency used for retainer invoice. | ||
CurrentSubStatus | String | True |
Current sub status of a retainer invoice. | ||
CurrentSubStatusId | String | True |
Current sub status ID of a retainer invoice. | ||
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 a retainer invoice. | ||
ExchangeRate | Decimal | True |
Exchange rate of the currency. | ||
InvoiceUrl | String | True |
URL of invoice. | ||
IsEmailed | Boolean | True |
Check if the retainer invoice is emailed. | ||
IsInclusiveTax | Boolean | True |
Check if the retainer invoice is inclusive of tax. | ||
IsPreGst | Boolean | True |
Check if the retainer invoice is applied by pre GST. | ||
IsViewedByClient | Boolean | True |
Check if retainer invoice is viewed by client. | ||
LastModifiedById | String | True |
Users.UserId |
Id of a user who has last modified the retainer invoice. | |
LastModifiedTime | Datetime | True |
The time of last modification of the retainer invoice. | ||
LastPaymentDate | Date | True |
Date of payment which was last paid. | ||
Notes | String | False |
Notes of retainer invoice. | ||
Orientation | String | True |
Orientation of a page. | ||
PageHeight | String | True |
Height of a page. | ||
PageWidth | String | True |
Width of a page. | ||
PaymentDrawn | Decimal | True |
The payment which was drawn for retainer invoice. | ||
PaymentOptionPaymentGateways | String | False |
Payment options for the retainer invoice, online payment gateways and bank accounts. | ||
PaymentMade | Decimal | True |
Payment which was made for the invoice. | ||
PricePrecision | Integer | True |
The precision for the price. | ||
ReferenceNumber | String | False |
Reference number of a retainer invoice. | ||
RoundoffValue | Decimal | True |
Round Off value. | ||
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. | ||
Status | String | True |
Status of the retainer invoice. | ||
SubTotal | Decimal | True |
Sub total of retainer invoices. | ||
SubmittedBy | String | True |
Detail of the user who has submitted the retainer invoice. | ||
SubmittedDate | Date | True |
Date of submission of retainer invoice. | ||
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 retainer invoice. | ||
Total | Decimal | True |
Total of retainer invoices. | ||
TransactionRoundingType | String | True |
Type of round off used for transaction. | ||
VatTreatment | String | True |
VAT treatment for the retainer invoice. | ||
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. | ||
UnusedRetainerPayments | Decimal | True |
Payment of the retainer invoice which is unused. | ||
LineItems | String | False |
Line items of an estimate. |
To list, add, update and delete a sales order.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
Note: SalesorderId is required in order to query SalesOrderDetails.
SELECT * FROM SalesOrderDetails WHERE SalesorderId = '1894553000000077349'
Insert can be executed by specifying Name,itemid,rate,quantity columns. The columns that are not read-only can be inserted optionally. 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)
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'
Delete can be executed by specifying the SalesorderID in the WHERE Clause
For example:
DELETE FROM SalesorderDetails WHERE SalesorderID = '3285934000000259151'
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. | |
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 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. | ||
ContactCategory | String | True |
Category 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. | |
CreatedTime | Datetime | True |
Time at which the sales order was created. | ||
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 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. | ||
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. . | ||
HasQtyCancelled | Boolean | True |
Check if the sales order quantity has been cancelled. | ||
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. | ||
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. | ||
PriceBookId | String | False |
ID of a pricebook. | ||
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 | True |
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. | |
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 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 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. |
To list, add, update and delete tasks added to a project. Also, get the details of a task.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
SELECT * FROM Tasks WHERE ProjectId = '1894553000000078367' AND TaskId = '1894553000000085708'
Name | Type | ReadOnly | References | SupportedOperators | Description |
TaskId [KEY] | String | True |
ID of a task. | ||
ProjectId | String | True |
Projects.ProjectId |
ID of the project. | |
CurrencyId | String | True |
Currencies.CurrencyId |
Currency ID of the customer's currency. | |
TaskName | String | False |
Name of the task. | ||
Description | String | False |
Description of the task. | ||
ProjectName | String | True |
Name of the project. | ||
CustomerId | String | True |
Contacts.ContactId |
ID of the customer or vendor. | |
CustomerName | String | True |
Name of the customer or vendor. | ||
BilledHours | String | True |
Total number of billed hours for a task. | ||
BudgetHours | Integer | False |
A project comprises of a single or multiple tasks that need to be completed. | ||
LogTime | String | True |
Time logs for the task. | ||
UnBilledHours | String | True |
Total number of hours which was un-billed. | ||
Rate | Decimal | False |
Rate for task. | ||
Status | String | True |
Status of the task. | ||
IsBillable | Boolean | True |
Check if tasks is billable or not. |
To list, add, update and delete simple and compound taxes. Also, get the details of a simple or compound tax.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
SELECT * FROM Taxes WHERE TaxId = '1894553000000077244'
Insert can be executed by specifying the TaxName, TaxPercentage columns. The columns that are not read-only can be inserted optionally. Following is an example of how to insert into this table.
INSERT INTO Taxes (TaxName,TAXPERCENTAGE) VALUES ('tax1','3')
Update can be executed by specifying the TaxId in the WHERE Clause. The columns that are not read-only can be Updated.
For example:
UPDATE Taxes SET TaxName = 'TaxUpdated',TaxPercentage='5' WHERE TaxId = '3350895000000089005'
Delete can be executed by specifying the TaxId in the WHERE Clause
For example:
DELETE FROM Taxes WHERE TaxId = '3350895000000089001'
Name | Type | ReadOnly | References | SupportedOperators | Description |
TaxId [KEY] | String | True |
Id of tax. | ||
TaxName | String | False |
Name of the tax. | ||
TaxPercentage | Integer | False |
Percentage applied for tax. | ||
TaxType | String | False |
Type of tax. | ||
TaxSpecificType | String | False |
Type of tax. | ||
TaxAuthorityId | String | False |
ID of a tax authority. | ||
TaxAuthorityName | String | False |
Name of the tax authority. | ||
TaxSpecification | String | True |
Working of tax when specifying special tax options and tax methods for earnings codes. | ||
TdsPayableAccountId | String | True |
BankAccounts.AccountId |
Account ID of TDS payable. | |
Country | String | True |
Name of the country for taxes. | ||
CountryCode | String | False |
Country code for taxes. | ||
IsDefaultTax | Boolean | True |
Check if the tax is default. | ||
IsValueAdded | Boolean | False |
Check if Tax is Value Added. | ||
IsEditable | Boolean | False |
Check if the tax is editable. | ||
TaxType | String | True |
Type of tax. | ||
PurchaseTaxExpenseAccountId | String | False |
Account ID in which Purchase Tax will be Computed. | ||
UpdateRecurringInvoice | Boolean | False |
Check if recurring invoice should be updated. | ||
UpdateRecurringExpense | Boolean | False |
Check if Draft Invoices should be updated. | ||
UpdateDraftInvoice | Boolean | False |
Check if Draft Invoices should be updated. | ||
UpdateRecurringBills | Boolean | False |
Check if Subscriptions should be updated. | ||
UpdateDraftSo | Boolean | False |
Check if Subscriptions should be updated. | ||
UpdateSubscription | Boolean | False |
Check if Subscriptions should be updated. | ||
UpdateProject | Boolean | False |
Check if Projects should be updated. |
To list, add, update and delete time entries.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud. By default, response will be showing the time entries of current month only.
SELECT * FROM TimeEntries WHERE TimeEntryId = '1894553000000085710' AND UserId = '1894553000000068001' SELECT * FROM TimeEntries WHERE TimeEntryFilter = 'Date.All'
Name | Type | ReadOnly | References | SupportedOperators | Description |
TimeEntryId [KEY] | String | True |
Id of time entry. | ||
TimerDurationInMinutes | Integer | True |
Timer duration in minutes. | ||
TimerDurationInSeconds | Integer | True |
Timer duration in seconds. | ||
TimerStartedAt | String | True |
Time when the timer started. | ||
BeginTime | String | False |
Time the user started working on this task. | ||
BilledStatus | String | True |
Status which are billed. | ||
CostRate | Decimal | False |
Hourly cost rate. | ||
CanBeInvoiced | Boolean | True |
Check if the entry can be invoiced. | ||
CanContinueTimer | Boolean | True |
Check if the entry can continue the timer. | ||
CanCreateClientApproval | Boolean | True |
Check if the entry can create client approval. | ||
CanCreateUserApproval | Boolean | True |
Check if the entry can create user approval. | ||
CreatedTime | Datetime | True |
Time at which the time entry was created. | ||
CustomerId | String | True |
Contacts.ContactId |
ID of the customer or vendor. | |
CustomerName | String | True |
Name of the customer or vendor. | ||
EndTime | String | False |
Time the user stopped working on this task. | ||
InvoiceId | String | True |
Invoices.InvoiceId |
ID of an invoice. | |
InvoiceNumber | String | True |
Number of an invoice. | ||
IsBillable | Boolean | False |
Check if time entries is billable. | ||
IsClientApprovalNeeded | Boolean | True |
Check if the client approval is needed in time entries. | ||
IsCurrentUser | Boolean | True |
Check if it is a current user of time entries. | ||
IsPaused | Boolean | True |
Check if time entries is paused. | ||
LogDate | Date | False |
Log of date. | ||
LogTime | String | False |
Log of time. | ||
Notes | String | False |
Notes for this time entry. | ||
ProjectHeadId | String | True |
ID of project head. | ||
ProjectHeadName | String | True |
Name of project head. | ||
ProjectId | String | False |
Projects.ProjectId |
Id of a project. | |
ProjectName | String | True |
Name of the project. | ||
StartTimer | String | False |
Start Timer. | ||
TaskId | String | False |
Tasks.TaskId |
ID of task. | |
TaskName | String | True |
Name of the task. | ||
UserId | String | False |
Users.UserId |
Id of a user. | |
UserName | String | True |
Name of user for time entries. |
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 |
FromDate | Date |
Date from which the time entries logged to be fetched. |
ToDate | Date |
Date up to which the time entries logged to be fetched. |
TimeEntryFilter | String |
Filter time entries by date and status. The allowed values are Date.All, Date.Today, Date.ThisWeek, Date.ThisMonth, Date.ThisQuarter, Date.ThisYear, Date.PreviousDay, Date.PreviousWeek, Date.PreviousMonth, Date.PreviousQuarter, Date.PreviousYear, Date.CustomDate, Status.Unbilled, Status.Invoiced. |
To list, add, update and delete users in the organization. Also, get the details of a user.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
SELECT * FROM Users WHERE Status = 'All' SELECT * FROM Users ORDER BY UserRole DESC
Insert can be executed by specifying the Name,Email column. The columns that are not read-only can be inserted optionally. Following is an example of how to insert into this table.
INSERT INTO Users (Name,Email,UserRole) VALUES ('user1','[email protected]','staff')
Update can be executed by specifying the UserId in the WHERE Clause. The columns that are not read-only can be Updated.
For example:
UPDATE Users SET Name = 'User Name Change',Email='[email protected]',UserRole='staff' WHERE UserId = '3350895000000089005'
Delete can be executed by specifying the UserId in the WHERE Clause
For example:
DELETE FROM Users WHERE UserId = '3350895000000089001'
Name | Type | ReadOnly | References | SupportedOperators | Description |
UserId [KEY] | String | True |
Id of a user. | ||
UserRole | String | False |
Role of a user. | ||
UserType | String | True |
Type of a user. | ||
CreatedTime | Datetime | True |
Time at which the user was created. | ||
String | False |
Email ID of a user. | |||
IsAssociatedForApproval | Boolean | True |
Check if the user is associated for the approval. | ||
IsClaimant | Boolean | True |
Check if the user is claimant. | ||
IsCustomerSegmented | Boolean | True |
Check if the user is customer segmented. | ||
IsEmployee | Boolean | True |
Check if the user is an employee. | ||
Name | String | False |
Name of the user. | ||
PhotoUrl | String | True |
Photo URL of the user. | ||
RoleId | String | False |
Role ID of a user. | ||
CostRate | Double | False |
Hourly cost rate. | ||
Status | String | True |
Status of the user. The allowed values are All, Active, Inactive, Invited, Deleted. |
To list, add, update and delete details of a vendor credit.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
Note: VendorCreditId is required in order to query VendorCreditDetails.
SELECT * FROM VendorCreditDetails WHERE VendorCreditId = '1894545000000083308'
Insert can be executed by specifying the ContactName column. The columns that are not read-only can be inserted optionally. Following is an example of how to insert into this table.
Insert INTO VendorCreditLineItems#TEMP(Name,itemid,rate,quantity) values('Cloth-Jeans3','3285934000000104097','1700','1')
INSERT INTO VendorCreditDetails (VendorId,lineitems,VendorCreditNumber) VALUES ('3285934000000104023',VendorCreditLineItems#Temp,'9')
Update can be executed by specifying the VendorCreditId in the WHERE Clause. The columns that are not read-only can be Updated.
For example:
Insert INTO VendorCreditLineItems#TEMP(Name,itemid,rate,quantity) values('Cloth-Jeans3','3285934000000104097','1700','1')
Update VendorCreditDetails set CustomerId='3285934000000104002',LineItems='VendorCreditLineItems#TEMP' WHERE VendorCreditID='3285934000000259151'
Delete can be executed by specifying the VendorCreditId in the WHERE Clause
For example:
DELETE FROM VendorCreditDetails WHERE VendorCreditId = '3350895000000089001'
Name | Type | ReadOnly | References | SupportedOperators | Description |
VendorCreditId [KEY] | String | True |
VendorCredits.VendorCreditId |
Id of a vendor credit. | |
VendorCreditNumber | String | False |
Number of vendor credit. | ||
VendorId | String | False |
ID of the vendor the vendor credit has been made. | ||
VendorName | String | True |
Name of the vendor the vendor credit has been made. | ||
Adjustment | Decimal | True |
Adjustments made to the vendor credit. | ||
AdjustmentDescription | String | True |
Description of adjustments made to the vendor credit. | ||
ApproverId | String | True |
Users.UserId |
ID of a approver. | |
Balance | Decimal | True |
Total balance of vendor credit. | ||
BillId | String | False |
Bills.BillId |
ID of a Bill. | |
BillNumber | String | True |
Number of a Bill. | ||
ColorCode | String | True |
Color code of vendor credit. | ||
ContactCategory | String | True |
Category of a contact. | ||
CreatedTime | Datetime | True |
Time at which the vendor credit was created. | ||
CurrencyCode | String | True |
Currency code of the customer's currency. | ||
CustomFields | String | True |
Custom Fields defined for Journal | ||
CurrencyId | String | True |
Currencies.CurrencyId |
Currency ID of the customer's currency. | |
CurrentSubStatus | String | True |
Current sub status of a vendor credit. | ||
CurrentSubStatusId | String | True |
Current sub status ID of a vendor credit. | ||
Documents | String | False |
List of files to be attached to a particular transaction. | ||
Date | Date | False |
Vendor Credit date. | ||
DestinationOfSupply | String | False |
Place where the goods/services are supplied to. | ||
Discount | String | True |
Discount amount applied to vendor credit. | ||
DiscountAccountId | String | True |
BankAccounts.AccountId |
Account ID of the discount. | |
DiscountAmount | Decimal | True |
Amount of the discount. | ||
DiscountAppliedOnAmount | Decimal | True |
Amount applied on discount. | ||
DiscountSetting | String | True |
Settings of discount. | ||
ExchangeRate | Decimal | False |
Exchange rate applied for vendor credits. | ||
FiledInVatReturnId | String | True |
VAT return ID of bill which was filed. | ||
FiledInVatReturnName | String | True |
VAT return name of bill which was filed. | ||
FiledInVatReturnType | String | True |
VAT return type of bill which was filed. | ||
GstNo | String | False |
GST number. | ||
GstReturnDetailsReturnPeriod | String | True |
Return period of GST return details. | ||
GstReturnDetailsStatus | String | True |
Status of GST return details. | ||
GstTreatment | String | False |
Choose whether the vendor credit is GST registered/unregistered/consumer/overseas. | ||
HasNextVendorcredit | Boolean | True |
Check if there is nect vendor credit. | ||
IsDiscountBeforeTax | Boolean | True |
Check if discount is applicable before tax. | ||
IsInclusiveTax | Boolean | False |
Check if tax is inclusive. | ||
IsUpdateCustomer | Boolean | False |
Check if customer should be updated. | ||
IsPreGst | Boolean | True |
Check if pre GST is applied. | ||
IsReverseChargeApplied | Boolean | True |
Check if the reverse charge is applied. | ||
LastModifiedTime | Datetime | True |
The time of last modification of the vendor credits. | ||
Notes | String | False |
Notes of vendor credit. | ||
Orientation | String | True |
Orientation of vendor credit. | ||
PageHeight | String | True |
Height of a page. | ||
PageWidth | String | True |
Width of a page. | ||
PricebookId | String | False |
ID of the pricebook. | ||
PricePrecision | Integer | True |
The precision for the price. | ||
ReasonForDebitNote | String | True |
Specified reason for debit note. | ||
ReferenceNumber | String | False |
Reference number of vendor credit. | ||
SourceOfSupply | String | False |
Source of supply. | ||
Status | String | True |
Status of the vendor credit. | ||
SubTotal | Decimal | True |
Sub total of vendor credits. | ||
SubTotalInclusiveOfTax | Decimal | True |
Amount if the subtotal is inclusive of tax. | ||
SubmittedBy | String | True |
Detail of the user who has submitted the vendor credit. | ||
SubmittedDate | Date | True |
Date when vendor credit was submitted. | ||
SubmitterId | String | True |
Users.UserId |
ID of vendor credit submitter. | |
TaxTreatment | String | False |
VAT treatment for the Vendor Credit. | ||
TemplateId | String | True |
ID of a template. | ||
TemplateName | String | True |
Name of a template. | ||
TemplateType | String | True |
Type of a template. | ||
Total | Decimal | True |
Total of vendor credits. | ||
TotalCreditsUsed | Decimal | True |
Total credits used for this vendor credit. | ||
TotalRefundedAmount | Decimal | True |
Total amount refunded for a vendor credit. | ||
PlaceOfSupply | String | False |
The place of supply is where a transaction is considered to have occurred for VAT purposes. | ||
VatTreatment | String | False |
VAT treatment for the vendor credit. | ||
LineItems | String | False |
Line items of an estimate. |
To list, add, update and delete details of a Vendor Payment.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
Note: PaymentId is required in order to query VendorPaymentDetails.
SELECT * FROM VendorPaymentDetails WHERE PaymentId = '1894553000000085277'
Insert can be executed by specifying the VendorId,Amount column. The columns that are not read-only can be inserted optionally. Following is an example of how to insert into this table.
INSERT INTO VendorPaymentDetails (VendorId,Amount) VALUES ('3285934000000104023','500')
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 VendorPaymentDetails VendorId='3285934000000104023',Amount='1000' WHERE PaymentId = '3350895000000089005'
Delete can be executed by specifying the PaymentId in the WHERE Clause
For example:
DELETE FROM VendorPaymentDetails WHERE PaymentId = '3350895000000089001'
Name | Type | ReadOnly | References | SupportedOperators | Description |
PaymentId [KEY] | String | True |
VendorPayments.PaymentId |
Id of a payment. | |
VendorId | String | False |
VendorPayments.VendorId |
ID of the vendor the vendor payment has been made. | |
VendorName | String | True |
Name of the vendor the vendor payment has been made. | ||
VendorpaymentRefunds | String | True |
Refunds of a vendor payment. | ||
AchPaymentStatus | String | True |
Status of ACH Payment. | ||
Amount | Decimal | False |
Amount of the vendor payments. | ||
Balance | Decimal | True |
Total amount of a vendor payment. | ||
Bills | String | False |
Individual bill payment details as array. | ||
BillingAddress | String | True |
Billing address of a vendor payment. | ||
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. | ||
CheckDetailsAmountInWords | String | False |
Checking details with amount in words. | ||
CheckDetailsCheckId | String | False |
Id of check. | ||
CheckDetailsCheckNumber | String | False |
Number if check. | ||
CheckDetailsCheckStatus | String | False |
Status of check. | ||
CheckDetailsMemo | String | False |
Memo of check details. | ||
CheckDetailsTemplateId | String | False |
Template Id of a vendor payment in check. | ||
CreatedTime | Datetime | True |
Time at which the vendor payment was created. | ||
CurrencyId | String | True |
Currencies.CurrencyId |
Currency ID of the customer's currency. | |
CurrencySymbol | String | True |
Currency symbol of the customer's currency. | ||
CustomFields | String | False |
Custom Fields. | ||
Date | Date | False |
Date of a vendor payment. | ||
Description | String | False |
Description of the vendor payment. | ||
ExchangeRate | Decimal | False |
Exchange rate of a vendor payment. | ||
IsAchPayment | Boolean | True |
Check if the payment if done with ACH payment. | ||
IsPaidViaPrintCheck | Boolean | False |
Check if vendor payment paid via print check. | ||
IsPreGst | Boolean | True |
Check if vendor payment includes pre GST. | ||
IsTdsAmountInPercent | Boolean | True |
Check if the TDS amount is in percent. | ||
LastModifiedTime | Datetime | True |
The time of last modification of the vendor payment. | ||
OffsetAccountId | String | True |
BankAccounts.AccountId |
Id of an offset account. | |
OffsetAccountName | String | True |
Name of an offset account. | ||
PaidThroughAccountId | String | False |
BankAccounts.AccountId |
Account Id from which vendor payment has been made. | |
PaidThroughAccountName | String | True |
Account name from which vendor payment has been made. | ||
PaidThroughAccountType | String | True |
Account type from which vendor payment has been made. | ||
PaymentMode | String | False |
Mode through which payment is made. | ||
PaymentNumber | Integer | True |
Number through which payment is made. | ||
ProductDescription | String | True |
Description of the product. | ||
PurposeCode | String | True |
Purpose code of vendor payment. | ||
ReferenceNumber | String | False |
Reference number of a vendor payment. | ||
TaxAccountId | String | True |
BankAccounts.AccountId |
ID of a tax account. | |
TaxAccountName | String | True |
Name of a tax account. | ||
TaxAmountWithheld | Decimal | True |
Amount withheld for tax. | ||
TdsTaxId | String | True |
ID of a TDS tax. |
Views are composed of columns and pseudo columns. Views are similar to tables in the way that data is represented; however, views do not support updates. Entities that are represented as views are typically read-only entities. Often, a stored procedure is available to update the data if such functionality is applicable to the data source.
Queries can be executed against a view as if it were a normal table, and the data that comes back is similar in that regard.
Dynamic views, such as queries exposed as views, and views for looking up specific combinations of project_team work items are supported.
Name | Description |
BalanceSheetsReport | This report summarizes your company's assets, liabilities and equity at a specific point in time |
BankMatchingTransactions | Retrieves the list of transactions which includes invoices/bills/credit-notes. |
BankRuleCriterions | Get criterions of a specific bank rule. |
BankTransactionLineItems | Get details of bank transaction line items. |
BaseCurrencyAdjustmentAccounts | Retrieves lists of base currency adjustment accounts. |
BillLineItems | Get the details of a line items of bills. |
BillPayments | Get the list of payments made for a bill. |
Bills | Retrieves list of bills. |
ChartOfAccountInlineTransactions | Retrieves the list of inline transactions. |
ChartOfAccountTransactions | Retrieves list of all involved transactions for the given account. |
ContactAddresses | Get addresses of a contact including its Shipping Address, Billing Address. |
Contacts | Retrieves list of all contacts. |
CreditNoteInvoices | Retrieves details of invoices from an existing Credit Note. |
CreditNoteLineItems | Retrieves details of line items from existing Credit Notes. |
CreditNotes | Retrieves list of all the Credit Notes. |
CurrencyExchangeRates | Retrieves list of exchange rates configured for the currency. |
CustomerPayments | Retrieves list of all the payments made by your customer. |
Employees | Retrieves list of employees. Also, get the details of an employee. |
EstimateApprovers | Get the details of approvers for estimates. |
EstimateLineItems | Get the details of line items for estimates. |
Estimates | Retrieves list of all estimates. |
Expenses | Retrieves list of all the Expenses. |
InvoiceAppliedCredits | Retrieves list of credits applied for an invoice. |
InvoiceLineItems | Get the details of line items from invoices. |
InvoicePayments | Get the list of payments made for an invoice. |
Invoices | Retrieves list of all invoices. |
Items | Retrieves list of all active items. |
JournalLineItems | Retrieves list of line items of a journal. |
OpeningBalanceAccounts | Retrieves list of accounts of opening balance. |
OpeningBalanceTransactionSummaries | Get transaction summaries of opening balance. |
Organizations | Retrieves list of organizations. |
ProfitsAndLossesReport | This report summarizes your company's assets, liabilities and equity at a specific point in time |
ProjectInvoices | Retrieves list of invoices created for a project. |
ProjectUsers | Retrieves list of users associated with a project. Also, get details of a user in project. |
PurchaseOrderLineItems | Get the details of line items of purchase orders. |
PurchaseOrders | Retrieves list of all purchase orders. |
RecurringExpenses | Retrieves list of all the Expenses. |
RecurringInvoiceLineItems | Get the details of line items of a recurring invoice. |
RecurringInvoices | Retrieves list of all recurring invoices. |
RecurringSubExpense | Retrieves list of child expenses created from recurring expense. |
ReportsAccountTransactionsDetails | Retrieves the list of inline transactions. |
RetainerInvoiceLineItems | Retrieves detail of line items of retainer invoices. |
RetainerInvoicePayments | Get the list of payments made for a retainer invoices. |
RetainerInvoices | Retrieves list of all retainer invoices. |
SalesOrderLineItems | Retrieves list of line items of a sales order. |
SalesOrders | Retrieves list of all sales orders. |
TaxSummaryReport | This report summarizes your company's assets, liabilities and equity at a specific point in time |
TrialBalanceReport | This report summarizes your company's assets, liabilities and equity at a specific point in time |
VendorCreditBills | Retrieves list of bills to which the vendor credit is applied. |
VendorCreditLineItems | Retrieves list of line items from vendor credits. |
VendorCredits | Retrieves list of vendor credits. |
VendorPayments | Retrieves list of all the payments made to your vendor. |
This report summarizes your company's assets, liabilities and equity at a specific point in time
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
SELECT * FROM BalanceSheetsReport SELECT * FROM BalanceSheetsReport WHERE TransactionDate = 'Today' SELECT * FROM BalanceSheetsReport WHERE ToDate = '2022-10-31' SELECT * FROM BalanceSheetsReport WHERE FromDate = '2022-10-10' and ToDate = '2022-10-31' SELECT * FROM BalanceSheetsReport WHERE CashBased = True
Name | Type | References | SupportedOperators | Description |
BalanceTypeName | String | Balance Type Name | ||
SubBalanceTypeName | String | SubBalance Type Name | ||
AccountTransactionTypeName | String | Account Transaction Type Name | ||
SubAccountTransactionTypeName | String | Sub Account Transaction Type Name | ||
AccountTransactionTotal | Decimal | Account Transaction Total | ||
SubAccountTransactionTotal | Decimal | Sub Account Transaction Total | ||
SubBalanceTypeTotal | Decimal | SubBalance Type Total | ||
BalanceTypeTotal | Decimal | Balance Type Total |
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 | |
CashBased | Boolean | Balance Type Total | |
TransactionDate | String | Filter transaction by any transaction date.
The allowed values are Today, ThisWeek, ThisMonth, ThisQuarter, ThisYear, PreviousDay, PreviousWeek, PreviousMonth, PreviousQuarter, PreviousYear and CustomDate. | |
ToDate | Date | To Date | |
FromDate | Date | From Date |
Retrieves the list of transactions which includes invoices/bills/credit-notes.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud. You can even provide criteria to search for matching uncategorised transactions.
SELECT * FROM BankMatchingTransactions WHERE TransactionId = '1894578000000087001
Name | Type | References | SupportedOperators | Description |
TransactionId [KEY] | String |
BankTransactions.TransactionId | ID of the Transaction. | |
TransactionNumber | String | Numnber of transaction. | ||
TransactionType | String | Transaction Type of the transaction. | ||
Amount | Integer | Amount of the bank matching transactions. | ||
ContactName | String | Display Name of the contact. Max-length [200] | ||
Date | Date | =,<,> | Date when transaction was made. | |
DebitOrCredit | String | Indicates if transaction is Credit or Debit. | ||
IsBestMatch | Boolean | Check if the transaction is a best match. | ||
IsPaidViaPrintCheck | Boolean | Check if it is paid via print check. | ||
ReferenceNumber | String | Reference Number of the transaction. |
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 | |
Contact | String | Contact person name, involved in the transaction. | |
ShowAllTransactions | Boolean | Check if all transactions must be shown. |
Get criterions of a specific bank rule.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
SELECT * FROM BankRuleCriterions WHERE RuleAccountId = '1894553000000085382
Name | Type | References | SupportedOperators | Description |
CriteriaId [KEY] | String | Id of a criteria. | ||
RuleAccountId | String |
BankAccounts.AccountId | ID of the Bank Account. | |
Comparator | String | Operator for comparing criteria. | ||
Field | String | Field of a criteria. | ||
Value | String | Value of a criteria. |
Get details of bank transaction line items.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
Note: TransactionId is required in order to query BankTransactionLineItems.
SELECT * FROM BankTransactionLineItems WHERE TransactionId = '1894553000000098001'
Name | Type | References | SupportedOperators | Description |
TransactionId [KEY] | String |
BankTransactions.TransactionId | ID of the Transaction. | |
BcyTotal | Decimal | Total Base Currency. | ||
CustomerId | String |
Contacts.ContactId | ID of the customer or vendor. | |
CustomerName | String | Name of the customer or vendor. | ||
FromAccountId | String |
BankAccounts.AccountId | Transaction from account ID. | |
FromAccountName | String | Transaction from account name. | ||
PaymentMode | String | Mode through which payment is made. | ||
SubTotal | Decimal | Sub total of bank transaction line items. | ||
Tags | String | Details of tags related to bank transactions. | ||
Total | Decimal | Total of bank transaction line items. | ||
VendorId | String | ID of the vendor the bank transaction line items has been made. | ||
VendorName | String | Name of the vendor the bank transaction line items has been made. |
Retrieves lists of base currency adjustment accounts.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
SELECT * FROM BaseCurrencyAdjustmentAccounts WHERE BaseCurrencyAdjustmentId = '1894553000000000065'
Name | Type | References | SupportedOperators | Description |
BaseCurrencyAdjustmentId [KEY] | String | Id of base currency adjustment account. | ||
AccountId | String |
BankAccounts.AccountId | Id of the Bank Account. | |
AccountName | String | Name of the account. | ||
BcyBalance | Decimal | Balance of Base Currency. | ||
FcyBalance | Decimal | Balance of Foreign Currency. | ||
AdjustedBalance | Decimal | Balance adjusted for base currency. | ||
GainOrLoss | Decimal | Check the amount if gain or loss. | ||
GlSpecificType | String | Specific type of gain or loss. |
Get the details of a line items of bills.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
Note: BillId is required in order to query BillLineItems.
SELECT * FROM BillLineItems WHERE BillId = '1894253000000085259'
Name | Type | References | SupportedOperators | Description |
LineItemId [KEY] | String | Id of line item. | ||
BillId | String |
Bills.BillId | Id of a Bill. | |
AccountId | String |
BankAccounts.AccountId | Id of the Bank Account. | |
AccountName | String | Name of the account. | ||
BcyRate | Decimal | Rate of Base Currency. | ||
CustomFields | String | Custom fields | ||
CustomerId | String |
Contacts.ContactId | ID of the customer or vendor. | |
CustomerName | String | Name of the customer or vendor. | ||
Description | String | Description of the bill line item. | ||
Discount | Double | Discount to be applied on the bill line item. | ||
GstTreatmentCode | String | Treatment code of GST. | ||
HasProductTypeMismatch | Boolean | Check if the product type is mismatch. | ||
HsnOrSac | String | HSN Code. | ||
ImageDocumentId | String | ID of the image document. | ||
InvoiceId | String |
Invoices.InvoiceId | ID of an invoice. | |
InvoiceNumber | String | Number of an invoice. | ||
IsBillable | Boolean | Check if the bill line items is billable. | ||
ItcEligibility | String | Eligibility if bill for Input Tax Credit. | ||
ItemId | String |
Items.ItemId | ID of an item. | |
ItemOrder | Integer | Order of an item. | ||
ItemTotal | Decimal | Total items. | ||
ItemType | String | Type of item. | ||
Name | String | Name of the bill line item. | ||
PricebookId | String | ID of pricebook. | ||
ProductType | String | Type of product. | ||
ProjectId | String |
Projects.ProjectId | ID of project. | |
ProjectName | String | Name of the project. | ||
PurchaseorderItemId | String | Item ID for purchase order. | ||
Quantity | Decimal | Quantity of line item. | ||
Rate | Decimal | Rate of the line item. | ||
ReverseChargeTaxId | String | ID of the reverse charge tax. | ||
TaxExemptionCode | String | Code for tax exemption. | ||
TaxExemptionId | String |
BankRules.TaxExemptionId | ID of tax exemption. | |
TaxId | String |
Taxes.TaxId | ID of tax. | |
TaxName | String | Name of tax. | ||
TaxPercentage | Integer | Percentage applied for tax. | ||
TaxType | String | Type of tax. | ||
Unit | String | Number of quantity. |
Get the list of payments made for a bill.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
SELECT * FROM BillPayments WHERE BillId = '1894253000000085259'
Name | Type | References | SupportedOperators | Description |
BillPaymentId [KEY] | String | ID of bill payment. | ||
BillId | String |
Bills.BillId | ID of a Bill. | |
VendorId | String | ID of the vendor the bill payments has been made. | ||
VendorName | String | Name of the vendor the bill payments has been made. | ||
PaymentId | String | Id of a payment. | ||
Amount | Decimal | Amount of the bill payments. | ||
Date | Date | Date of a bill payment. | ||
Description | String | Description of the bill payment. | ||
ExchangeRate | Decimal | Exchange rate of bill payments. | ||
IsSingleBillPayment | Boolean | Check if it is single bill payment. | ||
PaidThrough | String | Amount paid via check/cash/credit. | ||
PaymentMode | String | Mode through which payment is made. | ||
ReferenceNumber | String | Reference number of bill payment. |
Retrieves list of bills.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
SELECT * FROM Bills WHERE Total < 100 AND Total <= 98 SELECT * FROM Bills WHERE Date < '2018-07-03' SELECT * FROM Bills WHERE CONTAINS(BillNumber,'Bi')
Name | Type | References | SupportedOperators | Description |
BillId [KEY] | String | Id of a bill. | ||
BillNumber | String | Number of bill. | ||
Balance | Decimal | Amount of bill. | ||
CreatedTime | Datetime | Time at which the bill was created. | ||
Date | Date | =,<,> | Date of a bill. | |
DueDate | Date | Delivery date to pay the bill. | ||
DueDays | String | Due days to pay the bill. | ||
HasAttachment | Boolean | Check if the bill has attachment. | ||
LastModifiedTime | Datetime | Last Modified Time of bill. | ||
ReferenceNumber | String | Reference number of a bill. | ||
Status | String | Status of a bill.
The allowed values are paid, open, overdue, void, partially_paid. | ||
TdsTotal | Decimal | Total amount of TDS applied. | ||
Total | Decimal | =,<,<=,>,>= | Total of bills. Search by bill total. | |
VendorId | String | ID of the vendor the bill has been made. Search bills by Vendor ID. | ||
VendorName | String | Name of the vendor the bill has been made. Search bills by vendor name. |
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 | |
PurchaseOrderId | String | Id of a Purchase Order. | |
RecurringBillId | String | Id of a Recurring Bill. | |
BillFilter | String | Filter bills by any status.
The allowed values are Status.All, Status.PartiallyPaid, Status.Paid, Status.Overdue, Status.Void, Status.Open. | |
ItemId | String | Id of an Item. | |
ItemDescription | String | Description of a bill. |
Retrieves the list of inline transactions.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
SELECT * FROM ChartOfAccountInlineTransactions WHERE ChartAccountId = '1894553000000003003'
Name | Type | References | SupportedOperators | Description |
ChartAccountId | String |
BankAccounts.AccountId | ID of the Bank Account. | |
TransactionId [KEY] | String |
BankTransactions.TransactionId | ID of the Transaction. | |
Credit | Decimal | Credit of inline transaction. | ||
CustomerId | String |
Contacts.ContactId | ID of the customer or vendor. | |
CustomerName | String | Name of the customer or vendor. | ||
Date | Date | Date of an inline account transaction. | ||
Debit | String | Debit of inline transaction. | ||
EntityType | String | Entity type of inline transactions. | ||
FcyCredit | Decimal | Foreign Currency credits. | ||
FcyDebit | String | Foreign Currency debits. | ||
ReferenceNumber | String | Reference number of inline transactions. |
Retrieves list of all involved transactions for the given account.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud. You can even provide criteria to search for matching uncategorised transactions.
SELECT * FROM ChartOfAccountTransactions WHERE ChartAccountId = '1894553000000003001' AND TransactionType = 'opening_balance'
Name | Type | References | SupportedOperators | Description |
ChartAccountId | String |
ChartOfAccounts.ChartAccountId | Chart of Account Id. | |
CategorizedTransactionId [KEY] | String | ID of a categorized transaction in chart of account. | ||
CreditAmount | Decimal | Total amount credited. | ||
DebitAmount | Decimal | Total amount debited. | ||
CurrencyCode | String | Currency code of the customer's currency. | ||
CurrencyId | String |
Currencies.CurrencyId | Currency ID of the customer's currency. | |
CustomerId | String |
Contacts.ContactId | ID of the customer or vendor. | |
DebitOrCredit | String | Indicates if transaction is Credit or Debit. | ||
Description | String | Description of the chart of account transactions. | ||
EntryNumber | String | Entry number of account transaction. | ||
Payee | String | Information about the payee. | ||
TransactionDate | Date | Date of the transaction. | ||
TransactionId | String |
BankTransactions.TransactionId | ID of the Transaction. | |
TransactionType | String | Type of transactions.
The allowed values are invoice, customer_payment, bills, vendor_payment, credit_notes, creditnote_refund, expense, card_payment, purchase_or_charges, journal, deposit, refund, transfer_fund, base_currency_adjustment, opening_balance, sales_without_invoices, expense_refund, tax_refund, receipt_from_initial_debtors, owner_contribution, interest_income, other_income, owner_drawings, payment_to_initial_creditors. |
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 | |
Date | Date | Date range filter of Chart of Account. | |
Amount | Decimal | Amount of the transaction. |
Get addresses of a contact including its Shipping Address, Billing Address.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
SELECT * FROM ContactAddresses WHERE ContactId = '1894952000000071009'
Name | Type | References | SupportedOperators | Description |
AddressId [KEY] | String | Id of an address. | ||
ContactId | String |
Contacts.ContactId | ID of a contact. | |
Attention | String | Name of a person in billing address. | ||
Address | String | Address of a contact. | ||
Street2 | String | Street two of a billing address. | ||
City | String | City of a billing address. | ||
State | String | State of a billing address. | ||
Zip | String | ZIP code of a billing address. | ||
Country | String | Country of a billing address. | ||
Phone | String | Phone number of a billing address. | ||
Fax | String | Fax of a billing address. |
Retrieves list of all contacts.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
SELECT * FROM Contacts WHERE CONTAINS(LastName,'D') SELECT * FROM Contacts WHERE FirstName = 'John' AND Status = 'Active' SELECT * FROM Contacts LIMIT 5
Name | Type | References | SupportedOperators | Description |
ContactId [KEY] | String | Id of a Contact. | ||
ContactName | String | Name of a Contact. | ||
CustomerName | String | Name of the customer. | ||
VendorName | String | Name of the Vendor. | ||
CompanyName | String | Name of a Company. | ||
Website | String | Website of this contact. | ||
LanguageCode | String | Language of a contact. | ||
ContactType | String | Contact type of the contact. | ||
Status | String | Status of the contact.
The allowed values are All, Active, Inactive, Duplicate. | ||
CustomerSubType | String | Sub type of a customer. | ||
Source | String | Source of the contact. | ||
CurrencyId | String |
Currencies.CurrencyId | Currency ID of the customer's currency. | |
String | Facebook profile account. | |||
CurrencyCode | String | Currency code of the customer's currency. | ||
OutstandingReceivableAmount | Decimal | Outstanding receivable amount of a contact. | ||
OutstandingReceivableAmountBcy | Decimal | Base Currency of Outstanding receivable amount of a contact. | ||
OutstandingPayableAmount | Decimal | Outstanding payable amount of a contact. | ||
OutstandingPayableAmountBcy | Decimal | Base Currency of Outstanding payable amount of a contact. | ||
UnusedCreditsPayableAmount | Decimal | Unused credits payable amount of a contact. | ||
FirstName | String | First name of the contact person. | ||
LastName | String | Last name of the contact person. | ||
String | Email address of the contact person. | |||
Phone | String | Phone number of the contact person. | ||
Mobile | String | Mobile number of the contact person. | ||
PlaceOfContact | String | Code for the place of contact. | ||
AchSupported | Boolean | Check if ACH is supported. | ||
CreatedTime | Datetime | Time at which the contact was created. | ||
GSTTreatment | String | Choose whether the contact is GST registered/unregistered/consumer/overseas. | ||
HasAttachment | Boolean | Check if contacts has attachment. | ||
LastModifiedTime | Datetime | The time of last modification of the contact. |
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 | |
Address | String | Address of a contact. |
Retrieves details of invoices from an existing Credit Note.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
Note: CreditnoteId is required in order to query CreditNoteInvoices.
SELECT * FROM CreditNoteInvoices WHERE CreditnoteId = '1895452000000083136'
Name | Type | References | SupportedOperators | Description |
CreditnoteInvoiceId [KEY] | String | ID of credit note invoice. | ||
CreditnoteId | String |
CreditNotes.CreditnoteId | ID of a credit note. | |
Credited_amount | Decimal | Amount which is credit in credit note invoice. | ||
CreditnoteNumber | String | Number of a credit note. | ||
Date | Date | Date of an credit note invoice. | ||
InvoiceId | String |
Invoices.InvoiceId | ID of an invoice. | |
InvoiceNumber | String | Number of an invoice. |
Retrieves details of line items from existing Credit Notes.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
Note: CreditnoteId is required in order to query CreditNoteLineItems.
SELECT * FROM CreditNoteLineItems WHERE CreditnoteId = '1895452000000083136'
Name | Type | References | SupportedOperators | Description |
CreditnoteId | String |
CreditNotes.CreditnoteId | ID of credit note. | |
LineItemId [KEY] | String | ID of line item. | ||
AccountId | String |
BankAccounts.AccountId | ID of the Bank Account. | |
AccountName | String | Name of the account. | ||
BcyRate | Decimal | Rate of Base Currency. | ||
CustomFields | String | Custom Fields | ||
Description | String | Description of the credit note line item. | ||
Discount | String | Discount given to specific item in credit note. | ||
DiscountAmount | Decimal | Amount of discount. | ||
GstTreatmentCode | String | Treatement codes for GST. | ||
HasProductTypeMismatch | Boolean | Check if the credit note item contains product type mismatch. | ||
HsnOrSac | String | HSN Code. | ||
ImageDocumentId | String | ID of image document. | ||
InvoiceId | String |
Invoices.InvoiceId | ID of an invoice. | |
InvoiceItemId | String | ID of an invoice item. | ||
ItemId | String |
Items.ItemId | ID of an item. | |
ItemOrder | Integer | Order of an item. | ||
ItemTotal | Decimal | Total number of an item. | ||
ItemType | String | Type of an item. | ||
Name | String | Name of the credit note. | ||
PricebookId | String | ID of price book. | ||
ProductType | String | Type of product. | ||
ProjectId | String |
Projects.ProjectId | ID of project. | |
Quantity | Double | Quantity of items in credit note. | ||
Rate | Decimal | Rate of the line item. | ||
ReverseChargeTaxId | String | ID of the reverse charge tax. | ||
Tags | String | Details of tags related to credit note line items. | ||
TaxId | String |
Taxes.TaxId | Id of tax. | |
TaxName | String | Name of the tax. | ||
TaxPercentage | Integer | Percentage applied for tax. | ||
TaxType | String | Type of tax. | ||
Unit | String | Number of quantity. |
Retrieves list of all the Credit Notes.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
SELECT * FROM CreditNotes WHERE CreditnoteNumber = 'CN-00006' AND ReferenceNumber = 'Ref-0075' AND CustomerName = 'AWS Stores' SELECT * FROM CreditNotes WHERE CreditNoteFilter = 'Status.All'
Name | Type | References | SupportedOperators | Description |
CreditnoteId [KEY] | String | ID of credit note. | ||
Balance | Decimal | Total amount of credit note left. | ||
ColorCode | String | Color code of a credit note. | ||
CreatedTime | Datetime | Time at which the credit note was created. | ||
CreditnoteNumber | String | Number of a credit note. | ||
CurrencyCode | String | Currency code of the customer's currency. | ||
CurrencyId | String |
Currencies.CurrencyId | Currency ID of the customer's currency. | |
CurrentSubStatus | String | Current sub status of a credit note. | ||
CustomerId | String |
Contacts.ContactId | ID of the customer or vendor. | |
CustomerName | String | Name of the customer or vendor. | ||
Date | Date | Date of a credit note. | ||
HasAttachment | Boolean | Check if the credit note has attachment. | ||
ReferenceNumber | String | Reference number of credit note. | ||
Status | String | Status of the credit note.
The allowed values are open, closed, void. | ||
Total | Decimal | Total of credit notes. |
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 | |
CreditNoteFilter | String | Filter credit notes by status.
The allowed values are Status.All, Status.Open, Status.Draft, Status.Closed, Status.Void. | |
LineItemId | String | Id of a line item. | |
TaxId | String | Id of a tax. | |
ItemId | String | Id of an item. | |
ItemName | String | Name of an item. | |
ItemDescription | String | Description of an item. |
Retrieves list of exchange rates configured for the currency.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
SELECT * FROM CurrencyExchangeRates WHERE CurrencyId = '1894553000000000089' SELECT * FROM CurrencyExchangeRates WHERE CurrencyId = '1894553000000000087' AND IsCurrentDate = true
Name | Type | References | SupportedOperators | Description |
CurrencyId [KEY] | String |
Currencies.CurrencyId | Currency ID of the customer's currency. | |
CurrencyCode | String | Currency code of the customer's currency. | ||
EffectiveDate | Date | Effective date for currency exchange. | ||
IsMarketClosedRates | Boolean | Check if the rates are closed to markets. | ||
Rate | Decimal | Rate of exchange for the currency with respect to base currency. |
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 | |
FromDate | Date | Returns the exchange rate details from the given date or from previous closest match in the absence of the exchange rate on the given date. | |
IsCurrentDate | Boolean | To return the exchange rate only if available for current date. |
Retrieves list of all the payments made by your customer.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
SELECT * FROM CustomerPayments WHERE Amount < 900 AND Amount > 50 AND ReferenceNumber = '1894553000000079879' SELECT * FROM CustomerPayments WHERE CustomerName = 'Harry' SELECT * FROM CustomerPayments WHERE CONTAINS(ReferenceNumber,'455')
Name | Type | References | SupportedOperators | Description |
PaymentId [KEY] | String | Id of a payment. | ||
AccountId | String |
BankAccounts.AccountId | ID of the Bank Account. | |
PaymentMode | String | Mode through which payment is made. | ||
PaymentNumber | Integer | Number through which payment is made. | ||
PaymentType | String | Type of the payment. | ||
AccountName | String | Name of the account. | ||
Amount | Decimal | =,<,<=,>,>= | Amount of the customer payments. | |
BcyAmount | Decimal | Amount applied for Base Currency. | ||
BcyRefundedAmount | Decimal | Refunded amount from Base Currency. | ||
BcyUnusedAmount | Decimal | Unused amount from Base Currency. | ||
CreatedTime | Datetime | Time at which the customer payment was created. | ||
CustomerId | String |
Contacts.ContactId | ID of the customer or vendor. | |
CustomerName | String | Name of the customer or vendor. | ||
Date | Date | Date of a customer payment. | ||
GatewayTransactionId | String | ID of gateway transaction. | ||
HasAttachment | Boolean | Check if the customer payment has an attachment. | ||
LastModifiedTime | Datetime | The time of last modification of the customer payment. | ||
ReferenceNumber | String | Reference number of a customer payment. |
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 | |
PaymentModeFilter | String | Filter customer payments by payment mode.
The allowed values are PaymentMode.All, PaymentMode.Check, PaymentMode.Cash, PaymentMode.BankTransfer, PaymentMode.Paypal, PaymentMode.CreditCard, PaymentMode.GoogleCheckout, PaymentMode.Credit, PaymentMode.Authorizenet, PaymentMode.BankRemittance, PaymentMode.Payflowpro, PaymentMode.Stripe, PaymentMode.TwoCheckout, PaymentMode.Braintree, PaymentMode.Others. | |
Notes | String | Notes of customer payments. |
Retrieves list of employees. Also, get the details of an employee.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
SELECT * FROM Employees WHERE EmployeeId = '1894553000000068001' AND Status='All'
Name | Type | References | SupportedOperators | Description |
EmployeeId [KEY] | String | Id of an employee. | ||
Name | String | Name of an employee. | ||
String | Email address of an employee. |
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 | |
Status | String | Filter employee based on the status.
The allowed values are All, Billable, Nonbillable, Reimbursed, Invoiced, Unbilled. |
Get the details of approvers for estimates.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
SELECT * FROM EstimateApprovers WHERE EstimateId = '1894553000000077244'
Name | Type | References | SupportedOperators | Description |
EstimateId | String |
Estimates.EstimateId | ID of an estimate. | |
ApproverUserId [KEY] | String |
Users.UserId | User Id of an approver. | |
Order | String | Order number. | ||
ApproverName | String | Name of the approver. | ||
String | Email Id of the approver. | |||
Has_approved | Boolean | Check if the User has approved the estimate. | ||
ApprovalStatus | String | Status of an approval. | ||
IsNextApprover | String | Check if it is a next approver. | ||
SubmittedDate | Date | Date of submission. | ||
ApprovedDate | Date | Date of approval. | ||
PhotoUrl | String | Photo Url. | ||
UserStatus | String | Status of a user. | ||
IsFinalApprover | Boolean | Check if this is a final approver. |
Get the details of line items for estimates.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
Note: EstimateId is required in order to query EstimateLineItems.
SELECT * FROM EstimateLineItems WHERE EstimateId = '1894553000000077244'
Name | Type | References | SupportedOperators | Description |
LineItemId [KEY] | String | Id of the line item. | ||
EstimateId | String |
Estimates.EstimateId | ID of an estimate. | |
BcyRate | Decimal | Rate of base currency. | ||
CustomFields | String | Custom Fields defined for the line item | ||
Description | String | Description of the estimate line items. | ||
Discount | String | Discount given to specific item in estimate. | ||
DiscountAmount | Decimal | Amount of discount given to estimate. | ||
HeaderId | String | ID of the header. | ||
HeaderName | String | Name of the header. | ||
ItemId | String |
Items.ItemId | ID of the item. | |
ItemOrder | Integer | Order of the item. | ||
ItemTotal | Decimal | Total number of items. | ||
Name | String | Name of the line item. | ||
PricebookId | String | Id of a pricebook. | ||
Quantity | Decimal | Quantity of the line item. | ||
Rate | Decimal | Rate of the line item. | ||
Tags | String | Details of tags related to estimates. | ||
TaxId | String |
Taxes.TaxId | Id of tax. | |
TaxName | String | Name of the tax. | ||
TaxPercentage | Integer | Percentage applied for tax. | ||
TaxType | String | Type of tax. | ||
Unit | String | Number of quantity. |
Retrieves list of all estimates.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
SELECT * FROM Estimates WHERE Total >= 500 AND Total <= 600 SELECT * FROM Estimates WHERE Date <= '2019-02-26' SELECT * FROM Estimates ORDER BY CustomerName SELECT * FROM Estimates WHERE CONTAINS(EstimateNumber,'006')
Name | Type | References | SupportedOperators | Description |
EstimateId [KEY] | String | ID of an estimate. | ||
AcceptedDate | Date | Accepted date of an estimate. | ||
ClientViewedTime | Datetime | Time when client viewed the estimate. | ||
ColorCode | String | Color code of estimates. | ||
CompanyName | String | Name of the company. | ||
CreatedTime | Datetime | Time at which the estimate was created. | ||
CurrencyCode | String | Currency code of the customer's currency. | ||
CurrencyId | String |
Currencies.CurrencyId | Currency ID of the customer's currency. | |
CustomerId | String |
Contacts.ContactId | ID of the customer or vendor. Search estimates by customer id.. | |
CustomerName | String | Name of the customer or vendor. Search estimates by customer name. | ||
Date | Date | =,<,> | Date of an estimate. | |
DeclinedDate | Date | Declined date of the estimate. | ||
EstimateNumber | String | Number of the estimate. | ||
ExpiryDate | Date | The date of expiration of the estimates. | ||
HasAttachment | Boolean | Check if the estimate has attachment. | ||
IsEmailed | Boolean | Check if the estimate is emailed. | ||
ReferenceNumber | String | Reference number of the estimate. | ||
Status | String | Status of the estimate.
The allowed values are draft, sent, invoiced, accepted, declined, expired. | ||
Total | Decimal | =,<,<=,>,>= | Total of estimates. Search estimates by estimate total. |
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 | |
ItemId | String | ID of the item. | |
ItemName | String | Name of an item. | |
ItemDescription | String | Description of an item. | |
EstimateFilter | String | Filter estimates by status.
The allowed values are Status.All, Status.Sent, Status.Draft, Status.Invoiced, Status.Accepted, Status.Declined, Status.Expired. |
Retrieves list of all the Expenses.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
SELECT * FROM Expenses WHERE Amount < 10000.0 AND Amount > 300.0 SELECT * FROM Expenses WHERE ExpenseFilter = 'Status.Billable'
Name | Type | References | SupportedOperators | Description |
ExpenseId [KEY] | String | ID of an expense. | ||
AccountName | String | Name of the account. | ||
BcyTotal | Decimal | Total Base Currency. | ||
BcyTotalWithoutTax | Decimal | Base Currency total amount without tax. | ||
CreatedTime | Datetime | Time at which the expense was created. | ||
CurrencyCode | String | Currency code of the customer's currency. | ||
CurrencyId | String |
Currencies.CurrencyId | Currency ID of the customer's currency. | |
CustomerId | String |
Contacts.ContactId | ID of the customer or vendor. | |
CustomerName | String | Name of the customer or vendor. | ||
Date | Date | =,<,> | Date of an expense. | |
Description | String | Description of the expense. | ||
Distance | Double | Distance Covered. | ||
EndReading | String | End reading of odometer when creating a mileage expense where mileage_type is odometer. | ||
ExchangeRate | Decimal | Exchange rate of the currency. | ||
ExpenseReceiptName | String | Name of the expense receipt. | ||
ExpenseType | String | Type of an expense. | ||
HasAttachment | Boolean | Check if the expense has attachment. | ||
IsBillable | Boolean | Check if the expense is billable. | ||
IsPersonal | Boolean | Check if the expense is personal. | ||
LastModifiedTime | Datetime | The time of last modification of the expense. | ||
MileageRate | Double | Mileage rate for a particular mileage expense. | ||
MileageType | String | Type of Mileage. | ||
MileageUnit | String | Unit of the distance travelled. | ||
PaidThroughAccountName | String | Name of account which payment was paid. | ||
ReferenceNumber | String | Reference number of a expense. | ||
ReportId | String | ID of report. | ||
ReportName | String | Name of the report. | ||
StartReading | String | Start reading of odometer when creating a mileage expense where mileage_type is odometer. | ||
Status | String | Status of the expense.
The allowed values are unbilled, invoiced, reimbursed, non-billable, billable. | ||
Total | Decimal | Total of expenses. | ||
TotalWithoutTax | Decimal | Total amount of expense calculated without tax. | ||
VendorId | String | ID of the vendor the expense has been made. | ||
VendorName | String | Name of the vendor the expense has been made. |
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 | |
Amount | Decimal | Amount of the expenses. | |
ExpenseFilter | String | Filter expenses by their status.
The allowed values are Status.All, Status.Billable, Status.Nonbillable, Status.Reimbursed, Status.InvoicedStatus.Unbilled. | |
RecurringExpenseId | String | Id of the recurring expense. |
Retrieves list of credits applied for an invoice.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
SELECT * FROM InvoiceAppliedCredits WHERE InvoiceId = '1864543000000078539'
Name | Type | References | SupportedOperators | Description |
CreditnotesInvoiceId [KEY] | String | ID of credit note invoice. | ||
InvoiceId | String |
Invoices.InvoiceId | ID of an invoice. | |
CreditnoteId | String |
CreditNotes.CreditnoteId | ID of a credit note. | |
AmountApplied | Decimal | Amount used for the credit note. | ||
CreditedDate | Date | Date when the credit was applied to the invoice. | ||
CreditnotesNumber | String | Total number of credit notes applied to the invoice. |
Get the details of line items from invoices.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
Note: InvoiceId is required in order to query InvoiceLineItems.
SELECT * FROM InvoiceLineItems WHERE InvoiceId = '1864543000000078539'
Name | Type | References | SupportedOperators | Description |
LineItemId [KEY] | String | Line Item ID of an item. | ||
InvoiceId | String |
Invoices.InvoiceId | ID of an invoice. | |
AccountId | String |
BankAccounts.AccountId | ID of the Bank Account. | |
AccountName | String | Name of the account. | ||
BcyRate | Decimal | Rate of Base Currency. | ||
BillId | String |
Bills.BillId | ID of an invoice. | |
BillItemId | String | Item ID of an invoice. | ||
CustomFields | String | Custom Fields added for the line item | ||
Description | String | Description of the invoice line item. | ||
Discount | String | Discount applied in invoice. | ||
DiscountAmount | Decimal | Discount Amount applied in invoice. | ||
ExpenseId | String |
Expenses.ExpenseId | Id of an expense. | |
ExpenseReceiptName | String | Receipt name of an expense. | ||
GstTreatmentCode | String | Code GST treatement. | ||
HasProductTypeMismatch | Boolean | Check if product type mismatch or not. | ||
HeaderId | String | Id of a header. | ||
HeaderName | String | Name of a header. | ||
HsnOrSac | String | HSN Code. | ||
ItemId | String |
Items.ItemId | Id of an item. | |
ItemOrder | Integer | Order of an item. | ||
ItemTotal | Decimal | Total number of an item. | ||
ItemType | String | Type of an item. | ||
Name | String | Name of an invoice. | ||
ProductType | String | Type of the product. | ||
ProjectId | String |
Projects.ProjectId | Id of the project. | |
PurchaseRate | Double | Purchase rate of an invoice. | ||
Quantity | Decimal | Quantity of line items. | ||
Rate | Decimal | Rate of the line item. | ||
ReverseChargeTaxId | String | ID of the reverse charge tax. | ||
SalesorderItemId | String | Item ID of sales order. | ||
TaxId | String |
Taxes.TaxId | ID of tax. | |
TaxName | String | Name of tax. | ||
TaxPercentage | Integer | Percentage of tax. | ||
TaxType | String | Type of tax applied to invoice line item. | ||
Unit | String | Number of unit in line items. |
Get the list of payments made for an invoice.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
SELECT * FROM InvoicePayments WHERE InvoiceId = '1864543000000078539'
Name | Type | References | SupportedOperators | Description |
InvoicePaymentId [KEY] | String | ID of an invoice payment. | ||
InvoiceId | String |
Invoices.InvoiceId | ID of an invoice. | |
PaymentId | String | ID of payment. | ||
PaymentNumber | Integer | Number through which payment is made. | ||
PaymentMode | String | Mode through which payment is made. | ||
Description | String | Description of the invoice payment. | ||
Date | Date | Date of an invoice payment. | ||
ReferenceNumber | String | Reference number of an invoice payment. | ||
ExchangeRate | Decimal | Exchange rate provided for this invoice. | ||
Amount | Decimal | Amount of the invoice payments. | ||
TaxAmountWithheld | Decimal | Amount withheld for tax. | ||
OnlineTransactionId | String | ID of online transaction. | ||
IsSingleInvoicePayment | Boolean | Check if the invoice is single invoice payment. |
Retrieves list of all invoices.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
SELECT * FROM Invoices WHERE InvoiceNumber = 'INV-000057' AND CustomerName = 'OldTech Industries' AND Date = '2016-03-02' SELECT * FROM Invoices WHERE DueDate > '2019-07-02' SELECT * FROM Invoices ORDER BY CreatedTime DESC
Name | Type | References | SupportedOperators | Description |
InvoiceId [KEY] | String | ID of an invoice. | ||
InvoiceNumber | String | Number of an invoice. | ||
Adjustment | Decimal | Adjustments made to the invoices. | ||
Balance | Decimal | The unpaid amount. | ||
CompanyName | String | Name of the conpany. | ||
CreatedTime | Datetime | Time at which the invoice was created. | ||
CustomerId | String |
Contacts.ContactId | ID of the customer the invoice has to be created. | |
CustomerName | String | The name of the customer. | ||
Date | Date | =,<,> | Date of an invoice. | |
DueDate | Date | =,<,> | Date of when the invoice is due. | |
DueDays | String | Number of due day left for invoice. | ||
HasAttachment | Boolean | Check if the invoice has attachment. | ||
InvoiceURL | String | URL of Invoice. | ||
LastModifiedTime | Datetime | The time of last modification of the invoice. | ||
ProjectName | String | Name of the project. | ||
ReferenceNumber | String | The reference number of the invoice. | ||
Status | String | Status of the invoice.
The allowed values are sent, draft, overdue, paid, void, unpaid, partially_paid, viewed. | ||
Total | Decimal | Total of invoices. |
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 | |
String | Email address of an invoice. | ||
RecurringInvoiceId | String | ID of the recurring invoice from which the invoice is created. | |
ItemId | String | Id of an item. | |
ItemName | String | Name of an item. | |
ItemDescription | String | Description of an item. | |
InvoiceFilter | String | Filter invoices by any status or payment expected date.
The allowed values are Status.All, Status.Sent, Status.Draft, Status.OverDue, Status.Paid, Status.Void, Status.Unpaid, Status.PartiallyPaid, Status.Viewed, Date.PaymentExpectedDate. |
Retrieves list of all active items.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
SELECT * FROM Items WHERE Rate < 200 AND Rate > 24.9 SELECT * FROM Items WHERE Description = '16' AND Name = 'Monitor'
Name | Type | References | SupportedOperators | Description |
ItemId [KEY] | String | ID of an item. | ||
ItemName | String | Name of an item. | ||
ItemType | String | Type of item. | ||
TaxId | String |
Taxes.TaxId | Id of a tax. | |
TaxName | String | Name of tax. | ||
TaxPercentage | Integer | Tax percentage of item. | ||
AccountName | String | Name of the account | ||
Description | String | Description of an item. | ||
HasAttachment | Boolean | Check if the items has attachment. | ||
ImageDocumentId | String | ID of an image document. | ||
Name | String | Name of an item. | ||
Rate | Decimal | =,<,<=,>,>= | Price of the item. Search items by rate. | |
ReorderLevel | String | Reorder level of the item. | ||
Status | String | Status of the item.
The allowed values are All, Active, Inactive. |
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 | |
AccountId | String | ID of the Bank Account to which the item has to be associated with. |
Retrieves list of line items of a journal.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
Note: JournalId is required in order to query JournalLineItems.
SELECT * FROM JournalLineItems WHERE JournalId = '1894553000000085774'
Name | Type | References | SupportedOperators | Description |
LineId [KEY] | String | ID of line in journal. | ||
JournalId | String |
Journals.JournalId | ID of journal. | |
AccountId | String |
BankAccounts.AccountId | ID of the Bank Account. | |
AccountName | String | Name of the account. | ||
Amount | Decimal | Amount of the journal line items. | ||
BcyAmount | Decimal | Amount of base currency. | ||
CustomerId | String |
Contacts.ContactId | ID of the customer or vendor. | |
CustomerName | String | Name of the customer or vendor. | ||
DebitOrCredit | String | Indicates if line item is Credit or Debit. | ||
Description | String | Description of the journal line item. | ||
Tags | String | Details of tags related to journal line items. | ||
TaxAuthorityId | String |
Taxes.TaxAuthorityId | Authority ID for tax. | |
TaxExemptionId | String |
BankRules.TaxExemptionId | ID of exemption in tax. | |
TaxExemptionType | String | Type of exemption in tax. | ||
TaxId | String |
Taxes.TaxId | Id of tax. | |
TaxName | String | Name of the tax. | ||
TaxPercentage | Integer | Percentage applied for tax. | ||
TaxType | String | Type of tax. |
Retrieves list of accounts of opening balance.
No filters are supported server side for this table. All criteria will be handled client side within the Cloud.
Name | Type | References | SupportedOperators | Description |
AccountId [KEY] | String |
BankAccounts.AccountId | ID of an account. | |
AccountName | String | Name of an account. | ||
AccountSplitId | String | Split ID of an account. | ||
AccountType | String | Type of the account. | ||
BcyAmount | Decimal | Base currency of the amount. | ||
CurrencyCode | String | Code of currency. | ||
CurrencyId | String |
Currencies.CurrencyId | ID of a currency. | |
DebitOrCredit | String | Type of mode the opening balance is. | ||
ExchangeRate | Decimal | Exchange rate of the currency. | ||
ProductId | String | ID of the product. | ||
ProductName | String | Name of the product. | ||
ProductStock | Integer | Stock of the product. | ||
ProductStockRate | Integer | Stock rate of the product. |
Get transaction summaries of opening balance.
No filters are supported server side for this table. All criteria will be handled client side within the Cloud.
Name | Type | References | SupportedOperators | Description |
EntityType | String | Type of entity of opening balance transaction summaries. | ||
Count | Integer | Count of transaction summary. |
Retrieves list of organizations.
No filters are supported server side for this table. All criteria will be handled client side within the Cloud.
Name | Type | References | SupportedOperators | Description |
OrganizationId [KEY] | String | ID of an organization. | ||
AccountCreatedDate | Date | Date when the account was created. | ||
CanChangeTimezone | Boolean | Check if the organization can change the timezone. | ||
CanShowDocumentTab | Boolean | Check if the organization can show a document tab. | ||
CanSignInvoice | Boolean | Check if an organization can sign invoice. | ||
ContactName | String | Display Name of the contact. Max-length [200] | ||
Country | String | Country of the organization. | ||
CountryCode | String | Country code of the organization. | ||
CurrencyFormat | String | Format of the currency used in the organization. | ||
CurrencyCode | String | Currency code of the customer's currency. | ||
CurrencyId | String |
Currencies.CurrencyId | Currency ID of the customer's currency. | |
CurrencySymbol | String | Symbol of currency used in the organization. | ||
DigitalSignatureMode | String | Mode of digital signature. | ||
String | Email address of an organization. | |||
FieldSeparator | String | Character which is used as a field separator. | ||
FiscalYearStartMonth | Integer | Start month of the fiscal year in an organization. | ||
IsBillOfSupplyEnabled | Boolean | Check if the bill of the supply is enabled in an organization.. | ||
IsDefaultOrg | Boolean | Check if this is a default organization. | ||
IsDesignatedZone | Boolean | Check if there is a designated zone in an organization. | ||
IsDsignRequired | Boolean | Check if the digital signature is required in the organization. | ||
IsExportWithPaymentEnabled | Boolean | Check if export with the payment enabled in the organization. | ||
IsGstIndiaVersion | Boolean | Check if the organization has GST version on India. | ||
IsHsnOrSacEnabled | Boolean | Check if the organization is enabled with HSN or SAC | ||
IsInternationalTradeEnabled | Boolean | Check if international trade is enabled in an organization. | ||
IsInventoryEnabled | Boolean | Check if inventory is available in an organization. | ||
IsInvoicePmtTdsAllowed | Boolean | Check if invoice payment TDS allowing in the organization | ||
IsPoEnabled | Boolean | Check if probationary officer is enabled in the organization. | ||
IsQuickSetupCompleted | Boolean | Check if quick setup is completed for the organization. | ||
IsRegisteredForCompositeScheme | Boolean | Check if the organization is registered for composite scheme. | ||
IsRegisteredForGst | Boolean | Check if the organization is registered for GST. | ||
IsRegisteredForTax | Boolean | Check if the organization is registered for tax. | ||
IsSalesInclusiveTaxEnabled | Boolean | Check if sales inclusive tas is enabled in the organization. | ||
IsSalesReverseChargeEnabled | Boolean | Check if sales reverse charge is enabled in the organization. | ||
IsScanPreferenceEnabled | Boolean | Check if scan preference is enabled in the organization. | ||
IsSearch360Enabled | Boolean | Check if search 360 is enabled in the organization. | ||
IsSkuEnabled | Boolean | Check if stock keeping unit is enabled in the organization. | ||
IsTaxRegistered | Boolean | Check if the tax is registered in the organization. | ||
IsTrialExpired | Boolean | Check if the trial is expired or not for a particular organization. | ||
IsTrialExtended | Boolean | Check if the trial is extended or not for a particular organization. | ||
IsZiedition | Boolean | Nodes used for Internal Usage for Zoho Books. | ||
IsOrgActive | Boolean | Check if Organization is active. | ||
IsOrgNotSupported | Boolean | Check if Organization is not supported. | ||
Mode | String | Mode of an organization. | ||
OrgAction | String | Action of the organization. | ||
OrgCreatedAppSource | Integer | Source of the app where organization was created. | ||
OrgSettings | Boolean | Settings of organization. | ||
PartnersDomain | String | Domain of partners. | ||
PlanName | String | Name of the plan. | ||
PlanPeriod | String | Period of the plan. | ||
PlanType | Integer | Type of a plan. | ||
SalesTaxType | String | Type of sales tax. | ||
Source | Integer | Source of the organizations | ||
StateCode | String | Code of state. | ||
TaxGroupEnabled | Boolean | Check if the tax group is enabled in the organization. | ||
ZiMigrationStatus | Integer | Nodes used for Internal Usage for Zoho Books. | ||
ZiZbClient | Integer | Nodes used for Internal Usage for Zoho Books. | ||
ZiZbEdition | Integer | Nodes used for Internal Usage for Zoho Books. |
This report summarizes your company's assets, liabilities and equity at a specific point in time
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
SELECT * FROM ProfitsAndLossesReport SELECT * FROM ProfitsAndLossesReport WHERE TransactionDate = 'Today' SELECT * FROM ProfitsAndLossesReport WHERE ToDate = '2022-10-31' SELECT * FROM ProfitsAndLossesReport WHERE FromDate = '2022-10-10' and ToDate = '2022-10-31' SELECT * FROM ProfitsAndLossesReport WHERE CashBased = True
Name | Type | References | SupportedOperators | Description |
BalanceTypeName | String | Balance Type Name | ||
SubBalanceTypeName | String | Sub Balance Type Name | ||
AccountTransactionTypeName | String | Account Transaction Type Name | ||
AccountTransactionTotal | Decimal | Account Transaction Total | ||
BalanceTypeTotal | Decimal | Balance Type Total | ||
SubBalanceTypeTotal | Decimal | Sub Balance Type Total |
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 | |
CashBased | Boolean | It has value either true or false | |
TransactionDate | String | Filter transaction by any transaction date.
The allowed values are Today, ThisWeek, ThisMonth, ThisQuarter, ThisYear, PreviousDay, PreviousWeek, PreviousMonth, PreviousQuarter, PreviousYear and CustomDate. | |
ToDate | Date | To Date | |
FromDate | Date | From Date |
Retrieves list of invoices created for a project.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
SELECT * FROM ProjectInvoices WHERE ProjectId = '1894553000000012367'
Name | Type | References | SupportedOperators | Description |
InvoiceId [KEY] | String |
Invoices.InvoiceId | ID of an invoice. | |
InvoiceNumber | String | Number of an invoice. | ||
ProjectId | String |
Projects.ProjectId | Id of a project. | |
Balance | Decimal | Total amount available in invoice. | ||
CreatedTime | Datetime | Time at which the project invoice was created. | ||
CustomerId | String |
Contacts.ContactId | ID of the customer or vendor. | |
CustomerName | String | Name of the customer or vendor. | ||
Date | Date | Date of project invoice. | ||
DueDate | Date | Delivery date of the project invoice. | ||
ReferenceNumber | String | Reference number of project invoice. | ||
Status | String | Status of the project invoice | ||
Total | Decimal | Total of project invoices. |
Retrieves list of users associated with a project. Also, get details of a user in project.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
SELECT * FROM ProjectUsers WHERE ProjectId = '1894553000000072363' AND UserId = '1894553000000056001'
Name | Type | References | SupportedOperators | Description |
ProjectId | String |
Projects.ProjectId | Id of a project. | |
UserId [KEY] | String |
Users.UserId | Id of the user. | |
IsCurrentUser | Boolean | Check if it is a current user. | ||
UserName | String | Username of the project users. | ||
String | Email ID of user. | |||
UserRole | String | Role of the user in project. | ||
RoleId | String |
Users.RoleId | ID of the role. | |
Status | String | Status of the project user. | ||
Rate | Decimal | Hourly rate for a task. | ||
BudgetHours | Integer | Total number of hours alloted to the user for the project. | ||
BudgetHoursInTime | String | Time of total number of hours alloted to the user for the project. | ||
TotalHours | String | Total number of hours to be spent in project. | ||
BilledHours | String | Total number of billed hours to be spent in project. | ||
UnBilledHours | String | Total number of unbilled hours spent in project. | ||
BillableHours | String | Total number of billable hours spent in project. | ||
NonBillableHours | String | Total number of non billable hours spent in project. | ||
StaffRole | String | Role of staff in project. | ||
StaffStatus | String | Status of staff in project. |
Get the details of line items of purchase orders.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
Note: PurchaseorderId is required in order to query PurchaseOrderLineItems.
SELECT * FROM PurchaseOrderLineItems WHERE PurchaseorderId = '1894553000000087078'
Name | Type | References | SupportedOperators | Description |
LineItemId [KEY] | String | Id of line item. | ||
PurchaseorderId | String |
PurchaseOrders.PurchaseorderId | Id of purchase order. | |
ItemId | String |
Items.ItemId | Id of an item. | |
ItemOrder | Integer | Order of an item. | ||
ItemTotal | Decimal | total number of item in purchase order. | ||
ItemType | String | Types of item. | ||
AccountId | String |
BankAccounts.AccountId | ID of the Bank Account. | |
AccountName | String | Name of the account. | ||
BcyRate | Decimal | Rate of base currency. | ||
CustomFields | String | Custom Fields added for the line item | ||
Description | String | Description of the purchase order line item. | ||
Discount | Double | Discount to be applied on purchase order line item. | ||
Name | String | Name of the line item. | ||
ProjectId | String |
Projects.ProjectId | ID of the project. | |
Quantity | Double | Total number of items added in purchase order. | ||
QuantityCancelled | Double | Total number of cancelled quantity added in purchase order. | ||
Rate | Decimal | Rate of the line item. | ||
Tags | String | Details of tags related to purchase order line items. | ||
TaxId | String |
Taxes.TaxId | Id of tax. | |
TaxName | String | Name of the tax. | ||
TaxPercentage | Integer | Percentage applied for tax. | ||
TaxType | String | Type of tax. | ||
Unit | String | Number of quantity. |
Retrieves list of all purchase orders.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
SELECT * FROM PurchaseOrders WHERE VendorId = 1894553000000077963 SELECT * FROM PurchaseOrders WHERE Total < 300 SELECT * FROM PurchaseOrders WHERE CONTAINS(PurchaseorderNumber,'PO-')
Name | Type | References | SupportedOperators | Description |
PurchaseorderId [KEY] | String | ID of a purchase order. | ||
BilledStatus | String | Billed status of a purchase order. | ||
ClientViewedTime | Datetime | Last time when the client has viewed purchase order. | ||
ColorCode | String | Color code of this purchase order. | ||
CompanyName | String | Name of the company. | ||
CreatedTime | Datetime | Time at which the purchase order was created. | ||
CurrencyCode | String | Currency code of the customer's currency. | ||
CurrencyId | String |
Currencies.CurrencyId | Currency ID of the customer's currency. | |
CurrentSubStatus | String | Current sub status of a purchase order. | ||
CurrentSubStatusId | String | Current sub status ID of a purchase order. | ||
Date | Date | The date the purchase order is created. | ||
DeliveryDate | Date | Delivery date of the order. | ||
HasAttachment | Boolean | Check if the purchase order has attachment. | ||
IsDropShipment | Boolean | Check if it has drop shipment. | ||
IsViewedByClient | Boolean | Check if the purchase order is viewed by client. | ||
LastModifiedTime | Datetime | Last modified time of a purchase order. | ||
OrderStatus | String | Status of purchase order. | ||
PricePrecision | String | The precision for the price. | ||
PurchaseorderNumber | String | Number of the purchase order. | ||
QuantityYetToReceive | Decimal | Number of quantity yet to receive from purchase order. | ||
ReferenceNumber | String | Reference number of a purchase order. | ||
Status | String | Status of a purchase order.
The allowed values are draft, open, billed, cancelled. | ||
Total | Decimal | =,<,<=,>,>= | Total of purchase orders. Search by purchase order total. | |
VendorId | String | ID of the vendor the purchase orders has been made. Search by vendor id. | ||
VendorName | String | Name of the vendor the purchase orders has been made. Search by vendor name. |
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 | |
PurchaseOrderFilter | String | Filter purchase order by any status.
The allowed values are Status.All, Status.Draft, Status.Open, Status.Billed, Status.Cancelled. | |
ItemId | String | Id of an item. | |
ItemDescription | String | Description of an item. |
Retrieves list of all the Expenses.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
SELECT * FROM RecurringExpenses WHERE Amount = 500 AND PaidThroughAccountName = 'Petty Cash' AND RecurrenceName = 'Vehicle Rent' SELECT * FROM RecurringExpenses WHERE LastCreatedDate > '2017-07-16'
Name | Type | References | SupportedOperators | Description |
RecurringExpenseId [KEY] | String | ID of recurring expense. | ||
AccountName | String | Account name of an expense. | ||
CreatedTime | Datetime | Time at which the recurring expense was created. | ||
CurrencyCode | String | Currency code of the customer's currency. | ||
CurrencyId | String |
Currencies.CurrencyId | Currency ID of the customer's currency. | |
CustomerName | String | Name of the customer. | ||
Description | String | Description of the recurring expense. | ||
IsBillable | Boolean | Check if recurring expense is billable or not. | ||
LastCreatedDate | Date | =,<,> | Recurring expenses date on when last expense was generated. | |
LastModifiedTime | Datetime | The time of last modification of the recurring expense. | ||
NextExpenseDate | Date | =,<,> | Recurring expenses date on which next expense will be generated. | |
PaidThroughAccountName | String | Name of the account from which expenses was paid. | ||
RecurrenceName | String | Name of the recurrence. | ||
Status | String | Status of a recurring expenses.
The allowed values are active, stopped, expired. | ||
Total | Decimal | Total of recurring expenses. |
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 | |
CustomerId | String | Id of a customer. | |
AccountId | String | ID of the Bank Account. | |
Amount | Decimal | Amount of a recurring expenses. | |
RecurringExpenseFilter | String | Filter expenses by expense status.
The allowed values are Status.All, Status.Active, Status.Expired, Status.Stopped. |
Get the details of line items of a recurring invoice.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
Note: RecurringInvoiceId is required in order to query RecurringInvoiceLineItems.
SELECT * FROM RecurringInvoiceLineItems WHERE RecurringInvoiceId = '1895453000000042244'
Name | Type | References | SupportedOperators | Description |
LineItemId [KEY] | String | ID of line item. | ||
RecurringInvoiceId | String |
RecurringInvoices.RecurringInvoiceId | ID of recurring invoice. | |
ItemId | String |
Items.ItemId | ID of the item. | |
ItemOrder | Integer | Order of the item. | ||
ItemTotal | Decimal | Total number of item. | ||
Description | String | Description of the recurring invoice line item. | ||
Discount | String | Amount of discount applied for items of recurring invoice. | ||
DiscountAmount | Decimal | Amount of discount. | ||
HeaderId | String | ID of the header. | ||
HeaderName | String | Name of the header. | ||
Name | String | Name of the recurring invoice line item. | ||
ProjectId | String |
Projects.ProjectId | Id of the project. | |
Quantity | String | Total number of item. | ||
Rate | Decimal | Rate for recurring invoice.. | ||
TaxId | String |
Taxes.TaxId | Id of tax. | |
TaxName | String | Name of the tax. | ||
TaxPercentage | Integer | Percentage applied for tax. | ||
TaxType | String | Type of tax. | ||
Unit | String | Total quantity included in recurring invoice items. |
Retrieves list of all recurring invoices.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
SELECT * FROM RecurringInvoices WHERE RecurrenceName = 'Office Rent' AND EndDate = '2018-06-21' SELECT * FROM RecurringInvoices WHERE ItemName = 'Standard Plan'
Name | Type | References | SupportedOperators | Description |
RecurringInvoiceId [KEY] | String | ID of a recurring invoice. | ||
RecurrenceName | String | Name of a recurrence. | ||
ChildEntityType | String | Entity type of a child. | ||
CreatedTime | Datetime | Time at which the recurring invoice was created. | ||
CustomerId | String |
Contacts.ContactId | ID of the customer or vendor. | |
CustomerName | String | Name of the customer or vendor. | ||
EndDate | Date | End date for the statement. | ||
LastFourDigits | String | It store the last four digits of customer's card details. | ||
LastModifiedTime | Datetime | The time of last modification of the recurring invoice. | ||
LastSentDate | Date | Date recurring invoice was last sent. | ||
NextInvoiceDate | Date | Date when recurring invoice will be send next. | ||
RecurrenceFrequency | String | Frequency of the recurrence. | ||
ReferenceNumber | String | Reference number of recurring invoice. | ||
StartDate | Date | Starting date of recurring invoice. | ||
Status | String | Status of the recurring invoice.
The allowed values are active, stopped, expired. | ||
Total | Decimal | Total of recurring invoices. |
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 | |
RecurringInvoiceFilter | String | Filter recurring invoice by status.
The allowed values are Status.All, Status.Active, Status.Stopped, Status.Expired. | |
LineItemId | String | Id of a line item. | |
TaxId | String | ID of tax. | |
Notes | String | Notes for this recurring invoice. | |
ItemId | String | ID of an item. | |
ItemName | String | Name of an item. | |
ItemDescription | String | Description of an item. |
Retrieves list of child expenses created from recurring expense.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
SELECT * FROM RecurringSubExpense WHERE RecurringExpenseId = '1801553000000089750'
Name | Type | References | SupportedOperators | Description |
RecurringExpenseId [KEY] | String |
RecurringExpenses.RecurringExpenseId | Sort expenses. | |
AccountName | String | Name of the account. | ||
CustomerName | String | Name of the customer. | ||
Date | Date | Date of a recurring expense. | ||
ExpenseId | String |
Expenses.ExpenseId | ID of an expense. | |
PaidThroughAccountName | String | Name of the account from which payment was made. | ||
Status | String | Status of the recurring expense. | ||
Total | Decimal | Total of child expenses of recurring expenses. | ||
VendorName | String | Name of the vendor. |
Retrieves the list of inline transactions.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
SELECT * FROM ReportsAccountTransactionsDetails SELECT * FROM ReportsAccountTransactionsDetails WHERE TransactionDate = 'Today' SELECT * FROM ReportsAccountTransactionsDetails WHERE ToDate = '2022-10-31' SELECT * FROM ReportsAccountTransactionsDetails WHERE FromDate = '2022-10-10' and ToDate = '2022-10-31' SELECT * FROM ReportsAccountTransactionsDetails WHERE AccountId = 1055236000000014066
Name | Type | References | SupportedOperators | Description |
AccountGroup | String | Account Group | ||
AccountType | String | Account Type | ||
AccountId | Long | = | Account Id | |
AccountName | String | Account Name | ||
Branch | String | Branch | ||
ContactId | Long |
Contacts.ContactId | Contact Id | |
Credit | Decimal | Credit | ||
CurrencyCode | String | Currency Code | ||
Date | Date | Date | ||
Debit | Decimal | Debit | ||
EntityNumber | String | Entity Number | ||
NetAmount | String | Net Amount | ||
OffsetAccountId | Long | Offset Account Id | ||
OffsetAccountType | String | Offset Account Type | ||
ProjectIds | Long |
Projects.ProjectId | Project Ids | |
ReferenceNumber | String | Reference Number | ||
ReferenceTransactionId | Long | Reference Transaction Id | ||
TransactionDetails | String | Transaction Details | ||
TransactionType | String | Transaction Type | ||
TransactionId | Long | Transaction Id |
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 | |
TransactionDate | String | Transaction Date
The allowed values are Today, ThisWeek, ThisMonth, ThisQuarter, ThisYear, PreviousDay, PreviousWeek, PreviousMonth, PreviousQuarter, PreviousYear and CustomDate. | |
ToDate | Date | To Date | |
FromDate | Date | From Date |
Retrieves detail of line items of retainer invoices.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
Note: RetainerInvoiceId is required in order to query RetainerInvoiceLineItems.
SELECT * FROM RetainerInvoiceLineItems WHERE RetainerInvoiceId = '1894663000000085023'
Name | Type | References | SupportedOperators | Description |
RetainerInvoiceId | String |
RetainerInvoices.RetainerInvoiceId | ID of a retainer invoice. | |
LineItemId [KEY] | String | Id of line item. | ||
AccountId | String |
BankAccounts.AccountId | ID of the Bank Account. | |
BcyRate | Decimal | Rate of Base Currency. | ||
Description | String | Description of the retainer invoice line item. | ||
ItemOrder | Integer | Order of items. | ||
ItemTotal | Decimal | Total number of items in retainer invoice. | ||
Rate | Decimal | Rate of the line item. | ||
TaxId | String |
Taxes.TaxId | Id of tax. | |
TaxName | String | Name of the tax. | ||
TaxPercentage | Integer | Percentage applied for tax. | ||
TaxType | String | Type of tax. |
Get the list of payments made for a retainer invoices.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
SELECT * FROM RetainerInvoicePayments WHERE RetainerInvoiceId = '1894663000000085023'
Name | Type | References | SupportedOperators | Description |
PaymentId [KEY] | String | Id of a payment. | ||
RetainerInvoiceId | String |
RetainerInvoices.RetainerInvoiceId | ID of a retainer invoice. | |
PaymentMode | String | Mode through which payment is made. | ||
ReferenceNumber | Currency | Reference number of retainer invoice payment. | ||
Amount | Integer | Amount of the retainer invoice payments. | ||
AttachmentName | String | Name of the attachment. | ||
BankCharges | Integer | Charges of the bank. | ||
CanSendInMail | Boolean | Check if the retainer invoice can be send through mail. | ||
CurrencyCode | String | Currency code of the customer's currency. | ||
CurrencyId | String |
Currencies.CurrencyId | Currency ID of the customer's currency. | |
CustomerId | String |
Contacts.ContactId | ID of the customer or vendor. | |
CustomerName | String | Name of the customer or vendor. | ||
Date | Date | Retainer Invoice date. | ||
Description | String | Description of the retainer invoice payment. | ||
DiscountAmount | Decimal | Amount given for discount. | ||
ExchangeRate | Decimal | Exchange rate given for this retainer invoice. | ||
HtmlString | String | HTML context of a retainer invoice. | ||
IsClientReviewSettingsEnabled | Boolean | Check if the client review settings is enabled or not. | ||
IsPaymentDrawnDetailsRequired | Boolean | Check if the payment drawn details is required. | ||
LastFourDigits | String | It store the last four digits of customer's card details. | ||
OnlineTransactionId | String | ID of online transaction. | ||
Orientation | String | Orientation of the page. | ||
PageHeight | String | Height of the page. | ||
PageWidth | String | Width of the page. | ||
RetainerinvceBalance | Integer | Total amount for retainer invoice. | ||
RetainerInvceDate | Date | Date for retainer invoice. | ||
RetainerInvceId | String |
RetainerInvoices.RetainerInvoiceId | ID for a retainer invoice. | |
RetainerInvceNumber | String | Number of a retainer invoice. | ||
RetainerInvceTotal | Integer | Total of retainer invoice. | ||
TaxAmountWithheld | Integer | Amount withheld for tax. | ||
TemplateId | String | ID of a template. | ||
TemplateName | String | Name of a template. | ||
TemplateType | String | Type of a template. | ||
UnusedAmount | Integer | Total amount which is unused. |
Retrieves list of all retainer invoices.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
SELECT * FROM RetainerInvoices WHERE Status = 'All'
Name | Type | References | SupportedOperators | Description |
RetainerInvoiceId [KEY] | String | ID of a retainer invoice. | ||
Balance | Decimal | Total balance of a retainer invoice. | ||
ClientViewedTime | Datetime | Time when client viewed the retainer invoice. | ||
ColorCode | String | Color code of retainer invoice. | ||
CreatedTime | Datetime | Time at which the retainer invoice was created. | ||
CurrencyCode | String | Currency code of the customer's currency. | ||
CurrencyId | String |
Currencies.CurrencyId | Currency ID of the customer's currency. | |
CurrentSubStatus | String | Current sub status of a retainer invoice. | ||
CurrentSubStatusId | String | Current sub status ID of a retainer invoice. | ||
CustomerId | String |
Contacts.ContactId | ID of the customer or vendor. | |
CustomerName | String | Name of the customer or vendor. | ||
Date | Date | Date of a retainer invoice. | ||
EstimateNumber | String | Estimate number of retainer invoice. | ||
HasAttachment | Boolean | Check if the retainer invoice has attachment. | ||
IsEmailed | Boolean | Check if the retainer invoice is emailed. | ||
IsViewedByClient | Boolean | Check if the retainer invoice is viewed by client. | ||
LastModifiedTime | Datetime | The time of last modification of the retainer invoices. | ||
LastPaymentDate | Date | Date of last payment made to retainer invoice. | ||
ProjectName | String | Name of the project. | ||
ProjectOrEstimateName | String | Name of project or estimate. | ||
ReferenceNumber | String | Reference number of a retainer invoice. | ||
RetainerinvoiceNumber | String | Total number of retainer invoice. | ||
Status | String | Status of the retainer invoice
The allowed values are All, Sent, Draft, OverDue, Paid, Void, Unpaid, PartiallyPaid, Viewed, Date.PaymentExpectedDate. | ||
Total | Decimal | Total of retainer invoices. |
Retrieves list of line items of a sales order.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
Note: SalesorderId is required in order to query SalesOrderLineItems.
SELECT * FROM SalesOrderLineItems WHERE SalesorderId = '1894553000000077349'
Name | Type | References | SupportedOperators | Description |
LineItemId [KEY] | String | ID of a line item. | ||
SalesorderId | String |
SalesOrders.SalesorderId | ID of a sales order. | |
BcyRate | Decimal | Rate of Base Currency. | ||
CustomFields | String | Custom Fields added for the line item | ||
Description | String | Description of the sales order line item | ||
Discount | String | Discount given to specific item in sales order. | ||
DiscountAmount | Decimal | Amount of discount applied for items of sales order. | ||
ImageDocumentId | String | ID of image document. | ||
ImageName | String | Name of the image. | ||
ImageType | String | Type of image. | ||
IsInvoiced | Boolean | Check if the sales order is invoiced. | ||
ItemId | String |
Items.ItemId | Id of an item. | |
ItemOrder | Integer | Order of an item. | ||
ItemTotal | Decimal | Total amount of an item. | ||
ItemType | String | Type of item. | ||
Name | String | Name of the sales order. | ||
ProjectId | String |
Projects.ProjectId | ID of the project. | |
Quantity | Decimal | Total number of quantity | ||
QuantityCancelled | Decimal | Total number of quantity canceled for salesorder. | ||
Rate | Decimal | Rate of the line item. | ||
Tags | String | Details of tags related to sales order line items | ||
TaxId | String |
Taxes.TaxId | Id of tax. | |
TaxName | String | Name of the tax. | ||
TaxPercentage | Integer | Percentage applied for tax. | ||
TaxType | String | Type of tax. | ||
Unit | String | Total quantity included in sales order items. |
Retrieves list of all sales orders.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
SELECT * FROM SalesOrders WHERE CustomerId = '1894553000000077328' AND CustomerName = 'Zylak' AND SalesOrderFilter = 'Status.All' SELECT * FROM SalesOrders WHERE ShipmentDate > '2017-08-01' SELECT * FROM SalesOrders WHERE CONTAINS(SalesorderNumber,'SO-00')
Name | Type | References | SupportedOperators | Description |
SalesorderId [KEY] | String | ID of a sales order. | ||
SalespersonName | String | Name of the sales order. | ||
BcyTotal | Decimal | Total Base Currency. | ||
ColorCode | String | Color code of Sales order. | ||
CompanyName | String | Name of the company. | ||
CreatedTime | Datetime | Time at which the sales order was created. | ||
CurrencyCode | String | Currency code of the customer's currency. | ||
CurrencyId | String |
Currencies.CurrencyId | Currency ID of the customer's currency. | |
CurrentSubStatus | String | Current sub status of a sales order. | ||
CurrentSubStatusId | String | Current sub status ID of a sales order. | ||
CustomerId | String |
Contacts.ContactId | ID of the customer or vendor. Search Sales Order based on customer_id. | |
CustomerName | String | Name of the customer or vendor. . | ||
Date | Date | =,<,> | Date of an sales order. | |
DueByDays | String | Total number of day sales order is due by. | ||
DueInDays | String | Total number of day the sales order is due in. | ||
HasAttachment | Boolean | Check if the sales order has attachment. | ||
InvoicedStatus | String | Status of invoiced sales orders. | ||
IsEmailed | Boolean | Check if the sales order is emailed. | ||
LastModifiedTime | Datetime | The time of last modification of the sales order. | ||
OrderStatus | String | Status of order. | ||
ReferenceNumber | String | Reference number of sales order. | ||
SalesorderNumber | String | Number of a sales order. | ||
ShipmentDate | Date | =,<,> | Date of shipment. | |
ShipmentDays | String | Total number of days for shipment. | ||
Status | String | Status of sales order.
The allowed values are draft, open, invoiced, partially_invoiced, void, overdue. | ||
Total | Decimal | =,<,<=,>,>= | Total of sales orders. | |
TotalInvoicedAmount | Decimal | Total amount which is invoiced. |
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 | |
ItemId | String | Id of an item. | |
ItemName | String | Name of an item. | |
ItemDescription | String | Description of an item. | |
SalesOrderFilter | String | Filter sales order by status.
The allowed values are Status.All, Status.Open, Status.Draft, Status.OverDue, Status.PartiallyInvoiced, Status.Invoiced, Status.Void, Status.Closed. |
This report summarizes your company's assets, liabilities and equity at a specific point in time
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
SELECT * FROM TaxSummaryReport SELECT * FROM TaxSummaryReport WHERE ToDate = '2022-10-31' SELECT * FROM TaxSummaryReport WHERE FromDate = '2022-10-10' and ToDate = '2022-10-31'
Name | Type | References | SupportedOperators | Description |
InputTaxAccountId | Long | Input Tax Account Id | ||
IsTaxAccount | Boolean | Is Tax Account | ||
IsValueAdded | Boolean | Is Value Added | ||
OutputTaxAccountId | Long | Output Tax Account Id | ||
TaxAmount | String | Tax Amount | ||
TaxId | Long | Tax Id | ||
TaxName | String | Tax Name | ||
TaxPercentage | String | Tax Percentage | ||
TransactionAmount | String | Transaction Amount |
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 | |
ToDate | Date | To Date | |
FromDate | Date | From Date |
This report summarizes your company's assets, liabilities and equity at a specific point in time
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
SELECT * FROM TrialBalanceReport SELECT * FROM TrialBalanceReport WHERE TransactionDate = 'Today' SELECT * FROM TrialBalanceReport WHERE ToDate = '2022-10-31' SELECT * FROM TrialBalanceReport WHERE FromDate = '2022-10-10' and ToDate = '2022-10-31' SELECT * FROM TrialBalanceReport WHERE CashBased = True
Name | Type | References | SupportedOperators | Description |
BalanceTypeName | String | Balance Type Name | ||
SubBalanceTypeName | String | Sub Balance Type Name | ||
AccountTransactionTypeName | String | Account Transaction Type Name | ||
AccountTransactionCreditTotal | Decimal | Credit Total | ||
AccountTransactionDebitTotal | Decimal | Debit Total | ||
SubAccountCreditTotal | Decimal | Sub Account Credit Total | ||
SubAccountDebitTotal | Decimal | Sub Account Debit Total | ||
CreditTotal | Decimal | Account Transaction Type Credit Total | ||
DebitTotal | Decimal | Account Transaction Type Debit Total |
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 | |
CashBased | Boolean | Cash Based | |
TransactionDate | String | Filter transaction by any transaction date.
The allowed values are Today, ThisWeek, ThisMonth, ThisQuarter, ThisYear, PreviousDay, PreviousWeek, PreviousMonth, PreviousQuarter, PreviousYear and CustomDate. | |
ToDate | Date | ToDate | |
FromDate | Date | FromDate |
Retrieves list of bills to which the vendor credit is applied.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
SELECT * FROM VendorCreditBills WHERE VendorCreditId = '1894545000000083308'
Name | Type | References | SupportedOperators | Description |
VendorCreditBillId [KEY] | String | Bill ID of vendor credit. | ||
VendorCreditId | String |
VendorCredits.VendorCreditId | ID of a vendor credit. | |
VendorCreditNumber | String | Number of vendor credit. | ||
Amount | Decimal | Amount of the vendor credited bills. | ||
BillId | String |
Bills.BillId | Id of a bill. | |
BillNumber | String | Number of a bill. | ||
Date | Date | Date of a vendor credit. |
Retrieves list of line items from vendor credits.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
Note: VendorCreditId is required in order to query VendorCreditLineItems.
SELECT * FROM VendorCreditLineItems WHERE VendorCreditId = '1894545000000083308'
Name | Type | References | SupportedOperators | Description |
LineItemId [KEY] | String | ID of a line item. | ||
VendorCreditId | String |
VendorCredits.VendorCreditId | ID of a vendor credit. | |
AccountId | String |
BankAccounts.AccountId | ID of the Bank Account. | |
AccountName | String | Name of the account. | ||
BcyRate | Decimal | Rate of a Base Currency. | ||
CustomFields | String | Custom Fields added for the line item | ||
Description | String | Description of the vendor credit line item. | ||
GstTreatmentCode | String | Treatement code of GST. | ||
HasProductTypeMismatch | Boolean | Check if the product type has mismatch. | ||
HsnOrSac | String | HSN Code. | ||
ItcEligibility | String | Eligibility of Input Tax Credit. | ||
ItemId | String |
Items.ItemId | ID of an item. | |
ItemOrder | Integer | Order of an item. | ||
ItemTotal | Decimal | Total of an item. | ||
ItemType | String | Type of an item. | ||
Name | String | Name of a line item. | ||
PricebookId | String | Id of a price book. | ||
ProductType | String | Product type of vendor credit. | ||
ProjectId | String |
Projects.ProjectId | Id of a project. | |
Quantity | Decimal | Quantity of vendor credit. | ||
Rate | Decimal | Rate of vendor credit. | ||
ReverseChargeTaxId | String | ID of the reverse charge tax. | ||
Tags | String | Details of tags related to vendor credit line items. | ||
TaxExemptionCode | String | Code of tax exemption. | ||
TaxExemptionId | String |
BankRules.TaxExemptionId | ID of tax exemption. | |
TaxId | String |
Taxes.TaxId | ID of tax. | |
TaxName | String | Name of tax. | ||
TaxPercentage | Integer | Percentage of tax. | ||
TaxType | String | Type of tax. | ||
Unit | String | Unit of line items in vendor credit. |
Retrieves list of vendor credits.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
SELECT * FROM VendorCredits WHERE Total >= 100 AND Status = 'open' SELECT * FROM VendorCredits WHERE VendorCreditNumber = 'CN-00001'
Name | Type | References | SupportedOperators | Description |
VendorCreditId [KEY] | String | ID of a vendor credit. | ||
VendorCreditNumber | String | Number of a vendor credit. | ||
VendorName | String | Name of the vendor the vendor credit has been made. | ||
Balance | Decimal | Balance of a vendor credit. | ||
ColorCode | String | Color code of vendor credit. | ||
CreatedTime | Datetime | Time at which the vendor credit was created. | ||
CurrencyCode | String | Currency code of the customer's currency. | ||
CurrencyId | String |
Currencies.CurrencyId | Currency ID of the customer's currency. | |
CurrentSubStatus | String | Current sub status of a vendor credit. | ||
CurrentSubStatusId | String | Current sub status ID of a vendor credit. | ||
Date | Date | =,<,> | Date of the vendor credit. | |
HasAttachment | Boolean | Check if vendor credit has attachment. | ||
LastModifiedTime | Datetime | Last modfified time of the vendor credit. | ||
ReferenceNumber | String | Reference number of the vendor credit. | ||
Status | String | Status of the vendor credit.
The allowed values are open, closed, void. | ||
Total | Decimal | =,<,<=,>,>= | Total of vendor credits. Search by total amount. |
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 | |
CustomerId | String | Id of a customer. | |
VendorCreditFilter | String | Filter vendor credits by statuses.
The allowed values are Status.All, Status.Open, Status.Draft, Status.Closed, Status.Void. | |
LineItemId | String | Id of a line item. | |
ItemId | String | Id of an item. | |
TaxId | String | Id of a tax. | |
CustomerName | String | Name of the vendor. | |
ItemDescription | String | Description of an item. | |
Notes | String | Notes of a vendor credit. | |
ItemName | String | Name of an item. |
Retrieves list of all the payments made to your vendor.
The Cloud will use the Zoho Books API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
SELECT * FROM VendorPayments WHERE Amount >= 98 AND Date < '2019-07-07' SELECT * FROM VendorPayments WHERE PaymentNumber = 1; SELECT * FROM VendorPayments WHERE CONTAINS(PaymentMode,'sh')
Name | Type | References | SupportedOperators | Description |
PaymentId [KEY] | String | ID of a payment. | ||
VendorId | String | ID of the vendor the vendor payment has been made. Search payments by vendor id. | ||
VendorName | String | Name of the vendor the vendor payment has been made. Search payments by vendor name. | ||
AchGwTransactionId | String | ID of a ACH GW Transaction. | ||
Amount | Decimal | =,<,<=,>,>= | Payment amount made to the vendor. Search payments by payment amount. | |
Balance | Decimal | Balance of vendor payment. | ||
BcyAmount | Decimal | Amount of Base Currency. | ||
BcyBalance | Decimal | Balance of Base Currency. | ||
CheckDetailsCheckId | String | ID of a check. | ||
CheckDetailsCheckNumber | String | Number of a check. | ||
Date | Date | =,<,> | Date the payment is made. Search payments by payment made date. | |
Description | String | Description of a vendor payment. | ||
HasAttachment | Boolean | Check if a vendor payment has attachment. | ||
LastModifiedTime | Datetime | Last Modified Time of the Vendor Payment. | ||
PaymentMode | String | Mode through which payment is made. Search payments by payment mode. | ||
PaymentNumber | Integer | Number through which payment is made. Search with Payment Number. | ||
ReferenceNumber | String | Reference number of a a bill. |
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 | |
VendorPaymentFilter | String | Filter payments by mode.
The allowed values are PaymentMode.All, PaymentMode.Check, PaymentMode.Cash, PaymentMode.BankTransfer, PaymentMode.Paypal, PaymentMode.CreditCard, PaymentMode.GoogleCheckout, PaymentMode.Credit, PaymentMode.Authorizenet, PaymentMode.BankRemittance, PaymentMode.Payflowpro, PaymentMode.Others.. | |
Notes | String | Notes of vendor payments.. |
Stored procedures are function-like interfaces that extend the functionality of the Cloud beyond simple SELECT/INSERT/UPDATE/DELETE operations with Zoho Books.
Stored procedures accept a list of parameters, perform their intended function, and then return, if applicable, any relevant response data from Zoho Books, along with an indication of whether the procedure succeeded or failed.
Name | Description |
Returns the file attached to the invoice.
Zoho Books allows only a small subset of columns to be used in the Exec query. These columns can typically be used with only = comparison. The available columns for DownloadAttachment are SourceId, SourceType and FileLocation.
SourceId will be the ID of different categories such as Invoices, Purchase Orders, Bills, SalesOrders.
SourceType will be the category types such as Invoices, Purchase Orders, Bills, SalesOrders.
FileLocation will be the path where the attachment will be downloaded.
For example:
EXECUTE DownloadAttachment SourceId = '1894553000000091007', SourceType = 'invoices', FileLocation = 'C:/zohobooks'
Name | Type | Required | Accepts Output Streams | Description |
SourceId | String | True | False | ID of categories like Invoices, Purchase Orders, Bills, SalesOrders. |
SourceType | String | True | False | Type of source
The allowed values are invoices, purchaseorders, bills, salesorders. |
FileLocation | String | False | False | The folder path to download the file to. |
Encoding | String | False | False | The FileData input encoding type.
The allowed values are NONE, BASE64. The default value is BASE64. |
FileStream | String | False | True | An instance of an output stream where file data is written to. Only used if FileLocation is not provided. |
Name | Type | Description |
Status | String | Stored procedure execution status. |
FileData | String | If the FileLocation and FileStream are not provided, this contains the content of the file. |
Returns the receipt attached to the expense.
Zoho Books allows only a small subset of columns to be used in the Exec query. These columns can typically be used with only the = comparison. The available columns for DownloadExpenseReceipt are ExpenseId and FileLocation.
ExpenseId will be ID of an Expense.
FileLocation will be containing the path where the attachment will get downloaded.
For example:
EXECUTE DownloadExpenseReceipt ExpenseId = '1894553000000092001', FileLocation = 'C:/zohobooks'
Name | Type | Required | Accepts Output Streams | Description |
ExpenseId | String | True | False | Id of an expense. |
FileLocation | String | False | False | The folder path to download the file to. |
ExportFormat | String | False | False | The format of the document to download (HTML or markdown).
The default value is html. |
AsZip | String | False | False | Download the file or folder in a zip format. |
Encoding | String | False | False | The FileData input encoding type.
The allowed values are NONE, BASE64. The default value is BASE64. |
FileStream | String | False | True | An instance of an output stream where file data is written to. Only used if FileLocation is not provided. |
Name | Type | Description |
Status | String | Stored procedure execution status. |
FileData | String | If the FileLocation and FileStream are not provided, this contains the content of the file. |
Returns the file attached to the retainer invoice.
Zoho Books allows only a small subset of columns to be used in the Exec query. These columns can typically be used with only = comparison. The available columns for DownloadExpenseReceipt are RetainerInvoiceId, DocumentId and FileLocation.
RetainerInvoiceId will be the ID of the Retainer Invoice.
DocumentId will be the ID of the Document.
FileLocation will contain the path where the attachment will get downloaded.
For example:
EXECUTE DownloadRetainerInvoiceAttachment RetainerInvoiceId = '1894553000000085021', DocumentId = '1894553000000099221', FileLocation = 'C:/zohobooks'
Name | Type | Required | Accepts Output Streams | Description |
RetainerInvoiceId | String | True | False | Id of retainer invoice. |
DocumentId | String | True | False | Id of document. |
FileLocation | String | False | False | The folder path to download the file to. |
Encoding | String | False | False | The FileData input encoding type.
The allowed values are NONE, BASE64. The default value is BASE64. |
FileStream | String | False | True | An instance of an output stream where file data is written to. Only used if FileLocation is not provided. |
Name | Type | Description |
Status | String | Stored procedure execution status. |
FileData | String | If the FileLocation and FileStream are not provided, this contains the content of the file. |
Gets an authentication token from Zoho Books.
Name | Type | Required | Description |
AuthMode | String | False | The type of authentication mode to use. Select App for getting authentication tokens via a desktop app. Select Web for getting authentication tokens via a Web app.
The allowed values are APP, WEB. The default value is APP. |
Scope | String | False | A comma-separated list of permissions to request from the user. Please check the Zoho Books API for a list of available permissions.
The default value is ZohoBooks.fullaccess.all. |
CallbackUrl | String | False | The URL the user will be redirected to after authorizing your application. This value must match the Redirect URL you have specified in the ZohoBooks app settings. Only needed when the Authmode parameter is Web. |
Verifier | String | False | The verifier returned from Zoho Books after the user has authorized your app to have access to their data. This value will be returned as a parameter to the callback URL. |
State | String | False | Indicates any state which may be useful to your application upon receipt of the response. Your application receives the same value it sent, as this parameter makes a round-trip to the ZohoBooks authorization server and back. Uses include redirecting the user to the correct resource in your site, nonces, and cross-site-request-forgery mitigations. |
Name | Type | Description |
OAuthAccessToken | String | The access token used for communication with Zoho Books. |
OAuthRefreshToken | String | The OAuth refresh token. This is the same as the access token in the case of Zoho Books. |
ExpiresIn | String | The remaining lifetime on the access token. A -1 denotes that it will not expire. |
Gets the authorization URL that must be opened separately by the user to grant access to your application. Only needed when developing Web apps. You will request the OAuthAccessToken from this URL.
Name | Type | Required | Description |
CallbackUrl | String | False | The URL the user will be redirected to after authorizing your application. This value must match the Redirect URL in the Zoho Books app settings. |
Scope | String | False | A comma-separated list of scopes to request from the user. Please check the Zoho Books API documentation for a list of available permissions.
The default value is ZohoBooks.fullaccess.all. |
State | String | False | Indicates any state which may be useful to your application upon receipt of the response. Your application receives the same value it sent, as this parameter makes a round-trip to the Zoho Books authorization server and back. Uses include redirecting the user to the correct resource in your site, nonces, and cross-site-request-forgery mitigations. |
Name | Type | Description |
URL | String | The authorization URL, entered into a Web browser to obtain the verifier token and authorize your app. |
Refreshes the OAuth access token used for authentication with ZohoBooks.
Name | Type | Required | Description |
OAuthRefreshToken | String | True | Set this to the token value that expired. |
Name | Type | Description |
OAuthAccessToken | String | The authentication token returned from ZohoBooks. This can be used in subsequent calls to other operations for this particular service. |
OAuthRefreshToken | String | This is the same as the access token. |
ExpiresIn | String | The remaining lifetime on the access token. |
You can query the system tables described in this section to access schema information, information on data source functionality, and batch operation statistics.
The following tables return database metadata for Zoho Books:
The following tables return information about how to connect to and query the data source:
The following table returns query statistics for data modification queries:
Lists the available databases.
The following query retrieves all databases determined by the connection string:
SELECT * FROM sys_catalogs
Name | Type | Description |
CatalogName | String | The database name. |
Lists the available schemas.
The following query retrieves all available schemas:
SELECT * FROM sys_schemas
Name | Type | Description |
CatalogName | String | The database name. |
SchemaName | String | The schema name. |
Lists the available tables.
The following query retrieves the available tables and views:
SELECT * FROM sys_tables
Name | Type | Description |
CatalogName | String | The database containing the table or view. |
SchemaName | String | The schema containing the table or view. |
TableName | String | The name of the table or view. |
TableType | String | The table type (table or view). |
Description | String | A description of the table or view. |
IsUpdateable | Boolean | Whether the table can be updated. |
Describes the columns of the available tables and views.
The following query returns the columns and data types for the INVOICES table:
SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='INVOICES'
Name | Type | Description |
CatalogName | String | The name of the database containing the table or view. |
SchemaName | String | The schema containing the table or view. |
TableName | String | The name of the table or view containing the column. |
ColumnName | String | The column name. |
DataTypeName | String | The data type name. |
DataType | Int32 | An integer indicating the data type. This value is determined at run time based on the environment. |
Length | Int32 | The storage size of the column. |
DisplaySize | Int32 | The designated column's normal maximum width in characters. |
NumericPrecision | Int32 | The maximum number of digits in numeric data. The column length in characters for character and date-time data. |
NumericScale | Int32 | The column scale or number of digits to the right of the decimal point. |
IsNullable | Boolean | Whether the column can contain null. |
Description | String | A brief description of the column. |
Ordinal | Int32 | The sequence number of the column. |
IsAutoIncrement | String | Whether the column value is assigned in fixed increments. |
IsGeneratedColumn | String | Whether the column is generated. |
IsHidden | Boolean | Whether the column is hidden. |
IsArray | Boolean | Whether the column is an array. |
Lists the available stored procedures.
The following query retrieves the available stored procedures:
SELECT * FROM sys_procedures
Name | Type | Description |
CatalogName | String | The database containing the stored procedure. |
SchemaName | String | The schema containing the stored procedure. |
ProcedureName | String | The name of the stored procedure. |
Description | String | A description of the stored procedure. |
ProcedureType | String | The type of the procedure, such as PROCEDURE or FUNCTION. |
Describes stored procedure parameters.
The following query returns information about all of the input parameters for the ExpenseReceipt stored procedure:
SELECT * FROM sys_procedureparameters WHERE ProcedureName='ExpenseReceipt' AND Direction=1 OR Direction=2
Name | Type | Description |
CatalogName | String | The name of the database containing the stored procedure. |
SchemaName | String | The name of the schema containing the stored procedure. |
ProcedureName | String | The name of the stored procedure containing the parameter. |
ColumnName | String | The name of the stored procedure parameter. |
Direction | Int32 | An integer corresponding to the type of the parameter: input (1), input/output (2), or output(4). input/output type parameters can be both input and output parameters. |
DataTypeName | String | The name of the data type. |
DataType | Int32 | An integer indicating the data type. This value is determined at run time based on the environment. |
Length | Int32 | The number of characters allowed for character data. The number of digits allowed for numeric data. |
NumericPrecision | Int32 | The maximum precision for numeric data. The column length in characters for character and date-time data. |
NumericScale | Int32 | The number of digits to the right of the decimal point in numeric data. |
IsNullable | Boolean | Whether the parameter can contain null. |
IsRequired | Boolean | Whether the parameter is required for execution of the procedure. |
IsArray | Boolean | Whether the parameter is an array. |
Description | String | The description of the parameter. |
Ordinal | Int32 | The index of the parameter. |
Describes the primary and foreign keys.
The following query retrieves the primary key for the INVOICES table:
SELECT * FROM sys_keycolumns WHERE IsKey='True' AND TableName='INVOICES'
Name | Type | Description |
CatalogName | String | The name of the database containing the key. |
SchemaName | String | The name of the schema containing the key. |
TableName | String | The name of the table containing the key. |
ColumnName | String | The name of the key column. |
IsKey | Boolean | Whether the column is a primary key in the table referenced in the TableName field. |
IsForeignKey | Boolean | Whether the column is a foreign key referenced in the TableName field. |
PrimaryKeyName | String | The name of the primary key. |
ForeignKeyName | String | The name of the foreign key. |
ReferencedCatalogName | String | The database containing the primary key. |
ReferencedSchemaName | String | The schema containing the primary key. |
ReferencedTableName | String | The table containing the primary key. |
ReferencedColumnName | String | The column name of the primary key. |
Describes the foreign keys.
The following query retrieves all foreign keys which refer to other tables:
SELECT * FROM sys_foreignkeys WHERE ForeignKeyType = 'FOREIGNKEY_TYPE_IMPORT'
Name | Type | Description |
CatalogName | String | The name of the database containing the key. |
SchemaName | String | The name of the schema containing the key. |
TableName | String | The name of the table containing the key. |
ColumnName | String | The name of the key column. |
PrimaryKeyName | String | The name of the primary key. |
ForeignKeyName | String | The name of the foreign key. |
ReferencedCatalogName | String | The database containing the primary key. |
ReferencedSchemaName | String | The schema containing the primary key. |
ReferencedTableName | String | The table containing the primary key. |
ReferencedColumnName | String | The column name of the primary key. |
ForeignKeyType | String | Designates whether the foreign key is an import (points to other tables) or export (referenced from other tables) key. |
Describes the primary keys.
The following query retrieves the primary keys from all tables and views:
SELECT * FROM sys_primarykeys
Name | Type | Description |
CatalogName | String | The name of the database containing the key. |
SchemaName | String | The name of the schema containing the key. |
TableName | String | The name of the table containing the key. |
ColumnName | String | The name of the key column. |
KeySeq | String | The sequence number of the primary key. |
KeyName | String | The name of the primary key. |
Describes the available indexes. By filtering on indexes, you can write more selective queries with faster query response times.
The following query retrieves all indexes that are not primary keys:
SELECT * FROM sys_indexes WHERE IsPrimary='false'
Name | Type | Description |
CatalogName | String | The name of the database containing the index. |
SchemaName | String | The name of the schema containing the index. |
TableName | String | The name of the table containing the index. |
IndexName | String | The index name. |
ColumnName | String | The name of the column associated with the index. |
IsUnique | Boolean | True if the index is unique. False otherwise. |
IsPrimary | Boolean | True if the index is a primary key. False otherwise. |
Type | Int16 | An integer value corresponding to the index type: statistic (0), clustered (1), hashed (2), or other (3). |
SortOrder | String | The sort order: A for ascending or D for descending. |
OrdinalPosition | Int16 | The sequence number of the column in the index. |
Returns information on the available connection properties and those set in the connection string.
When querying this table, the config connection string should be used:
jdbc:cdata:zohobooks:config:
This connection string enables you to query this table without a valid connection.
The following query retrieves all connection properties that have been set in the connection string or set through a default value:
SELECT * FROM sys_connection_props WHERE Value <> ''
Name | Type | Description |
Name | String | The name of the connection property. |
ShortDescription | String | A brief description. |
Type | String | The data type of the connection property. |
Default | String | The default value if one is not explicitly set. |
Values | String | A comma-separated list of possible values. A validation error is thrown if another value is specified. |
Value | String | The value you set or a preconfigured default. |
Required | Boolean | Whether the property is required to connect. |
Category | String | The category of the connection property. |
IsSessionProperty | String | Whether the property is a session property, used to save information about the current connection. |
Sensitivity | String | The sensitivity level of the property. This informs whether the property is obfuscated in logging and authentication forms. |
PropertyName | String | A camel-cased truncated form of the connection property name. |
Ordinal | Int32 | The index of the parameter. |
CatOrdinal | Int32 | The index of the parameter category. |
Hierarchy | String | Shows dependent properties associated that need to be set alongside this one. |
Visible | Boolean | Informs whether the property is visible in the connection UI. |
ETC | String | Various miscellaneous information about the property. |
Describes the SELECT query processing that the Cloud can offload to the data source.
See SQL Compliance for SQL syntax details.
Below is an example data set of SQL capabilities. The following result set indicates the SELECT functionality that the Cloud can offload to the data source or process client side. Your data source may support additional SQL syntax. Some aspects of SELECT functionality are returned in a comma-separated list if supported; otherwise, the column contains NO.
Name | Description | Possible Values |
AGGREGATE_FUNCTIONS | Supported aggregation functions. | AVG, COUNT, MAX, MIN, SUM, DISTINCT |
COUNT | Whether COUNT function is supported. | YES, NO |
IDENTIFIER_QUOTE_OPEN_CHAR | The opening character used to escape an identifier. | [ |
IDENTIFIER_QUOTE_CLOSE_CHAR | The closing character used to escape an identifier. | ] |
SUPPORTED_OPERATORS | A list of supported SQL operators. | =, >, <, >=, <=, <>, !=, LIKE, NOT LIKE, IN, NOT IN, IS NULL, IS NOT NULL, AND, OR |
GROUP_BY | Whether GROUP BY is supported, and, if so, the degree of support. | NO, NO_RELATION, EQUALS_SELECT, SQL_GB_COLLATE |
OJ_CAPABILITIES | The supported varieties of outer joins supported. | NO, LEFT, RIGHT, FULL, INNER, NOT_ORDERED, ALL_COMPARISON_OPS |
OUTER_JOINS | Whether outer joins are supported. | YES, NO |
SUBQUERIES | Whether subqueries are supported, and, if so, the degree of support. | NO, COMPARISON, EXISTS, IN, CORRELATED_SUBQUERIES, QUANTIFIED |
STRING_FUNCTIONS | Supported string functions. | LENGTH, CHAR, LOCATE, REPLACE, SUBSTRING, RTRIM, LTRIM, RIGHT, LEFT, UCASE, SPACE, SOUNDEX, LCASE, CONCAT, ASCII, REPEAT, OCTET, BIT, POSITION, INSERT, TRIM, UPPER, REGEXP, LOWER, DIFFERENCE, CHARACTER, SUBSTR, STR, REVERSE, PLAN, UUIDTOSTR, TRANSLATE, TRAILING, TO, STUFF, STRTOUUID, STRING, SPLIT, SORTKEY, SIMILAR, REPLICATE, PATINDEX, LPAD, LEN, LEADING, KEY, INSTR, INSERTSTR, HTML, GRAPHICAL, CONVERT, COLLATION, CHARINDEX, BYTE |
NUMERIC_FUNCTIONS | Supported numeric functions. | ABS, ACOS, ASIN, ATAN, ATAN2, CEILING, COS, COT, EXP, FLOOR, LOG, MOD, SIGN, SIN, SQRT, TAN, PI, RAND, DEGREES, LOG10, POWER, RADIANS, ROUND, TRUNCATE |
TIMEDATE_FUNCTIONS | Supported date/time functions. | NOW, CURDATE, DAYOFMONTH, DAYOFWEEK, DAYOFYEAR, MONTH, QUARTER, WEEK, YEAR, CURTIME, HOUR, MINUTE, SECOND, TIMESTAMPADD, TIMESTAMPDIFF, DAYNAME, MONTHNAME, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, EXTRACT |
REPLICATION_SKIP_TABLES | Indicates tables skipped during replication. | |
REPLICATION_TIMECHECK_COLUMNS | A string array containing a list of columns which will be used to check for (in the given order) to use as a modified column during replication. | |
IDENTIFIER_PATTERN | String value indicating what string is valid for an identifier. | |
SUPPORT_TRANSACTION | Indicates if the provider supports transactions such as commit and rollback. | YES, NO |
DIALECT | Indicates the SQL dialect to use. | |
KEY_PROPERTIES | Indicates the properties which identify the uniform database. | |
SUPPORTS_MULTIPLE_SCHEMAS | Indicates if multiple schemas may exist for the provider. | YES, NO |
SUPPORTS_MULTIPLE_CATALOGS | Indicates if multiple catalogs may exist for the provider. | YES, NO |
DATASYNCVERSION | The CData Data Sync version needed to access this driver. | Standard, Starter, Professional, Enterprise |
DATASYNCCATEGORY | The CData Data Sync category of this driver. | Source, Destination, Cloud Destination |
SUPPORTSENHANCEDSQL | Whether enhanced SQL functionality beyond what is offered by the API is supported. | TRUE, FALSE |
SUPPORTS_BATCH_OPERATIONS | Whether batch operations are supported. | YES, NO |
SQL_CAP | All supported SQL capabilities for this driver. | SELECT, INSERT, DELETE, UPDATE, TRANSACTIONS, ORDERBY, OAUTH, ASSIGNEDID, LIMIT, LIKE, BULKINSERT, COUNT, BULKDELETE, BULKUPDATE, GROUPBY, HAVING, AGGS, OFFSET, REPLICATE, COUNTDISTINCT, JOINS, DROP, CREATE, DISTINCT, INNERJOINS, SUBQUERIES, ALTER, MULTIPLESCHEMAS, GROUPBYNORELATION, OUTERJOINS, UNIONALL, UNION, UPSERT, GETDELETED, CROSSJOINS, GROUPBYCOLLATE, MULTIPLECATS, FULLOUTERJOIN, MERGE, JSONEXTRACT, BULKUPSERT, SUM, SUBQUERIESFULL, MIN, MAX, JOINSFULL, XMLEXTRACT, AVG, MULTISTATEMENTS, FOREIGNKEYS, CASE, LEFTJOINS, COMMAJOINS, WITH, LITERALS, RENAME, NESTEDTABLES, EXECUTE, BATCH, BASIC, INDEX |
PREFERRED_CACHE_OPTIONS | A string value specifies the preferred cacheOptions. | |
ENABLE_EF_ADVANCED_QUERY | Indicates if the driver directly supports advanced queries coming from Entity Framework. If not, queries will be handled client side. | YES, NO |
PSEUDO_COLUMNS | A string array indicating the available pseudo columns. | |
MERGE_ALWAYS | If the value is true, The Merge Mode is forcibly executed in Data Sync. | TRUE, FALSE |
REPLICATION_MIN_DATE_QUERY | A select query to return the replicate start datetime. | |
REPLICATION_MIN_FUNCTION | Allows a provider to specify the formula name to use for executing a server side min. | |
REPLICATION_START_DATE | Allows a provider to specify a replicate startdate. | |
REPLICATION_MAX_DATE_QUERY | A select query to return the replicate end datetime. | |
REPLICATION_MAX_FUNCTION | Allows a provider to specify the formula name to use for executing a server side max. | |
IGNORE_INTERVALS_ON_INITIAL_REPLICATE | A list of tables which will skip dividing the replicate into chunks on the initial replicate. | |
CHECKCACHE_USE_PARENTID | Indicates whether the CheckCache statement should be done against the parent key column. | TRUE, FALSE |
CREATE_SCHEMA_PROCEDURES | Indicates stored procedures that can be used for generating schema files. |
The following query retrieves the operators that can be used in the WHERE clause:
SELECT * FROM sys_sqlinfo WHERE Name='SUPPORTED_OPERATORS'
Note that individual tables may have different limitations or requirements on the WHERE clause; refer to the Data Model section for more information.
Name | Type | Description |
NAME | String | A component of SQL syntax, or a capability that can be processed on the server. |
VALUE | String | Detail on the supported SQL or SQL syntax. |
Returns information about attempted modifications.
The following query retrieves the Ids of the modified rows in a batch operation:
SELECT * FROM sys_identity
Name | Type | Description |
Id | String | The database-generated Id returned from a data modification operation. |
Batch | String | An identifier for the batch. 1 for a single operation. |
Operation | String | The result of the operation in the batch: INSERTED, UPDATED, or DELETED. |
Message | String | SUCCESS or an error message if the update in the batch failed. |
The connection string properties are the various options that can be used to establish a connection. This section provides a complete list of the options you can configure in the connection string for this provider. Click the links for further details.
Property | Description |
OrganizationId | The Id associated with the specific Zoho Books organization you wish to connect to. |
IncludeCustomFields | Set whether to include custom fields that are added to the views. |
AccountsServer | The full Account Server URL. |
RowScanDepth | The maximum number of rows to scan for the custom fields columns available in the table. |
Property | Description |
OAuthClientId | The client Id assigned when you register your application with an OAuth authorization server. |
OAuthClientSecret | The client secret assigned when you register your application with an OAuth authorization server. |
OAuthAccessToken | The access token for connecting using OAuth. |
CallbackURL | The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings. |
OAuthVerifier | The verifier code returned from the OAuth authorization URL. |
OAuthRefreshToken | The OAuth refresh token for the corresponding OAuth access token. |
OAuthExpiresIn | The lifetime in seconds of the OAuth AccessToken. |
OAuthTokenTimestamp | The Unix epoch timestamp in milliseconds when the current Access Token was created. |
Property | Description |
SSLServerCert | The certificate to be accepted from the server when connecting using TLS/SSL. |
Property | Description |
FirewallType | The protocol used by a proxy-based firewall. |
FirewallServer | The name or IP address of a proxy-based firewall. |
FirewallPort | The TCP port for a proxy-based firewall. |
FirewallUser | The user name to use to authenticate with a proxy-based firewall. |
FirewallPassword | A password used to authenticate to a proxy-based firewall. |
Property | Description |
ProxyAutoDetect | This indicates whether to use the system proxy settings or not. This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings. |
ProxyServer | The hostname or IP address of a proxy to route HTTP traffic through. |
ProxyPort | The TCP port the ProxyServer proxy is running on. |
ProxyAuthScheme | The authentication type to use to authenticate to the ProxyServer proxy. |
ProxyUser | A user name to be used to authenticate to the ProxyServer proxy. |
ProxyPassword | A password to be used to authenticate to the ProxyServer proxy. |
ProxySSLType | The SSL type to use when connecting to the ProxyServer proxy. |
ProxyExceptions | A semicolon separated list of destination hostnames or IPs that are exempt from connecting through the ProxyServer . |
Property | Description |
Logfile | A filepath which designates the name and location of the log file. |
Verbosity | The verbosity level that determines the amount of detail included in the log file. |
LogModules | Core modules to be included in the log file. |
MaxLogFileSize | A string specifying the maximum size in bytes for a log file (for example, 10 MB). |
MaxLogFileCount | A string specifying the maximum file count of log files. |
Property | Description |
Location | A path to the directory that contains the schema files defining tables, views, and stored procedures. |
BrowsableSchemas | This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC. |
Tables | This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC. |
Views | Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC. |
Property | Description |
AutoCache | Automatically caches the results of SELECT queries into a cache database specified by either CacheLocation or both of CacheConnection and CacheProvider . |
CacheLocation | Specifies the path to the cache when caching to a file. |
CacheTolerance | The tolerance for stale data in the cache specified in seconds when using AutoCache . |
Offline | Use offline mode to get the data from the cache instead of the live source. |
CacheMetadata | This property determines whether or not to cache the table metadata to a file store. |
Property | Description |
MaxRows | Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time. |
Other | These hidden properties are used only in specific use cases. |
Pagesize | The maximum number of results to return per page from Zoho Books. |
PseudoColumns | This property indicates whether or not to include pseudo columns as columns to the table. |
RTK | The runtime key used for licensing. |
Timeout | The value in seconds until the timeout error is thrown, canceling the operation. |
UserDefinedViews | A filepath pointing to the JSON configuration file containing your custom views. |
This section provides a complete list of the Connection properties you can configure in the connection string for this provider.
Property | Description |
OrganizationId | The Id associated with the specific Zoho Books organization you wish to connect to. |
IncludeCustomFields | Set whether to include custom fields that are added to the views. |
AccountsServer | The full Account Server URL. |
RowScanDepth | The maximum number of rows to scan for the custom fields columns available in the table. |
The Id associated with the specific Zoho Books organization you wish to connect to.
string
""
In Zoho Books, your business is termed as an organization. If you have multiple businesses, you simply set each of those up as an individual organization. Each organization is an independent Zoho Books Organization with its own organization ID, base currency, time zone, language, contacts, reports, etc. If the value of Organization Id is not specified in the connection string, then we will make a call to get all the available organizations and will select the first organization Id as the default one.
Set whether to include custom fields that are added to the views.
bool
true
If set to FALSE, the custom fields of the tables will not be retrieved. This defaults to true.
The full Account Server URL.
string
""
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 (.com, .eu, .in, .com.au, ...).
The maximum number of rows to scan for the custom fields columns available in the table.
string
"200"
Setting a high value may decrease performance. Setting a low value may prevent the data type from being determined properly.
This section provides a complete list of the OAuth properties you can configure in the connection string for this provider.
Property | Description |
OAuthClientId | The client Id assigned when you register your application with an OAuth authorization server. |
OAuthClientSecret | The client secret assigned when you register your application with an OAuth authorization server. |
OAuthAccessToken | The access token for connecting using OAuth. |
CallbackURL | The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings. |
OAuthVerifier | The verifier code returned from the OAuth authorization URL. |
OAuthRefreshToken | The OAuth refresh token for the corresponding OAuth access token. |
OAuthExpiresIn | The lifetime in seconds of the OAuth AccessToken. |
OAuthTokenTimestamp | The Unix epoch timestamp in milliseconds when the current Access Token was created. |
The client Id assigned when you register your application with an OAuth authorization server.
string
""
As part of registering an OAuth application, you will receive the OAuthClientId value, sometimes also called a consumer key, and a client secret, the OAuthClientSecret.
The client secret assigned when you register your application with an OAuth authorization server.
string
""
As part of registering an OAuth application, you will receive the OAuthClientId, also called a consumer key. You will also receive a client secret, also called a consumer secret. Set the client secret in the OAuthClientSecret property.
The access token for connecting using OAuth.
string
""
The OAuthAccessToken property is used to connect using OAuth. The OAuthAccessToken is retrieved from the OAuth server as part of the authentication process. It has a server-dependent timeout and can be reused between requests.
The access token is used in place of your user name and password. The access token protects your credentials by keeping them on the server.
The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings.
string
""
During the authentication process, the OAuth authorization server redirects the user to this URL. This value must match the callback URL you specify in your app settings.
The verifier code returned from the OAuth authorization URL.
string
""
The verifier code returned from the OAuth authorization URL. This can be used on systems where a browser cannot be launched such as headless systems.
See to obtain the OAuthVerifier value.
Set OAuthSettingsLocation along with OAuthVerifier. When you connect, the Cloud exchanges the OAuthVerifier for the OAuth authentication tokens and saves them, encrypted, to the specified file.
Once the OAuth settings file has been generated, you can remove OAuthVerifier from the connection properties and connect with OAuthSettingsLocation set.
To automatically refresh the OAuth token values, set OAuthSettingsLocation and additionally set InitiateOAuth to REFRESH.
The OAuth refresh token for the corresponding OAuth access token.
string
""
The OAuthRefreshToken property is used to refresh the OAuthAccessToken when using OAuth authentication.
The lifetime in seconds of the OAuth AccessToken.
string
""
Pair with OAuthTokenTimestamp to determine when the AccessToken will expire.
The Unix epoch timestamp in milliseconds when the current Access Token was created.
string
""
Pair with OAuthExpiresIn to determine when the AccessToken will expire.
This section provides a complete list of the SSL properties you can configure in the connection string for this provider.
Property | Description |
SSLServerCert | The certificate to be accepted from the server when connecting using TLS/SSL. |
The certificate to be accepted from the server when connecting using TLS/SSL.
string
""
If using a TLS/SSL connection, this property can be used to specify the TLS/SSL certificate to be accepted from the server. Any other certificate that is not trusted by the machine is rejected.
This property can take the following forms:
Description | Example |
A full PEM Certificate (example shortened for brevity) | -----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE----- |
A path to a local file containing the certificate | C:\cert.cer |
The public key (example shortened for brevity) | -----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB -----END RSA PUBLIC KEY----- |
The MD5 Thumbprint (hex values can also be either space or colon separated) | ecadbdda5a1529c58a1e9e09828d70e4 |
The SHA1 Thumbprint (hex values can also be either space or colon separated) | 34a929226ae0819f2ec14b4a3d904f801cbb150d |
If not specified, any certificate trusted by the machine is accepted.
Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.
This section provides a complete list of the Firewall properties you can configure in the connection string for this provider.
Property | Description |
FirewallType | The protocol used by a proxy-based firewall. |
FirewallServer | The name or IP address of a proxy-based firewall. |
FirewallPort | The TCP port for a proxy-based firewall. |
FirewallUser | The user name to use to authenticate with a proxy-based firewall. |
FirewallPassword | A password used to authenticate to a proxy-based firewall. |
The protocol used by a proxy-based firewall.
string
"NONE"
This property specifies the protocol that the Cloud will use to tunnel traffic through the FirewallServer proxy. Note that by default, the Cloud connects to the system proxy; to disable this behavior and connect to one of the following proxy types, set ProxyAutoDetect to false.
Type | Default Port | Description |
TUNNEL | 80 | When this is set, the Cloud opens a connection to Zoho Books and traffic flows back and forth through the proxy. |
SOCKS4 | 1080 | When this is set, the Cloud sends data through the SOCKS 4 proxy specified by FirewallServer and FirewallPort and passes the FirewallUser value to the proxy, which determines if the connection request should be granted. |
SOCKS5 | 1080 | When this is set, the Cloud sends data through the SOCKS 5 proxy specified by FirewallServer and FirewallPort. If your proxy requires authentication, set FirewallUser and FirewallPassword to credentials the proxy recognizes. |
To connect to HTTP proxies, use ProxyServer and ProxyPort. To authenticate to HTTP proxies, use ProxyAuthScheme, ProxyUser, and ProxyPassword.
The name or IP address of a proxy-based firewall.
string
""
This property specifies the IP address, DNS name, or host name of a proxy allowing traversal of a firewall. The protocol is specified by FirewallType: Use FirewallServer with this property to connect through SOCKS or do tunneling. Use ProxyServer to connect to an HTTP proxy.
Note that the Cloud uses the system proxy by default. To use a different proxy, set ProxyAutoDetect to false.
The TCP port for a proxy-based firewall.
int
0
This specifies the TCP port for a proxy allowing traversal of a firewall. Use FirewallServer to specify the name or IP address. Specify the protocol with FirewallType.
The user name to use to authenticate with a proxy-based firewall.
string
""
The FirewallUser and FirewallPassword properties are used to authenticate against the proxy specified in FirewallServer and FirewallPort, following the authentication method specified in FirewallType.
A password used to authenticate to a proxy-based firewall.
string
""
This property is passed to the proxy specified by FirewallServer and FirewallPort, following the authentication method specified by FirewallType.
This section provides a complete list of the Proxy properties you can configure in the connection string for this provider.
Property | Description |
ProxyAutoDetect | This indicates whether to use the system proxy settings or not. This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings. |
ProxyServer | The hostname or IP address of a proxy to route HTTP traffic through. |
ProxyPort | The TCP port the ProxyServer proxy is running on. |
ProxyAuthScheme | The authentication type to use to authenticate to the ProxyServer proxy. |
ProxyUser | A user name to be used to authenticate to the ProxyServer proxy. |
ProxyPassword | A password to be used to authenticate to the ProxyServer proxy. |
ProxySSLType | The SSL type to use when connecting to the ProxyServer proxy. |
ProxyExceptions | A semicolon separated list of destination hostnames or IPs that are exempt from connecting through the ProxyServer . |
This indicates whether to use the system proxy settings or not. This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings.
bool
true
This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings.
To connect to an HTTP proxy, see ProxyServer. For other proxies, such as SOCKS or tunneling, see FirewallType.
The hostname or IP address of a proxy to route HTTP traffic through.
string
""
The hostname or IP address of a proxy to route HTTP traffic through. The Cloud can use the HTTP, Windows (NTLM), or Kerberos authentication types to authenticate to an HTTP proxy.
If you need to connect through a SOCKS proxy or tunnel the connection, see FirewallType.
By default, the Cloud uses the system proxy. If you need to use another proxy, set ProxyAutoDetect to false.
The TCP port the ProxyServer proxy is running on.
int
80
The port the HTTP proxy is running on that you want to redirect HTTP traffic through. Specify the HTTP proxy in ProxyServer. For other proxy types, see FirewallType.
The authentication type to use to authenticate to the ProxyServer proxy.
string
"BASIC"
This value specifies the authentication type to use to authenticate to the HTTP proxy specified by ProxyServer and ProxyPort.
Note that the Cloud will use the system proxy settings by default, without further configuration needed; if you want to connect to another proxy, you will need to set ProxyAutoDetect to false, in addition to ProxyServer and ProxyPort. To authenticate, set ProxyAuthScheme and set ProxyUser and ProxyPassword, if needed.
The authentication type can be one of the following:
If you need to use another authentication type, such as SOCKS 5 authentication, see FirewallType.
A user name to be used to authenticate to the ProxyServer proxy.
string
""
The ProxyUser and ProxyPassword options are used to connect and authenticate against the HTTP proxy specified in ProxyServer.
You can select one of the available authentication types in ProxyAuthScheme. If you are using HTTP authentication, set this to the user name of a user recognized by the HTTP proxy. If you are using Windows or Kerberos authentication, set this property to a user name in one of the following formats:
user@domain domain\user
A password to be used to authenticate to the ProxyServer proxy.
string
""
This property is used to authenticate to an HTTP proxy server that supports NTLM (Windows), Kerberos, or HTTP authentication. To specify the HTTP proxy, you can set ProxyServer and ProxyPort. To specify the authentication type, set ProxyAuthScheme.
If you are using HTTP authentication, additionally set ProxyUser and ProxyPassword to HTTP proxy.
If you are using NTLM authentication, set ProxyUser and ProxyPassword to your Windows password. You may also need these to complete Kerberos authentication.
For SOCKS 5 authentication or tunneling, see FirewallType.
By default, the Cloud uses the system proxy. If you want to connect to another proxy, set ProxyAutoDetect to false.
The SSL type to use when connecting to the ProxyServer proxy.
string
"AUTO"
This property determines when to use SSL for the connection to an HTTP proxy specified by ProxyServer. This value can be AUTO, ALWAYS, NEVER, or TUNNEL. The applicable values are the following:
AUTO | Default setting. If the URL is an HTTPS URL, the Cloud will use the TUNNEL option. If the URL is an HTTP URL, the component will use the NEVER option. |
ALWAYS | The connection is always SSL enabled. |
NEVER | The connection is not SSL enabled. |
TUNNEL | The connection is through a tunneling proxy. The proxy server opens a connection to the remote host and traffic flows back and forth through the proxy. |
A semicolon separated list of destination hostnames or IPs that are exempt from connecting through the ProxyServer .
string
""
The ProxyServer is used for all addresses, except for addresses defined in this property. Use semicolons to separate entries.
Note that the Cloud uses the system proxy settings by default, without further configuration needed; if you want to explicitly configure proxy exceptions for this connection, you need to set ProxyAutoDetect = false, and configure ProxyServer and ProxyPort. To authenticate, set ProxyAuthScheme and set ProxyUser and ProxyPassword, if needed.
This section provides a complete list of the Logging properties you can configure in the connection string for this provider.
Property | Description |
Logfile | A filepath which designates the name and location of the log file. |
Verbosity | The verbosity level that determines the amount of detail included in the log file. |
LogModules | Core modules to be included in the log file. |
MaxLogFileSize | A string specifying the maximum size in bytes for a log file (for example, 10 MB). |
MaxLogFileCount | A string specifying the maximum file count of log files. |
A filepath which designates the name and location of the log file.
string
""
Once this property is set, the Cloud will populate the log file as it carries out various tasks, such as when authentication is performed or queries are executed. If the specified file doesn't already exist, it will be created.
Connection strings and version information are also logged, though connection properties containing sensitive information are masked automatically.
If a relative filepath is supplied, the location of the log file will be resolved based on the path found in the Location connection property.
For more control over what is written to the log file, you can adjust the Verbosity property.
Log contents are categorized into several modules. You can show/hide individual modules using the LogModules property.
To edit the maximum size of a single logfile before a new one is created, see MaxLogFileSize.
If you would like to place a cap on the number of logfiles generated, use MaxLogFileCount.
The verbosity level that determines the amount of detail included in the log file.
string
"1"
The verbosity level determines the amount of detail that the Cloud reports to the Logfile. Verbosity levels from 1 to 5 are supported. These are detailed in the Logging page.
Core modules to be included in the log file.
string
""
Only the modules specified (separated by ';') will be included in the log file. By default all modules are included.
See the Logging page for an overview.
A string specifying the maximum size in bytes for a log file (for example, 10 MB).
string
"100MB"
When the limit is hit, a new log is created in the same folder with the date and time appended to the end. The default limit is 100 MB. Values lower than 100 kB will use 100 kB as the value instead.
Adjust the maximum number of logfiles generated with MaxLogFileCount.
A string specifying the maximum file count of log files.
int
-1
When the limit is hit, a new log is created in the same folder with the date and time appended to the end and the oldest log file will be deleted.
The minimum supported value is 2. A value of 0 or a negative value indicates no limit on the count.
Adjust the maximum size of the logfiles generated with MaxLogFileSize.
This section provides a complete list of the Schema properties you can configure in the connection string for this provider.
Property | Description |
Location | A path to the directory that contains the schema files defining tables, views, and stored procedures. |
BrowsableSchemas | This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC. |
Tables | This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC. |
Views | Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC. |
A path to the directory that contains the schema files defining tables, views, and stored procedures.
string
"%APPDATA%\\CData\\ZohoBooks Data Provider\\Schema"
The path to a directory which contains the schema files for the Cloud (.rsd files for tables and views, .rsb files for stored procedures). The folder location can be a relative path from the location of the executable. The Location property is only needed if you want to customize definitions (for example, change a column name, ignore a column, and so on) or extend the data model with new tables, views, or stored procedures.
If left unspecified, the default location is "%APPDATA%\\CData\\ZohoBooks Data Provider\\Schema" with %APPDATA% being set to the user's configuration directory:
This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.
string
""
Listing the schemas from databases can be expensive. Providing a list of schemas in the connection string improves the performance.
This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC.
string
""
Listing the tables from some databases can be expensive. Providing a list of tables in the connection string improves the performance of the Cloud.
This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.
Specify the tables you want in a comma-separated list. Each table should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Tables=TableA,[TableB/WithSlash],WithCatalog.WithSchema.`TableC With Space`.
Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.
Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.
string
""
Listing the views from some databases can be expensive. Providing a list of views in the connection string improves the performance of the Cloud.
This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.
Specify the views you want in a comma-separated list. Each view should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Views=ViewA,[ViewB/WithSlash],WithCatalog.WithSchema.`ViewC With Space`.
Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.
This section provides a complete list of the Caching properties you can configure in the connection string for this provider.
Property | Description |
AutoCache | Automatically caches the results of SELECT queries into a cache database specified by either CacheLocation or both of CacheConnection and CacheProvider . |
CacheLocation | Specifies the path to the cache when caching to a file. |
CacheTolerance | The tolerance for stale data in the cache specified in seconds when using AutoCache . |
Offline | Use offline mode to get the data from the cache instead of the live source. |
CacheMetadata | This property determines whether or not to cache the table metadata to a file store. |
Automatically caches the results of SELECT queries into a cache database specified by either CacheLocation or both of CacheConnection and CacheProvider .
bool
false
When AutoCache = true, the Cloud automatically maintains a cache of your table's data in the database of your choice.
When AutoCache = true, the Cloud caches to a simple, file-based cache. You can configure its location or cache to a different database with the following properties:
Specifies the path to the cache when caching to a file.
string
"%APPDATA%\\CData\\ZohoBooks Data Provider"
The CacheLocation is a simple, file-based cache.
If left unspecified, the default location is "%APPDATA%\\CData\\ZohoBooks Data Provider" with %APPDATA% being set to the user's configuration directory:
The tolerance for stale data in the cache specified in seconds when using AutoCache .
int
600
The tolerance for stale data in the cache specified in seconds. This only applies when AutoCache is used. The Cloud checks with the data source for newer records after the tolerance interval has expired. Otherwise, it returns the data directly from the cache.
Use offline mode to get the data from the cache instead of the live source.
bool
false
When Offline = true, all queries execute against the cache as opposed to the live data source. In this mode, certain queries like INSERT, UPDATE, DELETE, and CACHE are not allowed.
This property determines whether or not to cache the table metadata to a file store.
bool
false
As you execute queries with this property set, table metadata in the Zoho Books catalog are cached to the file store specified by CacheLocation if set or the user's home directory otherwise. A table's metadata will be retrieved only once, when the table is queried for the first time.
The Cloud automatically persists metadata in memory for up to two hours when you first discover the metadata for a table or view and therefore, CacheMetadata is generally not required. CacheMetadata becomes useful when metadata operations are expensive such as when you are working with large amounts of metadata or when you have many short-lived connections.
This section provides a complete list of the Miscellaneous properties you can configure in the connection string for this provider.
Property | Description |
MaxRows | Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time. |
Other | These hidden properties are used only in specific use cases. |
Pagesize | The maximum number of results to return per page from Zoho Books. |
PseudoColumns | This property indicates whether or not to include pseudo columns as columns to the table. |
RTK | The runtime key used for licensing. |
Timeout | The value in seconds until the timeout error is thrown, canceling the operation. |
UserDefinedViews | A filepath pointing to the JSON configuration file containing your custom views. |
Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.
int
-1
Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.
These hidden properties are used only in specific use cases.
string
""
The properties listed below are available for specific use cases. Normal driver use cases and functionality should not require these properties.
Specify multiple properties in a semicolon-separated list.
DefaultColumnSize | Sets the default length of string fields when the data source does not provide column length in the metadata. The default value is 2000. |
ConvertDateTimeToGMT | Determines whether to convert date-time values to GMT, instead of the local time of the machine. |
RecordToFile=filename | Records the underlying socket data transfer to the specified file. |
The maximum number of results to return per page from Zoho Books.
int
200
The Pagesize property affects the maximum number of results to return per page from Zoho Books. Setting a higher value may result in better performance at the cost of additional memory allocated per page consumed.
This property indicates whether or not to include pseudo columns as columns to the table.
string
""
This setting is particularly helpful in Entity Framework, which does not allow you to set a value for a pseudo column unless it is a table column. The value of this connection setting is of the format "Table1=Column1, Table1=Column2, Table2=Column3". You can use the "*" character to include all tables and all columns; for example, "*=*".
The runtime key used for licensing.
string
""
The RTK property may be used to license a build.
The value in seconds until the timeout error is thrown, canceling the operation.
int
60
If Timeout = 0, operations do not time out. The operations run until they complete successfully or until they encounter an error condition.
If Timeout expires and the operation is not yet complete, the Cloud throws an exception.
A filepath pointing to the JSON configuration file containing your custom views.
string
""
User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The Cloud 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 Cloud.
This User Defined View configuration file is formatted as follows:
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"