https://help.tallysolutions.com/docs/td9rel54/integration_capabilities/json_based_export_import.htm

Integration Using JSON

Release 6.4.1 Enhancement - HTTP JSON

Our endeavour is to enable business operations to transition from disconnected to connected environments along with a seamless exchange of information with Tally.ERP 9. This depends on several factors such as modes of exchange, file formats, encoding methods, protocols, structure of data, APIs, and so on.

To accommodate these factors various approaches are included in TDL - Integration capabilities for import and export - Import File, Import Object, XML Tag, Data Source (FileXML, HTTPXML, ODBC), productivity suites, procedural file IO, soap-based pull or push over HTTP.

With Release 6.2, you can address integrations with third-party applications which deliver and consume files in JSON format. For this, TDL - Integration capabilities are now enriched to support JSON file based Import and Export (with UTF-8 encoding). JSON (JavaScript Object Notation), a light-weight text-based data interchange format is widely used today for its simplicity of data structure representation and ease of comprehension.

As you already know, the GST portal supports JSON format for data exchange. With Tally.ERP 9 Release 6.2, our customers can directly generate GSTR-1 and GSTR-2 in JSON format and upload it to the GST portal.

TDL - Integration Capabilities to Support JSON Format

Report-based export in JSON format using the attribute JSON Tag at UI definition level.

o A new attribute JSON Tag is introduced at UI definition levels Form, Part, Line, and Field to specify the tag name in the exported file.

o A new attribute Plain JSON is introduced at the Report level to ensure export of formatted or unformatted JSON.

Productivity suite-based JSON export using the attribute Resource Type at Resource level and JSON Map attribute at Form level.

o The form attribute Map is enhanced to support JSON mapping at Form level.

o The attribute Resource Type is enhanced to support JSON.

Reading a JSON file using File JSON as a data source in collection.

o The collection attribute Data Source is enhanced to support reading from JSON files. You can populate all the data from JSON file to a collection using the data source type File JSON .

o A collection attribute JSON Object Path is added to read a specific data fragment from the given JSON file and gather collections based on it.

o A collection attribute JSON Object is introduced to specify the object (user-defined or schema object) name to map with data from a JSON file.

Integration with HTTP Source in JSON format using HTTP JSON as a Data Source in collection.

o The collection attribute Export Header is introduced to specify the HTTP headers when you send a GET or POST request.

JavaScript Object Notation (JSON)

JSON format supports storing the data types: string, number, object, array, boolean, and null. The object and array are data structures. Other data types store leaf values. To know more about JSON refer http://www.json.org/ .

Comparison of JSON and XML Representations

The following example denotes a collection of 3 employee objects in the JSON and XML formats.

JSON

XML

{

"employeecount": "3",

"employeedept": "RnD",

"employees":

[

{

"firstName": "John",

"lastName": "Doe"

},

{

"firstName": "Anna",

"lastName": "Smith"

},

{

"firstName": "Peter",

"lastName": "Jones"

}

]

}

<Envelope>

<employeecount>3</employeecount>

<employeedept>RnD</employeedept>

<employees>

<employee>

<firstName>John</firstName>

<lastName>Doe</lastName>

</employee>

<employee>

<firstName>Anna</firstName>

<lastName>Smith</lastName>

</employee>

<employee>

<firstName>Peter</firstName>

<lastName>Jones</lastName>

</employee>

</employees>

</Envelope>

In the above example you can observe that:

JSON format does not have end tags

It is short

Cannot duplicate the tags at the same level of hierarchy

Comparison of JSON and Tally.ERP 9 data types

JSON Data Type

Corresponding Tally Data Type

String

Long, Amount, Rate, String, NamePtr, Time, FlagSet, RateX, Date, DateTime, NumSet, Quantity, DateRange, Duration

Boolean

Logical

Object

Tally Object

Array

Collection

Number

Number

Report-based Export in JSON Format

When you want to integrate any third-party solution with Tally.ERP 9, you can send the data in a given structure. Store the data at the field level for the TDL artefact Report to create the structure.

In Release 6.2, you can export the reports in JSON format using the character encodings UTF-8, UTF-16, and ANSI.

