CHAPTER 91 Exporting Records

Introduction

This chapter provides an overview of the techniques associated with exporting information out of IMSeries. IMSeries provides two types of export processes to help you exchange information between IMSeries databases (binary) and other applications and/or other IMSeries databases (text). Near the end of this chapter, is a discussion of how to use the export routines to synchronize multiple databases and to pre-determine the location and name of export files.

You can also import information into an IMSeries database in binary and text formats. The chapter "Importing Records" covers import procedures.

Text Exports

You will want to export information from IMSeries in text (or ASCII) form when you want to edit the exported data and/or when you want to import the exported data into another database or other computer application (e.g., word processor or spreadsheet).

When you want to export information that will be imported into another IMSeries database you could choose to export the data in either binary or text format. For information on exporting data in binary format, see "Binary Exports"

In order to export information in text format, you will use the Query Editor. By selecting the appropriate records and by creating templates that produce text that conforms to a required record structure, you can generate export files that enable you to send any data that IMSeries manages to any other system that allows you to import data in text (ASCII) form.

Before creating (or running) a query that produces a file that contains information you want to import into another application, make sure you are familiar with the import requirements of the other application. Your goal is to create a query that produces output that conforms to the needs of the system into which you will be importing the data.

Generally, this means that you need to know the record structure (i.e., the fields that are required, the sequence in which they need to be, and the character that serves as the delimiter (separator) between each field. You also need to know what character serves as the delimiter between each record. The following example will help illustrate how you can create a query to produce an export file that conforms to a given structure.

The example here illustrates how to create a delimited file. You can also create queries that produce fixed length records, where each field occupies a given number of characters. To do that, you need to use string functions to pad fields so they are each converted to the appropriate length.

Example: Exporting Student Absences

This example assumes that the computer system into which the data will be eventually imported requires the following information for each absence: the student's ID, the section ID in which the absence occurred, the type of absence recorded, and the date on which the absence occurred. Furthermore, the example assumes that the field order as listed above matches the requirements of the receiving system.

To create the query use the following steps.

1. Open a query list view by either of the following methods.

			Choose Query from the Select Menu, or press [Ctrl+G]PC or [Cmd+G]Mac. IMSeries displays a list view for queries and lists the existing queries.
			Double-click the query folder in the folder list while one of the folder views is active. IMSeries opens a list view for queries. Choose Select All Records from the Select menu or press [Ctrl+J]PC or [Cmd+J]Mac.
			To create a new query, choose New from the Record menu or press [Ctrl+N]PC or [Cmd+N]Mac.

2. IMSeries displays a new template for a query record and puts you in the add mode so you can create a new query. Complete the fields page of the query as illustrated in Figure 294.

FIGURE 294. Building a Query to Export Student Absence Records: Fields Page

A	Query Name		"Student Absence Export"
B	Output		Set the output to On Screen for now. After you have test run the query and are certain that it produces the output you want, you will change this setting to Save to Disk.
C	Categories		If you want, specify values for category 1 and category 2.
D	Comment		Insert a description of the query. The text in the sample reads as follows.
			"This query is designed to export student absence records that occur between two dates specified by the user.
			The query finds all the absence records for each student and, for each absence found, creates a record structure as follows:
			student ID [tab] section ID [tab] absence type [tab] absence date
			each field is separated with a tab and each record is separated with a return.
			The query prompts the user to specify the file name and location that will contain the output from the query."

3. Now, switch to the operations page of the query and create a folder sequence structure similar to the one depicted in Figure 295.

FIGURE 295. Query to Export Student Absence Records: Operations Page

A	Student		Drag the student folder into the process area from the list of folders.
B	Section		Highlight the Section folder in the list of folders and while pressing [Alt]PC or [Option]Mac, drag it to the process area. Position it over the student folder and drop it. IMSeries should indent it below the student folder as illustrated
C	Absence		Highlight the Absence folder in the list of folders and, while pressing [Alt]PC or [Option]Mac, drag it to the process area. Position it over the section folder and drop it. IMSeries should indent it below the student folder as illustrated.

4. Now build the operations for each folder in the process area.

5. Add the following operations for the student folder.

Operator	Details	Description
Procedure	Text1:=Request("Export Absences that occurred after which date?";text1)	This prompts the user to enter a date and assigns the user's entry as variable 'text1'. Even though the user is entering a date, this is a text variable. IMSeries will still be able to interpret it as you will see later.
	Text2:=Request("and that occurred before which date?";text2)	This prompts the user to enter another date and assigns the user's entry as variable 'text2'.
		Both operations can be included in the same procedure if you separate each with a return.
Procedure	QUERY([Absence];[Absence]Date>text1)*   QUERY([Absence]; & ;[Absence]Date<text2)	This procedure instructs IMSeries to search the absence folder (the first parameter) for all records whose date fields (the second parameter) have values greater than the date equivalent of the value of text1.
		The terminating asterisk at the end of the first query statement, tells IMSeries that there are more conditions to the search. That is, it tells IMSeries not to begin the search just yet.
		The second query statement instructs IMSeries to search the absence folder to find the records whose date fields contain values less than the date equivalent of text 2.
		The ampersand '&' placed between the first and second parameters, represents the 'and' conjunction.
		So, the combined statement tells IMSeries to find all absence records in the data base whose date fields contain dates greater than the value of text1 and less than the value of text2.
Put in Folder	Absence	This operation instructs IMSeries to put the records it finds into the absence folder. The query will ask for these records later.
		This is just a temporary storage place for the found records, so we do not have to find them again.
Relate Selection	Absence -> Student	This operation finds all the students who have had at least one absence.
		Now, the query 'knows' who missed at least one class in the time frame specified.

6. Add the following operation for the Section folder.

Operator	Details	Description
Relate Record	Student -> Section	Because the application into which the data will be imported needs to know the section in which each absence occurred, we need to find each student's schedule and then see which one(s) s/he was absent from.

7. Add the following operations for the absence folder.

Operator	Details	Description
Relate Record	Student -> Absence	This operation will find all the absences for the current student (The query will proceed from student-to-student until it has processed every student record that was found.
Relate Record	Section -> Absence   Add an Intersection Set Combination	This will find all the absences for the current section. When the intersection set combination is applied, IMSeries will see if the current student has missed any classes in the current section.
Get from Folder	Absence	Now, the query knows all the times the student has missed class for the current section, but it needs to determine whether or not s/he has missed any classes in the time frame specified.
	Add an Intersection Set Combination	So, the query needs to intersect the absences that occurred between the time frame the user specified, with the set of absences that it has found for the current student and current section.

8. Now, switch to the template page. This is where you will instruct the query to produce the output you want in the format you want it.

9. Highlight the absence folder in the process area and drag a body template from the template list ('A' in Figure 296) into the template area ('B' in Figure 296).

FIGURE 296. Inserting a body template

10. When you drag the body template into the template area, IMSeries displays a blank body template and an accompanying fields tool. Use the fields tool to select fields that you want to include in the template. Figure 297 illustrates this.

FIGURE 297. Details for the body template for the Absence Folder

A	[Student]ID		This field was inserted into the template first, because the computer application into which the data will be imported expects to read the student ID as the first field in the absence records in the import file.
			Insert [Student]ID by choosing Student from the pop-up in the field tool. ('D' in Figure 297). IMSeries displays all the fields for the student records. When you double-click on ID, IMSeries inserts [Student]ID into the template where the insertion point is.
B	Tab		After inserting the [Student]ID, press [Tab] to insert a tab character. In the figure, the template preferences have been adjusted to show invisible characters.
			To have IMSeries show invisible characters in document fields, choose Show Invisibles from the Edit Menu.
			The tab serves as a field delimiter. It will tell the application into which the file will be imported, to stop reading the student ID and prepare to read the next field in the field sequence.
C	Return		This is a return character. Insert it by pressing [Enter]PC or [Return]Mac. It serves as a record delimiter, and tells the application into which the data will be imported to stop reading one record and begin reading the next.
D	Field Tool - Folder Pop-up		To insert a field into the template, choose the folder that manages the corresponding records from this pop-up. IMSeries will display the fields in the list below.
E	Field List		When you select a folder from the pop-up above, IMSeries displays all the fields associated with records managed by that folder. To insert a field into the template, simply double click it.
			The fields that are added in the template include
			[Student]ID
			[Section]ID
			[Absence]Type
			[Absence]Date
			The names within the brackets '[" and ']' represent the folders, the names after the closing bracket ']' represent fields.
			Choose the folders from the pop-up, and then double click on the field to insert it into the template.

