Document Designer
Add a document template
- Expand the “Documents” menu on the sidebar navigation.
- Select the “Design” menu.
- Select “Add” to add a document template.
- Populate the fields. Data fields with an asterisk (*) are required.
- Type*: Select the type of document template.
- Name*: Enter the name of the document template.
- Version: Enter the version of the document template.
- Document Template File: Browse and select the file to upload for this document template.
- Prior to uploading the template:
- Confirm the template document is closed prior to selection. The document will not load if the document is open on your computer.
- Ensure the templates are not password-protected upon upload.
- Complete all formatting within the template file; Assetas will only paste data into the defined locations.
- Ensure Excel files are .xlsx files, as .xlsm files are not supported.
- In Word files, create Bookmarks and place them in the appropriate areas of the document where data should be populated.
- Uploaded template files:
- Uploaded template files are not stored within Assetas as ‘Documents’.
- Download the template file to your computer via the download icon after saving.
- Data processing details:
- Data will be populated into hidden Excel tabs if mapped into those tabs.
- This tool will bypass any locked cell or individual worksheet protection. However, the workbook must be unprotected for Assetas to populate it via this tool.
- If the cell’s date format is set in the template, the tool will honor that format. If the date format is not defined, the system will populate the date format as defined in your preferences.
- Photos will fill the size of the cell. Merged cells will not alter this; the single cell within the merged cell cluster where the photo is mapped to is where the photo will reside.
- Prior to uploading the template:
- Tags: Enter the list of tags associated with this record.
- Description: Enter a description of the document template.
- External Identifier: Enter a unique identifier that represents this record in an external system.
- Select “Save” upon completion.
Build the file name
The file name defines how the resulting documents are named. By default, the system will define the file name as the name of the Document Template, the name of the user generating the file, and the generated date and time.
- Locate the document template and select the “Edit” button for the desired document template.
- Select “Edit” in the File Name section.
- Populate the fields. Data fields with an asterisk (*) are required. Review the sections below for how to reference other data sources.
- Select “Save” upon completion.
Develop mappings
Mappings define where and how the system places data into the document template.
- Locate the document template and select the “Edit” button for the desired document template.
- Select “Add” in the Mappings section.
- Populate the fields. Data fields with an asterisk (*) are required.
- Position*: Enter the position of the mapping within the template.
- Name*: Enter the name of the mapping.
- Bookmark*: The name of the bookmark for the mapping.
- Worksheet*: Select the name of the worksheet for the mapping.
- Column*: Select the column letter for the mapping.
- Row*: Enter the row number for the mapping.
- Maximum Rows*: The maximum number of rows to display.
- Distinct Rows: Toggle on to filter out duplicate rows.
- Datasource*: Select the source of the underlying data for this mapping.
- Comments: Enter any comments regarding this mapping.
- Select “Save” upon completion.
Import report as mapping
Make use of an existing report to configure mapping.
- Locate the document template mapping record and select the “Edit” button for the desired document template.
- Select “Import Report” in the Details section.
- Populate the field. Data fields with an asterisk (*) are required.
- Report*: Select the report would like to import.
- When importing a report to the mapping, and the report originally included linked text, all links will be removed upon import (but the original text will remain).
- This tool will overwrite any existing column, filters, and sorting configurations.
- Report*: Select the report would like to import.
- Select “Import” upon completion.
Mapping Column
The columns define what data is placed into the document template.
- Locate the document template mapping record and select the “Edit” button for the desired document template.
- Select “Add” in the Columns section.
- Populate the fields. Data fields with an asterisk (*) are required.
- Column*: Select the column to include in the report.
- Position*: Enter the position of this column within the report.
- Aggregate: Select the aggregate function to apply to this column (leave blank to use the unchanged data).
- Limit Length*: Where applicable, truncate the data that is displayed to this number of characters. Use 0 to show all the data.
- Select “Save” upon completion.
Mapping Filters
- Locate the document template mapping record and select the “Edit” button for the desired document template.
- Select “Add” in the Filter section.
- Populate the fields. Data fields with an asterisk (*) are required.
- Group*: Select the group of this filter. Groups are used in conjunction with logical statements where the entire group evaluates true or false.
- Precedence*: Enter a number to define the order of filters within each Group in relation to the other filters defined. This field requires a whole number.
- Column*: Select the data element chosen for each filter. The naming convention is: [Table.Element] (data type).
- For example, if you want to filter on the asset type, the appropriate selection would be “Asset Type.Name”.
- There is a search box within the dropdown that is helpful if you’re not certain which table your data element resides in.
- Operator*: Select the operator used to apply the filter.
- The Operator value of “contains any of these items” will return all results that meet the entries. For example, a filter to show only records that “contains any of these items” with “WELL,TANK” would return WELL0001, WELL0002, and TANK0001.
- The Operator value of “is in the list” is appropriate for matching entire values.
- Value*: Enter the value(s) to use for this filter. There are several options:
- Enter a single numerical or text value (please note that text values are not case sensitive).
- A comma-separated list of numerical or text values.
- Use a variable. See Variables for more information.
- The Append Variable button allows you to quickly select a variable from a re-defined list, including {Today}, {CurrentUser}, {StartDate}, or {EndDate}.
- The two filters {StartDate} and {EndDate} must be applied together; you cannot refer to only one of these two in the filters.
- Variables are denoted with curly brackets. Certain variables require you to enter a variable’s unique identifier or external identifier to evaluate properly. For example, for {ContactID}, simply enter the Contact’s unique/external identifier within the curly brackets. For Selection Lists, enter the Selection List report’s external identifier within the curly brackets, like so: {SL:Assetas_SL_Active_Assets}.
- See Variables for more information.
- If the filter is referencing one date, please use {AsOfDate}, which uses the current date as the reference point.
- Logical*: Select either ‘AND’ or ‘OR’ to describe how the filter relates to the following filter (in order of precedence). If the filter directly after this filter is in a different group, then the Logical field will describe how the two groups interact.
- Select “Save” upon completion.
Mapping Sort
Sorting criteria allow the document template to display results in the expected order.
- Locate the document template mapping record and select the “Edit” button for the desired document template.
- Select “Add” in the Filter section.
- Populate the fields. Data fields with an asterisk (*) are required.
- Column*: Selects the data element where the sorting is applied, must be one of the data elements returned.
- Position*: Defines the order of sorting rules applied, must be a whole number.
- Order*: Sets whether the sort is in ascending or descending order.
- Select “Save” upon completion.
View SQL Select Statement
No SQL skills are necessary to design a document. This is simply for validation if you’d like to use it.
This is a non-editable box that returns the SQL defined by the report parameters chosen in the sections above. The SQL will dynamically adjust as data elements, filters, and sorting rules are changed. If you are comfortable with SQL, reviewing the code within this box may provide additional validation that your report is returning correctly.
Validate the mappings
- Locate the document template mapping record and select the “Edit” button for the desired document template.
- Select the “Validate” button to check the document template mapping for any issues and display any errors found.
A document template mapping without validation errors does not necessarily guarantee that the logic in the document template is accurate or that the document template will return data. It simply affirms the way the mappings, filters, and sorting rules have been defined to generate the document template.
Security
Add a security role
The document templates are only available for use by users who have appropriate security roles.
- Locate the document template record and select the View/Edit button for the desired record.
- Select the “Add” button in the Security section.
- Populate the field. Data fields with an asterisk (*) are required.
- Security Role*: Select the security role that allows the use of the document template.
- Select “Grant” upon completion.
Add all security roles
The document templates may be available to all users if all security roles are granted access.
- Locate the document template record and select the View/Edit button for the desired record.
- Select the “Add All” button in the Security section.
- Select “Grant All” to confirm your selection.
Manage security roles
- Locate the document template record and select the View/Edit button for the desired record.
- To the right of each record, under the Security menu, you may revoke this security role which will prompt you to confirm revoking access to any user with this security role.
Manage document templates
- Expand the “Documents” menu on the sidebar navigation.
- Select the “Design” menu.
- To the right of each record, under the Actions menu, you may:
- Generate a document using the template.
- View detailed information or edit the document template.
- Download the document template definition for import into another database.
- Copy this document template as a new document template.
- Delete this dashboard template.
Validate
- Locate the document template record and select the “Edit” button for the desired document template.
- Select the “Validate” button to check the document template for any issues and display any errors found.
A document template without validation errors does not necessarily guarantee that the logic in the document template is accurate or that the document template will return data. It simply affirms that the way the mappings, filters, and sorting rules have been defined to generate the document template.
Generate
- Locate the document template record and select the “Edit” button for the desired document template.
- Select the “Generate” button to generate a document using the template.
- Populated templates are not stored within Assetas after they are generated.