Generic Excel Template to Create Collect Template

<< Click to Display Table of Contents >>

Generic Excel Template to Create Collect Template

EarthSoft has provided a generic Microsoft Excel template file (GenericTemplate.xlsx) that can be used to create a new Collect Template with the Generic Template option (see Create New Form Template article). The GenericTemplate.xlsx file contains worksheets for Forms, Fields, Mappings, and Selectors that can be populated to define the form(s), field(s), field type(s) and mappings for the Collect template. The Lookups worksheet does not require any input and is used in the Excel file for drop-down menu reference.


Populating both the Forms worksheet and Field worksheet, at a minimum, is required for the Excel template to correctly produce a Collect template file. The Selectors worksheet is required if any Selector field types are added on the Fields worksheet. The Mappings worksheet is optional. Data mappings can always be created in the Template Designer in Collect Enterprise.  


Elements of each worksheet are described in the following sections and a simple example is provided. Column headers denoted in red are required elements. Column headers denoted in green are conditionally required when certain other criteria are met.


Forms Worksheet

Form Name – Name given to a form on the Collect template.

Parent Form Name – Name of the associated Parent form if the form name is designed to be a Child form (i.e., sub-form).

Description – The description given for the form.

Header Field – The designated field used as the Header for the form. This requires using a field name from the Fields worksheet.

Sort – Numerical values that can define the sort order but is not required. This column can be useful for complex Collect templates with many forms.



Fields Worksheet

Form Name – The form name for where the field will be placed. The form name must match a name from the Forms worksheet.

Field Name – The name for the field. Best practice is to keep field names concise. Field names are required to be unique for each given form name.

Field Caption – A brief note regarding field details. Providing a clear and concise caption is recommended as the caption will be displayed on the mobile device rather than the field name. If no caption is given, then the field name will be displayed instead.

Field Description – A longer description, if necessary. Provides details about the nature/content of the field.

Field Type – Select the desired field type from the drop-down list. Formula and selector fields require the appropriate attribute and/or Selector page options defined.

Field Group Name – Enter a Group name if the field is to be part of a Field Group.

Field Combined Name – Enter a Combined Group name if the field is to be a part of a Combined Field Group.

Order – Numerical values that can define the sort order but is not required.

Field Attributes – Various attributes (columns with green shaded headers) can be assigned to the field using formulas. See the Field Attributes article for more information.



Mappings Worksheet


To generate a Collect template without data mappings, leave the Mappings worksheet blank.

Form Name – The form name to be mapped. The form name must match a name from the Forms worksheet. Forms can have multiple data mappings to various tables.

Table – The specific table, from a given and available EQuIS format, to which a field will be mapped.

Mapping Group – Name to be applied to the defined Table Mapping.

Target Field – The specific format table field to which a particular field value will be mapped (Target field from Target table).

Target Value – The field or field value to be assigned to a format. This can be defined as a formula or specific field. Fields should be noted using brackets [ ] (e.g., [sys_loc_code]).


See the Data Mapping article for more information.



Tip: To efficiently generate Excel files with data mapping, have a blank Format EDD open to serve as a reference. Copy and paste the fields to be mapped and used the Blank EDD to quickly copy and paste the appropriate mapping target fields from the format.



Selectors Worksheet

Field Name – The field name associated with a selector field type where options are to be listed. The field name must match a name from the Fields worksheet.

Code – The code assigned for the options of the selector field type.

Description – A description can be used to expand details of the assigned code if needed.

Group – A group name if the selector field type is a Group Selector or Group MultiSelector.

Visible – The visible attribute can show or hide selector options using a function or formula.


See the Selector/Multiselector Field Type article for more information.




Generated Collect Template


In this example, a new Collect template was create with three forms (two parent forms and one sub-form) and the associated fields and data mappings defined in the Excel file.