HomeDeveloper ReferenceTDL ReferenceCustomisation Using Productivity Suites

 

Explore Categories

 

Customisation Using Productivity Suites

Customisation using productivity suites is a facility to create reports in the required format with minimal time consumption and effort, using the applications available in productivity suites like Microsoft Office, Open Office, and so on. Using this facility, TallyPrime accepts a predefined document template designed using any of the productivity suites, and generates output in the desired format. For example, if the input is defined as a Word XML Document, the output is displayed in MS Word.

Watch the below video to know the enhancements that are provided for Release 5.0.

Based on the business requirements, there may be a need for change in the format of default documents like statutory forms provided in TallyPrime. To reflect these changes in the default documents, users can now edit the document template using MS Word or MS Excel, and create the required layout.

The applications supported are:

  • Microsoft Office (2003 and above): MS Word, MS Excel
  • Open Office: Open Document Text (.odt), Open Document Spreadsheet (.ods)
  • XML (Data Exchange)

Prerequisites for the User

The user is required to have any of the following productivity suites:

  • MS Office 2003 or higher version
  • Open Office
  • Office Viewer

Note: TallyPrime uses any compatible format based on the productivity suite available in the user system. To know more about designing or creating the document template, refer the section Guidelines for Designing Document Template .

TDL Enhancements for the Capability

In TallyPrime, all reports follow the design hierarchy of Form > Part > Line > Field . The same hierarchy is used for creating and printing the defined layout.

The user can design the required document template for print as a MS Word or MS Excel document, and save it in XML format. The designed document template contains the layout, and the corresponding data to be fetched. Once the document is designed, it is to be associated to the respective TDL Form definition. Once the document is associated to the Form definition, TallyPrime accepts this document. In this case, the application does not proceed further into the hierarchy.

To support the capability Customisation using Productivity Suites, two Form level attributes and four platform functions are introduced. Also, the attribute value of Resource Type is extended to accept other file formats. The language enhancements are listed below:

  • Form Attribute – Resource
  • Form Attribute – XML Map
  • TDL Function – $$TplLine
  • TDL Function – $$WordInfo
  • TDL Function – $$ExcelInfo
  • TDL Function – $$IsFileTypeSupported
  • Resource Types – WordXML, ExcelXML, XML, ODT, ODS

Form Attribute – Resource

The attribute Resource at Form definition is used to specify the name of the Resource definition, which provides the details of the document template. This document template is used for exporting/printing the Form.

Syntax

[Form: <Form Name>]

Resource : <Resource Name>

Where,

< Resource Name > is the name of the resource definition.

Form Attribute – XML Map

XML Map is a Form level list type attribute, used to provide correct value for the token(s) specified in the document template. Token is an alpha-numeric value without any spaces, prefixed with $. No special characters are allowed in the token name. It is an expression specified in the document template to map the values from TallyPrime.

For example, $Name, $CmpName, $FrDate, $Val1

When the document template is used for exporting/printing:

  • The defined tokens in the document template are substituted with the respective values by evaluating the expression in XML Map attribute, against the defined token.
  • If the token is not defined in the document template, it evaluates as a method in current object context. A token refers to a method of current object context. In this case, defining XML Map is not necessary.
  • XML Map also allows specification of repeatable data, when it is referenced in TallyPrime. It creates a required number of rows/entries for each object in the collection.

Syntax:

[Form: <Form Name>]

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

Where,

< Map Name > is the name of the token specified in the document template for evaluating the value.

< Expression > is a valid TDL expression such as method, variable, system formula.

< Collection Name > is specified where the token is defined for repeating the data. It is an optional parameter.

Example

[Resource: InvoiceWord]

Source : “D:WorkInvoiceWord.xml”

;;It is the word document saved as .xml(WORDXML)

Resource Type : Word XML

[Form: InvFrm]

Resource : InvoiceWord

XML Map : BasicSDN : @@DelNoteNo

XML Map : SVCompany : ##SVCurrentCompany

XML Map : Rate : $Rate : Inventory Entries

[System: Formula]

DelNoteNo : $BasicShipDeliveryNote

In the example, InvoiceWord is the name of the resource. BasicSDN , SVCompany and Rate are the tokens which are defined in the document template, and values are taken from the respective second parameters, @@ DelNoteNo, ## SVCurrentCompany and $Rate:Inventory Entries.

You can use a relative path in the attribute Source as given below:

[Resource: InvoiceWord]

Source : “ResourceInvoiceWord.xml”

;;It is the word document saved as .xml(WORDXML). The specification of resource always accepts the relative path as the TDL Folder and not the TallyPrime application folder. If you mention a relative path, it is concatenated along with the absolute path of the folder where TDL File exists.

Resource Type : Word XML

[Form: InvFrm]

