Employees Table Formats (EM)
Follow Vantagepoint 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 |
|---|---|---|---|
| ADA | varchar | ||
| Address1 | nvarchar | 50 | First line of the employee's address |
| Address2 | nvarchar | 50 | Second line of the employee's address |
| Address3 | nvarchar | 50 | Third line of the employee's address |
| ADPCompanyCode | nvarchar | 3 | Employee's ADP company code |
| ADPFileNumber | nvarchar | 6 | Employee's ADP file number |
| ADPRateCode | nvarchar | 1 | Employee's ADP rate code |
| AlienNumber | nvarchar | ||
| AllowBreakTime | varchar | ||
| AllowChargeUnits | varchar | ||
| 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 |
| BirthDate | datetime | ||
| 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 Vantagepoint'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) |
| CitizenshipStatus | varchar | ||
| City | varchar | 30 | Employee's city |
| Class | nvarchar | ||
| ClientID | varchar | ||
| ClientVendorInd | varchar | ||
| ClieOp | varchar | ||
| ClieOpAccount | nvarchar | ||
| ClieOpAccountType | varchar | ||
| ClieOpTransactionType | nvarchar | ||
| 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 |
| CostRateMeth | smallint | ||
| CostRateTableNo | int | ||
| Country | varchar | 40 | Employee's country; must exist in CFGCountry |
| DefaultBreakEndDateTime | datetime | ||
| DefaultBreakStartDateTime | datetime | ||
| 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 |
| DefaultTaxLocation | nvarchar | 50 | |
| Disabled | varchar | ||
| DisableTSRevAudit | varchar | ||
| 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 | |
| EmailExpenseRemittance | varchar | ||
| EmailPayrollRemittance | varchar | ||
| Employee | nvarchar | 20 | Employee number; required field; primary key |
| EmployeeCompany | nvarchar | ||
| EmployeePhoto | nvarchar | 255 | This column is not used |
| Ethnicity | nvarchar | ||
| ExitInterviewer | nvarchar | ||
| ExportInd | varchar | 1 | Reserved for future use |
| Fax | varchar | 24 | Employee's fax number |
| FaxFormat | nvarchar | ||
| FirstName | varchar | 25 | Employee's first name |
| FLSAStatus | nvarchar | ||
| Gender | varchar | ||
| GeographicCode | nvarchar | ||
| HireDate | datetime | 8 | Employee's hire date |
| HomeCompany | nvarchar | ||
| HomeEmail | nvarchar | ||
| HomePhone | nvarchar | 24 | Employee's home telephone number |
| HomePhoneFormat | nvarchar | ||
| 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 |
| I9Verification | varchar | ||
| IncludeLocalJurisOnly | varchar | ||
| 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) |
| JobLevel | nvarchar | ||
| KonaAccessToken | nvarchar | ||
| KonaAccessToken | nvarchar | ||
| KonaRefreshToken | nvarchar | ||
| KonaUserID | int | ||
| KonaUsername | nvarchar | ||
| Language | varchar | ||
| LastDayPaid | datetime | ||
| LastDayWorked | datetime | ||
| 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 |
| Location | nvarchar | ||
| MailAddress1 | nvarchar | ||
| MailAddress2 | nvarchar | ||
| MailAddress3 | nvarchar | ||
| MailAddressSameAsHome | varchar | ||
| MailCity | nvarchar | ||
| MailCounty | nvarchar | ||
| MailState | nvarchar | ||
| MailZIP | nvarchar | ||
| MaritalStatus | varchar | ||
| Memo | text | unlimited | Memo field for comments on the employee |
| MiddleName | nvarchar | 30 | Employee's middle name |
| MobilePhone | nvarchar | 24 | Employee's cell phone number |
| MobilePhoneFormat | nvarchar | ||
| OccupationalCode | nvarchar | ||
| 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 |
| OtherPay2 | decimal | ||
| OtherPay3 | decimal | ||
| OtherPay4 | decimal | ||
| OtherPay5 | decimal | ||
| PassportCountry | nvarchar | ||
| PassportExpDate | datetime | ||
| PassportNumber | nvarchar | ||
| PaychexCode1 | nvarchar | ||
| PaychexCode2 | nvarchar | ||
| PaychexCode3 | nvarchar | ||
| PaychexRateNumber | varchar | ||
| PayOvtPct | decimal | 9 | Employee's overtime percentage rate |
| PayRate | decimal | 9 | Employee's pay rate |
| PayRateMeth | smallint | ||
| PayRateTableNo | int | ||
| PaySpecialOvtPct | decimal | 9 | Employee's special overtime percentage rate |
| PayType | varchar | 1 | Employee's pay type; valid options are H (Hourly) or S (Salary) |
| PIMID | varchar | ||
| PreferredName | varchar | 60 | Employee's preferred name, as entered on the general tab of the Employees hub |
| PrimaryContact | nvarchar | ||
| PrimaryContactPhone | nvarchar | ||
| PrimaryContactPhoneFormat | nvarchar | ||
| PrimaryContactRelationship | nvarchar | ||
| PriorYearsFirm | smallint | ||
| 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 |
| QBOAddressID | nvarchar | ||
| QBOID | nvarchar | ||
| QBOLastUpdated | datetime | ||
| QBOVendorID | nvarchar | ||
| 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 |
| RehireDate | datetime | ||
| RehireEligible | varchar | ||
| RequireStartEndTime | varchar | ||
| RetirementPlan | varchar | ||
| Salutation | varchar | 5 | Prefix to use with this employee's name; null or must exist in CFGPrefix |
| SecondaryContact | nvarchar | ||
| SecondaryContactPhone | nvarchar | ||
| SecondaryContactPhoneFormat | nvarchar | ||
| SecondaryContactRelationship | nvarchar | ||
| SeniorityDate | datetime | ||
| SEPABIC | nvarchar | ||
| SEPAIBAN | nvarchar | ||
| SeparationDate | datetime | ||
| SOCCode | nvarchar | ||
| 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 Vantagepoint. 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) |
| StatutoryEmployee | varchar | ||
| 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 |
| TalentModDate | datetime | ||
| TalentUserID | nvarchar | ||
| 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 |
| TerminationReason | nvarchar | ||
| TerminationType | nvarchar | ||
| ThirdPartySickPay | varchar | ||
| Title | varchar | 50 | Employee's job title |
| 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 |
| TKAdminLevel | varchar | 1 | Employee's Time Administration level; valid options are 0 (Staff), 1 (Group), or 2 (System) |
| TKGroup | varchar | 2 | Employee's Time group; nulll or must exist in CFGTKEmployeeGroup (EmployeeGroup/Description) |
| TLInternalKey | nvarchar | ||
| TLSyncModDate | datetime | ||
| 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) |
| UIPaymentMethod | varchar | ||
| 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 |
| UtilizationRatio | decimal | ||
| 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 |
| VetStatus | varchar | ||
| VetType | nvarchar | ||
| VisaCountry | nvarchar | ||
| VisaExpDate | datetime | ||
| VisaNumber | nvarchar | ||
| VisaType | nvarchar | ||
| WorkPhone | varchar | 24 | Employee's work telephone number |
| WorkPhoneFormat | nvarchar | ||
| WorkSchedule | nvarchar | ||
| YearsOtherFirms | smallint | 2 | The number of years this employee worked for other enterprise |
| ZIP | varchar | 10 | Employee's zip code |