The following field information is intended to assist those who design reports for Sage 50 using Crystal Reports.
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.
There are additional files that contain payroll information for employees. These are EARNSUMM.DAT, EMPAYINF.DAT, EMPPRFLD.DAT, ESPRFLD.DAT, and ESWAGE.DAT.
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
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) |