Resource: InvoiceWord

XML Map : BasicSDN : @@DelNoteNo

XML Map : SVCompany : ##SVCurrentCompany

XML Map : Rate : $Rate : Inventory Entries

[System: Formula]

DelNoteNo : $BasicShipDeliveryNote

Use the following code to prevent printing values of Not Applicable or End of List .

[System: Formula]

DelNoteNo : If $$IsEmpty:$BasicShipDeliveryNoteor $$IsSysName:$BasicShipDeliveryNote then ” ” + else $BasicShipDeliveryNote

Note: Microsoft Word has a limitation to print the special character used in TallyPrime for the SysNames, Not Applicable or End of List .

For every token, TallyPrime looks for an XML Map definition

  • If TallyPrime finds the specified token in the XML Map definition, it evaluates and replaces the specified expression.
  • If TallyPrime does not find the specified token in the XML Map definition, it treats the token as a method in current object context, and evaluates the value.
  • If the value corresponding to a token is not found in the XML Map or in any of the object storages/methods, the token is printed as it is in the document.
  • For example, $DutyValue is the token, and there is neither XML Map with this name nor any storage/method in that object context, then the system prints $DutyValue as it is, indicating that the value corresponding to the token is not available.
  • When the collection name is specified, the specified paragraph or row in the docu­ment template is repeated for every collection object. When the objects are not found in the collection, paragraph or row is not displayed.

TDL Function – $$TplLine

Function $$TplLine provides line number or object index number in the current collection, where the paragraph or rows of the document template are repeated.

Syntax

$$TplLine

Example

[Form: InvFrm]

Resource : InvoiceWord

XML Map : Line : $$TplLine

TDL Function – $$WordInfo

This function helps to find whether MS Word is installed in the user system or not. It accepts any one parameter, i.e., Version or IsDocxSupported.

  • Version – to get the version number of MS Word.
  • IsDocxSupported – to check whether the .docx format is supported or not.

Syntax

$$WordInfo:<Info Type>

Where,

<Info Type> has two values, i.e., Version and IsDocxSupported

Example

Set as : $$WordInfo:Version

Set as : $$WordInfo:IsDocxSupported

TDL Function – $$ExcelInfo

This function helps to find whether MS Excel is installed in the system or not. It accepts any one parameter, i.e., Version or IsXlsxSupported.

  • Version – to get the version number of MS Excel.
  • IsXlsxSupported – to check whether the .xlsx format is supported or not.

Syntax

$$ExcelInfo:<Info Type>

Where,

<Info Type> has two values, i.e., Version and IsXlsxSupported

Example

Set as : $$ExcelInfo:Version

Set as : $$ExcelInfo:IsXlsxSupported

The behavior of different versions of MS Office in the use of $$ExcelInfo and $$WordInfo are as shown below:

MS Office

Version

Docx Supported

Xslx Supported

2003

11

No

No

2007

 

 

 

2010

14

Yes

Yes

2013

15

Yes

Yes

TDL Function – $$IsFileTypeSupported

It returns the logical value, whether the provided format is supported in the system or not.

Syntax

$$IsFileTypeSupported:<document extension>

Where,

<document extension> is the type of file extension.

Example

$$IsFileTypeSupported:”.odt”

$$IsFileTypeSupported:”.ods”

The behavior of different versions of MS Office are as shown below:

MS Office

.odt Supported

.ods Supported

2003

No

No

2007

No

No

2010

No

No

2013

Yes

Yes

Resource Types – WordXML, ExcelXML, XML, ODT, ODS

In TDL, Resource definition is used to access and use the resources (images, icons, cursors, etc.) from a local disk, HTTP/FTP, or from a DLL/EXE. The formats supported are BMP/JPEG/ICON/CUR. These resources are allowed in Part definition, using the attribute Image for displaying, printing and exporting.

To support the facility Customisation using Productivity Suites, the capability of Resource definition is enhanced to use the resources like Word Xml, Excel Xml, Xml, odt, ods as resource types.

Syntax

[Resource: <Resource Name>]

Source              : <Path to File>

Resource Type : <Supported Resource>

Where,

<Path to File> is the absolute or relative path to the document or image. The relative path should not start with ./.

<Supported Resource> is the resource type which is supported. The supported resources are ODS/ODT/WORD XML/Excel XML/XML/Bitmap/Icon/Jpeg/Cursor.

Example

[Resource: InvoiceWord]

Source : “D:WorkInvoiceWord.xml” ;;this is the document saved as xml

Resource Type : Word XML

In the example, InvoiceWord.xml is the Word XML document template, and it prints in Word format from TallyPrime. To read from a relative path, the file should be located in the TDL path.

[Resource: InvoiceWord]

Source : “InvoiceWord.xml”