Attribute JSON Tag at Form, Part, Line, and Field Level

The attribute JSON Tag specifies the tag name under the UI definitions,  Form, Part, Line, and Field. You can use this attribute to provide the tag name for the exported JSON file and to create the structure of the data in JSON file.  If you are not specifying this attribute at the field level, by default the definition name acts as the tag name. For other levels you need to specify this attribute.

Syntax

[Form/Part/Line/Field: <Definition Name>]

JSON Tag : <String Expression>

Where,

<Definition Name> - the name of the definition.

< String Expression> - any expression which evaluates to a string.

Example

[Form: TSPLForm]

JSON Tag : "Employees"

Report Attribute - Plain JSON

You can set the attribute Plain JSON to Yes , to create a JSON file without any formatting for the content (such as indents and new lines). The default value is No .

Syntax

[Report: <Report Name>]

Plain JSON: <Logical Expression>

Where,

<Report Name> -  the name of the report.

<Logical Expression> - any expression that evaluates to a logical value.

Example

[Report: TSPLReport]

Plain JSON : Yes

The JSON file will be exported without any formatting.

Use the attributes JSON Tag and Plain JSON to export a Tally.ERP 9 report in JSON format,

Specify the attribute Scroll at part level to get the data association with the report.

Use the attributes Full Object and Belongs To at form and line levels to export the entire object and dependant objects in JSON format.

For Release 6, the variable SVExportFormat needs to be set with the combination of the export format and the encoding, as this variable is used in the platform for format and encoding. The sysname UTF8JSON is set to SVExportFormat , to export the report in JSON format with UTF-8 encoding. ANSIJSON for JSON with ANSI encoding and UTF16JSON for JSON with UTF-16 encoding.

Example

To export the vouchers with inventory details in JSON format with UTF-8 encoding.

[Function: TSPLFunction JSON]

000: Set   :SVExportFormat  : $$SysName:UTF8JSON

005: Set   :SVExportLanguage: $$SysName:UTF8JSON

010: Set   :SVPrintFileName : ".\Export\export.json"

015: Export:TSPLVchJSonExp  : Yes:Yes

[Report: TSPLVchJSonExp]

.....

.....

[Part: TSPLVchJSonExp]

Line  : TSPLVchJSonExp

Repeat: TSPLVchJSonExp: TSPLVchCollection

Scroll: Vertical

[Line: TSPLVchJSonExp]

Field    : TSPLVchNumber, TSPLVchDate

Explode  : TSPLVchInvJSonExp

JSON Tag : "Vch"

[Field: TSPLVchNumber]

Use     : NameField

Set As  : $VoucherNumber

JSON Tag: "Vch Num"

[Field: TSPLVchDate]

Use     : Uni Date Field

Set As  : $Date

JSON Tag: "Vch Dt."

[Part: TSPLVchInvJSonExp]

Line     : TSPLVchInvJSonExp

Repeat   : TSPLVchInvJSonExp : All Inventory Entries

Scroll   : Vertical

[Line: TSPLVchInvJSonExp]

Field       : TSPLVchItemName, TSPLVchItemQty

JSON Tag    : "Items"

[Field: TSPLVchItemName]

Use      : NameField

Set As   : $StockItemName

JSON Tag : "Stock Item"

[Field: TSPLVchItemQty]

Use     : Qty Field

Set As  : $BilledQty

JSON Tag: "Qty"

[Collection: TSPLVchCollection]

Type : Voucher

Fetch: VoucherNumber, Date, All Inventory Entries.*

When you export the vouchers using this code snippet, it generates a JSON file as shown below:

{

"Vch":

[

{

"Vch Num": "1",

"Vch Dt.": "1-Apr-2017",

"Items":

[

{

"Stock Item": "Item1",

"Qty": "100 Nos"

},

{

"Stock Item": "Item2",

"Qty": "40 Nos"

},

{

"Stock Item": "Item3",

"Qty": "25 Nos"

}

]

}

},

{

"Vch Num": "1",

"Vch Dt.": "1-Apr-2017",

"Items":

[

{

"Stock Item": "Item1",

"Qty": "500 Nos"

},

{

"Stock Item": "Item2",

"Qty": "300 Nos"

},

{

"Stock Item": "Item3",

"Qty": "450 Nos"

}

]

}

}

]

}

