Adhoc data export
The RefTracker Adhoc data export function is accessed by clicking on System in the main header bar, then selecting Batch process menu, Data import/Export and then Adhoc data export.
It allows data to be selected from the RefTracker database and exported as an XML file. This is normally used to make data collected in RefTracker, available for use in other applications.
You can run the Adhoc data export definitions provided here, from this screen (using the “Run” button), OR, you can set up a regular schedule for running the export using System>Batch process menu>Background processing>Adhoc export process.
Some example export definitions have been provided with your RefTracker system. The Description column provides information about the purpose of each Export. Some of the standard Exports are typical of the uses that this function can be applied to – for example the “Staff Time Journals By Date Since Last Run” example export makes the time data accumulated in RefTracker since the last time this Export was run, available as an XML file suitable for importing into a staff time billing system (see following section describing a common use for this function in legal libraries).
The examples provided are general purpose Exports supported by Altarama, however with a little SQL expertise, or with the help of Altarama, this Adhoc data export function can be used to create any Export that you want to create. Check the System>Data dictionary screens for information about the SQL variables used in RefTracker.
Applications receiving data from RefTracker will generally have specific requirements about the data they need. Your IT staff can work with the owner of the receiving application to define the exact SQL statement needed to achieve the output required for your specific receiving application, or, for a fee, Altarama can work with them to define the SQL statement.
Some of the standard RefTracker-provided Adhoc data exports cannot be amended by you, but you can copy them to use as the basis for custom Exports.
The exported data is placed in a file in your reftracker/exchange/export/<Export ID#><Export_Name> directory, and given a file name of <Export_Name><date>_<time>.
A permanent record of the exported data file is also saved in RefTracker.
Parameter 0.5 Background process retention specifies the amount of time for which files created by the Adhoc data export process (either run manually or when this process is run by the Adhoc data export background process), will be retained. The Housekeeping routine will delete all files in the exchange/export sub directories (as that is where Adhoc data exports are placed) that were created before the period specified in this parameter, except for the most recent one – the system will always keep the most recently generated output from each Export process as this file can be referred to in future export runs in order to determine “date last run”.
If you are going to use the Adhoc data export function it is important that you set Parameter 0.5 Background process retention to a value that suits your needs.
Information about how to use the Adhoc data export function with Web Services/SOAP will be provided in an upcoming release.
Information about using Adhoc data export as a regularly scheduled automatic batch process is provided at System>Batch process menu>Background processing>Adhoc export process .
The following information talks about how to set up the Adhoc data export and run it manually, which needs to be done before setting up any automatic Adhoc data export schedule.
In the Export definitions tab:
Simple view provides a summary of the Adhoc data exports available in your system.
Advanced View allows new Exports to be defined and existing user defined Exports to be amended (see the following section that covers the details of how to use the Advanced View function)
The columns in these views are:
ID is the unique Export ID for this Export definition and is used in the name of the directory into which results of running this definition will be placed in order to ensure uniquely named directories. Name is the name of the Export that can be run. Description is a summary of what will be Exported and/or its purpose, as recorded by the designer of the Export.
Type advises whether the output will be in:
- “standard” XML format, or
- “exchange” format which is a hierarchical XML format that allows question data to be passed between RefTracker systems, or
- “EAD” format which is a standard output format called “Encoded archival description” that is used for passing item and providence information into Archive Management systems.
- “MARC” format which is a standard output format that can be used for passing item bibliographic information into an electronic library catalog system.
Output File Name is the core name of the file that will be created in the reftracker/exchange/export/<Export_ID#><Export_Name> directory by the Export (when the Export is run, the Export log will show the subdirectory and exact name of the file that was created by that run).
The exact file name used will be :<Output File Name><Sequence ID#>.xml
Query defines the SQL that will be run for this export (maximum length 2 million characters).
Created is when this Export definition was created, and by whom.
Last updated is when the Export definition was last changed, and by whom.
Using the export definitions
You can test a data export, i.e. run it without actually creating the output file, or having the fact that it was run recorded, by clicking on the Export test button for the appropriate Export. A common reason for performing test runs is to check the likely results of a run. Importantly, because Export test does not record that the Export was run, it will not impact on results of Exports that only select records since the last time the Export was run. Results of the Export test run are shown in the Results tab.
When you are ready to Run the Adhoc data export, i.e. create the output file, click on the Run button for that Export. The file will be created and a record of its creation will be shown to you in the “Export log” tab.
In the Export log tab:
Output to shows the subdirectory and file name to which the output was written. The file name is hyperlinked so you just need to click it to view it. The display is in XML format and you can easily save this file from this view – or you can use the “Download Exported files” link to save it.
Records shows the number of records exported to that file.
Exported by is the name of the staff member who ran this export.
Export date is the date and time that this Export was created.
Notes is any comment that has been recorded about this Export. Notes are recorded by clicking on the Edit icon in the next column.
View Exported Data – click this to view the contents of the file that was created. It displays in the “Results” tab. Note that the data in the exported file is in XML format – the results tabs shows it to you in a column format that is easily read, and can be easily copy and pasted into Excel (for statistical analysis).
Here’s an example of the Results tab display.
Using the exported data file
The file created by your Export can be found in the reftracker/exchange/export directory, in the subdirectory and with the file name shown in the Export log screen. You can click on the file name to view the file (and download it).
You can also use the “Download Exported Files” link to go to the File upload/download utility page where your exported files can be downloaded.
A copy of the Exported data has also been saved in the exchange/export/archive folder so you can remove files from this directory after you use them if desired. Use System>Utilities>Administration utilities>File upload/download.
The file is in XML format. Here’s an example of the data in the exported file:
If you need customized nodes used in your XML output, modify the Query of your Adhoc data export and use the SQL statement AS to change what appears in the output XML. For example, the following query:
Yields the below XML:
Additionally, please note that if either the question or answer fields had HTML in them when they were input/updated, the HTML formatting will be exported as well.
Using the exported data file to interface with other IT systems
Because the data export function is customisable within RefTracker, it can be customised to meet a wide range of needs for taking information collected in RefTracker out to other applications.
See the upcoming section called “Using the standard exports” for more information about uses for the distribution examples provided, and the “Using Advanced view” section for how to create a customised exports to meet your specific needs.
Ask your Altarama support representative for assistance, if you need more help than is provided in this section of the manual.
Using the Advanced view
The Export definitions Advanced View provides the ability to define new Exports to suit the needs of the specific application that you want to export data to. Interfacing with another application usually requires the skills of a programmer so this page is designed to be used by a programmer with SQL skills.
To add a new Export definition, click on the “Add” icon in the top line. The screen will redisplay with open boxes into which you can enter the details of your new Export.
In the Name column, enter a unique short name for your new Export.
In the Description column, enter a description of what this Export will export. You might like to include an example use for the Export.
In the Output file name column, enter the core name that will be given to all files generated by this Export. The generated files will be placed in the reftracker/export/<Export_ID#>_<Export_Name> directory.
In the Query column, enter the SQL that will be used to generate this Export. You can cut and paste the SQL from one of the standard queries and modify it to suit your needs, or you can write entirely new SQL. Information about the RefTracker tables in provided at System>Data dictionary. Should you need any help with where to find the data that you need in the RefTracker database, contact your Altarama support representative.
The example Exports utilise good practice, for example, where a code table has been used to define the contents of a field, both a column containing the code, and a column containing the description of that code from the code table, have been included in the output.
When you are happy with your new Export definition, click on the green tick update icon to save your definition or the changes you have made to it.
The screen will redisplay showing your new Export. Note that the system has automatically recorded that you created the Export definition, and when.
To make changes to your Export definition click on the Edit icon. The system will automatically update the “Last updated by” and “Last updated on” information when you change definitions.
There is also a delete function which is instigated using the garbage bin shaped icon. BEWARE – It will not only delete the definition but will also delete the output files and log records, and remove any batch process that uses it.
Using the Standard Exports
A number of exports are provided in every RefTracker system. Here’s how to use them:
Staff journals by date since last run
This routine provides all of the Journal records entered by all staff in this RefTracker system since this routine was last run. The purpose of this routine is to provide enough information about each time journal, in XML format, to allow it to be billed by an external time billing system.
To use this routine simply click the Run button. When the extraction is complete click the name of the new file added to the Export log to display the XML output and save it to an appropriate location, or use the Download exported files link in the top right of the screen to download the file name just added to the Export log.
Altarama and a legal library customer worked with Advanced Productivity Software (APS) to create this data export that matches the data input requirements for their DTE Time Management system batch upload function. Any data collected in the Request form for the question that was worked on (which for a legal library will include matter number and details of the person requesting the work) plus the name of the staff member doing the work and their staff number, the date and time that the work was done and the time they spent, is exported by this routine. However, as with all exports you can use this export to create a similar on to meet your specific need.
Journals per question
This routine provides all of the Journal records that have been recorded for a specific question number. The purpose of this routine is to provide enough information about the journals entered in relation to a specific question, in XML format, to allow them to be billed by an external time billing system.
To use this routine go to Advanced and replace the Question number in the SQL for that export, with the Question number for which you need the journals in XML format (edit it and save it). Click the Run button. When the extraction is complete click the name of the new file added to the Export log to display the XML output and save it to an appropriate location, or use the Download exported files link in the top right of the screen to download the file name just added to the Export log.
Journals for closed Q’s in date range
This routine provides all of the Journal records that have been recorded in relation to any question that was closed during the specified date range. The purpose of this routine is to provide enough information about the work done in relation to questions closed in the specified date range, in XML format, to allow them to be billed by an external time billing system.
To use this routine go to Advanced and replace the date range specified in the SQL for that export, with the date range for which you need the journals in XML format (edit it and save it). Click the Run button. When the extraction is complete click the name of the new file added to the Export log to display the XML output and save it to an appropriate location, or use the Download exported files link in the top right of the screen to download the file name just added to the Export log.
FAQ records
This routine provides question text, answer text, associated attachment hyperlinks, and date added to the FAQ subset of the KB, in an XML format that your web master can use to easily include this information as an FAQ list in a web page – just send the exported file to your web master whenever the copy in your web pages needs updating.
The file is sorted by date added to the FAQ with most recent at the top.
To use this routine simply click the Run button. When the extraction is complete click the name of the new file added to the Export log to display the XML output and save it to an appropriate location, or use the Download exported files link in the top right of the screen to download the file name just added to the Export log. The file always contains ALL questions in the FAQ database.
EAD export since last run
This routine provides a list of information about all questions closed since this routine was last run, in the standard XML based EAD (Encoded Archival Description) format used by Archivists. The purpose of this routine is to transfer item description and provenance information to Archive Management Systems like Archivists Toolkit, where this information has been gathered in RefTracker as a result of RefTracker being used to manage the correspondence in relation offers of donation or purchase of historically important manuscripts.
The format of the XML output from this function is controlled by
\config\exchange\EAD\schema\EAD.xsd
\config\exchange\EAD\settings\EADFieldMap.xml
This routine needs to be adjusted to meet the specific needs of your organisation (to make it outputting the fields that you are using in your RefTracker system for the fields that need to be transported to your EAD compliant system), so, to use this “EAD export since last run” export definition you will need to do the following system setup:
- Have a staff member who is familiar with the data being collected in RefTracker, and familiar with the data that is needed by your EAD compliant Archive system (most likely your RefTracker system administrator), indicate the fields they are using in RefTracker, that they want exported in EAD format, and the EAD fields that each should be imported into. A handy way to indicate what they want, for the benefit of the SQL programmer, is by writing the appropriate EAD field name for each RefTracker field that should be exported, onto a RefTracker Data extract report, or a screen print of the RefTracker Details tab.
- An SQL programmer (your own or a RefTracker support team member) will then edit the file \config\exchange\EAD\settings\EADFieldMap.xml to reflect the mapping described by the System administrator.
See the “Column/Variable name” in your RefTracker data dictionary screens for the variables associated with each field that needs to be exported.
See the config\exchange\EAD\settings\schema\EAD.xsd file for the fields that the EAD format can accept. The \config\exchange\EAD\schema\EAD.xsd file is a copy of the schema provided by the EAD web site http://www.loc.gov/ead/
Schema http://www.loc.gov/ead/eadschema.html - The \config\exchange\EAD\settings\EADFieldMap.xml file can be downloaded for editing, and uploaded again at System>Utilities>Administration utilities>File upload download, with Target folder \config. Choose the Exchange folder, then EAD and then Settings to find this file for uploading and downloading.
EAD format SQL programmer notes about the Field Map (config\exchange\EAD\settings\ EADfieldmap.xml)
The following example shows the Field map that corresponds to the data returned from the SQL statement below;
The mapfrom value is the RefTracker variable for the field that will be included in the XML output.
The mapto value is the EAD field that this RefTracker variable should be mapped to.
You can also use a dependent field mapping such as
<add mapfrom=”question” mapto=”descriptionType” dependent=”bib_udf_ta02″ mapvalue=”Biography” maptype=”namelink”></add>
In this example:
If a value exists in the depended field ‘bib_udf_ta02’ then the descriptionType tag will output with the value ‘Biography’
The field map MUST include a line for every RefTracker variable that is to be included in the EAD output.
When the \config\exchange\EAD\settings\EADFieldMap.xml file has been edited to include all the fields with data in them that need to be passed out in the EAD format, an SQL programmer will need to go to System>Batch process menu>Data Import\export>Adhoc data export and create an export definition.
EAD export SQL programmer notes about the Adhoc data export definition
The following example shows the Adhoc data export SQL statement that corresponds to the Field map above.
Create a new Adhoc data export definition at System>Batch process menu>Data Import\export>Adhoc data export using the Advanced function.
Set Type = EAD
The following SQL statement is an example of the SQL used to extract only records closed since the last export. To restrict the output to only records closed since the last export, it is important to use the correct data extract ID. The SQL statement MUST include every RefTracker variable that was included in the EADFieldMap;
MARC export since last run
This routine provides a list of information about all questions closed since this routine was last run, in the standard XML based MARC (Machine Readable Cataloging). The purpose of this routine is to transfer item description and bibliographic information to a MARCXML compliant library catalog system, where this information has been gathered in RefTracker as a result of RefTracker being used to manage the correspondence in relation to requests, donations, or purchase of unique items.
The format of the XML output from this function is controlled by
\config\exchange\MARC\settings\MARCFieldMap.xml
To use this “MARC export since last run” export definition you will need to do the following system setup:
- Have a staff member who is familiar with the data being collected in RefTracker and familiar with the data that is needed by your library’s catalog software (most likely your RefTracker system administrator), indicate the fields they are using in RefTracker that they want exported in MARC format, and the MARC fields that each should be imported into. They can indicate what they want, for the benefit of the SQL programmer, by writing the appropriate MARC field name for each RefTracker field that should be exported, onto a RefTracker Data extract report, or a screen print of the Details tab.
- An SQL programmer (your own or a RefTracker support team member) will then edit the file \config\exchange\MARC\settings\MARCFieldMap.xml to reflect the mapping described by the System administrator. If you only need bibliographic fields output, and you have used the RefTracker bibliographic fields for their intended purposes, you may not need to make any changes to this mapping.
See the “Column/Variable name” in your RefTracker data dictionary screens for the variables associated with each field that needs to be exported. - The \config\exchange\MARC\settings\MARCFieldMap.xml file can be downloaded for editing, and uploaded again at System>Utilities>Administration utilities>File upload download, with Target folder \config. Choose the Exchange folder, then MARC and then Settings to find this file for uploading and downloading.
MARC format SQL programmer notes about the Field Map (config\exchange\MARC\settings\MARCfieldmap.xml)
The following example shows the Field map that corresponds to the data returned from the SQL statement below;
The mapfrom value is the RefTracker variable for the field that will be included in the XML output.
The mapto value is the numerical MARC field that this RefTracker variable should be mapped to.
The ind1 value is the desired Indicator First value to be used in the output.
The ind2 value is the desired Indicator Second value to be used in the output.
The subfieldcode value is the desired Subfield Code value to be used in the output.
You can reference the Library of Congress website at http://www.loc.gov/marc/bibliographic/lite/ for help mapping the RefTracker fields to the appropriate MARC fields.
NOTE: Even though some MARC fields could potentially hold multiple subfields, RefTracker will only allow exporting one subfield with the exception of
- the 260 – Publication field where $a (Place of publication), $b (Name of publisher), and $c (Date of publication) fields are added using the RefTracker fields bib_pubplace, bib_pubname, and bib_pubdate respectively.
- the 020 – International Standard Book Number field where the $c (Price) is added using the RefTracker field bib_price_actual.
The field map MUST include a line for every RefTracker variable that is to be included in the MARC output.
When the \config\exchange\MARC\settings\MARCFieldMap.xml file has been edited to include all the fields with data in them that need to be passed out in the MARC format, an SQL programmer will need to go to System>Batch process menu>Data Import\export>Adhoc data export and create an export definition.
MARC export SQL programmer notes about the Adhoc data export definition
The following example shows the Adhoc data export SQL statement that corresponds to the Field map above.
Create a new Adhoc data export definition at System>Batch process menu>Data Import\export>Adhoc data export using the Advanced function.
Set Type = MARC
The following SQL statement is an example of the SQL used to extract only records closed since the last export and that have a Title in the bib_title RefTracker field. To restrict the output to only records closed since the last export, it is important to use the correct dataExtractLog_Extract_ID. The SQL statement MUST include every RefTracker variable that was included in the MARCFieldMap:
Exercise
To use and of these export routines simply click the Run button. When the extraction is complete click the name of the new file added to the Export log to display the XML output and save it to an appropriate location, or use the Download exported files link in the top right of the screen to download the file name just added to the Export log.
When you have finished examining the Adhoc data export function, go to System>Batch process menu>Email importing.