Text File Importing for HRIS Integration.

File uploaded by 762463 Support on Mar 28, 2014Last modified by 2979407 on Jun 5, 2017
Version 14Show Document
  • View in full screen mode

     

Introduction

Relias Learning offers several ways for clients to upload and manage employee demographics and Learner Profiles. This document describes demographic uploads and transfers via batch processing of ASCII text files to allow clients to integrate the RLMS with their HRIS.  Please also see the Text File Importing for HRIS Integration: Client FAQ Page for more details.

 

Process

At the client organization’s request, Relias Learning can provide a secure FTP site that they may use to deliver text files for batch processing. Under this model, the client will export a properly formatted ASCII text file and upload it to Relias Learning’s SFTP site. A program on the SFTP server then processes the file, inserts new employees, and updates existing staff as needed. Client organizations will send their text files on a weekly or daily schedule, and after the process is set-up, it runs without manual intervention.

 

File Specification

Each value will be separated by a pipe “|” character and each row will be separated by an ASCII Carriage Return/Line Feed characters (ASCII 10/13). The table on the next page shows field names, data types, nullability and special conditions.

 

File Naming Convention

Files should be named according to the following convention: {YYYYMMDDHHMMSS}.txt. For example, a file created 1/15/2010 at 3:15:22 PM would be named “20100115151522.txt.” Each time a file is generated, the creation timestamp should be used for the file name.

 

Files will be moved to the "processed" subfolder after being processed.  The processed files will be renamed according to the following convention: {original filename-YYYYMMDDHHMMSS}.txt with the timestamp of when the file was processed.

 

For example, if the original file name was “20100115151522.txt” and was processed at 2/16/2010 4:45:00 AM, the processed file would be moved to the /processed/ subdirectory and renamed “20100115151522-20100216044500.txt”

 

File Specifications Table: Please download the attached file for a complete table the below is for reference only.

 

 

Field NumberField NameData TypeRequiredRecommendedReportableSpecial Conditions
USER:00Entry typechar(4)YString literal "USER"
USER:01OrgIDintYRelias defined.  Viewable through the application at System --> Settings
USER:02OrgCodevarchar(6)YRelias defined.  Viewable through the application at System --> Settings
USER:03Last Namenvarchar(50)YCustomer defined
USER:04First Namenvarchar(50)YCustomer defined
USER:05User Namenvarchar(50)YCustomer defined - must be unique for each user in the organization.  Note that this is the primary key used to determine whether records need to be updated or inserted.
USER:06Passwordnvarchar(50)YCustomer defined - Will be case sensitive
USER:07GUIDnvarchar(50)Customer defined - if used, must be unique for the organization
USER:08Employee IDnvarchar(50)Customer defined
USER:09Hire DatesmalldatetimeYYYYYY-MM-DD hh:mm:ss
USER:10Termination DatesmalldatetimeYYYY-MM-DD hh:mm:ss
USER:11Emailnvarchar(150)YCustomer defined. Required for anyone with user permissions greater than a learner (e.g., Administrator).
USER:12Job Titlenvarchar(100)YYCustomer defined. If left blank, existing information in RLMS will be removed. Separate multiple job title names with a semicolon (;).
USER:13Departmentnvarchar(100)YYCustomer defined. If left blank, existing information in RLMS will be removed. Separate multiple department names with a semicolon (;).
USER:14Organizationnvarchar(110)Customer defined
USER:15Locationnvarchar(100)YYCustomer defined. If left blank, existing information in RLMS will be removed.
USER:16Work Phonenvarchar(25)Customer defined
USER:17Credentialsnvarchar(25)Customer defined
USER:18Faxnvarchar(25)Customer defined
USER:19Addressnvarchar(255)Customer defined
USER:20Address2nvarchar(255)Customer defined
USER:21Citynvarchar(255)Customer defined
USER:22Statechar(2)Customer defined
USER:23Countrynvarchar(255)Customer defined
USER:24Postal Codenvarchar(10)Customer defined
USER:25ActivetinyintYY0=inactive, 1=active, 2= onleave Note: Users cannot be created with a status of 0 or 2. They must be added as active before being made "inactive" or "onleave".
USER:26HierarchyIDintY"YInteger corresponding to one of the hierarchy nodes. Viewable through the application at Users --> Hierarchy --> Export Hierarchy.
USER:27UDF1intYYCustomer defined
USER:28UDF2intYYCustomer defined
USER:29UDF3intYYCustomer defined
USER:30RestrictedtinyintY

0=full user, 1=restricted user

All users are contractually full users unless designated as restricted within terms of contract.
USER:31User Categoriesnvarchar(255)YYCustomer defined. If left blank, existing information in RLMS will be removed. Separate multiple categories names with a semicolon (;).
USER:32Hide From Masterint

0=no, 1=yes    

Blank defaults to 0.  Applicable only to Enterprise customers.
USER:33CanElectint

0=no, 1=yes    

Blank defaults to 1.
USER:34Self-Completion Modeint

0=none, 1=no approval needed, 2=approval needed    

Blank defaults to current system settings viewable through the application at System --> Settings.
USER:35External Request Modeint

0=none, 1=no approval needed, 2=initial approval needed, 3=full approval needed

Blank defaults to current system settings viewable through the application at System --> Settings.
USER:36Genderchar1 character only:  M=male or F=female
USER:37Ethnicitynvarchar(50)

Y

(EEOC only)

Predefined list of options (customizable).  Can only have one value per user.
African American/Black, Asian, Caucasian, Hispanic/Latin American, Middle Eastern, Native American/Alaskan, Other, Pacific Islander
USER:38Employment Typesnvarchar(100)YYCustomer defined.  Multiple employment types per user should be separated by a semicolon (;). If left blank, existing information in RLMS will be removed.
USER:39Date of BirthDatetimeYYYY-MM-DD hh:mm:ss
USER:40Middle Namenvarchar(50)Customer defined
USER:41Administratorint

0=no, 1=yes.  

Leave blank if not using, as it will not remove permissions.
USER:42Instructorint

0=no, 1=yes.  

Leave blank if not using, as it will not remove permissions.
USER:43Supervisorchar

Enter U, E and/or R with no commas between for each permission level.  U = User Management, E = Enrollment Management, C = Curriculum Enrollment, M = Module Enrollment, R = Report Management.  Leave blank if not using, as it will not remove permissions.

USER: 44ObservercharEnter Ob, De, or ObDe with no commas between for each permission level.  Ob = Observer.  De = Data Entry.  Leave blank if not using, as it will not remove permissions.
USER: 45Performance Optimizer Reporterint

 

0=no, 1=yes.

Leave blank if not using, as it will not remove permissions.
USER: 46Require Password Change on Next Loginint

0=no, 1=yes.

Leave blank if not using, as it will not remove permissions.

USER:

47

Exempt Statusint

0=non exempt 1=exempt.

Leave blank if not using IP Whitelist feature.

 

Example of Exported User Demographic Data

USER|101|AA1|Doe|John|JDoe|Password|{guid}|088482|2007-10-11 00:00:00|{termination date}|{email}|{job title as text}|{department as text}|{organization as text}|{user

location as text}|123-456-7890|{credentials}|{fax}|123 A. Street|{address 2}|San Diego|CA|United States|96785|1|52|{udf 1 as text}|{udf 2 as text}|{udf 3 as

text}|0|{user category as text}|0|1|0|0|{gender}|{ethnicity}|{employment types}|{dateofbirth}|{middlename}|1|0|URE|ObDe|1|0|0

 


To get back to the Knowledge Base and the master list of topics, please click here: RLMS Knowledge Base

Outcomes