11. Close the body template and save the query.

12. Try running the query. If it produces the output you want, edit it and, on the fields page, change the output from On Screen to Save to Disk.

13. Finally, to speed up the query, click the Partial (faster) radio button, so the query does not have to update the screen for each record it finds.

14. Save the query.

15. When the query is run, the user will be prompted for two dates. IMSeries will find all the absences in the database between those two dates and produce a file containing the student ID, the section ID, the absence type and the absence date for each absence.

16. When it has found all the records, it will prompt the user to specify a file name and location into which it will place the information.

17. You should be able to import the information in the file into another application.

Binary Exports

Whenever you want to exchange information between two different IMSeries databases, and you want to include contents of document fields in the exchanged information, you will need to use the binary export features in IMSeries. When you choose this option, IMSeries controls all the formatting and content, you merely indicate which records you want to include in the export file.

When someone imports the file, IMSeries reads the content and displays the information in the file so s/he can determine whether or not s/he wants to complete the import process.

This section describes how to create a binary export file. The documentation covers the process used to import binary files elsewhere, see "Binary Import"

If you want to create a binary export file for a single occasion, you would initiate the process by making the folder that manages the records you want to export, including the records you want to export in the folder, and then choosing Export from the File menu. If you want to export the same or similar data repeatedly, you will want to create a query in which you include the binary export specifications. That way, you do not have to recreate the specification each time you want to export data. You would just run the query and have the query produce the export file.

