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:
- Two baseline setups for the staging data:
- Group Type Records
- Group Records
- Contact Records
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.