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

This topic provides information on the following:

Below are the baseline staging database fields for both Hybrid and Group-Group Type Setups. Please note that your EMS HR Toolkit staging database may be different. Please contact your Professional Services Consultant if you are unsure of what EMS HR Toolkit setup you are using.

Hybrid Setup

In some instances, it may be necessary to combine elements of the Group/Group Type model into a Hybrid data model. This is typically necessary when you will have some Everyday Application Users belonging to a department while other Everyday Application Users are not part of a department and are simply using EMS Web App on their own.

For example, for study rooms on college campuses, students on campus could have access to EMS Web App to book these study spaces, but they may not all belong to a Group (department), and therefore are created following the Group/Group Type rules. Additionally, it is possible to have users that belong to both individual Groups and organizational Groups. For instance, John Doe could be created as a contact for the History department as well as an individual Group. You should work with your Professional Services Consultant for guidance on whether the Hybrid data model is right for you.  

Baseline Stage Database Fields for Hybrid Setup

EMS Staging Field (varchar)

Writes Data to EMS Database Fields

Requirements and Notes

PersonnelNumber (255)

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 (3)

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-Group Type Setup

The Group/Group Type setup categorizes User records by group and group type and does not create Contacts under the Groups imported into the EMS HR Toolkit.

Your EMS environment may use different names for Groups and Contacts, such as Employee.

Baseline Stage Database Fields for Group-Group Type Setup

EMS Staging Field (varchar)

Writes Data to EMS Database Fields

Requirements/Notes

PersonnelNumber (20)

Group.ExternalReference

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

FirstName (50)

Group.GroupName WebUser.UserName

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

LastName (50)

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

Group.Group Name WebUser.UserName

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

EMailAddress (75)

Group.EmailAddress WebUser.EmailAddress

REQUIRED for Web User creation.

Phone (50)

Group.Phone WebUser.Phone

Can be null.

Fax (50)

Group.Fax WebUser.Fax

Can be null.

Address1 (50)

Group.Address1

Can be null.

Address2 (50)

Group.Address2

Can be null.

City (50)

Group.City

Can be null.

State (50)

Group.State

Can be null.

ZipCode (10)

Group.ZipCode

Can be null.

Country (50)

Group.Country

Can be null.

BillingReference (50)

Group.BillingReference

Can be null.

NetworkID (50)

Group.NetworkID WebUser.NetworkID

REQUIRED for Integrated Authentication.

GroupType (50)

GroupType.Description if unique Group.GrouptypeID

REQUIRED for assignment of Group Type to groups. Will assign GroupType of (None) when left blank or null.

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.PersonnelNumber and EMS.tblGroup.ExternalReference) will be created in EMS.
  • For Groups that exist in EMS_Staging.tblPeople and in EMS.tblGroup, GroupName (tblPeople.LastName, tblPeople.FirstName) 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.
  • An Everyday User Account will be created for each 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. 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 EMS Web App.
  • 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.
  • Contacts that exist in EMS.tblContact, but not in EMS_Staging.tblPeople will be de-activated in EMS.