Querying, updating, and deleting data with the Easysoft Zoho Books ODBC driver

Contents

• Base tables
• Comment tables and views
  • SELECT statements
  • UPDATE statements
  • INSERT statements
  • DELETE statements
• WHERE clause limitations
  • Why doesn't the Easysoft Zoho Books ODBC driver use both methods to get the rows?
  • Which columns work under LIST and GET?
• Using the Easysoft Zoho Books ODBC driver under Microsoft Access
• Zoho Books API limitations
  • DELETE employee
  • Attachments
  • Exchange rates
  • Error 45
  • Organization
  • Project
  • Sales_Receipts
  • Zoho Books trials
• Special tables
  • API table
  • LastId table
  • JSON formatted columns
  • DELETE statements
• Zoho Books tables
  • Bank_Account
  • Bank_Rule
  • Bank_Transaction
  • Bill
  • Chart_Of_Account
  • Contact
  • Credit_Note
  • Currency
  • Customer_Payments
  • Delivery_Challan
  • Employee
  • Estimate
  • Expense
  • Invoice
  • Item
  • Journal
  • Opening_Balance
  • Organization
  • Project
  • Purchase_Order
  • Recurring_Bill
  • Recurring_Expense
  • Recurring_Invoice
  • Retainer_Invoice
  • Salesorder
  • Salesreceipt
  • Task
  • Tax
  • Time_Entry
  • Users
  • Vendor_Credit
  • Vendor_Payment
• Appendix A
  • SQL functionality
    • String functions
    • Numeric functions
    • Date and time functions
• Appendix B
  • Zoho Books tables and columns
  • Multilingual Zoho Books versions

Base tables

These tables correlate with their equivalent Zoho Books API call.

¹ Issue with underlying Zoho API call. Refer to the notes for the affected tables.

² SELECT query limitations. Refer to the notes for the affected tables.

³ SQL statement limitations. Refer to the notes for the affected tables.

Comment tables and views

All Easysoft comment tables support SELECT, INSERT, and DELETE statements. The Bill_Comment, Credit_Note_Comment, Project_Comment, and Vendor_Credit_Comment tables don't support UPDATE statements as the underlying Zoho Books APIs for these tables don't currently support updates.

Views are SELECT only.

Comments automatically generated by Zoho Books can't be updated or deleted.

SELECT statements

To retrieve all comments for a record:

SELECT
    *
FROM
    Invoice_Comment
WHERE
    Invoice_Id = '579676000000050234'

You need to pass in the Id column and value for the record you want to get the comments from. Each table or view has its own Id column for example the Bill_Comment table has Bill_Id.

UPDATE statements

You can update a comment by passing in both the Id and Comment_Id columns for the table. For example:

UPDATE Invoice_Comment
SET
    Description = 'Invoice updated. 2'
WHERE
    Invoice_Id = '579676000000050234'
    AND Comment_Id = '579676000000061089'

If you try to update a system generated comment or history record, you'll get the error:

[Easysoft Zoho Books ODBC Driver]Error <4073: You cannot edit system generated or customer comments.

INSERT statements

You can insert a comment by running:

INSERT INTO
    Invoice_Comment (
        Invoice_Id,
        Description,
        Show_Comment_To_Clients,
        Payment_Expected_Date
    )
VALUES
    (
        '579676000000050234',
        'Comment visible to customer',
        1,
        '2024-08-29'
    )

The columns Show_Comment_To_Clients and Payment_Expected_Date are only applicable to the table Invoice_Comment and are optional.

DELETE statements

You can delete a comment by running:

DELETE FROM Invoice_Comment
WHERE
    Invoice_Id = '579676000000050234'
    AND Comment_Id = '579676000000165022'

Both the Id and Comment_Id columns are required. If you try to delete a system generated comment, history record, or customer comment you'll get the error:

[Easysoft Zoho Books ODBC Driver]Error <4073: You cannot delete system generated or customer comments.>

WHERE clause limitations

Any of the columns in a table or view can be used in a WHERE clause. This can lead to incorrect result sets being returned. To ilustrate this issue, here's a very simple query:

SELECT
    Contact_Id,
    Contact_Name,
    Opening_Balance_Amount
FROM
    Contact
WHERE
    Contact_Id = '579676000000093092'

This correctly returns one row from my test organization, which has a non-zero opening balance:

Now to show the problem. This query returns no rows:

SELECT
    Contact_Id,
    Contact_Name,
    Opening_Balance_Amount
FROM
    Contact
WHERE
    Opening_Balance_Amount <> 0

I'll try to explain the reason for no rows.

The Easysoft Zoho Books ODBC driver has to use the Zoho Books API to access data in Zoho Books. In the Zoho Books API, there are two methods that can be used to get data from Contact: LIST and GET. If you specify the Id column (in my example, this is Contact_Id = '579676000000093092') the GET method is called.

The GET method gets one record from Zoho Books and costs 1 API call. It gets almost all the data relating to that record including the Opening_Balance_Amount.

When you do not specify the Id column, the Easysoft Zoho Books ODBC driver has to call the LIST method. This is where the problem occurs. The LIST method costs 1 API call for each array of rows returned. An array can contain up to a maximum 200 rows. In the case of the query that returns no rows, you can't query the Opening_Balance_Amount column with the LIST method. So in this case, the Easysoft Zoho Books ODBC driver gets all the rows but when filtered locally Opening_Balance_Amount always contains null, and so NOT results are <> 0. This costs 1 API call per 200 rows in the Contact table.

To work around this, you need to get the Zoho Books ODBC driver to make both LIST and GET calls. To do this, you need to use the table in the query twice. For example:

SELECT
    l.Contact_Id,
    l.Contact_Name,
    g.Opening_Balance_Amount
FROM
    Contact l,
    Contact g
WHERE
    g.Contact_Id = l.Contact_id
    AND g.Opening_Balance_Amount <> 0

Contact l This is the first table queried. The LIST method is called and Contact_Id is returned for all records.

Contact g The second table is called. Passing in the Contact_Id forces the Easysoft Zoho Books ODBC driver to use the GET method, which does return Opening_Balance_Amount. This can then be filtered locally and the correct rows are returned.

Why doesn't the Easysoft Zoho Books ODBC driver use both methods to get the rows?

The answer is down to the number of API calls it would need to make to get all data for all the rows. The LIST method costs 1 API call per 200 rows and GET costs 1 API call per row. If you have a table that has 1000 rows of data, you need to 1005 API calls to get all the data by using LIST (5) and GET (1000).

One advantage the LIST method has over the GET method is that LIST can be called with some common query parameters for most of the API calls, which reduces the total number of API calls that need to be made to get data out. LIST also gives you most of what you need without giving you the full details.

In this example, I want to get a list of all customers starting with S that have an outstanding balance of over 1000. I want the following columns:

SELECT
    Contact_Id,
    Contact_Name,
    First_Name,
    Last_Name,
    Email,
    Outstanding_Receivable_Amount
FROM
    Contact
WHERE
    Contact_Name LIKE 'S%'
    AND Outstanding_Receivable_Amount > 1000
    AND Contact_Type = 'customer'

Let's say that out of the 1000 contacts, 80 contacts start with the letter S and only 20 of those have an Outstanding_Receivable_Amount of over 1000. In the above example, all the columns being returned can come from a LIST call.

The Contact API LIST call allows the driver to filter by Contact_Name, which means that in this test case, the Easysoft Zoho Books ODBC driver would only use 1 API call to get the 80 contacts from Zoho. The Outstanding_Receivable_Amount and Contact_Type would then be filtered locally to return just the 20 rows expected.

Which columns work under LIST and GET?

This document contains a full list of all base tables, the available columns and API types, along with whether they can be used with LIST or GET.

Zoho Books supports several regions:

• Australia
• Bahrain
• Canada
• Global
• India
• Saudi Arabia
• UAE
• UK
• USA

Use Appendix B to find out whether the table, column, or method is available in your region. For example, the Item table has a column named Tax_Name. In all regions apart from Canada, you can filter on this column with either LIST or GET. If you want to filter on this column with Canadian Zoho Books data, you can only use the GET method.

Appendix B also shows API data types:

Some of the data returned under LIST differs from GET. Refer to Project for an example of this.

Using the Easysoft Zoho Books ODBC driver under Microsoft Access

You can link all tables and views without any issue in all versions of Microsoft Access from 2013 onwards. What you need to be aware of is what happens if you open a table directly in Microsoft Access rather than run a query or SQL statements.

When you open a base table such as Contact in Microsoft Access, Access sends this query to the Easysoft driver:

SELECT Contact_Id FROM Contact

This causes the driver to ask Zoho for a list of all Contact_Id values. This costs 1 Zoho Books API call for every 200 records you have. When the first 10 rows are passed back from the Easysoft driver to Microsoft Access, Access then sends a second query to the driver:

SELECT
    *
FROM
    Contact
WHERE
    Contact_Id = X1
    OR Contact_Id = X2
    OR Contact_Id = X3
    OR Contact_Id = X4
    OR Contact_Id = X5
    OR Contact_Id = X6
    OR Contact_Id = X7
    OR Contact_Id = X8
    OR Contact_Id = X9
    OR Contact_Id = X10

where X1 to X10 are the 10 Contact_Id values passed back from the first query.

This is where the problem lies. The query uses 10 API calls.

The next 10 rows from the first query are passed from the Easysoft driver to Access and then the second query is sent again from Access to the Easysoft driver, using another 10 API calls.

If your contact table has, say, 1000 rows, by the time you have all the data returned you've used 1005 API calls.

Zoho Books API limitations

This is a list of problems I've found while testing the Zoho Books ODBC driver and writing this document. As these limitations are at the Zoho Books end, there's nothing we can do about them other than hope they go away, as Zoho release new API versions.

These limitations were found using API version 3.

DELETE employee

The example shown in the API documentation does not actually work and does not specify the employee_id. Reported to Zoho Books support on 5-Aug-2024.

Attachments

Getting a list of file attachments such as a receipt attached to an expense is restricted to getting the first attachment only.

INSERTing attachments does not currently work. Reported to Zoho Books support on 5-Aug-2024.

Exchange rates

Getting exchange rates without supplying the exchange_rate_id does not return the correct rate. Reported to Zoho Books support on 5-Aug-2024.

Error 45

[Easysoft Zoho Books ODBC Driver]Error 45: The API call for this organization has exceeded the maximum call rate limit of <n>>

You are limited to <n> API calls per Zoho Books organization. When you reach this limit, you need to wait until some of the API calls pass the 24 hour mark before you can use the Easysoft driver again. <n> is defined by your Zoho Books subscription type.

Organization

There is currently no Zoho Books API documentation on this.

Project

The views that the Easysoft Zoho Books ODBC driver presents use the Zoho Books Project API. The [<Get a Project>] view uses the Project_User API. The [<List Users>] view uses Project_Users API. The result sets that these return are different. For example, [<Get a Project>]:

GET https://www.zohoapis.com/books/v3/projects/579676000000161054/users?organization_id=20099941201

returns the user array:

{
    "user_id": "579676000000050001",
    "is_current_user": true,
    "user_name": "zoho2",
    "email": "test@easysoft.com",
    "user_role": "admin",
    "role_id": "579676000000000679",
    "status": "active",
    "rate": 0.00,
    "budget_hours": 0,
    "budget_hours_in_time": "00:00",
    "total_hours": "00:04",
    "billed_hours": "00:00",
    "un_billed_hours": "00:04",
    "billable_hours": "00:04",
    "non_billable_hours": "00:00",
    "staff_role": "staff",
    "staff_status": "active"
},

Using [<List Users>] returns:

{
    "user_id": "579676000000050001",
    "is_current_user": true,
    "user_name": "zoho2",
    "email": "test@easysoft.com",
    "user_role": "admin",
    "role_id": "579676000000000679",
    "user_photo_url": "https://contacts.zoho.eu/file?t=user&ID=20099940566&fs=thumb",
    "status": "active",
    "rate": 0.00,
    "is_timer_running": false,
    "timer_started_date": "",
    "timer_running_task": "",
    "budget_hours": 0,
    "budget_hours_in_time": "00:00",
    "un_billed_hours": "00:00",
    "billed_hours": "00:00",
    "total_hours": "00:00"
},

In the two sample responses, the total_hours and un_billed_hours columns have different values. This is reason why we provide two views so you can choose the one that contains the data you need.

Sales_Receipts

This is not included in the Zoho Books API documentation.

Zoho Books trials

If you use Zoho Books on a trial license, the Zoho Books API still allows you to read the data in read-only mode after the trial expires. If you try doing things like bulk email through the API, the error message returned does not indicate that your trial has expired.

Special tables

API table

This table can be used to directly access the Zoho Books API:

You can use SELECT statements to access the API. The GET method selects data, POST or PUT inserts or update data and DELETE removes data.

Use a WHERE clause containing the URL, method, and when required RequestBody. For example:

SELECT
    *
FROM
    API
WHERE
    URL = '/books/v3/bankaccounts/rules?account_id=576438000000118001'
    AND Method = 'GET'
SELECT
    *
FROM
    API
WHERE
    URL = '/books/v3/bankaccounts/'
    AND Method = 'GET'
SELECT
    *
FROM
    API
WHERE
    URL = '/books/v3/bankaccounts/576438000000118001'
    AND Method = 'GET'
SELECT
    *
FROM
    API
WHERE
    URL = 'ContactGroups/fd33e5d2-3f95-4b1b-a405-7ed7067b9c22/Contacts'
    AND Method = 'DELETE'

If you have more than one Zoho Books organization, you have to specify which organization_id you're using. For example:

SELECT
    *
FROM
    API
WHERE
    URL = '/books/v3/bankaccounts/?organization_id=20100353410'
    AND Method = 'GET'

Specify the columns in the URL and Method in the WHERE clause. Otherwise, you'll get an error.

If you send invalid JSON in the RequestBody, the API will try to process the request and the appropriate response will be sent back.

Warning PUT and POST work in different ways in the Zoho Books API. Make sure you check the API documentation to be 100% sure you're using the correct method when inserting or updating records.

Error code 404

Zoho Books returns 404 for any error that occurs. For example:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/estimates/576438000000189002/comments'
    AND Method = 'GET'

The Estimate_Id value (576438000000189002) is not a valid estimate. You need to check the ResponseBody column:

