The Data import function under System>Batch process menu>Data import/export>Data import provides access to a function for creating RefTracker questions from XML formatted data (file loading for batches and a web service for individual questions), as well as access to the XML based Time and cost Journal import process (file loading).
The Question import function accessible from Batch process menu>Data import/export>Data import allows question information in a defined XML format to be imported into RefTracker in such a way that a new question (or questions) is created.This API (application programming interface) can be used for one off imports of a batch of questions (such as importing records from a previous Request management system), or for individual question submission via a web service (such a submission of questions referred for other systems (for example, the already existing RefTracker to RefTracker functionality uses this web service).
Using the process for bulk loading question data from an individual file of records
To use this process choose "Question importQuestions" in the import type drop down of the System>Batch process menu>Data import/export>Data import screen.
The XML schema to which the files of question data that is to be imported must be formatted, can be viewed by clicking the appropriate link in the top right of this screen. The schema is generated at the time that you want to view it and so will always be up to date for the database from which it has been extracted. The schema defines the fields for which data can be supplied in XML format to create a question. To be valid, the data for a question MUST include:
As a guide, sample data import files that comply with the XML schema are provided in the exchange/import/request/sample directory.
To run a question import process ensure that the data to be loaded is in the appropriate RefTracker defined XML schema format:
After running the import process, with a successful data load, you will notice that the file that you process is no longer showing in the Data import screen – it has been moved to the exchange/import/request/archive directory (you can view it by downloading it using System>Utilities>Administration utilities>File upload/download).
After running the import process, with any questions not successfully loaded, the input file remains showing in the Data import screen so that the unprocessed records can be corrected and rerun.
By default, the question will be created using the labels provided by the "Default form (System)" (which you can modify if you need to), but Altarama can help you to use a specific form if required. As this is a bulk load, any emails associated with the form used to load the question are NOT sent, and allocation is determined by the data that is loaded rather than the form. Any questions loaded where fields that are designated as mandatory in the form do not have data loaded into them, will be set to "Incomplete" and will cause the Change screen to display when accessed so that the empty mandatory fields can be completed. Deliberately loading a record with an empty mandatory field will ensure that this review process is instigated for the loaded record/s.
If the XML record is invalid, but contains data that cannot be mapped to the required RefTracker field for any reason, that data will not be lost – it will be added to the "Other imported data field of the created question.
This web service is designed to allow data for one question at a time to be submitted – for example, transfer of a question from your IT department's call tracking system to RefTracker, or transfer of the data entered into a form provided by your IT department. Note that use of RefTracker created forms is always preferable to data being input via a form designed elsewhere as there is always the risk that the form designed elsewhere could become incompatible with changed values in RefTracker (code table values changed in RefTracker, but not changed in the external form, for example).
The same schema accessible from the System>Batch process menu>Data import/export>Data import screen, determines the format required to submit question data to the RefTracker question import web service at <yourdomain>/service/addQuestion.
There is limited validation built into this RefTracker import process at this time, so it is important to ensure that the format being used for this data importing is not only compliant with the schema, but is well tested to ensure that it will always create a valid question. Invalid records will simply NOT be loaded.
Please contact Altarama for more information if you want to use this web service.
RefTracker allows costs associated with responding to requests, and librarian hours spent developing responses to requests (which can be automatically converted into costs by RefTracker), to be entered as answers are developed using the RefTracker screens (the Answer, Third party and Quick screens respectively provide "Cost incurred" and "Time used" fields for entry of this information).
The RefTracker "Data extract" statistical reports show the total costs accumulated in relation to a question, allowing libraries to report on the hours spent, or costs of, developing a response for billing or management purposes.
Sometimes costs are incurred externally in relation to RefTracker questions, and information about those costs need to be loaded into RefTracker so that the total cost being reported by RefTracker includes those external costs. For example:
This Time and cost journal entry loading Batch process allows costs and/or time to be loaded as single batch process from information in a published XML file format.
<?xml version="1.0" encoding="utf-8" standalone="yes"?> <journaldata> <journalentry> <journal_amount>500.98</journal_amount> <transaction_message>Invalid question number [PROB9999]</transaction_message> <question_no>PROB9999</question_no> <journal_datetime>2007-04-08</journal_datetime> <journal_type>100</journal_type> <journal_hours>0</journal_hours> <journal_text>Beta</journal_text> <transaction_id>1006</transaction_id> <journal_source>200</journal_source> </journalentry> </journaldata>
About the Time and cost journal entry input file XML format
Records must be enclosed in the journaldata xml tag
Each record must be enclosed in the journalentry tag
Each record must include at least the following tags:
and journal_amount and/or journal_hours
Details of the XML tags
- The question_no field is mandatory, and the data in it must match a question number existing in your RefTracker database. Therefore the Question number must include the prefix eg RT1234. This tag specifies the RefTracker question number that the journal entry will be created in.- The journal_amount field should be numeric (no currency sign). If this field is present the amount will be loaded into the RefTracker journal record amount field. Negative values are allowed in order to provide a mechanism for correcting information that may have been accidentally bulk loaded more than once.
- The journal_hours field can be in the format HH:MM or a numeral that represents the number of minutes. If this field is present, the time specified will be loaded into the RefTracker journal record time field. Negative journal_hours values are not allowed. Important note - journal_hours simply loads hours to the journal – in loading this data RefTracker will not do any automatic multiplication of journal_hours by staff member billing rate, as it cannot tell which staff member spent the hours. Therefore, if you want the cost of these hours to be loaded, you should also include a journal_amount tag that specifies the cost of the hours, in the same input record. Similarly, to correct for journal_hours loaded more than once, use a negative Journal_amount value to correct the impact of those hours.
Optional XML tags that may be supplied are:
- The journal_datetime field is used to specify the date on which the transaction specified by this record occurred. It must be in the format yyyy-mm-dd hh:mm where hh:mm is optional, however supply of time information is optional. If not included, it will default to the date and time at which the batch was processed.Note that the date and time specified here will be interpreted as having been specified in the time zone where your RefTracker server is located.
- The journal_text field is used to attribute a description to the data being loaded. For example you might want to enter the name of the database that was searched to generate the cost being loaded e.g. "ABC database search". If not included, no description will be loaded.
- The journal_type field is used to indicate the type of data being loaded. Valid values for this tag are the numerical codes displayed to the left of the entries in the "Journal type (time)" and "Journal type (cost)" code tables – they will have codes between 300 and 399 for time and 600 and 699 for costs.The Journal type (time and cost) code tables can be edited to provide other values that describe each of the different types of time and cost that your library can incur e.g. "database searching cost", "contractor cost", "company search fee", etc. Defining a Journal entry (time or cost) code table entry, will often remove the need to supply a Journal_text entry in your input file to describe the cost.Note that defining a journal type describes the data you are loading only - the batch loading process does NOT utilise any default values in these code tables. When using batch loading the amounts MUST be included in the records that you load.
- The journal_source field is used to indicate the source of the cost or hours in the journal entry – in other words how they were loaded. When loading records using this Journal Entry Loading function, the value of this field MUST be 200 to indicate that they were loaded using the Journal entry Batch loading process. If this tag not included in the XML file, this value will default to 200 when a journal record is created via the loader – so there is no need to include this tag in your xml data record.
- The transaction_id tag is not actually loaded into the RefTracker database but it can be used to provide a unique id for the record. We recommend use of unique (possibly sequential) numbers this tag as it makes it easier to track issues when there are loading problems.
The transaction_message - you should not include this tag in your data file – when appropriate, it will be inserted by the RefTracker Journal Entry Load function to indicate the reason why that record did not pass RefTracker Journal Entry Load function validation, and so could not be loaded to the RefTracker database. (When you correct the eror you should also remove this tag.)
Except for transaction_id and transaction_message, neither of which are loaded to the RefTracker database, the lines in this XML definition are appropriately named to indicate the fields of the RefTracker Journal Table, into which the data will be loaded.
An example input file, ready to load to RefTracker, might look like:
<?xml version="1.0" encoding="utf-8" standalone="yes"?> <journaldata> <journalentry> <journal_amount>60.60</journal_amount> <question_no>PROB1254</question_no> <journal_datetime>2007-09-02</journal_datetime> <journal_type>700</journal_type> <journal_text>Better database searching costs</journal_text> <transaction_id>0002</transaction_id> <journal_source>200</journal_source> </journalentry> <journalentry> <journal_amount>90.00</journal_amount> <question_no>PROB1242</question_no> <journal_datetime>2007-09-02</journal_datetime> <journal_type>600</journal_type> <journal_hours>1</journal_hours> <journal_text>Better database searching costs</journal_text> <transaction_id>0003</transaction_id> <journal_source>200</journal_source> </journalentry> </journaldata>
But my input file is not in this format – it's not even in XML!
Manipulating data files in, or into, XML is easy.
If your file is in XML format, but just not in the format specified here, you can download a free XML editing/viewing tool from Microsoft at http://www.microsoft.com/downloads/details.aspx?familyid=72d6aa49-787d-4118-ba5f-4f30fe913628&displaylang=en
If your file is in .csv format, it is easy to both manipulate it and convert it to XML by using Microsoft Excel.
Here's an example of how to prepare a CSV file for use in RefTracker Time and cost journal entry Loading including conversion to XML:
- Open the .csv file in Excel
- Perform any data manipulations required to obtain the mandatory data fields.
- Select Data > xml > xml source from menu bar
- Click on XML Maps button
- Click the add button and browse for and select the file:
- Click OK - now you should see an XML schema tree
- Click on journal_datetime in the tree and drag to the first cell of the column that contains the date of the transaction (it might be called 'billing date' for example)
- Click on question_no in the tree and drag to the first cell of the column that contains the question number to which the journal should be applied (it might be called 'cost centre' for example)
- Click on journal_amount in the tree and drag to the first cell of the column that contains the cost to be applied to this question (it might be called 'Net amt' for example)
- Select Data > Xml > Export from the menu bar and save to a new file
- This new file is now ready for loading via the RefTracker loader (so it needs to be placed in the reftracker/exchange/import/journal entry/input directory
A helpful tutorial for using the Excel XML conversion feature can be found at
2. Processing details
When your data file has been provided in the required format, it needs to be loaded into the reftracker/load/journalentry/input directory. We recommend that files are uniquely named using a file name and date e.g. ABC20071102, so that the archive files for each processing run will also be uniquely named.
To process your input file, log into RefTracker as a System Administrator, click on System, then click on Batch processing, then click on Time and cost journal entry import.
A screen showing all files currently waiting to be loaded in the reftracker/load/journalentry/input directory displays, and the files to be loaded should be ticked before clicking on Submit.
The Loader does an initial pass through the input file and reads to the end of the file but does not process any records. This pass is simply to verify that the file can be read. If there are any major XML irregularities they will be detected during this pass and a screen similar to the next screen print will display detailing the errors found.
The Loader then does a second pass – each logical record (bounded by the journalentry xml tag) is extracted. If the journal record passes validation, the data specified in it will be loaded as a new entry in the journal table of the specified question number, and the xml record will be saved to the archive file.
Results of the processing run are displayed in the following screen. In the example below, the red flag indicates not all records were successfully processed – a green flag indicates a fully successful runNote that you can view the files resulting from this processing run by clicking on the file names displayed in this screen. Clicking on the "Input file after processing" will show you any records that were not processed and the transaction_message tag that has been inserted to indicate why the record could not be processed.
3. Error handling
If the record does not pass validation it will remain in the input file and will not be processed. An xml transaction_message tag will be added to the xml record containing a message indicating why the journal record did not pass validation.
A record is also written to the log file in the reftracker/load/journalentry/log directory as a file named <inputfilename>_<today's date>.txt.
After processing, the input file, now containing only the records in error, each marked with a transaction_message tag describing how the record is in error, can be edited to correct the identified problems and the file can then be processed again. When you correct the input file, you should remove the transaction_message tag that describes the error that you fixed. When the records process successfully, they will be appended to the same file in the /archive directory, that that records from the same input file were transferred to in earlier processing runs.
Note – during processing the records that are loaded are output directly to the archive file, the records that failed validation are output to a temporary file. At the end of processing, the original input file is deleted and the temporary file is renamed to have the same name as the original input file. If there is an unexpected error during processing, it is important that you check for the existence of the archive file, and manually adjust the input file to reflect the records that were processed, in order to ensure that records are not loaded twice (because the renaming process was not undertaken due to the unexpected error).
4. Output from the Time and cost journal entry loading function
Records processed by the RefTracker Time and cost journal entry Loading function create journal entries in the specified question number. They can be viewed in the Journal tab of the Summary screen and, as normal journal entries, will be automatically include in the costs and hours reported by the RefTracker Statistics reports (including the Data extract records). So that the Time and cost journal entry Loader generated entry is obvious in the example below it was loaded with a noticeably earlier date making it appear at the very bottom of the History tab of the Summary screen example below.
5. Subdirectories in the RefTracker directory structure to support this function
reftacker/exchange/import/journalentry/input Place files for loading here
reftracker/exchange/import/journalentry/log Log files will be created here
reftracker/exchange/import/journalentry/archiveProcessed files placed here
reftracker/exchange/import/schema The files defining this process's XML schema