version

Component tutorial – RTF Document

In this tutorial you will get to know the RTF document component by building and testing various features.

Introduction

The RTF document component is used to generate RTF documents from a template and can be filled with information from the system. For example, to print invoices, a diploma, or a form.

Generated documents can either be opened/saved by a user or stored in the database.

A menu item with the RTF-component can be run by a scheduled job if there is a need to regularly generate documents. Documents stored in the database are commonly exported or e-mailed at a later point.

How does the component work

To generate a document a template is needed. This is the base for the document and all tags in the template will be replaced by the component with data selected from your stored procedure.

"The component" is the functionality provided by the Softadmin® platform which can be used by a menu item.

"Container" is a section in the template that will be repeated one or more times depending on the data sent to the component.

"Tag" is an element in the template that will be replaced with data from the database.

The following steps are performed when a menu item with the RTF Document component generates a document:

The component asks for a template by calling your stored procedure with the parameter @Template = 1.
- The stored procedure selects an RTF-template (binary) which the component receives.
The component now needs to know what all tags in the template should be replaced with. The component reads the template and makes one or more calls to the stored procedure for the data to replace each tag with.
The component makes one call with @Container ISNULL for all tags that are not within a container.
- The stored procedure selects data for each tag that is not within a container.
The component makes one more call for each container in the template, the @Container-parameter will have the name of the container.
- The stored procedure selects data for each tag that is relevant to the container specified in the @Container-parameter.
The component generates the final document and depending on the file mode a call to the stored procedure with @RtfDocument will be made which contains the generated document.
- The stored procedure selects receives the final document if the file mode is one of "Save", "Save and download" or "Batch" and stores the document. If the file mode is "Download" the generated file will only be sent to the user.

A container can have containers within them which results in another call for each container for each row in the outer container. This is not reflected in the list of steps above.

If this list is hard to understand, return to it after completing the task to check if it's clearer now.

It is possible to send images to the component and the component can generate QR-codes.

Task

Preparation

For the task we need a few tables with some data. Here RTFTutorialCreateSchemaAndTables.sql is a .SQL-script which you can run in a database of your choice. For example, in a new empty system on Sandbox. Click the link to open the file.

After the task is completed another script to remove the schema and its content will be provided.

Here TaskStepOneTemplate.rtf is a base template to use for Step 1 below, it'll be further modified in Step 2 and 3 later. Click the link to open the file.

Save the template and store it in the database, for example in SoftadminDev.Document which can be reached from the Admin-menugroup.

The SoftadminDev.Document table is an ok place to store the template in this tutorial, but in a real system, templates are mostly stored in their own table and are often stored with a type so they can be filtered for different use cases.

Step 1

In step one we will create a menu item that lets the user choose a Person (from the database table Person) and generate a RTF-document with the person's name and how many pets (from the database table Pet) the person owns.

Begin by creating a new menu item with the settings shown in the image below.

Create a stored procedure for the menu item and set a parameter PersonId so the user can choose a person as input.

Code example for the stored procedure

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE OR ALTER PROCEDURE RtfDocumentComponentTutorial.GenerateDocument_Task
   @Template bit = NULL, -- Will be set to "1" when the RTF-component asks for a template
   @PersonId int
AS
BEGIN
   SET NOCOUNT, XACT_ABORT ON;
 
   IF @Template = 1 -- The RTF-component asks for a template
   BEGIN
      -- Select the template so that the RTF-component know which template to use
      SELECT
         D.Document
      FROM
         SoftadminDev.Document D
      WHERE
         D.DocumentId = 4; -- If you use SoftadminDev.Document to store your template, make sure to have the correct DocumentId here!

      RETURN;
   END;

   -- Replace tags from the template with data from the databse
   SELECT
      P.FirstName,
      P.LastName,
      COUNT(PT.PetId) AS [NumberOfPets]
   FROM
      RtfDocumentComponentTutorial.Person P
      LEFT JOIN RtfDocumentComponentTutorial.Pet PT ON 
         PT.PersonId = P.PersonId
   WHERE
      P.PersonId = @PersonId
   GROUP BY
      P.FirstName,
      P.LastName;

END;

Create field information

Create field information for the PersonId-parameter. Set it to dropdown and create a stored procedure that selects the persons from the Person-table. Set Nullchoice to "No".

View the results

Now try the menu item! In the provided tables person Albin has one pet, Signe has zero pets and Lucia has two pets.