Code: 404
ResponseType: application/json;charset=UTF-8
ResponseBody: {"code":1002,"message":"Quote does not exist."}

Likewise:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/invoices/576438000000189002/comments'
    AND Method = 'GET'

Code: 404 ResponseType: application/json;charset=UTF-8 ResponseBody: {"code":5,"message":"Invalid URL Passed"}

In the ResponseBody, the code number returned is often the same across different URLs so if something doesn't exist you will get a 1002 code:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/invoices/576438000000189002'
    AND Method = 'GET'

Code: 404 ResponseType: application/json;charset=UTF-8 ResponseBody: {"code":1002,"message":"Resource does not exist."}

LastId table

Use this table to find out what's going on at the Zoho Books API end after you've run an INSERT, UPDATE, or DELETE statement.

The table contains the following columns:

Successful inserts will always update the LastID table.

UPDATE and DELETE statements only change this table if at least one record is updated or deleted. If more than one record is updated or deleted, only the Id of the last record altered will be returned. If you attempt to update or delete but the statement does not alter any records, no data will be returned.

The data returned from the LastId table is only applicable to the last action on the current connection.

JSON formatted columns

Some of the columns used in the INSERT or UPDATE statements need to be in an JSON format such as the Bill table's Line_Items column. For example:

[
    {
        "line_item_id": "576438000000131023",
        "account_id": "576438000000000415",
        "account_name": "IT and Internet Expenses",
        "description": "Dell Laser Printer",
        "bcy_rate": 100,
        "rate": 100,
        "quantity": 1,
        "tax_type": "tax",
        "tax_percentage": 0,
        "item_total": 100,
        "item_order": 1,
        "is_billable":False
    },
    {
        "line_item_id": "576438000000136009",
        "account_id": "576438000000000415",
        "account_name": "IT and Internet Expenses",
        "description": "Dell Computer",
        "bcy_rate": 1233,
        "rate": 1233,
        "quantity": 4,
        "tax_type": "tax",
        "tax_percentage": 0,
        "item_total": 4932,
        "item_order": 2,
        "is_billable": False
    },
    {
        "line_item_id": "576438000000137009",
        "account_id": "576438000000000415",
        "account_name": "IT and Internet Expenses",
        "description": "Dell Laser Paper",
        "bcy_rate": 3,
        "rate": 3.5,
        "quantity": 16,
        "tax_type": "tax",
        "tax_percentage": 0,
        "item_total": 56,
        "item_order": 3,
        "is_billable": False
    }
]

In this example, I have only shown the lines that contain data. NULLs or empty strings have been left out.

In the Zoho Books API, JSON has this format:

• Data is in name/value pairs.
• Data is separated by commas.
• Curly braces ({}) hold objects.
• Square brackets ([]) hold arrays.

In the example data shown above, the square brackets round the data denote an array that's separated by commas. Each {<group of objects>} denotes one of the lines on the bill and "quantity":16 is a data name/value pair.

All the data names in the Zoho Books API are in lower case. JSON is case sensitive when it comes to these names so putting them in upper or mixed case will result in some strange errors.

In the API we connect to, JSON values must be one of the following data types:

• String
• Number
• Boolean
• Array
• Object

Timestamps are returned as a string in the ISO 8601 YYYY-MM-DDThh:mm:ssTZD format. For example:

"last_modified_time":"2024-06-19T08:04:44-0400"

Dates are returned as a string in the YYYY-MM-DD format. For example:

"date":"2024-06-05"

In some of the JSON returned, you may get something like:

"tags":[]

This denotes an empty array.

String data needs to be escaped.

• Backslash becomes \\
• Backspace becomes \b
• Form feed becomes \f
• Newline becomes \n
• Carriage return becomes \r
• Tab becomes \t
• Double quote becomes \"

If you need to clear the contents of a JSON column, you need to set the value to [].

UPDATE Invoice
SET
    Contact_Persons = '[]'
WHERE
    Invoice_Id = '579676000000050260'

Setting the value to empty string ('') or NULL will not work as the Zoho Books API does not support this.

DELETE statements

Where the table supports a DELETE statement, there are two methods that can be used to delete records. Directly by specifying the table. For example:

DELETE FROM Bank_Account
WHERE
    Account_Id = '579676000000088102'

Indirectly by using the API table. For example:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/bankaccounts/579676000000109001'
    AND Method = 'DELETE'

If you're just deleting one record and you have the Id of the record you want to delete, the API method is faster and uses fewer API calls than going by using the table.

But if you want to delete multiple records at the same time, you can go through the table and send something like:

DELETE FROM Bank_Account
WHERE
    Balance = 0

This deletes all records matching the specified criteria.

Note Once a DELETE statement or API Method = 'DELETE' is called, any rows that match will be deleted. There is no undo option and no way of getting the rows back.

Zoho Books tables

Bank_Account

Supports: SELECT, INSERT, UPDATE, and DELETE

SELECT statements

The Zoho Books API always sends back the Account_Number column as xxxx followed by the last 4 digits of your account number.

INSERT statements

You can create new Bank_Account records by sending:

INSERT INTO
    Bank_Account (
        Account_Name,
        Account_Type,
        Account_Number,
        Routing_Number,
        Currency_Code
    )
VALUES
    (
        'Test COOP Account',
        'bank',
        '80000009823',
        '123456789',
        'GBP'
    )

UPDATE statements

You can, for example, change the Account_Name of a Bank_Account record by sending:

UPDATE Bank_Account
SET
    Account_Name = 'COOP Account'
WHERE
    Account_Id = '576438000000114002'

DELETE statements

You can delete a Bank_Account record by sending:

DELETE FROM Bank_Account
WHERE
    Account_Id = '576438000000114002'

Using the API table to delete an account

You can also delete a single record by running:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/bankaccounts/579676000000109001'
    AND Method = 'DELETE'

Using the API table to deactivate or activate an account

Use something like:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/bankaccounts/579676000000109001/status/<X>'
    AND Method = 'POST'

where <X> is one of:

Using the API table to import bank or credit card statements

Create the JSON data you want to import in this format:

{
    "account_id": "579676000000107014",
    "start_date": "2024-07-01",
    "end_date": "2024-09-01",
    "transactions": [
        {
            "transaction_id": "B00001",
            "date": "2024-08-01",
            "debit_or_credit": "credit",
            "amount": 100,
            "payee": "Bowman and Co",
            "description": "Electronics purchase",
            "reference_number": "Ref-2134"
        },
        {
            "transaction_id": "B00001",
            "date": "2024-07-01",
            "debit_or_credit": "credit",
            "amount": 200,
            "payee": "Bowman and Co",
            "description": "Electronics purchase x2",
            "reference_number": "Ref-1134"
        }
    ]
}

Then call the API table by using:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/bankstatements'
    AND Method = 'POST'
    AND RequestBody = '<JSON_Data>'

The response back if that works is:

Code: 201
 ResponseType: application/json;charset=UTF-8
 ResponseBody:  
 {
      "code":0,
      "message":"Your bank statement has been imported."
 }

Using the API table to get the last statement

Run something like:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/bankaccounts/579676000000107014/statement/lastimported'
    AND Method = 'GET'

Using the API table to delete a record.

Run something like:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/bankaccounts/579676000000107014/statement/579676000000108021'
    AND Method = 'DELETE'

Bank_Rule

Supports: Nothing

Bank_Rule in the Zoho Books API does not function correctly.

Do not use this table.

Bank_Transaction

Supports: Nothing

Do not use this table.

Bill

Supports: SELECT, INSERT, UPDATE, and DELETE

UPDATE statements

UPDATE statements can be used to change some of the header details such as Due_Date:

UPDATE Bill
SET
    Due_Date = '2024-06-21'
WHERE
    Bill_Id = '576438000000118046'

UPDATE statements can't be used to change a Billing_Address_City as this is part of the Customer address and it needs to be changed in the Customer record. If you attempt to change a column that's not updatable, the Zoho Books API may return 200 (Success) but nothing changes.

UPDATE can also be used to add, amend, and delete line items. When you update Line_Items you can add, update, and delete lines at the same time. Zoho simply replaces the lines in your bill with the JSON data you send.

Adding a line item

The easiest way to add an additional line item is to get the JSON column back from Zoho by running:

SELECT
    Line_Items
FROM
    Bill
WHERE
    bill_id = '576438000000118046'

The JSON returned will be in the format:

[{row 1 data},{row 2 data},{row n data}]

There will be one object per row of data. Change the JSON data returned so that between the last } and ] add a comma and then your JSON object:

{
    "item_order": 3,
    "account_name": "IT and Internet Expenses",
    "account_id": "576438000000000415",
    "description": "Dell Laser Paper",
    "rate": 3.50,
    "quantity": 16,
    "item_total": 56.00
}

Then send the JSON back by using:

UPDATE Bill
SET
    Line_Items = '<JSON_Data>'
WHERE
    bill_id = '576438000000118046'

Removing a line item

The easiest way to remove a line item is to get the JSON column back from Zoho Books by running:

SELECT
    Line_Items
FROM
    Bill
WHERE
    bill_id = '576438000000118046'

The JSON array returned will contain a set of objects that each have the data name line_item_id on:

[
    {
        "line_item_id": "1"
    },
    {
        "line_item_id": "2"
    },
    {
        "line_item_id": "n"
    }
]

If you remove the comma and object "{ "line_item_id": "2"} then send an UPDATE statement, the line will be removed.

Amending a line item

This is the same as removing a line item but instead of taking an object out, you simply change one or more of the data name: data value sets.

INSERT statements

Insert statements are very straightforward, but make sure that the Vendor_Id sent is a valid Contact_Id in the Contact table where Contact_Type = 'vendor'.

JLine=[
    {
        "item_order": 1,
        "account_name": "IT and Internet Expenses",
        "account_id": "576438000000000415",
        "description": "Dell Laser Paper",
        "rate": 3.50,
        "quantity": 16,
        "item_total": 56.00
    }
]

         SQL="INSERT INTO Bill (Bill_Number, Vendor_Id, [Date], Due_Date, Currency_Code, Line_Items) VALUES ('BILL-002', '576438000000118013', '2024-06-25', '2024-07-25', 'USD', '" & JLine & "')"

Date and Due_Date need to be passed in in the format yyyy-mm-dd.

DELETE statements

DELETEs can be done by using an SQL DELETE statement or by using the API table. For example:

DELETE FROM Bill
WHERE
    Bill_Id = '579676000000088074'
SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/bills/579676000000089067'
    AND Method = 'DELETE'

DELETE statements are not limited to Bill_Id. You can delete a range of Bills by using:

DELETE FROM Bill
WHERE
    [Date] = '2024-07-01'

But be careful as this type of query will delete all bills it finds that match the specified WHERE clause criteria.

Using the API table to mark a record as open or void

Use something like:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/bills/579676000000081095/status/<X>'
    AND Method = 'POST'

where <X> is one of:

Using the API table to submit or approve a bill

Use something like:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/bills/579676000000081095/<X>'
    AND Method = 'POST'

where <X> is one of:

submit and approve only work if they have been turned on in the Zoho Books company you're connecting to. If they're not turned on, the ResponseBody is:

{
    "code": 21016,
    "message": "You have not enabled the Approval feature for this organization. Please enable it in Settings."
}

Using the API table to apply credit to a bill

You need to have both the Bill_Id and Vendor_Credit_Id to be able to carry this out. First, create the JSON data to allocate the amounts to each bill.

{
    "apply_vendor_credits": [
        {
            "vendor_credit_id": "579676000000098059",
            "amount_applied": 25
        }
    ]
}

Then apply it to the bill by using:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/bills/579676000000098003/credits'
    AND Method = 'POST'
    AND RequestBody = '<JSON_Data>'

Both the vendor credit and bill must be related to the same contact record.

Using the API table to add comments

First create the JSON data you want to add:

{
    "description": "Test add comment.",
}

Send the comment:

SELECT
    ResponseBody
FROM
    api
WHERE
    url = 'books/v3/bills/579676000000081095/comments'
    AND Method = 'POST'
    and RequestBody = '<JSON_Data>'

The ResponseBody will contain the message Comments added.

Using the API table to update comments

Updating bill comments is not currently supported in the Zoho Books API.

Using the API table to list bill comments and history

This example lists all the comments and history for the Bill_Id sent.

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/bills/579676000000081095/comments'
    AND Method = 'GET'

This API call lists all the comments and history for the Bill_Id sent.

SELECT
    *
FROM
    Bill_Comment
WHERE
    Bill_Id = '579676000000081095'

Using the API table to delete bill comments

This example deletes the selected comment from a bill.

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/bills/579676000000081095/comments/579676000000102002'
    AND Method = 'DELETE'

Chart_Of_Account

Supports: SELECT, INSERT, UPDATE, and DELETE

INSERT statements

INSERT statements can be very simple:

INSERT INTO
    Chart_Of_Account (Account_Name, Description, Account_Type)
values
    ('Test Account', 'Easysoft test COA', 'income')

If you need to add an opening balance to the account you create, refer to Opening_Balance.

UPDATE statements

Use something like the following:

UPDATE Chart_Of_Account
SET
    Description = 'Testing the COA'
WHERE
    Account_Id = '579676000000106021'

DELETE statements

Use something like the following:

DELETE FROM Chart_Of_Account
WHERE
    Account_Name = 'Test Account'

Alternatively, use the API table if you have the Account_Id:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/chartofaccounts/579676000000107086'
    AND Method = 'DELETE'

Using the API table to mark an account record as active or inactive

Use something like:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/chartofaccounts/579676000000107086/<X>'
    AND Method = 'POST'

where <X> is one of:

Using the API table to list account transactons

Use something like:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/chartofaccounts/transactions?account_id=579676000000000352&amount.less_than=25384.04'
    AND Method = 'GET'

Listing transactons

This example SQL query returns a list of transactions for an account's invoices:

SELECT
    *
FROM
    Chart_Of_Account_Transaction
WHERE
    Account_Id = '579676000000000376'
    AND Transaction_Type = 'invoice'

The Account_Id must be specified.

Contact

Supports: SELECT, INSERT, UPDATE, and DELETE

This table is where your customer and vendor (supplier) information is stored. The column Contact_Type denotes the type of contact you have (customer or vendor). A contact can only be of one type. If you have both a customer and vendor with the same name, you need to create two records.

