The Timesheet Import module allows time and attendance information that is captured outside of the FastTrack360 system to be imported into the system so that electronic timesheets can be created, approved, paid and billed.
The Timesheet Import module imports timesheets via an import batch process, which groups together a collection of timesheet data that is processed together to create timesheets within the FastTrack360 Time and Attendance module. The timesheet import batch process consists of the following general steps:
Uploading the timesheet import file.
Defining the field mapping.
Queuing the timesheet import batch for processing. Each of these steps is described in more detail below.
Timesheet Import supports import of attendance items and additional items only. Absence items and reimbursement items cannot be imported.
Uploading the Timesheet Import File
The import batch process is initiated by uploading a timesheet import file, which contains the data that will be used to create timesheets and to populate information on each timesheet. The timesheet import file must be formatted such that Timesheet Import can read the import file and its content. For example, the first row of the import file must contain column headers that can be mapped to FastTrack timesheet fields. For more information, see Import File Format Validation.
Defining the Field Mapping
If the format of the import file format is valid the system will read and display the column headers from the import file. The column headers must be mapped to the corresponding Time and Attendance timesheet fields to inform the system what data is supplied in the import file and what that data corresponds to within the FastTrack system.
A set of field mappings that are configured when importing a timesheet import file can be saved as a template, which can be applied whenever an import file that originates from the same source is processed in the future. This prevents the need to manually configure the field mapping each time an import is processed. For more information, see Saving Field Mapping Templates.
Queuing the Import Batch for Processing
Once the field mapping has been configured it is possible to queue the timesheet import batch for processing. On attempting to queue an import batch for processing, the system performs field mapping and data type validation to ensure that timesheets and timesheet items can be theoretically created based on the mapping and types of data provided within the import file. For more information, see Field Mapping & Data Type Validation.
If the validation succeeds the import batch is sent to the Process Queue, where the batch undergoes processing. The amount of time that it takes for a queued batch to complete processing depends on a number of factors such as the following:
the number of prior import batches already queued for processing the number of individual rows within the import file.
When processing of an import batch is completed, the following occurs:
any timesheets that can be successfully created based on the data in the import file are created in the Time and Attendance module and the status of the timesheets is set to Submitted
the status of the batch is set to Completed or Completed with Exceptions.
Once the processing of an import batch is completed you can do the following:
download the original import file
download the exception report that identifies the rows from the import file that failed to import successfully and the exception reasons that explain why each exception row failed to import
download the validation report that identifies all rows from the import file that imported successfully
view the import batch audit information.
If a system error occurs when an import batch is queued for processing the status of the batch will be set to Failed and no timesheets will be created in Time and Attendance. If this occurs, you will need to process the original import file as part of a new timesheet import batch.
Import File Format Validation
When you attempt to create a timesheet import batch the system performs validation to ensure that the Timesheet Import module can read the contents of the import file.
The following conditions must apply to the import file:
the first row must consist of column headers and each column header must be unique
individual fields within the file must be separated by a delimiter character, such as a comma, tab, pipe or some other type of character that must be specified at the time of import.
no grouping rows, such as summed totals, can exist in the file
each row must contain data that can be imported independently of any other row (e.g. a single row cannot contain both an attendance item and an additional item)
the first row that contains no data signals the end of the file (for XLS or XLSX files only) all dates must be in the format dd/mm/yyyy
all times must be in 24 hour format hh:mm.
Applying Formatting to Import Files
Timesheet data that is output by a third-party application may not be in a format that is readable by the Timesheet Import module. Therefore, an import file will usually require some level of formatting before it can be uploaded into Timesheet Import. The formatting can be applied manually or via an automated macro script.
Macro scripts can be generated automatically via the Record Macro function in Microsoft Excel, or in more complex cases, can be written manually.
Import File Examples
The example below shows an example of an incorrectly formatted import file:
Contract | PO Number | Item | Contractor Name | Service | WorkDay | Qty | Unit | Rate | Net Value |
123015011 | 78932 | 50 | Bloggs, Joe | ||||||
3000456 | 01/01/2011 | 8 | HR | 88.25 | 706 | ||||
3000456 | 02/01/2011 | 8 | HR | 88.25 | 706 | ||||
3000456 | 03/01/2011 | 8 | HR | 88.25 | 706 | ||||
TOTAL: | 24 | 2118 | |||||||
123005111 | 84527 | 30 | Smith, John | ||||||
3000458 | 01/01/2011 | 10 | HR | 90.25 | 902.5 | ||||
3000458 | 02/01/2011 | 10 | HR | 90.25 | 902.5 | ||||
TOTAL: | 20 | 1805 |
In the example above, the import data is not formatted correctly because the data includes grouping rows with summed totals. The next example shows the same data but this time formatted correctly to remove the grouping and grouped totals:
Contract | PO Number | Item | Contractor Name | Service | WorkDay | Qty | Unit | Rate | Net Value |
123015011 | 78932 | 50 | Bloggs, Joe | 3000456 | 01/01/2011 | 8 | HR | 88.25 | 706 |
123015011 | 78932 | 50 | Bloggs, Joe | 3000456 | 02/01/2011 | 8 | HR | 88.25 | 706 |
123015011 | 78932 | 50 | Bloggs, Joe | 3000456 | 03/01/2011 | 8 | HR | 88.25 | 706 |
123015111 | 84527 | 30 | Smith, John | 3000458 | 01/01/2011 | 10 | HR | 90.25 | 902.5 |
123015111 | 84527 | 30 | Smith, John | 3000458 | 02/01/2011 | 10 | HR | 90.25 | 902.5 |
Shifts with Multiple Project Codes & Breaks
A shift on a timesheet in Time and Attendance can be allocated against one or more project codes and can include one or more breaks. To support this, Timesheet Import provides the ability to define up to ten individual work start and end times, break start and end times and project codes that each line within the import file can be allocated to. For example, consider the shift information in the table below.
Job Order | Payee Name | Date | Attendance Name | Start Time 1 | End Time 1 | Start Time 2 | End Time 2 | Break Start 1 | Break End 1 | Project Code 1 | Project Code 2 |
123456789 | Bloggs, Joe | 01/10/2015 | Day Shift | 09:00 | 12:30 | 13:00 | 17:00 | 12:30 | 13:00 | ABCD | ABCD |
In the example above there is one shift from 12:30 until 13:00. Therefore, there are two sets of start and end times (Start Time 1, End Time 1, Start Time 2, End Time 2) to represent time worked before and after the break respectively. In this case the total time worked during the shift is attributable to project code ABCD. Note that because there are two sets of start and end times the project code is defined twice (Project Code 1, Project Code 2), with each instance being applicable to the time prior the break and after the break respectively.
However, if the last hour of the shift was attributable to project code XYZ1 instead of ABCD, the row of the import file would be as shown in the table below.
Job Order | Payee Name | Date | Attendance Name | Start Time 1 | End Time 1 | Start Time 2 | End Time 2 | Start Time 3 | End Time 3 | Break Start 1 | Break End 1 | Project Code 1 | Project Code 2 |
123456789 | Bloggs, Joe | 01/10/2015 | Day Shift | 09:00 | 12:30 | 13:00 | 16:00 | 16:00 | 17:00 | 13:30 | 13:00 | ABCD | ABCD |
If there was an additional 30 minute break at 15:30, the row of the import file would be as shown in the table below.
Job Order | Payee Name | Date | Attendance Name | Start Time 1 | End Time 1 | Start Time 2 | End Time 2 | Start Time 3 | End Time 3 | Break Start 1 | Break End 1 | Break Start 2 | Break End 2 | |
123456789 | Bloggs, Joe | 01/10/2015 | Day Shift | 09:00 | 12:30 | 13:00 | 13:30 | 16:00 | 17:00 | 13:30 | 13:00 | 15:30 | 16:00 | A |
Field Mapping & Data Type Validation
When you attempt to queue an import batch for processing the system will perform validation of the field mapping that has been applied to ensure that fields that have been mapped are sufficient to theoretically create timesheets and timesheet items within Time and Attendance. These validations are described below.
If any of the field mapping or data type validation checks fail, the Timesheet Import batch will not be queued for processing and an error message will be displayed to identify the reason why the validation failed.
For a full list and description of the fields that can be mapped and the applicable data type of each field, see Mapping Fields.
Field Mapping Validation
When you attempt to queue a Timesheet Import batch for processing, the system will first check if fields have been mapped to identify the FastTrack job order to which each row in the import file corresponds. Specifically, the system will check if any of the following fields have been mapped:
Job ID
Job Alt No
Purchase Order Number
If none of the abovementioned fields have been mapped, Timesheet Import will check if all of the following combinations of fields have been mapped:
Candidate ID or Candidate Alt No or Candidate Full Name or (Candidate Firstname + Candidate Surname)
Item Date or Week Ending Date.
Next, Timesheet Import will check if the following fields, which are required to identify attendance items, have been mapped:
Start Time 1
End Time 1
Standard rates timesheets may not have shift start and end times and therefore it is not mandatory to map the Start Time 1 and E nd Time 1 fields. However, if a start time field is mapped, the corresponding end time field must also be mapped.
If the Start Time 1 and End Time 1 fields have been mapped the system will then check if the following fields have been mapped:
Item Date
Attendance Item Name, Attendance Item Import Code, Pay Code Name or Pay Code Import Code.
If neither Start Time 1 nor End Time 1 fields have been mapped the system will check if all of the following fields have been mapped:
Item Date
Pay Code Name or Pay Code Import Code
Quantity
Fields Required to Identify Additional Shift Start & End Times
If the Start Time 1 and End Time 1 fields have been mapped, the system will check if the Start Time 2 – Start Time 10 fields and the End Time 2 – End Time 10 fields have been mapped. Where any of those fields have been mapped, the system will check that corresponding start/end time field has also been mapped.
For example, if Start Time 2 has been mapped the system must check that End Time 2 has also been mapped. If End Time 10 has been mapped the system must check that Start Time 10 has also been mapped and so on.
Fields Required to Identify Unpaid Breaks
If the Start Time 1 and End Time 1 fields have been mapped, the system will check if any of the Break Start 1 – Break Start 10 and Break End 1 – Break End 10 fields have been mapped. If any one of these fields is mapped, the system will check that the corresponding start/end field has also been mapped.
For example, if Break Start 1 has been mapped the system must check that Break End 1 has also been mapped. If Break End 10 has been mapped the system must check that Break Start 10 has also been mapped and so on.
Data Type Validation
When you attempt to queue a Timesheet Import batch for processing the system also checks that the type of data that is provided in each mapped column within the import file matches the data type of FastTrack field to which that column is mapped. For example, if a column header is mapped to a field that must contain a date value, Timesheet Import will check that the corresponding column contains dates and not any other type of data.
Data Validation
If the import file format is correct and the minimum required fields have been mapped, the file will be queued for processing by the import process, at which point the following validation occurs:
job order matching
general validation.
If an item fails either of these validations, the entire corresponding timesheet will fail to be imported and an exception error message or messages will be written to an exception report. However, this will not prevent other timesheets in the same import file from being imported if they pass validation.
Job Order Matching
Timesheet Import will attempt to match each item in the import file to a unique job order that exists in the system. This is done in one of the following ways depending on which import file fields have been mapped:
by attempting to match on Job ID fields
by attempting to match on Client fields
by attempting to match on Candidate fields.
Matching by Job ID Fields
If any of the following Job ID fields are mapped, they will be used to match rows in the import file to a job order in the database based on the applicable item date:
Job Order ID
Job Alt No
Purchase Order No.
If two or more of the Job ID fields are mapped, only the first field will be used as shown in the order above. For example, if the Job Order ID field and the Job Alt No fields are provided, only the Job Order ID field will be used for matching purposes.
The table below lists the exception conditions that may apply when attempting to match timesheet data to an existing job order using the abovementioned fields. In each case, the table shows the error message that will be written to the exception report.
Condition | Exception Report Message |
A row in the import file could not be matched to an existing, valid job order by any of the job ID fields. A valid job order must have: | Job Order cannot be found. |
A valid job has not been found for the provided Purchase Order Number. | Job Order Cannot Be Found for the Submitted Purchase Order Number. |
A row in the import file specifies a purchase order number but there are multiple job orders with the same purchase order number and item date and therefore the row cannot be matched to a single job order. | Multiple matching job orders identified for specified date. |
The Job Order has been identified by one of the Job Identifiers, but the item date for this line does not fall between the job's start and end dates. | Specified Date Does Not Exist on the Job Order. |
The Job has been determined from the provided information, and a Candidate has been identified from the provided information, but this candidate is not the candidate on this order. | Specified Candidate Does Not Exist on the Job Order |
Matching by Client Fields
If none of the Job ID fields are mapped but any of the following Client fields are mapped, the system will use these Client fields to match rows in the import file to job orders in the database:
Cost Centre No
Cost Centre Code
Cost Centre Name
Client No
Client Alternate No.
If two or more of the Client fields are mapped, only the first field will be used as shown in the order above. For example, if the Cos t Centre Code field and the Client No fields are provided, only the Cost Centre Code field will be used for matching purposes.
The client ID will be determined from the client fields, as above. The candidate ID will be determined from these candidate fields: Candidate ID or Candidate Alt No or Candidate Full Name or (Candidate Firstname + Candidate Surname).
The Client fields listed above are used to identify the client who owns a job order. Once the client is identified, Timesheet Import will try to match a timesheet item to a job order by attempting to find a job order in which the specified candidate is placed for that client for the corresponding item date. However, if the candidate has been placed in multiple jobs for the same client for an overlapping period, the system may not be able to uniquely identify the relevant job order and the corresponding timesheet will fail to import. Therefore, it is recommended that, if possible, Job ID fields are included in the import file if there is a possibility that payees may have multiple placements with the same client for the same period.
The table below lists the exception conditions that may apply when attempting to match timesheet data to an existing job order using the abovementioned fields. In each case, the table shows the error message that will be written to the exception report..
Condition | Exception Report Message |
A job must be able to be identified based on the combination of client and candidate identifiers. A valid job order must have:
| Job Order Cannot Be Found. |
No matching candidate is found for the Candidate identifier. | Candidate Cannot be Found |
The candidate can be determined from the identifiers, but is not placed on a valid order for this client. | Candidate Not Placed on Valid Order for This Client and Date. |
Only one order can be identified – the resulting job number must be unique. | Multiple matching job orders identified for specified Candidate, Client and Date |
Matching by Candidate Fields
If neither the Job ID nor Client fields are mapped, the system will attempt to match rows in the import file to job orders in the database using the following Candidate fields:
Candidate ID
Candidate Alt No
Candidate Full Name
Candidate Firstname and Candidate Surname.
If two or more of the Candidate fields are mapped, only the first field will be used as shown in the order above. For example, if the Candidate Alt No field and the Candidate Full Name fields are provided, only the Candidate Alt No field will be used for matching purposes.
The Candidate fields listed above are used to identify the candidate linked to the payee who is placed in a job order. Once the candidate is identified, Timesheet Import will try to match a timesheet item to a job order by attempting to find a job order in which the specified candidate is placed for the corresponding item date. However, if the candidate has been placed in multiple jobs for the same or different clients for an overlapping period, the system may not be able to uniquely identify the relevant job order and the corresponding timesheet will fail to import. Therefore, it is recommended that, if possible, Job ID fields are included in the import file if there is a possibility that payees may have multiple placements with the same period.
The table below lists the exception conditions that may apply when attempting to match timesheet data to an existing job order using the abovementioned fields. In each case, the table shows the error message that will be written to the exception report.
Condition | Exception Report Message |
No matching candidate is found for the Candidate identifier. | Candidate Cannot Be Found. |
The candidate can be determined from the identifiers, but is not placed on a valid order for this date. | Candidate Not Placed on Valid Order For This Date. |
The candidate can be identified, but is placed on more than one valid order on this date. | Candidate Placed on Multiple Orders for this Date. |
More than one candidate can be found which matches both the candidate information used (i.e. candidate no, candidate alt no, etc.) and the date. | Multiple Candidates Identified. |
General Validation
If an item in the import file can be matched to a unique job order in the database, Timesheet Import will then perform general validation to ensure that the data provided in the item is correct.
The table below lists the conditions that may cause an item in the import file to fail the general validation. Except where stated otherwise, the entire timesheet to which an item in the import file corresponds will fail to import and an exception will be generated in the exception report.
Condition | Exception Report Message |
An attendance item in the import file has no import code/item name. | Attendance Item has no Import Code/Name. |
The import code/item name of an attendance item within the import file does not match the import code/item name of any existing attendance item within the system. | Attendance Item Import Code/Name does not exist in the system. |
A job order number has been provided in the import file but the corresponding job order does not exist in the system. | Job Order cannot be found. |
Data has already been keyed in and saved on the target timesheet in Time and Attendance. | The timesheet item has been keyed. |
An attendance item in the import file has a date that clashes with the date of an approved leave request item. On import the attendance item will overwrite the leave request item. However, unless a leave approver cancels the leave request, it will remain in the system and will be paid in a pay batch if not cancelled. This is a warning only and will not prevent the timesheet from being imported and created within FastTrack360. | Warning: an attendance item for <date |
An additional item in the import file has a date that clashes with the date of an approved leave request item. On import the additional item will overwrite the leave request item. However, unless a leave approver cancels the leave request, it will remain in the system and will be paid in a pay batch if not cancelled. This is a warning only and will not prevent the timesheet from being imported and created within FastTrack360. | Warning: an additional item for <date> overlaps an existing leave request item and will overwrite the existing leave request. |
The Job ID field has been mapped but a job order number has not been specified for the item. | The job order number is mandatory. |
The Timesheet Code Required option is enabled on the job order to which a timesheet belongs but a timesheet code is not specified for the timesheet in the import file. | Timesheet code is required. |
The timesheet code specified on a timesheet is not unique. | Timesheet code already exists. |
Standard rates have been applied to the job order to which the timesheet belongs but no standard rate items exist on the timesheet. | Timesheet is assigned standard rates but has no additional items specified. |
The start date of an attendance item on a timesheet falls outside of the date range of the timesheet. | Attendance item start date must fall within the timesheet header date range. |
An attendance item on a timesheet has a start or end time that overlaps another attendance item. | Attendance item overlaps another attendance item. |
An attendance item on a timesheet has a start or end time that overlaps an absence item | Attendance item overlaps an absence item. |
An attendance item on a timesheet has an attendance type that does not match any attendance type defined within the Maintain Attendance / Absence area in the Recruitment Maintenance. | Attendance Item Import Code/Name does not exist in the system |
An unpaid break during an attendance on a timesheet has a start time that falls outside of the attendance start or end time. | Attendance Item with work date |
An unpaid break during an attendance on a timesheet has an end time that falls outside of the attendance start or end time. | Attendance Item with work date |
An unpaid break during an attendance on a timesheet has a duration that is equal to or greater than the duration of the attendance itself. | Attendance Item with work date |
An unpaid break during an attendance on a timesheet has a start or end time that clashes with another unpaid break. | Attendance Item with work date |
The start or end time of a project item on a timesheet overlaps that of another project item. | Attendance Item with work date |
The Enforce Project Codes option has been enabled on the job order to which a timesheet belongs but a project item on the timesheet does not have a project code. | Project Item total time for attendance item with work date <date><time>must equal attendance time. |
The Enforce Project Code Selection option has been enabled on the job order to which a timesheet belongs but the project code of a project item on the timesheet does not match any of the project codes defined as valid for the job order. | Attendance Item with work date |
A timesheet includes an unpaid break during an absence and the start time of the unpaid break falls outside the start or end time of the absence. | Break item start time must fall within the absence item duration. |
A timesheet includes an unpaid break during an absence and the duration of the unpaid break is equal to or greater than the duration of the absence. | Break item end time must fall within the absence item duration. |
A timesheet includes an additional item that is not valid on the date on which it occurs on the timesheet. | Additional item not available for this timesheet date. |
A timesheet includes an additional item for which a pay rate must be specified (direct valuation item) on the timesheet but no pay rate has been specified. | Direct valuation item must have a pay rate. |
A timesheet includes an additional item for which a pay rate cannot be specified (non-direct valuation) on the timesheet but a pay rate has been specified. | Additional item rate cannot be supplied. |
An attendance item and an additional item is specified in a single row of the import file. | Attendance and manual/additional items cannot be specified in the same row. |
The Enforce Project Codes option has been enabled on the job order to which a timesheet belongs but a project item for an additional item on the timesheet does not have a project code. | Additional item Project Code required. |
The Enforce Project Code Selection option has been enabled on the job order to which a timesheet belongs but the project code of a project item for an additional item on the timesheet does not match any of the project codes defined as valid for the job order. | Additional item Project Code must exist in available pick list. |
A pay code name in the import file is not found in the system. | There is no Pay Code with this Name |
A pay code name in the import file is not unique. | There are multiple pay codes with this name in the system. |
A pay code import code has been provided in the import file but there are multiple pay codes with the same import code in the system. | Manual Item Import Code duplicates found. |
An unpaid break during an attendance on a timesheet has a start or end time that clashes with another unpaid break. | Attendance Item with work date |
A pay code import code in the import file is not found in the system. | Manual Item Import Code/Name does not exist in the system. |
A pay code name in the import file is not found in the system. | Manual Item Import Code/Name does not exist in the system. |
The Pay Code Import Code field is used in the field mappings but no item code is supplied in the import file. | Item code not supplied. |
Timesheet Exceptions
Timesheet exceptions occur when a row in the timesheet import file contains erroneous or incomplete data that cannot be used to create a timesheet or timesheet items. For example, if a row within the timesheet import file references a job order number that does not exist in the system then it will not be possible to create the corresponding timesheet in the Time and Attendance module because there will be no job to which the timesheet can be matched.
Rows that correspond to a timesheet that could not be imported due to an exception error are written to an Exception Report, which can be downloaded once the Timesheet Import batch has completed processing.
For more information, see Downloading the Import File, Exception Report & Validation Report and Data Validation.