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).

PayStatus

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.

HourlyEmpType

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.

Email

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
Method

Integer

4

ExtraSickTime
AccrualMethod

Integer

4

ExtraMEDFSA

Decimal

8

ExtraDEPJOINTFSA

Decimal

8

ExtraDEPFSA

Decimal

8

ExtraADOPTFSA

Decimal

8

LastVacationAccrual
Date

Date

4

LastSickAccrual
Date

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)

What do you want to do next?