There are two JSON columns in this table, Contact_Persons, which is an array that can contain one or more people's details and Addresses.

INSERT statements

When inserting a new record into the Contact table, the only field required in the API is Contact_Name. For example:

INSERT INTO
    Contact (Contact_Name)
VALUES
    ('J. Jones Lumber')

The API assumes you're creating a customer rather than a vendor. In my test organization, the API defaults are:

• Contact_Type: customer
• Customer_Sub_Type: business
• Is_Linked_With_Zohocrm: 0
• Payment_Terms:0
• Payment_Terms_Label: Due On Receipt
• Currency_Id: 576438000000000059
• Currency_Code: USD
• Outstanding_Receivable_Amount: 0
• Outstanding_Receivable_Amount_Bcy: 0
• Unused_Credits_Receivable_Amount: 0
• Unused_Credits_Receivable_Amount_Bcy: 0
• Status: active
• Track_1099: 0

UPDATE statements

UPDATE statements work in two ways depending on the column type. With the exception of the Status column, the other non-JSON columns can be updated as long as the Zoho Books API supports this. For example:

UPDATE Contact
SET
    Company_Name = 'J. Jones Lumber'
WHERE
    Contact_Id = '576438000000152001'

To alter the Status column, refer to Contact table.

The JSON column Contact_Persons can be updated by sending a JSON string. This can be used to add, edit, or delete people associated with the contact record.

When sending JSON data back to Contact_Persons, you must make sure that you do not send:

is_primary_contact:false

The Zoho Books API only supports sending true. If you don't want to make a person record true, don't specify is_primary_contact in your JSON data.

The Contact_Persons column

In this test, I select Contact_Persons from my Contact record, which returns:

[
    {
        "contact_person_id": "576438000000153022",
        "is_added_in_portal": false,
        "salutation": "Mr.",
        "is_portal_invitation_accepted": false,
        "email": "tim.tester@easysoft.com",
        "first_name": "Tim",
        "last_name": "Tester",
        "phone": "555-222-8888",
        "can_invite": true,
        "is_primary_contact": true,
        "is_sms_enabled_for_cp": false,
        "photo_url": "https://secure.gravatar.com/avatar/63c1cb7cfeb56dc678156af6fe48cca3?&d=mm",
        "communication_preference": {
            "is_sms_enabled": false,
            "is_email_enabled": true,
            "is_whatsapp_enabled": false
        }
    },
    {
        "contact_person_id": "576438000000153029",
        "is_added_in_portal": false,
        "salutation": "Mr.",
        "is_portal_invitation_accepted": false,
        "email": "j.jones@easysoft.com",
        "first_name": "John",
        "last_name": "Jones",
        "phone": "555-666-9999",
        "designation": "Boss",
        "can_invite": true,
        "is_primary_contact": false,
        "is_sms_enabled_for_cp": false,
        "photo_url": "https://secure.gravatar.com/avatar/4f6761051027e0cf9cc43c08596dd77a?&d=mm",
        "communication_preference": {
            "is_sms_enabled": false,
            "is_email_enabled": true,
            "is_whatsapp_enabled": false
        }
    }
]

Next, I remove all occurrences of "is_primary_contact":false, as the API does not support that. I then add a comma between the last } and ] followed by a new record:

{
    "email": "tina.tester@easysoft.com",
    "salutation": "Mrs",
    "first_name": "Tina",
    "last_name": "Tester"
}

Then I send the data using:

UPDATE Contact
SET
    Contact_Persons = '<JSON_Data>'
WHERE
    Contact_Id = '576438000000152001'

To alter a person's details, select the Contact_Persons column, remove all occurrences of "is_primary_contact":false, alter the other parts you want, and then send this to Zoho Books by using an UPDATE statement.

To remove a person's details, select the data, which has this format:

[{person 1 data},{person 2 data},{person n data}]

Remove all occurrences of "is_primary_contact":false. Remove the block of data round the person you want to delete and then execute the update.

Each time an update is sent, the current record is replaced with the data you send, so you can add, edit, and delete all at the same time.

Using the API table to activate or deactivate a record

Contacts can only be made active or inactive by using the API table. Updating the Status column in the table is not supported by the Zoho Books API. Here's an example of making a contact inactive:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/contacts/576438000000059118/inactive'
    AND Method = 'POST'

When the change is made, the ResponseBody column returns:

{"code":0,"message":"The contact has been marked as inactive."}

Using the API table to turn portal access on or off

To turn on portal access, create a JSON data record containing the person or persons you need to give access to:

{"contact_persons":[{"contact_person_id":576438000000158066}]}

Then in the SQL you send:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/contacts/576438000000059118/portal/disable'
    AND Method = 'POST'
    AND RequestBody = '<JSON_Data>'

Replace disable with enable to turn on portal access. Note The contact_person_id needs to be a contact person in the contact record you're sending. Assuming it works, the ResponseBody contain:

{"code":0,"message":"You have disabled the portal for the contact"}

Using the API table to turn payment reminders on or off

Using the API table, send:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/contacts/576438000000059118/paymentreminder/disable'
    AND Method = 'POST'

Change disable to enable to turn on reminders and the ResponseBody will return:

{"code":0,"message":"All reminders associated with this contact have been enabled."}

Using the API table to get statement content

Use something like:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/contacts/576438000000059118/statements/email'
    AND Method = 'GET'
SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/contacts/576438000000059118/statements/email?start_date=2024-01-01&end_date=2024-02-01'

If you don't supply a start_date and end_date, the statement is for the current month.

The ResponseBody contains:

{
    "message": "success",
    "data": {
        "contact_name": "Customer - USD",
        "file_name_without_extension": "statement_Customer-USD",
        "from_email": "test@test.co.uk",
        "subject": "Account Statement from 01 Jan 2024 to 01 Feb 2024",
        "file_name": "statement_3MBrookingsIT-USD.pdf",
        "filter_by": "Status.All",
        "contact_id": "576438000000059118",
        "body":"<br>Dear Customer - USD,
         <br><br><span>It's been a great experience working with you.<br></span>Attached with this email is a list of all transactions for the period between&nbsp;<b>01 Jan 2024</b>&nbsp;to&nbsp;<b>01 Feb 2024</b><span>.<br></span><span>If you have any questions,
         just drop us an email or call us.</span><br><br><br>Regards,
         <br>zoho1<br>ESL Test<br><br><br>","entity_id": "576438000000059118",
        "to_mails_str": "test@test.co.uk",
        "from_emails": [
            {
                "is_org_email_id": false,
                "user_name": "zoho1",
                "organization_contact_id": "576438000000049001",
                "email": "test@easysoft.com",
                "selected": true
            }
        ],
        "to_contacts": [
            {
                "last_name": "Smith",
                "contact_person_id": "576438000000158066",
                "salutation": "Mr.",
                "first_name": "A",
                "email": "a.smith@test.co.uk",
                "selected": true
            },
            {
                "last_name": "Smith",
                "contact_person_id": "576438000000158068",
                "salutation": "Mrs.",
                "first_name": "T",
                "email": "t.smith@ test.co.uk",
                "selected": false
            }
        ],
        "emailtemplates": [
            {
                "email_template_id": "576438000000000024",
                "name": "Default",
                "selected": true
            }
        ],
        "from_address": "null"
    }
}

Blank lines have been removed to save space.

Using the API table to send an email statement

First create the JSON data:

{
    "to_mail_ids": [
        "test@easysoft.com",
        "another@easysoft.com"
    ],
    "cc_mail_ids": [
        "support@easysoft.com"
    ],
    "subject": "Test credit note from ZB",
    "body": "This is a test body text to check the example code."
}

To get Zoho Books to send the email:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/creditnotes/576438000000160023/ statements/email and Method' = 'POST'
    AND RequestBody = '<JSON_Data>'

The ResponseBody will contain:

{"code":0,"message":"Statement has been sent to the customer."}

Using the API table to list comments

Use something like:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/contacts/576438000000059118/comments'
    AND Method = 'GET'

The ResponseBody will contain:

{
    "message": "success",
    "contact_comments": [
        {
            "comment_id": "576438000000158081",
            "contact_id": "576438000000059118",
            "contact_name": "Customer - USD",
            "comments_html_format": "Customer Statement was sent",
            "description": "Customer Statement was sent",
            "commented_by_id": "576438000000049001",
            "commented_by": "zoho1",
            "date": "2024-07-02",
            "date_description": "39 minutes ago",
            "time": "6:00 AM",
            "transaction_type": "contact",
            "is_entity_deleted": false,
            "operation_type": "updated"
        },
        {
            "comment_id": "576438000000162031",
            "contact_id": "576438000000059118",
            "contact_name": "Customer - USD",
            "comments_html_format": "Automated payment reminder disabled",
            "description": "Automated payment reminder disabled",
            "commented_by_id": "576438000000049001",
            "commented_by": "zoho1",
            "date": "2024-07-02",
            "date_description": "about an hour ago",
            "time": "5:00 AM",
            "is_entity_deleted": false,
            "operation_type": "updated"
        },
        {
            "comment_id": "576438000000160071",
            "contact_id": "576438000000059118",
            "contact_name": "Customer - USD",
            "comments_html_format": "Portal enabled for test@test.co.uk",
            "description": "Portal enabled for test@test.co.uk",
            "commented_by_id": "576438000000049001",
            "commented_by": "zoho1",
            "date": "2024-07-02",
            "date_description": "2 hours ago",
            "time": "4:35 AM",
            "is_entity_deleted": false,
            "operation_type": "updated"
        }
    ],
    "page_context": {
        "page": 1,
        "per_page": 200,
        "has_more_page": false,
        "customer_name": "Customer - USD",
        "applied_filter": "RecentActivity.All",
        "sort_column": "date",
        "sort_order": "D"
    }
}

The ResponseBody above has been reduced to just a sample of the data returned.

To list comments, use something like:

SELECT
    *
FROM
    Contact_Comment
WHERE
    Contact_Id = '579676000000093175'

Using the API table to list refunds

Use something like:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/contacts/576438000000059118/refunds'
    AND Method = 'GET'

Using the API table to turn 1099 tracking on or off

Use something like:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/contacts/576438000000059118/track1099'
    AND Method = 'POST'

Change track1099 to untrack1099 to stop the 1099 tracking.

Using the API table to manage addresses

Addresses can only be created, edited, or deleted by using the API table.

Adding an address

Before you send the SQL, you need to create a new JSON group of objects that contains the address details. For example:

{
    "phone": "505-111-2222",
    "attention": "Paul Smith",
    "address": "The Main Office",
    "street2": "Big Lumber Yard",
    "city": "Ripon",
    "state": "Colorado",
    "state_code": "CO",
    "zip": 22334,
    "country_code": "US"
}

Then send it through the API table by using a POST method:

SELECT
    *
FROM
    API
WHERE
    URL = '/books/v3/contacts/576438000000152001/address'
    AND Method = 'POST'
    AND RequestBody = '<JSON_Data>'

where 576438000000152001 is the Contact_Id of the contact to add the address to and <JSON_Data> is the address group. Assuming this works, the Response column contains:

{
    "code": 0,
    "message": "The address has been created.",
    "address_info": {
        "address_id": "576438000000149024",
        "address_type": "",
        "attention": "Paul Smith",
        "address": "The Main Office",
        "street2": "Big Lumber Yard",
        "city": "Ripon",
        "state_code": "",
        "state": "Colorado",
        "zip": "22334",
        "country": "",
        "country_code": "",
        "phone": "505-111-2222",
        "fax": "",
        "latitude": "",
        "longitude": "",
        "update_existing_transactions_address": ""
    }
}

Editing an address

To edit an address, you need to know the Contact_Id of the contact record and the Address_Id of the record being edited.

 {
    "phone": "505-111-2222",
    "attention": "Paul Smith",
    "address": "Admin Office",
    "street2": "Big Lumber Yard",
    "city": "Ripon",
    "state": "Colorado",
    "state_code": "CO",
    "zip": 22334,
    "country_code": "US"
}

This time, I have just changed the address part. This is then sent by using the PUT method with the address_id in the URL:

SELECT
    *
FROM
    API
WHERE
    URL = '/books/v3/contacts/576438000000152001/address/576438000000149024'
    AND Method = 'PUT'
    AND RequestBody = '<JSON_Data>'

If this succeeds, the Response column contains:

{
    "code": 0,
    "message": "The address has been updated.",
    "address_info": {
        "address_id": "576438000000149024",
        "address_type": "",
        "attention": "Paul Smith",
        "address": "Admin Office",
        "street2": "Big Lumber Yard",
        "city": "Ripon",
        "state_code": "",
        "state": "Colorado",
        "zip": "22334",
        "country": "",
        "country_code": "",
        "phone": "505-111-2222",
        "fax": "",
        "latitude": "",
        "longitude": "",
        "update_existing_transactions_address": ""
    }
}

Deleting an address

To delete an address, you need to know the Contact_Id of the contact record and the address_id of the record being edited.

SELECT
    *
FROM
    API
WHERE
    URL = '/books/v3/contacts/576438000000152001/address/576438000000149024'
    AND Method = 'DELETE'

If it works, the Response column contains:

{"code":0,"message":"The address has been deleted."}

Credit_Note

Supports: SELECT, INSERT, UPDATE, and DELETE

INSERT statements

You can create new credit notes by executing an INSERT statement. To create a new credit note, you need the minimum of Customer_Id, Currency_Id, Date, and Line_Items. The Creditnote_Number is only needed if you don't have auto numbering turned on in Zoho Books. Here is the <JSON_Data> I'm passing in:

[
    {
        "account_id": "576438000000000376",
        "tax_type": "tax",
        "item_id": "576438000000080083",
        "name": "1000-99-9999-99-99-99-07",
        "tax_id": "576438000000056007",
        "item_order": 1,
        "description": "Easysoft Contract - Renewal",
        "quantity": 1,
        "unit": "pcs",
        "item_total": 404.73,
        "sales_rate": 1,
        "rate": 404.73,
        "tax_percentage": 10,
        "account_name": "Sales",
        "tax_name": "10% Tax",
        "pricing_scheme": "unit",
        "item_type": "sales"
    }
]

The SQL being sent is:

INSERT INTO
    Credit_Note (Customer_Id, Currency_Id, [Date], Line_Items)