After choosing a person on the parameter page and clicking "Ok" a generated document will be downloaded.

The base template provided in this tutorial demonstrates that the heading is still a heading since only the tag itself is replaced, and that all occurrences of the same tag is replaced with the same value.

Step 2

In step two we want to list the chosen persons pets in a table in the document. It's time to update the template and add a container. We do not want any table with pets if the person has zero pets.

Update template

Here is an updated template TaskStepTwoTemplate.rtf, click the link to open the file, save it and store it in the database.

To get a variable number of rows in our table in the generated document we need a container that will be repeated for each row we return from the database.

Image from the updated template:

Here we put the container tag so that the row will be repeated. The table definition for the row will also be repeated since all content between <##PetRow##> and </##PetRow##> will be iterated for each row in the resultset.

But since everything in the template will be interpreted as RTF literals by the RTF Document component it is possible to build a table in a template by hand.

From the RTF Document documentation:

When creating tables with a container tag the start container should be put in column 1 of the table, and the end container tag should be put after the table.

This should result in this order of data in the rtf file:

 \trowd
 \pard\intbl <##Container##>\cell\row
 \pard </##Container##>

To exclude the entire table from the final document we add a section surrounding the container tag and everything inside this section will be excluded if the resultset for the container has zero rows. The image below illustrates the section surrounding the container:

The surrounding section needs to have the same name as the container.

Update stored procedure

Now the stored procedure needs to be updated. See the code example below:

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE OR ALTER PROCEDURE RtfDocumentComponentTutorial.GenerateDocument_Task
   @Template bit = NULL, -- Will be set to "1" when the RTF-component asks for a template
   @Container sysname = NULL, -- New parameter because we need to know when to select data for a container or non-container tags.
   @PersonId int
AS
BEGIN
   SET NOCOUNT, XACT_ABORT ON;
   
   IF @Template = 1 -- The RTF-component asks for a template
   BEGIN
      -- Select the template so that the RTF-component know which template to use
      SELECT
         D.Document
      FROM
         SoftadminDev.Document D
      WHERE
         D.DocumentId = 5; -- If you use SoftadminDev.Document to store your template, make sure to have the correct DocumentId here! Updated to 5 in the example code for step two.
   
      RETURN;
   END;
   
   -- Replace tags from the template with data from the databse, for those tags that are not in a container
   IF @Container IS NULL
   BEGIN
      SELECT
         P.FirstName,
         P.LastName,
         COUNT(PT.PetId) AS [NumberOfPets],
         IIF(COUNT(PT.PetId) = 0, 'No pet info to display.', NULL) AS [DoDisplayNoPetInfo]
      FROM
         RtfDocumentComponentTutorial.Person P
         LEFT JOIN RtfDocumentComponentTutorial.Pet PT ON 
            PT.PersonId = P.PersonId
      WHERE
         P.PersonId = @PersonId
      GROUP BY
         P.FirstName,
         P.LastName;
   END;
   ELSE IF @Container = 'PetRow' -- Replace tags from the template with data from the database for the container "PetRow", each row in the resultset will be repeated in the document generation.
   BEGIN
      SELECT
         P.PetName,
         P.Age AS [PetAge], -- Make sure the aliases is corresponding with the tag in the template
         PT.PetTypeName AS [PetType]
      FROM
         RtfDocumentComponentTutorial.Pet P
         JOIN RtfDocumentComponentTutorial.PetType PT ON 
            PT.PetTypeId = P.PetTypeId
      WHERE
         P.PersonId = @PersonId
      ORDER BY
         P.PetName;
   END;

Make sure to select the new template in the updated stored procedure.

We now have two different calls from the component to the stored procedure for the data. One where @Container IS NULL and one where @Container ='PetRow', so that we can select the data that is appropriate in both cases from the stored procedure.

View the results

Try the menu item for the different persons with the updated template and stored procedure.

You can now see that the number of rows in the table can vary with the number of rows in the resultset from the stored procedure and that we can exclude the table-section altogether with an empty resultset for the PetRow-container.

Step 3

In step three we want to be able to generate a document for all the persons at the same time. Every person's information should start on a new page so that no page contains information for two different persons.

Delete field information

We do not need to choose a person now. So, remove the parameter from the menu item by editing the menu item and remove it under the SQL-section:

Update template

Here is an updated template TaskStepThreeTemplate.rtf, click the link to open the file, save it and store it in the database.

To get a variable number of pages we need another container.

