Using the TimeTrack Import Feature

By using the TimeTrack Import feature, field personnel can send data to the office via e-mail and have that information automatically entered into the system. Labor, expense, travel, and fixed asset transaction data can be imported.

You can generate the Account Index List. The report provides a list of general ledger accounts and an index number. This makes it possible to lookup an index number and use it in the import file to link an unbilled or FA-GL transaction to the general ledger account to be charged.

If Open Database Connectivity (ODBC) is used to import data directly to the WSWHSE table, a text note can be entered for a transaction. Two fields, Warehouse Note String and Warehouse Note String2 are used to enter a note. Each field holds a maximum of 255 characters.

Setting Up the Time Sheet Import File

If you are not using ODBC to import data directly to the WSWHSE table, the import file must be created as a tab-delimited or comma-delimited text file. Following is the order required for data entered in the import file. Each record in the file must be entered in the order listed. The text file containing the following fields, including blank fields, must be entered in the order listed, for the system to populate the WSWHSE table and make that data available to TimeTrack.

A field must be created for each item listed here. If a field is not used, create a column for it in the time sheet import file and leave it empty. The empty column will be skipped when the file is imported into TimeTrack. Additionally, the import text file should not include the field names. The file should contain just the data to be imported.

IMPORTANT

If you create the comma-delimited file with Excel (.csv) and you do not enter the batch ID and week-ending date, Excel does not add commas for those columns and you will have to add them in manually. In addition, if you have cost codes that precede with a 0 and start the cost code in Excel with an apostrophe, it will save with the preceding zero. If you open the .csv file back up in Excel it strips out the preceding zero so you will have to be careful about reopening a .csv import file if using Excel.

Order	Field	Description
1	Batch ID	A value is not required for this field. A batch ID can be entered in the Import Time Sheet window. However, whenever a batch ID is found in the import file, it is used. The ID entered in the import file overrides an ID entered in the Import Time Sheet window. The Batch ID is limited to 15 characters, alphanumeric characters only. If a batch ID is not entered, you must include two tab or comma characters that precede the Week Ending Date field.
2	Week Ending Date	Format must be MM/DD/YY or MMDDYY. A week-ending date can also be entered in the Import Time Sheet window. A date must be entered in the import file or the Import Time Sheet window. However, whenever a week-ending date is found in the import file, that date is used. The date entered in the import file overrides a date entered in the Import Time Sheet window.
3	Employee ID	From Microsoft Dynamics GP employee setup.
4	Signature Transaction Type	Job Cost, Service, Unbilled, Asset-JC, FA-GL, or WO-EQS. The first letter of each transaction type can be used.
5	Signature Cost Type	In the text file, use: 1 for labor 2 for expense 3 for travel 4 for fixed asset If the Signature Cost Type field is empty, the system enters a value of: 4 if the transaction type is asset-JC or FA-GL. 1 if the transaction type is Job Cost, Service, Unbilled, or WO-EQS.
6	Signature Job Number / Service Call ID/ WO Number
7	Day of the Week	First letter(s) or integer abbreviation.
8	Signature Cost Code	For labor cost types, codes 6 - 10 are valid. For expense cost types, codes 1, 2, 4, and 5 are valid. For travel cost types, code 5 is valid.
9	Equipment ID
10	Cost Code Number - CC Phase	Each segment of the cost code must be in a separate field. Insert an empty space for unused segments. A maximum of four segments are allowed.
11	Cost Code Number - CC_Step	Each segment of the cost code must be in a separate field. Insert an empty space for unused segments. A maximum of four segments are allowed.
12	Cost Code Number - CC_Detail	Each segment of the cost code must be in a separate field. Insert an empty space for unused segments. A maximum of four segments are allowed.
13	Cost Code Number - Component	Each segment of the cost code must be in a separate field. Insert an empty space for unused segments. A maximum of four segments are allowed.
14	Cost Element	For labor transactions, must be 1. For expense, travel, or fixed asset transactions, codes 2 - 9 are valid.
15	Pay Rate Amount / Hour	Pay Rate Amount / Hour (optional) Pay Rate Amount/ Unit (optional) In TimeTrack, the value defaults from the pay code if the field is empty.
16	UPR Trx Code (Pay Code)	Use a business expense pay type for expense or travel transactions. Do not use a pay code for asset-JC or FA-GL transactions.
17	Trx Hours / Units or Trx Qty	Trx Hours/Units (Actual Units multiplied by 100) Trx Qty for expense or travel – Do not multiply the units by 100.
18	Account Index	For unbilled and FA-GL transactions only. To use with unbilled transactions, the Create a Journal Entry for Unbilled Transactions option must be selected in the Setup Options window. To use with FA-GL transactions, the option, Integrate to Fixed Asset Management, must be selected in the Setup Options window. See Creating an Account Index List.
19	Asset ID	Enter a value for asset-JC or FA-GL transactions only. Leave the field empty for other transactions.
20	Transaction Description	Use up to 30 characters for a description. Leave the field empty for asset-JC or FA-GL transactions.
21	Department
22	Task ID	For WO-EQS transactions only. If a task ID is not entered, a tab or comma character must follow the Transaction Description field.
23	Union Code