values
    (
        '576438000000066678',
        '576438000000000059',
        '2021-06-14',
        '<JSON_Data>'
    )

UPDATE statements

You can update an existing credit note by sending an UPDATE statement:

UPDATE Credit_Note
SET
    [Date] = '2024-01-01'
WHERE
    Creditnote_Id = '576438000000159004'

DELETE statements

You can delete a credit note by sending:

DELETE FROM Credit_Note
WHERE
    Creditnote_Id = '576438000000159004'

As long as the Creditnote_Id is valid, the record will be deleted.

Using the API table to get a credit note

Using the API table, you can get a credit note PDF encoded in Base64. For example:

SELECT
    ResponseBody
FROM
    api
WHERE
    url = 'books/v3/creditnotes/576438000000160023?accept=pdf'
    AND Method = 'GET'

Using the API table to email a credit note

First, create the JSON data containing the email details for the credit note recipients.

{
    "to_mail_ids": [
        "test@easysoft.com",
        "another@easysoft.com"
    ],
    "cc_mail_ids": [
        "support@easysoft.com"
    ],
    "subject": "Test credit note from Zoho Books",
    "body": "This is a test body text to check the example code."
}

To send the credit note to these email addresses:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/creditnotes/576438000000160023/email'
    AND Method = 'POST'
    AND RequestBody = '<JSON_Data>'

The ResponseBody will contain:

{"code":0,"message":"Your credit note has been sent."}

Using the API table to access email history

Email history can only be accessed through the API table. You need to pass a CreditNote_Id into the URL. For example:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/creditnotes/576438000000156003/emailhistory/'
    AND Method = 'GET'

This returns:

{
    "code": 0,
    "message": "success",
    "email_history": [
        {
            "mailhistory_id": "576438000000156061",
            "from": "zoho1@test.co.uk",
            "to_mail_ids": "j.smith@easysoft.com",
            "subject": "Credit Note - CN-00001 from ESL Test",
            "date": "2024-06-28",
            "type": 28
        },
        {
            "mailhistory_id": "576438000000156071",
            "from": "zoho1@ test.co.uk",
            "to_mail_ids": "support@easysoft.com",
            "subject": "Credit Note - CN-00001 from ESL Test",
            "date": "2024-06-28",
            "type": 28
        }
    ]
}

Using the API table to mark a credit note status (void, draft, or open)

Use something like:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/creditnotes/576438000000156003/status/<X>'
    AND Method = 'POST'

where <X> is one of:

Using the API table to submit, approve, or get the email history of a credit note

Use something like:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/creditnotes/576438000000156003/<X>'
    AND Method = 'POST'

where <X> is one of:

submit and approve only work if they have been enabled in the Zoho Books company you're connecting to. If they are not allowed, the ResponseBody is:

{
    "code": 21016,
    "message": "You have not enabled the Approval feature for this organization. Please enable it in Settings."
}

Using the API table to update billing or shipping address

Use something like:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/creditnotes/576438000000156003/address/<X>'
    AND Method = 'PUT'
    AND RequestBody = '<JSON_Data>'

where <X> is one of:

The JSON data is in the format:

{
    "address":"Suite 125,
         McMillan Avenue","city": "San Francisco",
    "state": "CA",
    "zip": 94134,
    "country": "USA",
    "fax": "+1-925-924-9600"
}

Using the API table to apply credit notes to an invoice

First, you need to build some JSON data in this format:

{
    "invoices": [
        {
            "Invoice_Id": "576438000000105717",
            "amount_applied": 445.2
        },
        {
            "Invoice_Id": "576438000000105718",
            "amount_applied": 100
        }
    ]
}

This tells Zoho Books which invoices you want to apply a credit to. The only thing you need to keep in mind is that the total of the amount_applied must be less than or equal to the amount of credit on your credit note.

Then send this to Zoho Books through the API table by using:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/creditnotes/576438000000159024/invoices'
    AND Method = 'POST'
    AND RequestBody = '<JSON_Data>'

If this works, each invoice in the list will have the amount_applied deducted from the invoice total. If the new total is 0, the invoice will be marked as paid. If the new total is not 0, the balance will be updated accordingly.

The ResponseBody will return:

