Working with Zoho Books data

The Easysoft ODBC-Zoho Books Driver lets you use SQL to work with Zoho Books data. Your application might build the SQL for you or you might have to specify the SQL statements yourself.

SELECT statements

Use either a Zoho Books table or the API view in your queries. For example, both these statements fetch the comments for a particular bill:

SELECT
  *
FROM
  Bill_Comment
WHERE
  Bill_Id = '579676000000081095'

=Or-

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

Example select queries

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

SELECT
  *
FROM
  Employee
WHERE
  Name = 'Roland Mendel'

SELECT
  *
FROM
  Invoice_Comment
WHERE
  Invoice_Id = '579676000000050234'

INSERT statements

When inserting a record that has one or more line items, specify the line items with a JSON array. For example, this line item is for a recurring expense

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

In the INSERT statement, specify the JSON as a single line. For example:

INSERT INTO
  Recurring_Invoice (
    Customer_Id,
    Line_Items,
    Recurrence_Name,
    Recurrence_Frequency,
    Repeat_Every,
    Start_Date,
    End_Date
  )
VALUES
  (
    '579676000000050204',
    '[{"description":"Cloud Storage Monthly","rate":9.99,"quantity":3,"tax_id":"579676000000050178"}]',
    'Monthly Cloud Charge',
    'weeks',
    '1',
    '2024-07-29',
    '2024-12-30'
  )

Example inserts

Bill

Create a new bill:

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',
    '[
      {
        "item_order": 1,
        "account_name": "IT and Internet Expenses",
        "account_id": "576438000000000415",
        "description": "Dell Laser Paper",
        "rate": 3.5,
        "quantity": 16,
        "item_total": 56
      }
     ]'
  )

Use the yyyy-mm-dd format for Date and Due_Date. Vendor_Id is a Contact_Id from the Contact table where the Contact_Type is 'vendor'. The JSON for the line items is:

[
  {
    "item_order": 1,
    "account_name": "IT and Internet Expenses",
    "account_id": "576438000000000415",
    "description": "Dell Laser Paper",
    "rate": 3.5,
    "quantity": 16,
    "item_total": 56
  }
]
Invoice comment

Insert an invoice comment:

INSERT INTO
  Invoice_Comment (
    Invoice_Id,
    Description,
    Show_Comment_To_Clients,
    Payment_Expected_Date
  )
VALUES
  (
    '579676000000050234',
    'Customer comment.',
    1,
    '2024-08-29'
  )
Contact

Create a new contact:

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

Create a new credit note:

INSERT INTO
  Credit_Note (Customer_Id, Currency_Id, [Date], Line_Items)
values
  (
    '576438000000066678',
    '576438000000000059',
    '2021-06-14',
    '[
      {
        "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"
      }
     ]'
  )

As a minimum, you need to include Customer_Id, Currency_Id, Date, and Line_Items. If you don’t have auto numbering turned on in Zoho Books, include Creditnote_Number.

The JSON for the line items is:

[
  {
    "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"
  }
]
Payment

Create a new payment:

INSERT INTO
  Customer_Payment (Customer_Id, Payment_Mode, Amount, Invoices)
VALUES
  (
    '576438000000072770',
    'cash',
    373.76,
    '[
      {
        "Invoice_Id": "576438000000104809",
        "invoice_number": "39440",
        "date": "2024-07-03",
        "invoice_amount": "373.76",
        "amount_applied": "373.76",
        "balance_amount": "0"
      }
     ]'
  )

The JSON for Invoices is:

[
  {
    "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: [{Inv 1},{Inv 2],{Inv n}].

Delivery challan

Create a new delivery challan:

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}'
  )
Estimate

Create a new estimate:

INSERT INTO
  Estimate ([Date], Customer_Id, Line_Items)
VALUES
  (
    '2024-07-08',
    '576438000000059096',
    '[
      {
        "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"
      }
     ]'
  )

The JSON for the line items is:

[
  {
    "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"
  }
]

There are two groups here: Leads and Paper. header_name defines a group and the items that belong to each group. The items in the Leads group have a discount applied to them. No total values are supplied, rate, quantity, and tax_id are all that’s required.

Expense

Submit a new expense:

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',
    '[
      {
        "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
      }
     ]'
  )