The next section describes how to specify which records to include in the binary export file regardless of which technique you use.

Example: Exporting Instructional History for a Student

Suppose you want to export all pertinent instructional information for a student who is transferring to another school in the district. You want the receiving school to be able to import (in their IMSeries database), the relevant demographic information on the student, as well as all of the lesson plans, assessments, and grades the student has received while s/he has been enrolled in your school.

To do this on a single-event basis, you would use the following steps.

1. Make the student folder active and find the record for the student for whom you are going to create the binary file.

2. Open a list view for the students and highlight the student's name.

To open a list view for a folder, choose List from the View Menu or from the folder level pop-up menu. Alternatively, you can press [Ctrl+L]PC or [Cmd+L]Mac.

3. Click Select to put that student's name, and only that student's name into the student folder and close the list view.

4. Choose Export from the File Menu. IMSeries displays a binary export file dialog box. A picture of the dialog box appears in Figure 301.

Notice that the Student folder is expanded in the figure. This is because the student folder was the active folder when the user chose Export from the File menu.

FIGURE 298. Standard Binary Export Dialog Box (Records Page)

A	Folder List		Here, IMSeries displays all the folders that you can view with your current account. If you click on the expansion box to the left of the folder, IMSeries will display every folder that is directly related to it.
			IMSeries automatically expands the folder that corresponds to the active folder so you can quickly select directly related records to include in the export file if you wish.
B	Related Folders		Activate the check box next to each folder that manages records you want to include in the export file.
			The folders that are displayed are those that are directly related to the folder you just expanded.
C	Included Folders		IMSeries will export records from folders that have check marks next to them. The records that are included in the export file are those that are related to other records that are already included in the export specification.
D	Select All		If you want to create a binary export file that contains every record in the open database, click Select All.
			This is an excellent way to move entire sets of data from one database (e.g., a 'live' database) to another (e.g., an archival database).
	Clear All		When you click Clear All, IMSeries removes all the check marks from all the folders. This is equivalent to clearing out the export specification.
E	Mandatory / Dependent		The Mandatory / Dependent button gives you a convenient way to include, in the export specification, all records (from other folders) that are dependent on records you have already selected.
			For example, if the objective folder is active when you choose Export from the File menu and you click Mandatory/Dependent, then IMSeries automatically includes the units, activities, and resources that are related to the selected objectives.
			That is, IMSeries automatically selects records upon which the records that have already been selected depend.
F	Selection only for Current Folder		When you activate this check box, regardless of what records are included in the export specification, IMSeries will not export records of the type managed by the active folder unless the records are in the active folder when you start the export process.
			An example is in order. The example being discussed here posits the creation of an export file for a single student.
			Suppose that, as you build the export specification, you include all the lessons taught to the student whose record is in the student folder.
			If you then go to the lesson plan folder and expand it, and check related students, you are indicating that you want to include all the students who received the plan: not just the student whose record you are exporting.
			If you have checked the 'Selection only for Current Folder', IMSeries would limit the exported data to just the student whose record is in the student folder.
G	Export		To initiate the export process, click Export. IMSeries displays a file locator dialog box. Use this dialog box to supply a file name and location for the binary export file that IMSeries will create.
	Cancel		To abort the export process, click Cancel.

5. Once you have marked all the records you want to include in the binary export file, click Export.

6. IMSeries will display a file locator dialog box. Use this box to provide a name and location for the file you will be creating.

A binary export file contains all the information that IMSeries needs in order to import the data. So, you can safely send the binary file to another site where IMSeries is being used. As long as the user at the other site has permission to add or edit all the record types that re in the import file, s/he can import the file easily.

Including Binary Export Specification in Queries

If you find that you will be exporting similar data on a regular basis, you probably do not want to go through the trouble of defining a new export specification each time you need to create a new binary export file. You can automate the creation of binary export files by including the export specifications into queries.

For example, it is possible that, throughout the year, several students might transfer from one school to another in your district. Instead of creating a new export specification each time a student transfers, you could write a query that prompts the user to identify which student s/he wants to export records for and have the query select and export the related records. The following text describes how you can do this.

1. Open a list view of queries by choosing Query from the Select Menu, or by pressing [Ctrl+G]^PC or [Cmd+G]^Mac.