Resource Type : Word XML

Guidelines for Designing the Document Template

To design a document template consider the following:

  • Design the document template for a new format.
  • Use the document template available in Word/ODT/Excel/ODS. These document tem­plates may require modifications to get proper output.

The file formats supported with this facility to print/design a document in printable format are:

Format

Format Type

Usage

Word Formats

Word 2003 XML Document (.xml)

For complex layouts, multiple tables, Forms, and so on.

Open Document Text(.odt)

Excel/Spreadsheet Formats

Excel Spreadsheet 2003 (.xml)

For listing summary, annexure, and so on.

Open Document Source(.ods)

Note:

♦ Generate any suitable format which is suitable for customers/clients in Microsoft or OpenOffice.
♦ Designed document template is for generic purpose, it is recommended that generate the document templates in WordXML, & .ODT and ExcelXML & .ODS format.

Creating Multiple Formats

To create document templates in multiple formats

  1. Design the document template either in MS Office or Open Office.
  2. Copy the document template, and Save As in the required format.

Example: 1

    •  Design the document template in Word.
    •  Save the document template as Word 2003 XML document(.xml) .
    •  Open the designed document template.
    •  Copy the content from the template.
    •  Open an Open Office document, and paste the content.
    •  Save as .ODT.
    •  Open with any of the Open Office applications.

Example: 2

    • Design the document template in Word.
    • Save the document template as Word 2003 XML Document (.xml) .
    • Open the designed document template.
    • Save as .ODT in Word.
    • Open the document template using any of the Open Office applications.
    • If the format is incorrect, correct the format, and save as .ODT .

 

  1. Print the designed document template and check the font, alignment, width of values, data wrapping, image, value with page break, and so on, for any formatting errors.

 

Using Multilingual Capability in the Document Template

In TallyPrime, to print/e-mail/upload the documents in different languages, the document templates are flexible and can be generated in the desired language. In multilingual document templates, the tokens are always in English.

Scenarios:

Scenario 1: Titles in English, and data in any other language

  1. Create the document template with all labels/titles in English.
  2. Token values are printed in the language used to enter data in TallyPrime. The title appears in English, and data, based on language settings in TallyPrime.

Scenario 2: Titles and data in a different language

  1. Create the document template with the labels/titles in any one language by setting the Win­dows keyboard language.
  2. The data is printed in the language used in TallyPrime. The title appears in one language, while data will appear in the language set in TallyPrime.
Note: When you design document template using the multilingual capability use the font which supports Unicode. For example., Arial Unicode MS.

General Guidelines

  1. Use platform defined resource types : WordXML, ExcelXML, XML, .ODT and .ODS are con­sidered as valid resource types for the capability Customisation using Productivity Suites. Any other resource types are treated as invalid, and does not print.
  2. Horizontally adjacent tables are not applicable for Excel/.ODS : Two tables adjacent to each other horizontally (one table repeating on one set of data, and other table repeating another set of data) are not recognised as separate, as Excel considers the complete row as a single table.
  3. Design table within table for Word/.ODT : To have multi-line address in a single cell, add a table inside that cell, with the required number of columns. When data is repeated for that table, the number of rows are added accordingly.
  4. Print a single form in multiple pages :
    • Check the headers for each page.
    • Use the option Fit to one page , if required.
    • Select the appropriate orientation, portrait or landscape.
    • Adjust margins to fit more columns, and so on.
  5. Horizontal repeat over a string to print each letter/digit in separate boxes is supported: Horizontal repeat over a string is supported character-wise also. For example, if TIN number has 10 digits, and each digit is printed in separate boxes.

2

6

5

4

7

9

0

5

2

8

To achieve this, while designing the template in Word/ODT, create a table with number of cells equivalent to the number of characters in the data, and specify the token in the first cell, as shown below:

$TIN

                                           

By adding the below code snippet in the TDL, the output appears as shown below:

[Form: sample]

XML Map : TIN : $TinNumber ;;$TinNumber = 4512468921

Repeat : TIN : String

4

5

1

2

4

6

8

9

2

1

                         

If the document template does not support the character length of the data, data loss will occur.

$TIN

       

In this case, add the code snippet in TDL shown below:

[Form: sample]

XML Map : TIN : $TinNumber ;$TinNumber = 4512468771

Repeat : TIN : String

The output appears as shown below:

4

5

1

2

4

In this example, the document template contains only 5 cells, and the data has 10 characters. Hence, in the output, only 5 characters are displayed.

Print/Export/E-mail/Upload for Multiple Objects/Forms

When print/export/e-mail/upload is done for multiple objects, specified as part of Collection attribute at Report level, that many number of files are generated.

Consider the below points:

