SFTP Employee Management
Overview
TrackMy provides an SFTP Employee Management integration that helps clients manage employee data through secure file exchange. This guide is to assist the setup and configuration of such an integration, as well as provide higher-level, explanatory details about how client data is handled once it is imported into the TrackMy system.
Connection Requirements
Standard SFTP (Secure File Transfer Protocol) is the method to connect to and put files in the directories specified in Directory Structure below. Connection details and directory information will be provided by the integration specialist assigned for the integration. Questions about SFTP, as well as assistance with connecting, should be directed toward the integration specialist.
File Requirements
Allowed file formats:
Comma Delimited (.csv)
Text (.txt)
Allowed delimiters:
Comma
Pipe
Note: After a delimiter is configured, changing it will require a re-configuration. Please see your integration specialist for more details about this.
File Columns and Data Requirements
You can download and use the attached file as an example of data definitions and requirements. You can also refer to the tables below.
sftp_employee_management_template.xlsx
Column Name | Data Type | Required | Comments |
|---|---|---|---|
yes | Standard RFC 5321 protocol. | ||
first_name | string | yes | string/varchar, 1-64 chars |
last_name | string | yes | string/varchar, 1-64 chars |
date_of_birth | date | yes | Any standard date formula. Examples: 1/22/1994, 01/22/1995, and 1984-05-15 are all acceptable |
employee_id | string | yes | A unique value to identify your employee. Examples: 239978, X34409, 004933 |
sso_id | string | yes | A value unique to the employee to identify sso setup. May be the email address or a string. |
hcp_role | integer | yes | See HCP Role Table below |
address | string | no | string/varchar, 255 chars max. |
city | string | yes | string/varchar, 64 chars max |
state | string | yes | string/varchar, 2 chars max. Example, “FL,” “CO”, etc. |
zipcode | string | yes | String/varchar 10 chars max. Examples: 81303, 34695-3324. |
phone_number | string | yes | String/varchar, 15 chars max. Examples: (818) 231-5512, 8182315512, and 818.231.5512. Allowed delimiters: parentheses, hyphens, periods. |
personal_email | yes, but see comments | Empty String or NULL, but if populated, Standard RFC 5321 protocol. | |
action | string | yes | Allowed values (case insensitive): enable, disable |
facility | string | no | string/varchar, 64 chars max |
start_date | date | no | The assignment start date. Any standard date formula. Examples: 1/22/1994, 01/22/1995, and 1984-05-15 are all acceptable. See Start and End Dates in Assignment below. |
end_date | date | no | The assignment end date. Any standard date formula. Examples: 1/22/1994, 01/22/1995, and 1984-05-15 are all acceptable. See Start and End Dates in Assignment below. |
manager_id | string | no | string/varchar, 64 chars max |
department_number | string | no | string/varchar, 64 chars max |
birth_sex | integer | no | see Birth Sex table below |
race | integer | no | see Race table below |
ethnicity | integer | no | see Ethnicity table below |
HCP Role (field hcp_role)
Value | Description |
|---|---|
1 | Employee on Facility Payroll |
2 | Licensed Independent Practitioners |
3 | Adult Student/Trainees and Volunteers |
4 | Other Contract Personnel |
Birth Sex (field birth_sex)
Value | Description |
|---|---|
0 | Other |
1 | Male |
2 | Female |
Race (field race)
Value | Description |
|---|---|
1 | American Indian or Alaska Native |
2 | Asian |
3 | Black or African American |
4 | Native Hawaiian or other Pacific Islander |
5 | Other Race |
6 | Patient Refused |
7 | White or Caucasian |
Ethnicity (field ethnicity)
Value | Description |
|---|---|
0 | Hispanic or Latino |
1 | Not Hispanic or Latino |
2 | Unknown |
Directory Structure
When clients access their sftp location, the directory structure is as follows:
During the testing phase, files should be placed in the /test directory. If there are any errors, an error log file will be placed in the /test/sftp_errors directory.
When production ready, files should be placed in the /incoming directory. Any error log files will be placed in the /incoming/sftp_errors directory.
After files are processed, they are placed in the /completed directory.
Clients are encouraged to regularly view and monitor their error logs to improve the quality of client data imported in the TrackMy system, as well as to improve the overall process.
Data Categories
The data clients pass in the file fall into three general categories or subsets. Additional categories may be configurable depending on the integration type, which the assigned integration specialist can explain. The three subsets are as follows:
Employee Data
This data is used to identify and describe the employee profile in the Track My System. This has to do with unique identifiers (such as email, employee_id, sso_id, and personal_email), as well as demographic information (such as address, birth_sex, and ethnicity).
Assignment Data
This data has to do with an employee's location, facility or other company assignments that are relevant for various reporting (such as NHSN reporting), and also includes an employees start and end date at the particular location, facility, or other assignment.
Custom Field Data
Custom data can be added to the system for ad hoc reporting purposes. Please discuss with your implementation specialist for details.
Workflow Process
The action column drives the workflow of each record as described below:
Workflow Diagram
Enable Action Workflow
With an enable action, we search for matching users and follow the following workflow:
If more than one matching user is found, we will skip the record and log the error.
If only one match found:
If the user is currently enabled, we will update the user data with the presented data.
If the user is currently disabled, we will enable the user and update the user data with the presented data.
If there are no matches found, we will create the user with the presented data.
Note: Pre-hire conversion is part of the enable action, and it works as follows. If the incoming data record presents email and personal_email fields, and both are populated, the system will check to see whether the user is currently in a pre-hire status. If so, the process will update the user’s pre-hire status to the presented hcp_role value passed in the file.
Disable Action Workflow
Used in the employee termination context, the disable action will disable the user and enter an end date to any assignment records that may be tied to the user. If no enabled users are found with the presented data, the system will take no action other than creating a log with an informational error.
Errors and Logging
For each file that is processed, a corresponding error log file will be placed in the appropriate sftp_errors directory. See Directory Structure above. The file name will be identical to the client sftp file, except it will have a .log appended to the file name. For example, for a client file client_name_YYMMDD.csv, a log file will be created with the name client_name_YYMMDD.csv.log.
The content of the log file will be identical to the client file, but with two columns added: error_msg and error_level.
Error Messages
Error messages are descriptive and will provide sufficient information to troubleshoot and discover why the error occurred.
Error Levels
The error levels in the file indicate the action that is required as a result of the error. They are as follows:
CRITICAL
This means that the record was not processed at all and needs attention immediately. Common reasons for this type of error would be that the format for the row in question is off, that multiple active accounts were found for the employee and need manual attention, or that a critical field for the employee was not validated (i.e., email, employee_id, first_name, last_name).
ERROR
The record was processed in part, but there were errors after processing the employee’s profile and demographic records which caused the process to stop. Common reasons for this type of error would be that a presented assignment value or a custom user field is not recognized. These are client-side data issues in most cases, which present an opportunity to improve data integrity by monitoring the error log file and communicating with client support where necessary.
INFO
This is purely informational and no action is necessary. An example is when attempting to disable a user, no active users are found to disable. This is logged for informational purposes only.
Order of Operations Processing
Processing happens to three data groups sequentially with the following priority:
Process User Data
Process User Assignment Data
Process Custom User Data
When there is an error for one data group, the process will exit and not move to the next group.
Example: If a record has a missing value for assignment (such as, department), the system will process the user data, but it will not process the assignment and custom user data. In this situation, the error log will indicate that there is a missing value for the assignment data field department.
See also the workflow diagram in Workflow Process above.
Assignment
For sftp employment management, we can track an employee’s assignment status. This can refer to the employee’s department or division or any other logical assignment.
Start and End Dates
When start and end dates are included with the file, we process them as follows:
When adding a new assignment, the incoming start date provided in the file will be used for the start_date. If there is an existing assignment record, the incoming start date will be used as the end_date for the existing record as well. When adding a new assignment, any end_date values in the file will be ignored.
When a disable action occurs (see above), any current assignment records will be updated with the provided end_date and, if one is not provided, they will be updated with the current_date.
Custom User Fields
Add and Update Actions
Sometimes custom user field values are presented in a file for an employee which are different from the current ones saved in the system for that same employee. For example, a manager_id value for an employee in the TrackMy system might be 88937, but the incoming manager_id value for that same employee might be 88952. When values change like this, the old custom field will be set to inactive in the TrackMy system, and the new one added.
Disable Action
On a disable action, all custom user fields for the employee will be set to inactive.