Productivity Suite-based JSON Export

In Release 6.2, the productivity suite framework is enhanced to support JSON format. Now it is possible to create templates using JSON and display the output using Tally.ERP 9 data in the template.

The supported formats for template creation and output are

Microsoft Office (2003 and later): MS Word, MS Excel

Open Office: Open Document Text (.odt), Open Document Spreadsheet (.ods)

XML and JSON (Data Exchange)

To support productivity suite based export in JSON format, we have enhanced the following language capabilities:

Form attribute – Map

Resource attribute – Resource Type

Form Attribute - Map

The attribute Map provides the appropriate value for the token(s) specified in the document template. JSON Map and XML Map are the aliases for the attribute Map.

Syntax

[Form : <Form Name>]

Map: <Token Name>: <Expression>[:<Collection Name>]

Where,

<Token Name> - the name of the token which is specified in the template for evaluating the value.

<Expression> - any valid TDL expression.

<Collection Name> - an optional attribute and it specifies the token for repeating the data.

Resource Attribute – Resource Type

The attribute Resource Type is enhanced to accept JSON as type of the source document.

Keywords in JSON Template

The following keywords are used in JSON to handle special cases like ignore the object if a certain condition is satisfied.

Keyword

Usage

__tlyEmptyIf

Add this keyword as key : value pair within a JSON object when the value of JSON is a string type and the expression evaluates to a logical value.

Use this attribute at any level in the JSON template file. When the value of this attribute is true, the object will not appear in the output.

Example

In this example, the array uses the keyword at the object level. When the value is true, for vouchers other than sales, it will not export the particular voucher object, except for sales vouchers.

{

"Vch":

[

{

"Vch Num": "$VchNum",

"__tlyEmptyIf" : "$Check"

}

]

}

[Form: TSPL Json ColRepSupinSuite]

Resource: TSPL Json ColRepSupinSuite

Map     : VchNum : $VoucherNumber : TSPLVchCollection

Map     : Check : @@Check

[System: Formula]

Check : NOT $$IsSales:$VoucherTypeName

__tlyEmptyIf_<JSON Tag>

This attribute is similar to the attribute __tlyEmptyIf. This appears as a "key:value" pair within an JSON object, when the value of JSON is a string type and the expression evaluates to a logical value.

Use this keyword to skip the value in key: value pair of a JSON object based on the condition specified.

When the value of this attribute is true, the specified tag will not appear in the output.

The JSON Tag used with the keyword should match with the actual JSON Tag. Tags are case and space sensitive. For exampl, if the JSON Tag is Vch Num , then the keyword would be _ _tlyEmptyIf_Vch Num .

Example

Here the JSON Tag Vch Num uses the keyword __tlyEmptyIf .

{

"Vch":

[

{

"Vch Num": "$VchNum",

"__tlyEmptyIf_Vch Num" : "$Check"

}

]

}

[Form: TSPL Json ColRepSupinSuite]

Resource: TSPL Json ColRepSupinSuite

Map : VchNum : $VoucherNumber: TSPLVchCollection

Map : Check : @@Check

[System: Formula]

Check : NOT $$IsEmpty:$VoucherNumber

An array member in the JSON template repeats over a collection.

A collection is identified by a name specified against the first method occurring within the array. The identification can either be at the array level directly in case of an array of simple values, or within an object template in the array.

If no collection name is specified against the first occurring method, then it is assumed that the template value or the object is not repeating. In this case, the array is exported with the methods being evaluated against the current object context.

Example

JSON Template

TDL Code

Result

{

"Vch":

[

{

"Vch Num": "$VchNum", "Vch dt":"$Vchdt"

}

]

}

[Form: TSPLJSONColRepSupinSuite]

Resource: TSPLJSON ColRepSupinSuite

Map : VchNum : $VoucherNumber

Map : Vchdt : $Date: TSPLVchCollection

