Skip to main content
EMS Software, LLC

Aligning Your Source Data for HR Toolkit

You can use the specifications below to make sure your source data will import correctly into EMS.

The HRTK_Update_Group stored procedure (created by HRTK Update Script G_C_GT_WU.sql) contains all of the logic to create your EMS Contact, Group and Everyday Applications (web) User data. Records are imported into EMS from the EMS_Staging database according to the specifications below.

EMS Staging Field (varchar)

Writes Data to EMS Database Fields

Requirements/Notes

PersonnelNumber (20)

Contact.External Reference

WebUser.ExternalReference

Required Part of Primary Key Cluster. Unique identifier (e.g. Employee/Student ID, Personnel Number, etc.) for each web user.  Used for purposes of creating /updating EMS.

FirstName (50)

Contact.Contact

WebUser.UserName

Must have at least 1 of 3. Built in EMS as LastName, FirstName Middle Initial.

LastName (50)

Contact.Contact

WebUser.UserName

Must have at least 1 of 3. Built in EMS as LastName, FirstName Middle Initial.

Title (50)

Contact.Title

can be null

MiddleInitial (10)

Contact .Contact

WebUser.UserName

Must have at least 1 of 3. Built in EMS as LastName, FirstName Middle Initial.

EMailAddress (75)

Contact.EmailAddress

WebUser.EmailAddress

Required for Web User creation

Phone (50)

Contact.Phone

WebUser.Phone

can be null

Fax (50)

Contact.Phone

WebUser.Fax

can be null

Address1 (50)

Contact.Address1

can be null

Address2 (50)

Contact.Address2

can be null

City (50)

Contact.City

can be null

State (50)

Contact.State

can be null

ZipCode (10)

Contact.ZipCode

can be null

Country (50)

Contact.Country

can be null

NetworkID (50)

WebUser.NetworkID

Required for Integrated Authentication.

GroupID (50)

Group.ExternalReference

Required.  Unique identifier for each group.  Along with PersonnelNumber, part of primary key cluster. If (Hybrid) for Group/GroupType functionality must be 0.

GroupName (50)

Group.GroupName

Required only for Everyday Application User associated with Groups. If (Hybrid) and GroupID = 0, this value is ignored.

GroupType (50)

Group.GroupTypeID

GroupType.Description if unique

Required only for Everyday Application User associated with Groups. Associates groups with group types. If (Hybrid) and GroupID = 0, this value is ignored.

 

Group Type Records

Unique GroupTypes in EMS_Staging.tblPeople are added to the Group Types table in EMS (EMS.tblGroupType). Group Types are not updated or de-activated.

Group Records

  • Groups that exist in EMS_Staging.tblPeople, but not in EMS.tblGroup (based on EMS_Staging.tblPeople.GroupID and EMS.tblGroup.ExternalReference) will be created in EMS.
  • For Groups that exist in EMS_Staging.tblPeople and in EMS.tblGroup, GroupName and GroupType changes in EMS_Staging will be updated in EMS.
  • Groups that exist in EMS.tblGroup, but not in EMS_Staging.tblPeople will be de-activated in EMS (includes all of the Contacts associated with the Group).

Contact Records

Contacts that exist in EMS_Staging.tblPeople, but not in EMS.tblContact (based on EMS_Staging.tblPeople.PersonnelNumber and EMS.tblContact.ExternalReference) will be created in EMS for the associated Group (see section above for Group maintenance logic). A Web User account will also be created for the Contact in EMS and the Group record will be automatically assigned to the Contact’s Web User record. This allows the Contact to make a reservation on behalf of the Group in Virtual EMS. 

For Contacts that exist in EMS_Staging.tblPeople and in EMS.tblContact, any/all changes to the Contact’s information (name, address, etc.) in EMS_Staging will be updated in EMS. If the Group that a Contact is assigned to changes, the Contact record under the original Group will be de-activated and a new Contact record will be added under the new Group. This process also automatically assigns the Group record to the Contact’s Web User record.

Contacts that exist in EMS.tblContact, but not in EMS_Staging.tblPeople will be de-activated in EMS.