Main Menu

SIS 2000+ Table Editor

A base table in SIS 2000+ is a list of codes, a description of the code, and in many cases, other fields which dictate any actions related to the code. Codes are used extensively throughout a relational database to facilitate data storage, save space, and for ease of use. The Table Editor maintains the information in the SIS 2000+ base tables. It is a tool used primarily by the technical support staff and administrators responsible for installing SIS 2000+ and maintaining the SIS 2000+ environment.  It will edit any table whose sntables.tecanedit field is > 0, and will dynamically reconfigure the display regardless of the number of fields in that table.

When a table is first selected (by selecting an entry in either of the Table combo boxes or by using the Find button), all fields of all existing records (rows) are displayed in a grid.  The column widths may be changed by dragging and the form can be widened or lengthened.  To make a change to a table, press the Edit button.  Another table can then not be selected until Undo, Done or Save is selected.

When a table is first selected, it is checked for NULL values.  If any are found, a message is displayed and the user is asked if the NULLs should be fixed.
 

1. Open the Table Editor
From the System Menu, click on the Table Editor icon button.

                                   Fig. 1 - main screen, read-only mode
 

Screen layout -
The default view is read-only, indicated by the gray-colored grid. The name of the table selected is displayed in the two Table fields in the upper left corner of the screen. The first field is an English description of the table; the second field is the actual table name as used in the database model. The table elements are displayed as a grid with columns for each field and rows for each record.

Default field formats -
The data in each column (field) is displayed in one of the following ways by default:

• Logical fields that are defined as decimal (1=On, 0=Off) are shown as check boxes. A check in the box means a value of "1", which is equivalent to "On" or "Yes". Unchecked boxes are equivalent to zero (Off, No).
• Fields whose names end in ‘color’ are shown as a rectangle of the appropriate color. Colors are used in fields as visual aids for some SIS 2000+ applications.
• All other fields are shown as text boxes, which can be alphanumeric or strictly numerical.

System administrator notes -
By modifying a local cursor created in the Load event, the default display format for any field can be changed.  The items that can be specified are:

• Display type:  textbox, colorbox, checkbox or combobox.  For comboboxes, the RowSource can be either a database table or a local cursor.
• Field is or is not required.
• Field is or is not modifiable by the user.
• If display type is checkbox, whether only one row can be checked.
• Whether at least one row with an empty value is required.

2. Find a Table

2.1 Using drop-down lists
Select a table to edit from either of two drop-down lists in the Table fields (Fig. 2).

Fig. 2

2.2 Using the Find command
Click on the Find command button to open the 'Find Table' form (Fig. 3); a master list of all editable tables in the system.

Fig. 3
 

2.2.1 Select a sort method
The list will be in ascending alphabetical order either by Description or Table Name, depending on which option is checked in the lower left radio buttons (Fig. 3).

2.2.2 Select a table to edit
Use the scroll bars to view the entire list. Click on the target entry in the list until it is highlighted, then click on the Select command button. The 'Find Table' form will close and the view will revert to the main screen with the target table displayed in the read-only mode.
 

Search Shortcut -
Instead of using the scroll bars to find a table in the list, type the first few letters of either the Table Name or the Description (depending on which sort method is selected) in the Search Description field (Fig. 4). The focus will jump to the first matching entry as the search string is entered. Once the target entry is found, click on the highlighted entry to activate the Select button.  Then click on the Select command button. The 'Find Table' form will close and the view will revert to the main screen with the target table displayed in the read-only mode.

Fig. 4

 
 

2.3 Fix Null fields?
A "NULL" in a field is a place holder data string that occurs when a table gets populated by a script (such as SQL) or when a new field is added to a table as a result of a model change in the database structure. Nulls should be fixed whenever possible. When the target table to view or edit is first opened and it contains NULL fields, a message will appear prompting the user to fix the null fields (Fig. 5). Answer "Yes" to fix or "No" to ignore the nulls. If "Yes", this action replaces NULLs in character fields with blank and in numeric fields with zero.

Fig. 5

3. Edit a Table
Click on the Edit command button to activate the edit mode of the selected table, indicated when the grid turns from gray to white (Fig. 6)

Depending on the size of the selected table, the default view may not show all of the rows and columns of data. Scroll right or left or up and down to view the rest of the data or use the mouse to expand the borders of  Table Editor window so that more rows and columns are visible.

Fig. 6  - edit mode, window resized, focus is on the Description field of  "In School Suspen" record.
 

3.1 Edit fields
Use the Tab key or the arrow keys on the keyboard to move the focus from row to row and field to field or click directly on the field to be edited until it is outlined in blue. There are several editing techniques depending on the type of field:

• Text fields -

