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.
- Expand Data, expand Exports, and click Manage from the menu.
- Click the name of an existing data export or go to the Actions drop-down menu and click Create Export. If creating a new export, you also have to complete the "Properties" tab first.
- Click the Formatting tab. If the "Formatting" tab is not present, the Mode of the Data Export is set to 'Basic' on the "Properties" tab.
Formatting Options
Once your fields are selected from the "Fields" tab, and saved it will fill the form on the "Formatting" tab. Data will be exported 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:
- From the Properties Tab
- A "Fixed Length" file format will include the “Start Field”
- A .csv file format will include the “Max Field Length” field
- From the Records Tab
- Enabling Cross Tab / Matrix will enable the “Repeat Field”
- Filter By – used to select a record when there are multiple records in the export
Formatting Columns
Depending on other options enabled, the "Formatting" tab will provide the following columns:
- Export Field
- Max Field Length/Position
- Prefix
- Suffix
- Column Header
- Exclude Field
- Mapping (Hyperlink)
- Field Mappings
- Formatting (Hyperlink)
- Formatting
- Notes
Export Field
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 you 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.
Prefix
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.
Suffix
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~".
Column Header
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 you are pulling a custom text field into the file but you do not want the header of the column in the file to be "Custom Text Field".
Exclude 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.
Mapping (Hyperlink)
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. Mapping Groups are displayed 25 to a page. The Display Mappings dropdown allows you to advance to a certain mapping group.
Field Mappings
- 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. You have 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.
| Column | Description |
|---|---|
| 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. When entering a value, the value must match the value exactly as it is in Core HR. For example, you must use the full text of "Terminated" and not any shorthand like "Term". |
| 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. |
| Mapped Value |
The value is to be included in the output of the export. There are four options available:
|
Actions
- 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.
Mapping
If a mapping group has been set up for the field, an will appear in this column. Hovering over the question icon shows the first ten mapping groups that exist against the field.
Formatting (Hyperlink)
Allows you to format data in accordance with carrier requirements. For example, Date, Time, and Currency format. Removing special characters such as decimals, dashes, and apostrophes.
This column contains the link to add or remove formatting configuration on a field. When clicked, you will be redirected to the field formatting configuration page. From here, you can format the value of the field by selecting an option and clicking save. You will select a formatting option that corroborates with the field selected.
For example, if the field is a date, you may want to select a date format, such as CCYYMMDD.
Formatting
When formatting is configured on a field, the formatting description will appear in the rows under this column.
Notes
Allow you to add a reminder, column header (especially helpful when using multiple custom text fields.
This is an open text field that allows you 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 you what the field is for; the notes section can notify others 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, you can click the drop-down menu, but only the single selected option will appear.
Save Often
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.