{
    "code": 0,
    "message": "Credits have been applied to the invoice(s).",
    "apply_to_invoices": {
        "invoices": [
            {
                "Invoice_Id": "576438000000105717",
                "amount_applied": 445.200,
                "apply_date": "2021-06-14"
            },
            ":[{"Invoice_Id":"576438000000105718","amount_applied":100.00,"apply_date":"2021-06-14"}]}
}

Using the API table to list invoices credited

Use something like:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/creditnotes/576438000000159024/invoices'
    AND Method = 'GET'

The ResponseBody is:

{
    "code": 0,
    "message": "success",
    "invoices_credited": [
        {
            "creditnote_id": "576438000000159024",
            "Invoice_Id": "576438000000105717",
            "creditnote_Invoice_Id": "576438000000159064",
            "date": "2021-06-14",
            "invoice_number": "39439",
            "creditnote_number": "CN-00005",
            "credited_amount": 445.20
        }
    ]
}

Using the API table to delete invoices credited

Use something like:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/creditnotes/576438000000159024/invoices'
    AND Method = 'DELETE'

The ResponseBody returns the message Credits applied to an invoice have been deleted.

Using the API table to add a comment to a credit note

Use this JSON data format for the comment:

{
    "description": "Testing a comment description"
}

Then send this to Zoho Books through the API table by using:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/creditnotes/576438000000159024/comments'
    AND Method = 'POST'
    AND RequestBody = '<JSON_Data>'

The ResponseBody will return:

{
    "code": 0,
    "message": "Comments added.",
    "comment": {
        "comment_id": "576438000000162035",
        "comment_type": "internal",
        "creditnote_id": "576438000000159024",
        "comments_html_format": "Testing a comment description",
        "description": "Testing a comment description",
        "commented_by_id": "576438000000049001",
        "commented_by": "zoho1",
        "date": "2024-07-03"
    }
}

Using the API table to list credit note comments and history

Use something like:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/creditnotes/576438000000159024/comments'
    AND Method = 'GET'

The ResponseBody is:

{
    "code": 0,
    "message": "success",
    "comments": [
        {
            "comment_id": "576438000000162035",
            "creditnote_id": "576438000000159024",
            "comments_html_format": "Testing a comment description",
            "description": "Testing a comment description",
            "commented_by_id": "576438000000049001",
            "commented_by": "zoho1",
            "comment_type": "internal",
            "date": "2024-07-03",
            "date_description": "3 minutes ago",
            "time": "4:55 AM",
            "operation_type": "",
            "transaction_id": "",
            "transaction_type": ""
        },
        {
            "comment_id": "576438000000159066",
            "creditnote_id": "576438000000159024",
            "description": "Credits applied to invoice 39439",
            "commented_by_id": "576438000000049001",
            "commented_by": "zoho1",
            "comment_type": "system",
            "date": "2024-07-02",
            "date_description": "19 hours ago",
            "time": "9:13 AM",
            "operation_type": "Added",
            "transaction_id": "576438000000159064",
            "transaction_type": "creditnote_invoice"
        },
        {
            "comment_id": "576438000000159034",
            "creditnote_id": "576438000000159024",
            "description": "Credit note created for $445.20",
            "commented_by_id": "576438000000049001",
            "commented_by": "zoho1",
            "comment_type": "system",
            "date": "2024-07-01",
            "date_description": "yesterday",
            "time": "6:02 AM",
            "operation_type": "Added",
            "transaction_id": "",
            "transaction_type": "creditnote"
        }
    ]
}

Currency

Supports: SELECT, INSERT, UPDATE, and DELETE

INSERT statements

You can create a new currency with:

INSERT INTO
    Currency (
        Currency_Code,
        Currency_Name,
        Currency_Symbol,
        Price_Precision,
        Currency_Format
    )
VALUES
    (
        'UAH',
        'Ukraine Hryvnia',
        '₴',
        '2',
        '1,234,567.89'
    )

UPDATE statements

You can update a currency with:

UPDATE Currency
SET
    Currency_Name = 'Ukraine Hryvnia'
WHERE
    Currency_Code = 'UAH'

DELETE statements

You can delete a currency with:

DELETE FROM Currency
WHERE
    Currency_Code = 'UAH'

Or you can use the API table instead:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/settings/currencies/579676000000122020'
    AND Method = 'DELETE'

Using the API table to create an exchange rate

First create some JSON data that contains:

{
    "effective_date": "2024-02-01",
    "rate": 1.521234
}

Then send it by using the API table:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/settings/currencies/579676000000121014/exchangerates'
    AND Method = 'POST'
    AND RequestBody = '<JSON_Data>'

The ResponseBody will contain the exchange_rate_id.

Using the API table to update an exchange rate

To update an exchange rate, use the JSON data in the format shown earlier and send it to Zoho Books with:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/settings/currencies/579676000000121004/exchangerates/579676000000122004'
    AND Method = 'PUT'
    AND RequestBody = '<JSON_Data>'

The second Id value in the URL is the exchange_rate_id of the record you want to update.

Using the API table to list exchange rates

To do this, either use the exchange_rate_id as part of the URL:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/settings/currencies/579676000000121014/exchangerates/579676000000122031'
    AND Method = 'GET'

or use:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/settings/currencies/579676000000121014/exchangerates'
    AND Method = 'GET'

Note If you try the second method, you'll find that the rate returned does not match the rate returned by using the first method. I have reported this issue to Zoho Books support (5-Aug-2024). If the status changes, I'll update this document to reflect that.

Customer_Payments

Supports: SELECT, INSERT, UPDATE, and DELETE

INSERT statements

To create new payments, first create the JSON data for the invoice details to be paid:

[
    {
        "Invoice_Id": "576438000000104809",
        "invoice_number": "39440",
        "date": "2024-07-03",
        "invoice_amount": "373.76",
        "amount_applied": "373.76",
        "balance_amount": "0"
    }
]

You can pay several invoices at once by adding a new block to the array such as [{Inv 1},{Inv 2],{Inv <n>}].

Then run the INSERT statement:

INSERT INTO
    Customer_Payment (Customer_Id, Payment_Mode, Amount, Invoices)
VALUES
    (
        '576438000000072770',
        'cash',
        373.76,
        '<JSON)Data>'
    )

UPDATE statements

You can update all columns. Here's an example of updating a description:

UPDATE Customer_Payment
SET
    Description = 'Customer paid at till'
WHERE
    Payment_Id = '576438000000168003'

DELETE statements

Use something like:

DELETE FROM Customer_Payment
WHERE
    Payment_Id = '576438000000168003'

Or:

SELECT
    *
FROM
    API
WHERE
    url = 'books/v3/customerpayments/579676000000107086'
    AND Method = 'DELETE'

Using the API table to refund an excess customer payment

Create some JSON data containing the refund details:

{
    "date": "2024-07-03",
    "refund_mode": "cash",
    "amount": 1000,
    "from_account_id": "576438000000000346"
}

Then:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/customerpayments/576438000000172004/refunds'
    AND Method = 'POST'
    AND RequestBody = '<JSON_Data>'

The ResponseBody contains:

The refund information for this payment has been saved.

Using the API table to update a refund

These work in the same way as described in Refund an excess customer payment, but in the SQL, change the Method from POST to PUT.

Using the API table to retrieve refund details

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/customerpayments/576438000000172004/refunds'
    AND Method = 'GET'

The ResponseBody will contain a JSON list of refunds against the Payment_Id sent.

Using the API table to delete a refund

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/customerpayments/576438000000172004/refunds'
    AND Method = 'DELETE'

The Delivery_Challan

Supports: SELECT, INSERT, UPDATE, and DELETE

INSERT statements

You can create a new record by firstly creating the JSON data required for the line item details:

[
    {
        "description": "A5 Notepad",
        "rate": 9.99,
        "quantity": 3
    }
]

Then run the INSERT statement:

INSERT INTO
    Delivery_Challan (
        [Date],
        Challan_Type,
        Reference_Number,
        Currency_Code,
        Customer_Id,
        Tax_Treatment,
        Place_Of_Supply,
        Line_Items
    )
VALUES
    (
        '2024-09-01',
        'job work',
        'DelChallan2',
        'BHD',
        '633066000000053001',
        'vat_registered',
        'BH',
        '{"description":"A5 Notepad","rate":9.99,"quantity":3}'
    )

UPDATE statements

This example adds an additional line to the Line_Item column. First, I change the JSON data from the INSERT example to have two lines:

[
    {
        "account_id": "548752000000000430",
        "description": "A5 Notepad",
        "rate": 9.99,
        "quantity": 3
    },
    {
        "account_id": "548752000000000430",
        "description": "A3 Paper box 100",
        "rate": 9.99,
        "quantity": 1
    }
]

Then:

UPDATE Delivery_Challan
SET
    line_item = '<JSON_Data>'
WHERE
    deliverychallan_id = '576438000000176002'

DELETE statements

DELETE FROM Delivery_Challan
WHERE
    deliverychallan_id = '633066000000051395'

Using the API table to mark an item as delivered or returned

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/deliverychallans/633066000000051423/status/<X>?organization_id=20100804129'
    AND Method = 'POST'

where <X> is one of:

Employee

Supports: SELECT and INSERT

SELECT statements

If you use the Name column in the WHERE clause:

SELECT
    *
FROM
    Employee
WHERE
    Name = 'Easysoft Support'

The columns are returned, but the Email column is always sent back from the API containing NULL.

If you pass in the Employee_Id, you get back:

SELECT
    *
FROM
    Employee
WHERE
    Employee_Id = '576438000000109002'

You'll get this error from the Zoho Books API:

Error <37: The HTTP method GET is not allowed for the requested resource>.

INSERT statement

INSERT statements are supported. For example:

INSERT INTO
    Employee (Name, Email)
VALUES
    ('Easysoft Support 2', 'support2@easysoft.com')

Inserting the same email address results in the error:

Code: 118356
Message: Employee has already been enrolled with support@easysoft.com

Inserting the same name is valid as long as the email is not already in the table.

Deleting employees

According to the Xero Books API documentation, you should be able to delete an employee. However, the example they give does not work and also does not specify which employee they are deleting.

Estimate

Supports: SELECT, INSERT, UPDATE, and DELETE

An estimate is a quote or a proposal for the products you sell or the services you render to your clients to take your business forward.

INSERT statements

In this example, I'm going to insert four lines in groups of two. Two of the lines will have a discounted percentage applied. To do this, I first create the Line_Items as <JSON_Data>:

[
    {
        "header_name": "Leads",
        "description": "USB-C",
        "rate": 2.75,
        "quantity": 33,
        "discount": "5.00%",
        "tax_id": "576438000000056007"
    },
    {
        "header_name": "Leads",
        "description": "USB-B",
        "rate": 0.68,
        "quantity": 127,
        "discount": "11.5%",
        "tax_id": "576438000000056007"
    },
    {
        "header_name": "Paper",
        "description": "A4 (5 x per box)",
        "rate": 9.99,
        "quantity": 5,
        "tax_id": "576438000000056007"
    },
    {
        "header_name": "Paper",
        "description": "A5 (10 x per box)",
        "rate": 6.87,
        "quantity": 12,
        "tax_id": "576438000000056007"
    }
]

To group each item, header_name must be used for each of the items added. In this example the tax_id is the Id of my 10% tax. Note there are no total values that need to be supplied, rate, quantity, and tax_id are all that's needed. discount is optional.

Then send that to Zoho Books by using:

INSERT INTO
    Estimate ([Date], Customer_Id, Line_Items)
VALUES
    (
        '2024-07-08',
        '576438000000059096',
        '<JSON_Data>'
    )

The estimate_number number will automatically be inserted by Zoho Books with the next new number in the sequence. Keep in mind that if you delete an estimate, the number will not be freed up. Square brackets are only needed round columns that are reserved words such as date.

UPDATE statements

Let's say you need to change the quantity of the A5 paper line in the previous insert. To do this, change the JSON data and send with an UPDATE query:

UPDATE Estimate
SET
    Line_Items = '<JSON_Data>'
WHERE
    Estimate_Id = '576438000000182002'

The total and tax totals are automatically updated by Zoho.

To remove or add a line, alter the JSON data and send the update again.

DELETE statements

This example deletes all estimates on a given date:

DELETE FROM Estimate
WHERE
    [Date] = '2024-07-08'

To do this by using the API table:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/estimates/579676000000107086'
    AND Method = 'DELETE'

Using the API table to mark an estimate as sent, accepted, or declined

Use something like:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/estimates/576438000000182002/status/<X>'
    AND Method = 'POST'

where <X> is one of:

If Zoho Books is configured to automatically generate invoices when a quote is accepted, setting the status to accepted results in this response:

Code: 200
ResponseType: application/json;charset=UTF-8
ResponseBody: {
    "code": 0,
    "message": "Quote status has been changed to Invoiced.",
    "data": {
        "Invoice_Id": "576438000000199003"
    }
}

Using the API table to email an estimate

First, create the JSON data contain the email details you want to use to send the data.

{
    "to_mail_ids": [
        "test@easysoft.com",
        "annother@easysoft.com"
    ],
    "cc_mail_ids": [
        "support@easysoft.com"
    ],
    "subject": "Test credit note from ZB",
    "body": "This is a test body text to check the example code."
}

To get Zoho Books to send the email:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/estimates/576438000000189001/email/'
    AND Method = 'POST'
    AND RequestBody = '<JSON_Data>'

The ResponseBody contains:

{
    "code": 0,
    "message": "Your quote has been sent."
}

Using the API table to email multiple estimates

Emailing multiple estimates only works if all the estimates in Zoho Books have a contact person assigned to the estimate_id values you pass in:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/estimates/email/?estimate_ids=576438000000189001,576438000000189019'
    AND Method = 'POST'

576438000000189001,576438000000189019 is a list of comma separated Estimate_Id values. You can specify up to a 10 of these.

The ResponseBody will contain:

{
    "code": 0,
    "message": "Your mails are scheduled to be sent in a few minutes. We will notify you once it is done."
}

If you pass in an invalid estimate_id or there's another problem, the only way feedback you get is on the Zoho Books web site under the Notificationssection. This section will contain something like:

We're facing a roadblock while sending Estimates. Unable to send the following Estimate(s) as no contact person was associated to it: QT-000008, QT-000009.

Using the API table to bulk export estimates

This API call returns up to 25 estimates in a single PDF:

SELECT
    ResponseBody
FROM
    api
WHERE
    url = 'books/v3/estimates/email/pdf?estimate_ids=576438000000189002,576438000000189019'
    AND Method = 'GET'

The ResponseBody contains a single PDF in Base64 encoded format.

Using the API table to bulk print estimates

This API call returns up to 25 estimate details in one go.

SELECT
    ResponseBody
FROM
    api
WHERE
    url = 'books/v3/estimates/email/?estimate_ids=576438000000189002,576438000000189019'
    AND Method = 'GET'

Any estimate_id values passed in that are not valid are ignored and only valid entries are returned in the ResponseBody:

{
    "code": 0,
    "message": "success",
    "estimates": [
        {
            "estimate_id": "576438000000189019",
            "zcrm_potential_id": "",
            "zcrm_potential_name": "",
            "customer_name": "Pension Funds - USD",
            "customer_id": "576438000000059096",
            "company_name": "Pension Funds - USD",
            "status": "sent",
            "color_code": "",
            "current_sub_status_id": "",
            "current_sub_status": "sent",
            "estimate_number": "QT-000009",
            "reference_number": "",
            "date": "2024-07-09",
            "currency_id": "576438000000000059",
            "Currency_Code": "USD",
            "total": 324.53,
            "created_time": "2024-07-10T05:05:18-0400",
            "last_modified_time": "2024-07-10T06:39:38-0400",
            "accepted_date": "",
            "declined_date": "",
            "expiry_date": "",
            "has_attachment": false,
            "is_viewed_by_client": false,
            "client_viewed_time": "",
            "is_emailed": true,
            "is_viewed_in_mail": false,
            "template_type": "",
            "template_id": "",
            "mail_first_viewed_time": "",
            "mail_last_viewed_time": "",
            "salesperson_id": "",
            "salesperson_name": ""
        }
    ],
    "page_context": {
        "page": 1,
        "per_page": 200,
        "has_more_page": false,
        "report_name": "Quotes",
        "applied_filter": "Status.All",
        "sort_column": "created_time",
        "sort_order": "D",
        "search_criteria": [
            {
                "column_name": "estimate_ids",
                "search_text_0": "576438000000189002",
                "search_text_1": "576438000000189019",
                "search_text_formatted_0": "576438000000189002",
                "search_text_formatted_1": "576438000000189019",
                "comparator": "in"
            }
        ]
    }
}

Using the API table to add comments to an estimate

This API call lets you add a comment to an estimate and chose whether you want your customer to be able to refer to that comment. First, create the JSON for the comment:

{
    "description": "PDF sent to customer 10-Jul-2024",
    "show_comment_to_clients": true
}

Send the comment:

SELECT
    ResponseBody
FROM
    api
WHERE
    url = 'books/v3/estimates/576438000000189019/comments'
    AND Method = 'POST'
    AND RequestBody = '<JSON_Data>'

The ResponseBody contains the message Comments added.

Using the API table to list comments and history for an estimate

This API call lists all the comments and history for the Estimate_Id sent.

SELECT
    ResponseBody
FROM
    api
WHERE
    url = 'books/v3/estimates/576438000000189019/comments'
    AND Method = 'GET'

Data is returned in descending date order:

{
    "code": 0,
    "message": "success",
    "comments": [
        {
            "comment_id": "576438000000195002",
            "estimate_id": "576438000000189019",
            "description": "PDF sent to customer 10-Jul-2024",
            "commented_by_id": "576438000000049001",
            "commented_by": "zoho1",
            "comment_type": "user",
            "date": "2024-07-11",
            "date_description": "4 minutes ago",
            "time": "3:11 AM",
            "operation_type": "",
            "transaction_id": "",
            "transaction_type": ""
        },
        {
            "comment_id": "576438000000192045",
            "estimate_id": "576438000000189019",
            "description": "Quote emailed to test@easysoft.com",
            "commented_by_id": "576438000000049001",
            "commented_by": "zoho1",
            "comment_type": "system",
            "date": "2024-07-10",
            "date_description": "17 hours ago",
            "time": "9:23 AM",
            "operation_type": "updated",
            "transaction_id": "",
            "transaction_type": "email"
        },
        {
            "comment_id": "576438000000191018",
            "estimate_id": "576438000000189019",
            "description": "Quote updated.",
            "commented_by_id": "576438000000049001",
            "commented_by": "zoho1",
            "comment_type": "system",
            "date": "2024-07-10",
            "date_description": "20 hours ago",
            "time": "6:38 AM",
            "operation_type": "updated",
            "transaction_id": "",
            "transaction_type": "estimate"
        },
        {
            "comment_id": "576438000000189039",
            "estimate_id": "576438000000189019",
            "description": "Quote marked as sent",
            "commented_by_id": "576438000000049001",
            "commented_by": "zoho1",
            "comment_type": "system",
            "date": "2024-07-10",
            "date_description": "21 hours ago",
            "time": "5:32 AM",
            "operation_type": "updated",
            "transaction_id": "",
            "transaction_type": "estimate"
        },
        {
            "comment_id": "576438000000189029",
            "estimate_id": "576438000000189019",
            "description": "Quote created for $324.53",
            "commented_by_id": "576438000000049001",
            "commented_by": "zoho1",
            "comment_type": "system",
            "date": "2024-07-10",
            "date_description": "22 hours ago",
            "time": "5:05 AM",
            "operation_type": "Added",
            "transaction_id": "",
            "transaction_type": "estimate"
        }
    ]
}

Using the API table to DELETE comments

This API call deletes the estimate comment based on the Estimate_Id and the Comment_Id:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/estimates/576438000000189019/comments/576438000000198001'
    AND Method = 'DELETE'

Code: 200 ResponseType: application/json;charset=UTF-8 ResponseBody:
{
    "code": 0,
    "message": "The comment has been deleted."
}

Expense

Supports: SELECT, INSERT, UPDATE, and DELETE

INSERT statements

First, create the JSON data for the expense:

[
    {
        "account_id": "576438000000000406",
        "account_name": "Air Travel Expense",
        "description": "New York to LA",
        "tax_amount": 10,
        "tax_type": "tax",
        "tax_percentage": 10,
        "item_total": 100,
        "amount": 110,
        "item_order": 1
    },
    {
        "account_id": "576438000000000406",
        "account_name": "Air Travel Expense",
        "description": "LA to Miami",
        "tax_amount": 30,
        "tax_type": "tax",
        "tax_percentage": 10,
        "item_total": 300,
        "amount": 330,
        "item_order": 2
    }
]

Then run the INSERT statement:

INSERT INTO
    Expense (
        Transaction_Type,
        Account_Id,
        Account_Name,
        Vendor_Id,
        Vendor_Name,
        Currency_Code,
        Line_Items
    )
VALUES
    (
        'expense',
        '576438000000000406',
        'Air Travel Expense',
        '576438000000166003',
        'Air Travel USA',
        'USD',
        '<JSON DATA>'
    )

UPDATE statements

You can update some of the columns but only if the expense in question is not itemized. If you try to edit one of these Zoho Books, returns Error <115124: Itemized expense cannot be edited.>

UPDATE Expense
SET
    [Date] = '2024-07-01'
WHERE
    Expense_Id = '576438000000170042'

DELETE statements

Use something like:

DELETE FROM Expense
WHERE
    Expense_Id = '576438000000168003'

Or by using the API table:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/expenses/579676000000113002'
    AND Method = 'DELETE'

Using the API table to get expense receipts

To do this by using the API table:

SELECT
    Code,
    ResponseType,
    ResponseBody
FROM
    api
WHERE
    url = 'books/v3/expenses/576438000000170042/receipt'
    AND Method = 'GET'

The ResponseBody column contains the data Base64 encoded. The ResponseType denotes the type of receipt downloaded.

Using the API table to delete expense receipts

To do this by using the API table:

SELECT
    Code,
    ResponseType,
    ResponseBody
FROM
    api
WHERE
    url = 'books/v3/expenses/576438000000170042/receipt'
    AND Method = 'DELETE'

Invoice

Supports: SELECT, INSERT, UPDATE, and DELETE

SELECT statement

The Zoho Books Invoices API call lets get data back in JSON, HTML, or PDF. For example:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/invoices/576438000000197002/?accept=pdf'
    AND Method = 'GET'

Valid values for accept are json, pdf, or html.

INSERT, UPDATE, and DELETE statements

Except for changing the SQL table name from Estimate to Invoice, invoice works in exactly the same way as documented in this section.

UPDATE statements

Let's say you need to change the quantity of a line in the Line_Items column:

{
    "line_item_id": "576438000000205017",
    "tax_name": "10% Tax",
    "item_order": 8,
    "quantity": 2.0,
    "header_name": "Storage",
    "description": "8TB Surveillance Hard Drive",
    "account_id": "576438000000000376",
    "rate": 169.13999999999999,
    "tax_id": "576438000000056007",
    "bcy_rate": 169.13999999999999,
    "account_name": "Sales",
    "pricing_scheme": "unit",
    "header_id": "576438000000197034",
    "tax_type": "tax",
    "tax_percentage": 10,
    "item_total": 338.27999999999997,
    "line_item_taxes": [
        {
            "tax_id": "576438000000056007",
            "tax_name": "10% Tax (10%)",
            "tax_amount": 33.810000000000002
        }
    ]
}

Alter the data and send it back by using:

UPDATE Invoice SET Line_Items = '<JSON DATA>' WHERE Invoice_Id = '576438000000205001'

Running SELECT * FROM LastId after this update returns:

Code: 0
Message: Invoice information has been updated.
Operation: UPDATE
Api: invoice
Key: Invoice_Id
Value: 576438000000205001

Delete an invoice

DELETE FROM Invoice WHERE Invoice_Id = '576438000000205001'

Running SELECT * FROM LastId after this update returns:

Code: 0
Message: The invoice has been deleted.
Operation: DELETE
Api: invoice
Key: Invoice_Id
Value: 576438000000205001
FullMessage: {"code": 0, "message": "The invoice has been deleted."}

Using the API table to approve an invoice

Depending on your Zoho Books settings, invoices might have to be approved before they can be sent:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/invoices/576438000000206025/approve'

API response columns:

Code: 201
ResponseType: application/json;charset=UTF-8
ResponseBody: {
    "code": 0,
    "message": "The invoice(s) has been approved successfully."
}

Using the API table to mark invoices as sent, void, or draft

Use something like:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/invoices/576438000000206025/status/<X>'
    AND Method = 'POST'

where <X> is one of:

The ResponseBody is:

Code: 201
ResponseType: application/json;charset=UTF-8
ResponseBody: {
    "code": 0,
    "message": "The invoice(s) has been approved successfully."
}

Using the API table to email an invoice

Create some JSON data that specifies the email recipients:

{
    "to_mail_ids": [
        "test1@easysoft.com",
        "test2@easysoft.com"
    ],
    "cc_mail_ids": [
        "test3@easysoft.com"
    ],
    "subject": "Invoice Email Test",
    "body": "ZB Email containing accepted Invoice"
}

The body part of the JSON can be plain text or HTML. To send the invoice:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/invoices/576438000000206025/email'
    AND Method = 'POST'
    AND RequestBody = '<JSON_Data>'

If you want the email to include the customer's statement after /email, add:

?send_customer_statement=true

Using the API table to get email sent details

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/invoices/576438000000206025/email'
    AND Method = 'GET'

The ResponseBody will contain all the details about the last email sent.

Using the API table to send payment reminders

This is the same <JSON_Data> layout as in Using the API table to email an invoice but the API url changes at the end from /email to /paymentreminder.

Using the API table to get payment reminder email content

This is the same method as shown in Using the API table to get email sent details. In url, change the end from /email to /paymentreminder.

Using the API table to generate bulk invoice reminders

Bulk invoice reminders can only be sent to contacts added to the customer record and check on the invoice. If the invoice does not have a contact selected, you'll not be able to use Bulk Remind. Only open and overdue invoices can be bulk sent:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/invoices/paymentreminder?Invoice_Ids=<IDs>'
    AND Method = 'POST'

where <IDs> is a comma separated list of up to 10 invoice IDs. For example:

579676000000054079,579676000000050364,579676000000053106

If this succeeds, the ResponseBody contains:

{
    "code": 0,
    "message": "Success! All reminders have been sent."
}

Otherwise, the ResponseBody returns something like:

{
    "code": 0,
    "message": "Success! All reminders have been sent.",
    "info": {
        "email_errors_info": [
            {
                "code": 4083,
                "ids": [
                    "579676000000050364",
                    "579676000000053106",
                    "579676000000053080",
                    "579676000000050312",
                    "579676000000053054",
                    "579676000000050286",
                    "579676000000053028",
                    "579676000000053002"
                ],
                "message":"Unable to send payment reminder as no contact person was found for the invoice(s): INV-000015,
                      INV-000014,
                      INV-000011,
                      INV-000010,
                      INV-000009,
                      INV-000007,
                      INV-000006,
                      INV-000003"
            }
        ],
        "email_success_info": {
            "message": "We were able to send 2 reminders. However, we faced a glitch while sending the rest of them.",
            "sent_count": 2
        }
    }
}

Using the API table to bulk export invoices

This API call returns up to 25 estimates in a single PDF:

SELECT
    ResponseBody
FROM
    api
WHERE
    url = 'books/v3/invoices/pdf?Invoice_Ids=579676000000053002,579676000000054079,579676000000053106,'
    AND Method = 'GET'

The ResponseBody contains a single PDF in Base64 encoded format.

Using the API table to bulk export and print invoices

This is the same as the previous export but includes the print flag, so you're prompted with the print window when you open the PDF:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/invoices/print?Invoice_Ids=579676000000053002,579676000000054079,579676000000053106'

Using the API table to generate payment reminders

Use something like:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/invoices/579676000000053002/paymentreminder/disable'

Change disable to enable to turn on the reminder.

Using the API table to write off or cancel write off

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/invoices/579676000000053002/writeoff'
    AND Method = 'POST'
SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/invoices/579676000000053002/writeoff/cancel'

Using the API table to list payments

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/invoices/579676000000050234/payments'

The ResponseBody returns all the payments against the Invoice_Id sent:

{
    "code": 0,
    "message": "success",
    "payments": [
        {
            "payment_id": "579676000000063535",
            "payment_number": "9",
            "Invoice_Id": "579676000000050234",
            "invoice_payment_id": "579676000000063539",
            "payment_mode": "cash",
            "description": "",
            "date": "2024-07-17",
            "reference_number": "",
            "exchange_rate": 1.00,
            "amount": 2000.00,
            "tax_amount_withheld": 0.00,
            "online_transaction_id": "",
            "is_single_invoice_payment": true
        },
        {
            "payment_id": "579676000000060144",
            "payment_number": "10",
            "Invoice_Id": "579676000000050234",
            "invoice_payment_id": "579676000000060148",
            "payment_mode": "cash",
            "description": "",
            "date": "2024-07-17",
            "reference_number": "",
            "exchange_rate": 1.00,
            "amount": 2000.00,
            "tax_amount_withheld": 0.00,
            "online_transaction_id": "",
            "is_single_invoice_payment": true
        }
    ]
}

Using the API table to add comments

This API call allows you to add a comment to an invoice and choose whether or not you want your customer to be able to refer to that. First, create the JSON data for the comment:

{
    "description": "Spoke to customer 22-Jul-2024",
    "show_comment_to_clients": true
}

Send the comment:

SELECT
    ResponseBody
FROM
    api
WHERE
    url = 'books/v3/invoices/579676000000050364/comments'
    AND Method = 'POST'
    AND RequestBody = '<JSON_Data>'

The ResponseBody contains the message Comments added.

Using the API table to UPDATE comments

Use the same format as shown in the previous example for your JSON data.

SELECT
    ResponseBody
FROM
    api
WHERE
    url = 'books/v3/invoices/579676000000050364/comments/579676000000078003'
    AND Method = 'PUT'
    AND RequestBody = '<JSON_Data>'

The second ID (579676000000078003) is the one for the comment you're updating.

Using the API table to list comments and history

This API call lists all the comments and history for the Invoice_Id sent.

SELECT
    ResponseBody
FROM
    api
WHERE
    url = 'books/v3/invoices/579676000000050364/comments'
    AND Method = 'GET'

Using the API table to delete comments

Use something like:

SELECT
    ResponseBody
FROM
    api
WHERE
    url = 'books/v3/invoices/579676000000050364/comments/579676000000078003'
    AND Method = 'DELETE'

Item

Supports: SELECT, INSERT, UPDATE, and DELETE

INSERT statements

This example inserts an item that's purchased and sold as goods:

INSERT INTO
    Item (
        [Name],
        Description,
        Tax_Id,
        Item_Type,
        Product_Type,
        Unit_Id,
        Rate,
        Account_Id,
        Purchase_Rate,
        Purchase_Description,
        Purchase_Account_Id,
        Vendor_Id
    )
VALUES
    (
        '1TB Hard Disk',
        'WD 1TB HDD 3.5"',
        '579676000000050178',
        'sales_and_purchases',
        'goods',
        '579676000000050038',
        '299',
        '579676000000000376',
        '249',
        '1 x WD 1TB HDD 3.5"',
        '579676000000000509',
        '579676000000081069'
    )

As long as you have the IDs for the columns (vendor_id, for example) there's no need to put in the column (vendor_name, for example) as this will be automatically filled in by Zoho Books.

DELETE statements

This statement deletes all items that match a particular name:

DELETE FROM Item
WHERE
    [Name] = '1TB Hard Disk'

You can do this by using the API table if you have the Item_Id:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/items/579676000000131010'
    AND Method = 'DELETE'

UPDATE statements

This example adds 10% onto the sales price for all goods:

UPDATE Item
SET
    Rate = Rate * 1.1
WHERE
    product_type = 'goods'

Using the API table to mark an invoice as active or inactive

Use something like:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/items/579676000000089003/status/<X>'
    AND Method = 'POST'

where <X> is one of:

Journal

Supports: SELECT, INSERT, UPDATE, and DELETE

INSERT statements

Create the JSON data for Line_Items. There needs to be at least one debit and one credit line:

[
    {
        "account_id": 579676000000000397,
        "customer_id": 579676000000050204,
        "description": "Standard cheque charge",
        "tax_id": 579676000000050182,
        "debit_or_credit": "debit",
        "amount": 4.99
    },
    {
        "account_id": 579676000000000501,
        "customer_id": 579676000000050204,
        "description": "Bank charge",
        "debit_or_credit": "credit",
        "amount": 4.99
    }
]

Then send it by using:

INSERT INTO
    Journal (
        Reference_Number,
        Journal_Date,
        Journal_Type,
        Currency_Id,
        Notes,
        Vat_Treatment,
        Tax_Treatment,
        Product_Type,
        Line_Items
    )
VALUES
    (
        'Bank charge 2',
        '2024-08-06',
        'both',
        '579676000000000065',
        'Standard cheque processing charge.',
        'non_eu',
        'non_eu',
        'service',
        '<JSON_Data>'
    )

UPDATE statements

Alter the JSON data for Line_Items to reflect the amount you want to change. For example:

[
    {
        "account_id": 579676000000000397,
        "customer_id": 579676000000050204,
        "description": "Standard cheque charge",
        "tax_id": 579676000000050182,
        "debit_or_credit": "debit",
        "amount": 5.99
    },
    {
        "account_id": 579676000000000501,
        "customer_id": 579676000000050204,
        "description": "Bank charge",
        "debit_or_credit": "credit",
        "amount": 5.99
    }
]

Then send the update by using:

UPDATE Journal
SET
    Line_Items = '<JSON_Data>'
WHERE
    Journal_id = '579676000000130007'

DELETE statements

DELETE FROM Journal
WHERE
    Journal_Id = '579676000000130007'

Or use the API table:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/journals/579676000000130007/'
    AND Method = 'DELETE'

Using the API table to mark a journal as published

Use something like:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/journals/579676000000130007/status/publish'
    AND Method = 'POST'

Opening_Balance

Supports: SELECT, INSERT, UPDATE, and DELETE

This table contains one row of data when you have customers, suppliers, or accounts with opening balances only. All the opening balances for customers, suppliers, and accounts are in the JSON data in the Accounts column. This JSON data shows the total amount for each account. If you have more than one customer with an opening balance and you need to find out a particular customer's opening balance, check the Contact table's Opening_Balance_Amount column instead.

INSERT statements

You can only insert one record into the opening balance table. If you already have a record in there you need to update it. To insert the first record, first, create the JSON data:

[
    {
        "account_id": 579676000000138031,
        "debit_or_credit": "credit",
        "exchange_rate": 1,
        "currency_id": 579676000000000065,
        "amount": 1223441.22
    }
]

Then send it using:

INSERT INTO
    Opening_Balance ([date], [accounts])
values
    ('2024-07-16', '<JSON_Data>')

UPDATE statements

Updating the opening balance accounts is a little tricky as the data returned in the Accounts column has all the opening balance details for bank accounts, customers, and suppliers. For example:

[
    {
        "accounts_list": [
            {
                "bcy_amount_formatted": "£ 1,223,441.22",
                "branch_id": "",
                "exchange_rate": 1.0,
                "product_name": "",
                "Currency_Code": "GBP",
                "debit_or_credit": "credit",
                "product_id": "",
                "account_split_id": "579676000000142036",
                "account_id": "579676000000138031",
                "product_stock_rate": 0,
                "product_stock_rate_formatted": "£ 0.00",
                "product_stock": 0,
                "bcy_amount": 1223441.22,
                "account_name": "Test Bank Account",
                "branch_name": "",
                "currency_id": "579676000000000065"
            },
            {
                "bcy_amount_formatted": "£ 14, 400.00",
                "branch_id": "",
                "exchange_rate": 1.2,
                "product_name": "",
                "Currency_Code": "USD",
                "debit_or_credit": "credit",
                "product_id": "",
                "account_split_id": "579676000000142038",
                "account_id": "579676000000107014",
                "product_stock_rate": 0,
                "product_stock_rate_formatted": "£ 0.00",
                "product_stock": 0,
                "bcy_amount": 14400.0,
                "account_name": "Test COOP USD 32",
                "branch_name": "",
                "currency_id": "579676000000000059"
            }
        ],
        "account_type": "asset",
        "account_type_formatted": "Asset"
    },
    {
        "accounts_list": [
            {
                "debit_or_credit": "debit",
                "product_name": "",
                "bcy_amount_formatted": "£ 299.00",
                "account_id": "579676000000000352",
                "product_stock_rate": 0,
                "product_stock_rate_formatted": "£ 0.00",
                "product_stock": 0,
                "bcy_amount": 299.0,
                "product_id": "",
                "account_name": "Accounts Receivable"
            }
        ],
        "account_type": "accounts_receivable",
        "account_type_formatted": "Accounts Receivable"
    },
    {
        "accounts_list": [
            {
                "bcy_amount_formatted": "£ 1,237,
                542.22","branch_id": "",
                "exchange_rate": 1.0,
                "product_name": "",
                "Currency_Code": "GBP",
                "debit_or_credit": "debit",
                "product_id": "",
                "account_split_id": "579676000000145099",
                "account_id": "579676000000000499",
                "product_stock_rate": 0,
                "product_stock_rate_formatted": "£ 0.00",
                "product_stock": 0,
                "bcy_amount": 1237542.22,
                "account_name": "Opening Balance Adjustments",
                "branch_name": "",
                "currency_id": "579676000000000065"
            }
        ],
        "account_type": "liability",
        "account_type_formatted": "Liability"
    }
]

It's only the first two sections that can be changed. After removing the other sections, you end up with JSON data in this format:

[
    {
        "exchange_rate": 1.0,
        "debit_or_credit": "credit",
        "account_split_id": "579676000000142036",
        "account_id": "579676000000138031",
        "bcy_amount": 1223441.22,
        "currency_id": "579676000000000065"
    },
    {
        "exchange_rate": 1.2,
        "debit_or_credit": "credit",
        "account_split_id": "579676000000142038",
        "account_id": "579676000000107014",
        "bcy_amount": 14400.0,
        "currency_id": "579676000000000059"
    }
]

Then use:

UPDATE Opening_Balance
SET
    Accounts = '<JSON_Data>'

If you need to adjust a customer or supplier opening balance, use:

UPDATE Contact
SET
    Opening_Balance_Amount = 299
WHERE
    Contact_Id = '579676000000093092'

This updates the opening balance at the Zoho Books API end.

DELETE statement

You can only delete a record if no customer or suppliers have an opening balance.

DELETE FROM Opening_Balance

Or use the API table:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/settings/openingbalances/'
    AND Method = 'DELETE'

Organization

Supports: SELECT, INSERT, UPDATE, and DELETE

This table can be used to create, amend, and delete organizations in Zoho Books. There is currently no Zoho Books API documentation on this API call.

INSERT statements

This appears to be the minimum needed to create a new organization:

INSERT INTO
    Organization (
        Name,
        Contact_Name,
        Email,
        Version,
        Currency_Code,
        Time_Zone,
        Address_State_Code
    )
VALUES
    (
        'ESL Two (India)',
        'zoho2',
        'zoho2@fastphotography.co.uk',
        'india',
        'INR',
        'Asia/Calcutta',
        'CG'
    )

UPDATE statements

This changes the first line in the organization's address:

UPDATE Organization
SET
    Address_Street_Address1 = 'Building 1'
WHERE
    organization_id = '20100387237'

DELETE statements

Use something like:

DELETE FROM Organization
WHERE
    Organization_Id = '20100386941'

This can also be done by calling the API table:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/organizations/20100386834'
    AND Method = 'DELETE'

Project

Supports: SELECT, INSERT, UPDATE, and DELETE

INSERT statements

To create a project:

INSERT INTO
    Project (
        Project_Name,
        Customer_Id,
        Currency_Id,
        Billing_Rate_Frequency,
        Billing_Type,
        Rate,
        Description
    )
VALUES
    (
        'PROJ-00002',
        '579676000000093092',
        '579676000000000059',
        'hourly',
        'based_on_project_hours',
        '99.99',
        'Create Zoho Books API Examples'
    )

UPDATE statements

This changes the rate:

UPDATE Project
SET
    Rate = 110
WHERE
    project_id = '579676000000151003'

DELETE statements

Use something like:

DELETE FROM Project
WHERE
    project_id = '579676000000151003'

This can also be done with the API table:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/projects/579676000000152003?organization_id=20099941201'
    AND Method = 'DELETE'

Using the API table to mark a project as active or inactive

Use something like:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/projects/579676000000149075/<X>'
    AND Method = 'POST'

where <X> is one of:

Using the API table to clone projects

Create the JSON data for the project to clone:

{
    "project_name": "PROJ-00003",
    "description": "Cloned Example"
}

Use something like:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/projects/579676000000149075/clone?organization_id=20099941201'
    AND Method = 'POST'
    AND RequestBody = '<JSON_Data>'

Using the API table to invite users to a project

Create JSON data in the form:

{
    "user_name": "Paul West",
    "email": "test27@easysoft.com",
    "user_role": "staff",
    "rate": "0",
    "budget_hours": "0",
    "cost_rate": "35"
}

Then use something like:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/projects/579676000000149075/users/invite?organization_id=20099941201'
    AND Method = 'POST'
    AND RequestBody = '<JSON_Data>'

Using the API table to list invited users

Use something like:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/projects/579676000000149075/users?organization_id=20099941201'
    AND Method = 'GET'

Only users that have accepted the invitation are listed.

Using the API table to assign users to a project

Assigns an existing user to a project. First, create the list of users in some JSON data:

{
    "users": [
        {
            "user_id": "579676000000156001"
        },
        {
            "user_id": "579676000000155001"
        },
        {
            "user_id": "579676000000050001"
        }
    ],
    "cost_rate": 100
}

The cost_rate is also required. Any number of users can be added at once. Then send it through the API table:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/projects/579676000000149075/users?organization_id=20099941201'
    AND Method = 'POST'
    AND RequestBody = '<JSON_Data>'

Using the API table to update a project user

Create JSON data in the form:

{
    "user_role": "admin"
}

You only need to put in the items that need to be updated. Send the JSON data to the API by using:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/projects/579676000000149075/users/579676000000156001?organization_id=20099941201'
    AND Method = 'PUT'
    AND RequestBody = '<JSON_Data>'

The second ID (579676000000156001) is the user_id of the user you want to update.

Using the API table to get a project user's details

To do this, pass in the user_id. SELECT it from the API by using:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/projects/579676000000149075/users/579676000000156001?organization_id=20099941201'
    AND Method = 'GET'

Using the API table to delete a project user

Use something like:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/projects/579676000000149075/users/579676000000156001?organization_id=20099941201'
    AND Method = 'DELETE'

Purchase_Order

Supports: SELECT, INSERT, UPDATE, and DELETE

INSERT statements

Create the JSON data for Line_Items:

[
    {
        "description": "2TB Hard Disk",
        "rate": 99,
        "quantity": 3,
        "account_id": "579676000000000415",
        "tax_id": "579676000000050178"
    }
]

Then send it by using:

INSERT INTO
    Purchase_Order (Vendor_Id, [Date], Line_Items)
VALUES
    ('579676000000081069', '2024-07-30', '<JSON_Data>')

If you leave out the Purchaseorder_Number column, this will be auto populated with the next number in the sequence.

UPDATE statements

This example changes the quantity from 3 to 4:

UPDATE Purchase_Order
SET
    Line_Items = REPLACE (Line_Items, '{"quantity": 3', '{"quantity": 4')
WHERE
    purchaseorder_id = '579676000000092047'

DELETE statements

Use something like:

DELETE FROM Purchase_Order
WHERE
    PurchaseOrder_Id = '579676000000092047'

Alternatively, use the API table. For example:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/purchaseorders/579676000000091014'
    AND Method = 'DELETE'

Using the API table to mark a purchase order as open, billed, or cancelled

Call the API table by using:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/purchaseorders/579676000000089003/status/<X>'
    AND Method = 'POST'

where <X> is one of:

Using the API table to submit or approve purchase orders

Use something like:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/purchaseorders/579676000000089003/<X>'
    AND Method = 'POST'

where <X> is one of:

Submit and approve only work if they have been turned on in the Zoho Books company that you're connecting to.

Using the API table to add purchase order comments

Create the JSON data containing the comment:

{
    "description": "Spoke to customer 22-Jul-2024"
}

Then use the API table to send it:

SELECT ResponseBody FROM api WHERE url = 'books/v3/purchaseorders/579676000000088014/comments' AND Method = 'POST' AND RequestBody = '<JSON_Data>'

If successful, the ResponseBody will contain:

{
    "code": 0,
    "message": "Comments added.",
    "comment": {
        "comment_id": "579676000000107005",
        "purchaseorder_id": "579676000000088014",
        "comments_html_format": "Spoke to customer 22-Jul-2024",
        "description": "Spoke to customer 22-Jul-2024",
        "commented_by_id": "579676000000050001",
        "commented_by": "zoho2",
        "comment_type": "internal",
        "date": "2024-08-01",
        "date_description": "few seconds ago",
        "time": "8:30 AM"
    }
}

To update the comment, change the JSON data and use the API table to send it. Include the comment ID:

SELECT
    ResponseBody
FROM
    api
WHERE
    url = 'books/v3/purchaseorders/579676000000088014/comments/579676000000116023'
    AND Method = 'POST'
    AND RequestBody = '<JSON_Data>'

To delete a comment:

SELECT
    ResponseBody
FROM
    api
WHERE
    url = 'books/v3/purchaseorders/579676000000088014/comments/579676000000107005'
    AND Method = 'DELETE'

Recurring_Bill

Supports: SELECT, INSERT, UPDATE, and DELETE

INSERT statements

You can create a new recurring bill by creating the JSON data required for the line item details:

[
    {
        "account_id": "548752000000000430",
        "description": "A5 Notepad",
        "rate": 9.99,
        "quantity": 3
    }
]

Then run the INSERT statement:

INSERT INTO Recurring_Bill (Recurrence_Name, Start_Date, End_Date, Recurrence_Frequency, Repeat_Every, Vendor_Id, Line_Items) VALUES ('A5 notepads' , '2024-07-08' , '2026-12-12' , 'months' , '1' , '548752000000052031' , '<JSON_Data>')

recurrence_name values need to be unique.

UPDATE statements

This example adds an additional line to the Line_Item column:

[
    {
        "account_id": "548752000000000430",
        "description": "A5 Notepad",
        "rate": 9.99,
        "quantity": 3
    },
    {
        "account_id": "548752000000000430",
        "description": "A3 Paper box 100",
        "rate": 9.99,
        "quantity": 1
    }
]

Then use:

UPDATE Recurring_Bill
SET
    Line_Item = '<JSON_Data>'
WHERE
    Recurring_Bill_Id = '576438000000176002'

DELETE statements

DELETE FROM Recurring_Bill WHERE Recurring_Bill_Id = '576438000000176002'

Using the API table to stop a recurring expense

Use something like:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/recurringbills/576438000000180001/status/stop'
    AND Method = 'POST'

Using the API table to resume a recurring expense

Use something like:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/recurringbills/576438000000180001/status/resume'
    AND Method = 'POST'

The ResponseBody message is "The recurring expense has been activated."

Using the API table to list recurring bill history

Use something like:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/recurringbills/576438000000173123/comments'
    and Method = 'GET'

Recurring_Expense

Supports: SELECT, INSERT, UPDATE, and DELETE

INSERT statements

To create a new recurring expense, first create the JSON data required for the line item details:

[
    {
        "account_id": "576438000000173094",
        "account_name": "Cleaning",
        "description": "Weekly Outside Window Clean",
        "amount": 100
    }
]

Then run the INSERT statement:

INSERT INTO
    Recurring_Expense (
        Recurrence_Name,
        Start_Date,
        End_Date,
        Recurrence_Frequency,
        Repeat_Every,
        Vendor_Id,
        Paid_Through_Account_Id,
        Line_Item
    )
VALUES
    (
        'Window Clean',
        '2024-07-08',
        '2024-12-12',
        'weeks',
        '1',
        '576438000000173100',
        '576438000000118006',
        '<JSON DATA>'
    )

Recurrence_Name values need to be unique.

UPDATE statements

This example adds an additional line to the Line_Item column. Create the JSON data:

[
    {
        "account_id": "576438000000173094",
        "account_name": "Cleaning",
        "description": "Weekly Outside Window Clean",
        "amount": 100
    },
    {
        "account_id": "576438000000173094",
        "account_name": "Cleaning",
        "description": "Weekly Inside Window Clean",
        "amount": 150
    }
]

then:

UPDATE Recurring_Expense
SET
    Line_Item = '<JSON_Data>'
WHERE
    Recurring_Expense_Id = '576438000000176002'

The Recurring_Expense table does support multiple lines and the total gets increased. However, you can only refer to item lines two and beyond if you select the data through the Zoho Books API.

DELETE statements

DELETE FROM Recurring_Expense
WHERE
    Recurring_Expense_Id = '576438000000176002'

Using the API table to stop recurring expense

Use something like:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/recurringexpenses/576438000000180001/status/stop'
    AND Method = 'POST'

The ResponseBody message is "The recurring expense has been stopped."

Using the API table to resume recurring expenses

Use something like:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/recurringexpenses/576438000000180001/status/resume'
    AND Method = 'POST'

The ResponseBody message says "The recurring expense has been activated."

Using the API table to list child expenses

Use something like:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/recurringexpenses/576438000000173123/expenses'
    AND Method = 'GET'

The ResponseBody returns:

{
    "code": 0,
    "message": "success",
    "expensehistory": [
        {
            "expense_id": "576438000000173133",
            "date": "2024-07-05",
            "account_name": "Cleaning",
            "vendor_name": "Clare's Cleaners",
            "paid_through_account_name": "COOP Account 4",
            "customer_name": "",
            "total": 63.87,
            "status": "nonbillable"
        },
        {
            "expense_id": "576438000000174001",
            "date": "2024-07-06",
            "account_name": "Cleaning",
            "vendor_name": "Clare's Cleaners",
            "paid_through_account_name": "COOP Account 4",
            "customer_name": "",
            "total": 64.05,
            "status": "nonbillable"
        },
        {
            "expense_id": "576438000000175001",
            "date": "2024-07-07",
            "account_name": "Cleaning",
            "vendor_name": "Clare's Cleaners",
            "paid_through_account_name": "COOP Account 4",
            "customer_name": "",
            "total": 63.98,
            "status": "nonbillable"
        },
        {
            "expense_id": "576438000000175047",
            "date": "2024-07-08",
            "account_name": "Cleaning",
            "vendor_name": "Clare's Cleaners",
            "paid_through_account_name": "COOP Account 4",
            "customer_name": "",
            "total": 64.12,
            "status": "nonbillable"
        }
    ],
    "page_context": {
        "page": 1,
        "per_page": 200,
        "has_more_page": false,
        "report_name": "expense history",
        "sort_column": "expense_account",
        "sort_order": "D"
    }
}

In this example, I had one daily expense record. The expensehistory array shows each of the transactions created. These transactions were created in the GBP currency, but the test company default is USD so the total amount changes each day to reflect the changes in exchange rate.

Using the API table to list recurring expense history

Use something like:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/recurringexpenses/576438000000173123/comments'
    AND Method = 'GET'

ResponseBody returns:

{
    "code": 0,
    "message": "success",
    "comments": [
        {
            "comment_id": "576438000000175055",
            "recurring_expense_id": "576438000000173123",
            "description": "Expense Created for £50.00",
            "commented_by_id": "",
            "commented_by": "Zoho Books",
            "date": "2024-07-08",
            "date_description": "22 hours ago",
            "time": "6:15 AM",
            "operation_type": "Added",
            "transaction_id": "576438000000175047",
            "transaction_type": "expense"
        },
        {
            "comment_id": "576438000000175009",
            "recurring_expense_id": "576438000000173123",
            "description": "Expense Created for £50.00",
            "commented_by_id": "",
            "commented_by": "Zoho Books",
            "date": "2024-07-07",
            "date_description": "yesterday",
            "time": "6:14 AM",
            "operation_type": "Added",
            "transaction_id": "576438000000175001",
            "transaction_type": "expense"
        },
        {
            "comment_id": "576438000000174009",
            "recurring_expense_id": "576438000000173123",
            "description": "Expense Created for £50.00",
            "commented_by_id": "",
            "commented_by": "Zoho Books",
            "date": "2024-07-06",
            "date_description": "2 days ago",
            "time": "6:14 AM",
            "operation_type": "Added",
            "transaction_id": "576438000000174001",
            "transaction_type": "expense"
        },
        {
            "comment_id": "576438000000173141",
            "recurring_expense_id": "576438000000173123",
            "description": "Expense Created for £50.00",
            "commented_by_id": "576438000000049001",
            "commented_by": "zoho1",
            "date": "2024-07-05",
            "date_description": "4 days ago",
            "time": "2:44 AM",
            "operation_type": "Added",
            "transaction_id": "576438000000173133",
            "transaction_type": "expense"
        },
        {
            "comment_id": "576438000000173125",
            "recurring_expense_id": "576438000000173123",
            "description": "Recurring expense created for £50.00",
            "commented_by_id": "576438000000049001",
            "commented_by": "zoho1",
            "date": "2024-07-05",
            "date_description": "4 days ago",
            "time": "2:44 AM",
            "operation_type": "Added",
            "transaction_id": "",
            "transaction_type": "recurring_expense"
        }
    ]
}

In this example, the history returned relates to the child expenses created previously.

Recurring_Invoice

Supports: SELECT, INSERT, UPDATE, and DELETE

INSERT statements

To create a new recurring invoice, first create the JSON data required for the line item details:

[
    {
        "description": "Cloud Storage Monthly",
        "rate": 9.99,
        "quantity": 3,
        "tax_id": "579676000000050178"
    }
]

Then run:

INSERT INTO
    Recurring_Invoice (
        customer_id,
        Line_Items,
        recurrence_name,
        recurrence_frequency,
        Repeat_Every,
        start_date,
        end_date
    )
VALUES
    (
        '579676000000050204',
        '<JSON_Data>',
        'Monthly Cloud Charge',
        'weeks',
        '1',
        '2024-07-29',
        '2024-12-30'
    )

Recurrence_Name values need to be unique.

UPDATE statements

This example changes the occurrence to two weeks:

UPDATE Recurring_Invoice
SET
    Repeat_Every = '2'
WHERE
    Recurring_Invoice_Id = '579676000000076008'

DELETE statements

DELETE FROM Recurring_Invoice
WHERE
    Recurring_Invoice_Id = '579676000000076008'

Or use the API table:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/recurringinvoices/579676000000114023/'
    AND Method = 'DELETE'

Using the API table to stop recurring invoice

Use something like:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/recurringinvoices/579676000000076008/status/stop'
    AND Method = 'POST'

The ResponseBody message is: "The recurring invoice has been stopped."

Using the API table to resume recurring invoices

Use something like:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/recurringinvoices/579676000000076008/status/resume'
    AND Method = 'POST'

ResponseBody message is: "The recurring invoice has been activated."

Using the API table to list recurring invoice history

Use something like:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/recurringinvoices/579676000000076008/comments'
    AND Method = 'GET'

Retainer_Invoice

Supports: SELECT, INSERT, UPDATE, and DELETE

SELECT, INSERT, and UPDATE statements

The Retainer_Invoice table works in the same way as the Invoice table.

Using the API table to select retainer invoices

Use something like:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/retainerinvoices/579676000000081041'
    AND Method = 'GET'

DELETE statements

Use something like:

DELETE FROM Retainer_Invoice
WHERE
    RetainerInvoice_Id = '579676000000091002'

This can also be done with the API table. For example:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/retainerinvoices/579676000000091014'
    AND Method = 'DELETE'

Using the API table to mark retainer invoices as sent, void, or draft

Call the API table using:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/retainerinvoices/579676000000089003/status/<X>'
    AND Method = 'POST'

where <X> is one of:

Using the API table to submit or approve retainer invoices

Use something like:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/retainerinvoices/579676000000089003/<X>'
    AND Method = 'POST'

where <X> is one of:

submit and approve only work if they have been turned on in the Zoho Books company you're connecting to.

Using the API table to manage recurring invoice comments

Refer to this section (remember to specify retainerinvoices instead of invoices).

Salesorder

Supports: SELECT, INSERT, UPDATE, and DELETE

INSERT statements

Create the JSON data for line items:

[
    {
        "description": "2TB Hard Disk",
        "rate": 99,
        "quantity": 3,
        "account_id": "579676000000000415",
        "tax_id": "579676000000050178"
    }
]

Then send it using:

INSERT INTO
    Salesorder (Customer_Id, [Date], Line_Items)
values
    ('579676000000050204', '2024-07-30', '<JSON_Data>')

If you leave out the Salesorder_Number column, this will be autopopulated with the next number in the sequence.

UPDATE statements

This example update changes the quantity from 3 to 4:

UPDATE Salesorder
SET
    Line_Items = REPLACE (
        Line_Items,
        '{""quantity"": 3',
        '{""quantity"": 4'
    )
WHERE
    Salesorder_Id = '579676000000119019'

DELETE statements

Use something like:

DELETE FROM Salesorder
WHERE
    Salesorder_Id = '579676000000119019'

This can also be done by calling the API table. For example:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/salesorders/579676000000116031'
    AND Method = 'DELETE'

Using the API table to mark a sales order as open or void

Call the API table by using:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/salesorders/579676000000089003/status/<X>'
    AND Method = 'POST'

where <X> is one of:

Using the API table to submit or approve sales orders

Use something like:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/salesorders/579676000000089003/<X>'
    AND Method = 'POST'

where <X> is one of:

submit and approve only work if they have been turned on in the Zoho Books company you're connecting to.

Using the API table to manage sales order comments

Refer to this section and change purchaseorders to salesorders.

Salesreceipt

Supports: SELECT, INSERT, UPDATE, and DELETE

INSERT statements

You can create a new sales receipt by firstly creating the <JSON_Data> required for the line item details:

[
    {
        "description": "2TB Hard Disk",
        "rate": 99,
        "quantity": 3,
        "account_id": "593625000000000503"
    }
]

Then run the INSERT statement:

INSERT INTO
    Salesreceipt (Customer_Id, [Date], Line_Items)
VALUES
    ('593625000000054001', '2024-08-17', '<JSON_Data>')

UPDATE statements

Alter the JSON data from the INSERT and send it back by using:

UPDATE Salesreceipt
SET
    Line_Items = '<JSON_Data>'
WHERE
    sales_receipt_id = '593625000000065001'

DELETE statements

DELETE FROM Salesreceipt
WHERE
    Sales_Receipt_Id = '593625000000065001'

Or:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/salesreceipts/593625000000065001?organization_id=20100449894'
    AND Method = 'DELETE'

Task

Supports: SELECT, INSERT, UPDATE, and DELETE

INSERT statements

You can create a task and assign it to a project by running:

INSERT INTO
    Task (Project_Id, Task_Name, Description)
values
    (
        '579676000000093167',
        'QA Test',
        'Test the source code developed'
    )

UPDATE statements

You can update tasks by running:

UPDATE Task
SET
    description = description + '.'
WHERE
    Project_Id = '579676000000093167'
    AND Task_Id = '579676000000160004'

Project_Id is mandatory.

DELETE statements

Directly in the driver using:

DELETE FROM Task
WHERE
    Project_Id = '579676000000093167'
    AND Task_id = '579676000000164003'

You need to give it both IDs.

This can also be done by calling the API table. For example:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/projects/579676000000093167/tasks/579676000000160004?organization_id=20099941201'
    AND Method = 'DELETE'

Tax

Supports: SELECT, INSERT, UPDATE, and DELETE

These examples were created under a Zoho Books organization set up for UK taxes.

INSERT statements

You can create a new tax time entry by running:

INSERT INTO
    Tax (Tax_Name, Tax_Percentage)
VALUES
    ('S Reduced rate', 17.5)

UPDATE statements

You can update the Tax_Percentage by running:

UPDATE Tax SET Tax_Percentage=17.75 WHERE Tax_Id = '579676000000165002'

DELETE statements

Use something like:

DELETE FROM Tax WHERE tax_name = 'S Reduced rate'

This can also be done by calling the API table. For example:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/settings/taxes/579676000000169001?organization_id=20099941201'
    AND Method = 'DELETE'

Time_Entry

Supports: SELECT, INSERT, UPDATE, and DELETE

INSERT statements

You can create a time entry and add time by running:

INSERT INTO
    Time_Entry (Project_ID, Task_Id, Log_Date, Log_Time)
VALUES
    (
        '579676000000093167',
        '579676000000093228',
        '2024-08-14',
        '02:00'
    )

Both Project_Id and Task_Id are required. The log_time has to be in the format hh:mm.

UPDATE statements

You can update the Log_Time by running:

UPDATE Time_Entry SET Log_Time = '03:00' WHERE Time_Entry_Id = '579676000000160017'

DELETE statements

Use something like:

DELETE FROM Time_Entry WHERE Time_Entry_Id = '579676000000160017'

This can also be done by using the API table. For example:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/projects/timeentries/579676000000160021?organization_id=20099941201'
    AND Method = 'DELETE'

Using the API table to start a timer

You can ONLY start the timer for the user account you're logged in as. To do this, first create a blank Time_Entry record by sending:

INSERT INTO
    Time_Entry (Project_Id, Task_Id, User_Id, Log_Date)
VALUES
    (
        '579676000000149075',
        '579676000000161018',
        '579676000000156001',
        '2024-08-14'
    )

The user_id value passed in must be the same user_id as you're connected as with the Easysoft Zoho Books ODBC driver. If you use LastId after the insert, the column value contains the Time_Entry_Id of the record created. Start the timer by passing that column in by using the API table:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/projects/timeentries/time_entry_id/timer/start?organization_id=20099941201'
    AND Method = 'POST'

where Time_Entry_Id is the ID value rather than the name.

Using the API table to get an active timer

If you have an active timer running, you can get the details through the API table by calling:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/projects/timeentries/runningtimer/me?organization_id=20099941201'
    AND Method = 'GET'

Using the API table to stop a timer

If you have an active timer running, you can stop it with:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/projects/timeentries/timer/stop?organization_id=20099941201'
    AND Method = 'POST'

Users

Supports: SELECT, INSERT, UPDATE, and DELETE

INSERT statements

INSERT statements can be very simple:

INSERT INTO
    Users ([Name], Email, Role_Id, Billing_Rate)
VALUES
    (
        'Sally Smedley',
        's.smedley@easysoft.com',
        '579676000000000682',
        150
    )

UPDATE statements

Use something like:

UPDATE Users
SET
    Billing_Rate = 159
WHERE
    User_Id = '579676000000160069'

DELETE statements

Use something like:

DELETE FROM Users
WHERE
    User_Id = '579676000000160069'

Or you can use the API table, if you have the Account_Id:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/users/579676000000160069?organization_id=20099941201'
    AND Method = 'DELETE'

Using the API table to mark a user as active or inactive

Use something like:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/users/579676000000160069/<X>?organization_id=20099941201'
    and Method = 'POST'

where <X> is one of:

Vendor_Credit

Supports: SELECT, INSERT, UPDATE, and DELETE

INSERT statements

To create new vendor credits, first create the JSON data required for the line_item details to be credited:

[
    {
        "account_id": "593625000000000388",
        "description": "Dell Laser Paper",
        "rate": 3.50,
        "quantity": 2
    }
]

Then run:

INSERT INTO
    Vendor_Credit (Vendor_Credit_Number, Vendor_Id, [Date], Line_Items)
VALUES
    (
        'VC-000001',
        '593625000000055001',
        '2024-08-19',
        '<JSON_Data>'
    )

UPDATE statements

Alter the JSON data from the previous example and then use:

UPDATE Vendor_Credit
SET
    Line_Items = '" & EscQuotes(JSON DATA) & "'
WHERE
    Vendor_Credit_Id = '593625000000067162'

DELETE statements

DELETE FROM Vendor_Credit
WHERE
    vendor_credit_id = '593625000000067162'

Or use the API table:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/vendorcredits/593625000000064222?organization_id=20100449894'
    AND Method = 'DELETE'

Using the API table to create a refund

Create the JSON for the refund:

{
    "date": "2024-08-19",
    "refund_mode": "cash",
    "reference_number":VC Payment 1,
    "amount": 1,
    "account_id": 593625000000054023
}

Then send it by using the API table:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/vendorcredits/593625000000064260/refunds?organization_id=20100449894'
    AND Method = 'POST'
    AND RequestBody = '<JSON_Data>'

Using the API table to DELETE refunds

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/vendorcredits/593625000000064260/refunds/593625000000064278?organization_id=20100449894'
    AND Method = 'DELETE'

The second ID used is the vendor_credit_refund_id.

Vendor_Payment

Supports: SELECT, INSERT, UPDATE, and DELETE

INSERT statements

First, create the JSON data required for the bill to be paid:

[
    {
        "bill_id": "593625000000068005",
        "amount_applied": 5
    },
    {
        "bill_id": "593625000000069005",
        "amount_applied": 1
    }
]

Then run:

INSERT INTO
    Vendor_Payment (Vendor_Id, Payment_Mode, [Date], Amount, Bills)
VALUES
    (
        '593625000000055001',
        'cash',
        '2024-08-19',
        6,
        '<JSON_Data>'
    )

UPDATE statements

Alter the JSON data from the insert example and send it back using:

UPDATE Vendor_Payment
SET
    Amount = 6,
    Bills = '<JSON_Data>'
WHERE
    Payment_Id = '593625000000064094'

DELETE statements

DELETE FROM Vendor_Payment
WHERE
    Payment_Id = '593625000000067040'

Or use API table:

SELECT
    *
FROM
    API
WHERE
    URL = 'books/v3/Vendor_Payment/593625000000067040?organization_id=20100449894'
    AND Method = 'DELETE'

Appendix A

SQL functionality

Supported string functions

Supported numeric functions

Supported date and time functions

Appendix B

Zoho Books tables and columns

Bank_Account

Bill

Chart_Of_Account

Contact

Credit_Note

Currency

Customer_Payment

Delivery_Challan

Estimate

Expense

Invoice

Item

Journal

Opening_Balance

Organization

Project

Purchase_Order

Recurring_Bill

Recurring_Expense

Recurring_Invoice

Retainer_Invoice

Salesorder

Salesreceipt

Task

Tax

Time_Entry

Users

Vendor_Credit

Vendor_Payment

Multilingual versions

Where a two digit country code is used, refer to this table:

Global GCC is the generic Zoho Books API version you're given if you do not reside in a country they directly support.