Skip to main content
Eptura Knowledge Center

Eptura Workplace AUP With Ticket Creation

  1. Last updated
  2. Save as PDF

This article provides an overview of the Automated User Process (AUP) import, including rules, relevant safeguards, and the standard logic flow required to process flat files (.csv, .pipe, .txt, etc.) received from clients. Additionally, for clients using the Move module, we a process to create New, Terminate, and Modify tickets, which are used to make changes to employee records, such as adding new employees, terminating existing employees, and moving employees between buildings.

The following rules, safeguards, and logic flow process must be followed to successfully complete an AUP import:

Rules

  1. The file to be processed (the input file) must be a flat file with fixed columns.

  2. Building information must be matchable in some way to Eptura Workplace building records. In the event that only a single building is being processed, the building record can be set to default.

  3. Records must have a unique, non-null (not blank) identifier. The most commonly used identifier is employeeId.

  4. If termination tickets/users will be deactivated, the input file is expected to be a full list of all active users.

  5. Eptura Workplace DB will be updated based on the input file in three ways:

a. New Hires will be added to a New Hire ticket or added directly to Eptura Workplace database, depending on client needs.

b. Existing users (matched by their unique identifier) will be updated to match information from the input file. Users will be checked to see if their building has changed. Those users will be added to a move request ticket if desired by the client. 

c. Users marked as active in the database, but are no longer in the input file are considered to no longer be employees. Terminated employees will be added to terminate tickets, or can be marked as inactive, depending on client needs.

  1. A post processing email will be sent, including a summary of actions taken, and any error logs.

Safeguards

  1. Record Validation Check. Records are checked for valid information at several places in the process. Records that fail these validations will be removed from processing and marked as "ERROR" records in the summary email.

    The following will be validated:

a. The input file is parsable into the fields established in rule number one.

b. The unique and non-null identifier is checked (all duplicate or blank records will be removed).

c. The "Building", "Room", and  "Floor" can be found based on client-specific criteria.

d. When creating move tickets, the data written to each field should match the data type and required flag of that field. For example, the First Name field is required for New Hire tickets; therefore, a New Hire ticket will only be created if the FirstName column is populated with parsable text. 
 

  1. Update Error Handling. Occasionally, the import process will receive bad data or will parse improperly. This error can cause the process to incorrectly assume the majority of the existing user records need to be updated, even if they do not (generally, only a small fraction of users are updated in any single AUP import). To protect against unnecessary updates, a variable has been defined (maxUpdate) to verify that only a certain percentage of records are updated during any given import. The default threshold is less than 10%; however, the default setting can be modified if more than 10% of the records should be updated at once. 

    To calculate the update percentage, the maxUpdate variable divides the number of records to be updated by the total number of records. For example, if 195 of 2000 input records require updates, the update percentage is calculated as 9.75% (200 divided by 2000) and the update can proceed. If the update percentage exceeds the defined threshold, the process is stopped. 
     
  2. Termination Request Threshold. Similar to bad or improperly parsed data, incomplete files are occasionally received during the AUP import. These files can cause a significant number of records to be marked as Terminate records incorrectly. To protect against inaccurate termination requests, the threshold for termination requests is capped at 100 per import.

    During the AUP import, the number of detected terminate requests is counted and compared to the defined threshold. The default threshold is 100; however, the default setting can be modified to process more than 100 terminate requests at one time, if necessary.

Logic Flow

  1. The import file is parsed, and the columns are renamed to use Eptura Workplace terminology and data types.

  2. The import file is checked for duplicate or blank unique identifiers. Records that violate that rule are removed from processing.

  3. The import file is matched to Eptura Workplace location information (refer to Rules 2). Records with no valid location are removed from processing.

  4. The import file is matched to an Eptura Workplace Cost Center ID.

Cost Center IDs vary by client, but are often named after a corresponding department. NULL Cost Centers are either allowed or can flag as an error at the client’s discretion. 

  1. The import file is saved to a temporary database table called [dbo].[_userImport]. This table serves as a soft backup for future comparison logic.

  2. The import file is matched to Eptura Workplace user information (EmployeeID is most commonly used).

    At this point, the logic breaks into two flows based on whether the information being imported is new or existing. Step 7 details how existing information is processed, and step 8 details how new information is processed.

  3. Logic flow for EXISTING information:

a. The Eptura Workplace database is queried for user data, and matched to the users in the import file, for only the columns provided in the import file (no building information is considered in this step).

b. Eptura Workplace and import file records are compared, in lowercase, with no spaces (Houston, TX matches houston,tx) and any differences are flagged.

c. For records with different data, the data contained in the import file is written to the Eptura Workplace database.

d. Building information in the import file is compared to building information in the Eptura Workplace database. 

e. If the building information has changed, the record is flagged as needing a Move ticket.

f. Records flagged for a Move ticket are checked against current Move tickets. If the unique identifier for the record is found in an existing Move ticket, the record is considered complete, as a ticket already exists.

g. Records flagged that are not tied to an existing Move ticket go to ticket creation.

h. The Ticket Creation process validates the data (refer to Safeguards 1.d) and creates Move tickets based on groups. Users are grouped by building, with each ticket containing up to 10 users in alphabetical order by last name. For example, if 13 records are moving to the Clay Street building, 2 tickets would be created: the first containing 10 users and the second containing the remaining 3 users. Note: Grouping logic is configurable if necessary.

i. Move tickets are populated with "items" (one item per record), and the items are then populated with "fields". The number of fields is configurable, which means fields can vary by ticket and supplied data.
 

  1. Logic flow for NEW information:

a. Logic is implemented to see if New Hire records will be flagged for New Hire tickets. Clients can mark all New Hires as going to tickets or can filter such that only specific buildings or other criteria get tickets.

b. Records not flagged for a New Hire ticket are inserted directly into the User table.

c. Records flagged for a New Hire ticket are checked against current New Hire tickets. If the unique identifier for the record is found in an existing New Hire ticket, the record is considered complete, as a ticket already exists.

d. Records that are not tied to an existing New Hire ticket go to ticket creation.

e. The Ticket Creation process validates the data (refer to Safeguards 1) and creates New Hire tickets based on groups. Users are grouped by building, with each ticket containing up to 10 users in alphabetical order by last name. For example, if 13 new hire records are added to the Clay Street building, 2 tickets would be created: the first containing 10 users and the second containing the remaining 3 users. Note: Grouping logic is configurable if necessary.

f.  New Hire tickets are populated with "items" (one item per record), and the items are then populated with "fields". The number of fields is configurable, which means fields can vary by ticket and supplied data.

At this point, all new data has been added to the database, and all updates have been resolved.
 

  1. The user records in the import file are compared to all active users in the Eptura Workplace  database. Any users in the Eptura Workplace database that are not found in the import file are marked as Terminate records.

    Similar to other tickets, when a user is terminated, they can either be flagged for a terminate ticket or not, based on client logic. Users not flagged for a ticket are marked as inactive directly in the Eptura Workplace  database. Terminated users flagged for tickets go through logic similar to the logic defined in steps 7.f. through 7.i. The records are checked to confirm they are not part of an existing ticket, and new tickets are created as needed.

  2. Database stored procedures are called to add default center permissions and mail stops for users who do not currently have them assigned. The center permissions given to users by default vary by client. Note: This step is only required if users are added directly to the database rather than by using the New Ticket process.

  3. The process is complete, and a confirmation email is sent summarizing the actions taken. Any error logs created during the import are attached to the confirmation email.

The processes and procedures outlined in this article are meant to provide a high-level overview of the Eptura Workplace AUP import.