Evaluates $VoucherNumber in the current context and exports Vch with single object. Ignores the Vchdt token where the collection is mentioned.

{

"Vch":

[

{

"VchNum":"", "Vchdt":""

}

]

}

The default value for the attribute __tlyEmptyIf or __tlyEmptyIf__ <JSON Tag> is false.

If __tlyEmptyIf is mentioned at the root level and it evaluates to true, then the exported file will be empty.

Note: To skip values or object conditionally use the keywords _tlyEmptyIf_<MemberName> or _tlyEmptyIf which appears as a key:value pair. It is not possible to specify these attributes for array members, since values within JSON arrays are not named.

Reading a JSON File

When you integrate a third-party solution Tally.ERP 9 needs to read and process the third-party data.

To read a JSON file, use the data source as File JSON . Use the collection attributes JSON Object Path to read the specific data fragment and JSON Object to map the data to TDL objects. Supports UTF-8 encoding and UTF-16 encoding for reading JSON files.

Data Source - File JSON

Use the collection attribute Data Source to populate data to a collection dynamically from data sources like Directory, File XML, Name Set, Rule Set, and so on. This attribute accepts the type and identity of the data source from where the data is to be retrieved.

In Release 6.2, the collection attribute Data Source allows to specify the data source type as File JSON . You can create the collection directly from the specified JSON file.

Syntax

[Collection: <Collection Name>]

Data Source : File JSON : <File path>

Where,

<Collection Name> - the name of the collection where the data is populated

<File Path> - the JSON file name including its path.

Data Source - HTTP JSON

In Release 6.4.1, the collection attribute Data Source accepts a new data source type as HTTP JSON . Use this attribute to specify a URL as source of data and send request and gather data from the response received in JSON format, from the URL over HTTP, in ASCII, UTF8 or UTF16 encodings.

Syntax

[Collection: <Collection Name>]

Data Source : HTTP JSON : <Target URL> [:<Encoding>]

Where,

<Collection Name> - the name of the collection where the data is populated.

<Target URL> - the URL to which the request is to be sent.

<Encoding> -  can be ASCII, UNICODE, UNICODE8, UTF8, or UTF16. It is applicable for the data source types File XML, HTTP XML, File JSON, HTTP JSON. The default value is UTF16.

Example: POST request with HTTP JSON and Remote Request

[Collection: <Collection Name>]

Data Source     : HTTP JSON : <Target URL> [:<Encoding>]

Remote Request  : <Request Report> [:<Encoding>]

Where,

< Request Report > - the report that exports as JSON to construct the request data content.

< Encoding > - the encoding scheme of the requested data. The values can be ASCII, UNICODE, UTF16, UNICODE8, UTF8. The default value is UTF16.

When you use HTTP JSON as data source type

The behaviour of the data source type HTTP JSON is similar to the HTTP XML. Only difference is the format of exchange. Hence the behaviour of the sub-attributes as well as the behaviour of the attribute Remote Request remains same, if specified.

The data source type HTTP JSON is an enhancement over the File JSON data source, where the source JSON is obtained over HTTP/HTTPS. The attributes related to File JSON, namely JSON Object Path and JSON Object , are applicable here.

Use the attribute Remote Request to send a POST request. For a GET request, only the HTTP data source is used.

Encoding for POST requests: If the attribute Remote Request is specified, the specified encoding will override the Data Source attribute encoding. Hence both the request and response encodings will be the same.

Encoding for GET requests: The encoding for a response is taken from the attribute Data Source encoding. If encoding is not specified for the attribute Data Source , the default encoding is UNICODE or UTF16.

Example

Case 1 : Reading data from a GET Request

The following code snippet sends a GET request to the URL mentioned, and receives a list of branches as a response in JSON format.

[Collection: TSPLGetBranchesColl]

Data Source     : HTTP JSON: " http://localhost/HttpJson/getbranchlist.php "
JSON Object Path: "BranchName:1"

The response in JSON format.

{

"BranchName":

[

"East",

"North East",

"North",

"West",

"South"

]

}

Case 2 : Sending POST Request to a server and reading the response

