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.

In the SQL procedure, you can use <b> </b> markups to apply bold styling to your tokens:

Copy 
SELECT '<b>' + Name + '</b>' as Solicitant,
       Age as Age
FROM ebs.Contract

Use table tokens in DOCX templates

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}

How to configure a table in the .xlsx template:
HEADER col 1 Header col 2 Header col3 Header col 4
{table2.col1} {table2.col2} Static text {table2.col4}
       
{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.

In the SQL procedure, you can use <b> </b> HTML tags to apply bold styling to your text:

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,
    ,'This is the <b>description</b> of the Contract.' as col4
    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 Header col4
Test1 TestCol2 Static text This is the description of the Contract.
Test2 Test2Col2 Static text This is the description of the Contract.

 

 

 

  
Table1 Info  

Table1 Info 2

  

Table1 Info 3

  
NOTE  
  • There must 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 Bar Code in your DOCX Templates

In order to insert a bar code 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, 'custom/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.

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