The Data Importer tool's Create Template process saves a template configuration as an Excel file. Email capabilities are available to notify specified personnel when the Excel file has been saved. A link is provided to download the file as an Excel Workbook that can be distributed to users for data input. Initiate the Create Template process on the Manage Template Configurations form.
For instructions on creating data templates, click the following links or use the scroll bar to scan the page.
|
Template configurations are organized by Template Types, such as Entity Information, Permits, Contacts and Personnel, Task Assignments, etc. Just the types applicable to a module are available for that module. For example, Task Assignment template configurations are available only for the Task Manager module, while Contacts and Personnel template configurations are available for all modules. By default, a module's Data Importer folder contains links to the types of template configurations available for the module. The Data Importer folder is a part of the Tools folder in the Navigation Tree. To create a template for Contacts and Personnel data, you would click the "Create Contacts & Personnel Template" link. Accordingly, to create a template for Entity Information, you would click the "Create Entity Template" link. The links open the Manage Template Configurations form.
Once template configurations exist for your organization, templates can be created for specific data collection activities. Each template created is saved as an Excel file that is downloaded as an Excel Workbook and distributed in accordance with your business processes. Users input data into the Workbook via Excel. Refer to Working with Template Excel Files for a description of the workbook.
Before the template is created, the following configuration settings can be changed:
Exclude Columns that have not been marked as required. To help increase the probability of successful imports, interdependent columns are displayed with a color to indicate that all columns in the grouping must be selected or unselected. One or more groupings may apply to a template. Each group's color is chosen randomly and its primary purpose is to provide a visual representation of data interdependencies.
Add or edit Comments or special instructions. The comments are added to the cell with the column name in the Excel file. Cells with comments have indicator triangles in the upper-right corners. Rest the pointer over a cell that has an indicator to display the comments.
Specify how many times a column should appear in the template in the Child Instances field. For example, in the Entity Information template you might want the Contacts column to appear 3 or 4 times to permit multiple contact entries for a given entity. The Child Instances option is available for applicable columns only.
Indicate whether or not a Validation List applies to a column. Validation lists contain the data that exists in your Essential database at the time the template is created. All validation data is used to verify user input during the template validation and import process. Validation data can be viewed in the Excel Workbook. Refer to Working with Template Excel Files.
Email is the method used to notify personnel that an Excel file is ready to download. Enter one or more email recipient addresses, separated by semi-colons, in the E-mail Template Link To field on the Manage Template Configurations form. In addition, a copy of the email is sent to your Data Importer administrator (at the address designated for the "AdminEmailID" key in the Data Importer configuration file). The email message contains a link to the file. Since the file will be deleted from the web server after 7 days, it is recommended that you save the file to your desktop or another local destination when the message is received.
As a default, the logged-in user's Entity Access Profile is used when a template is created and the associated validation data is generated. In many cases, this is more information than necessary for the users responsible for data entry. An option is available to filter template data by another, existing Entity Access Profile to help reduce the size of the Excel Workbooks and expedite the data entry process. In the Entity Access Profile Configurations section on the Manage Template Configurations form, choose the applicable Entity Access Profile by profile name or user login name when a template configuration is established. Just the entity or entities associated with the Entity Access Profile specified will be used to generate validation data for the templates created with this configuration.
For the Entity Access Profile option, a field is available to select a Culture that will be used for the Excel Workbooks created from the template. By default, the field is populated with the logged-in user's Culture setting. For the User Login option, the Culture setting associated with the user's login account is automatically applied. Refer to User Security Management for additional information.
The Entity Access Profile Configurations section is only available when the following conditions are met:
Entity-related validation applies to the template.
Administrative privileges have been granted to the user establishing the template configuration.
Otherwise, the Entity Access Profile Configurations section will not be displayed on the form.
Create templates using the Manage Template Configurations form. Just the template types applicable to the modules that are a part of your Essential installation are available for selection. To avoid possible serious validation errors, all templates should be re-created after an upgrade to a newer version of Essential.
To create templates
Click Tools
> Data Importer in the Navigation
Tree. Then click the Create
Template link for the type of template to be created.
The Manage Template Configurations
form is displayed.
Use the following guidelines to determine your next step:
If the Entity Access Profile Configurations section IS NOT displayed, continue to step 3.
If the Entity Access Profile Configurations section IS displayed, select the appropriate option and corresponding value from the list to filter by entities. For the Entity Access Profile option, select a different culture from the list when applicable. Otherwise, continue to step 3. Refer to Entity Access Template Configurations for additional information.
Access the Manage Template Configurations section, review the Open Template Configuration field entry, and select a different one from the list when applicable.
Enter the address for each recipient
of the template notification email message in the E-mail
Template Link To field. Separate multiple addresses with a
semi-colon.
Note:
A copy of the email message is automatically sent to the address
of the administrator identified in the Data Importer configuration
file.
Use the guidelines below to determine your next step:
When the template does not require changes, skip to step 11.
When the template requires changes, continue to step 6.
Review each Column
Name and de-select any that should not be included in the template
being created from this configuration. The All
and None links are available
to quickly select all fields or clear all field selections.
To help increase the probability of successful imports, interdependent
columns are displayed with a background color to indicate that all
columns in the grouping must be selected or unselected. One or more
groupings may apply to a template. Each group's color is chosen randomly
and its primary purpose is to provide a visual representation of data
interdependencies.
Add or edit comments or special instructions for a field by clicking the Ellipsis button next to the Comments field, entering your comments, and clicking the Save button. The comments are added to the cell with the column name in the Excel file.
Enter a number in the Child Instances field that represents how many times a column should appear in the template. This field does not apply to every column, and therefore, is displayed only when applicable.
Remove the Validation List requirement for a column by clearing the check box.
Click the Save Configuration button.
Click the Create Template button.
A confirmation message is displayed. Click OK
to close the message window.
Repeat steps 2 through 11 to create another template.
Caution: It is important to re-create your templates after
upgrading to a newer version of Essential. Otherwise, serious validation
errors may occur.
An existing template configuration can be copied by opening the source configuration, specifying the name of the target configuration, and clicking the Save Configuration As button.
To copy templates
Click Tools
> Data Importer in the Navigation
Tree. Then click the Create
Template link for the type of template to be created.
The Manage Template Configurations
form is displayed.
Select the configuration to be copied from the list for the Open Template Configuration field.
Enter the name of the new configuration in the field next to the Save Configuration As button, and then click the button.
Click OK to close the confirmation message window. The new configuration name is displayed in the Open Template Configuration field.
Make any necessary changes and click the Save Configuration button.
At least one email address must be entered when a template is created. A link to the Excel file is provided in the message text. Click the link and specify the file name and location details. Since the file will be deleted from the web server after 7 days, it is recommended that you save the file to your desktop or another local destination when the message is received.
The Excel Workbook created from the template can be distributed either inside or outside of Essential. The Data Importer tool provides the flexibility to determine the appropriate process based on your business needs. Distribution options include:
Outside Essential: Email the Excel Workbook to your facility representatives, or
Inside Essential: Grant access to specific users to view and create templates themselves.
Email Excel Workbook Option |
User Template Access Option |
Benefits |
|
Since one person is typically responsible for distributing the workbooks, access is restricted to a limited number of users (e.g., system administrator, power user). |
Administrative work is reduced for the administrator or person responsible for distributing templates. |
Risks |
|
Users may not access the file until
days/weeks after initial delivery. If subsequent files are sent
with changes, there is a possibility the user will open the original
file, which is no longer current. |
The opportunity for users to manipulate a template in the Data Importer tool before creating the Excel Workbook exists. |
Caution: It is important to re-create your templates after
upgrading to a newer version of Essential. Otherwise, serious validation
errors may occur.
Each Excel Workbook contains the following four worksheets.
Sheet 1: Template Type – The name of the template type (e.g., Permits) is displayed on the worksheet tab. Data to be imported is entered in this worksheet. It may be necessary to format date-related cells to match the date format of your Essential culture. Refer to Date format Compatibility for additional information.
Sheet 2: Validation Data – All the options available for the columns included in the template are stored in this worksheet. The values listed either are part of the configuration or existed in your Essential database at the time the template was created. Any data entered in Sheet 1 will be validated against the data in this worksheet prior to the import process. General users should NOT modify this data.
Sheet 3: Validation List Relations – Information regarding relationships between different validation lists is stored in this worksheet. For example, on the Entity template, the options available in the State column may change if a different Country is selected. General users should NOT modify this data.
Sheet 4: Reference – Information generated for identification and validation purposes when a template is created is stored in Sheet 4. The information includes the applicable user Culture setting that identifies localization for template data entry and validation. General users should NOT modify this data.
Caution: Do not add any worksheets to, nor delete any worksheets
from, template Excel Workbooks. Changes (data input/selection) should
only be made to worksheet #1.
When localization has been implemented, a user's Culture setting determines the language and numeric/date format of the user's Essential interface. As a result, users can expect to enter dates in the format of their assigned culture. However, Data Importer defaults template date formats to mm/dd/yyyy HH:MM:SS AM/PM in Excel Workbooks. In order to successfully import dates, the date format in Excel must match the date format of the your assigned culture. Consequently, it may be necessary to re-format date-related cells in Excel.
Listed below are examples of Cultures with date formats that can differ (based on operating system) from the default template date format:
Culture |
Date format |
Danish (Denmark) |
dd-mm-yyyy |
English (Canada) |
dd/mm/yyyy |
German (Germany) |
dd.mm.yyyy |
Japanese (Japan) |
yyyy/mm/dd |
Create a Custom Number format that matches the Culture date format for the cells that require date entries in Sheet 1 of the Excel Workbook where data is entered. You can select a column, right-click, and select the format Cells option. On the Number tab, select the Custom category and enter the appropriate format in the Type field.
The spreadsheet format introduced with Microsoft Excel 2007 stores data in cells arranged in rows and columns based on the Open XML format and includes ZIP compression for smaller file sizes. Over 1 million rows and 16 thousand columns per worksheet are supported. To use a newly created Data Importer Open XML template with an earlier version of Excel, such as Excel 2003/2000/XP, the Microsoft Office Compatibility Pack must first be downloaded and installed. When the template is opened in your version of Excel, an informational message displays notifying you any data outside the previous column (256) and row (65,536) maximums will not be available. A read-only version of the workbook is presented to protect the original file. Save the read-only version as a different file so the data outside of the valid range can be removed. The saved file can then be populated and saved as an Excel 2007 file. Data Importer will not accept earlier Excel file versions for validation and import. Users with Excel 2003/2000/XP can still only view and edit cells within the 256 column/65,536 row range.
