Tally Connection Details

Introduction

Connector Version

This documentation is based on version 23.0.8936 of the connector.

Get Started

Tally Version Support

The connector leverages the Tally API to enable access to Tally. The connector supports both Tally ERP 9 and Tally Prime.

Establish a Connection

Connect to Tally

Set the following connection properties to connect to Tally Instance:

• URL (Set this to the URL for Tally instance. For example: http://localhost:9000).

Important Notes

Configuration Files and Their Paths

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

Advanced Features

This section details a selection of advanced features of the Tally connector.

User Defined Views

The connector allows you to define virtual tables, called user defined views, whose contents are decided by a pre-configured query. These views are useful when you cannot directly control queries being issued to the drivers. See User Defined Views for an overview of creating and configuring custom views.

SSL Configuration

Use SSL Configuration to adjust how connector handles TLS/SSL certificate negotiations. You can choose from various certificate formats; see the SSLServerCert property under "Connection String Options" for more information.

Proxy

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

Query Processing

The connector offloads as much of the SELECT statement processing as possible to Tally and then processes the rest of the query in memory (client-side).

See Query Processing for more information.

User Defined Views

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

There are two ways to create user defined views:

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

Define Views Using a Configuration File

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

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

This User Defined View configuration file is formatted as follows:

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

For example:

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

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

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

Define Views Using DDL Statements

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

Create a View

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

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

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

Alter a View

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

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

The view is then updated in the JSON configuration file.

Drop a View

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

DROP LOCAL VIEW [MyViewName]

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

Schema for User Defined Views

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

Work with User Defined Views

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

SELECT * FROM Customers WHERE City = 'Raleigh';

An example of a query to the driver:

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

Resulting in the effective query to the source:

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

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

SSL Configuration

Customize the SSL Configuration

By default, the connector attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.

To specify another certificate, see the SSLServerCert property for the available formats to do so.

Data Model

Overview

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

Key Features

• The connector models Tally entities like documents, folders, and groups as relational views, allowing you to write SQL to query Tally data.
• Stored procedures allow you to execute operations to Tally
• Live connectivity to these objects means any changes to your Tally account are immediately reflected when using the connector.

Views

Views describes the available views. Views are statically defined to entities such as Company, Employees, and Ledgers.

Stored Procedures

Stored Procedures are function-like interfaces to Tally. Stored procedures allow you to execute operations to Tally, including downloading documents and moving envelopes.

Views

Views are similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

Tally Connector Views

Company

Retrieve Company Details.

Table Specific Information

Select

The connector will use the Tally API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• String supports the '=, !=,<, <=, >, >=, LIKE, ORDER BY' comparisons.

For example these queries are processed server side:

SELECT * FROM Company WHERE Name LIKE 'cdata%'

SELECT * FROM Company WHERE CompanyFBTAssesseeType != 'Default'

SELECT * FROM Company WHERE StateName = 'Karnataka' AND PINCode >= '560034'

SELECT * FROM Company WHERE CountryName = 'India' OR CountryName = 'US'

SELECT * FROM Company WHERE PINCode > '560034'

SELECT * FROM Company ORDER BY DBName ASC

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Employees

Retrieve Employee Details.

Table Specific Information

Select

The connector will use the Tally API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• String supports the '=, !=,<, <=, >, >=, LIKE, ORDER BY' comparisons.

For example these queries are processed server side:

SELECT * FROM Employees WHERE EMailID LIKE '%cdata%'

SELECT * FROM Employees WHERE BloodGroup != 'A Positive'

SELECT * FROM Employees WHERE Gender = 'Male' AND FatherName >= 'abc'

SELECT * FROM Employees WHERE BankBranch = 'Delhi' OR BankBranch = 'Bangalore'

SELECT * FROM Employees WHERE ForPayroll > 'No'

SELECT * FROM Employees ORDER BY Guid ASC

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Ledger

Retrieve Ledger Details.

Table Specific Information

Select

The connector will use the Tally API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• String supports the '=, !=,<, <=, >, >=, LIKE, ORDER BY' comparisons.

For example these queries are processed server side:

SELECT * FROM Ledger WHERE TaxType LIKE '%GST%'

SELECT * FROM Ledger WHERE GSTType != 'Applicable'

SELECT * FROM Ledger WHERE GSTTypeofSupply = 'Goods' AND GSTDutyHead = 'Central Tax'

SELECT * FROM Ledger WHERE Parent = 'Sales Accounts' OR Parent = 'Bank Accounts'

SELECT * FROM Ledger WHERE IsChequePrintingEnabled > 'No'

SELECT * FROM Ledger ORDER BY Guid Desc

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Payhead

Retrieve Payhead Details.

Table Specific Information

Select

The connector will use the Tally API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• String supports the '=, !=,<, <=, >, >=, LIKE, ORDER BY' comparisons.

For example these queries are processed server side:

SELECT * FROM PayHead WHERE Parent LIKE '%Maruti%'

SELECT * FROM PayHead WHERE RemoteAltGUID != NULL

SELECT * FROM PayHead WHERE TCSApplicable = 'True' AND OpeningValue > '0.00'

SELECT * FROM PayHead WHERE BaseUnits = 'Nos' OR BaseUnits = 'POS'

SELECT * FROM PayHead ORDER BY BaseUnits Desc

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

PriceList

Retrieve PriceList.

Table Specific Information

Select

The connector will use the Tally API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• String supports the '=, !=,<, <=, >, >=, LIKE, ORDER BY' comparisons.

For example these queries are processed server side:

SELECT * FROM PriceList WHERE Guid LIKE '%4da0-b84f5%'

SELECT * FROM PriceList WHERE RemoteGUID != NULL

SELECT * FROM PriceList WHERE GSTApplicable = 'Applicable' AND CostingMethod = 'Avg. Cost'

SELECT * FROM PriceList WHERE Parent = 'Ashok Leyland' OR Parent = 'Maruti Limited'

SELECT * FROM PriceList WHERE IsBatchWiseOn > 'No'

SELECT * FROM PriceList ORDER BY Guid Asc

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Report_BalanceSheet

Balance Sheet Report of Tally

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Report_BankGroupSummary

Bank Group Summary Report of Tally

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Report_BillsPayable

Bills Payable Report of Tally

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Report_BillsReceivable

Bills Receivable Report of Tally

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Report_GodownSummary

Godown Summary Report of Tally

Table Specific Information

Select

The connector will use the Tally API to process WHERE clause conditions built with the following column and operator. The DspGodownName is required to make a request and the rest of the filter is executed client side within the connector.

• DspGodownName supports the '=' comparisons.
• FromDate supports the '=' comparisons.
• ToDate supports the '=' comparisons.

For example these queries are processed server side:

SELECT * FROM Report_GodownSummary WHERE DspGodownName = 'Karnataka'

SELECT * FROM Report_GodownSummary WHERE DspGodownName = 'Karnataka' AND FromDate = '01/01/2019' AND ToDate = '01/01/2020'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Report_GroupOutstandings

Group Outstandings Report of Tally

Table Specific Information

Select

The connector will use the Tally API to process WHERE clause conditions built with the following column and operator. The GroupName is required to make a request and the rest of the filter is executed client side within the connector.

• GroupName supports the '=' comparisons.
• FromDate supports the '=' comparisons.
• ToDate supports the '=' comparisons.

For example these queries are processed server side: