ADVANCED DATA MANIPULATION APPEND records Pirouette allows you to append (add on) records to the active database from another file. This may be either another .DBF file or an ASCII file. The current version of Pirouette does not allow data to be translated directly from other programs such as Lotus, which store data in a different format. However, the key word here is "directly". Any software package worth its salt will have some capability to write data to an ASCII format file -- also referred to as a Text file. For instance, to translate from Lotus, one simply has to arrange the data in its appropriate format, print it to an ASCII text file and use the Append command to read the data from the Text file. When the Append command is issued, you will be prompted for a file name. If you wish to see a list of files, use the DOS wildcards. For instance, to see all the files in your \WORDSTAR directory, enter \WORDSTAR\*.* as the file name. You will also be prompted for a file type. The three file types are .DBF, ASCII Columnar, and ASCII Delimited. If you select .DBF format, Pirouette will append all records from that database file. However, it will only append from fields which have the same name as fields in the active database. For example, if the active database contains the fields NAME and WORK_PHONE, and the database being appended from contains the fields NAME and PHONE_NO, only the field NAME will be captured. If you're dealing with an ASCII Text file, the process is a little more complicated. First off, data in a Text file may be formatted two different ways, so you'll have to tell Pirouette how that data is organized; either columnar or delimited. Columnar means that the data is arranted in columns, so that every field has the same width within the Text file. Consider a database containing lifetime home runs by major league baseball players: HENRY AARON 755 BABE RUTH 714 WILLIE MAYS 660 FRANK ROBINSON 586 HARMON KILLEBREW 573 REGGIE JACKSON 563 This format is most useful when appending data originally created with a spread sheet program. Delimited, on the other hand, is useful when importing data created on a word processor. Delimited means that a comma is used to separate the fields: -27- HENRY AARON,755 BABE RUTH,714 WILLIE MAYS,660 FRANK ROBINSON,586 HARMON KILLEBREW,573 REGGIE JACKSON,563 One problem with delimited ASCII occurs when a comma is itself part of the field (suppose we had listed the players names last name first, such as AARON, HENRY). In this case the string must be enclosed in quotes (i.e., "AARON, HENRY") or it will be treated as two items of data. Dates also need special treatment. When you append a date from an ASCII file, Pirouette expects it to be in the format YYYYMMDD. That is, the date July 4, 1776 must be written as 17760704 in order to be appended properly. Logical fields may be listed as T,t,Y,y for affirmative values, and F,f,N,n for negative values. Once you have designated the format of your data, you must indicate which fields will be receiving the data. At this point, a window will appear which accepts up to twenty field names. (As an alternative to typing in the field names, will display a list of the fields in the active database for your selection.) When you have selected all the desired fields, press and the append process will begin. When Pirouette is through appending, a tone will sound, and you will be returned to the Input Window. The appended data is then a permanent part of your database. FILTER data The filter command causes Pirouette to behave as if only certain records in the database were present; namely, those conforming to the filter condition. This command is particularly important because it works with the Update and Purge commands. To illustrate, suppose we open the ROLODEX and set the filter condition as: STATE="CA" Pirouette will behave as if the database contains only the records of people who live in California. This filter condition will stay in effect until you either close that database or use to explicitly remove the filter. For more information about formulas which can be used as filter conditions, see Appendix B, Functions and Formulas. Whenever a filter condition is in effect, the word Filter will appear in the top left corner of the screen. -28- PURGE database The Purge command is used to delete a group of records from the database, in contrast to Kill, which eliminates only one record at a time. Purge works closely with the Filter command. Once a Filter has been set, Purge deletes all records which do not meet that filter condition. For example, if the filter condition is LAST_NAME<>"JONES" and a Purge command is issued, Pirouette will delete all records where the last name is JONES. Since this offers the potential to delete large portions of your database, you will be asked for confirmation before the command is executed. TIP: Because Purge works with a Filter, you can set the Filter condition and then Browse your database to be sure that the Filter is correct before you issue the Purge command. This method provides a high degree of protection against accidental deletion of data. UPDATE records On some occasions, you may wish to change the information in a certain field for an entire group of records. To do this, first activate the appropriate Filter, so you are working with only the appropriate "sub-set" of your database. Then press . You will be prompted for the name of the field you wish to update. You will also be asked for a formula to substitute for the current value. Frequently, this "formula" may be a single, constant value. If the field in question is a character field, simply enclose the desired value in quotes (e.g., "SMITH"). A more complicated example would be when you wished to transform existing character data which is currently in upper/ lowercase, to all uppercase. If the field were called LAST_NAME, you would update the field, with the following formula. UPPER(LAST_NAME) Again, for more information on using formulas, see Appendix B, Functions and Formulas. -29-