Zoho Books Connection
This documentation is based on version 21.0.8383 of the connector.
This documentation is based on version 21.0.8383 of the connector.
The connector leverages the Zoho Books API to enable bidirectional access to Zoho Books.
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. After setting the following, you are ready to connect:
When you connect the connector opens the OAuth endpoint in your default browser. Log in and grant permissions to the application. The connector 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 connector 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 connector on another machine, authenticate, and then transfer the resulting OAuth values.
On a second machine, install the connector 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:
This section details a selection of advanced features of the Zoho Books connector.
The connector allows you to define virtual tables, called user defined views, whose contents are decided by a pre-configured query. These views are useful when you cannot directly control queries being issued to the drivers. See User Defined Views for an overview of creating and configuring custom views.
Use SSL Configuration to adjust how connector handles TLS/SSL certificate negotiations. You can choose from various certificate formats; see the SSLServerCert property under "Connection String Options" for more information.
To configure the connector using Private Agent proxy settings, select the Use Proxy Settings checkbox on the connection configuration screen.
The Jitterbit Connector for Zoho Books allows you to define a virtual table whose contents are decided by a pre-configured query. These are called User Defined Views, which are useful in situations where you cannot directly control the query being issued to the driver, e.g. when using the driver from Jitterbit. The User Defined Views can be used to define predicates that are always applied. If you specify additional predicates in the query to the view, they are combined with the query already defined as part of the view.
There are two ways to create user defined views:User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The connector automatically detects the views specified in this file.
You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the connector.
This User Defined View configuration file is formatted as follows: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 connector attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.
To specify another certificate, see the SSLServerCert property for the available formats to do so.
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.
NOTE: Stored procedures are not currently supported. See the above note for details.
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 connector offloads as much of the SELECT statement processing as possible to Zoho Books and then processes the rest of the query in memory. API limitations and requirements are also documented in this section.
See SupportEnhancedSQL for more information on how the connector circumvents API limitations with in-memory client-side processing.
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. To find out more about tables and stored procedures, please navigate to their corresponding entries in this help document.
Name | Description |
BankAccounts | Retrieves list of all bank and credit card accounts for your organization. |
BankMatchingTransactions | Retrieves the list of transactions which includes invoices/bills/credit-notes. |
BankRuleCriterions | Get criterions of a specific bank rule. |
BankRules | Fetch all the rules or a specific rule created for a specified bank or credit card account ID. |
BankTransactionLineItems | Get details of bank transaction line items. |
BankTransactions | Get all the transaction details involved in an account. |
BaseCurrencyAdjustmentAccounts | Retrieves lists of base currency adjustment accounts. |
BaseCurrencyAdjustments | Retrieves list of base currency adjustment. |
BillDetails | Get the details of a bill. |
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. |
ChartOfAccounts | Retrieves list of all chart of accounts. |
ChartOfAccountTransactions | Retrieves list of all involved transactions for the given account. |
ContactAddresses | Get addresses of a contact including its Shipping Address, Billing Address. |
ContactDetails | Get details of a contact. |
Contacts | Retrieves list of all contacts. |
CreditNoteDetails | Retrieves details of an existing Credit Note. |
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. |
Currencies | Get list of currencies configured. Also, get the details of a currency. |
CurrencyExchangeRates | Retrieves list of exchange rates configured for the currency. |
CustomerContacts | Retrieves list of all contact persons. Also, get the contact person details. |
CustomerPaymentDetails | Retrieves details of an existing payment. |
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. |
EstimateDetails | Get the details of an estimate. |
EstimateLineItems | Get the details of line items for estimates. |
Estimates | Retrieves list of all estimates. |
ExpenseDetails | Get the details of an Expense. |
Expenses | Retrieves list of all the Expenses. |
InvoiceAppliedCredits | Retrieves list of credits applied for an invoice. |
InvoiceDetails | Get the details of 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. |
ItemDetails | Retrieves details of an existing item. |
Items | Retrieves list of all active items. |
JournalLineItems | Retrieves list of line items of a journal. |
Journals | Retrieves list of journals. |
OpeningBalanceAccounts | Retrieves list of accounts of opening balance. |
OpeningBalances | Get details of opening balance. |
OpeningBalanceTransactionSummaries | Get transaction summaries of opening balance. |
Organizations | Retrieves list of organizations. |
ProjectInvoices | Retrieves list of invoices created for a project. |
Projects | Retrieves list of projects. |
ProjectUsers | Retrieves list of users associated with a project. Also, get details of a user in project. |
PurchaseOrderDetails | Get the details of a purchase order. |
PurchaseOrderLineItems | Get the details of line items of purchase orders. |
PurchaseOrders | Retrieves list of all purchase orders. |
RecurringExpenseDetails | Get the details of a recurring expense. |
RecurringExpenses | Retrieves list of all the Expenses. |
RecurringInvoiceDetails | Get the details of a recurring invoice. |
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. |
RetainerInvoiceDetails | Get the details of a retainer invoice. |
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. |
SalesOrderDetails | Get the details of a sales order. |
SalesOrderLineItems | Retrieves list of line items of a sales order. |
SalesOrders | Retrieves list of all sales orders. |
Tasks | Retrieves list of tasks added to a project. Also, get the details of a task. |
Taxes | Retrieves list of simple and compound taxes. Also, get the details of a simple or compound tax. |
TimeEntries | Retrieves list of all time entries. |
Users | Retrieves list of all users in the organization. Also, get the details of a user. |
VendorCreditBills | Retrieves list of bills to which the vendor credit is applied. |
VendorCreditDetails | Get details of a vendor credit. |
VendorCreditLineItems | Retrieves list of line items from vendor credits. |
VendorCredits | Retrieves list of vendor credits. |
VendorPaymentDetails | Get details of a Vendor Payment. |
VendorPayments | Retrieves list of all the payments made to your vendor. |
Retrieves list of all bank and credit card accounts for your organization.
The connector 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 connector.
SELECT * FROM BankAccounts WHERE Status = 'All' SELECT * FROM BankAccounts WHERE AccountId = '1894343000000085314'
Name | Type | References | SupportedOperators | Description |
AccountId [KEY] | String | Id of the Bank Account. | ||
AccountCode | String | Code of the Account. | ||
AccountName | String | Name of the account. | ||
AccountType | String | Type of the account. | ||
Balance | Decimal | The unpaid amount. | ||
BankBalance | Decimal | Total balance in Bank. | ||
BankName | String | Name of the Bank. | ||
BcyBalance | Decimal | Balance of Base Currency. | ||
CanShowInZe | Boolean | Check if it can show in Zero Emission. | ||
CanShowPayPalDirectIntegBanner | Boolean | Check if it can show direct integ banner. | ||
CurrencyCode | String | Currency code of the customer's currency. | ||
CurrencyId | String |
Currencies.CurrencyId | Currency ID of the customer's currency. | |
CurrencySymbol | String | Currency symbol of the customer's currency. | ||
Description | String | Description of the Account. | ||
IsActive | Boolean | Check if bank account is active. | ||
IsDirectPayPal | Boolean | Check if bank account is direct by paypal. | ||
PricePrecision | Integer | The precision for the price. | ||
TotalUnprintedChecks | Integer | Total number of unprinted checks. | ||
UncategorizedTransactions | Integer | 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. |
Retrieves the list of transactions which includes invoices/bills/credit-notes.
The connector 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 connector. 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 connector 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 connector.
For example:
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. |
Fetch all the rules or a specific rule created for a specified bank or credit card account ID.
The connector 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 connector.
SELECT * FROM BankRules WHERE RuleAccountId = '1894553000000085382' AND RuleId = '1894553000000085386'
Name | Type | References | SupportedOperators | Description |
RuleId [KEY] | String | Id of the rule. | ||
RuleName | String | Name of the rule. | ||
RuleOrder | Integer | Order of rule. | ||
ApplyTo | String | To whom can rule be applied. | ||
CriteriaType | String | Type of criteria0. | ||
RecordAs | String | Entity as which it should be recorded. | ||
RuleAccountId | String |
BankAccounts.AccountId | ID of the Bank Account. | |
AccountName | String | Name of the account. | ||
TaxId | String |
Taxes.TaxId | Id of a tax. | |
CustomerId | String |
Contacts.ContactId | ID of the customer or vendor. | |
CustomerName | String | Name of the customer or vendor. | ||
ReferenceNumber | String | Reference number of a bank rule. | ||
PaymentMode | String | Mode through which payment is made. | ||
TaxAuthorityId | String |
Taxes.TaxAuthorityId | ID of a tax authority. | |
TaxAuthorityName | String | Name of a tax authority. | ||
TaxExemptionId | String | ID of a tax exemption. | ||
TaxExemptionCode | String | Code of a tax exemption. | ||
IsReverseCharge | Boolean | Check if it is charged reverse. | ||
GstTreatment | String | Choose whether the bank rule is GST registered/unregistered/consumer/overseas. . | ||
TaxTreatment | String | VAT treatment for the Bank Rule. | ||
GstNo | String | GST Number. | ||
HsnOrSac | String | HSN Code. | ||
DestinationOfSupply | String | Place where the goods/services are supplied to. |
Get details of bank transaction line items.
The connector 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 connector.
Note: TransactionId is required in order to query BankTransactionLineItems.
For example:
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. |
Get all the transaction details involved in an account.
The connector 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 connector.
SELECT * FROM BankTransactions WHERE Status = 'All' SELECT * FROM BankTransactions LIMIT 5
Name | Type | References | SupportedOperators | Description |
TransactionId [KEY] | String | Id of the Transaction. | ||
TransactionType | String | 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 |
BankAccounts.AccountId | Account ID for which transactions are to be listed. | |
AccountName | String | Name of the account. | ||
AccountType | String | Type of the account. | ||
Amount | Decimal | Start and end amount, to provide a range within which the transaction amount exist. | ||
CurrencyCode | String | Currency code of the customer's currency. | ||
CurrencyId | String |
Currencies.CurrencyId | Currency ID of the customer's currency. | |
CurrencySymbol | String | Currency symbol of the customer's currency. | ||
CustomerId | String |
Contacts.ContactId | ID of the customer or vendor. | |
Date | Date | Start and end date, to provide a range within which the transaction date exist. | ||
DebitOrCredit | String | Indicates if transaction is Credit or Debit. | ||
Description | String | Description of the bank transactions. | ||
ExcludeDescription | String | Is the description is to be excluded. | ||
ImportedTransactionId | Long | ID of the Imported Transaction. | ||
IsOffsetaccountMatched | Boolean | Check if Offset Account is matched. | ||
IsPaidViaPrintCheck | Boolean | Check if paid via print check. | ||
IsRuleExist | Boolean | Check if rule exists. | ||
OffsetAccountName | String | Name of the offset account. | ||
Payee | String | Information about the payee. | ||
PricePrecision | Integer | The precision for the price. | ||
ReferenceNumber | String | Reference Number of the transaction. | ||
RunningBalance | String | Running balance in bank. | ||
Source | String | Source of the bank transaction. | ||
Status | String | Transaction status wise list view.
The allowed values are All, uncategorized, manually_added, matched, excluded, categorized. | ||
VendorId | String | 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 | 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 | Bank charges of bank transactions. This field will be populated with a value only when the Transaction ID is specified. | ||
BcyTotal | Decimal | Total Base Currency This field will be populated with a value only when the Transaction ID is specified. | ||
CustomerName | String | Name of the customer or vendor. This field will be populated with a value only when the Transaction ID is specified. | ||
ExchangeRate | Decimal | Exchange rate of a bank transaction. This field will be populated with a value only when the Transaction ID is specified. | ||
FromAccountId | String |
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. | |
FromAccountName | String | Account name from which bank transaction was made. This field will be populated with a value only when the Transaction ID is specified. | ||
ImportedTransactions | String | Imported bank transations. This field will be populated with a value only when the Transaction ID is specified. | ||
IsInclusiveTax | Boolean | Check if bank transaction is invlusive tax. This field will be populated with a value only when the Transaction ID is specified. | ||
IsPreGst | Boolean | Check if bank transaction is pre GST. This field will be populated with a value only when the Transaction ID is specified. | ||
PaymentMode | String | Mode through which payment is made. This field will be populated with a value only when the Transaction ID is specified. | ||
SubTotal | Decimal | Sub total of bank transactions This field will be populated with a value only when the Transaction ID is specified. | ||
Tags | String | Details of tags related to bank transactions. This field will be populated with a value only when the Transaction ID is specified. | ||
TaxAmount | Decimal | Amount of tax. This field will be populated with a value only when the Transaction ID is specified. | ||
TaxId | String |
Taxes.TaxId | Id of tax. This field will be populated with a value only when the Transaction ID is specified. | |
TaxName | String | Name of tax. This field will be populated with a value only when the Transaction ID is specified. | ||
TaxPercentage | Integer | Percentage of tax. This field will be populated with a value only when the Transaction ID is specified. | ||
ToAccountId | String |
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 | Account name the transaction was made to. This field will be populated with a value only when the Transaction ID is specified. | ||
Total | Decimal | Total of bank transactions. This field will be populated with a value only when the Transaction ID is specified. |
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. |
Retrieves lists of base currency adjustment accounts.
The connector 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 connector.
For example:
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. |
Retrieves list of base currency adjustment.
The connector 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 connector. By default, response will be showing the base currency adjustments of current month only.
SELECT * FROM BaseCurrencyAdjustments WHERE BaseCurrencyAdjustmentsFilter = 'Date.All'
Name | Type | References | SupportedOperators | Description |
BaseCurrencyAdjustmentId [KEY] | String | Id of a base currency adjustment. | ||
CurrencyCode | String | Currency code of the customer's currency. | ||
CurrencyId | String |
Currencies.CurrencyId | Currency ID of the customer's currency. | |
AdjustmentDate | Date | Date of currency adjustment. | ||
ExchangeRate | Decimal | Exchange rate of currency adjustment. | ||
GainOrLoss | Decimal | Check the amount if gain or loss. | ||
Notes | String | Notes of Base cuurrency adjustments. |
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. |
Get the details of a bill.
The connector 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 connector.
Note: BillId is required in order to query BillDetails.
For example:
SELECT * FROM BillDetails WHERE BillId = '1894553000000085259'
Name | Type | References | SupportedOperators | Description |
BillId [KEY] | String |
Bills.BillId | ID of a Bill. | |
BillNumber | String | Number of a Bill. | ||
Adjustment | Decimal | Adjustments made to the bill. | ||
AdjustmentDescription | String | Description of adjustments made to the bill. | ||
AllocatedLandedCosts | String | Allocated landed costs of bill. | ||
ApproverId | String |
Users.UserId | ID of an approver. | |
AttachmentName | String | Name of an attachment. | ||
Balance | Decimal | Balance of bill. | ||
BillingAddress | String | Billing address of a bill. | ||
BillingAddressAttention | String | Name of a person in billing address. | ||
BillingAddressCity | String | City of a billing address. | ||
BillingAddressCountry | String | Country of a billing address. | ||
BillingAddressFax | String | Fax of a billing address. | ||
BillingAddressPhone | String | Phone of a billing address. | ||
BillingAddressState | String | State of a billing address. | ||
BillingAddressStreet2 | String | Street two of a billing address. | ||
BillingAddressZip | String | Zip of a billing address. | ||
ClientViewedTime | String | Time when client viewed. | ||
ColorCode | String | Color code. | ||
ContactCategory | String | Category if contact. | ||
CreatedById | String |
Users.UserId | Id of a user who has created bill. | |
CreatedTime | Datetime | Time at which the bill was created. | ||
CurrencyCode | String | Currency code of the customer's currency. | ||
CurrencyId | String |
Currencies.CurrencyId | Currency ID of the customer's currency. | |
CurrencySymbol | String | Symbol of the currency. | ||
CurrentSubStatus | String | Current sub status of a bill. | ||
CurrentSubStatusId | String | Current sub status ID of a bill. | ||
Date | Date | Bill date. | ||
DestinationOfSupply | String | Place where the goods/services are supplied to. | ||
Discount | String | Discount of bills. | ||
DiscountAccountId | String |
BankAccounts.AccountId | Account ID of discount. | |
DiscountAmount | Decimal | Amount of discount. | ||
DiscountAppliedOnAmount | Decimal | Discount applied on amount. | ||
DiscountSetting | String | Setting of discount. | ||
DueByDays | Integer | Number of days the bill is due by. | ||
DueDate | Date | Delivery date of the bill. | ||
DueInDays | String | Number of days the bill is due in. | ||
EntityType | String | Entity type of the bill. | ||
ExchangeRate | Decimal | Exchange rate of the currency. | ||
FiledInVatReturnId | String | VAT return ID of bill which was filed. | ||
FiledInVatReturnName | String | VAT return name of bill which was filed. | ||
FiledInVatReturnType | String | VAT return type of bill which was filed. | ||
GstNo | String | GST number. | ||
GstReturnDetailsReturnPeriod | String | Return period of GST. | ||
GstReturnDetailsStatus | String | Status of GST return details. | ||
GstTreatment | String | Choose whether the bill is GST registered/unregistered/consumer/overseas. | ||
HasNextBill | Boolean | Check if it has next bill. | ||
InvoiceConversionType | String | Type of invoice conversion. | ||
IsApprovalRequired | Boolean | Check of the approval required. | ||
IsDiscountBeforeTax | Boolean | Check if discount should be applied before tax. | ||
IsInclusiveTax | Boolean | Check if the tax is inclusive in the bill. | ||
IsItemLevelTaxCalc | Boolean | Check if the item leven tax is calculated. | ||
IsLineItemInvoiced | Boolean | Check if the line item is invoiced in the bill. | ||
IsPreGst | Boolean | Check if pre GST is applied. | ||
IsReverseChargeApplied | Boolean | Check if reverse charge is applied. | ||
IsTdsAmountInPercent | Boolean | Check if the TDS amount in percent. | ||
IsTdsApplied | Boolean | Check if TDS is applied. | ||
IsViewedByClient | Boolean | Check if bill is viewed by client. | ||
LastModifiedId | String | ID when bill was last modified. | ||
LastModifiedTime | Datetime | The time of last modification of the bill. | ||
Notes | String | Notes of the bill. | ||
OpenPurchaseordersCount | Integer | Count of open purchase order. | ||
Orientation | String | Orientation of the bill. | ||
PageHeight | String | Height of the page. | ||
PageWidth | String | Width o the page. | ||
PaymentExpectedDate | Date | Date when the payment is expected. | ||
PaymentMade | Decimal | Amount paid of this bill. | ||
PaymentTerms | Integer | Net payment term for the customer. | ||
PaymentTermsLabel | String | Label for the paymet due details. | ||
PricePrecision | Integer | The precision for the price. | ||
RecurringBillId | String | ID of a recurring bill. | ||
ReferenceBillId | String | ID of a reference bill. | ||
ReferenceId | String | Id of a reference. | ||
ReferenceNumber | String | Number of a reference. | ||
SourceOfSupply | String | Source of supply of bill. | ||
Status | String | Status of the bill. | ||
SubTotal | Decimal | Sub total of bills. | ||
SubTotalInclusiveOfTax | Decimal | Subtotal amount which are inclusive of tax. | ||
SubmittedBy | String | Detail of the user who has submitted the bill. | ||
SubmittedDate | Date | Bill submitted date. | ||
SubmitterId | String |
Users.UserId | Id of a submitter. | |
TaxAccountId | String |
BankAccounts.AccountId | Account ID of tax. | |
TaxTotal | Decimal | Total amount of tax. | ||
TaxTreatment | String | VAT treatment for the Bill. | ||
TdsAmount | Decimal | Amount of TDS. | ||
TdsPercent | String | Percent of TDS. | ||
TdsSection | String | Section of TDS. | ||
TdsTaxId | String | Tax ID of TDS. | ||
TdsTaxName | String | Tax name of TDS. | ||
TemplateId | String | ID of a template. | ||
TemplateName | String | Name of a template. | ||
TemplateType | String | Type of a template. | ||
Terms | String | Terms and Conditions apply of a bill. | ||
Total | Decimal | Total of bills. | ||
UnallocatedLandedCosts | String | Costs of unlocated land. | ||
UnusedCreditsPayableAmount | Decimal | Payable amount of unused credits. | ||
VatTreatment | String | VAT treatment for the bills. | ||
VendorCreditsApplied | Decimal | Amount of applied vendor credits. | ||
VendorId | String | ID of the vendor the bill has been made. | ||
VendorName | String | Name of the vendor the bill has been made. |
Get the details of a line items of bills.
The connector 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 connector.
Note: BillId is required in order to query BillLineItems.
For example:
SELECT * FROM BillLineItems WHERE BillId = '1894253000000085259'
Name | Type | References | SupportedOperators | Description |
BillId | String |
Bills.BillId | Id of a Bill. | |
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 | ||
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 connector 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 connector.
For example:
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 connector 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 connector.
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 connector 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 connector.
For example:
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 chart of accounts.
The connector 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 connector. You can even provide criteria to search for matching uncategorised transactions.
SELECT * FROM ChartOfAccounts WHERE AccountType = 'All' AND ShowBalance = true
Name | Type | References | SupportedOperators | Description |
ChartAccountId [KEY] | String |
BankAccounts.AccountId | Id of the Bank Account. | |
AccountName | String | Name of the account. | ||
AccountType | String | Type of the account.
The allowed values are All, Active, Inactive, Asset, Liability, Equity, Income, Expense. | ||
CanShowInZe | Boolean | Check if it can show in Zero Emission. | ||
ChildCount | String | Child count in chart of accounts. | ||
CreatedTime | Datetime | Time at which the Chart of Accounts was created. | ||
CustomFields | String | Custom Fields defined for the chart of account | ||
Depth | Integer | Depth of account. | ||
Description | String | Description of the Chart of Account. | ||
HasAttachment | Boolean | Check if the chart of account has attachment. | ||
IsActive | Boolean | Check if chart of account is active. | ||
IsChildPresent | Boolean | Check if the child is present in chart of account. | ||
IsStandaloneAccount | Boolean | Check if the account is standalone account. | ||
IsUserCreated | Boolean | Check if the account is created by the user. | ||
LastModifiedTime | Datetime | Last Modified time associated with the entity. | ||
ParentAccountName | String | Account name of parent. | ||
AccountCode | String | Code of the Account. | ||
ClosingBalance | Decimal | Closing balance of account. This field will be populated with a value only when the Chart Account ID is specified. | ||
IsInvolvedInTransaction | Boolean | Check if this account is involved in the transaction. | ||
IsSystemAccount | Boolean | Check if it is a system account. | ||
IsDebit | Boolean | Check if this account is debit. This field will be populated with a value only when the Chart Account ID is specified. | ||
ParentAccountId | String |
BankAccounts.AccountId | Id of a Parent account. |
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. |
Retrieves list of all involved transactions for the given account.
The connector 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 connector. 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 | Integer | 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 connector 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 connector.
For example:
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. |
Get details of a contact.
The connector 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 connector.
Note: ContactId is required in order to query ContactDetails.
For example:
SELECT * FROM ContactDetails WHERE ContactId = '1894952000000071009'
Name | Type | References | SupportedOperators | Description |
ContactId [KEY] | String |
Contacts.ContactId | Id of a contact. | |
AchSupported | Boolean | Check if ACH is supported. | ||
AssociatedWithSquare | Boolean | Check if the contact is associated with square. | ||
BillingAddress | String | Billing address of a contact. | ||
BillingAddressId | String |
ContactAddresses.AddressId | Id of a billing address. | |
BillingAddressAttention | String | Name of a person in billing address. | ||
BillingAddressCity | String | City of a billing address. | ||
BillingAddressCountry | String | Country of a billing address. | ||
BillingAddressFax | String | Fax of a billing address. | ||
BillingAddressPhone | String | Phone number of a billing address. | ||
BillingAddressState | String | State of a billing address. | ||
BillingAddressStateCode | String | State code of a billing address. | ||
BillingAddressStreet2 | String | Street two of a billing address. | ||
BillingAddressZip | String | ZIP code of a billing address. | ||
CanShowCustomerOb | Boolean | Check if contact can show customer ob. | ||
CanShowVendorOb | Boolean | Check if contact can show vendor ob. | ||
CompanyName | String | Name of the company. | ||
ContactCategory | String | Category of this contact. | ||
ContactName | String | Display Name of the contact. Max-length [200]. | ||
ContactSalutation | String | Salutation of a contact. | ||
CreatedTime | Datetime | Time at which the contact was created. | ||
CreditLimitExceededAmount | Decimal | Amount if the credit limit exceeded. | ||
CurrencyCode | String | Currency code of the customer's currency. | ||
CurrencySymbol | String | Currency symbol of the customer's currency. | ||
CustomerSubType | String | Sub type of a customer. | ||
DefaultTemplatesBillTemplateId | String | ID of a bill template in default template. | ||
DefaultTemplatesBillTemplateName | String | Name of a bill template in default template. | ||
DefaultTemplatesCreditnoteEmailTemplateId | String | ID of a credit note email template in default template. | ||
DefaultTemplatesCreditnoteEmailTemplateName | String | Name of a credit note email template in default template. | ||
DefaultTemplatesCreditnoteTemplateId | String | ID of a credit note template in default template. | ||
DefaultTemplatesCreditnoteTemplateName | String | Name of a credit note template in default template. | ||
DefaultTemplatesEstimateEmailTemplateId | String | ID of a estimate email template in default template. | ||
DefaultTemplatesEstimateEmailTemplateName | String | Name of a estimate email template in default template. | ||
DefaultTemplatesEstimateTemplateId | String | ID of a estimate template in default template. | ||
DefaultTemplatesEstimateTemplateName | String | Name of a estimate template in default template. | ||
DefaultTemplatesInvoiceEmailTemplateId | String | ID of a invoice email template in default template. | ||
DefaultTemplatesInvoiceEmailTemplateName | String | Name of a invoice email template in default template. | ||
DefaultTemplatesInvoiceTemplateId | String | ID of a invoice template in default template. | ||
DefaultTemplatesInvoiceTemplateName | String | Name of a invoice template in default template. | ||
DefaultTemplatesPaymentRemittanceEmailTemplateId | String | ID of a payment remittance template in default template. | ||
DefaultTemplatesPaymentRemittanceEmailTemplateName | String | Name of a payment remittance template in default template. | ||
DefaultTemplatesPaymentthankyouEmailTemplateId | String | ID of a payment thank you email template in default template. | ||
DefaultTemplatesPaymentthankyouEmailTemplateName | String | Name of a payment thank you email template in default template. | ||
DefaultTemplatesPaymentthankyouTemplateId | String | ID of a payment thank you template in default template. | ||
DefaultTemplatesPaymentthankyouTemplateName | String | Name of a payment thank you template in default template. | ||
DefaultTemplatesPurchaseorderEmailTemplateId | String | ID of a purchase order email template in default template. | ||
DefaultTemplatesPurchaseorderEmailTemplateName | String | Name of a purchase order email template in default template. | ||
DefaultTemplatesPurchaseorderTemplateId | String | ID of a purchase order template in default template. | ||
DefaultTemplatesPurchaseorderTemplateName | String | Name of a purchase order template in default template. | ||
DefaultTemplatesSalesorderEmailTemplateId | String | ID of a sales order email template in default template. | ||
DefaultTemplatesSalesorderEmailTemplateName | String | Name of a sales order email template in default template. | ||
DefaultTemplatesSalesorderTemplateId | String | ID of a sales order template in default template. | ||
DefaultTemplatesSalesorderTemplateName | String | Name of a sales order template in default template. | ||
String | Email ID of a contact. | |||
ExchangeRate | Decimal | Exchange rate of the currency. | ||
HasTransaction | Boolean | Check if this contact has transaction. | ||
IsClientReviewAsked | Boolean | Check if the client review is asked. | ||
IsClientReviewSettingsEnabled | Boolean | Check if the client review settings is enabled for this contact. | ||
IsSmsEnabled | Boolean | Check if SMS is enabled. | ||
LanguageCode | String | Language code used for a contact. | ||
LastModifiedTime | Datetime | The time of last modification of the contact. | ||
Mobile | String | Mobile number of a contact. | ||
Notes | String | Notes of a contact. | ||
OpeningBalanceAmount | Decimal | Opening balance amount of a contact. | ||
OpeningBalanceAmountBcy | Decimal | Base Currency of Opening balance amount of a contact. | ||
OutstandingPayableAmount | Decimal | Outstanding OB payable amount of a contact. | ||
OutstandingReceivableAmount | Decimal | Outstanding OB receivable amount of a contact. | ||
OwnerId | String | ID of the owner. | ||
OwnerName | String | Name of the owner. | ||
PaymentReminderEnabled | Boolean | Check if payment reminder is enabled. | ||
PaymentTerms | Integer | Net payment term for the customer. | ||
PaymentTermsLabel | String | Label for the paymet due details. | ||
PortalStatus | String | Status of a portal. | ||
PricePrecision | Integer | The precision for the price. | ||
PricebookId | String | ID of a price book. | ||
PricebookName | String | Name of a price book. | ||
PrimaryContactId | String | Primary ID of a contact. | ||
SalesChannel | String | Channel of sales. | ||
ShippingAddress | String | Shipment Address. | ||
ShippingAddressId | String |
ContactAddresses.AddressId | ID of a shipping address. | |
ShippingAddressAttention | String | Name of a person of shipping address. | ||
ShippingAddressCity | String | City of a shipping address. | ||
ShippingAddressCountry | String | Country of a shipping address. | ||
ShippingAddressFax | String | Fax of a shipping address. | ||
ShippingAddressPhone | String | Phone number of a shipping address. | ||
ShippingAddressState | String | State of a shipping address. | ||
ShippingAddressStateCode | String | State code of a shipping address. | ||
ShippingAddressStreet2 | String | Street two details of a shipping address. | ||
ShippingAddressZip | String | Zip code of a shipping address. | ||
Source | String | Source of the contact. | ||
Status | String | Status of the contact. | ||
String | Twitter account. | |||
UnusedCreditsPayableAmount | Decimal | Payable amount of Unused credits of a contact. | ||
UnusedCreditsPayableAmountBcy | Decimal | Base Currency Payable amount of Unused credits of a contact. | ||
UnusedCreditsReceivableAmount | Decimal | Receivable amount of Unused credits of a contact. | ||
UnusedCreditsReceivableAmountBcy | Decimal | Base Currency Receivable amount of Unused credits of a contact. | ||
UnusedRetainerPayments | Decimal | Payment of the contact which is unused. | ||
Website | String | Link of a website. |
Retrieves list of all contacts.
The connector 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 connector.
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 an existing Credit Note.
The connector 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 connector.
Note: CreditnoteId is required in order to query CreditNoteDetails.
For example:
SELECT * FROM CreditNoteDetails WHERE CreditnoteId = '1895452000000083136'
Name | Type | References | SupportedOperators | Description |
CreditnoteId [KEY] | String |
CreditNotes.CreditnoteId | ID of a credit note. | |
CreditnoteNumber | String | Number of a credit note. | ||
Adjustment | Decimal | Adjustments made to the credit note. | ||
AdjustmentDescription | String | Description of adjustments made to the credit note. | ||
ApproverId | String |
Users.UserId | ID of an approver. | |
Balance | Decimal | The unpaid amount. | ||
BillingAddress | String | Billing address of a credit note. | ||
BillingAddressAttention | String | Name of a person in billing address. | ||
BillingAddressCity | String | City of a billing address. | ||
BillingAddressCountry | String | Country of a billing address. | ||
BillingAddressFax | String | Fax of a billing address. | ||
BillingAddressPhone | String | Phone number of a billing address. | ||
BillingAddressState | String | State of a billing address. | ||
BillingAddressStreet2 | String | Street two of a billing address. | ||
BillingAddressZip | String | ZIP code of a billing address. | ||
ColorCode | String | Color code of a credit note. | ||
ContactCategory | String | Category of a contact. | ||
CreatedById | String |
Users.UserId | Id of a user who has created credit note. | |
CreatedTime | Datetime | Time at which the credit note was created. | ||
CurrencyCode | String | Currency code of the customer's currency. | ||
CurrencyId | String |
Currencies.CurrencyId | Currency ID of the customer's currency. | |
CurrencySymbol | String | Currency symbol of the customer's currency. | ||
CurrentSubStatus | String | Current sub status of a credit note. | ||
CurrentSubStatusId | String | Current sub status ID 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. | ||
Discount | String | Discount given to specific item in credit note. | ||
DiscountAmount | Decimal | Amount of discount. | ||
DiscountAppliedOnAmount | Decimal | Discount applied on amount. | ||
DiscountType | String | Type of discount. | ||
ExchangeRate | Decimal | Exchange rate of the currency. | ||
FiledInVatReturnId | String | VAT return ID of credit note which was filed. | ||
FiledInVatReturnName | String | VAT return name of credit note which was filed. | ||
FiledInVatReturnType | String | VAT return type of credit note which was filed. | ||
GstNo | String | GST number used for credit note. | ||
GstReason | String | Reason for GST given for credit note. | ||
GstReturnDetailsReturnPeriod | String | Period for GST return. | ||
GstReturnDetailsStatus | String | Status of GST return details. | ||
GstTreatment | String | Choose whether the credit note is GST registered/unregistered/consumer/overseas. . | ||
HasNextCreditnote | Boolean | Check if it has credit note. | ||
InvoiceId | String |
Invoices.InvoiceId | Invoice ID for credit note. | |
InvoiceNumber | String | Invoice number for credit note. | ||
IsDiscountBeforeTax | Boolean | Check if the discount is applied before tax in credit note. | ||
IsEmailed | Boolean | Check if the credit note is emailed. | ||
IsEwayBillRequired | Boolean | Check if eway bill is required for credit note. | ||
IsInclusiveTax | Boolean | Check if the credit note is inclusive tax. | ||
IsPreGst | Boolean | Check if pre GST is applied. | ||
IsTaxable | Boolean | Check if this credit note is taxable. | ||
LastModifiedById | String |
Users.UserId | Id of the user last modified. | |
LastModifiedTime | Datetime | The time of last modification of the credit note. | ||
Notes | String | Notes for this credit note. | ||
Orientation | String | Orientation of a page. | ||
PageHeight | String | Height of a page. | ||
PageWidth | String | Width of a page. | ||
PlaceOfSupply | String | The place of supply is where a transaction is considered to have occurred for VAT purposes. | ||
PricePrecision | Integer | The precision for the price. | ||
ReasonForCreditnote | String | Any specific reason for taking credit note. | ||
ReferenceNumber | String | Reference number of credit note. | ||
ReverseChargeTaxTotal | Decimal | Total amount to pay the liability of tax. | ||
RoundoffValue | Decimal | Rounding off the values to precise number. | ||
SalespersonId | String | ID of a sales person. | ||
SalespersonName | String | Name of a sales person. | ||
ShippingAddress | String | Shipment Address. | ||
ShippingAddressAttention | String | Name of a person of shipping address. | ||
ShippingAddressCity | String | City of a shipping address. | ||
ShippingAddressCountry | String | Country of a shipping address. | ||
ShippingAddressFax | String | Fax of a shipping address. | ||
ShippingAddressPhone | String | Phone number of a shipping address. | ||
ShippingAddressState | String | State of a shipping address. | ||
ShippingAddressStreet2 | String | Street two details of a shipping address. | ||
ShippingAddressZip | String | Zip code of a shipping address. | ||
ShippingCharge | Decimal | Shipping charge of credit note. | ||
Status | String | Status of the credit note. | ||
SubTotal | Decimal | Sub total of credit notes. | ||
SubTotalInclusiveOfTax | Decimal | Subtotal amount which are inclusive of tax. | ||
SubmittedBy | String | Detail of the user who has submitted the credit note. | ||
SubmittedDate | Date | Date when credit note was submitted. | ||
SubmitterId | String |
Users.UserId | Id of a submitter of credit note. | |
TaxSpecification | String | Working of tax when specifying special tax options and tax methods for earnings codes. | ||
TaxTotal | Decimal | Total amount of Tax. | ||
TaxTreatment | String | VAT treatment for the Credit Note. | ||
TemplateId | String | ID of a template. | ||
TemplateName | String | Name of a tempalte. | ||
TemplateType | String | Type of a template. | ||
Terms | String | Terms and Conditions apply of a credit note. | ||
Total | Decimal | Total of credit notes. | ||
TotalCreditsUsed | Decimal | Total credits used for credit note. | ||
TotalRefundedAmount | Decimal | Total amount refunded for a credit note. | ||
TransactionRoundingType | String | Type of round off used for transaction. | ||
VatTreatment | String | VAT treatment for the credit note. |
Retrieves details of invoices from an existing Credit Note.
The connector 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 connector.
Note: CreditnoteId is required in order to query CreditNoteInvoices.
For example:
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 connector 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 connector.
Note: CreditnoteId is required in order to query CreditNoteLineItems.
For example:
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 connector 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 connector.
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. |
Get list of currencies configured. Also, get the details of a currency.
The connector 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 connector.
SELECT * FROM Currencies WHERE CurrencyId = '1894553000000000099' SELECT * FROM Currencies WHERE CurrencyFilter = 'Currencies.ExcludeBaseCurrency'
Name | Type | References | SupportedOperators | Description |
CurrencyId [KEY] | String | Currency ID of the customer's currency. | ||
CurrencyName | String | Name of a currency. | ||
CurrencyCode | String | Code of a currency. | ||
CurrencyFormat | String | Format of a currency. | ||
CurrencySymbol | String | Symbol of a currency. | ||
EffectiveDate | Date | Date which the exchange rate is applicable for the currency. | ||
ExchangeRate | Decimal | Exchange rate of the currency. | ||
IsBaseCurrency | Boolean | Check of it is a base currency. | ||
PricePrecision | Integer | 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. |
Retrieves list of exchange rates configured for the currency.
The connector 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 connector.
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 contact persons. Also, get the contact person details.
The connector 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 connector.
SELECT * FROM CustomerContacts WHERE ContactId = '1864553000000072009' AND CustomerContactId = '1896253000000071011'
Name | Type | References | SupportedOperators | Description |
CustomerContactId [KEY] | String | ID of a contact person. | ||
ContactId | String |
Contacts.ContactId | Id of a contact. | |
ContactName | String | Display Name of the contact. Max-length [200]. | ||
CreatedTime | Datetime | Time at which the contact person was created. | ||
CurrencyCode | String | Currency code used for this contact person. | ||
Department | String | Department on which a person belongs. . | ||
Designation | String | Designation of a person. | ||
String | Email ID of contact person. | |||
Fax | String | Fax ID of contact person. | ||
FirstName | String | First name of the contact person. | ||
IsPrimaryContact | Boolean | Check if it is a primary contact. | ||
LastName | String | Last name of contact person. | ||
Mobile | String | Mobile number of a contact person. | ||
Phone | String | Phone number of a contact person. | ||
Salutation | String | Salutation of a contact person. | ||
Skype | String | Skype ID of contact person. |
Retrieves details of an existing payment.
The connector 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 connector.
Note: PaymentId is required in order to query CustomerPaymentDetails.
For example:
SELECT * FROM CustomerPaymentDetails WHERE PaymentId = '1894553000000083001'
Name | Type | References | SupportedOperators | Description |
PaymentId [KEY] | String |
CustomerPayments.PaymentId | ID of a payment. | |
AccountId | String |
BankAccounts.AccountId | ID of the Bank Account. | |
AccountName | String | Name of the account. | ||
AccountType | String | Type of the account. | ||
Amount | Decimal | Amount of the customer payments. | ||
AttachmentName | String | Name of the attachment. | ||
BankCharges | Decimal | Charges of bank. | ||
CanSendInMail | Boolean | Check if the customer payment can be send in mail. | ||
CanSendPaymentSms | Boolean | Check if the customer payment can send payment SMS. | ||
CardType | String | Type of card. | ||
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 a customer payment. | ||
Description | String | Description of the customer payment. | ||
DiscountAmount | Decimal | Total discount amount applied in customer payment. | ||
ExchangeRate | Decimal | Exchange rate of the currency. | ||
IsClientReviewSettingsEnabled | Boolean | Check if the client review settings is enabled or not. | ||
IsPaymentDetailsRequired | Boolean | Check if the payment details is required. | ||
IsPreGst | Boolean | Check if pre GST is applied. | ||
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. | ||
PaymentMode | String | Mode through which payment is made. | ||
PaymentNumber | Integer | Number through which payment is made. | ||
PaymentNumberPrefix | String | Prefix of the payment number. | ||
PaymentNumberSuffix | String | Suffix of the payment number. | ||
ProductDescription | String | Description of the product. | ||
ReferenceNumber | String | Reference number of a customer payment. | ||
SettlementStatus | String | Status of the settlement. | ||
TaxAccountId | String |
BankAccounts.AccountId | Account ID of tax. | |
TaxAccountName | String | Account name of tax. | ||
TaxAmountWithheld | Decimal | Amount withheld for tax. | ||
TemplateId | String | ID of a template. | ||
TemplateName | String | Name of a template. | ||
TemplateType | String | Type of a template. | ||
UnusedAmount | Decimal | Unused amount of the customer payment. |
Retrieves list of all the payments made by your customer.
The connector 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 connector.
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 connector 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 connector.
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 connector 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 connector.
For example:
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 an estimate.
The connector 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 connector.
Note: EstimateId is required in order to query EstimateDetails.
For example:
SELECT * FROM EstimateDetails WHERE EstimateId = '1894553000000077244'
Name | Type | References | SupportedOperators | Description |
EstimateId [KEY] | String |
Estimates.EstimateId | Id of an estimate. | |
Adjustment | Decimal | Adjustments made to the estimate. | ||
AdjustmentDescription | String | Description of adjustments made to the estimate. | ||
AllowPartialPayments | Boolean | Check if estimate allows partial payments. | ||
ApproverId | String |
Users.UserId | Id of an approver. | |
AttachmentName | String | Name of the attachment. | ||
BcyAdjustment | Decimal | Adjustment made for base currency. | ||
BcyDiscountTotal | Decimal | Total amount get on discount for base currency. | ||
BcyShippingCharge | Decimal | Shipping charge applied for base currency. | ||
BcySubTotal | Decimal | Sub total of base currency. | ||
BcyTaxTotal | Decimal | Total tax applied for the base currency. | ||
BcyTotal | Decimal | Total Base Currency. | ||
BillingAddress | String | Billing address of a estimate. | ||
BillingAddressAttention | String | Name of a person in billing address. | ||
BillingAddressCity | String | City of a billing address. | ||
BillingAddressCountry | String | Country of a billing address. | ||
BillingAddressFax | String | Fax of a billing address. | ||
BillingAddressPhone | String | Phone number of a billing address. | ||
BillingAddressState | String | State of a billing address. | ||
BillingAddressStreet2 | String | Street two of a billing address. | ||
BillingAddressZip | String | ZIP code of a billing address. | ||
CanSendInMail | Boolean | Check if the estimate can be send in mail. | ||
ClientViewedTime | Datetime | Time when client viewed the estimate. | ||
ColorCode | String | Color code for estimate. | ||
ContactCategory | String | Category for contact. | ||
CreatedById | String |
Users.UserId | Id of a user who has created estimate. | |
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. | |
CurrencySymbol | String | Currency symbol of the customer's currency. | ||
CurrentSubStatus | String | Current sub status of an estimate . | ||
CurrentSubStatusId | String | Current sub status ID of an estimate . | ||
CustomerDefaultBillingAddress | String | Customer default billing address of an estimate. | ||
CustomerDefaultBillingAddressCity | String | City of a customer default billing address. | ||
CustomerDefaultBillingAddressCountry | String | Country of a customer default billing address. | ||
CustomerDefaultBillingAddressFax | String | Fax of a customer default billing address. | ||
CustomerDefaultBillingAddressPhone | String | Phone number of a customer default billing address. | ||
CustomerDefaultBillingAddressState | String | State of a customer default billing address. | ||
CustomerDefaultBillingAddressStateCode | String | State code of a customer default billing address. | ||
CustomerDefaultBillingAddressStreet2 | String | Street two of a customer default billing address. | ||
CustomerDefaultBillingAddressZip | String | ZIP code of a customer default billing address. | ||
CustomerId | String |
Contacts.ContactId | ID of the customer or vendor. | |
CustomerName | String | Name of the customer or vendor. | ||
Date | Date | Date of an estimate. | ||
Discount | String | Discount applied for estimate. | ||
DiscountAppliedOnAmount | Decimal | Discount applied on amount for estimate. | ||
DiscountPercent | Decimal | Percent of discount applied for estimate. | ||
DiscountTotal | Decimal | Total discount applied for estimate. | ||
DiscountType | String | Type of discount applied for estimate. | ||
EstimateNumber | String | Number of estimate. | ||
EstimateUrl | String | URL of estimate. | ||
ExchangeRate | Decimal | Exchange rate of the currency. | ||
ExpiryDate | Date | Expiration date of estimate. | ||
InvoiceConversionType | String | Conversion type of an invoice in estimate. | ||
IsConvertedToOpen | Boolean | Check if the estimate is converted to open. | ||
IsDiscountBeforeTax | Boolean | Check if the discount is applied before tax. . | ||
IsInclusiveTax | Boolean | Check if the expense is inclusive tax. | ||
IsPreGst | Boolean | Check if estimate includes pre GST. | ||
IsTransactionCreated | Boolean | Check if the transaction os created for estimate. | ||
IsViewedByClient | Boolean | Check if the estimate is viewed by client. | ||
LastModifiedById | String |
Users.UserId | Id of the user last modified. | |
LastModifiedTime | Datetime | The time of last modification of the estimate. | ||
Notes | String | Notes of Estimate. | ||
Orientation | String | Orientation of page. | ||
PageHeight | String | Height of page. | ||
PageWidth | String | Width of page. | ||
PricePrecision | Integer | The precision for the price. | ||
ProjectId | String | Id of a project. | ||
ProjectName | String | Name of a project. | ||
ProjectCustomerId | String | Id of a customer. | ||
ProjectCustomerName | String | Name of a customer. | ||
ProjectDescription | String | Details about the project. | ||
ProjectStatus | String | Status of the project. | ||
ProjectBillingType | String | Type of billing. | ||
ProjectRate | Decimal | Overall rate of the project. | ||
ReferenceNumber | String | Reference number of estimates. | ||
PlaceOfSupply | String | The place of supply is where a transaction is considered to have occurred for VAT purposes. | ||
TaxSpecification | String | Working of tax when specifying special tax options and tax methods for earnings codes. | ||
VatTreatment | String | VAT treatment for the estimates. | ||
IsTaxable | Boolean | Check if estimate is taxble. | ||
GstNo | String | GST number. | ||
GstTreatment | String | Choose whether the estimate is GST registered/unregistered/consumer/overseas. . | ||
TaxTreatment | String | VAT treatment for the Estimate. | ||
ReverseChargeTaxTotal | String | Total amount of tax reverse charge. | ||
CanSendEstimateSms | String | Check if the estimate can send the estimate SMS. | ||
RetainerPercentage | String | Percentage of the retainer in estimate. | ||
AcceptRetainer | Boolean | Check if estimate can accept the retainer. | ||
RoundoffValue | Decimal | Rounding off the values to precise number. | ||
SalespersonId | String | ID of a sales person. | ||
SalespersonName | String | Name of a sales person. | ||
ShippingAddress | String | Shipment Address. | ||
ShippingAddressAttention | String | Name of a person of shipping address. | ||
ShippingAddressCity | String | City of a shipping address. | ||
ShippingAddressCountry | String | Country of a shipping address. | ||
ShippingAddressFax | String | Fax of a shipping address. | ||
ShippingAddressPhone | String | Phone number of a shipping address. | ||
ShippingAddressState | String | State of a shipping address. | ||
ShippingAddressStreet2 | String | Street two of a shipping address. | ||
ShippingAddressZip | String | Zip of a shipping address. | ||
ShippingCharge | Decimal | Shipping charge of estimates. | ||
Status | String | Status of the estimate. | ||
SubTotal | Decimal | Sub total of estimates. | ||
SubTotalExclusiveOfDiscount | Decimal | Subtotal amount which are exclusive of discount. | ||
SubTotalInclusiveOfTax | Decimal | Subtotal amount which are inclusive of tax. | ||
SubmittedBy | String | Detail of the user who has submitted the estimate. | ||
SubmittedDate | Date | Date when estimate was submitted. | ||
SubmitterId | String |
Users.UserId | ID of the submitter. | |
TaxTotal | Decimal | Total amount of tax. | ||
TemplateId | String | ID of a template. | ||
TemplateName | String | Name of a template. | ||
TemplateType | String | Type of a template. | ||
Terms | String | Terms and Conditions apply of a estimate. | ||
Total | Decimal | Total of estimates. | ||
TransactionRoundingType | String | Type of round off used for transaction. |
Get the details of line items for estimates.
The connector 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 connector.
Note: EstimateId is required in order to query EstimateLineItems.
For example:
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 connector 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 connector.
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. |
Get the details of an Expense.
The connector 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 connector.
Note: ExpenseId is required in order to query ExpenseDetails.
For example:
SELECT * FROM ExpenseDetails WHERE ExpenseId = '1894553000000077244'
Name | Type | References | SupportedOperators | Description |
ExpenseId [KEY] | String |
Expenses.ExpenseId | Id of an expense. | |
AccountId | String |
BankAccounts.AccountId | ID of the Bank Account. | |
AccountName | String | Name of the account. | ||
Amount | Decimal | Amount of the expenses. | ||
ApproverEmail | String | Email of an approver. | ||
ApproverId | String |
Users.UserId | Id of an approver. | |
ApproverName | String | Name of an approver. | ||
BcySurchargeAmount | Decimal | Surcharge amount of Base Currency. | ||
BcyTotal | Decimal | Total Base Currency. | ||
CreatedById | String |
Users.UserId | Id of a user who has created expense. | |
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 | Expense date. | ||
Description | String | Description of the expense. | ||
EmployeeEmail | String | Email ID of an employee. | ||
EmployeeId | String |
Employees.EmployeeId | ID of an employee. | |
EmployeeName | String | Name of an employee. | ||
EndReading | String | End Reading of the Odometer. | ||
ExchangeRate | Decimal | Exchange rate of the currency. | ||
ExpenseItemId | String | Item ID of an expense. | ||
ExpenseReceiptName | String | Receipt name of an expense. | ||
ExpenseReceiptType | String | Receipt type of an expense. | ||
ExpenseType | String | Type of expense. | ||
FcySurchargeAmount | Decimal | Surcharge amount of Foreign Currency. | ||
InvoiceConversionType | String | Type of invoice conversion. | ||
InvoiceId | String |
Invoices.InvoiceId | ID of an invoice. | |
InvoiceNumber | String | Number of an invoice. | ||
IsBillable | Boolean | Check if the expense is billable. | ||
IsInclusiveTax | Boolean | Check if the expense is inclusive tax. | ||
IsPersonal | Boolean | Check if the expense is personal. | ||
IsPreGst | Boolean | Check if the pre GST is applied in the expense. | ||
IsRecurringApplicable | Boolean | Check if the recurring is applicable. | ||
IsReimbursable | Boolean | Check if the expense is reimbursable. | ||
IsSurchargeApplicable | Boolean | Check if the surcharge is applicable in this expense. | ||
LastModifiedById | String |
Users.UserId | Id of the user last modified. | |
LastModifiedTime | Datetime | The time of last modification of the expense. | ||
Location | String | Location of the expense. | ||
MerchantId | String | ID of the merchant. | ||
MerchantName | String | Name of the merchant. | ||
MileageRate | Double | Mileage rate for a particular mileage expense. | ||
MileageType | String | Type of Mileage. | ||
MileageUnit | String | Unit of the distance travelled. | ||
PaidThroughAccountId | String |
BankAccounts.AccountId | Account ID from which expense amount was paid. | |
PaidThroughAccountName | String | Account name from which expense amount was paid. | ||
PaymentMode | String | Mode through which payment is made. | ||
ProjectId | String |
Projects.ProjectId | ID of the project. | |
ProjectName | String | Name of the project. | ||
ReferenceNumber | String | Reference number of expense. | ||
ReportId | String | ID of the report. | ||
ReportName | String | Name of the report. | ||
ReportNumber | String | Number of the report. | ||
ReportStatus | String | Status of the report. | ||
StartReading | String | Start Reading of the Odometer. | ||
Status | String | Status of the expense. | ||
SubTotal | Decimal | Sub total of expenses. | ||
Tags | String | Details of tags related to expenses. | ||
TaxAmount | Decimal | Amount of tax. | ||
TaxId | String |
Taxes.TaxId | Id of tax. | |
TaxName | String | Name of tax. | ||
TaxPercentage | Integer | Percentage of tax. | ||
Total | Decimal | Total of expenses. | ||
TransactionType | String | Type of the Transaction. | ||
TripId | String | ID of a trip. | ||
TripNumber | String | Number of a trip. | ||
UserEmail | String | Email ID of a User. | ||
UserId | String |
Users.UserId | ID of a user. | |
UserName | String | Name of a user. | ||
VehicleId | String | ID of a vehicle. | ||
VehicleName | String | Name of a vehicle. | ||
VendorId | String | ID of the vendor the expenses has been made. | ||
VendorName | String | Name of the vendor the expenses has been made. |
Retrieves list of all the Expenses.
The connector 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 connector.
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 connector 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 connector.
For example:
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 an invoice.
The connector 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 connector.
Note: InvoiceId is required in order to query InvoiceDetails.
For example:
SELECT * FROM InvoiceDetails WHERE InvoiceId = '1864543000000078539'
Name | Type | References | SupportedOperators | Description |
InvoiceId [KEY] | String |
Invoices.InvoiceId | Id of an invoice. | |
InvoiceNumber | String | Number of an invoice. | ||
InvoiceUrl | String | URL of an invoice. | ||
AchPaymentInitiated | Boolean | Check if the Automated Clearing House payment is initiated. | ||
AchSupported | Boolean | Check if Automated Clearing House is supported. | ||
Adjustment | Decimal | Adjustments made to the invoice. | ||
AdjustmentDescription | String | Description of adjustments made to the invoice. | ||
AllowPartialPayments | Boolean | Check if invoice can allow partial payments. | ||
ApproverId | String |
Users.UserId | ID of an approver. | |
AttachmentName | String | Name of the attachment. | ||
Balance | Decimal | The unpaid amount. | ||
BcyAdjustment | Decimal | Adjustment of base currency. | ||
BcyDiscountTotal | Decimal | Total discount applied in base currency. | ||
BcyShippingCharge | Decimal | Shipping charge applied in base currency. | ||
BcySubTotal | Decimal | Sub total of base currency. | ||
BcyTaxTotal | Decimal | Tax total of base currency. | ||
BcyTotal | Decimal | Total Base Currency. | ||
BillingAddress | String | Billing address of an invoice. | ||
BillingAddressAttention | String | Name of a person in billing address. | ||
BillingAddressCity | String | City of a billing address. | ||
BillingAddressCountry | String | Country of a billing address. | ||
BillingAddressFax | String | Fax of a billing address. | ||
BillingAddressPhone | String | Phone number of a billing address. | ||
BillingAddressState | String | State of a billing address. | ||
BillingAddressStreet2 | String | Street two of a billing address. | ||
BillingAddressZip | String | ZIP code of a billing address. | ||
CanSendInMail | Boolean | Check if invoice can be send in mail. | ||
CanSendInvoiceSms | Boolean | Check if invoice can be send in SMS. | ||
ClientViewedTime | String | Last time when client viewed the invoice. | ||
ColorCode | String | Color code of invoice. | ||
ContactCategory | String | Category of a contact. | ||
ContactPersonsDetails | String | Details of a contact person. | ||
CreatedById | String |
Users.UserId | Id of a user who has created invoice. | |
CreatedTime | Datetime | Time at which the invoice was created. | ||
CreditsApplied | Decimal | Applied credits for invoice. | ||
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. | ||
CurrentSubStatus | String | Current sub status of an invoice . | ||
CurrentSubStatusId | String | Current sub status ID of an invoice . | ||
CustomerDefaultBillingAddress | String | Customer default billing address of an invoice. | ||
CustomerDefaultBillingAddressCity | String | City of a billing address. | ||
CustomerDefaultBillingAddressCountry | String | Country of a billing address. | ||
CustomerDefaultBillingAddressFax | String | Fax of a billing address. | ||
CustomerDefaultBillingAddressPhone | String | Phone number of a billing address. | ||
CustomerDefaultBillingAddressState | String | State of a billing address. | ||
CustomerDefaultBillingAddressStateCode | String | State code of a customer default billing address. | ||
CustomerDefaultBillingAddressStreet2 | String | Street two of a customer default billing address. | ||
CustomerDefaultBillingAddressZip | String | ZIP code of a customer default billing address. | ||
CustomerId | String |
Contacts.ContactId | ID of the customer or vendor. | |
CustomerName | String | Name of the customer or vendor. | ||
Date | Date | Date of an invoice. | ||
Discount | String | Discount given to specific item in invoice. | ||
DiscountAmount | Decimal | Amount of discount. | ||
DiscountAppliedOnAmount | Decimal | Discount applied on amount. | ||
DiscountPercent | Decimal | Percent of discount applied. | ||
DiscountTotal | Decimal | Total amount get on discount. | ||
DiscountType | String | Type of discount. | ||
DueDate | Date | Delivery date of the invoice. | ||
EcommOperatorGstNo | String | GST number of the ecommerce operator. | ||
EcommOperatorId | String | ID of the ecommerce operator. | ||
EcommOperatorName | String | Name of the ecommerce operator. | ||
EstimateId | String |
Estimates.EstimateId | ID of an estimate. | |
Ewaybills | String | Electronic way bills of the invoice. | ||
ExchangeRate | Decimal | Exchange rate of the currency. | ||
FiledInVatReturnId | String | VAT return ID of bill which was filed. | ||
FiledInVatReturnName | String | VAT return name of bill which was filed. | ||
FiledInVatReturnType | String | VAT return type of bill which was filed. | ||
GstNo | String | GST number. | ||
GstReturnDetailsReturnPeriod | String | Return period of GST. | ||
GstReturnDetailsStatus | String | Status of GST return. | ||
GstTreatment | String | Choose whether the invoice is GST registered/unregistered/consumer/overseas. . | ||
HasNextInvoice | Boolean | Check if it has next invoice. | ||
IsAutobillEnabled | Boolean | Check if the autobill is enabled for this invoice. | ||
IsClientReviewSettingsEnabled | Boolean | Check if the client review settings is enabled or not. | ||
IsDiscountBeforeTax | Boolean | Check if the invoice is discounted before tax. | ||
IsEmailed | Boolean | Check if the invoice can be emailed. | ||
IsEwayBillRequired | Boolean | Check if the eway bill is required. | ||
IsInclusiveTax | Boolean | Check if the invoice is inclusive tax. | ||
IsPreGst | Boolean | Check if pre GST is applied. | ||
IsTaxable | Boolean | Check if invoice is taxable. | ||
IsViewedByClient | Boolean | Check if the invoice is viewed by client. | ||
LastModifiedById | String |
Users.UserId | Id of the user last modified. | |
LastModifiedTime | Datetime | The time of last modification of the invoice. | ||
LastPaymentDate | Date | Date when last payment was made. | ||
LastReminderSentDate | Date | Date when last reminder was sent for an invoice. | ||
MerchantGstNo | String | GST number of a merchant. | ||
MerchantId | String | ID of a merchant. | ||
MerchantName | String | Name of a merchant. | ||
NoOfCopies | Integer | Total number of copies for invoice. | ||
Notes | String | Notes for this invoice. | ||
Orientation | String | Orientation of the page. | ||
PageHeight | String | Height of the page. | ||
PageWidth | String | Width of the page. | ||
PaymentDiscount | Decimal | Discount applied on payment. | ||
PaymentExpectedDate | Date | Expected date of payment. | ||
PaymentMade | Decimal | Total amount of payment made. | ||
PaymentReminderEnabled | Boolean | Check if the payment reminder is enabled for the invoice. | ||
PaymentTerms | Integer | Net payment term for the customer. | ||
PaymentTermsLabel | String | Label for the paymet due details. | ||
PlaceOfSupply | String | The place of supply is where a transaction is considered to have occurred for VAT purposes. | ||
PricePrecision | Integer | The precision for the price. | ||
ReaderOfflinePaymentInitiated | Boolean | Check if the payment for offline reader is initiated. | ||
ReasonForDebitNote | String | Description of having the debit note. | ||
RecurringInvoiceId | String |
RecurringInvoices.RecurringInvoiceId | Id of a recurring invoice. | |
ReferenceNumber | String | Reference number of invoice. | ||
RemindersSent | Integer | Total number of reminders sent for this invoice. | ||
ReverseChargeTaxTotal | Decimal | Total amount of reverse charge tax. | ||
RoundoffValue | Decimal | Rounding off the values to precise number. | ||
SalesorderId | String |
SalesOrders.SalesorderId | ID of a sales order. | |
SalespersonId | String | ID of a sales person. | ||
SalespersonName | String | Name of a sales person. | ||
ScheduleTime | String | Time scheduled for the invoice. | ||
ShipmentCharges | String | Charges deducted for shipment. | ||
ShippingAddress | String | Shipment Address. | ||
ShippingAddressAttention | String | Name of a person of shipping address. | ||
ShippingAddressCity | String | City of a shipping address. | ||
ShippingAddressCountry | String | Country of a shipping address. | ||
ShippingAddressFax | String | Fax of a shipping address. | ||
ShippingAddressPhone | String | Phone number of a shipping address. | ||
ShippingAddressState | String | State of a shipping address. | ||
ShippingAddressStreet2 | String | Street two details of a shipping address. | ||
ShippingAddressZip | String | Zip code of a shipping address. | ||
ShippingCharge | Decimal | Shipping charge of invoice. | ||
ShippingBills | String | Shipping bills of invoice. | ||
ShowNoOfCopies | Boolean | Check if the invoice can show number of copies. | ||
Status | String | Status of the invoice. | ||
StopReminderUntilPaymentExpectedDate | Boolean | Check if reminder can be stopped untill the payment expected date. | ||
SubTotal | Decimal | Sub total of the invoice. | ||
SubTotalInclusiveOfTax | Decimal | Subtotal amount which are inclusive of tax. | ||
SubmittedBy | String | Detail of the user who has submitted the invoice. | ||
SubmittedDate | Date | Date when invoice was submitted. | ||
SubmitterId | String |
Users.UserId | Id of the invoice submitter. | |
TaxAmountWithheld | Decimal | Amount withheld for tax. | ||
TaxRegNo | String | Registration number of tax. | ||
TaxSpecification | String | Working of tax when specifying special tax options and tax methods for earnings codes. | ||
TaxTotal | Decimal | Total number ot tax applied in the invoice. | ||
TaxTreatment | String | VAT treatment for the Invoice. | ||
TemplateId | String | ID of a template. | ||
TemplateName | String | Name of a template. | ||
TemplateType | String | Type of a template. | ||
Terms | String | Terms and Conditions apply of a invoice. | ||
Total | Decimal | Total of invoices. | ||
TransactionRoundingType | String | Type of round off used for transaction. | ||
Type | String | Types of invoice. | ||
UnusedRetainerPayments | Decimal | Payment of the invoice which is unused. | ||
VatTreatment | String | VAT treatment for the bills. | ||
WriteOffAmount | Decimal | Amount to be write off. |
Get the details of line items from invoices.
The connector 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 connector.
Note: InvoiceId is required in order to query InvoiceLineItems.
For example:
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 connector 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 connector.
For example:
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 connector 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 connector.
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 details of an existing item.
The connector 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 connector.
Note: ItemId is required in order to query ItemDetails.
For example:
SELECT * FROM ItemDetails WHERE ItemId = '1894553000000079049'
Name | Type | References | SupportedOperators | Description |
ItemId [KEY] | String |
Items.ItemId | ID of an item. | |
ItemType | String | Type of an item. | ||
AccountId | String |
BankAccounts.AccountId | ID of the Bank Account. | |
AccountName | String | Name of the account. | ||
Brand | String | Brand of the item. | ||
CreatedTime | Datetime | Time at which the item was created. | ||
Description | String | Description of an item. | ||
ImageName | String | Name of the image. | ||
LastModifiedTime | Datetime | The time of last modification of the item. | ||
Manufacturer | String | Company that makes goods for sale. | ||
Name | String | Name of an item. | ||
PricebookRate | Decimal | Rate of pricebook. | ||
ProductType | String | Type of the product. | ||
PurchaseAccountId | String |
BankAccounts.AccountId | Account ID of purchase items. | |
PurchaseAccountName | String | Account name of purchase items. | ||
PurchaseDescription | String | Description of purchase items. | ||
PurchaseRate | Decimal | Rate of purchase items. | ||
Rate | Decimal | Rate of the item. | ||
SalesChannels | String | Total channels exists for sales. | ||
SalesRate | Decimal | The rate of sale in the item. | ||
Sku | String | Stock Keeping Unit value of item, should be unique throughout the product. | ||
Source | String | Source of the item. | ||
Status | String | Status of the item. | ||
Tags | String | Details of tags related to 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 of item. |
Retrieves list of all active items.
The connector 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 connector.
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 connector 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 connector.
Note: JournalId is required in order to query JournalLineItems.
For example:
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 journals.
The connector 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 connector. 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 | References | SupportedOperators | Description |
JournalId [KEY] | String | Id of a journal. | ||
JournalType | String | Type of a journal. | ||
JournalDate | Date | Date of a journal. | ||
BcyTotal | Decimal | Total Base Currency | ||
CurrencyId | String |
Currencies.CurrencyId | Currency ID of the customer's currency. | |
CustomFields | String | Custom Fields defined for Journal | ||
EntityType | String | Entity type of a journal. | ||
EntryNumber | String | Entry number of the journal. | ||
Notes | String | Notes of the journal. | ||
ReferenceNumber | String | Reference number of the journal. | ||
Status | String | Status of the journal | ||
Total | Decimal | =, <, <=, >, >= | Total of journals. Search by journal total. This field will be populated with a value only when the Journal ID is specified. | |
JournalNumberPrefix | String | Prefix for journal number. This field will be populated with a value only when the Journal ID is specified. | ||
JournalNumberSuffix | String | Suffix for journal number. This field will be populated with a value only when the Journal ID is specified. | ||
CreatedTime | Datetime | Time at which the journal was created. This field will be populated with a value only when the Journal ID is specified. | ||
CurrencyCode | String | Currency code of the customer's currency. This field will be populated with a value only when the Journal ID is specified. | ||
CurrencySymbol | String | Currency symbol of the customer's currency. This field will be populated with a value only when the Journal ID is specified. | ||
ExchangeRate | Decimal | Exchange rate of the currency. This field will be populated with a value only when the Journal ID is specified. | ||
LastModifiedTime | Datetime | Last Modified Time of a journal. This field will be populated with a value only when the Journal ID is specified. | ||
LineItemTotal | Decimal | 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 | The precision for the price This field will be populated with a value only when the Journal ID is specified. | ||
ProjectId | String |
Projects.ProjectId | ID of a project. This field will be populated with a value only when the Journal ID is specified. | |
ProjectName | String | Name of a project. This field will be populated with a value only when the Journal ID is specified. |
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 |
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 connector.
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 details of opening balance.
No filters are supported server side for this table. All criteria will be handled client side within the connector.
Name | Type | References | SupportedOperators | Description |
OpeningBalanceId [KEY] | String | ID of an opening balance. | ||
CanShowCustomerOb | Boolean | Check if opening balance can show customer ob. | ||
CanShowVendorOb | Boolean | Check if opening balance can show vendor ob. | ||
Date | Date | Date of an opening balance. | ||
PricePrecision | Integer | The precision for the price | ||
Total | Decimal | Total of opening balances. |
Get transaction summaries of opening balance.
No filters are supported server side for this table. All criteria will be handled client side within the connector.
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 connector.
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. |
Retrieves list of invoices created for a project.
The connector 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 connector.
For example:
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 projects.
The connector 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 connector.
SELECT * FROM Projects WHERE CustomerId = '1894553000000078233' AND ProjectId = '1894553000000078363' SELECT * FROM Projects WHERE Status = 'Active'
Name | Type | References | SupportedOperators | Description |
ProjectId [KEY] | String | Id of the project. | ||
ProjectName | String | Name of the project. | ||
Rate | Decimal | Hourly rate for a task. | ||
Status | String | Status of the project.
The allowed values are All, Active, Inactive. | ||
BillableHours | String | Total number of billable hours. | ||
BillingType | String | Type of billing. | ||
CanBeInvoiced | Boolean | Check if the projecy can be invoiced. | ||
CreatedTime | Datetime | Time at which the project was created. | ||
CustomerId | String |
Contacts.ContactId | ID of the customer or vendor. Search projects by customer id. | |
CustomerName | String | Name of the customer or vendor. | ||
Description | String | Description of the projects | ||
HasAttachment | Boolean | Check if the projects has attachment. | ||
OtherServiceAppSource | String | Source of other service app. | ||
TotalHours | String | Total hours spent in the project. | ||
ShowInDashboard | Boolean | 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 | ID of the project head. This field will be populated with a value only when the ID is specified. | ||
ProjectHeadName | String | Name of the project head. This field will be populated with a value only when the ID is specified. | ||
BillableAmount | Decimal | Amount which is billable for this project. This field will be populated with a value only when the ID is specified. | ||
BilledAmount | Decimal | Total amount which was billed for the project. This field will be populated with a value only when the ID is specified. | ||
BilledHours | String | Total number of billed hours. This field will be populated with a value only when the ID is specified. | ||
BudgetThreshold | Decimal | 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 | Type of budget. This field will be populated with a value only when the ID is specified. | ||
CurrencyCode | String | Code of currency used in the project. This field will be populated with a value only when the ID is specified. | ||
IsBudgetThresholdNotificationEnabled | Boolean | 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 | Check if the client approval is needed. This field will be populated with a value only when the ID is specified. | ||
IsExpenseInclusive | Integer | 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 | Check if the user approval is needed. This field will be populated with a value only when the ID is specified. | ||
IsValidProjectHead | Boolean | Check if the project has valid project head. This field will be populated with a value only when the ID is specified. | ||
NonBillableAmount | Decimal | Amount which are non billable for the project.. This field will be populated with a value only when the ID is specified. | ||
NonBillableHours | String | Hours which are non billable for the project. This field will be populated with a value only when the ID is specified. | ||
TotalAmount | Decimal | Total amount for the project. This field will be populated with a value only when the ID is specified. | ||
TotalAmountExpenseInclusive | Decimal | Total amount for the project including the project. This field will be populated with a value only when the ID is specified. | ||
UnBilledAmount | Decimal | Total amount unbilled for the project. This field will be populated with a value only when the ID is specified. | ||
UnBilledHours | String | Total number of unbilled hours. This field will be populated with a value only when the ID is specified. | ||
UnusedRetainerPayments | Decimal | Payment of the project which is unused. This field will be populated with a value only when the ID is specified. |
Retrieves list of users associated with a project. Also, get details of a user in project.
The connector 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 connector.
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 a purchase order.
The connector 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 connector.
Note: PurchaseorderId is required in order to query PurchaseOrderDetails.
For example:
SELECT * FROM PurchaseOrderDetails WHERE PurchaseorderId = '1894553000000087078'
Name | Type | References | SupportedOperators | Description |
PurchaseorderId [KEY] | String |
PurchaseOrders.PurchaseorderId | ID of a purchase order. | |
PurchaseorderNumber | String | Number of purchase order. | ||
ReferenceNumber | String | Reference number of purchase order. | ||
Adjustment | Decimal | Adjustments made to the purchase order. | ||
AdjustmentDescription | String | Description of adjustments made to the purchase order. | ||
ApproverId | String |
Users.UserId | ID of an approver. | |
AttachmentName | String | Name of the attachment. | ||
Attention | String | Name of a person in purchase order. | ||
BilledStatus | String | Status of bill. | ||
BillingAddress | String | Billing address of a purchase order. | ||
BillingAddressAttention | String | Name of the person of bill order. | ||
BillingAddressCity | String | City of billing address. | ||
BillingAddressCountry | String | Country of billing address. | ||
BillingAddressFax | String | Fax number of billing address. | ||
BillingAddressPhone | String | Phone number of billing address. | ||
BillingAddressState | String | State of billing address. | ||
BillingAddressStreet2 | String | Street two of billing address. | ||
BillingAddressZip | String | Zip code of billing address. | ||
CanMarkAsBill | Boolean | Check if purchase order can be mark as bill. | ||
CanMarkAsUnbill | Boolean | Check if purhcase order can be mark as unbill. | ||
CanSendInMail | Boolean | Check if purchase order can be sent in mail. | ||
ClientViewedTime | Datetime | Last time when client viewed the purchase order. | ||
ColorCode | String | Color code. | ||
ContactCategory | String | Category of contacts. | ||
CreatedById | String |
Users.UserId | Contact ID who have created this purchase order. | |
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. | |
CurrencySymbol | String | Currency symbol 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 | Purchase order date. | ||
DeliveryAddress | String | Delivery address. | ||
DeliveryAddress1 | String | Delivery address one. | ||
DeliveryAddress2 | String | Delivery address two. | ||
DeliveryAddressCity | String | City of delivery address. | ||
DeliveryAddressCountry | String | Country of delivery address. | ||
DeliveryAddressOrganizationAddressId | String | Id or organization address of delivery address. | ||
DeliveryAddressPhone | String | Phone number of delivery address. | ||
DeliveryAddressState | String | State of delivery address. | ||
DeliveryAddressZip | String | Zip code of delivery address. | ||
DeliveryCustomerId | String |
Contacts.ContactId | ID of a customer of delivery address. | |
DeliveryDate | Date | Date of delivery. | ||
DeliveryOrgAddressId | String | Delivery address ID of an organization. | ||
Discount | String | Discount given to specific item in purchase order. | ||
DiscountAccountId | String |
BankAccounts.AccountId | Account ID of discount. | |
DiscountAmount | Decimal | Amount of discount. | ||
DiscountAppliedOnAmount | Double | Discount applied on amount. | ||
ExchangeRate | Decimal | Exchange rate of the currency. | ||
ExpectedDeliveryDate | Date | Expected delivery date of purchased product. | ||
HasQtyCancelled | Boolean | Check if the quantity of a purchase order has been cancelled. | ||
IsDiscountBeforeTax | Boolean | Check if purchase order applied discount before tax. | ||
IsDropShipment | Boolean | Check if purchase order have drop shipment. | ||
IsEmailed | Boolean | Check if purchase order is emailed or not. | ||
IsInclusiveTax | Boolean | Check if the purchase order is inclusive tax. | ||
IsPreGst | Boolean | Check if purchase order includes pre GST. | ||
IsViewedByClient | Boolean | Check if purchase order is viewed by client. | ||
LastModifiedTime | Datetime | The time of last modification of the purchase order. | ||
Notes | String | Notes for this purchase order. | ||
OrderStatus | String | Status of order. | ||
Orientation | String | Orientation of the page. | ||
PageHeight | String | Height of the page. | ||
PageWidth | String | Width of the page. | ||
PricePrecision | Integer | The precision for the price | ||
ShipVia | String | Mode of shipping the item. | ||
ShipViaId | String | ID of mode through which shipping was done of items. | ||
Status | String | Status of the purchase order | ||
SubTotal | Decimal | Sub total of Purhcase order. | ||
SubTotalInclusiveOfTax | Decimal | Subtotal amount which are inclusive of tax. | ||
SubmittedBy | String | Detail of the user who has submitted the purchase order. | ||
SubmittedDate | Date | Date of the submission. | ||
SubmitterId | String |
Users.UserId | ID of the submitter. | |
TaxTotal | Decimal | Total amount of tax. | ||
TemplateId | String | ID of the template. | ||
TemplateName | String | Name of the template | ||
TemplateType | String | Type of template. | ||
Terms | String | Terms and Conditions apply of a purchase order. | ||
Total | Decimal | Total of purchase orders. | ||
VendorId | String | ID of the vendor the purchase order has been made. | ||
VendorName | String | Name of the vendor the purchase order has been made. |
Get the details of line items of purchase orders.
The connector 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 connector.
Note: PurchaseorderId is required in order to query PurchaseOrderLineItems.
For example:
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 connector 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 connector.
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. |
Get the details of a recurring expense.
The connector 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 connector.
Note: RecurringExpenseId is required in order to query RecurringExpenseDetails.
For example:
SELECT * FROM RecurringExpenseDetails WHERE RecurringExpenseId = '1801553000000089750'
Name | Type | References | SupportedOperators | Description |
RecurringExpenseId [KEY] | String |
RecurringExpenses.RecurringExpenseId | Id of a recurring expense. | |
AccountId | String |
BankAccounts.AccountId | ID of the Bank Account | |
AccountName | String | Name of the account. | ||
Amount | Decimal | Amount of the recurring expenses. | ||
BcyTotal | Decimal | Total Base Currency. | ||
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. | |
CustomerId | String |
Contacts.ContactId | ID of the customer or vendor. | |
CustomerName | String | Name of the customer or vendor. | ||
Description | String | Description of the recurring expense. | ||
EmployeeEmail | String | Email of an employee. | ||
EmployeeId | String |
Employees.EmployeeId | Id of an employee. | |
EmployeeName | String | Name of an employee. | ||
EndDate | Date | End date of a recurring expense. | ||
ExchangeRate | Decimal | Exchange rate of a recurring expense. | ||
IsBillable | Boolean | Check if recurring expense is billable. | ||
IsInclusiveTax | Boolean | Check if recurring expense is inclusive tax. | ||
IsPreGst | Boolean | Check if recurring expense is pre GST. | ||
LastCreatedDate | Date | Last created date of a recurring expense. | ||
LastModifiedTime | Datetime | The time of last modification of the recurring expense. | ||
MileageRate | Double | Mileage rate for a particular mileage expense. | ||
MileageUnit | String | Unit of the distance travelled. | ||
NextExpenseDate | Date | Next date of expense to be paid. | ||
PaidThroughAccountId | String |
BankAccounts.AccountId | Account ID from which expense is paid through. | |
PaidThroughAccountName | String | Account name from which expense is paid through. | ||
ProjectId | String |
Projects.ProjectId | ID of a project. | |
ProjectName | String | Name of the project. | ||
RecurrenceFrequency | String | Frequency of a recurrence. | ||
RecurrenceName | String | Name of a recurrence | ||
RepeatEvery | Integer | Recurrence time of an expense. | ||
StartDate | Date | Start date of recurring expense. | ||
Status | String | Status of the recurring expense. | ||
SubTotal | Decimal | Sub total of recurring expenses. | ||
Tags | String | Details of tags related to recurring expenses. | ||
TaxAmount | Decimal | Amount of a tax. | ||
TaxId | String |
Taxes.TaxId | Id of a tax. | |
TaxName | String | Name of a tax. | ||
TaxPercentage | Integer | Percentage of a tax. | ||
Total | Decimal | Total of recurring expenses. | ||
VendorId | String | ID of the vendor the recurring expense has been made. | ||
VendorName | String | Name of the vendor the recurring expense has been made. |
Retrieves list of all the Expenses.
The connector 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 connector.
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 a recurring invoice.
The connector 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 connector.
Note: RecurringInvoiceId is required in order to query RecurringInvoiceDetails.
For example:
SELECT * FROM RecurringInvoiceDetails WHERE RecurringInvoiceId = '1895453000000042244'
Name | Type | References | SupportedOperators | Description |
RecurringInvoiceId [KEY] | String |
RecurringInvoices.RecurringInvoiceId | Id of a recurring invoice. | |
ActualChildInvoicesCount | Integer | Count total number of actual child invoices. | ||
Adjustment | Decimal | Adjustments made to the recurring invoices. | ||
AdjustmentDescription | String | Description of adjustments made to the recurring invoices. | ||
AllowPartialPayments | Boolean | Check if the recuuring invoice can allow partial payments. | ||
BcyAdjustment | Decimal | Adjustment of base currency. | ||
BcyDiscountTotal | Decimal | Total discount applied in base currency. | ||
BcyShippingCharge | Decimal | Shipping charge applied in base currency. | ||
BcySubTotal | Decimal | Sub total of base currency. | ||
BcyTaxTotal | Decimal | Tax total of base currency. | ||
BcyTotal | Decimal | Total Base Currency. | ||
BillingAddress | String | Billing address of a recurring invoice. | ||
BillingAddressAttention | String | Name of a person in billing address. | ||
BillingAddressCity | String | City of a billing address. | ||
BillingAddressCountry | String | Country of a billing address. | ||
BillingAddressFax | String | Fax of a billing address. | ||
BillingAddressPhone | String | Phone number of a billing address. | ||
BillingAddressState | String | State of a billing address. | ||
BillingAddressStreet2 | String | Street two of a billing address. | ||
BillingAddressZip | String | ZIP code of a billing address. | ||
ChildEntityType | String | Entity type of a child in recurring invoice. | ||
CompanyName | String | Name of the company. | ||
ContactCategory | String | Category of the contact. | ||
CreatedById | String |
Users.UserId | Id of a user who has created recurring invoice. | |
CreatedTime | Datetime | Time at which the recurring invoice was created. | ||
CurrencyCode | String | Currency code of the customer's currency. | ||
CurrencyId | String |
Currencies.CurrencyId | Currency ID of the customer's currency. | |
CurrencySymbol | String | Symbol of the currency. | ||
CustomerEmail | String | Email address of the customer. | ||
CustomerMobilePhone | String | Mobile phone number of customer. | ||
CustomerId | String |
Contacts.ContactId | ID of the customer or vendor. | |
CustomerName | String | Name of the customer or vendor. | ||
CustomerPhone | String | Phone number of a customer. | ||
Discount | String | Discount given to specific item in recurring invoice. | ||
DiscountAmount | Decimal | Total amount which was discounted. | ||
DiscountAppliedOnAmount | Decimal | Amount from which discount was applied. | ||
DiscountPercent | Double | Percentage of discount applied. | ||
DiscountTotal | Decimal | Total amount get on discount. | ||
DiscountType | String | Type to get discount in recurring invoice. | ||
EndDate | Date | End date for the statement. | ||
ExchangeRate | Decimal | Exchange rate of the currency. | ||
IsDiscountBeforeTax | Boolean | Check if the recurring invoice is discounted before tax. | ||
IsInclusiveTax | Boolean | Check if the expense is inclusive tax. | ||
IsPreGst | Boolean | Check if pre GST is applied. | ||
LastModifiedById | String |
Users.UserId | Id of the user last modified. | |
LastModifiedTime | Datetime | The time of last modification of the recurring invoice | ||
LastSentDate | Date | The date at which the last recurring invoice was sent. | ||
ManualChildInvoicesCount | Integer | Count of manual child invoices. | ||
NextInvoiceDate | Date | Date of a next invoice. | ||
Notes | String | Notes for this recurring invoice. | ||
Orientation | String | Orientation of a page. | ||
PageHeight | String | Height of the page. | ||
PageWidth | String | Width of the page. | ||
PaidInvoicesTotal | Decimal | Total number of paid invoices. | ||
PaymentTerms | Integer | Net payment term for the customer. | ||
PaymentTermsLabel | String | Label for the paymet due details. | ||
PhotoUrl | String | Photo URL for recurring invoices. | ||
PricePrecision | Integer | The precision for the price. | ||
ProjectDetails | String | Details of project. | ||
RecurrenceFrequency | String | Type of recurrence frequency the invoice is recurring. | ||
RecurrenceName | String | Name of the recurrence. | ||
ReferenceNumber | String | Reference number of a recurring invoice. | ||
RepeatEvery | Integer | Recurrence time of the invoice. | ||
RoundoffValue | Decimal | Rounding off the values to precise number. | ||
SalespersonId | String | ID of a sales person. | ||
SalespersonName | String | Name of a sales person. | ||
ShipmentCharges | String | Shipment charges of recurring invoice. | ||
ShippingAddress | String | Shipment Address. | ||
ShippingAddressAttention | String | Name of a person of shipping address. | ||
ShippingAddressCity | String | City of a shipping address. | ||
ShippingAddressCountry | String | Country of a shipping address. | ||
ShippingAddressFax | String | Fax of a shipping address. | ||
ShippingAddressPhone | String | Phone number of a shipping address. | ||
ShippingAddressState | String | State of a shipping address. | ||
ShippingAddressStreet2 | String | Street two details of a shipping address. | ||
ShippingAddressZip | String | Zip code of a shipping address. | ||
ShippingCharge | Decimal | Shipping charge. | ||
StartDate | Date | Starting date of recurring invoice. | ||
Status | String | Status of the recurring invoice. | ||
SubTotal | Decimal | Sub total of recurring invoices. | ||
SubTotalInclusiveOfTax | Decimal | Subtotal amount which are inclusive of tax. | ||
TaxTotal | Decimal | Total amount for tax. | ||
TemplateId | String | ID of a template. | ||
TemplateName | String | Name of a template. | ||
Terms | String | Terms and Conditions apply of a recurring invoice. | ||
Total | Decimal | Total of recurring invoices. | ||
TransactionRoundingType | String | Type of round off used for transaction. | ||
UnpaidChildInvoicesCount | String | Count of total number of unpaid child invoices. | ||
UnpaidInvoicesBalance | Decimal | Total amount of unpaid invoices. |
Get the details of line items of a recurring invoice.
The connector 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 connector.
Note: RecurringInvoiceId is required in order to query RecurringInvoiceLineItems.
For example:
SELECT * FROM RecurringInvoiceLineItems WHERE RecurringInvoiceId = '1895453000000042244'
Name | Type | References | SupportedOperators | Description |
RecurringInvoiceId | String |
RecurringInvoices.RecurringInvoiceId | ID of recurring invoice. | |
LineItemId [KEY] | String | ID of line item. | ||
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 connector 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 connector.
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 connector 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 connector.
For example:
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. |
Get the details of a retainer invoice.
The connector 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 connector.
Note: RetainerInvoiceId is required in order to query RetainerInvoiceDetails.
For example:
SELECT * FROM RetainerInvoiceDetails WHERE RetainerInvoiceId = '1894663000000085023'
Name | Type | References | SupportedOperators | Description |
RetainerInvoiceId [KEY] | String |
RetainerInvoices.RetainerInvoiceId | Id of retainer invoice. | |
RetainerinvoiceNumber | String | Number of a retainer invoice. | ||
AllowPartialPayments | Boolean | Check if the retainer invoice allows partial payments. | ||
AttachmentName | String | Name of the attachment. | ||
Balance | Decimal | Total amount left. | ||
BillingAddress | String | Billing address of a retainer invoice. | ||
BillingAddressAttention | String | Name of a person in billing address. | ||
BillingAddressCity | String | City of a billing address. | ||
BillingAddressCountry | String | Country of a billing address. | ||
BillingAddressFax | String | Fax of a billing address. | ||
BillingAddressPhone | String | Phone number of a billing address. | ||
BillingAddressState | String | State of a billing address. | ||
BillingAddressStreet2 | String | Street two of a billing address. | ||
BillingAddressZip | String | ZIP code of a billing address. | ||
CanSendInMail | Boolean | Check if retainer invoice can be send in mail. | ||
ClientViewedTime | Datetime | Last time when client viewed retainer invoice. | ||
ColorCode | String | Color code of retainer invoice. | ||
CreatedById | String |
Users.UserId | Id of a user who has created 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. | |
CurrencySymbol | String | Symbol of currency used for retainer invoice. | ||
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. | ||
ExchangeRate | Decimal | Exchange rate of the currency. | ||
InvoiceUrl | String | URL of invoice. | ||
IsEmailed | Boolean | Check if the retainer invoice is emailed. | ||
IsInclusiveTax | Boolean | Check if the retainer invoice is inclusive of tax. | ||
IsPreGst | Boolean | Check if the retainer invoice is applied by pre GST. | ||
IsViewedByClient | Boolean | Check if retainer invoice is viewed by client. | ||
LastModifiedById | String |
Users.UserId | Id of a user who has last modified the retainer invoice. | |
LastModifiedTime | Datetime | The time of last modification of the retainer invoice. | ||
LastPaymentDate | Date | Date of payment which was last paid. | ||
Notes | String | Notes of retainer invoice. | ||
Orientation | String | Orientation of a page. | ||
PageHeight | String | Height of a page. | ||
PageWidth | String | Width of a page. | ||
PaymentDrawn | Decimal | The payment which was drawn for retainer invoice. | ||
PaymentMade | Decimal | Payment which was made for the invoice. | ||
PricePrecision | Integer | The precision for the price. | ||
ReferenceNumber | String | Reference number of a retainer invoice. | ||
RoundoffValue | Decimal | Round Off value. | ||
ShippingAddress | String | Shipment Address. | ||
ShippingAddressAttention | String | Name of a person of shipping address. | ||
ShippingAddressCity | String | City of a shipping address. | ||
ShippingAddressCountry | String | Country of a shipping address. | ||
ShippingAddressFax | String | Fax of a shipping address. | ||
ShippingAddressPhone | String | Phone number of a shipping address. | ||
ShippingAddressState | String | State of a shipping address. | ||
ShippingAddressStreet2 | String | Street two details of a shipping address. | ||
ShippingAddressZip | String | Zip code of a shipping address. | ||
Status | String | Status of the retainer invoice. | ||
SubTotal | Decimal | Sub total of retainer invoices. | ||
SubmittedBy | String | Detail of the user who has submitted the retainer invoice. | ||
SubmittedDate | Date | Date of submission of retainer invoice. | ||
TemplateId | String | ID of a template. | ||
TemplateName | String | Name of a template. | ||
TemplateType | String | Type of a template. | ||
Terms | String | Terms and Conditions apply of a retainer invoice. | ||
Total | Decimal | Total of retainer invoices. | ||
TransactionRoundingType | String | Type of round off used for transaction. | ||
VatTreatment | String | VAT treatment for the retainer invoice. | ||
PlaceOfSupply | String | The place of supply is where a transaction is considered to have occurred for VAT purposes. | ||
TaxSpecification | String | Working of tax when specifying special tax options and tax methods for earnings codes. | ||
UnusedRetainerPayments | Decimal | Payment of the retainer invoice which is unused. |
Retrieves detail of line items of retainer invoices.
The connector 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 connector.
Note: RetainerInvoiceId is required in order to query RetainerInvoiceLineItems.
For example:
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 connector 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 connector.
For example:
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 connector 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 connector.
For example:
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. |
Get the details of a sales order.
The connector 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 connector.
Note: SalesorderId is required in order to query SalesOrderDetails.
For example:
SELECT * FROM SalesOrderDetails WHERE SalesorderId = '1894553000000077349'
Name | Type | References | SupportedOperators | Description |
SalesorderId [KEY] | String |
SalesOrders.SalesorderId | ID of sales order. | |
AccountIdentifier | String | Account identifier for sales order. | ||
Adjustment | Decimal | Adjustments made to the sales order. | ||
AdjustmentDescription | String | Description of adjustments made to the sales order. | ||
ApproverId | String |
Users.UserId | ID of an approver. | |
AttachmentName | String | Name of the attachment. | ||
BcyAdjustment | Decimal | Adjustment made in Base Currency. | ||
BcyDiscountTotal | Decimal | Total amount of discount in Base Currency. | ||
BcyShippingCharge | Decimal | Shipping charge applied in Base Currency. | ||
BcySubTotal | Decimal | Sub total of Base Currency. | ||
BcyTaxTotal | Decimal | Total tax of Base Currency. | ||
BcyTotal | Decimal | Total Base Currency. | ||
BillingAddress | String | Billing address of a sales order. | ||
BillingAddressAttention | String | Name of a person in billing address. | ||
BillingAddressCity | String | City of a billing address. | ||
BillingAddressCountry | String | Country of a billing address. | ||
BillingAddressFax | String | Fax of a billing address. | ||
BillingAddressPhone | String | Phone number of a billing address. | ||
BillingAddressState | String | State of a billing address. | ||
BillingAddressStreet2 | String | Street two of a billing address. | ||
BillingAddressZip | String | ZIP code of a billing address. | ||
CanSendInMail | Boolean | Check if the sales order can be send in mail. | ||
ColorCode | String | Color code for sales order. | ||
ContactCategory | String | Category of a contact. | ||
CreatedById | String |
Users.UserId | Id of a user who has created sales order. | |
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. | |
CurrencySymbol | String | Currency symbol 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. | |
CustomerName | String | Name of the customer or vendor. | ||
Date | Date | Date of a sales order. | ||
DeliveryMethod | String | Method of a delivery. | ||
DeliveryMethodId | String | Method ID of a delivery. | ||
Discount | String | Discount given to specific item in sales order. | ||
DiscountAppliedOnAmount | Decimal | Amount in which discount was applied. | ||
DiscountPercent | Double | Percentage applied for discount. | ||
DiscountTotal | Decimal | Total amount get on discount. | ||
DiscountType | String | Type of discount applied in sales order. | ||
EstimateId | String |
Estimates.EstimateId | ID of an estimate. | |
ExchangeRate | Decimal | Exchange rate of the currency. | ||
HasQtyCancelled | Boolean | Check if the sales order quantity has been cancelled. | ||
IntegrationId | String | ID of sales order integration. | ||
InvoiceConversionType | String | Type of invoice conversion applied for sales order. | ||
InvoicedStatus | String | Status of invoiced sales order. | ||
IsDiscountBeforeTax | Boolean | Check if the sales order can be applied discount before tax. | ||
IsEmailed | Boolean | Check if the sales order is emailed. | ||
IsInclusiveTax | Boolean | Check if the sales order is inclusive tax. | ||
IsPreGst | Boolean | Check if pre GST is applied. | ||
LastModifiedById | String |
Users.UserId | Id of the user last modified. | |
LastModifiedTime | Datetime | The time of last modification of the sales order. | ||
MerchantId | String | ID of the merchant. | ||
MerchantName | String | Name of the merchant. | ||
Notes | String | Notes of sales order. | ||
OrderStatus | String | Status of order. | ||
Orientation | String | Orientation of page. | ||
PageHeight | String | Height of page. | ||
PageWidth | String | Width of page. | ||
PricePrecision | Integer | The precision for the price. | ||
ReferenceNumber | String | Reference number of a sales order. | ||
RoundoffValue | Decimal | Round Off value of sales order. | ||
SalesorderNumber | String | Number of sales order. | ||
SalespersonId | String | ID of a sales person. | ||
SalespersonName | String | Name of the sales person. | ||
ShipmentDate | Date | Date when shipment was done for sale order. | ||
ShippingAddress | String | Shipment Address. | ||
ShippingAddressAttention | String | Name of a person of shipping address. | ||
ShippingAddressCity | String | City of a shipping address. | ||
ShippingAddressCountry | String | Country of a shipping address. | ||
ShippingAddressFax | String | Fax of a shipping address. | ||
ShippingAddressPhone | String | Phone number of a shipping address. | ||
ShippingAddressState | String | State of a shipping address. | ||
ShippingAddressStreet2 | String | Street two details of a shipping address. | ||
ShippingAddressZip | String | Zip code of a shipping address. | ||
ShippingCharge | Decimal | Shipping charge. | ||
Status | String | Status of the sales order. | ||
SubTotal | Decimal | Sub total of sales orders. | ||
SubTotalInclusiveOfTax | Decimal | Subtotal amount which are inclusive of tax. | ||
SubmittedBy | String | Detail of the user who has submitted the sales order. | ||
SubmittedDate | Date | Date when submission was made of sales order. | ||
SubmitterId | String |
Users.UserId | Id of a submitter. | |
TaxTotal | Decimal | Total amount of tax. | ||
TemplateId | String | ID of a template. | ||
TemplateName | String | Name of a template. | ||
TemplateType | String | Type of template. | ||
Terms | String | Terms and Conditions apply of a sales order. | ||
Total | Decimal | Total of sales order. | ||
TransactionRoundingType | String | Type of round off used for transaction. |
Retrieves list of line items of a sales order.
The connector 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 connector.
Note: SalesorderId is required in order to query SalesOrderLineItems.
For example:
SELECT * FROM SalesOrderLineItems WHERE SalesorderId = '1894553000000077349'
Name | Type | References | SupportedOperators | Description |
SalesorderId | String |
SalesOrders.SalesorderId | ID of a sales order. | |
LineItemId [KEY] | String | ID of a line item. | ||
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 connector 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 connector.
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. |
Retrieves list of tasks added to a project. Also, get the details of a task.
The connector 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 connector.
SELECT * FROM Tasks WHERE ProjectId = '1894553000000078367' AND TaskId = '1894553000000085708'
Name | Type | References | SupportedOperators | Description |
TaskId [KEY] | String | ID of a task. | ||
ProjectId | String |
Projects.ProjectId | ID of the project. | |
CurrencyId | String |
Currencies.CurrencyId | Currency ID of the customer's currency. | |
TaskName | String | Name of the task. | ||
Description | String | Description of the task. | ||
ProjectName | String | Name of the project. | ||
CustomerId | String |
Contacts.ContactId | ID of the customer or vendor. | |
CustomerName | String | Name of the customer or vendor. | ||
BilledHours | String | Total number of billed hours for a task. | ||
LogTime | String | Time logs for the task. | ||
UnBilledHours | String | Total number of hours which was un-billed. | ||
Rate | Decimal | Rate for task. | ||
Status | String | Status of the task. | ||
IsBillable | Boolean | Check if tasks is billable or not. |
Retrieves list of simple and compound taxes. Also, get the details of a simple or compound tax.
The connector 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 connector.
For example:
SELECT * FROM Taxes WHERE TaxId = '1894553000000077244'
Name | Type | References | SupportedOperators | Description |
TaxId [KEY] | String | Id of tax. | ||
TaxName | String | Name of the tax. | ||
TaxPercentage | Integer | Percentage applied for tax. | ||
TaxSpecificType | String | Type of tax. | ||
TaxAuthorityId | String | ID of a tax authority. | ||
TaxAuthorityName | String | Name of the tax authority. | ||
TaxSpecification | String | Working of tax when specifying special tax options and tax methods for earnings codes. | ||
TdsPayableAccountId | String |
BankAccounts.AccountId | Account ID of TDS payable. | |
Country | String | Name of the country for taxes. | ||
CountryCode | String | Country code for taxes. | ||
IsDefaultTax | Boolean | Check if the tax is default. | ||
IsEditable | Boolean | Check if the tax is editable. | ||
TaxType | String | Type of tax. |
Retrieves list of all time entries.
The connector 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 connector. 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 | References | SupportedOperators | Description |
TimeEntryId [KEY] | String | Id of time entry. | ||
TimerDurationInMinutes | Integer | Timer duration in minutes. | ||
TimerDurationInSeconds | Integer | Timer duration in seconds. | ||
TimerStartedAt | String | Time when the timer started. | ||
BeginTime | String | Time the user started working on this task. | ||
BilledStatus | String | Status which are billed. | ||
CanBeInvoiced | Boolean | Check if the entry can be invoiced. | ||
CanContinueTimer | Boolean | Check if the entry can continue the timer. | ||
CanCreateClientApproval | Boolean | Check if the entry can create client approval. | ||
CanCreateUserApproval | Boolean | Check if the entry can create user approval. | ||
CreatedTime | Datetime | Time at which the time entry was created. | ||
CustomerId | String |
Contacts.ContactId | ID of the customer or vendor. | |
CustomerName | String | Name of the customer or vendor. | ||
EndTime | String | Time the user stopped working on this task. | ||
InvoiceId | String |
Invoices.InvoiceId | ID of an invoice. | |
InvoiceNumber | String | Number of an invoice. | ||
IsBillable | Boolean | Check if time entries is billable. | ||
IsClientApprovalNeeded | Boolean | Check if the client approval is needed in time entries. | ||
IsCurrentUser | Boolean | Check if it is a current user of time entries. | ||
IsPaused | Boolean | Check if time entries is paused. | ||
LogDate | Date | Log of date. | ||
LogTime | String | Log of time. | ||
Notes | String | Notes for this time entry. | ||
ProjectHeadId | String | ID of project head. | ||
ProjectHeadName | String | Name of project head. | ||
ProjectId | String |
Projects.ProjectId | Id of a project. | |
ProjectName | String | Name of the project. | ||
TaskId | String |
Tasks.TaskId | ID of task. | |
TaskName | String | Name of the task. | ||
UserId | String |
Users.UserId | Id of a user. | |
UserName | String | 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. |
Retrieves list of all users in the organization. Also, get the details of a user.
The connector 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 connector.
SELECT * FROM Users WHERE Status = 'All' SELECT * FROM Users ORDER BY UserRole DESC
Name | Type | References | SupportedOperators | Description |
UserId [KEY] | String | Id of a user. | ||
UserRole | String | Role of a user. | ||
UserType | String | Type of a user. | ||
CreatedTime | Datetime | Time at which the user was created. | ||
String | Email ID of a user. | |||
IsAssociatedForApproval | Boolean | Check if the user is associated for the approval. | ||
IsClaimant | Boolean | Check if the user is claimant. | ||
IsCustomerSegmented | Boolean | Check if the user is customer segmented. | ||
IsEmployee | Boolean | Check if the user is an employee. | ||
Name | String | Name of the user. | ||
PhotoUrl | String | Photo URL of the user. | ||
RoleId | String | Role ID of a user. | ||
Status | String | Status of the user.
The allowed values are All, Active, Inactive, Invited, Deleted. |
Retrieves list of bills to which the vendor credit is applied.
The connector 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 connector.
For example:
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. |
Get details of a vendor credit.
The connector 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 connector.
Note: VendorCreditId is required in order to query VendorCreditDetails.
For example:
SELECT * FROM VendorCreditDetails WHERE VendorCreditId = '1894545000000083308'
Name | Type | References | SupportedOperators | Description |
VendorCreditId [KEY] | String |
VendorCredits.VendorCreditId | Id of a vendor credit. | |
VendorCreditNumber | String | Number of vendor credit. | ||
VendorId | String | ID of the vendor the vendor credit has been made. | ||
VendorName | String | Name of the vendor the vendor credit has been made. | ||
Adjustment | Decimal | Adjustments made to the vendor credit. | ||
AdjustmentDescription | String | Description of adjustments made to the vendor credit. | ||
ApproverId | String |
Users.UserId | ID of a approver. | |
Balance | Decimal | Total balance of vendor credit. | ||
BillId | String |
Bills.BillId | ID of a Bill. | |
BillNumber | String | Number of a Bill. | ||
ColorCode | String | Color code of vendor credit. | ||
ContactCategory | String | Category of a contact. | ||
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 | Vendor Credit date. | ||
DestinationOfSupply | String | Place where the goods/services are supplied to. | ||
Discount | String | Discount amount applied to vendor credit. | ||
DiscountAccountId | String |
BankAccounts.AccountId | Account ID of the discount. | |
DiscountAmount | Decimal | Amount of the discount. | ||
DiscountAppliedOnAmount | Decimal | Amount applied on discount. | ||
DiscountSetting | String | Settings of discount. | ||
ExchangeRate | Decimal | Exchange rate applied for vendor credits. | ||
FiledInVatReturnId | String | VAT return ID of bill which was filed. | ||
FiledInVatReturnName | String | VAT return name of bill which was filed. | ||
FiledInVatReturnType | String | VAT return type of bill which was filed. | ||
GstNo | String | GST number. | ||
GstReturnDetailsReturnPeriod | String | Return period of GST return details. | ||
GstReturnDetailsStatus | String | Status of GST return details. | ||
GstTreatment | String | Choose whether the vendor credit is GST registered/unregistered/consumer/overseas. | ||
HasNextVendorcredit | Boolean | Check if there is nect vendor credit. | ||
IsDiscountBeforeTax | Boolean | Check if discount is applicable before tax. | ||
IsInclusiveTax | Boolean | Check if tax is inclusive. | ||
IsPreGst | Boolean | Check if pre GST is applied. | ||
IsReverseChargeApplied | Boolean | Check if the reverse charge is applied. | ||
LastModifiedTime | Datetime | The time of last modification of the vendor credits. | ||
Notes | String | Notes of vendor credit. | ||
Orientation | String | Orientation of vendor credit. | ||
PageHeight | String | Height of a page. | ||
PageWidth | String | Width of a page. | ||
PricePrecision | Integer | The precision for the price. | ||
ReasonForDebitNote | String | Specified reason for debit note. | ||
ReferenceNumber | String | Reference number of vendor credit. | ||
SourceOfSupply | String | Source of supply. | ||
Status | String | Status of the vendor credit. | ||
SubTotal | Decimal | Sub total of vendor credits. | ||
SubTotalInclusiveOfTax | Decimal | Amount if the subtotal is inclusive of tax. | ||
SubmittedBy | String | Detail of the user who has submitted the vendor credit. | ||
SubmittedDate | Date | Date when vendor credit was submitted. | ||
SubmitterId | String |
Users.UserId | ID of vendor credit submitter. | |
TaxTreatment | String | VAT treatment for the Vendor Credit. | ||
TemplateId | String | ID of a template. | ||
TemplateName | String | Name of a template. | ||
TemplateType | String | Type of a template. | ||
Total | Decimal | Total of vendor credits. | ||
TotalCreditsUsed | Decimal | Total credits used for this vendor credit. | ||
TotalRefundedAmount | Decimal | Total amount refunded for a vendor credit. | ||
VatTreatment | String | VAT treatment for the vendor credit. |
Retrieves list of line items from vendor credits.
The connector 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 connector.
Note: VendorCreditId is required in order to query VendorCreditLineItems.
For example:
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 connector 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 connector.
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. |
Get details of a Vendor Payment.
The connector 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 connector.
Note: PaymentId is required in order to query VendorPaymentDetails.
For example:
SELECT * FROM VendorPaymentDetails WHERE PaymentId = '1894553000000085277'
Name | Type | References | SupportedOperators | Description |
PaymentId [KEY] | String |
VendorPayments.PaymentId | Id of a payment. | |
VendorId | String |
VendorPayments.VendorId | ID of the vendor the vendor payment has been made. | |
VendorName | String | Name of the vendor the vendor payment has been made. | ||
VendorpaymentRefunds | String | Refunds of a vendor payment. | ||
AchPaymentStatus | String | Status of ACH Payment. | ||
Amount | Decimal | Amount of the vendor payments. | ||
Balance | Decimal | Total amount of a vendor payment. | ||
BillingAddress | String | Billing address of a vendor payment. | ||
BillingAddressAttention | String | Name of a person in billing address. | ||
BillingAddressCity | String | City of a billing address. | ||
BillingAddressCountry | String | Country of a billing address. | ||
BillingAddressFax | String | Fax of a billing address. | ||
BillingAddressPhone | String | Phone number of a billing address. | ||
BillingAddressState | String | State of a billing address. | ||
BillingAddressStreet2 | String | Street two of a billing address. | ||
BillingAddressZip | String | ZIP code of a billing address. | ||
CheckDetailsAmountInWords | String | Checking details with amount in words. | ||
CheckDetailsCheckId | String | Id of check. | ||
CheckDetailsCheckNumber | String | Number if check. | ||
CheckDetailsCheckStatus | String | Status of check. | ||
CheckDetailsMemo | String | Memo of check details. | ||
CheckDetailsTemplateId | String | Template ID of a vendor payment in check. | ||
CreatedTime | Datetime | Time at which the vendor payment was created. | ||
CurrencyId | String |
Currencies.CurrencyId | Currency ID of the customer's currency. | |
CurrencySymbol | String | Currency symbol of the customer's currency. | ||
Date | Date | Date of a vendor payment. | ||
Description | String | Description of the vendor payment. | ||
ExchangeRate | Decimal | Exchange rate of a vendor payment. | ||
IsAchPayment | Boolean | Check if the payment if done with ACH payment. | ||
IsPaidViaPrintCheck | Boolean | Check if vendor payment paid via print check. | ||
IsPreGst | Boolean | Check if vendor payment includes pre GST. | ||
IsTdsAmountInPercent | Boolean | Check if the TDS amount is in percent. | ||
LastModifiedTime | Datetime | The time of last modification of the vendor payment. | ||
OffsetAccountId | String |
BankAccounts.AccountId | Id of an offset account. | |
OffsetAccountName | String | Name of an offset account. | ||
PaidThroughAccountId | String |
BankAccounts.AccountId | Account ID from which vendor payment has been made. | |
PaidThroughAccountName | String | Account name from which vendor payment has been made. | ||
PaidThroughAccountType | String | Account type from which vendor payment has been made. | ||
PaymentMode | String | Mode through which payment is made. | ||
PaymentNumber | Integer | Number through which payment is made. | ||
ProductDescription | String | Description of the product. | ||
PurposeCode | String | Purpose code of vendor payment. | ||
ReferenceNumber | String | Reference number of a vendor payment. | ||
TaxAccountId | String |
BankAccounts.AccountId | ID of a tax account. | |
TaxAccountName | String | Name of a tax account. | ||
TaxAmountWithheld | Decimal | Amount withheld for tax. | ||
TdsTaxId | String | ID of a TDS tax. |
Retrieves list of all the payments made to your vendor.
The connector 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 connector.
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.. |
NOTE: Stored procedures are not currently supported. See the above note for details.
Stored procedures* are available to complement the data available from the Data Model. It may be necessary to update data available from a view using a stored procedure* because the data does not provide for direct, table-like, two-way updates. In these situations, the retrieval of the data is done using the appropriate view or table, while the update is done by calling a stored procedure. Stored procedures* take a list of parameters and return back a dataset that contains the collection of tuples that constitute the response.
Name | Description |
DownloadAttachment | Returns the file attached to the invoice. |
DownloadExpenseReceipt | Returns the receipt attached to the expense. |
DownloadRetainerInvoiceAttachment | Returns the file attached to the retainer invoice. |
GetOAuthAccessToken | Gets an authentication token from Zoho Books. |
GetOAuthAuthorizationURL | 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. |
RefreshOAuthAccessToken | Refreshes the OAuth access token used for authentication with ZohoBooks. |
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 | Description |
SourceId | String | True | ID of categories like Invoices, Purchase Orders, Bills, SalesOrders. |
SourceType | String | True | Type of source
The allowed values are invoices, purchaseorders, bills, salesorders. |
FileLocation | String | True | The folder path to download the file to. |
Name | Type | Description |
Status | String | Stored procedure* execution status. |
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 | Description |
ExpenseId | String | True | ID of an expense. |
FileLocation | String | True | The folder path to download the file to. |
ExportFormat | String | False | The format of the document to download (HTML or markdown).
The default value is html. |
AsZip | String | False | Download the file or folder in a zip format. |
Name | Type | Description |
Status | String | Stored procedure* execution status. |
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 | Description |
RetainerInvoiceId | String | True | ID of retainer invoice. |
DocumentId | String | True | ID of document. |
FileLocation | String | True | The folder path to download the file to. |
Name | Type | Description |
Status | String | Stored procedure* execution status. |
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 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 connector can offload to the data source.
When working with data sources that do not support SQL-92, you can query the sys_sqlinfo view to determine the query capabilities of the underlying APIs, expressed in SQL syntax. The connector offloads as much of the SELECT statement processing as possible to the server and then processes the rest of the query in memory.
Below is an example data set of SQL capabilities. The following result set indicates the SELECT functionality that the connector 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 |
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 Data Sync version needed to access this driver. | Standard, Starter, Professional, Enterprise |
DATASYNCCATEGORY | The 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. |
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 advanced configurations 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. 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 |
InitiateOAuth | Set this property to initiate the process to obtain or refresh the OAuth access token when you connect. |
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 |
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 |
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. |
Timeout | The value in seconds until the timeout error is thrown, canceling the operation. |
This section provides a complete list of connection properties you can configure.
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 OAuth properties you can configure.
Property | Description |
InitiateOAuth | Set this property to initiate the process to obtain or refresh the OAuth access token when you connect. |
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. |
Set this property to initiate the process to obtain or refresh the OAuth access token when you connect.
string
"OFF"
The following options are available:
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 connector exchanges the OAuthVerifier for the OAuth authentication tokens and saves them, encrypted, to the specified file. Set InitiateOAuth to GETANDREFRESH automate the exchange.
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 SSL properties you can configure.
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.
Certificates are validated as trusted by the machine based on the System's trust store. The trust store used is the 'javax.net.ssl.trustStore' value specified for the system. If no value is specified for this property, Java's default trust store is used (for example, JAVA_HOME\lib\security\cacerts).
Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.
This section provides a complete list of schema properties you can configure.
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%\\ZohoBooks Data Provider\Schema"
The path to a directory which contains the schema files for the connector (.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%\\ZohoBooks Data Provider\Schema" with %APPDATA% being set to the user's configuration directory:
Platform | %APPDATA% |
Windows | The value of the APPDATA environment variable |
Mac | ~/Library/Application Support |
Linux | ~/.config |
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 connector.
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 connector.
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 miscellaneous properties you can configure.
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. |
Timeout | The value in seconds until the timeout error is thrown, canceling the operation. |
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 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 connector throws an exception.