Print with preview:
  • The number of files generated, opened and printed, equals the number of objects.
  • The files are deleted once they are viewed and closed.
  • The name of the file is randomly generated.
  • The file extension is decided by the format selected in the configuration screen.
Print without preview:
  • The number of files generated and printed equals the number of objects.
  • The files are deleted once they are printed.
  • The name of the file is randomly generated.
  • The file extension is decided by the format selected in the configuration screen.
Export:
  • The number of files generated equals the number of objects. Only the first file is opened.
  • The name of file is user specified
  • The extension is decided by the format selected in the configuration screen

Designing XML Formats

XML formats are used to interchange the data as per the personalized requirements. In TallyPrime these formats are used to upload files to government websites, for filing returns. These XML formats are either provided by the government.

Once the XML format is ready,

  1. Define tokens in between the XML tags for values
    • Use the XML attribute _ _tlyEmptyIf = “$<TokenName>” to eliminate a particular tag from the XML.
    • Edit the XML using editors like Notepad, Notepad++, SlickEdit, and so on.
  2. Save the edited document template as .xml file.

The XML tag can be repeated for a TDL collection of data. The repeated XML tags can further have tags which are repeated for other TDL collection of data.

Some Known Issues

  • Merging rows are not supported in Excel.
  • When exporting data, using XML(data interchange) Sysnames and data with special char­acters, for example, □Primary, are not considered.
  • ExcelXML format does not consider images for print/export/email/upload.

Export

While exporting the documents specify the proper format in the export configuration screen, and proper file name extension to achieve the expected output. Provide the format and file extension based on requirements.

Export Format

Format

File Extension

WordXML

Word 2003 XML Document (.xml)

.xml

ODT

Open Document Text

.odt

ExcelXML

Excel

.xls

ODS

Open Document Spreadsheet

.ods

Protecting the Document Templates from Edit

Word XML:

  • Open the document template in MS Word.
  • Click Restrict Editing from Review .
  • Select the option Allow only this type of editing in the document .
  • Click Yes, Start Enforcing Protection , the screen Start Enforcing Protection appears.
  • Enter the password details, if required.
  • Click OK to accept the changes.
  • Save the document template.

This will save the document as read only, and it can be edited only if users clear this option.

ODT:

  • Open the document template in any Open Office application (Libre Writer).
  • Save As the document template, and select the option Save Password .
  • Enter the password details.
  • Click Cancel and proceed.

It saves the document template as read only, and it can be edited only if users clear the option Save Password .

Excel XML:

  • Open the document template in MS Excel.
  • Click Protect Sheet button from Review menu.
  • Select the options Select locked cells, Select Unlocked Cells , and click OK.
  • Save the document template.

It saves the document template as read only, and it can be edited only if users clear the option.

ODS:

  • Open the document template in any Open Office application (Libre Writer).
  • Click Save As to save the document template, and select the option Save Password .
  • Enter password details.
  • Click Cancel and proceed.

It saves the document as read-only, and it can be edited only if users clear the option Save Password .

Release 5.2

Column-wise repeat of data over a collection

The Form level attribute Repeat is used to repeat data of a collection column-wise in the reports created using productivity suites, using the function TplColumnObject.

Syntax

Repeat : <Token Name> : <Collection Name>

Where,

<Collection Name> is the name of the collection or a sub-collection.

<Token Name> is the name of the token specified in the document template for evaluating the value using the attribute XML Map.

Function – TplColumnObject

Function TplColumnObject evaluates the given parameter in the context of the column object. In the absence of this function, the expression is evaluated in the current context of the Report.

Syntax

$$TPLColumnObject:<Expression>

Where,

<Expression> can be any expression which evaluates to any data type like, string, number, amount, and so on.

Example

To print ledger names as columns:

  • Design the document template as shown below:

    $LedName

$LedName

Token is specified in one cell, based on the number of objects in the collection, the columns are added.

  • Add the below code snippet in the required TDL.

[Form : Sample Report]

XML Map : LedName : @@TPLColObjName

Repeat : LedName : LedgerColl

[System : Formula]

TPL ColObjName : $$TPLColumnObject:$Name

[Collection : LedgerColl]

Type : Ledger

The output appears as shown below:

Ledger1

Ledger2

Ledger3

In this example, the collection includes three ledgers. All three ledgers are added as columns.

 

Release 5.4.8

Customisation using Productivity Suite is enhanced to take MS Excel as an input file and produce the specified Excel file as an output. The data is evaluated in the tokenized input file and then, only the values are written to the output file specified in the configuration screen.

If the output file is present, data will be updated to the appropriate cells of respective sheets provided in the input Excel file. If the output file is not present, then the same input Excel file will be created with the specified output file name.

To support this capability, MS Excel is introduced as a new Resource Type in Resource definition.

 

Post a Comment