Creating Document Templates

Before creating a digital document in FintechOS Studio, you should create a template using MS Word or MS Excel. Digital document templates are useful for journeys that offer personalized documents to customers, such as GDPR documents with the name of the customer on them.

IMPORTANT! To generate digital documents properly, after creating the document templates, make sure that:

comments are removed;
changes are accepted and tracking changes are stopped;
the signature details fit within the page, signature tokens should be centered.

When creating the template, use tokens for fields and tables. The template automatically fills in with data from the database, as it is going to be attached to a digital document.

NOTE
The digital document's data source is an SQL procedure or Fetch Collection.
The SQL procedure has to be defined in advance, while the Fetch Collection is defined after attaching the template to the digital document.

Use token fields

A token field is a text field that includes a block of text (token) that can be easily selected and manipulated. To use token fields within your report template, include them within curly brackets {} without leading or trailing spaces.

NOTE
The token should have the following format: table.Name.attributeAlias or tableName.attributeName.

Use table tokens

In case of a table token, the SQL procedure call must contain two select queries separated by comma:

The first SELECT should follow this pattern: SELECT 'tokenName' as 'table' to specify that the 'tokenName' token represents a table.
The second SELECT will return effective data for the table. The document table columns name must be identical to the attributes name returned by query.

Example:

Copy

    select 'views' as 'table',
                select entityviewid as 'entityviewid', Data as 'Data'
            from EbsTestEntityView

Format tables in DOCX and XLSX templates

You can format tables in templates in many ways, by: table size, border size, cell padding, add/delete header and border color. You can also include datasets within the table by using table tokens handled through SQL procedures or Fetch Collections.

To automatically fill-in table rows with data from the DB, add a new row to the table and provide the table token in the following format: {tableName.columnAlias}

Example

How to configure a table in the .xlsx template:

HEADER col 1	Header col 2	Header col3
{table2.col1}	{table2.col2}	Static text

{table1.column1}	{table1.column1}	{table1.column1}

This row will be replaced by the data returned as per the provided SQL procedure. You will provide both tableName and columnAlias in the SQL procedure.

SQL procedure

Copy

CREATE PROCEDURE [dbo].[uspTest]
                        @Id uniqueidentifier
                        AS
                        BEGIN
                        --the first data set should return only one row
                        select 1 as 'abc'

                        select 'table1' as 'table' --tableName
                        -- data set for table1
                        select name as column1
                        ,createdOn as column2
                        from ebs.TestTable1

                        select 'table2' as 'table' --tableName
                        -- data set for table2
                        select ContractName as col1
                        ,CreatedDate as col2
                        from Ebs.Contract
                    END

Table from the generated document based on the SQL procedure provided in the example above:

HEADER col 1	Header col 2	Header col3
Test1	TestCol2	Static text
Test2	Test2Col2	Static text

Table1 Info
Table1 Info 2
Table1 Info 3

NOTE

There shall be only one tableName per table row, otherwise, the rows will be duplicated based on the data from the first table provided in the SQL procedure.
You can add as many tableName tags in a table as long as they are on different rows.
If the procedure returns no dataset, the row is removed from the table within the template which contains the specific tag.

Table from the document generated with dataset table2 with no rows

HEADER col 1	Header col 2	Header col3

Table1 Info
Table1 Info 2
Table1 Info 3

Insert a barcode in your DOCX template

In order to insert a barcode in your DOCX template, go to FintechOS Studio> Admin > System Parameters and change the value of the sys-documentreport-should-read-barcode-from-userfiles-storage system parameter to:

0 - in order to declare in the SQL procedure the absolute path to the barcode image. E.g.:

Copy

select 1 as Txt1,
                            'C:\Users\john.doe\source\repos\master-new\ebscore\EBS.Core.Web.MVC\UploadEBS\document-report-azure.png' as 'barCode128'

1 - in order to declare in the SQL procedure the relative path to the barcode image. E.g.:

Copy

 select 1 as Txt1,
                            'document-report-azure.png' as 'barCode128'

Add the {barCode128} token in your template along with the rest of the token fields and upload the template in your Digital Document.

NOTE
Each template must contain only one {barCode128} token.

Show or hide objects in Word document templates

You can show or hide objects, such as paragraphs or tables, in Word document templates only.

NOTE
The option to show or hide objects is available only in Word document templates, and not Excel.

To do so, use the following token: {show=var1}, where var1 is provided in the SQL procedure in the first dataset. In the SQL procedure, the possible values for var1 are 1 ( show object) and 0 (hide object); whereas, the default value is 1. If the var1 value is not provided within the first dataset of the procedure, the object will be displayed. To hide an object, in the SQL procedure set the value of var1 to 0.

Example

Word document template with the show token:

{Show=var1}

Contract No : {table2.col1}

OverdraftLimit Amount : {DocumentNo}

Currency : {table1.col1}

{var1}

SQL procedure with var1 set to value 1

Copy

alter PROCEDURE [dbo].[testDocReport]
                        @Id uniqueidentifier
                        AS
                        BEGIN
                        select 'Doc00001' as 'DocumentNo'
                        , 'NEW_Doc00001' as 'FtosReportFileName'
                        ,1 as 'var1'

                        select 'table1' as 'table'
                        -- data set for table1 
                        select 1 as 'col1'
                        ,2 as col2
                        ,3 as col1
                        from ebs.Test

                        select 'table2' as 'table'
                        -- data set for table2
                        select 2 as 'col1'
                        ,1 as col2
                        from ebs.Test

                    END

The document generated based on the template and SQL procedure provided in the previous examples looks like this:

Contract No : 396986

OverdraftLimit Amount : 123

Currency : EUR

Word document template with the hide token:

{Show=var1}

Contract No : {table2.col1}

OverdraftLimit Amount : {DocumentNo}

Currency : {table1.col1}

{var1}

SQL procedure with var1 set to value 0

Copy

BEGIN
                        select 'Doc00001' as 'DocumentNo'
                        , 'NEW_Doc00001' as 'FtosReportFileName'
                        ,0 as 'var1'

                        select 'table1' as 'table'
                        -- data set for table1 
                        select 1 as 'col1'
                        ,2 as col2
                        ,3 as col1
                        from ebs.Test

                        select 'table2' as 'table'
                        -- data set for table2
                        select 2 as 'col1'
                        ,1 as col2
                        from ebs.Test

                    END

The document generated based on the template and SQL procedure provided in the previous examples looks like this:

OverdraftLimit Amount : 123

Currency : EUR