The JSON for the line items is:

[
  {
    "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
  }
]
Item

Insert 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'
  )
Journal

Create a new journal:

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',
    '[
      {
        "account_id": 579676000000000400,
        "customer_id": 579676000000050200,
        "description": "Standard cheque charge",
        "tax_id": 579676000000050200,
        "debit_or_credit": "debit",
        "amount": 4.99
      },
      {
        "account_id": 579676000000000500,
        "customer_id": 579676000000050200,
        "description": "Bank charge",
        "debit_or_credit": "credit",
        "amount": 4.99
      }
     ]'
  )

The JSON data for the line items is:

[
  {
    "account_id": 579676000000000400,
    "customer_id": 579676000000050200,
    "description": "Standard cheque charge",
    "tax_id": 579676000000050200,
    "debit_or_credit": "debit",
    "amount": 4.99
  },
  {
    "account_id": 579676000000000500,
    "customer_id": 579676000000050200,
    "description": "Bank charge",
    "debit_or_credit": "credit",
    "amount": 4.99
  }
]

There needs to be at least one debit and one credit line.

Purchase order

Create a new purchase order:

INSERT INTO
  Purchase_Order (Vendor_Id, [Date], Line_Items)
VALUES
  (
    '579676000000081069',
    '2024-07-30',
    '[
      {
        "description": "2TB Hard Disk",
        "rate": 99,
        "quantity": 3,
        "account_id": "579676000000000415",
        "tax_id": "579676000000050178"
      }
     ]'
  )

The JSON data for the line items is:

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

Create a new recurring bill:

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',
    '[
      {
        "account_id": "548752000000000430",
        "description": "A5 Notepad",
        "rate": 9.99,
        "quantity": 3
      }
     ]'
  )

The JSON data for the line items is:

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

Create a new recurring expense:

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',
    '[
      {
        "account_id": "576438000000173094",
        "account_name": "Cleaning",
        "description": "Weekly Outside Window Clean",
        "amount": 100
      }
     ]'
  )

The Recurrence_Name value must be unique.

The JSON data for the line items is:

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

UPDATE statements

To update records with JSON columns (for example, Line_Items columns), you first need to retrieve the existing JSON. Then change the existing values, or add new JSON to the existing JSON. For example:

SELECT Line_Items FROM Journal WHERE Journal_Id = '579676000000130007'

returns the following (formatted for readability):

{
  "account_id": 579676000000000400,
  "customer_id": 579676000000050200,
  "description": "Standard cheque charge",
  "tax_id": 579676000000050200,
  "debit_or_credit": "debit",
  "amount": 4.99
},
{
  "account_id": 579676000000000500,
  "customer_id": 579676000000050200,
  "description": "Bank charge",
  "debit_or_credit": "credit",
  "amount": 4.99
}

To change, say, the journal amounts, edit these parts of the JSON:

{
  "account_id": 579676000000000400,
  "customer_id": 579676000000050200,
  "description": "Standard cheque charge",
  "tax_id": 579676000000050200,
  "debit_or_credit": "debit",
  "amount": 5.99
},
{
  "account_id": 579676000000000500,
  "customer_id": 579676000000050200,
  "description": "Bank charge",
  "debit_or_credit": "credit",
  "amount": 5.99
}

and include the revised JSON in an UPDATE statement. Enclose the JSON in square brackets. For example:

UPDATE Journal
SET
  Line_Items = '[
    {
        "account_id": 579676000000000400,
        "customer_id": 579676000000050200,
        "description": "Standard cheque charge",
        "tax_id": 579676000000050200,
        "debit_or_credit": "debit",
        "amount": 5.99
    },
    {
        "account_id": 579676000000000500,
        "customer_id": 579676000000050200,
        "description": "Bank charge",
        "debit_or_credit": "credit",
        "amount": 5.99
    }
]'
WHERE
  Journal_id = '579676000000130007'

In the next example, an additional line item is added to a delivery challan:

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

becomes:

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

and is submitted by using:

UPDATE Delivery_Challan
SET
  Line_Item = '[
    {
        "account_id": "548752000000000430",
        "description": "A5 Notepad",
        "rate": 9.99,
        "quantity": 3
    },
    {
        "account_id": "548752000000000430",
        "description": "A3 Paper box 100",
        "rate": 9.99,
        "quantity": 1
    }
]'
WHERE
  deliverychallan_id = '576438000000176002'

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

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

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

