This article describes how to use a Multi-Record type transformation definition to create multiple records in an output based on a single record in the input.
Certain output formats require that an employee's hours worked are itemized by the type of hours in the time sheet. But, if we have a file where these hours are only on one row per employee, we need a way to create multiple records.
For example, here is our source file that shows each employee row with Regular Hours, Overtime Hours and PTO Hours:
We need to transform this data to create a new record for Regular Hours, a new record for Overtime Hours and a new record for PTO Hours:
This can be accomplished using a Multiple Record Type transformation definition.
Create a Transformation Definition
To create a Multiple Record Type transformation definition, click on your initials in the upper right and select Admin Console.
Click on the Transformation Definitions tab, click the ADD button, and select Create New Definition.
On the new transformation enter in a Name and Description. In the Input Definition Type field select "File Definition" and select your File Definition from the Input Definition dropdown.
Note: if you have not yet created a File Definition, please see How to create a new File Definition.
In the Output File Type dropdown, select "Multiple Record Type (CSV)" and set your desired Delimiter.
For each record we need in the transformed file, we will need to start a new record and add fields to the record.
To create a record, click the START NEW RECORD button and provide a name in the Record Name field.
Here we will create a new record for the Regular Hours for our employees.
In the Input Fields dropdown, we select 'Regular Hours | Numeric'. Click the ADD Rule button and select "Create Record if Any Greater" and enter 0 into the Compare value field and click OK.
This will create a record for any employee in the file where their Regular Hours are greater than zero.
We now need to add fields to this record. In most cases, each new record will have the same amount of fields; only the mappings of these fields may vary.
Referring back to our example above, we need to have the following fields for reach record we create:
EE_ID
Pay Code
Hours
To create a field, click the ADD FIELD button and provide a name of the field.
Then we select Employee ID | STRING to map the Employee ID to this field and click OK.
Then we create a field for the Pay Code. Here we don't need to map to an Input field, so we will use a Default Value transformation rule and enter the Pay Code.
Our last field will be the Hours field. Since we are creating a Regular Hours record, we will select the Regular Hours | NUMERIC from the Input fields dropdown and click OK.
We've now created our first record for Regular Hours that will be created if the employee has Regular Hours in the source file. To create a record for the Overtime Hours and PTO Hours, we simply create a new record, add the fields to the record, and map the appropriate hours field to the Hours field of each record.
Here is what the Overtime Hours record will look like with the appropriate field mappings and default value for Pay Code.
After creating a new record for the PTO hours in a similar fashion, click the Save Changes to save the multi-record type transformation definition.
Header Row on a Multi-Record Type Transformation
When creating a multi-record type transformation definition, the header row is left off the transformed file. If you require a header row, then you will need to use the Add Header/Footer Row step when creating the moov Definition that uses this transformation.
For general information on creating a moov Definition, please see the How to Create a new moov Definition article.
