Description
To support the ability to import an alternative timesheet number, a standard rate set or superseded rate values, the following Field Mapping changes have been made to Timesheet Import.
Field Mapping Changes to Support Standard Rate Sets and Superseded Values
In stage 2 of the import process ‘Import Settings’ page, we have made some additions to the existing Field Mappings ‘Map to Fields’:
New / Updated Map to Fields | Description | Data Format |
TS Alt No | Added to provide the ability to import an alternative timesheet id/number relating to the timesheet in the third party system. | Alphanumeric |
Bill Code Import Code | This value is mapped where import data is required to prompt the generation of a new Rate Stet in the related Standard Rate Job. Either Bill Code Import Code or Bill Code Name can be applied. The following items are mandatory to trigger the generation of a new rate set: Pay Code Name OR Pay Code Import Code; Pay Rate; Bill Code Name OR Bill Code Import Code; Bill Rate | Alphanumeric |
Bill Code Name | This value is mapped where import data is required to prompt the generation of a new Rate Stet in the related Standard Rate Job. Either Bill Code Import Code or Bill Code Name can be applied. The following items are mandatory to trigger the generation of a new rate set: Pay Code Name OR Pay Code Import Code; Pay Rate; Bill Code Name OR Bill Code Import Code; Bill Rate | Alphanumeric |
Bill Rate | This item is displayed below ‘Pay Rate' and is mapped where import data is required to either:
| Numeric |
Where you need to import a new Rate Set to a Standard Rate Job Order, the below provides an example of an import data sheet. In this example, the Overtime and Reimbursement items are not in the Standard Rate Job Order. Mandatory items to generate a new rate set are highlighted in yellow.
Candidate ID | Client No | Job ID | Item Date | Pay Code Name/Pay Code Import Name | Pay Quantity | Pay Rate | Bill Code/Bill Code Import Code | Bill Rate | Reimbursement Item | Reimbursement Net Value | Reimbursement GST/VAT Value | Alt TS Id |
100014020 | 300005146 | 200013720 | 14/06/2021 | Ordinary | 8 |
|
|
|
|
|
| 123456 |
100014020 | 300005146 | 200013720 | 14/06/2021 | Overtime | 2 | 60 | Overtime | 72 |
|
|
| 123456 |
100014020 | 300005146 | 200013720 | 14/06/2021 |
|
|
| Reimbursement | 100 | Reimbursement | 90.91 | 9.09 | 123456 |
For normal rates (not reimbursement) the following mapped values are required:
Pay Code/Pay Code Import Code
Pay Rate
Bill Code/Bill Code Import Code
Bill Rate
For Reimbursement items, we will only be able to add rates of Bill Type = Flat, and the Bill Rate will be applied to the rate. If you require any other Bill Type to be applied and the rate value to be dependent on this, Reimbursement Rates will need to be manually added to the Job Order. To use import to add Reimbursement items, the following is required:
Reimbursement Item/Reimbursement Import Code
Net
GST/VAT (if applicable to the reimbursement being claimed, not required for the new rate)
Bill Code/Bill Code Import Code
Bill Rate (please be aware this value will be applied to the Job’s new Rate Set as a FLAT RATE - if you wish to use the same Reimbursement item for future imports, provide the Bill Rate required for each reimbursement, and this value will be applied in the Interpreter as a superseded rate value)
Import Settings Changes to Support Grouping
To achieve the ability to bulk import timesheet iterations, for example an import file may contain more than one timesheet for the same job and week ending date - for example an original and its subsequent adjustment(s), in the Import Wizard Import Settings, a new setting is available and will need to be populated. This item, labelled ‘Grouping’, will enable the system to know the import field to targeted to determine the timesheet iteration grouping. In the import file, the Grouping value will need to be assigned to each data row for the same job and week ending date. Where multiple adjustment timesheets need to be generated, the system will use the Grouping value to determine the rows required for each timesheet and the order of the timesheets. The ability to sequence the order is very important as each iteration of the timesheet needs to target the timesheet it is adjusting.
Please note that where there is the possibility that the original timesheet and one or more subsequent adjustments could be imported in one file, then the ‘Adjustment Only’ setting must be true in the related Job Order, so that there is a zero result timesheet already in the system. Click here for more details.
In the example below, we have used the new Alternate Timesheet value as the grouping value, as this is a value that will be included in the import file and will be displayed next to each data row. Where there is data for more than one iteration of a timesheet for the same job and week ending date, the system will use Alt. TS Id to determine each timesheet and the order of each timesheet ie; Adjustment 1, Adjustment 2, based on the order of the group field. We have also mapped this field in the Field Mapping area, as it is a value that is available on the timesheet, which will be helpful if we need to cross check data with the VMS.
If your system does not provide a Timesheet Id, you can add a row to your data import and label it ‘Grouping’, then add values to determine each timesheet required ie; 1, 2, 3 etc. It is important to note that where multiple timesheets for same job and date are required, each row in the import must contain a grouping value.
The values provided for grouping must be able to be sequenced ie; 1, 2, 3, 4 and must be whole numbers. For example the system will not be able to sequence 1.2, 1.3, 1.4.
In the data sheet example below, ‘Alt TS No’ was used for Grouping. There are three iterations of the same timesheet, where the original timesheet is a Zero Result Adjustment Only Timesheet (click here for more details on this strategy).
Alt TS No: 4556 - First timesheet is for 8 hrs of Ordinary.
Alt TS No: 4559 - The first adjustment, changes this to 7 hrs of Ordinary 8 hrs was not correct.
Alt TS No: 4563 - In the third iteration, the hours have not changed, however a reimbursement item was missed in the previous iterations.
The adjustment strategy used below is the current recommended system strategy where the user provides an adjustment timesheet showing what the original should have looked like (positive strategy where the system balances the difference in the interpreted adjustment timesheet). If you want to use a debit/credit strategy, click here for more details.
Candidate Id | Job ID | Item Date | Alt TS No | Pay Code Name / Pay Code Import Code | Pay Quantity | Pay Rate | Reimbursement Item / Reimbursement Import Code | Reimbursement Net Value | Reimbursement GST/VAT Value | Adjustment Reason |
100000094 | 200005708 | 12/01/2024 | 4556 | Ordinary | 8 |
|
|
|
| Incorrect Timesheet |
100000094 | 200005708 | 12/01/2024 | 4559 | Ordinary | 7 |
|
|
|
| Incorrect Timesheet |
100000094 | 200005708 | 12/01/2024 | 4563 | Ordinary | 7 |
|
|
|
| Incorrect Timesheet |
100000094 | 200005708 | 12/01/2024 | 4563 |
|
|
| Reimbursement | 50 | 8 | Incorrect Timesheet |
Benefit
These enhancements are designed to support data import from third party system, where:
import data needs to inform FastTrack360 of the rate requirements to remove the need to update standard rates in a Job Order.
import data needs to generate multiple adjustment timesheets in one import for the same Job Order and same Week Ending Date to support different iterations of timesheets.
Configuration
Additional fields will need to be mapped in Time & Attendance > Timesheet Import Wizard > Field Mapping where new rates and or rate values are to be imported and where timesheet grouping is required for import of multiple adjustment timesheets for same Job Order and same Week Ending Date.