CREATING A DATABASE To create a new database, you must decide upon a name and then format an Input Window. If you are working with an existing .DBF file previously created with dBASE III or some other program, it is not necessary to go through this process; a default Input Window will be created automatically the first time you view the data (though you may not like the way it looks). At any time in the future when you wish to change the database structure or the Input Window, you can use the process outlined below. Begin by selecting MODIFY from the opening menu. Next, select "New File" from the list of databases (or the name of your existing file, if you're making revisions). You will then be prompted for a file name. The file name must meet the following criteria: o it may be no more than eight characters; o it should not include a DOS file extension, since the .DBF extension will automatically be assigned to the file; o the first character must be a letter; o subsequent characters may be letters, numbers, or the underscore ("_"). No spaces or other characters are allowed. Once a valid file name has been provided, the Formatting Window will appear. FORMATTING THE INPUT WINDOW As mentioned in the introduction, the best way to think of the Input Window is as an index card. If you were arranging your data in a card file, you would take a stack of index cards and arrange the information the same way on each one. The Formatting Window is where you design that format. TECHNICAL NOTE: The Formatting Window actually does two things; it allows you to create or modify the structure of the .DBF file, and it creates an associated file with the same name and a .WDW file extension. The .WDW file contains no data itself, merely the information on how the data should be displayed in the Input Window. The Formatting Window functions somewhat like an office bulletin board: you create objects and "tack" them on the screen at the desired position. You can then move them, edit them, or delete them. The following keys allow you to navigate around the Formatting Window: -14- Formatting Window - Navigation Keys Key Result ---------- ---------- cursor down down one line cursor up up one line cursor left left one column cursor right right one column PgDn down ten lines PgUp up ten lines Home move to first column End move to last column Tab left 15 columns space insert one column Del delete one column Ctrl-Enter insert one row Ctrl-Y delete one row Three kinds of objects can be placed upon the Input Window: text, data fields, and calculations. The commands which deal with each of these objects are discussed below. enter TEXT Text is included on your input screen to provide information for the person doing the data entry. The most common example is the field description which accompanies each data field in the sample databases. If you are creating a system for use by someone else, you might also use the Text command to enter basic information about the command keys ("Use to Quit, for instance), so that person doesn't have the consult the Help window. To place text objects on your screen, move the cursor to the location where you wish the first character of the text string to appear. Then press (or, alternatively, and ). A window will pop up, into which you can enter up to 50 characters of text. When you press , the text string will appear on the screen. If you press by accident, just press . enter a NEW field To add a new field to your database, move the cursor to the appropriate position and press . A window will appear which requires you to enter several items of information: o Field Name. Field names may be up to ten characters. The first character must be a letter. The other nine characters can be letters, numerals, or the underscore. Spaces are not allowed within a field name, just as they are not allowed in file names. The underscore is often used in their place (e.g., HOME_PHONE). -15- o Data Type. The four data types are discussed in the previous section on database concepts. They are Character, Numeric, Date, and Logical. o Field Length. If you select Date or Logical as the data type, the field length will be assigned automatically. If you select Character or Numeric, you must specify the length. This should be the length of the longest entry you will make in that field (you can change the length later if you need to). When selecting the length for a numeric field, remember to include one position for the minus sign (if you will be entering negative values) and one position for the decimal point. You will also get a prompt asking for the "# of decimals". Enter the number of digits that should appear to the right of the decimal point. Selecting a length for a character field is straight- forward. However, if you create a character field of more than 60 spaces, you will be unable to see the entire contents of the field at once. The screen is simply not wide enough to display very long fields, and to read or edit them you will have to use the cursor to scroll to the right. Within the Formatting Window, these fields will appear as if they are only 60 characters in length, even though more space is allocated within the database. o Input Restriction. It is also possible to place very specific restrictions upon the data being entered. These restrictions can be used to force the data into a particular form (e.g., all capital letters) or to prevent the entry of erroneous values. Input restrictions are discussed in more detail a little bit later. enter a CALCULATION The third kind of object which can appear in an input window is a calculation. Calculations are expressions of some data type (see Appendix B, Functions and Formulas), usually involving one or more data fields. One example of a calculations is found in the CALENDAR. It is helpful, in an appointment calendar, to know the date of the week corresponding to each event. In this case, we have used the following function: CDOW(DATE) This displays the date of the week (as a character string) corresponding to the field DATE in that record. -16- Another common use is to show totals or percentages. If your database involved retail purchases, you might wish to have a calculation which would display the appropriate sales tax on screen (.07*BASE_PRICE, if the tax rate were 7%), or the total purchase price (1.07*BASE_PRICE). MANIPULATING OBJECTS After you have created fields, calculations, and text strings, you will undoubtedly wish to manipulate them. By manipulate we mean change them, move them, or get rid of them all together. The formatting window has commands which allow you to perform all three of these tasks. EDIT an object To revise an existing object, move the cursor to some point on that object and press . The appropriate window will pop up to allow you to make changes in the field, calculation, or text string. If the object is a field, however, there are limitations as to the changes you can make. Only the field length (including the number of decimals, if it's numeric) and input restrictions can be changed. The field name and its data type cannot be altered. If you need to change either of these attributes, you must kill the field and create a new one. Which brings us to the next topic. KILL an object To kill an existing object, move the cursor to that object and press . Text and calculations will be deleted automatically. For data fields, however, a window will appear which will require you to confirm your intentions. DRAG an object To move, or drag, an object to another location in the formatting window, move the cursor to that object and press . The object will begin to blink. Move the cursor to the place where you wish the first character of the object to appear and press . The screen will be repainted with the object at its new location. To abort this process after you start to Drag an object, press . -17- RESTRICTING INPUT At the time you create (or edit) a field, you will be asked whether or not to create (or modify) an input restriction. If you answer "YES", Pirouette will give you a choice of three ways to do so: LIST, MASK, and RANGE. A fourth choice, NONE, allows you to remove an existing input restriction. Each of the three methods is described below: List Restricting input by List connects that field to a corresponding field in another database. Only values which already exist in the second database may be entered into this restricted field. As mentioned in the introduction, this is a very powerful tool for preventing data entry error. When you select List, a menu will pop up displaying all other databases in the current DOS directory. Select one from this list. Still another menu will pop up listing the fields in that database, so that you can select the appropriate field. Needless to say, this requires you to either create the restriction database first, or else come back later and edit the field to create the restriction. An example of a List restriction is found in the ROLODEX. Here, the field RELATNSHIP is restricted to a field of the same name in the database LISTS. LISTS is simply a three-record list containing the values Customer, Vendor, and Personal. Thus, only these three values can be entered into the Relationship field in the ROLODEX (unless, of course, you add other records to the list). Mask A mask allows only certain characters to be entered into certain places within the field. A variety of template symbols and functions are used, each of which is described in Appendix B. (If you're a user of dBASE, you'll be pleased to know that the same symbol system has been used.) Just to whet your appetite, though, here are a couple of examples. The template symbol # restricts input to only numerals or spaces for that particular character. Thus, a mask for a telephone number field might look like this: ###/###-#### If you wished to restrict input to all capital letters, you would use the ! function: @! This topic is covered in depth in Appendix A. -18- Range The final type of input restriction is by range. This is most commonly used for Date fields, though it can also be used for Characters or Numerics. When Range is selected, you will be asked for an upper and lower limit. Only values between those limits will then be accepted. If you wanted to ensure that all checks entered into your check register were from 1990, for instance, you would just restrict by range, with the lower limit being 1/1/90, and the upper limit being 12/31/90. RELATING DATABASES Creating a relational database structure, as discussed in the section on Database Concepts, is no more complex than creating the individual databases. Merely specify the same Name, Type, and Length for the common field when you create the related databases (in our sample databases, CHECKREG and PAYEES, this common field is PAYEE_NAME). Methods for entering data into related databases, and for printing that data out on reports, will be described as we go on. SAVING THE NEW DATABASE To close the formatting window and return to the opening menu, press . If you have made changes to the database structure (i.e., the fields themselves), you will be asked whether or not you wish to save the new structure. If you answer "NO", the original database structure, along with any data in your database, will be preserved. If you answer "YES", and you have killed fields which previously existed, the data which was stored within those fields will be discarded with no chance of recovery. -19-