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 TallyPrime. 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 TallyERP 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.
- 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.
- 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.
- The form attribute Map is enhanced to support JSON mapping at Form level.
- The attribute Resource Type is enhanced to support JSON.
- Reading a JSON file using File JSON as a data source in collection.
- 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 .
- 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.
- 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.
- 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, andnull. 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 the hierarchy
Comparison of JSON and TallyPrime 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 TallyPrime, 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.
From Tally.ERP 9 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 TallyPrime 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 : “.Exportexport.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 TallyPrime 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 |
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> |
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 TallyPrime 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 attributeData Sourceaccepts 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 attributeRemote Requestis 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:
- The leaf values of the JSON file are available as methods in the collections, and the JSON tags are the method names.
- 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.
- 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 TallyPrime.
{
“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
