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:
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
|