Skip to main content
EMS Software, LLC

HR Toolkit - Group/Contact Model

Obtaining the HR Toolkit Components

Please contact your Professional Services Consultant to obtain the following components:

  • HRTK Staging DB Install G_C_GT_WU.sql – SQL file required to create the staging database - EMS_Staging.
  • HRTK Update Script G_C_GT_WU.sql – SQL file required to create the stored procedure HRTK_ Update_Group within your EMS database.

HR Toolkit Overview

The EMS_Staging Database

The EMS_Staging database (created by HRTK Staging DB Install G_C_GT_WU.sql) is used to stage Contact, Group and Web User records before they are imported into EMS.

Note: your EMS database may have the “Groups” and “Contacts” fields renamed to other terms.

Once you have identified the data source within your organization that will be used to create and maintain your EMS Contact, Group and Web User records, you will be required to define/create a process to import ACTIVE records into the tblPeople table within EMS_Staging.

Maintaining Contact, Group and Group Type Records in 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 Web User data. Records are imported into EMS from the EMS_Staging database according to the specifications outlined in the following table:

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 Web Users associated with Groups. If (Hybrid) and GroupID = 0, this value is ignored.

GroupType (50)

 

Group.GroupTypeID

GroupType.Description if unique

Required only for Web Users 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 inactivated.

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 inactivated 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 inactivated 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 inactivated in EMS.

Virtual EMS Considerations

Various configuration settings are available to automatically assign the appropriate Web Process Template(s) when a user hits your Virtual EMS site for the first time. Within the Virtual EMS Parameters area of EMS (System Administration > Settings > Parameters (Virtual tab)), the following parameters must be set accordingly to activate this feature:

Note:  Ignore the “Auto Creates…” and “Security Status…” parameters if your HR Toolkit was customized to automatically assign Web Process Templates to Web Users.

Area

Description

Value

Account Management

 

Auto Creates Web Users During Integrated Authentication

Yes

 

Account Management

 

Default Security Template for User

 

Must be specified

 

Account Management

 

Security Status for User

 

Active

 

To automatically assign a Web Process Template to new Web Users, select the ‘Available to New Web Users’ option within your Web Process Template(s) (Configuration > Web > Web Process Templates).

Hybrid Group/Contact HR Toolkits

In some instances, it may be necessary to combine elements of the Group/Group Type and Group/Contact model HR Toolkits. Usually this is necessary when you will have some web users belonging to a department while other web users will not be part of a department and simply using Virtual EMS on their own behalf. A common example is with study rooms on college campuses; all students on campus should have access to Virtual EMS to book these study spaces, but they may not all belong to a Group, and therefore are created following the Group/Group Type rules. In addition with this Toolkit, it is possible to have users created 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 this particular version of the HR Toolkit is right for you.

Installing and Executing the HR Toolkit

Important Note:  Existing EMS clients must ‘synchronize’ existing EMS Group, Contact and Web User records before activating the HR Toolkit. Ignoring this step will result in duplicate records. Please see your Professional Services Consultant for more details.

Installation

The followings steps should be performed on the MS SQL Server that hosts your EMS database.

  1. Obtain the SQL files outlined in the Obtaining the HR Toolkit Components.
  2. Using Microsoft SQL Server Management Studio execute the (G_C_GT_WU.sql)/(G_GT_WU.sql)/(Hybrid.sql) script against your master database. This will create the EMS_Staging database containing one table named tblPeople.
  3. Using Microsoft SQL Server Management Studio execute the HRTK Update Script (G_C_GT_WU.sql)/(G_GT_WU.sql)/(Hybrid.sql) script against your EMS database.  This will create a stored procedure in your EMS database named HRTK_Update_Group

Execution

  1. Import ACTIVE records from your organization’s data source into the tblPeople table within the EMS_Staging database.
  2. For current EMS clients only: Ensure that existing Group and/or Web User records have been ‘synchronized’.
  3. Using Microsoft SQL Server Management Studio execute the HRTK_Update_Group stored procedure.

Note:  Step 3 (and Step 1 with assistance from your IT Department) can be scheduled to execute automatically using a SQL Job. Please refer to your MS SQL Server documentation for more information on creating and scheduling a SQL Job. This is NOT an EMS managed process.