PS Tools 5.5 Help


Overview of Help for Import:

    This help document will help you import (s). Sometimes there is more than one way to accomplish your task (for instance, you can import assignments in the import file or in a separate Membership file.) Therefore you should examine all of the import file specifications before you begin building your import file.

The accrual import will establish accrual balances for any user for any .  Those balances can be defined as 'hard' (meaning that the user will not be allowed to exceed them) or 'soft' (meaning that the user will be allowed to exceed them.)  

The import is assuming that the users' time records are getting exported to another system (like a Payroll application) which is maintaining and calculating correct Accrual balances.  There is generally an inherent lag-time in the process of sending new records to the payroll application, then the payroll application recalculates the accrual balances, and then the payroll application exports those accrual balances back to Timesheet.  The "As Of" field should always contain the date that the Payroll application is considering as its last known record date.  Then the accrual import tool will add or subtract any later records in Timesheet that are against this accrual, during the import process.

For example:
   Bob has these Vacation records:
      8 vacation hours on Thursday the 4th
      8 vacation hours on Friday the 5th
      8 vacation hours on Monday the 8th
      8 vacation hours on Tuesday the 9th
      8 vacation hours on Wednesday the 10th

Payroll is weekly, so Bob's 16 hours of vacation for the 4th and 5th go out to the Payroll application on the following Monday (the 8th).
The Payroll is generated on Tuesday the 9th.
A new Accrual Balance File is generated on Wednesday the 10th for import back in to Timesheet.
That Accrual Balance File should contain the 'As Of' date of Saturday the 6th (the actual last day of that week's payroll).
Bob's record in that file will look like this:
1234 soft Vacation 32 20021206
Meaning that Bob has 32 vacation hours on a soft accrual as of December 6th, 2002.
When the Accrual Import tool imports that record, it starts by looking at all of Bob's records *after* December 6th, 2002, and it subtracts them from this 'As Of' balance.
So, Bob had 32 hours of Vacation Accrual on the 6th, but he took 24 total hours on the 8th, 9th, and 10th.  When the accrual is imported, it will show that Bob currently has 8 hours of Vacation Accrual.

That math, checking all accrual hours after the As Of date, should almost always keep the Accrual Balance displayed on screen correct with the actual balance available to the user.  There are however some tricky situations that could arise and cause the displayed accrual balance to appear to be incorrect.
A User could have hours against his accrual that are entered in the future.  For instance, if Bob knew that he was going to take another vacation day on the 27th and entered that record back on the 9th, then that 8 hours would also get deducted from the Accrual balance.  It would leave Bob with an apparent accrual balance of 0.  He really still has 8, but he has already entered those future hours.
There are other (very complex, generally) situations in which the user can have negative and positive hours against the accrual balance from a prior period correction, or just from trying to "game the system" that will cause the apparent accrual balance to be off.  In all cases the accrual balance in the Payroll application should always be correct, and the final authority for any disputes.

    This document (and the attached sample files) should tell you everything that you need to know about importing files. You should start by testing these in a non-production environment. The sample files that are attached can all be imported with no modifications. And then you can edit them and re-import them to verify that you are able to edit and build importable files. Finally, you should always backupdb your Timesheet database before you begin importing files, especially into your production environment.

    The Scheduled Process is an advanced option that will allow for an automatic import on a recurring basis. You should not enable this until you have manually imported representative files several times, in order to verify that the system which is producing the files is doing so correctly. Remember that there is no Preview occurring in the Scheduled Imports, and so bad data can creep into the Timesheet system if not thoroughly tested.

Help Contents:

Usage Instructions

    This is the general method that Journyx recommends you follow when you are using the Import Tool.

1. Logged in as an Admin, on the main Admin page click the link for Professional Services.

2. On the Professional Services page, under the Import heading, select the import that you would like to run and press the button.

3. The screen will refresh to the Import page.  On this page you should click the button and browse your system to find the file that you want to import.  When you have found the correct file press the button.

4. The screen will refresh with your import file displayed in a grid on the screen.  You should examine the file thoroughly to make sure that it is complete and correct.

5. If the file is correct, then click the Admin button in the button bar, and on the main Admin page click the link for Professional Services.

6. On the Professional Services page, under the Import heading, select the import that you would like to run and press the button.

7. The screen will refresh to the Import page.  On this page you should click the button and browse your system to find the file that you want to import.  When you have found the correct file press the button.

8. The screen will refresh to a results screen with Messages, Warnings, and Errors.  The messages are simply confirmations.  There are not normally any warnings.  Errors will list each record that did not get imported along with the reason.  Generally those reasons are things like “the Employee Number in the import file could not be found”.

9. If you received error messages you can correct those errors in the Timesheet application (adding or correcting Employee Numbers, for instance) or in the import file.  You can "chop down" the file to only the previously-rejected records when you re-import it, or you can simply re-import the entire file.
(top)

Advanced Usage Instructions

    Most of the time you will be using the button in the Import a file that is located on your computer section and importing a file from your computer. When you are ready to begin importing a file automatically, through the scheduled process, then you need to use the Import a file that is located on the server section to test that the Timesheet application can read files from this location. You might also use this section if your import files are on the server.

    When you want to upload a file that is on the server, the Timesheet application itself will be going to get that file. This is different from the browser upload, because in the browser upload option it is really you who is reading the file. This is an important distinction for network security. For the server import (and the scheduled import as well) to work, then the Timesheet application must have the correct permissions to read from the location where your file(s) will be stored. This is why this document indicates that paths to your file that do not contain a drive letter will not work, because the Timesheet application will almost never have permission to read from that location.

    If your Timesheet installation is on a Windows server, then it will be the Anonymous IIS account that is trying to read the file for import. The Anonymous IIS account usually does not have read access to most directories, either on or off the server. In that case you will undoubtedly need to get assistance from your Network Administrator to set that up. They will probably suggest changing whatever program that is writing the files to have them written into a folder that the Anonymous IIS user already has permission to read in. This is a great suggestion, and Journyx recommends the \Journyx\jwt\ps directory for this purpose because this location will get backed up with Timesheet backups.

    If your Timesheet installation is on a Unix server, then it will be the account that installed the Timesheet application which will be trying to read the files for import. Your unix server administrator should be able to grant the proper read permissions with no difficulty.

    Other than the issue of the Timesheet application's user account having the proper rights to read the file, there are no special issues for the Import a file that is located on the server section of the Import Tool. The and buttons work in exactly the same way as they do in the Import a file that is located on the server section of the Import tool.

    This section is rarely used, except as a test during the transition to enabling Scheduled Imports. The Set a Schedule to regularly import a file that is located on the server section's File Location field works in exactly the same way as the Import a file that is located on the server section's field. That makes this section an ideal testbed before you begin scheduling your import(s).
(top)

Scheduled Process Instructions

    The Schedules Process is an advanced option that will allow for an automatic import on a recurring basis. You should not enable this until you have manually imported representative files several times, in order to verify that the system which is producing the files is doing so correctly. Remember that there is no Preview occurring in the Scheduled Imports, and so bad data can creep into the Timesheet system if not thoroughly tested.

    You cannot establish more than one schedule for a specific import, but you can have different imports running at different scheduled times. When you enable the Schedule for an import you will see that those values will persist on the import page, so you can view and edit the schedule later.

    You will need to start the process by setting the "Is Schedule Import enabled?" to "Yes".

    Then you will need to decide if you want the automatic import to run one time per day, or many times per day. If you want it to run one time per day, then you will select the radio button next to the option for "Set a schedule that runs once per day" and then set the options in that section. If you want the automatic process to run multiple times per day then you will need to select the radio button next to "Set a schedule that runs many times per day" and set the options in that section.

    If you set the automatic process to "Set a schedule that runs once per day" then you will need to specify a time of day for the import to run. These two fields are in military time, so 0:00 corresponds to midnight and 17:30 corresponds to 5:30 in the afternoon. The time is based upon the time (and time zone) of the server. You will also need to select the days of the week that the automatic process occurs on. If you do not select any of the days of the week you will receive an error. You can select any number of days.

    If you set the automatic process to "Set a schedule that runs many times per day" then you will simply set the number of hours and minutes between imports. Of course an interval like 6 hours or 4 hours will be easier to predict because it will occur at the same times every day. If you choose an odd interval, like 7 hours and 15 minutes, then you will have trouble predicting exactly what time the import will occur next. With the multiple imports per day you cannot choose to skip some days, the import will occur on every day.

    If the import file is missing, or cannot be read, or another error occurs, then it will be noted in the debug.log file. No other notification will occur. The import schedule will continue on at the next scheduled time.

    The File Location field is looking for a server-file. So you must supply a direct path to the file, like "c:\importfiles\filename.txt". You cannot use relative path names that reference computer names, like "\bobscomputer\importfiles\filename.txt". This means that you may have to map a drive from your Timesheet application server.

    In order to make the schedule active you must press the or button. Obviously the only difference between these buttons is that the second one will perform an import right now.

(top)

File Format

    The file that you generate for import must be a simple ASCII flat-file. It must be a delimited-type of file, not fixed-width. The delimiter should be tab, but can be comma, pipe, or just about any other 'standard' delimiter. Sometimes a file can have multiple delimiters. For instance, the User import file has a field for "Authority" that can contain multiple values. Those multiple values within a field must be a different delimiter from the primary delimiter of the file. So if the file is pipe-delimited, then the multiple Authority values in the User import file would be comma-delimited. The recommended and most common choice here is to tab-delimit the file and use commas within any field that allows for multiple values.

    This table lists each column that you can put into a import file. Your file will not have to contain all of these columns. The "Header" is the name of the field, and is its header in the import file. The "Req." field indicates whether or not the column is required ("Y" = required). If a column is required, then every record (row of the file) must have a valid value in that field. The "Size" field contains the maximum number of characters that are supported within this field. The "#" field is displayed in this chart for ease of reading only, your import file will not have numbers like this.
# Header Description Req. Size Notes
1 Employee Number Extra User field containing the payroll application's unique identifier for the users. Y 28 This can vary from installation to installation. You should use whatever piece of data that your payroll application uses and then put that in either the Employee Number field or the Social Security Number field. If you use Social Security Number then the Header on this field will need to change to that.
2 Type Hard or soft accrual type within Timesheet. Y 4 Must be either 'hard' or 'soft' as appropriate. Probably your payroll system does not have this concept, and so you will need to edit the file you receive from it and add this field with the appropriate values.
3 The field that accrual balances work with Y 28 This is the code or type of accrual. Value must match exactly a value in Timesheet. If the user does not already have an accrual of this type then it will be created.
4 Balance Number of hours that the users has on this accrual Y 4 Numeric. Maximum value is "999". Minimum value is "-999".
5 As Of Effective date of this accrual balance Y 8 Must be a date in the format YYYYMMDD.

(top)

Notes on Illegal Characters

    There are many characters that you should generally not include in your import file.  These characters can cause problems in displaying HTML in the web interface, and a few of them can cause database-level errors that can be difficult to recover from.  All extended-ASCII characters, like à, ü, and ñ are forbidden.

    Journyx recommends that you build your files in Excel, just because it is so easy to read and use.  If you do use Excel then save it off as a tab-separated-text file, then Excel will add double-quotes around each field that contains a comma.  Journyx has found that remembering to remove these double-quotes is difficult, so Journyx has enhanced the import tools to automatically strip double-quote characters from every field, if-and-only-if the double-quotes are the first and last characters in a field.  Therefore, in the unlikely event that you want a field to contain and initial and ending double-quote then you will simply have to double your double-quotes, like this: ""doubleed double-quotes"" will result in "single double-quotes" being imported.  Generally both double-quotes and commas should be avoided except in fields that specify them (like a users Full Name or the Groups field.)

    In HTML it is possible to generate extended ASCII characters by using codes.  So, if you view the source-code of this page you will see that many characters in the following chart were produced using codes like this: &# 38 to create an &.  But & and # are both not-recommended characters.  The fact is that they will often import, but sometimes they will display as the character you wish to display and sometimes they will display as the code itself (&# 38).  This is simply a limitation of the cgi script-based nature of the Timesheet application.

    All of these characters should be avoided, unless specifically allowed in the Notes on a particular field:
: ; & % ' # @ ~ {tab} ÷ \
< > / ¢ £ ¥ ™ © ® ¡ ¿
µ ¦ § ¬ ± ¾ ½ ¼    
    If you must have a “character” in the file/field, then these are the characters that Journyx recommends:
- _ * !

(top)

Sample in browser

    This is generally what your import file will look like. This one is formatted in an HTML table for easy reading. See the downloadable sample file, below, for a more precise file to model yours after.
Employee Number Type Balance As Of
1014 hard Vacation 64 20021215
1015 hard Vacation 32 20021215
1016 soft Vacation 102 20021215
1016 hard Personal Day 16 20021215

(top)

Link to external sample text file

    Right-click on any of these links and select to Save (or 'Save as...') the file onto your local computer. These files are all tab-separated text files. All of the sample files are valid and ready to be imported, recognizing that some of the imports will need to occur in a specific sequence (like importing (s) before you import Memberships.)

    A standard Import file.

    An Excel file with the template for importing s.
(top)