The Records Tab is only visible when you have selected Advanced Mode from the Properties tab. This tab allows you to select the types of Records to be included in the export. The requirements for the types of records to be included are generally provided in the file specification document received by the carrier/vendor. The exception to this is the 834 file which is a standard format across all carriers.
Advanced Record Layout Options
The Advanced Record Layout Options dictate the output of the data on each of the selected records as well as the order (Separate or immediately after each Employee record) of the Dependent records.
The options listed will differ depending on the data type selected in the properties tab.
Include the Dependent Records immediately following Employee Record
When enabled the data will export as follows:
- Employee Record 1
- Dependent Record 1-1
- Dependent Record 1-2
- Employee Record 2
- Dependent Record 2-1
Your file specification document will dictate how dependent data should export.
Enable a Cross Tab / Matrix Export - one record per Employee and Dependent using repeating fields
When enabled, the opportunity to configure certain fields as 'repeated' will appear on the 'Formatting' Tab'. When this is done, it will be possible to create a Matrix-style export as in the following example:
- Employee2(-Field1, Employee2 - Field2, Medical - Field1, Medical - Field2, Dental - Field1, Dental - Field2)
In this example, the Benefit Fields (Field1 and Field2) have been configured as 'repeated' fields and as such, will be repeated in the export for each employee and selected benefit.
When using the Cross Tab/Matrix option, think of the data as being “horizontal”.
Group export records by Employee based upon fields included in export. Numeric field will be summed within Group
This option will group records based upon the Fields selected within the export AND sum any numeric fields within the Group. An example of when this feature might be used is when Benefit Deductions might need to be summed together into a single record for export to payroll.
Example (Summing Deductions)
Benefit Configuration includes Employee, Spouse and Voluntary Life and the account’s payroll system take this as one single deduction.
Example (Grouping Records)
When the option is NOT enabled:
The yellow highlights identify the Employee Record Type:
02-Employee Record 1
03-Employee Coverage Record 1
05-Employee Record 2
06-Employee Record 3
When the option is enabled:
When this option is enabled, and you wish for Change Log records to be closed out of the Change Log when this export executes, you MUST select the 'Reset all change log records' option on the Properties Tab.
Allow for configuration of separate Employee and Employee Coverage record
When enabled, it will separate the Employee Records and the coverage records.
- Employee Record 1 for John Doe (firstname, lastname)
- Employee Record 1 for John Smith (firstname, lastname)
- Employee Record 1 for One Test (firstname, lastname)
- Employee Coverage Record 1 for John Doe (benefit 1, plan, coverage)
- Employee Coverage Record 1 for John Doe (benefit 2, plan, coverage)
- Employee Coverage Record 1 for John Smith (benefit 1, plan, coverage)
- Employee Coverage Record 1 for John Smith(benefit 2, plan, coverage)
- Employee Coverage Record 1 for One Test (benefit 1, plan, coverage)
In the above example, the employee firstname and lastname should only be included in the export once for each employee. However, if the employee is covered in more than one benefit, then the Employee Coverage Record should be included once for each covered benefit.
Allows for the configuration of:
- 2 Header Records
- 12 Employee Records (typically you will use 1-3 records with the exception of Time Off files that you may use all 12 records depending on the number of Time Off types the client has (Sick, Vacation, Jury Duty, etc.)
- 1 Dependent Record
- 2 Footer Records
The above variation will change if you are configuring separate employee and coverage records.
- The Header record provides initial Export information to the receiving entity.
- The Employee record provides demographic and benefits info.
- The Dependent record also provides demographic and benefit info, as well as Employee demographic fields as most exports with Dependent info, require the data to be associated with an Employee.
- The Footer record is the trailing data (i.e. Record Count) for the export.