Formatted notes when using SQL in fragment templates

Fragment Template

A fragment template is an RTF template, which can be called from another RTF template. This increases the re-usability of the templates and the flexibility to generate documents.

When the template fragment is called, the ID of the currently edited model element and the ID of the package in which the model element is located or the ID of a diagram is passed.

With template fragments, you can use script and SQL to retrieve any information, outgoing from the data, from the model, and output it using the fragment template in the generated document.

Output formated notes

If formatting is used in the note, this formatting information is stored as an HTML tag in the EA repository. If you use fragment templates to retrieve model information from the EA repository by using scripts or SQL searches, you get the data as they are stored in the EA repository.

In the case of an SQL search for memo contents, the formatted text is output in the template fragment between the HTML tags.

Interpretation of formatting information

In order to correctly interpret the formatting of the note when generating the documents, the following procedure must be followed:

SELECT ea_guid AS CLASSGUID, Object_Type AS CLASSTYPE, Name, Note as [Note-Formatted] 
FROM t_object
Where Object_ID=#OBJECTID#

It is important to add to the column name -Formatted!

Note as [Note-Formatted]

The EA help describes how to output the column as “Note.Formatted”. This works however only for EA repositories in a DBMS, but not for .EAP files. If we use a minus (hyphen) instead of the dot and brackets instead of the two commas, the formatting also works with .EAP files.

Concrete Example

We have a model element with a formatted note.

We then create an RTF template from which we call a template fragment.

In the template fragment, we add Custom Tags by selecting the Custom check box in the tree on the left. In the document, we add the tags Name and Note.Formatted.

Under the Document Options of the template fragment, we can now enter the SQL query. It is important that the column name of the note is specified as [Note-Formatted]. With the Formatted extension, the EA knows that it should interpret the existing formatting characters. Here, it is important that a minus (-) be used as a separator instead of the point (.). The point can not work with the MSJet DB (.EAP file). It is also important to use square brackets instead of the comma. Otherwise, you will receive an error message from the DB.


If we now generate a document using the RTF template, we get the following result:

If we use a different name in the SQL query, we get an error message.
If we use an incorrect name in the fragment template (for example, {Note-Formatted} instead of {Note.Formatted}), the HTML tags are output in the document.

Posted in Document Generation, Model Search
Tags: , , , ,
2 comments on “Formatted notes when using SQL in fragment templates
  1. Henri-Rene Kuningas says:

    Hi, Is it somehow possible to query and present element’s linked documents in generated documents with custom fragments and sql query, in which table is this file and formatted info kept?

  2. H.Kargl says:

    Hallo Henri-Rene,

    well, it is possible to print linked documents associated to model-elements when a document is generated. This can be done with the checkbox “LinkedDocument” which is avaialbe within the branch: [Package > Element > LinkedDocument] or [Package > Package Element > LinkedDocument]. The first is the linked document of an element, the second is the linked document of a Package.

    Linked documents are stored within the DB table: t_document. However, the content of the linked document is stored as binary and cannot be accessed via SQL.

    So, unfortunately, the answer is currently NO, you cannot access linked documents via SQL from fragment templates.