The Formatting tab is available with the Advanced mode has been selected from the Properties tab. The Formatting tab allows you to customize and manipulate fields within an export.
Data will export in the order shown in the "Form". Each specified field will export data associated with the specified field. Custom Text fields will export as blank unless a mapping is attached to the Custom Text Field, or a Prefix or Suffix is populated.
The "Form" appearance will change based on the Format specified on the Properties or Records Tab. For Example:
- Properties Tab
- A fixed length file will include the “Start Field”
- A .csv file will include the “Max Field Length” field
- Records Tab
- Cross Tab / Matrix – enables the “Repeat Field”
- Filter By – used to select a record when there are multiple records in the export
Identifies the fields included in the applicable record.
Max Field Length/Position
Max field length (for delimited files) or Position (for fixed length files)
Max field length, used in delimited fields, allows you to limit the number of characters the field exports. For example, if the field is Middle Name and the user wants to only see the middle initial, a max field length of one can be entered, and the export will only the first letter of the middle name.
Position, used in fixed-length files, determines the numerical position the field should appear. This works only for text files (.txt extension) and is typically only used upon benefit carrier request.
Allows you to add data that is universal to all employee data to be exported or a prefix to the data expected to export without any spaces.
For example, if everyone is in "Class 0001", then adding "0001" to the prefix field will add "0001" to every record will export 0001 in this field.
The 834 file format has predefined prefixes in accordance with industry standards.
Allows you to add universal data to all employee to be exported or a suffix to the data expected to be expected to export without spaces.
For example, ~ is used to terminate/end data in an 834 file. So the data would be shown as "EXAMPLEDATA~".
This column will appear only when "Include Column Headers" is enabled on the Properties tab of the export. The column header field is an open text box and any characters can be entered. The column header will populate the data entered at the top of the file and will not be repeated per record. This is useful when a user is pulling a custom text field into the file but the user does not want the header of the column in the file to be "Custom Text Field".
If enabled, allows you to specify the fields that should not be included in the export.
For example, some fields will be used for mapping only and should not be exported. Additionally, you may have inserted fields and later learn that they are not needed.
It is recommended once the export is approved for production that these fields be removed leaving a "clean" export. This makes it easier for anyone looking at the file in the future not to wonder if it is actually being used or not.
Used to populate a field in the file with a value that is based on the database field or a hard-coded value. Is also used to exclude a field or record under certain scenarios.
- Copy Mappings from this field to:
- Allows you to update field mappings and copy them to another field. This will cut down use time when configuring a file. The user has the ability to select the record from the first drop-down, and then the field within that particular record in the second dropdown
- Add x new mapping groups
- Allows you to add one or multiple mappings groups to field mappings configuration
- Drop-down menu to specify Mapping Groups is displayed 25 to a page. The Display Mappings dropdown allows you to advance to a certain mapping group.
Field Mapping Columns
The columns available may vary depending on your setup.
|Add a new row to the mapping group. Useful when needing to use a conjunction rule.|
|Mapping Group||Mapping Groups contain the statement(s) for a specific field. A field can have multiple Mapping Groups to identify different employees and the data that should be populated in the field. Mapping Groups can be reordered by reassigning the Mapping Group number.|
|Conjunction||When a mapping group contains multiple rows, a conjunction must be specified. The available conjunctions are "and" and "or."|
|Source Field||The fields in the dropdown are the only fields that are available to use in mappings. If a field is needed in a mapping group, it must be included in the record on the Fields tab. A field can be included on the Fields tab, and then excluded on the Formatting tab, so that the field is not included in the output of the export. Source Fields appear in the dropdown in the order they are in the export.|
|Operator||Used to specify how the field selected as the Source Field relates to the Source Value.
|Source Value||An entered value or a drop-down menu that is related to the Source Value via the Operator.|
|Source Value Lookup||When the Operator is either; is one of (lookup), or is not one of (lookup) and Lookup Values (configured in detail) is selected, a hyperlink for "Click here to manage Lookup values" will appear. The Source Value can be a list of values in a single column spreadsheet. The spreadsheet should contain a Header row, as the first row of data is not included. The spreadsheet is uploaded after the Mapping Group has been added by clicking the link in the Source Value column.|
The value is to be included in the output of the export. There are four options available:
- Duplicate Selected Mapping Group
- Allows you to duplicate existing mapping groups. This is especially helpful when multiple mappings are needed with little variance between groups.
- Delete Selected Records
- Allows you to delete entire mappings groups or individual lines within a mapping group.
- To use these features, select the first checkbox to delete or duplicate the entire mapping group or select a specific line to delete.
The data populated in the rows that follow are question icons when a mapping group exists against the field. Hovering over the question icon shows the first ten mapping groups that exist against the field.
Allows you to format data in accordance with carrier requirements. For example, Date, Time, Currency format. Removing special characters such as decimals, dashes, apostrophes.
This column contains the link to add or remove formatting configuration on a field. When clicked, the user will be redirected to the field formatting configuration page. From here, the user can format the value of the field by selecting an option and clicking save. The user will select a formatting option that corroborates with the field selected. Example: if the field is a date, the user may want to select a date format, such as CCYYMMDD.
When formatting is configured on a field, the formatting description will appear in the rows under this column.
Allow you to add a reminder, column header (especially helpful when using multiple custom text fields.
This is an open text field that allows the user to type notes against a certain field. This is commonly used when the field itself does not describe its purpose in the file (i.e. a blank custom text field will not tell the user what the field is for; the notes section can notify other users what the intended purpose of the blank field is for.)
Filter by Drop Down Menu
Clicking the filter icon will show the "filter by" drop-down menu that will display all records contained within the file. The number of records included is configured on the Records tab of the data export. When a record is selected from the drop-down menu, the page will refresh and display fields only contained within that record (fields can be added to records on the Fields tab of the export.) If only one record is on the file, the user can click the drop-down menu, but only one option will appear.
Clicking "Save Formatting" saves data entered into any of the open text fields on this page. It is best to save your changes before navigating to another record within the formatting tab.