The following code snippet sends a POST request to the URL mentioned with the branch name, and receives customer details of the branch, in JSON format.

[Collection: TSPLBranchCustomers]

Data Source   : HTTP JSON: " http://localhost/HttpJson/branchcustomers.php "
Remote Request: TSPL Branch Customers: ASCII

[Report: TSPLBranchCustomers]

Form : TSPLBranchCustomers

......

......

[Field: TSPLBranchName]

Set As  : "North"

XML Tag : "Branch"

Request Sent

Response Received

{

"Branch":"North"

}

{

"Branch": "North","Customer":

[

{

"Name": "Sapna Awasthi",

"State": "Delhi"

},

{

"Name": "Ashish Rai",

"State": "Delhi"

}

]

}

Collection Attribute - JSON Object Path

Use the data source type File JSON to populate all the data from a JSON file to a collection. To populate a specific data fragment from the given JSON file to a collection, use the attribute JSON Object Path . This attribute converts the extracted data fragment as a TDL object in the collection. By default, the data fragment starts from the root node.

Syntax

[Collection: <Collection Name>

Data Source: FileJSON : <JSON file name>

JSON Object Path :<StartNode>:<Path to start node>

Where,

<StartNode> -  the name of the starting JSON Node. The parameter is specified as <Node Name> : <Position>

<Path to Start Node> - the path of the starting JSON Node. The structure is  <Child Node> : <Start Pos> : <Child Node> : <Start Pos> …

Note: In JSON, you cannot specify the root node because the root node is unnamed.

Example

[Collection: TSPLJSONFileColl]

Data Source     : FileJSON : "DataFile.Json"

JSON Object Path: "Items:1:Vch:1"

Note: While specifying the tag names in JSON Object Path , the case should be as per the tag specified in JSON file. Make sure the JSON Object Path is written within quotes.

Collection Attribute - JSON Object

Use the attribute JSON Object to map the user defined TDL object name to a JSON file.

Syntax

[Collection: <Collection Name>]

JSON Object: <Object Name>

Where,

<Object Name> - the name of the object.

When you read JSON file you can observe the following features:

1. The leaf values of the JSON file are available as methods in the collections, and the JSON tags are the method names.

2. In case TDL Object is used for mapping the JSON Object, only the method/storage, collection defined within the TDL Object will be gathered from the JSON file.

3. While JSON allows an object to have another object directly under it , this is not allowed in the Tally Object structure. Hence, this translates to a collection within the current object having a single object. The name of this sub collection will be the name of the object member in the source file.

Example: Read JSON file in TDL

Third-party tool sends the following JSON file with voucher details to Tally.ERP 9.

{

"Vch":

[{

"Vch Num": "1",

"Vch Dt.": "1-Apr-2017",

"items":

[{

"Stock Item": "Item1",

"Qty": "100 Nos"

},

{

"Stock Item": "Item2",

"Qty": "40 Nos"

},

{

"Stock Item": "Item3",

"Qty": "25 Nos"

}]

},

{

"Vch Num": "1",

"Vch Dt.": "1-Apr-2017",

"items": [{

"Stock Item": "Item1",

"Qty": "500 Nos"

},

{

"Stock Item": "Item2",

"Qty": "300 Nos"

},

{

"Stock Item": "Item3",

"Qty": "450 Nos"

}]

}]

}

Case 1: Read the entire data in the JSON file.

[Collection: TSPLJSONFileColl]

Data Source: FileJSON : "DataFile.Json"

Case 2: Read the data fragment of items of first voucher object.

[Collection: TSPLJSONFileColl]

Data Source: FileJSON : "DataFile.Json"

JSON Object Path : "Items:1:Vch:1"

Case 3: Read the data fragment and map them to the user defined TDL Object.

[Collection: TSPLJSONFileColl]

Data Source: FileJSON : "DataFile.Json"

JSON Object: VoucherData

[Object: VoucherData]

Collection : Vch : Vouchers

[Object: Vouchers]

Storage : Vch Num : String

Storage : Vch Dt. : Date

Collection : Items : Items Coll

[Object: ItemsColl]

Storage : Stock Item : String

Storage : Qty        : Quantity