DrugTestNetwork’s online application software for drug and alcohol testing data management and random selection has easy to use import features to manage the upkeep of employer personnel rosters.
One of the biggest challenges faced by TPAs is keeping employee rosters up-to-date for the random selection of personnel for drug and alcohol testing. Importing data is an easy task with resources that read Excel spreadsheets saved as “tab delimited” text files.
Employers often have the capability to review their active personnel/employee lists in an Excel spreadsheet. From there it’s easy to modify the spreadsheet to import the data into the Employer’s account. The system has the capability to import far more than simply an employee’s first name, last name and employee ID whether it is a Social Security Number or other proprietary employment ID, however, this article will limit the discussion to just a few of the available keywords for import.
The first row of the spreadsheet contains “key-words” that identify the columns of data. The following keywords identify the columns for the employee’s first name, last name and ID, respectively:
- First
- Last
- ID_No
Sometimes a single column might contain the employees first and last name as “John Doe” or “Doe, John”
When a single column contains both names you can use the following key-words in row-1 above that data:
- LF-Name: when the data appears as “Doe, John”
- FL-Name: when the data appears as “John Doe”
Other useful items often imported are fields referred to as “Location” and “Misc” and use those keywords.
When the program generates a random selection for drug and alcohol testing, if either of those fields are populated for the eligible participants in the randomization, the report of participants selected will include that data.
The Excel spreadsheet must be saved as Tab-Delimited Text to remove all the Excel formatting and proprietary attributes before importing the data into the application. When the file is saved, close Excel because Excel prevents other applications from accessing the file while Excel has it open.
The import resource uses the Browse feature to locate and upload the data file. Existing participant records are deleted, however, any existing drug and alcohol tests associated with the participant are not effected, and the new participant records are ready for a random selection.
Other import methods are provided that do not delete existing personnel. The Merge features makes all personnel Not-Active prior to the import and if an employee with the same name and ID is imported, the existing record is set Active or Not-Active pending on the status of the imported record.
Below is an image of the Excel spreadsheet with instructions to save the spreadsheet, as Tab-Delimited Text, the file that is selected for import:
You can download the instructional Excel spreadsheet Here.
How to Import the text file:
Tap the menu Clients to navigate to the application’s main page, and search for the account for the import, and tap the personnel icon to open Personnel Management where you will find the import icon.
1. Search for the Account:
This example searches for a company whose name starts with heritage.
2. Tap the people icon to open Personnel Management for the account. If the account is a DOT account you will see the people icon with a shield:3. When personnel management opens, click the import icon on the far right under the section Options for this Account:
4. Using the import module, click the browse button to locate the .txt file saved from the Excel spreadsheet, choose the Overwrite option, and click submit.
