Employees Table Formats (EM)
Follow DPS specifications for preparing a file containing employee data.
If you use multiple companies the Home Company and Employee Company fields display and are updated automatically.
| Field Name | Data Type | Length | Notes |
|---|---|---|---|
| Address1 | varchar | 50 | First line of the employee's address |
| Address2 | varchar | 50 | Second line of the employee's address |
| Address3 | varchar | 50 | Third line of the employee's address |
| ADPCompanyCode | varchar | 3 | Employee's ADP company code |
| ADPFileNumber | varchar | 6 | Employee's ADP file number |
| ADPRateCode | varchar | 1 | Employee's ADP rate code |
| AvailableForCRM | varchar | 1 | Variable indicating whether the record is available to CRM users; valid options are Y (Yes) or N (No); default is Y |
| BillingCategory | smallint | 2 | Employee's default category when using the By category labor billing method; must exist in BTLaborCats (Category/Description) |
| BillingPool | smallint | 2 | Reserved for future use |
| ChangeDefaultLC | varchar | 1 | Variable indicating whether the employee can change the default labor code in timekeeper; valid options are Y (Yes) or N (No); default is N |
| CheckHours | varchar | 1 | Variable indicating DPS's response when the hours entered on a submitted timesheet do not match the hours expected; valid options are Global (System-wide setting), Error (User receives an error message and is not allowed to proceed), Warning (User receives a warning message and is allowed to proceed), or None (No message) |
| City | varchar | 30 | Employee's city |
| ConsultantInd | varchar | 1 | Variable indicating whether the employee is a consultant; valid options are Y (Yes) or N (No); if Y must link to Vendor; default is N |
| Country | varchar | 40 | Employee's country; must exist in CFGCountry |
| DefaultLC1 | varchar | 14 | Level 1 default labor code for timekeeper time sheets; must exist in CFGLCodes |
| DefaultLC2 | varchar | 14 | Level 2 default labor code for timekeeper time sheets; must exist in CFGLCodes |
| DefaultLC3 | varchar | 14 | Level 3 default labor code for timekeeper time sheets; must exist in CFGLCodes |
| DefaultLC4 | varchar | 14 | Level 4 default labor code for timekeeper time sheets; must exist in CFGLCodes |
| DefaultLC5 | varchar | 14 | Level 5 default labor code for timekeeper time sheets; must exist in CFGLCodes |
| EKAdminApproval | varchar | 1 | Variable indicating whether the employee can approve the expense reports of other employees in the same Expense group; valid options are Y (Yes) or N (No); default is N |
| EKAdminEdit | varchar | 1 | Variable indicating whether the employee can edit the expense reports of other employees in the same Expense group; valid options are Y (Yes) or N (No); default is N |
| EKAdminLevel | decimal | 1 | Employee's Expense Administration level; valid options are 0 (Staff), 1 (Group), or 2 (System) |
| EKGroup | decimal | 2 | Employee's Expense group; null or must exist in CFGEKEmployeeGroup (EmployeeGroup/Description) |
| varchar | 50 | Employee's email address | |
| Employee | varchar | 20 | Employee number; required field; primary key |
| EmployeePhoto | varchar | 255 | This column is not used |
| ExportInd | varchar | 1 | Reserved for future use |
| Fax | varchar | 24 | Employee's fax number |
| First Name | varchar | 25 | Employee's first name |
| HireDate | datetime | 8 | Employee's hire date |
| HomePhone | varchar | 24 | Employee's home telephone number |
| HoursPerDay | decimal | 9 | Number of hours the employee works per day; this field is used by Time to check the hours entered on the employee's timesheet against the expected hours for the employee |
| JCOvtPct | decimal | 9 | Overtime percentage |
| JCSpecialOvtPct | decimal | 9 | Employee's job costing special overtime percentage |
| JobCostRate | decimal | 9 | Employee's job cost rate |
| JobCostType | varchar | 1 | Variable indicating the employee's job cost type; valid options are H (Hourly) or S (Salary) |
| LastName | varchar | 30 | Employee's last name; required field |
| Locale | varchar | 2 | Internal reference number for the payroll tax locale, when LocaleMethod is Follow Project Locale |
| LocaleMethod | varchar | 1 | Variable indicating the method for specifying the payroll tax locale; valid options are Follow Project Locale, % of Wages, or None |
| Memo | text | unlimited | Memo field for comments on the employee |
| MiddleName | varchar | 30 | Employee's middle name |
| MobilePhone | varchar | 24 | Employee's cell phone number |
| Org | varchar | 14 | Employee's organization; required field if ConsultantInd = N (Not Consultant); must exist in Organization and approved for use in Accounting app=Y |
| OtherPay | numeric | 9 | First Other Pay amount on the employee's Payroll tab |
| PayOvtPct | decimal | 9 | Employee's overtime percentage rate |
| PayRate | decimal | 9 | Employee's pay rate |
| PaySpecialOvtPct | decimal | 9 | Employee's special overtime percentage rate |
| PayType | varchar | 1 | Employee's pay type; valid options are H (Hourly) or S (Salary) |
| PreferredName | varchar | 60 | Employee's preferred name, as entered on the general tab of the Employees hub |
| ProfessionalSuffix | varchar | 25 | Professional suffix for the employee's name that indicates educational degrees or professional licenses or accreditation. It can be a single item (for example, PhD) or a combination of two or more (for example, CPA, CMA, MBA). |
| ProvBillOTPct | decimal | 9 | Provisional billing rate for unposted overtime on interactive project reports |
| ProvBillRate | decimal | 9 | Provisional billing rate for unposted time on interactive project reports |
| ProvBillSpecialOTPct | decimal | 9 | Employee's provisional billing percentage for unposted special overtime in interactive project reports |
| ProvCostOTPct | decimal | 9 | Provisional cost rate for unposted overtime on interactive project reports |
| ProvCostRate | decimal | 9 | Provisional cost rate for unposted time on interactive project report |
| ProvCostSpecialOTPct | decimal | 9 | Employee's provisional cost percentage for unposted special overtime in interactive project reports |
| RaiseDate | datetime | 8 | Date of the employee's next raise |
| ReadyForApproval | varchar | 1 | Variable indicating whether the record is available to Accounting users; valid options are Y (Yes) or N (No) |
| ReadyForProcessing | varchar | 1 | Variable indicating whether the record is approved for use in transaction processing; valid options are Y (Yes) or N (No) |
| Region | varchar | 10 | Not used |
| Salutation | varchar | 5 | Prefix to use with this employee's name; null or must exist in CFGPrefix |
| SSN | varchar | 11 | Employee's social security number |
| State | varchar | 10 | Employee's state.
The state must exist in CFGStates. If you import both state and country, that combination must already be a valid combination in DPS. If you import a state but not a country, the state must be a valid state for the United States. |
| Status | varchar | 1 | Variable indicating the employee's status; required field; valid options are A (Active), I (Inactive), or T (Terminated); must exist in CFGEmployeeStatus (Status/Label) |
| Suffix | varchar | 20 | Suffix to use with this employee's name; null or must exist in CFGSuffix |
| Supervisor | varchar | 20 | Employee's supervisor; must exist in EM |
| TargetRatio | decimal | 9 | Percentage of the employee's hours they are expected to charge to a project |
| TerminationDate | datetime | 8 | Date when the employee was terminated |
| Title | varchar | 50 | Employee's job title |
| TKGroup | varchar | 2 | Employee's Time group; nulll or must exist in CFGTKEmployeeGroup (EmployeeGroup/Description) |
| TKAdminLevel | varchar | 1 | Employee's Time Administration level; valid options are 0 (Staff), 1 (Group), or 2 (System) |
| TKAdminEdit | varchar | 1 | Variable indicating whether the employee can edit the timesheets of other users in the same Time group; valid options are Y (Yes) or N (No); default is N |
| TKAdminApproval | decimal | 1 | Variable indicating whether the employee can approve the timesheets of other users in the same Time group; valid options are Y (Yes) or N (No); default is N |
| Type | varchar | 1 | Variable indicating the employee's type; required field; valid options are P (Principal) or E (Employee); must exist in CFGEmployeeType (Type/Label) |
| UseTotalHrsAsStd | varchar | 1 | Variable indicating whether to use the total hours worked by this employee as the standard for Time Analysis reporting; valid options are Y (Yes) or N (No); default is N |
| Vendor | varchar | 20 | If ConsultantInd in this table is Yes, the number of the vendor/consultant; required field when ConsultantInd = Y; must exist in VE |
| WorkPhone | varchar | 24 | Employee's work telephone number |
| WorkPhoneExt | varchar | 8 | Employee's work telephone number extension |
| YearsOtherFirms | smallint | 2 | The number of years this employee worked for other enterprise |
| ZIP | varchar | 10 | Employee's zip code |