Skip to main content
EMS Software, LLC

FM Toolkit Overview

The EMS_Staging Database

The EMS_Staging database (created by FMTK Staging DB Install.sql) is used to stage room records before they are imported into EMS.

Once you have identified the FM data source within your organization that will be used to create and maintain your EMS room records, you will be required to define/create a process to import room records into the tblRoom table within EMS_Staging.

Maintaining Room Records in EMS

The FMTK_Update_Room stored procedure (created by FMTK Update Script.sql) contains all of the logic to create your EMS room data. Records are imported into EMS from the EMS_Staging database according to the specifications outlined in the table below:

EMS_Staging.tblRoom Field

EMS.tblRoom Field

Field

Notes

ExternalReference

ExternalReference

Required.  Primary Key.  Unique identifier that links a room record in your FM system with a room record in EMS.

BuildingCode

BuildingID

BuildingCode is matched against tblBuilding.BuildingCode to derive the correct Building ID to be stored in tblRoom.BuildingID.  If BuildingCode can’t be matched in tblBuilding, the room record WILL NOT be created in EMS.

RoomCode

Room

Required

RoomDescription

Description

Required

RoomType

RoomType

RoomType is matched against tblCategoryGroup.Description (Room Type table) to derive the correct Room Type ID to be stored in tblRoom.RoomType.  If the room type can’t be matched in tblCategoryGroup, a new room type record WILL be created.

Floor

FloorID

Floor is matched against tblFloor.Description to derive the correct Floor ID to be stored in tblRoom.FloorID.  If the floor can’t be matched in tblFloor, a new floor record WILL be created.

RoomSize

RoomSize

Optional

Active

Active

Active = 1.  Inactive = 0.

The FM Toolkit uses the ExternalReference field in EMS_Staging.tblRoom and the tblRoom.ExternalReference field in the EMS database to determine whether a record needs to be added, updated, or inactivated. 

Adding New Records in EMS

Rooms:  If the ExternalReference value in EMS_Staging.tblRoom is not found on an existing room record in EMS, a new room will be created. 

Important Note:  Existing EMS clients wanting to implement the FM Toolkit must “synchronize” existing EMS rooms before activating the toolkit by adding a valid ExternalReference value in tblRoomIgnoring this step will result in duplicate records

Room Types:  If the RoomType value in EMS_Staging.tblRoom is not found in the existing list of room types in EMS, a new room type will be created.

Floors:  If the Floor value for a particular room record in EMS_Staging.tblRoom is not found in the existing list of floors in EMS, a new floor will be created.

Buildings:  The FM Toolkit does not add or maintain building records in EMS.

Updates to Existing Records in EMS

If the ExternalReference value in EMS_Staging.tblRoom is found on an existing room record in EMS, the room will be updated with any changes.

Inactive Room Records

A room will be inactivated if one of the two conditions outlined below is met:

1)If an existing room record in EMS does not exist in EMS_Staging.tblRoom (based on ExternalReference).

2)If a room record’s Active status in EMS_Staging.tblRoom = 0.

Note:  Room records within EMS with an empty External Reference field are excluded from processing. Therefore, rooms that are not included in your data feed can be added and maintained manually within EMS and will not impacted by the FM Toolkit.