Employee Fields
The following field information is intended to assist those who design reports for Sage 50 using Crystal Reports.
Filename: EMPLOYEE.DAT
EMPLOYEE.DAT is the employee file. It contains a record (row) for each employee. Most of the information below is entered or displayed in Sage 50's Maintain Employees/Sales Reps window.
- To add Sage 50 employee fields to your Crystal reports, select EMPLOYEE.DAT.
There are additional files that contain payroll information for employees. These are EARNSUMM.DAT, EMPAYINF.DAT, EMPPRFLD.DAT, ESPRFLD.DAT, and ESWAGE.DAT.
Indexes and Their Uses
The EMPLOYEE.DAT file has 16 keys, as follows:
0 = ByEmpID. This field is unique but does change.
1 = ByName
2 = ByInactiveID - Active/Inactive and then by EmployeeID
3 = BySSNumber
4 = ByRecordNumber. Unique and doesn't change. Usually used to link to other tables.
5 = By GUID
6 = ZIP code
7 = By Inactive and then by Employee Name
8 = By Employee Type
9 = By Inactive and then by Employee Type
10 = By Employee Type, case sensitive
11 = By CustomField1
12 = By CustomField2
13 = By CustomField3
14 = By CustomField4
15 = By CustomField5
Fields
The following table shows the fields available in the EMPLOYEE.DAT file and includes the type and size in number of bytes of each field.
|
Field Name |
Type |
Size |
Comment |
|---|---|---|---|
|
EmployeeID |
ZString |
20 |
Employee ID entered in Maintain Employees/Sales Rep window. This field is unique in the table and never changes. |
|
EmployeeRecordNumber |
Integer |
Used as a table-linking field. |
|
|
EmployeeGUID |
UBinary |
GUID (Globally Unique Identifier) used for linking the Employee table. |
|
|
EmployeeName |
ZString |
39 |
Employee name entered on the Maintain Employees/Sales Reps window. This is the employee's full name stored as one string; later in this record, the name is also stored as First, Middle, and Last name. |
|
Address1 |
ZString |
30 |
Address line 1 (street address) entered on the General tab of the Maintain Employees/Sales Reps window. |
|
Address2 |
ZString |
30 |
Address line 2 (street address) entered on the General tab of the Maintain Employees/Sales Reps window. |
|
City |
ZString |
20 |
City for employee address. |
|
State |
ZString |
2 |
2-character state abbreviation for employee address. |
|
ZIP |
ZString |
12 |
ZIP code for employee address. |
|
Country |
ZString |
15 |
Country for employee address. |
|
EmployeeSSN |
ZString |
11 |
Employee's social security number entered on the General tab of the Maintain Employees/Sales Reps window. |
|
EmployeeIsInactive |
Integer |
1 |
The Inactive check box in the Maintain Employees/Sales Reps window. Valid choices include: 1 (True) = if the employee record is inactive. 0 (False) = employee record is active. |
|
Type |
ZString |
8 |
Employee type entered on the General tab of the Maintain Employees/Sales Reps window. |
|
CustomField1 |
ZString |
40 |
|
|
CustomField2 |
ZString |
40 |
|
|
CustomField3 |
ZString |
40 |
|
|
CustomField4 |
ZString |
40 |
|
|
CustomField5 |
ZString |
40 |
|
|
Retirement |
Logical |
True => if the Retirement check box is selected on the General tab of the Maintain Employees window. This is used on employee W-2 forms. |
|
|
PayFrequency |
Integer |
Pay frequency selected on the Pay Info tab of the Maintain Employees/Sales Reps window. Valid choices include: 0 => Weekly 1 => BiWeekly 2 => SemiMonthly 3 => Monthly 4 => BiMonthly (Currently not supported or used in Sage 50) 5 => Quarterly (Currently not supported or used in Sage 50) 6 => SemiAnnually (Currently not supported or used in Sage 50) 7 => Annually |
|
|
Statutory |
Integer |
1 |
1 (True) => if the Statutory check box is selected on the General tab of the Maintain Employees window. This is used on employee W-2 forms to indicate deferred taxable income (such as 401(k)). |
|
TaxState |
ZString |
2 |
2-character state abbreviation selected in the State/Locality column of the Withholding Information Table on the Withholding Info tab of the Maintain Employees/Sales Reps window. This identifies the single state that the employee gets taxed in. Sage 50 currently supports getting taxed in only one state. |
|
TaxLocality |
ZString |
8 |
8-character locality entered in the State/Locality column of the Withholding Information Table on the Withholding Info tab of the Maintain Employees/Sales Reps window (if applicable). |
|
StateFilingStatus |
Integer |
State filing status selected for the employee in the Withholding Information Table on the Withholding Info tab of the Maintain Employees/Sales Reps window. Valid values include: 0 = Single 1 = Married 2 = HeadOfHousehold 3 = Any 4 = Joint 5 = Separate 6 = Widow 7 = Joint2Income 8 = Special_A 9 = Special_B 10 = Special_C 11 = Special_D 12 = Special_E 13 = Special_F 14 = Special_G 15 = Special_H |
|
|
LocalFilingStatus |
Integer |
Local filing status selected for the employee in the Withholding Information Table on the Withholding Info tab of the Maintain Employees/Sales Reps window. Valid values include: 0 = Single 1 = Married 2 = HeadOfHousehold 3 = Any 4 = Joint 5 = Separate 6 = Widow 7 = Joint2Income 8 = Special_A 9 = Special_B 10 = Special_C 11 = Special_D 12 = Special_E 13 = Special_F 14 = Special_G 15 = Special_H |
|
|
FedFilingStatus |
Integer |
Federal filing status selected for the employee in the Withholding Information Table on the Withholding Info tab of the Maintain Employees/Sales Reps window. Valid values include: 0 = Single 1 = Married |
|
|
FedAllowances |
Integer |
Number of federal allowances entered for the employee in the Withholding Information Table on the Withholding Info tab of the Maintain Employees/Sales Reps window. |
|
|
StateAllowances |
Integer |
Number of state allowances entered for the employee in the Withholding Information Table on the Withholding Info tab of the Maintain Employees/Sales Reps window. |
|
|
LocalAllowances |
Integer |
Number of local allowances entered for the employee in the Withholding Information Table on the Withholding Info tab of the Maintain Employees/Sales Reps window (if applicable) |
|
|
ExtraFIT |
Decimal |
Amount of additional federal withholding entered for the employee in the Withholding Information Table on the Withholding Info tab of the Maintain Employees/Sales Reps window. |
|
|
ExtraSIT |
Decimal |
Amount of additional state withholding entered for the employee in the Withholding Information Table on the Withholding Info tab of the Maintain Employees/Sales Reps window. |
|
|
ExtraLIT |
Decimal |
Amount of additional local withholding entered for the employee in the Withholding Information Table on the Withholding Info tab of the Maintain Employees/Sales Reps window (if applicable). |
|
|
Integer |
Identifies which type of pay rate table (salaried or hourly) should display on the Pay Info tab of Maintain Employees/.Sales Reps window This option depends on the value entered in the HourlyEmpType field (see below). Valid values for PayStatus include: 0 = Salaried 1 = Hourly |
||
|
HireDate |
Date |
Hired date entered on the Additional Info tab of the Maintain Employees/Sales Reps window. |
|
|
TerminationDate |
Date |
Terminated date entered on the Additional Info tab of the Maintain Employees/Sales Reps window. |
|
|
PhoneNumber |
ZString |
20 |
Home phone entered on the General tab of the Maintain Employees/Sales Reps window. |
|
IsSalesRep |
Integer |
1 |
1 => if Sales Rep or Both payroll option is selected in the Maintain Employees/Sales Reps window for this employee. At least one of these 2 must be true. However, both of them can also be true. |
|
IsEmployee |
Integer |
1 |
1 => if Employee or Both payroll option is selected in the Maintain Employees/Sales Reps window for this employee. |
|
Integer |
Pay method entered on the pay info tab of the Maintain Employees/Sales Reps window. Valid values include: -1 = Salary 1 = Hourly - Hours Per Pay Period 2 = Hourly - Time Ticket Hours (Sage 50 Premium Accounting and higher) 3 = Hourly -Electronic Time (not currently available or supported in Sage 50) If the employee is hourly, then use this field to find out how to calculate their hours. |
||
|
DefaultHour |
Decimal |
If HourlyEmpType => 1 (above), then this field displays the number of default hours worked per pay period. (For example, biweekly might display 80 hours per pay period.) |
|
|
BillingRate |
Decimal |
If HourlyEmpType => 2 (above), this is the employee billing rate at which time tickets get charged to customers. (Sage 50 Premium Accounting and higher) |
|
|
eTimeEmpID |
ZString |
Not currently used. |
|
|
eTimeBadgeNum |
ZString |
Not currently used. |
|
|
eTimeDept |
ZString |
Not currently used. |
|
|
eTimePayClass |
ZString |
Not currently used. |
|
|
eTimeHomeTimeClock |
ZString |
Not currently used. |
|
|
eTimeProfile |
ZString |
Not currently used. |
|
|
SpecialAllow1 |
Integer |
Number of Special 1 payroll field allowances entered for the employee. Note: About the next five special allowances fields: These next five numbers map directly to the last three rows in the Withholding Information table on the Withholding Info tab of Maintain Employees/Sales Reps window. |
|
|
SpecialAllow2 |
Integer |
Number of Special 2 payroll field allowances entered for the employee. |
|
|
Extra401k |
Decimal |
401(k) percentage employee contributes to company 401(k) plan each pay period (for example, 4.0 = 4%). |
|
|
ExtraSpecial1 |
Decimal |
Amount of additional withholding for Special 1 payroll field entered for the employee. |
|
|
ExtraSpecial2 |
Decimal |
Amount of additional withholding for Special 2 payroll field entered for the employee. |
|
|
|
ZString |
64 |
Email address for this employee. |
|
DirectDepositActive |
Integer |
Indicates whether direct deposit has been turned on in Maintain Company window. Valid choices include: True = direct deposit turned on (inactive) for the company False = direct deposit not turned on for the company. |
|
|
DirDepAcntType1 |
Integer |
Type of the first direct deposit account for the company. |
|
|
DirDepAcntBankAcnt1 |
ZString |
17 |
Account number of the first direct deposit account for the company. |
|
DirDepRoutingNum1 |
ZString |
9 |
Routing number of the first direct deposit account for the company. |
|
DirDepPreNoteStat1 |
Integer |
Status of the pre-note for the first direct deposit account for the company. |
|
|
DirDepPreNoteGUID1 |
UBinary |
This is used to link direct deposit information to other tables. |
|
|
PreNoteStatDate1 |
Date |
Date of the pre-note for the first direct deposit account for the company. |
|
|
AmtPercentDist1 |
Decimal |
Percentage of total distribution allocated to the first direct account for the company. |
|
|
AllocationMethod1 |
Integer |
Method used to allocate funds to the first direct deposit account for the company. |
|
|
DirDepAcntType2 |
Integer |
Type of the second direct deposit account for the company. |
|
|
DirDepBankAcnt2 |
ZString |
17 |
Account number of the second direct deposit account for the company. |
|
DirDepRoutingNum2 |
ZString |
9 |
Routing number of the second direct deposit account for the company. |
|
DirDepPreNoteStat2 |
Integer |
Status of the pre-note for the second direct deposit account for the company. |
|
|
DepPreNoteGUID2 |
UBinary |
This is used to link direct deposit information to other tables. |
|
|
PreNoteStatDate2 |
Date |
Date of the pre-note for the second direct deposit account for the company. |
|
|
AmtPercentDist2 |
Decimal |
Percentage of total distribution allocated to the second direct account for the company. |
|
|
AllocationMethod2 |
Integer |
Method used to allocate funds to the second direct deposit account for the company. |
|
|
DirDepAcntType3 |
Integer |
Type of the third direct deposit account for the company. |
|
|
DirDepBankAcnt3 |
ZString |
17 |
Account number of the third direct deposit account for the company. |
|
DirDepRoutingNum3 |
ZString |
9 |
Routing number of the third direct deposit account for the company. |
|
DepPreNoteStat3 |
Integer |
Status of the pre-note for the third direct deposit account for the company. |
|
|
DepPreNoteGUID3 |
UBinary |
This is used to link direct deposit information to other tables. |
|
|
PreNoteStatDate3 |
Date |
Date of the pre-note for the third direct deposit account for the company. |
|
|
AmtPercentDist3 |
Decimal |
Percentage of total distribution allocated to the third direct account for the company. |
|
|
AllocationMethod3 |
Integer |
Method used to allocate funds to the third direct deposit account for the company. |
|
|
DirDepAcntType4 |
Integer |
Type of the fourth direct deposit account for the company. |
|
|
DirDepBankAcnt4 |
ZString |
17 |
Account number of the fourth direct deposit account for the company. |
|
DirDepRoutingNum4 |
ZString |
9 |
Routing number of the fourth direct deposit account for the company. |
|
DirDepPreNoteStat4 |
Integer |
Status of the pre-note for the fourth direct deposit account for the company. |
|
|
DepPreNoteGUID4 |
UBinary |
This is used to link direct deposit information to other tables |
|
|
PreNoteStatDate4 |
Date |
Date of the pre-note for the fourth direct deposit account for the company. |
|
|
AmtPercentDist4 |
Decimal |
Percentage of total distribution allocated to the fourth direct account for the company. |
|
|
AllocationMethod4 |
Integer |
Method used to allocate funds to the fourth direct deposit account for the company. |
|
|
Employee_LastName |
ZString |
20 |
Employee's last name. |
|
Employee_FirstName |
ZString |
15 |
Employee's first name. |
|
Employee_MiddleInit |
ZString |
1 |
Employee's middle initial. |
|
LastUpdateCounter |
Integer |
This increases incrementally when the employee record is changed. |
|
|
Suffix |
ZString |
4 |
|
|
BirthDate |
Date |
||
|
Gender |
ZString |
1 |
|
|
IsMedInsAvail |
ZString |
1 |
|
|
RehireDate |
Date |
||
|
IsW2ViaEmail |
ZString |
||
|
ExtraSimple |
Decimal |
8 |
This and the following "Extra" fields are for catchup contributions. |
|
ExtraR401K |
Decimal |
||
|
ExtraIRA |
Decimal |
||
|
ExtraRIRA |
Decimal |
||
|
Extra403B |
Decimal |
||
|
Extra457B |
Decimal |
||
|
Extra408K |
Decimal |
||
|
Uses401K |
Boolean |
1 |
|
|
UsesSIMPLE |
Boolean |
||
|
UsesR401K |
Boolean |
||
|
UsesIRA |
Boolean |
||
|
UsesRIRA |
Boolean |
||
|
Uses403B |
Boolean |
||
|
UsesR403B |
Boolean |
||
|
Uses457B |
Boolean |
||
|
Uses408K |
Boolean |
||
|
VacationAmount |
Decimal |
8 |
|
|
SickAmount |
Decimal |
8 |
|
|
ExtraVacationAccrual |
Integer |
4 |
|
|
ExtraSickTime |
Integer |
4 |
|
|
ExtraMEDFSA |
Decimal |
8 |
|
|
ExtraDEPJOINTFSA |
Decimal |
8 |
|
|
ExtraDEPFSA |
Decimal |
8 |
|
|
ExtraADOPTFSA |
Decimal |
8 |
|
|
LastVacationAccrual |
Date |
4 |
|
|
LastSickAccrual |
Date |
4 |
|
|
DEPFSAStatus |
Integer |
4 |
|
|
VacationAccrualCap |
Decimal |
8 |
|
|
SickTimeAccrualCap |
Decimal |
8 |
|
|
Nickname |
ZString |
15 |
Employee's nickname entered on the Maintain Employees/Sales Reps window. |
|
PhoneWork |
ZString |
20 |
Work phone number entered on the General tab of the Maintain Employees/Sales Reps window. |
|
PhoneMobile |
ZString |
20 |
Mobile phone number entered on the General tab of the Maintain Employees/Sales Reps window. |
|
EmailHome |
ZString |
64 |
Employee's Email 2 address entered on the General tab of the Maintain Employees/Sales Reps window. |
|
Ethnic Origin |
Integer |
Ethnic Origin selected on the Additional Info tab of the Maintain Employees/Sales Reps window. (Sage 50 Premium Accounting and higher) Valid values include: 0=none specified 1=American Indian/Alaska Native 2=Asian 3=Black/African American 4=Hispanic/Latino 5=Native Hawaiian/Pacific Islander 6=Other 7=White |
|
|
MaritalStatus |
Integer |
Marital Status selected on the Additional Info tab of the Maintain Employees/Sales Reps window. (Sage 50 Premium Accounting and higher) Valid values include: 0=none selected 1=Married 2=Single 3=Divorced 4=Widowed 5=Other |
|
|
EmergencyName |
ZString |
15 |
Emergency Contact Name entered on the Additional Info tab of the Maintain Employees/Sales Reps window. (Sage 50 Premium Accounting and higher) |
|
EmergencyPhone1 |
ZString |
20 |
Emergency Contact Phone 1 entered on the Additional Info tab of the Maintain Employees/Sales Reps window. (Sage 50 Premium Accounting and higher) |
|
EmergencyPhone2 |
ZString |
20 |
Emergency Contact Phone 2 entered on the Additional Info tab of the Maintain Employees/Sales Reps window. (Sage 50 Premium Accounting and higher) |
|
EmploymentStatus |
Integer |
Employment Status selected on the Additional Info tab of the Maintain Employees/Sales Reps window. (Sage 50 Premium Accounting and higher) Valid values include: 0=none selected 1-10=Statuses which are specified in Employee Defaults |
|
|
JobTitle |
ZString |
40 |
Job Title entered on the Additional Info tab of the Maintain Employees/Sales Reps window. (Sage 50 Premium Accounting and higher) |
|
JobCode |
ZString |
20 |
Job Code entered on the Additional Info tab of the Maintain Employees/Sales Reps window. (Sage 50 Premium Accounting and higher) |
|
SupervisorGUID |
UBinary |
GUID (Globally Unique Identifier) used for linking the Employee table. (Sage 50 Premium Accounting and higher) |
|
|
Division |
ZString |
50 |
Division entered on the Additional Info tab of the Maintain Employees/Sales Reps window. (Sage 50 Premium Accounting and higher) |
|
Location |
ZString |
50 |
Location entered on the Additional Info tab of the Maintain Employees/Sales Reps window. (Sage 50 Premium Accounting and higher) |
|
Department |
ZString |
50 |
Department entered on the Additional Info tab of the Maintain Employees/Sales Reps window. (Sage 50 Premium Accounting and higher) |
|
I9VerificationStatus |
Integer |
I-9 Verification Status selected on the Additional Info tab of the Maintain Employees/Sales Reps window. (Sage 50 Premium Accounting and higher) Valid values include: 0=none selected 1=Yes 2=No 3=Pending |
|
|
I9ReverificationDate |
Date |
I-9 Reverification Date entered on the Additional Info tab of the Maintain Employees/Sales Reps window. (Sage 50 Premium Accounting and higher) |
|
|
NextReviewDate |
Date |
Next Review Date from the Performance Reviews window which is accessed from the Pay Info tab of the Maintain Employees/Sales Reps window. (Sage 50 Premium Accounting and higher) |
|
|
PhotoGUID |
UBinary |
GUID (Globally Unique Identifier) used for linking the photo to the employee record. (Sage 50 Premium Accounting and higher) |