Question:
How can I reference additional data in my bookmark template (i.e, Attachment, Transmittal, Item Cover, etc), created in Microsoft Word?
Answer:
Almost any data can be accessed in a Word template by using dynamic aliases. Defining the dynamic alias is an advanced topic and requires an understanding of TSQL and data queries. The process begins by using a bookmark in your template with a custom alias, for example [RR_qDrwLog_Title]. Spitfire does not define the qDrwLog alias.
When the Spitfire template engine encounters a bookmark alias that is undefined and begins with a q, it looks in the QueryConfig rule group for a definition of the alias. In our example, it would look for qDrwLog:Alias.
The result value for this rule is a SQL query. The query can use a stored procedure or SELECT that returns a single result set. The query can be passed parameters. The predefined parameters are:
- @pDocMasterKey – the key to the document for which the template is being generated
- @pDocTypeKey – the document type key for which the template is being generated
- @pProject – the Project ID (without masking characters) for which the template is being generated
- @pUID – the identity key for the current user for whom the template is being generated
If your query requires any data from the document, it can access those data elements using a JOIN based upon @pDocMasterKey.
Each of the columns or fields in the result of your query is accessible to the template. In our example, Title would be one of these fields. You might also return ReferenceDate, Subtype, etc.
If your result set returns multiple rows, you would likely include the RR prefix on your template bookmarks.
When you define a qAlias, you can include additional optional rules to modify the alias execution. These additional rules are seldom used.
- qalias:QPList – Where qalias is the qAlias name (qDrwLog:QPList using the example above). The result value specifies a semicolon-seperated list of additional query parameters. It is a much better idea to use the parameters that are automatically supplied by default and simply use TSQL logic to gather any additional criteria.
- qalias:QTimeout – Where qalias is the qAlias name (qDrwLog:QTimeout using the example above). The result value indicates the number of seconds the query should be allowed to run before SQL cancels the query. Normally, the system wide default is adequate for well designed queries.
- qAlias:WithRole – Where qalias is the qAlias name (qDrwLog:QTimeout using the example above). The result value indicates the name of a specific role in which the user must have membership. Default is off, meaning all can use the query.
Additional Comments:
Creating the query ‘underneath‘ the dynamic alias is the tricky part. Contact your implementation specialist for assistance. See also the qAlias tool in the API Demo (KBA-01527) and distributed sample queries
- pqa_CCOAmt (KBA-01503: qAlias for Commitment Change Orders (CCO)),
- pqa_DaySummary (KBA-01570: qAlias for Daily Field Report), and
- pqa_ProjectTeam (qAlias for Project Team)
Dynamic aliases can be filtered and sorted in your template.
KBA-01476; Last updated: November 29, 2023 at 13:42 pm;
Keywords: qAlias