We add a container <##PersonContainer##> at the start of the document, then add it at the bottom of the template and insert a page break just before the </##PersonContainer##>. This will move the </##PersonContainer##> to the next page.

We make sure to have nothing else than the closing </##PersonContainer##>-tag on the last page so that we do not get a blank page at the end of the generated document.

With "Show all (Ctrl + ()" enabled in word you can see the page break. It's a good option to have enabled to see exactly what your template contains. Example image of the updated template:

And on the second page the </##PersonContainer##> tag is but nothing else.

Update stored procedure

Now we need to support a container within a container. See the example below:

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE OR ALTER PROCEDURE RtfDocumentComponentTutorial.GenerateDocument_Task
   @Template bit = NULL, -- Will be set to "1" when the RTF-component asks for a template
   @Container sysname = NULL, -- The component tells the stored procedure for which container data is needed
   @Id int = NULL -- Since we have a container within a container the component will tell us for which Id (row in the outer container) it needs data for the inner container.   
AS
BEGIN
   SET NOCOUNT, XACT_ABORT ON;
   
   IF @Template = 1 -- The RTF-component asks for a template
   BEGIN
      -- Select the template so that the RTF-component know which template to use
      SELECT
         D.Document
      FROM
         SoftadminDev.Document D
      WHERE
         D.DocumentId = 6; -- If you use SoftadminDev.Document to store your template, make sure to have the correct DocumentId here! Updated to 6 in the example code for step three.
   
      RETURN;
   END;
   
   -- Replace tags from the template with data from the databse, first for the PersonContainer (the outer container)
   IF @Container = 'PersonContainer'
   BEGIN
      SELECT
         P.PersonId AS [Id], -- Since we now have a container within a container, this Id will be sent to the stored procedure for each PetRow-container call made for each row selected in this resultset
         P.FirstName,
         P.LastName,
         COUNT(PT.PetId) AS [NumberOfPets],
         IIF(COUNT(PT.PetId) = 0, 'No pet info to display.', NULL) AS [DoDisplayNoPetInfo]
      FROM
         RtfDocumentComponentTutorial.Person P
         LEFT JOIN RtfDocumentComponentTutorial.Pet PT ON
            PT.PersonId = P.PersonId
      GROUP BY
         P.PersonId,
         P.FirstName,
         P.LastName
      ORDER BY
         P.FirstName,
         P.LastName;
   END;
   ELSE IF @Container = 'PetRow' -- Replace tags from the template with data from the database for the container "PetRow" which now will be needed for each row in the PersonContainer.
   BEGIN
      SELECT
         P.PetName,
         P.Age AS [PetAge], -- Make sure the aliases is corresponding with the tag in the template
         PT.PetTypeName AS [PetType]
      FROM
         RtfDocumentComponentTutorial.Pet P
         JOIN RtfDocumentComponentTutorial.PetType PT ON 
            PT.PetTypeId = P.PetTypeId
      WHERE
         P.PersonId = @Id -- Changed to @Id now.
      ORDER BY
         P.PetName;
   END;
   
END;

Now there is two container calls. An outer container (PersonContainer) and an inner container (PetRow) which depends on the outer container.

We remove the where-clause in the PersonContainer to select all persons and we add a new column P.PersonId AS [Id] which will be sent from the component when the PetRow-call is made.

In total the component now makes five calls to the stored procedure given that we have three persons in the database.

First call to retrieve the template
Second call to get the data for PersonContainer
Third to fifth call to get the data for each person's PetRow-container.

View the results

Now the document is instantly downloaded when the menu item is clicked since the parameter was removed.

Try it and make sure that every person is included in the document and that each persons information starts on a new page.

Finish

Clean up

Here RTFTutorialDropSPsTablesAndSchema.sql is a SQL-script to drop the tables and the schema which was created before. This script also drops the two stored procedures if they exist. If you made any other objects, you also need to remove them to be able to run the script fully since the schema wont be able to be dropped unless no objects reference it.

Feedback

Thank you for completing the tutorial for the RTF Document component. Please send us feedback by e-mail to KompetensagarePG@multisoft.se so that we can improve this tutorial!

Component tutorial – RTF Document

Introduction

How does the component work

Task

Preparation

Step 1

Create menu item

Code example for the stored procedure

Create field information

View the results

Step 2

Update template

Update stored procedure

View the results

Step 3

Delete field information

Update template

Update stored procedure

View the results

Finish

Clean up

Feedback