Text fields can be alphanumeric, such as a code field (Fig. 7) or a description field (Fig. 8) or strictly numeric (Fig. 9). In some cases a text field will be in the form of a combo box (Fig. 10). Combo boxes limit the data that can entered in a field for the sake of referential integrity. Use standard word processing techniques to edit a text field - with the exception of combo boxes. Combo boxes are edited by selecting entries using a drop-down list activated by a field button. The field button will only be visible when the focus is shifted to that field.
 

Fig. 7

Fig. 8

Fig. 9

Fig. 10

If you change a code in a code field, the system will check referential integrity (See Step 3.3). If only the Description field is changed, any saved data in applications that reference this table will not change (as it is the code which is stored), but when displayed, the new description will appear in the related application.
 
• Logical fields -

If the default format has been set to a check box (Fig. 11), simply click on the box to check or uncheck it or use the space bar to toggle between checked or unchecked. If the format has been set to numerical, enter a "1" for "On"(Yes) or a "0" (zero) for "Off" (No).


Fig. 11
 

• Color fields -

Colors may be used in fields as visual aids for some SIS 2000+ applications. These type of fields,  whose names end in ‘color’, are shown as a rectangle of the appropriate color (Fig. 12).  Double click on the field to edit the color. This action will open a color map (Fig. 13). Select a color and then press "OK" to close the map and copy the color to the field (or Cancel to close the map without changing the color).
 
 

Fig. 12

Fig. 13

 
 
 

Click on the Save command button to save all modifications or Undo to cancel without saving. The view will revert to the read-only mode, displaying the newly modified data.

3.2 Add a Record to a Table



New records can be added to the current table so new codes can be recognized by various programs. Many tables contain codes that vary from district to district and school to school. New codes may be needed at any given time to track a specific instance of an activity.

Click on the Add command button to activate the entry mode. The focus will jump to the first blank row at the end of the list (Fig. 14). The input focus will default to the first field on the left (normally a code field).


Fig. 14 - entry mode

Initially, each field in a row will not have a value. Use the Tab key to move from field to field. Enter appropriate values in each field (Fig. 15).


Fig. 15



When all data is entered correctly, click on the Save command button to save the new record (or Undo to cancel without saving). The view will revert to the read-only mode, displaying the newly added record.

3.3 Delete a record in a table



Individual records of the current table may be deleted under certain conditions. SIS 2000+ is a complex relational database where tables are linked to other tables. Before a record can be deleted, its code value must not be used in any other table. The SIS 2000+ data dictionary knows which tables use codes from various other tables, and if a certain code is used by another table, you will not be permitted to delete the record.

 
In the edit mode, select the record to delete by clicking on the row until it is selected by a small, black pointer in the left side (Fig. 16).


Fig. 16 - record selected for deletion

Click on the Delete command button. The record will be removed immediately from the list. While still in the edit mode, repeat this process to delete more records, if required.
 

As a safety precaution, the deleted records are not actually deleted until a successful save is executed.

 



Click on the Save command button to save the deletions. Before the save completes, a referential check will be done by the system, indicated by a screen gauge (Fig. 17)


Fig. 17

Every table that references the current table will be checked to see if the value(s) you are attempting to delete are being used. If any tables are using the value(s), you will be informed by a warning message (Fig. 18) that the deletion (s) cannot take place.


Fig. 18

If this warning message occurs, click "OK" to close the message. The view will revert to the main screen in the edit mode. Now click on Undo to cancel the illegal deletions. The screen will change to read-only and the illegal deletions will be restored to the list. When more than one record is being deleted in the same action, any occurrence of illegal deletions in the action will also prevent legal deletions from being saved. For this reason it is advisable to delete only one record per save.

If no illegal deletions are encountered in a Save action, the deletion will proceed and the view will revert to the main screen without a warning message. The deleted record will be removed from the list.

 

The Check button performs the following checks on the current table:

• Values are specified for all required fields.  By default only primary key fields are required, but this can be overridden.
• Fields that are displayed by a combo box must have either an empty value (if allowed) or must have a value that currently exists in the foreign table.
• No two records can have the same values for the primary key fields.
• Any other checks that have been specifically written for this table.

If a warning message occurs after running a Check in the edit mode, click on "OK" to close the message. Reverse the illegal editing by clicking on the Undo command button. The view will revert to the read-only mode.
 

7. Print a table
Click on the Print command button to print a report (Fig. 20) of the currently selected table.

Fig. 19
 
In the 'Report Selection' dialog that appears (Fig. 19) choose a destination for the report by clicking in one of the radio buttons and then clicking "OK" to begin the action selected.

• Printer -

Print directly to the default printer.
• Display -

Show a print preview with option to print to default printer when the preview is closed.
• File -

Print to an ASCII text file. (No custom parameters available.)
• Cancel -

Abort printing.

Fig. 20 - sample report
 

8. Exit the Table Editor
From the read-only mode click on the Quit command button to exit the program.

Main Menu