You can also us the API view to make updates to your Zoho Books data. Use PUT as the method in your SELECT statement. For example:

SELECT
  *
FROM
  API
WHERE
  URL = 'books/v3/customerpayments/576438000000172004/refunds'
  AND Method = 'PUT'
  AND RequestBody = '{
    "date": "2024-07-03",
    "refund_mode": "cash",
    "amount": 1010,
    "from_account_id": "576438000000000346"
}'

Example updates

Bank account

Change the name of a bank account:

UPDATE Bank_Account
SET
  Account_Name = 'COOP Account'
WHERE
  Account_Id = '576438000000114002'
Chart of account

Update a chart of account’s description:

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

Update a contact’s company name:

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

Change the date of a credit note:

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

Change the description for a customer payment:

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

Change the date of an expense:

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

Update an invoice comment:

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

Add 10% onto the sales price for all goods:

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

Change the address of an organization:

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

Change the purchase order quantity from 3 to 4:

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

Change a recurring invoice interval:

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

Change a tax rate:

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

DELETE statements

The Easysoft ODBC-Zoho Books Driver provides two ways to delete records. Either specify a table in your DELETE statement:

DELETE FROM Bank_Account
WHERE
  Account_Id = '579676000000088102'

or use the API view instead:

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

If you’re only deleting a single record and have the ID of this record, using the API view is faster and uses fewer Zoho Books API calls than using a DELETE statement.

When you run a DELETE statement or use API Method='DELETE', any rows that match your criteria are deleted. There’s no undo option and no way of getting the rows back.

Example deletes

Invoice comment

Delete a comment from an invoice:

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

You need to specify both the Invoice_Id and Comment_Id columns.

If you attempt to delete a system generated comment, history record, or customer comment you’ll get this error:

[{PRODUCT}]Error <4073: You cannot delete system generated or customer comments.>
Bill

Delete a single bill:

DELETE FROM Bill WHERE Bill_Id='579676000000088074'

-Or-

SELECT
  *
FROM
  API
WHERE
  URL = 'books/v3/bills/579676000000089067'
  AND Method = 'DELETE'
Customer payment

Delete a customer payment:

DELETE FROM Customer_Payment
WHERE
  Payment_Id = '576438000000168003'

-Or-

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

Delete a journal:

DELETE FROM Journal
WHERE
  Journal_Id = '579676000000130007'

-Or-

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

Delete an address:

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

To delete (or insert or update) an address, you must use the API view. You need to specify the Contact_Id of the contact and the Address_Id of the address.

Multiple records

These examples delete multiple records:

DELETE FROM Bank_Account WHERE Balance = 0
DELETE FROM Bill WHERE [Date] = '2024-07-01'
DELETE FROM Estimate WHERE [Date] = '2024-07-08'
DELETE FROM Item WHERE [Name] = '1TB Hard Disk'

API view

The API view lets you use SQL to make Zoho Books API calls. The view has these columns:

Column Description

URL

A Zoho Books API endpoint.

Method

GET, PUT, POST, or DELETE.

Code

HTTP status code.

Code Description

200

OK

201

Created

400

Bad request

401

Unauthorized (Invalid AuthToken)

404

Error

405

Method Not Allowed (Method you have called is not supported for the invoked API)

429

Rate Limit Exceeded (API usage limit exceeded)

500

Internal Error

RequestType

If NULL, JSON is expected in RequestBody.

RequestBody

Data sent to the Zoho Books API.

ResponseType

The type of data in the response. For example, application/json;charset=UTF-8.

ResponseBody

Data returned from the Zoho Books API.

Use SELECT statements with the API view. To select data set Method to GET. To insert data set Method to POST. To insert data set Method to PUT. To delete data set Method to DELETE.

Use a WHERE clause to specify the URL, Method, and, if updating or inserting data, RequestBody. For example:

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

-- List bank accounts
SELECT
  ResponseBody
FROM
  API
WHERE
  URL = 'books/v3/bankaccounts'
  AND Method = 'GET'

-- Remove an account a chart of accounts
SELECT
  *