2. Prepare to add a new query by choosing New from the Record Menu, or by pressing [Ctrl+N]^PC or [Cmd+N]^Mac.

3. In the appropriate fields on the fields page of the query record, enter a name for the query and give a detailed description of what it does.

FIGURE 299. Creating a Query to Create a Binary Export File: Fields Page

A	Name		Enter the name of the query in this field.
B	Output		You want the query to generate a binary file, so it will create no viewable documents. Choose Save to Disk from the Output Pop-up.
C	Categories		You can specify that the query is a certain type by choosing an option from the Category 1 and Category 2 choice lists.
D	Comments		In this text field, you should describe the query. What it does and how the user should use it.

4. Next, switch to the operations page and include the student folder in the process area.

FIGURE 300. Creating a Query to Create a Binary Export FIle: Operations Page

A	Student Folder		Drag this folder from the list of folders ('B') into the process area to include it in the query.
B	Folder List		IMSeries displays the list of folders that you can view with your account in this list. Drag a folder into the process area to include it in the query.
C	Procedure Operation 1		Drag the procedure operator into the operations area. IMSeries will display the procedure editor.
			Use the procedure editor to add a function that requests the user to input a student ID. To do this, type   "Text1:=Request("Enter the ID for the student whose records will be exported";Text1)"
	Procedure Operation 2		Drag the procedure operator again into the process area. In the procedure editor, type   "Query([student];[student]id = text1)" This operation directs IMSeries to search the records in the student folder and return the record that has the value of text1 in the ID field.
D	Export Operation		Now that the query has found the student record you want to export, set up the export specification.
			Drag the Export operator into the process area. IMSeries displays the binary export dialog box (see Figure 301 on page 834).
			Complete it in the same way as you did in the See "Example: Exporting Instructional History for a Student"
			When you have finished using the binary export dialog box to indicate which records related to the student you want the query to include in the export. click OK. IMSeries will embed the export specification in the query.
			If you save the query, then you can run it anytime you want to create a binary export file for any student.

Export Dialog box in Queries

To create a query that you can use over and over to create binary export files of selected records, first direct the query to find the initial records you want to include in the binary export file. Then, drag the export operator into the operations area and indicate, in the binary export dialog box, any other records that you want to include in the export file. You can use the binary export dialog box to include any record in the export file. Using the initial records as a start, specify which other records you want IMSeries to include in the export by selecting records from related folders.

When you include the export operation in a query, the dialog box is slightly different than the one displayed when you export information 'manually'. Compare Figure 298 on page 827 with Figure 301 on page 834. Note that, when you insert the export operation in queries, the Export and Cancel buttons no longer are present. Instead, there are two check boxes "Show Interface and Status" (F in Figure 301) and "Wait for Export Completion" (G in Figure 301).

FIGURE 301. Binary Export Dialog Box in a Query

When you activate the "Show Interface and Status" check box, IMSeries will display the status of the import process as it occurs. If you activate the "Wait for Export Completion" check box, then IMSeries will wait until the export is completed before it executes any other operation.

Controlling destination of the Export

By making the Destination Page of the Export dialog box active, you can pre-specify where IMSeries will send the data that are exported during the export operation. There are two options. First, if you specify a file name and location (B in Figure 302), then IMSeries will place the exported information in the specified file and at the specified location. C in Figure 302 illustrates this.

FIGURE 302. Standard Binary Export Dialog Box (File as Destination)

First, make the Destination Page of the Export Dialog Box active by clicking on the Destination tab.If you activate the 'File' Radio button and click Set File, you can use the standard file locator dialog box to navigate to the location where you want IMSeries to store the information it exports during this specific export operation.

You will be asked to select a file or specify a file name. If the file already exists, you will be asked if you are sure you want the contents to be overwritten when the export process occurs.

If you specify a Direct database connection instead of the File option and specify the URL or IP address of the 'target' database, then IMSeries will export the data specified in the dialog box to that database during the export process. This is illustrated in Figure 303.

Automating Data I/O and Synchronization

If you add a wait operator to the query and run it as a background process, then IMSeries can synchronize multiple databases automatically depending on the settings in the Wait operation. For a more detailed discussion of the Wait operator, see "Wait"

FIGURE 303. Standard Binary Export Dialog Box (IMSeries Database as Destination)

The user name / password combination (C in Figure 303) are sent with the data to the target database. The account in the target database that corresponds to the user name / password sent with the data, controls whether or not the data can be added to that database. In other words, if the account does not allow new student records to be added, and if the export file contains student records, then the target database will refuse the update.

Learning Technology Systems, Inc. http://www.imseries.com Voice: (865) 560-0261 Fax: (865) 769-5604 sales@imseries.com