Before Importing a Time Sheet File

Perform the following setup procedures before importing a timesheet file.

Step 1: Select TimeTrack Options

Select Microsoft Dynamics GP > Tools > Setup > TimeTrack > Setup Options.
Select the following options:
- Under Third Party, mark the Integrate to Fixed Asset Management checkbox if you want to import fixed asset transactions.
- Under Create a GL Journal Entry For, mark the Unbilled Transactions checkbox if you want to create a journal entry for unbilled transactions.
- Under Miscellaneous Items, mark the Create Expense/Travel Transactions checkbox. Fields for importing expense and travel data will be available in TimeTrack.

Step 2: Create a Batch File in Microsoft Dynamics GP

Select Transactions > Payroll > Batches. If a batch is not created, TimeTrack creates a single-use batch file. In the batch comment area the message, Created from TimeTrack import, is displayed.

Step 3: Select Payroll Options in Microsoft Dynamics GP

Select Setup > Payroll > Payroll > Options.
Under the Options section, mark the Edit Financial Fields checkbox and the Display Pay Rate on Trx checkbox. When these options are marked, a pay rate amount can be imported. If the options are not marked, the value for the pay code is used as the default value.

Importing a Time Sheet File

Once you have received and saved an appropriately formatted text file containing TimeTrack data from the field, you can import that data into your system through the TimeTrack Import window.

Select Microsoft Dynamics GP > Tools > Integrate > TimeTrack > Import.
Enter a path to the text file or select the folder button to locate the path.
Select Next > to open the Import Time Sheet window.
In the Import Time Sheet window, if you are not using ODBC to import data directly to the WSWHSE table, select Update Warehouse. The file is imported into a TimeTrack data table. If you are using ODBC to import data directly to the WSWHSE table, skip this step.
Select the Default Batch ID to use if the batch ID is not imported with the time sheet file. If you are using the Default Date From Batch option, the following is true:
- The Default Week Ending Date field will be locked. If a week-ending date is not imported with the time sheet file, the date will be that of the batch entered in the Import Time Sheet window.
- If the batch entered does not have a week-ending date, the transactions in the batch will not be imported.
- If the week-ending date of the batch entered does not fall on the default week-ending day specified in the Setup Options window, the transactions in the batch will not be imported.
- If the week-ending date of the transaction in the import file is different than the batch week-ending date, the batch date will be used for the transaction. You cannot import transactions into a batch that has been approved for committing or has been marked as single-user edit
Select the Default Week Ending Date to use when the date is not imported with the time sheet file. The date will default based on setup options for the week-ending date.
Mark the Import File Format: Tab Delimited or Comma Delimited. The default value is Tab Delimited.
Select Build Time Sheets. The transactions are created in TimeTrack from the time sheet import file data.
If errors are found, select the Print Audit button to generate an Exceptions report and view the error descriptions. For example, if there are 10 transactions, six without errors and four with errors, the six that are fine will be imported; the remaining four will appear on the Exceptions report. Let's say that, out of the four transactions with errors, two contain valid data in the import file, but additional changes need to be made to the system (for example, a job or cost code needs to be activated) - and the remaining two contain errors in the import file. After you fix all the errors (both within the system and in the import file), you must re-import the time sheet (before re-building) because changes have been made to the import file. In addition, before you re-build the time sheets, you must remove the transactions from the import file that have already been built successfully (in our example above, the six transactions). Otherwise, you will get duplicate transactions. An alternative is to remove all the TimeTrack transactions from the import file by deleting the batch. The TimeTrack transactions will not display in the Payroll Batch Entry window.
After the transactions have been removed, select Build Time Sheets again to create the transactions in TimeTrack.
Commit the transactions. See Committing Time Card Transactions.
- Importing Hours for Certified Payroll Jobs
- If 40 hours are imported for a certified payroll job, TimeTrack distributes eight hours to each day of that week, Monday through Friday. If less than 40 hours are imported, the hours will not be distributed to multiple days. You must distribute the hours to multiple days using the Time Card Entry window. You cannot import more than 40 hours per transaction.

Creating an Account Index List

To determine the index number to enter in the time sheet import file for a transaction that is charged to a general ledger account, generate the Account Index List.

Select Reports > TimeTrack > Account Index List.
Select a destination for the report and select OK in the Report Destination window. The length of the report depends on the number of accounts set up in Microsoft Dynamics GP.