FROM
  API
WHERE
  URL = 'books/v3/chartofaccounts/579676000000107086'
  AND Method = 'DELETE'

-- Add a contact address
SELECT
  *
FROM
  API
WHERE
  URL = '/books/v3/contacts/576438000000152001/address'
  AND Method = 'POST'
  AND RequestBody = '[
    {
        "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"
    }
]'


-- Update a contact address
SELECT
  *
FROM
  API
WHERE
  URL = '/books/v3/contacts/576438000000152001/address/576438000000149024'
  AND Method = 'PUT'
  AND RequestBody = '[
    {
        "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"
    }
]
]'

For more information, consult the Zoho Books API documentation.

JSON columns

Columns that can contain multiple values are in JSON format. For example, a bill’s line items (Bill table’s Line_Items column):

To update records with JSON columns (for example, Line_Items columns), you first need to retrieve the existing JSON. Then change the existing values, or add new JSON to the existing JSON. For example:

SELECT Line_Items FROM Journal WHERE Journal_Id = '579676000000130007'

returns the following (formatted for readability):

 {
  "account_id": 579676000000000400,
  "customer_id": 579676000000050200,
  "description": "Standard cheque charge",
  "tax_id": 579676000000050200,
  "debit_or_credit": "debit",
  "amount": 4.99
},
{
  "account_id": 579676000000000500,
  "customer_id": 579676000000050200,
  "description": "Bank charge",
  "debit_or_credit": "credit",
  "amount": 4.99
}

To change, say, the journal amounts, edit these parts of the JSON:

{
  "account_id": 579676000000000400,
  "customer_id": 579676000000050200,
  "description": "Standard cheque charge",
  "tax_id": 579676000000050200,
  "debit_or_credit": "debit",
  "amount": 5.99
},
{
  "account_id": 579676000000000500,
  "customer_id": 579676000000050200,
  "description": "Bank charge",
  "debit_or_credit": "credit",
  "amount": 5.99
}

and include the revised JSON in an UPDATE statement. Enclose the JSON in square brackets. For example:

UPDATE Journal
SET
  [Line_Items] = '[
    {
        "account_id": 579676000000000400,
        "customer_id": 579676000000050200,
        "description": "Standard cheque charge",
        "tax_id": 579676000000050200,
        "debit_or_credit": "debit",
        "amount": 5.99
    },
    {
        "account_id": 579676000000000500,
        "customer_id": 579676000000050200,
        "description": "Bank charge",
        "debit_or_credit": "credit",
        "amount": 5.99
    }
]'
WHERE
  Journal_id = '579676000000130007'

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.

All the data names in the Zoho Books API are in lower case. JSON is case sensitive.

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 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, set the value to []. For example:

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

LastId view

Use this view to get information back from the Zoho Books API after you’ve run an INSERT, UPDATE, or DELETE statement.

The LastId view contains these columns:

Column Description

Code

API status code (0 is OK).

Message

API response message. For example, The account has been created.

Operation

INSERT, UPDATE, or DELETE. The type of SQL statement that ran.

Api

The Zoho Books API called.

Key

Key column name.

Value

Key column value.

Full message

JSON response sent back from the API.

Successful inserts always update the LastId view.

UPDATE and DELETE statements only change the LastId view 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 view only applies to the last action on the current connection.

Here’s an example that shows the LastId result set retrieved after a successful update:

UPDATE Invoice
SET
  Line_Items = '{
    "line_item_id": "576438000000205017",
    "tax_name": "10% Tax",
    "item_order": 8,
    "quantity": 2,
    "header_name": "Storage",
    "description": "8TB Surveillance Hard Drive",
    "account_id": "576438000000000376",
    "rate": 169.14,
    "tax_id": "576438000000056007",
    "bcy_rate": 169.14,
    "account_name": "Sales",
    "pricing_scheme": "unit",
    "header_id": "576438000000197034",
    "tax_type": "tax",
    "tax_percentage": 10,
    "item_total": 338.28,
    "line_item_taxes": [
        {
            "tax_id": "576438000000056007",
            "tax_name": "10% Tax (10%)",
            "tax_amount": 33.81
        }
    ]
}'
WHERE
  Invoice_Id = '576438000000205001'

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