PRO-dVelBase (TM) Lite version 1.0 Reference Guide copyright (C) 1992 Accelerated Software 15910 - 112A St. Edmonton, Alberta Canada T5X 4X3 Phone: (403) 456-9163 Fax: (403) 457-3582 Page I T a b l e o f C o n t e n t s =============================== Chapter 1: Introduction . . . . . . . . . . . . . . . Page 1 A. Registration . . . . . . . . . . . . . . . . . Page 1 B. Technical Support . . . . . . . . . . . . . . . Page 1 C. How to Use This Documentation . . . . . . . . . Page 1 D. Technical Specifications of PRO-dVelBase . . . Page 2 Database Specifications . . . . . . . . . . . Page 2 Forms . . . . . . . . . . . . . . . . . . . . Page 2 Fields . . . . . . . . . . . . . . . . . . . Page 3 Tables . . . . . . . . . . . . . . . . . . . Page 3 Queries . . . . . . . . . . . . . . . . . . . Page 3 Reports . . . . . . . . . . . . . . . . . . . Page 3 Labels . . . . . . . . . . . . . . . . . . . Page 4 Mail . . . . . . . . . . . . . . . . . . . . Page 4 Functions and expressions . . . . . . . . . . Page 4 Searches . . . . . . . . . . . . . . . . . . Page 4 Utilities . . . . . . . . . . . . . . . . . . Page 4 E. System Requirements . . . . . . . . . . . . . . Page 4 F. Compatibility . . . . . . . . . . . . . . . . . Page 4 G. Installation and Running PRO-dVelBase . . . . . Page 5 H. Help . . . . . . . . . . . . . . . . . . . . . Page 6 I. Basic Principles . . . . . . . . . . . . . . . Page 6 Chapter 2: Form . . . . . . . . . . . . . . . . . . . Page 8 A. Form/Create . . . . . . . . . . . . . . . . . . Page 8 Database Fields . . . . . . . . . . . . . . . Page 12 Form: Fixed Field Definitions . . . Page 12 Form: Changeable Field Definitions Page 18 Calculated Fields . . . . . . . . . . . . . . Page 22 Calculated Field Definitions . . . Page 22 Summary Calculated Fields . . Page 22 Formula Calculated Fields . . Page 22 Editing Text and Fields . . . . . . . . . . . Page 24 Field Identification Characters . . Page 24 Moving Text and Fields . . . . . . Page 24 Blocking Text and Fields . . . . . Page 24 Editing Field Definitions . . . . . Page 25 Drawing Boxes . . . . . . . . . . . Page 25 IBM Extended Graphics Characters . Page 25 Moving to Next/Previous Screen . . Page 25 Deleting Fields . . . . . . . . . . Page 25 Multi-File Forms . . . . . . . . . . . . . . Page 26 Foreign Database Relationship Definitions . . . . . . . . . . . . Page 26 Scroll Window . . . . . . . . . . . Page 28 Steps to Removing Relationships . . Page 29 Saving a Form . . . . . . . . . . . . . . . . Page 29 B. Form/Modify . . . . . . . . . . . . . . . . . . Page 29 C. Form/Select . . . . . . . . . . . . . . . . . . Page 30 D. Form/Execute . . . . . . . . . . . . . . . . . Page 31 Page II Appending and Editing Records . . . . . . . . Page 33 Editing Character Fields . . . . . Page 34 Editing Numeric Fields . . . . . . Page 34 Editing Logical Fields . . . . . . Page 35 Editing Single Key Fields . . . . . Page 35 Editing Date Fields . . . . . . . . Page 35 Editing Time Fields . . . . . . . . Page 35 Editing Memo Fields . . . . . . . . Page 35 Editing Fields in a One-to-Many Area Page 36 Table View . . . . . . . . . . . . . . . . . Page 36 Altering Table View . . . . . . . . Page 37 Editing and Appending in Table Mode Page 38 Indexed Table Mode . . . . . . . . Page 39 The Search Menu . . . . . . . . . . . . . . . Page 40 Goto . . . . . . . . . . . . . . . Page 40 Search . . . . . . . . . . . . . . Page 40 Search & Replace . . . . . . . . . Page 42 Search & Delete . . . . . . . . . . Page 42 Search & Undelete . . . . . . . . . Page 43 E. Form/Unselect . . . . . . . . . . . . . . . . . Page 43 F. Form/Delete . . . . . . . . . . . . . . . . . . Page 43 Chapter 3: Query . . . . . . . . . . . . . . . . . . . Page 45 A. Query/Create . . . . . . . . . . . . . . . . . Page 45 Query Editor . . . . . . . . . . . . . . . . Page 46 To Select Fields for Viewing . . . . . . . . Page 48 To Sort the Fields . . . . . . . . . . . . . Page 49 Saving The Query . . . . . . . . . . . . . . Page 49 B. Query/Modify . . . . . . . . . . . . . . . . . Page 50 C. Query/Select . . . . . . . . . . . . . . . . . Page 50 To Select a Query . . . . . . . . . . . . . . Page 50 D. Query/Execute . . . . . . . . . . . . . . . . . Page 51 E. Query/Unselect . . . . . . . . . . . . . . . . Page 51 F. Query/Delete . . . . . . . . . . . . . . . . . Page 52 G. Query/Add to Cat. . . . . . . . . . . . . . . . Page 52 To Add a Query to a Catalog . . . . . . . . . Page 52 H. Query/Remove from Cat. . . . . . . . . . . . . Page 52 To Remove a Query from the catalog . . . . . Page 53 Chapter 4: Report . . . . . . . . . . . . . . . . . . Page 54 A. Report/Create . . . . . . . . . . . . . . . . . Page 54 Report Editor . . . . . . . . . . . . . . . . Page 56 Field Placement . . . . . . . . . . . . . . . Page 57 One-to-many Relationships . . . . . . . . . . Page 61 Group Expressions . . . . . . . . . . . . . . Page 62 Saving the Report . . . . . . . . . . . . . . Page 62 B. Report/Modify . . . . . . . . . . . . . . . . . Page 62 C. Report/Select . . . . . . . . . . . . . . . . . Page 62 D. Report/Execute . . . . . . . . . . . . . . . . Page 63 E. Report/Unselect . . . . . . . . . . . . . . . . Page 63 F. Report/Add to Cat. . . . . . . . . . . . . . . Page 63 G. Report/Remove from Cat. . . . . . . . . . . . . Page 63 H. Report/Delete . . . . . . . . . . . . . . . . . Page 63 Page III Chapter 5: Mail . . . . . . . . . . . . . . . . . . . Page 64 A. Mail/Create . . . . . . . . . . . . . . . . . . Page 64 B. Mail/Modify . . . . . . . . . . . . . . . . . . Page 66 C. Mail/Select . . . . . . . . . . . . . . . . . . Page 66 D. Mail/Execute . . . . . . . . . . . . . . . . . Page 66 E. Mail/Unselect . . . . . . . . . . . . . . . . . Page 66 F. Mail/Add to Cat. . . . . . . . . . . . . . . . Page 66 G. Mail/Remove from Cat. . . . . . . . . . . . . . Page 66 H. Mail/Delete . . . . . . . . . . . . . . . . . . Page 67 I. Mail/Import Text File . . . . . . . . . . . . . Page 67 Chapter 6: Label . . . . . . . . . . . . . . . . . . . Page 68 A. Label/Create . . . . . . . . . . . . . . . . . Page 68 B. Label/Modify . . . . . . . . . . . . . . . . . Page 72 C. Label/Select . . . . . . . . . . . . . . . . . Page 72 D. Label/Execute . . . . . . . . . . . . . . . . . Page 72 E. Label/Unselect . . . . . . . . . . . . . . . . Page 72 F. Label/Add to Cat. . . . . . . . . . . . . . . . Page 72 G. Label/Remove from Cat. . . . . . . . . . . . . Page 72 H. Label/Delete . . . . . . . . . . . . . . . . . Page 72 Chapter 7: Utilities . . . . . . . . . . . . . . . . . Page 73 A. Append . . . . . . . . . . . . . . . . . . . . Page 73 Append/Create . . . . . . . . . . . . . . . . Page 73 Append/Modify . . . . . . . . . . . . . . . . Page 74 Append/Select . . . . . . . . . . . . . . . . Page 74 Append/Execute . . . . . . . . . . . . . . . Page 75 Append/Unselect . . . . . . . . . . . . . . . Page 75 Append/Add to Cat. . . . . . . . . . . . . . Page 75 Append/Remove from Cat. . . . . . . . . . . . Page 75 Append/Delete . . . . . . . . . . . . . . . . Page 75 B. Post . . . . . . . . . . . . . . . . . . . . . Page 75 Post/Create . . . . . . . . . . . . . . . . . Page 76 Post/Modify . . . . . . . . . . . . . . . . . Page 77 Post/Select . . . . . . . . . . . . . . . . . Page 77 Post/Execute . . . . . . . . . . . . . . . . Page 77 Post/Unselect . . . . . . . . . . . . . . . . Page 78 Post/Add to Cat. . . . . . . . . . . . . . . Page 78 Post/Remove from Cat. . . . . . . . . . . . . Page 78 Post/Delete . . . . . . . . . . . . . . . . . Page 78 C. Setup . . . . . . . . . . . . . . . . . . . . . Page 78 D. Re-index . . . . . . . . . . . . . . . . . . . Page 79 E. Database Pack . . . . . . . . . . . . . . . . . Page 79 F. Clone . . . . . . . . . . . . . . . . . . . . . Page 79 G. Zap . . . . . . . . . . . . . . . . . . . . . . Page 79 Chapter 8: Options . . . . . . . . . . . . . . . . . . Page 80 A. Change Dir . . . . . . . . . . . . . . . . . . Page 80 B. Recalc . . . . . . . . . . . . . . . . . . . . Page 80 C. Flush . . . . . . . . . . . . . . . . . . . . . Page 80 D. Printer Setup . . . . . . . . . . . . . . . . . Page 80 Printer Setup/Create . . . . . . . . . . . . Page 81 Printer Setup/Modify . . . . . . . . . . . . Page 81 Printer Setup/Delete . . . . . . . . . . . . Page 82 Page IV E. Save WorkArea . . . . . . . . . . . . . . . . . Page 82 F. Quit . . . . . . . . . . . . . . . . . . . . . Page 82 Appendix A: dBASE (TM) Naming Conventions . . . . . . Page 83 Appendix B: Formulas and Functions . . . . . . . . . . Page 84 A. Formulas . . . . . . . . . . . . . . . . . . . Page 84 B. Constants . . . . . . . . . . . . . . . . . . . Page 84 Numeric Constants . . . . . . . . . . . . . . Page 84 Character Constants . . . . . . . . . . . . . Page 84 Logical Constants . . . . . . . . . . . . . . Page 85 Date Constants . . . . . . . . . . . . . . . Page 85 C. Operators . . . . . . . . . . . . . . . . . . . Page 85 D. Functions . . . . . . . . . . . . . . . . . . . Page 88 ABS(Num_Value) . . . . . . . . . . . . . . . Page 88 ASC(Char_Value) . . . . . . . . . . . . . . . Page 88 AT(Char_Value1, Char_Value2) . . . . . . . . Page 88 CDOW(Date_Value) . . . . . . . . . . . . . . Page 88 CHR(Num_Value) . . . . . . . . . . . . . . . Page 88 CMONTH(Date_Value) . . . . . . . . . . . . . Page 89 CTOD(Char_Value) . . . . . . . . . . . . . . Page 89 DATE() . . . . . . . . . . . . . . . . . . . Page 89 DAY(Date_Value) . . . . . . . . . . . . . . . Page 89 DBF() . . . . . . . . . . . . . . . . . . . . Page 89 DEL() . . . . . . . . . . . . . . . . . . . . Page 89 DELETED() . . . . . . . . . . . . . . . . . . Page 89 DOW(Date_Value) . . . . . . . . . . . . . . . Page 89 DTOC(Date_Value) and DTOS(Date_Value) . . . . Page 89 EXP(Num_Value) . . . . . . . . . . . . . . . Page 90 FIELD(Num_Value) . . . . . . . . . . . . . . Page 90 IIF(Condition, Expression1, Expression2) . . Page 90 INT(Num_Value) . . . . . . . . . . . . . . . Page 90 ISALPHA(Char_Value) . . . . . . . . . . . . . Page 90 ISLOWER(Char_Value) . . . . . . . . . . . . . Page 90 ISUPPER(Char_Value) . . . . . . . . . . . . . Page 90 LEFT(Char_Value, Number of characters from left) Page 90 LEN(Char_Value) . . . . . . . . . . . . . . . Page 91 LOG(Num_Value) . . . . . . . . . . . . . . . Page 91 LOWER(Char_Value) . . . . . . . . . . . . . . Page 91 LTRIM(Char_Value) . . . . . . . . . . . . . . Page 91 MAX(Num_Value1, Num_Value2) . . . . . . . . . Page 91 MIN(Num_Value1, Num_Value2) . . . . . . . . . Page 91 MOD(Num_Value1, Num_Value2) . . . . . . . . . Page 91 MONTH(Date_Value) . . . . . . . . . . . . . . Page 91 RECCOUNT() . . . . . . . . . . . . . . . . . Page 91 RECNO() . . . . . . . . . . . . . . . . . . . Page 91 RIGHT(Char_Value, Number of characters from right) . . . . . . . . . . . . . . . . . Page 92 ROUND(Num_Value1, Num_Value2) . . . . . . . . Page 92 RTRIM(Char_Value) . . . . . . . . . . . . . . Page 92 SPACE(Num_Value) . . . . . . . . . . . . . . Page 92 SQRT(Num_Value) . . . . . . . . . . . . . . . Page 92 STR(Num_Value, Length, Decimals) . . . . . . Page 92 SUBSTR(Char_Value, Starting Position, Number of Page V Characters) . . . . . . . . . . . . . . Page 92 TIME() . . . . . . . . . . . . . . . . . . . Page 92 UPPER(Char_Value) . . . . . . . . . . . . . . Page 93 VAL(Char_Value) . . . . . . . . . . . . . . . Page 93 YEAR(Date_Value) . . . . . . . . . . . . . . Page 93 Appendix C: Warning and Error Messages . . . . . . . . Page 94 A. Warning Messages . . . . . . . . . . . . . . . Page 94 B. Error Messages . . . . . . . . . . . . . . . . Page 95 Appendix D: PRO-dVelBase Files . . . . . . . . . . . Page 102 A. Program Files . . . . . . . . . . . . . . . . Page 102 B. Created Files . . . . . . . . . . . . . . . . Page 102 Appendix E: Trademark Notices . . . . . . . . . . . Page 104 Page 1 Chapter 1: Introduction ======================== PRO-dVelBase Lite (from now on abbreviated to PRO-dVelBase) is a relational database management system. It was developed around the premise that database management is not the sole domain of the programmer. With PRO-dVelBase, all the features and functions of a powerful database management system are accessed through menus and require no programming at all. Therefore, nonprogrammers may create very sophisticated database applications without using a programming language. PRO-dVelBase revolves around a series of "objects" such as form, query, report, mail, label, append and post. These "objects" may be created, modified, selected, executed and deleted. Through manipulation of these objects, the various database functions may be easily achieved without using a complex programming language. A. Registration =============== PRO-dVelBase Lite 1.0 is distributed as "shareware". This allows you to "try before you buy". If you find this program helpful, we ask you to send in a registration fee. With this registration fee, you will obtain a registered copy of the software, a printed 200 page reference manual, a 100 page tutorial, a coupon towards the purchase of PRO-dVelBase 2.0 and free telephone support for 90 days. Please print the accompanying file, README.TXT, for an order form. B. Technical Support ==================== Ninety day free telephone support is available for all registered users of PRO-dVelBase. C. How to Use This Documentation ================================ This reference guide will describe the objects and commands used in PRO-dVelBase. Each of the main menu items will be described in a separate chapter. Submenu commands will be described in logical order rather than in order of appearance on the menu. The reference guide is only a synopsis of the reference manual. Upon registration of the software, a complete, printed reference manual and tutorial will be sent. The convention used in this reference guide to indicate the path used to reach a certain command from the main menu is with a forward slash (/). For example, Form/Modify means first selecting Form from the main menu and then selecting Modify from Page 2 the first pull-down menu. D. Technical Specifications of PRO-dVelBase =========================================== The following is a detailed list of PRO-dVelBase features. It serves as a thumbnail sketch of the capabilities of PRO-dVelBase. Please note that these are the specifications of PRO-dVelBase Lite. PRO-dVelBase 2.0 has many more features and allows for larger databases. Database Specifications ----------------------- * Maximum of 127 fields per record * Maximum of 65,535 records per database file * Database (.DBF and .DBT) and index (.NDX) files are dBASE compatible * Field types: - Character (maximum of 128 characters) - Numeric (maximum of 19 digits) - Date - Time - Logical (True or False) - Single Key (user defined single key) - Memo (maximum of 10,000 characters) Forms ----- * Forms are designed by placing text and fields directly on the screen * Underlying database file automatically created with form * Forms span up to 5 screens * Place up to 127 fields per form * Draw boxes, lines and graphics characters to improve the look of the form * Forms may relate up to 5 database files, each with a maximum of 7 indexes * Maximum of one 1-to-20 link in a one-to-many relationship (i.e. one record in a parent database file linked with 20 records in child database file) * Displays a one-to-many relationship in a "scroll-window", that allows these records to be scrolled within a window on the screen for ease of viewing * Able to print a report after each new record as in an invoicing system * Able to print a report, label or mail of the currently viewed record directly from the form * Able to zoom into underlying database file and display it in table view (in sequential or indexed order) * Able to transfer records from table view into form view, thus acting as a "look-up table" Page 3 Fields ------ * Fields defined by answering a series of questions * Display length of long fields may be changed * Fields may be required (null data disallowed), fully filled or forced upper case * Able to perform range check on numeric fields * Automatic word wrap for memo fields with a maximum of one memo field per form * Multiple date and time formats supported * Single key fields allow users to specify allowed keys (i.e. "MF" for sex field) * Allows up to 127 calculated (virtual) fields per form * Allows for: - derived fields (fields based upon a formula) - copied fields (fields copied from previous record) - default fields (fields that automatically display a certain value) - sequential fields (fields that increment sequentially based upon an indexed field) * Create pictures that act as templates and data masks (i.e. (NNN) NNN-NNNN for a telephone field) Tables ------ * Able to edit records in table * Able to transfer records from table to form * Change order of fields to view * Able to view records in sequential or indexed order Queries ------- * Uses "Query-by-Example" to create queries * Multiple .AND. and .OR. conditions supported * Ascending sorts on up to 10 fields either case sensitive or case insensitive * Queries act as filters to allow only the selected records to be processed by Report, Mail, Label, Append or Post Reports ------- * Created by placing fields and text on screen * Uses "band" method to place fields and text in desired location on report * Allows up to 5 levels of grouping * Field formatting accomplished by answering a series of questions * Allows calculated, summary (sum, average, high, low, count) and system (record count, record number, page, date, time) fields Page 4 Labels ------ * Created by placing fields and text onto a label * Supports any size label * Multiple copies allowed * Calculated, summary and system fields supported Mail ---- * Merges letter with database records * Use built in editor or your favorite word processor Functions and expressions ------------------------- * Supports most dBASE III functions directly and expression syntax is dBASE compatible Searches -------- * Able to search through form or table * Allows global operations such as delete, undelete and replace Utilities --------- * Allows appending of records to a second database file * One database file may post to another one * Allows for zapping (removing all records) and packing (removing marked records) of a database file * Structure of a database file may be cloned to second database file E. System Requirements ====================== * IBM PC/XT/AT, PS/2 or 100% compatible computer * PC/MS DOS 3.0 or greater * 80x87 math coprocessor supported * Hard drive with at least 2 megabytes free disk space * 640K RAM * Supports monochrome, Hercules, CGA, EGA or VGA adapters * Printer recommended F. Compatibility ================ PRO-dVelBase is completely data file compatible with any program that uses .DBF file format. Examples of such programs include: dBASE III, dBASE IV, Clipper, FoxBase and dBXL. This means that any data files created by any of these programs may be used by PRO-dVelBase directly without conversion and vice versa. The index files (.NDX extension) are also dBASE III compatible although the names of the files are determined by the program Page 5 rather than the user. G. Installation and Running PRO-dVelBase ======================================== PRO-dVelBase Lite is distributed in .ZIP file format and requires a copy of PKUNZIP.EXE to uncompress it. A program directory should be created first. This may be done with the command, "MD C:\PRO" (without the quotes), from the DOS prompt. The program can then be uncompressed using the command, "PKUNZIP C:\PRO\PROLIT10 C:\PRO" (if PROLIT10.ZIP is on C:\PRO directory). The sample application is entitled, "WIDGETS.ZIP". Another subdirectory should be created to store the sample widgets application. This is created with the command, "MD C:\PRO\WIDGETS", from the DOS prompt. The widgets application can then be uncompressed with the command, "PKUNZIP C:\PRO\WIDGETS.ZIP C:\PRO\WIDGETS". The CONFIG.SYS file should be changed to include the following lines. FILES=60 BUFFERS=20 Be sure you are on the subdirectory in which PRO-dVelBase resides by typing CD\PRO followed by ENTER. It is recommended that different applications be stored in separate subdirectories beneath the one in which PRO-dVelBase resides. A subdirectory may be created with the command: MD The program may now be run directly by just typing PRO followed by ENTER from the DOS prompt. At the main menu, the sample application can be viewed by using the Options/Change Dir command to change the data directory to "WIDGETS". A form can then be selected and executed. PRO-dVelBase automatically senses what type of video adapter card the computer has and adjusts itself accordingly to display in either monochrome or color. The only problem that may arise if the computer has a CGA card with a composite monochrome monitor. PRO-dVelBase will then attempt to display the screens in color. The screen resolution on such a monitor may be very poor. The way around this problem is to force PRO-dVelBase to display in monochrome. To do this, use the DOS MODE command. From the DOS prompt, type "MODE BW80" before typing PRO. PRO-dVelBase will now be displaying its screens in monochrome. If the keyboard locks after starting the program, try re-starting the program by typing, "PRO -k". PRO-dVelBase normally attempts to accelerate the typematic rate on AT style keyboards. On some keyboards, this may not work causing the keyboard to freeze. Using the "-k" switch turns off the keyboard speed up. Page 6 The printer also has to be initially setup. Although not absolutely necessary, having a printer definition file allows for more precise control over your printer. At the main menu, press O followed by P followed by C (Options/Printer Setup/Create). You will then arrive at the Printer Definition Screen. You may now fill in the control codes that control special functions for your particular printer (see your printer reference manual). Pressing F9 will list some common brands of printers and you may just select one and the codes will automatically be filled in. If you do not see your particular printer, many dot-matrix printers are Epson compatible and selecting that one is a safe bet. If you have a laser printer, many are also H.P. LaserJet compatible and selecting the H.P. LaserJet may work. When finished press F10 followed by Y to save the printer definition. The specifics about printer setup are found in Chapter 8 under Options: Printer Setup. H. Help ======= PRO-dVelbase uses a "hypertext" help system that makes it very easy to learn all the features that PRO-dVelBase has to offer. The help system is accessed by pressing F1. The initial help screen is context sensitive i.e. it depends upon which part of the program it is accessed from. Key words are highlighted and further help on these words may be accessed by moving the cursor over the highlighted word and pressing ENTER. Previous help screens are accessed by pressing PAGEUP. The entire help system may be navigated through the "Main Help Menu" option. Selecting Next views the next help screen in the section. I. Basic Principles =================== There are a few basic principles that apply to PRO-dVelBase. Understanding these principles is essential. The main objects of PRO-dVelBase (Form, Query, Report, Mail and Label) may be accessed through the main menu. Secondary objects (Append and Post) may be accessed through the Utilities submenu. Each of these objects may be selected, executed, created, modified, unselected, deleted, added to catalog or removed from catalog. You must first create the object and select it before you may execute it. The process of developing objects requires that you use the submenu commands Create, Select and then Execute. However, you only need to create your objects once. Thereafter, you must always select it first before you will be allowed to execute it. All future changes to the objects may be made through the Modify submenu command. Page 7 To clarify the above, you only create your form once. Thereafter, whenever you want to use your form, you must select it first before executing it. Similarly, you only create your report once but whenever you need to use it you must select it first before you may execute it. Form is the main object that all other objects are related to or built upon. The creation of subsequent objects is dependent upon which form is selected. Page 8 Chapter 2: Form ================ The form constitutes the basic method of data entry and relating database files when using PRO-dVelBase. Creating a form automatically creates the underlying database (.DBF extension) file. There are six submenu commands under the form menu. These include: Select, Execute, Create, Modify, Unselect and Delete. A. Form/Create ============== This submenu command is used to create forms. A form consists of one or more database files. Each form is associated with a primary database file. A file that is related to the primary database file is called a secondary database file. One that is related to a secondary database file is called a tertiary database file and so on. The secondary, tertiary, etc. database files are also called collectively, foreign database files because they are foreign to the primary database file. Another way of expressing this is using the terms parent and child databases or parent and child records. In forms, you may: 1. Define and place fields. 2. Set relationships between various database files. To create your form: 1. Design your entire database on paper. Write down all the field names and the specifications for each field. 2. Identify key fields. If you are creating several databases that will be related together, you need to identify the key fields that will be used to set the relationships. Key fields are fields that have a unique value to them that distinguishes them from every other record in the database file. The value of a key field is common between various database files. For example, the social security number is a unique number that identifies a person and can be used as a key field. All key fields need to be indexed. Taking the extra time to design your database on paper will save you time when designing it at your computer. You should have a clear picture of what you want to design before sitting down at your computer. It is much easier and less time consuming to make corrections on paper than to make them at your computer. Once you have designed your database on paper, then proceed to use PRO-dVelBase. 1. Select Form/Create. 2. Give the form a name. This name is up to 8 characters in length and must follow dBASE conventions in naming. If you Page 9 are not familiar with dBASE conventions in naming, see Appendix A: dBASE Naming Conventions. If you select a name that already exists, you will get an error message and you will be asked to enter a new name. Make sure the new name you choose is descriptive of the form you are creating. 3. The next screen is the Form Definitions Screen and you will be asked, "Do you want to print a report after each new record? (Y/N)". Answer YES or NO depending upon whether you want a report. If you answer YES, PRO-dVelBase will generate a single transaction report after each new record added to the end of the database. The format of the report is dependent upon the currently selected report. If no report is in the work area, then the report will not be printed. This feature may be used in an invoicing system where the customer would receive a statement after the transaction. 4. This takes you to the next screen, which is the form editor. Towards the bottom of the screen is the status line. The status line consists of the following information. a. Date b. Time c. Screen number d. Row and column number of the cursor e. Status of the INSERT key f. Status of the Caps Lock key g. Status of the Scroll Lock key h. Status of the Num Lock key The form editor is similar to many basic text editors. It allows for full screen editing. Text and fields may be placed directly on the screen at any location on up to 5 different screens. Moving between various screens occurs by the PAGEUP and PAGEDOWN keys. The INSERT key allows toggling between the insert mode and overwrite mode. A blocking function is also present and the blocked area may be moved or copied to another area of the screen or to a different screen. Lines and IBM graphics characters (ASCII values greater than 127) may also be placed on the forms. Page 10 The following table shows the active keys on the form editor. ================================================================= Active Key Action ----------------------------------------------------------------- ESC takes you to the previous screen and allows you to exit this section of the program, without saving. ARROW CURSOR KEYS moves the cursor in the direction of the arrows. PAGEDOWN moves to the next screen. PAGEUP moves to the previous screen. HOME moves the cursor to the extreme left. END moves the cursor to the last text or field on the screen. INSERT toggles between overwrite and insert mode. DELETE deletes the text or field on the cursor. Also deletes the relationships between files if cursor is in a key field. BACKSPACE deletes the previous text character. ENTER inserts a blank line when in insert mode only if the bottom line of the form is empty. F1 context sensitive help. F3 starts and ends blocking of an area. F5 copies blocked area. F6 moves the blocked area. F7 places and defines a database or calculated field. ALT-F7 sets a relationship to another database file. F10 completes and saves the form. ALT-B displays and activates box drawing characters. ALT-C displays and places IBM extended graphics characters. ALT-S defines a scroll window for a one-to-many relationship. CTRL-C centers the text and fields on the current line. CTRL-Y clears the text from a line. ================================================================= Designing a form consists of placing text that describes a field and then placing and defining the actual field itself. The form editor allows you to place text and fields on a maximum of 5 different screens. As a general rule, unless you have many fields, only one screen should be used. The reason for this is that after you have completed the designing of your form, you will be able to enter data more efficiently with fewer screens. Page 11 If multiple screens are necessary, try to place logically related fields together on the same screen. For example, in an invoicing system, place customer information on screen one and sales on screen two. Start by placing your text on the screen. Lay out your text in the same way you would want it to appear for data entry purposes. When designing the form, the text should be placed on the screen first. This will give a basic layout as to how the fields should be placed. You may want to start by placing a title for the form. Later, you may want to place a box around the title. After placing the title, you may place the rest of your text on the screen. Note: You will need to wait until you have placed your fields before attempting to save your work. If you attempt to save your work after placing the text only, you will generate an error message and you will not be allowed to save your work until you have placed at least 1 field on your screen. It is recommended fields that are similar to one another be placed together. For example, it is wise to place a surname field next to a first name field. During data entry, the cursor will jump from fields in the direction of left to right, then top to bottom. This is similar to writing on a piece of paper. After the text has been placed on the screen, you will need to place the fields somewhere close to the appropriate text labels. When placing a field, it is important to make sure that there is enough space on the screen to accommodate the entire display length of the field. If there is insufficient space, an error message will be generated. Placing fields is done as follows: 1. Move the cursor over to where you want to place the field. This could be at a location adjacent, beneath or on top of the text that you have already placed. 2. Press F7. A window opens giving you two options to choose from: Database or Calculated fields. 3. Database fields are real fields that exist on database files. Calculated fields are virtual fields that exist in the memory of the computer only and are based on a calculation formula. 4. Select Database. You have now entered the field definition screen. Page 12 Database Fields --------------- There are 2 sets of field definitions for database fields: fixed and changeable. Fixed field definitions may only be modified from the form in which the database file was created. Changeable field definitions may be altered from any form that links to that field. This means that if a given field has been linked to multiple forms, the changeable field definitions for that field may be different for each form. The fixed field definitions, however, will be exactly the same for each of the forms. The process of making field definitions consists of answering a series of questions. Each subsequent question that is asked will be determined by how you answered the previous question. The built in logic of PRO-dVelBase determines what question will be asked next because some questions may be redundant or irrelevant depending on how the previous questions were answered. When defining your fields, the UP key will take you to the previous question and pressing ESC will abort the process of defining the field. Form: Fixed Field Definitions ----------------------------- Fieldname: A field name may be up to 10 characters in length and may consist of letters of the alphabet, numbers, or the underscore (_). A number cannot be the first character of the field name. This follows standard dBASE conventions. (See Appendix A: dBASE Naming Conventions). Try to choose a field name that is descriptive of the field. Note: Field names may not be changed once they are assigned. The reason is that there may be calculations and formulas that are referenced to this field name and it would be very difficult to update all the references in other files. The only way a field name may be changed is to delete the field and redefine it using another field name. This general rule applies to all objects created by PRO-dVelBase. Field type? (C)har (N)umeric (D)ate (T)ime (L)ogical (S)ingle key (M)emo PRO-dVelBase supports the five dBASE III field types: character, numeric, date, logical and memo. It also has two other field types: time and single key. ==> The default field type is Character. Page 13 The following table outlines the field types supported by PRO-dVelBase. ================================================================= Field Type Field Description ----------------------------------------------------------------- Char Is a basic text field and consists of alphanumerics or special characters. Maximum of 128 characters. Numeric Consists of positive or negative whole or decimal numbers. Generally used whenever a calculation has to be performed on that field. Maximum of 19 characters, including a negative sign. May have up to 14 decimal places. Date Consists of dates only and has a field length of 8 characters. It is stored on disk in the form of CCYYMMDD (where CC=century, YY=year, MM=month, DD=day). The display format will be different depending on how it is defined. Time Consists of time only and has a field length of 6 characters. It is stored on disk in the form of HHMMSS (where HH=24 military hours, MM=minutes, SS=seconds). Logical Is a single character field that can have only 1 of 2 values, either "T" (for TRUE) or "F" (for FALSE). The field may contain the following constants: T, Y, F, N. Single key Is a field that holds a single character. Allowed keys are set at the time of field definition. This field type is used when there are a number of choices to be made. Memo Is a free text field in which anything may be placed in it. In other words, it is a field for your comments. Only one memo field may be defined per form. Maximum length of 10,000 characters. ================================================================= The following table gives examples of some field types. ================================================================= Field Type Field Name Value ----------------------------------------------------------------- Char SURNAME Smith ADDRESS 123 - Baker St. CITY Washington D.C. Numeric PRICE 20.50 QUANTITY 54 Single key SEX "M" - male "F" - female COUNTRY "U" - United States "C" - Canada "E" - England ================================================================= Page 14 Is this field required? (Y/N) This question is only asked for character, date, time and single key fields. If you answer YES to this question, you will force the user to enter data into this field when he is doing data entry. In other words, you may not just leave this field blank. If you try to save a record with a required field blank, you will get a warning message that a required field was left blank. You may still save the record or you may choose to fill in the required field. ==> The default is NO. What is the length of this field? When determining the length of the field you should determine how long the longest entry for that field will be. Make sure there will be enough room for this. You would not enter a length of 10 in the surname field if you could possibly have a surname that is 20 in length. Do not allocate lengths that are much longer than what you need otherwise, you will be wasting disk space. Field lengths are automatically assigned to the field types below, therefore this question would not be asked for these. The length that is assigned is as follows: 1. date: 8 2. time: 6 3. logical and single key: 1 Memo fields behave differently from other fields. Memo fields only occupy 10 bytes on the .DBF file. These bytes contain a pointer that indicates where the actual field is located in the corresponding .DBT file. The amount of space the data occupies in the .DBT is not fixed but variable. Longer strings will take more space. If no data is present, no space is used in the .DBT file. Memo fields are therefore very useful for fields that have free text since they do not waste space if empty. How many decimal places are there? This question is only asked for numeric fields. They may have a maximum of 14 decimal places. What is the display width? What is the display height? These two questions are asked for memo fields. Both the height and the width of the window may be defined at this point. The height of the window must be at least 2 and the width cannot be greater than 80. Is this field to be fully filled? (Y/N) A YES answer will mean that the number of characters entered must always be equal to the maximum length assigned to this field. This question is only asked Page 15 for character fields. ==> The default is NO. Which keys are allowed? This question is asked for the single key field type. You may specify up to 20 keys that the user may choose from. Keys not included here will be rejected when doing data entry. Index this field? (Y/N) When indexing is performed, the value of this field will be placed in a separate file in a sorted ascending sequence. This allows the PRO-dVelBase to access information very quickly. ==> The default is NO. Indexing is necessary for: 1. key fields linked to other database files. 2. fast searches. 3. prevention of duplicate entries. For each database, a maximum of 7 indexes are allowed therefore, you need to be selective about which fields to index. The key field must be indexed. Fields that you use to perform frequent searches should also be indexed. Every index is updated automatically whenever you do data entry, making it a time consuming process. This means that the more indexes you create, the more time it will take to update your database thus slowing down data entry. All field types except logical and memo may be indexed. Ignore case while indexing? (Y/N) Answering YES will automatically convert all lower case alpha to upper case alpha before it is indexed. For example, this will make "smith" equivalent to "SMITH". This is helpful if case insensitive index searches are needed. ==> The default is NO. Prevent duplicate entries? (Y/N) Indexes may be used to prevent the user from entering a value that is the same as one already entered. After a value is entered into a field, the index is searched and if that value is already present, it is rejected. ==> The default is NO. Page 16 Are numbers allowed? (Y/N) This question is only asked for the character field type. If you would like to prevent the user from entering numbers in this field, answer NO. A numeric string may be created by answering YES to this question and not allowing letters of the alphabet or special characters. The difference between a numeric string and a numeric field is that a numeric string cannot be directly used in a calculation. For example, a phone number field should be defined as a numeric string. ==> The default is YES. Are letters of the alphabet allowed? (Y/N) If you would like the user to enter letters of the alphabet, answer YES. This question is only asked for character fields. ==> The default is YES. Upper case only? (Y/N) Answering YES will force all entries to be in upper case regardless of whether the caps lock key is in use. ==> The default is NO. Are special characters allowed? (Y/N) The following are special character keys: SPACE ! " # $ % & ' ( ) * + , - . / : ; < = > ? @ [ \ ] ^ _ ` { | } ~ This question is only asked for character fields. ==> The default is YES. Do you want a range check? (Y/N) Range checks may be performed on numeric fields. A lower and upper range may be specified and entries outside this range will generate an error message. ==> The default is NO. Display format for date? (A)-MM/DD/YY (B)-DD/MM/YY C)-YY/MM/DD Three different display formats are allowed for date fields. When the form is executed, a template will appear in the date field display indicating which format was selected. For example, choosing display format (A) will display the template, "mm/dd/yy". The purpose of this template is to guide the user on the proper date format for data entry. ==> The default is MM/DD/YY. Display format for time? (A)-12 hrs. (B)-24 hrs. Two different display formats are available for time Page 17 fields. The first is the 12 hour format (with a template of "hh:mmp", where p represents A for am or P for pm) and the second is the 24 hour format, or military time (with a template of "hh:mm"). ==> The default is 12 hours. Display seconds? (Y/N) This question is only asked for the time field type. It allows for seconds to be entered along with the hours and minutes. The template will also have ":ss" to indicate that seconds have to be entered. ==> The default is NO. Word wrap each line? (Y/N) This question is only asked for memo fields and allows for each line to be displayed properly without breaking up words at the end of the line. ==> The default is YES. Picture: The purpose of a picture is for formatting the way the data is to appear in the field display. Pictures only apply to character fields and the maximum total length of a picture is 70 characters. The picture acts as both a template and a mask. As a template, any special character placed in the picture will be displayed that way in the field. Note that these special characters are for display purposes only and are not saved. As a mask, pictures will only allow certain characters at a given position. The following table indicates how PRO-dVelBase uses pictures as a mask. ================================================================= Character Effect ----------------------------------------------------------------- a only alpha characters A only alpha characters forced to upper case b both alpha and numeric characters B both alpha and numeric characters, with alpha characters forced to upper case N or n only numeric characters x alpha, numeric or special characters X alpha, numeric or special characters, with alpha characters forced to upper case. ================================================================= Page 18 Here are some examples of pictures. ================================================================ Picture UserKey Strokes Result ---------------------------------------------------------------- (NNN) NNN-NNNN 4034569163 (403) 456-9163 AA-NNNNN xy4645k XY-4645 but blocks the final k ================================================================= The number of alphanumeric characters in the picture must be equal to the length of the field. e.g. For a phone number field having a field length of 7. NNNNNNN is allowed NNN-NNNN is allowed NNN-NNN is not allowed Remember that when using a picture as a mask, the previous field definition rules take precedence over the ones in the picture. For example, if you have not allowed numbers when you defined a field, placing an N in the picture will not allow you to use numbers. Form: Changeable Field Definitions ---------------------------------- Display status for field? (A)-both read & write (B)-read only (C)-hidden The display status of a field refers to whether the user is able to see and edit the field. In a read and write field, the user is able to see and edit the field. In a read only field, the user may see the value of the field but is unable to change it. The cursor will jump over a read only field. In a hidden field, the user may neither see nor edit the field. Most fields are generally both read and write. ==> The default is both read & write. What is the display width? In character fields, the width of the field display may be changed. This defaults to the length of the field. However, it may be shortened if there is limited space on the screen. If the width of the display is less than the length of the field, then on data entry, the value will be scrolled horizontally within the display. ==> The default is the field length. Page 19 Field value is (A)User entered (B)Derived (C)opied (D)efault (E)Sequential? This question affects how a value may be entered into a field. Most fields are user entered, meaning that it is up to the user to enter data into the field. A derived field is one that is calculated based on a formula that relates it to other fields, functions or values. For derived fields, a formula must be entered. Please refer to the Appendix B: Formulas and Functions. A derived field differs from a calculated field in that a derived field is real while a calculated field is virtual and exists only in the memory of the computer. Generally, a derived field should be used if you want the value to be saved. The display status for derived fields should be set for read only. The reason for this is because even though it is possible to edit a derived field, its value will automatically revert back to the calculated result once the cursor leaves the field. A copied field is one whose value is copied from the last record in the database file. A default field is one that defaults to a certain value only if the field is empty. It is also based upon a formula and is very similar to a derived field. The only difference between it and a derived field is that a default field is only evaluated when the cursor drops into the field display. If the field value is sequential, then its value is taken from the greatest value of the index of the field read from. This value is then incremented. This type of field is particularly valuable in generating unique values for key fields such as a customer number field. The field must be indexed. ==> The default is User entered. Field value appears (A)utomatically (U)ser selected. This question is only asked for copied, default and sequential field values. If you define the field value to appear automatically, then once the cursor drops into the field, that value will appear if the field is initially blank. If a value is present initially, no changes are made unless F5 is pressed. If you define the field to be user selected then F5 must be pressed before the value will appear. ==> The default is (A)utomatically. Page 20 (I)ncrease (D)ecrease (S)ame? This question is asked for copied field values. It indicates how the field is to be copied from the last record of the database file (i.e. whether the value is to increase, decrease or be the same). In a numeric field, a value of 1 is either added or subtracted if the field is increased or decreased. In a character field, the ASCII value of the rightmost character is either increased or decreased. If end characters such as 0, 9, a, z, A or Z are reached then the value is wrapped around and the second from the right character is increased or decreased and so on. In date fields, the date is either increased or decreased by one day. This question is only applicable to numeric, character and date fields. ==> The default is (S)ame. The following table shows some examples of various operations. ================================================================= Field Type Initial Value Operation Result ----------------------------------------------------------------- Numeric 14 Increase 15 Numeric 62.95 Decrease 61.95 Character xyz Increase xza Character yzzzz Increase zaaaa Character H06819 Increase H06820 ================================================================= Formula: Formulas are used for derived and default fields. The maximum length for formulas is 255 characters. Long formulas may be displayed in a window for editing by pressing CTRL-Z (Z means zoom!). A list of fields may be displayed by pressing F9, a list of operators displayed by pressing CTRL-F9 and a list of functions displayed by pressing ALT-F9. Formulas are dBASE expressions. See Appendix B: Formulas and Functions. Note that if a field name is used in a formula, the preceding database name must also be included if the field is from a foreign database (e.g. CLIENT->SURNAME). This is not necessary if the field is found in the primary database file. Automatic cursor advance on full field? (Y/N) When the field is fully filled, then the cursor will automatically move to the next field. ==> The default is NO. Page 21 Formula error handling? (Y/N) Formula error handling allows precise control over a field value using a formula. This question is asked only for fields that have both a read and write display status. ==> The default is NO. Error Formula: If formula error handling is requested, an error formula must be entered. An error formula is a dBASE expression that evaluates to a logical TRUE or FALSE. A FALSE evaluation will result in the field being rejected. The error formula is evaluated upon exiting the field. The maximum length for formulas is 255 characters. Long formulas may be displayed in a window for editing, by pressing CTRL-Z (Z means zoom!). Error Message: The error message may be up to 60 characters in length. It is displayed in a window in the middle of the screen if the error formula is evaluated to be FALSE. Generally, this message should have meaningful information to help the user enter a correct field value. The following table shows an example of error formulas and the resultant error messages if the given values were entered into the respective fields. ================================================================= Error Formula SALE_PRICE COST_PRICE Error Message ----------------------------------------------------------------- SALE_PRICE>COST_PRICE 13.95 15.00 ERROR: SALE PRICE IS LESS THAN COST PRICE! SALE_PRICE>COST_PRICE 16.95 15.00 No error displayed. ================================================================= Help Message: Each user entered field may have a help message associated with it. The help message is up to 70 characters and is displayed on line 23. Try to make this message useful for the person doing data entry. For example: a customer number field may have the message, "Enter a five digit number to find the customer". Page 22 Calculated Fields ----------------- Calculated fields are not real fields but are called virtual fields. That is, they do not exist in a database file but just appear on the form and exist only in the memory of the computer. Their value is obtained from formulas that may have fields, constants, functions and operators. Calculated Field Definitions ---------------------------- Calculated fields are placed by moving the cursor over to the desired location and pressing F7. Calculated fields are defined by the same process as database fields; by answering a series of questions. The value of a calculated field may be used by a formula in another calculated field. In order to reference a calculated field, the field name must be prefixed by the database name, "CAL_F->". This indicates that it is a calculated field and that it is in a form (as indicated by the "F"). Fieldname: Calculated field names are defined in the same way as database field names. Calculated fields must have a field name so that they may be referenced by other calculated fields. Calculation type? (S)um (A)verage (H)igh (L)ow (C)ount (F)ormula: Six different types of calculated fields are supported by PRO-dVelBase. They may be divided into two groups: summary and formula. Summary Calculated Fields ------------------------- The summary fields are sum, average, high, low and count. As its name indicates, these different field types are summary operators based upon a particular formula. They are particularly useful in one-to-many relationships where you would like to determine the sum, average, high, low or count of a certain column of numbers or fields. Formula Calculated Fields ------------------------- A formula calculation type is not a summary operator. That is, it does not require that a one-to-many relationship exist. The formula is entered, as defined in the appendix. Its value will automatically appear once the appropriate values that it references have been entered. This is exactly how a derived field works but this field is not saved. Page 23 What is the field type of the calculated field? (C)har (N)umeric (D)ate: Calculated fields may be either character, numeric or date type. Summary fields are always of numeric type therefore, this question will not even be asked for summary fields. ==> The default is (C)har. Is this field hidden? Calculated fields may be hidden from view. The purpose of having a hidden calculated field is that it acts like a temporary variable holding an intermediate result. Other fields may then reference this field for further calculations. ==> The default is NO. What is the length of this field? The field length of calculated fields represents the resulting display width on the screen. For numeric calculated fields, the maximum field length is 19. How many decimal places are there? The decimal places may be set for numeric calculated fields, therefore, this question only appears for numeric calculated fields. The maximum number of decimal places is 14. Please ensure that the display width is greater than the number of decimal places. Formula: Formulas have a different meaning in summary fields compared to formula fields. Summary fields are used when there is a one-to-many relationship. The formula for each of the one-to-many links is evaluated first before the summary operator is performed. Sum - takes the sum of the results of the formula for each linkage on a one-to-many relationship. High - finds the highest value of the results of the formula for each linkage in a one-to-many relationship. Low - finds the lowest value of the results of the formula for each linkage in a one-to-many relationship. Average - finds the arithmetic means of the results of the formula for each linkage in a one-to-many relationship. Count - formulas for count must be logical expressions that evaluate to either TRUE or FALSE. The count field will simply count the number of TRUE results. Page 24 Formula - formula calculated fields do not cross lines in one-to-many relationships. They are evaluated only for the current line. For example, a formula calculated field evaluating a line total will just use the values on that line and not any other line. Editing Text and Fields ----------------------- Field Identification Characters ------------------------------- Once the fields have been placed, you will notice some unusual characters at the beginning of each field display. These are called field identification characters and are used internally by PRO-dVelBase to locate fields. They will not show up on your data entry forms once you have finished designing your forms. They are also helpful for you to quickly identify which fields are the same in a one-to-many relationship. Moving Text and Fields ---------------------- Once fields and text have been placed on the form, their locations may be altered. The process of moving fields and text is similar to that in any other text editor. When in the insert mode, fields and text may be pushed to the right by typing blank spaces. Fields and text may be pulled to the left by deleting the spaces before them. The DELETE key will delete text under the cursor and the BACKSPACE key will delete text before the cursor. Blank lines may be inserted by pressing ENTER while in the insert mode. This will only work if the bottom line on the form is empty otherwise nothing happens. CTRL-Y will delete text (not fields) on the current line. CTRL-C will center the current line. Be careful when using these techniques on memo fields. Memo fields are represented by windows on the form and using these techniques may break the window up. If this occurs, you may not be able to save the form. Be sure the memo field is in the form of a rectangular window after it has been moved. Blocking Text and Fields ------------------------ The F3 key is used to block an area of the screen. Move the cursor over to the start position for blocking. Press F3. Move the cursor over to the end position for blocking. Press F3 again. The blocked area will appear in a different attribute depending upon what type of monitor you have. This block may then be copied (press F5) or moved (press F6). Pressing F3 again will unblock the area. Using this procedure to copy fields is how PRO-dVelBase creates one-to-many relationships (see Multi-File Forms). You will not be allowed to move or copy blocks beyond the edge of the Page 25 screen. You will also be given a warning if the target site already is occupied. By moving or copying a block to a target site that is occupied, the previous text or field will be deleted. Editing Field Definitions ------------------------- Field definitions may be changed by simply moving the cursor over to the field display and pressing F7. The field definition screens will again re-appear and the answers to the questions may be changed. Drawing Boxes ------------- Boxes may be drawn around important fields as follows. Move the cursor to the corner of the area you want to box. Start by pressing ALT-B. A window will open, showing the borders that you may choose for drawing your box. Select the appropriate box borders and draw the box using the UP, DOWN, LEFT or RIGHT keys. Once the box is drawn, press ENTER to complete the drawing of the box or press ESC to abort the drawing. Boxes may be deleted by selecting the box containing no drawing character and using that to draw on top of an existing box. IBM Extended Graphics Characters -------------------------------- The IBM Extended Graphics Characters (ASCII values greater than 127) may be placed by pressing ALT-C. Place the cursor over the location where the character is to be placed. A window will open, showing the various characters that may be placed. Select the appropriate character and press ENTER to place the character or press ESC to abort placing. One of the purposes of this feature is to allow the placing of the intersections for the boxes drawn. Moving to Next/Previous Screen ------------------------------ Text and fields may be placed on up to 5 screens. The PAGEUP and PAGEDOWN keys will move between various screens. Deleting Fields --------------- Fields are deleted by simply placing the cursor over them and pressing the DELETE key. A confirmation will be asked first before allowing to proceed. If the cursor is in a field that is linked to another field, pressing the DELETE key will first delete the linkage and then the field itself may be deleted by pressing DELETE again. Page 26 Multi-File Forms ---------------- The true power of PRO-dVelBase comes from its ability to relate forms and their underlying database files together easily. Relating forms allows information to be stored only once thus saving disk space and facilitating updating of that information. When you design any kind of a database application, you should first try to design all of your basic forms and database files i.e. all forms that only have one database file in them. After that you may design forms that have multiple database files related together. This is called the bottom up approach. Steps to relating forms: 1. Place the cursor over the key field that you would to relate to another form. This field must be of char type or else it cannot be used as a key. 2. When the cursor is in the field, press ALT-F7 and a window will appear with all the forms previously created in the current directory. Select the one that you would like to relate to. 3. Next a window appears with all the fields in that form that are indexed. Select the key field of that child database file and press ENTER. This links the 2 database files through their key fields. Note: The key field in the foreign database file must be indexed before this can occur. If it has not been indexed, you must go back to the primary form containing that field and index the field first. Next you will see the Foreign Database Relationship Definitions screen and questions will be asked that define the relationship. Foreign Database Relationship Definitions ----------------------------------------- (L)ink or (C)opy fields? There are two types of operations that may be done: linking or copying. When databases are linked through key fields, the editing of fields from a child database will change them in the child database. The fields of the child database do not exist in the parent database. Copying is when the fields of the child database also exist in the parent database. The values of matching fields from the child database are copied directly into the corresponding field in the parent database. Generally, linking should be chosen because it will Page 27 reduce the duplication of redundant data. For example, it is not necessary to store a person's name and address in 2 different databases when storing that information into one database will be sufficient. Secondly, by linking, changes that are made in one database will be reflected in all the databases that are linked to it. You would use copying of fields if the field values are to be fixed and not affected by any updates done in the foreign database. For example, in an invoicing system where the foreign inventory database has a particular item at a particular price in time. The price should be copied over to the invoice database because any price increases in the inventory database should not be reflected in the invoice database after the invoice has been generated and the item shipped. Delete linked record if primary record deleted? (Y/N) Answering YES to this question will cause linked records to be deleted when the record on the primary database is deleted. For some linked records you may not want this to occur. For example, when a customer record is linked to an invoice record, deleting the invoice record should not delete the customer record. In other situations, deletion of linked records should occur. For example, when an invoice record is linked in a one-to-many relationship to a group of details records (each record containing the details of a given invoice transaction), all the details records should be deleted automatically when the invoice record is deleted. This process is called referential integrity. Is this a one-to-many link? (Y/N) Only 1 one-to-many link is allowed per form. Up to 10 links may occur on any one form. A one-to-many link is used in situations where you would like to link one record in the primary database to many records in a foreign database. PRO-dVelBase allows you to create a link with up to 20 records on the many side of the relationship. This may be used in an invoicing system where a customer may buy more than one item but the number of items is not known at the time of database creation. Reject if value does not exist in the foreign database? This allows a check to be made if a particular value exists in a foreign database. Answer YES if you would like any non-existent value to be rejected. This may be used in an inventory system to ensure that a particular item is present in the inventory database before it can be accessed. After the key field has been defined, place the subsequent fields Page 28 on the screen. This is how you would place them. If you selected (L)ink fields, move the cursor over to an empty area. Press ALT-F7. Then select the next field to be transferred over. Then move to the next area to place another field. Continue this process until you have transferred all the fields you would like to transfer. When you have finished, press F10 to indicate that the process of transferring fields is completed. If you have selected (C)opy fields, the cursor will only be allowed to move into other fields that are in the parent database file. Move the cursor into the first field that you want data to be copied into. Select ALT-F7 again, then select the field you would like to be copied from and press ENTER. Continue this until you have finished. Again, press F10 to signify that you have completed this task. If you have set up a one-to-many relationship, subsequent lines can be created by performing a block and copy. Move the cursor to the beginning of the area you would like to block. Press F3. Then move the cursor to the end of the area you would like to block and press F3 again. Next move the cursor to the area you would like to copy to. Press F5. Every time you press F5, the blocked area will be replicated. Continue pressing F5 until you have reproduced as many lines as needed. Then press F3 again to unblock the blocked area. By using this technique, you may create multiple lines and fields for data entry in a one-to-many relationship. You may only block and copy up to 20 lines as this is the maximum that PRO-dVelBase handles in a one-to-many relationship. Any additional lines will be ignored. The key field does not need to be copied in the block as it only has to appear once on the form. PRO-dVelBase automatically recognizes this as a one-to-many relationship and the key field value will be copied appropriately into each record. In a situation where you know how many lines are required, this is all that is necessary. If an unknown number of lines are required, you should design a scroll window. Scroll Window ------------- A scroll window is designed around a one-to-many relationship and it allows the records in that area to be scrolled up and down. This allows the user to enter up to 20 records at a time. To set up a scroll window, place the cursor before the first field of a one-to-many relationship. Press ALT-S and a window will open. Move the end of the window to the bottom of the area that you want scrolled and press ENTER again. You will notice two small field identification characters appear in the corners of the window. These characters will not appear on the data entry screen. They are simply used by PRO-dVelBase to identify the boundaries of the scroll window. On the data entry screen, an up arrow will appear in the upper scroll field if the window has been scrolled up and a down arrow will appear Page 29 in the lower scroll field if the window has been scrolled down and there are more records beneath the scroll window. A scroll window may be deleted by moving the cursor to one of the field identification characters and pressing DELETE. Steps to Removing Relationships ------------------------------- Once a relationship or series of relationships have been defined, how do you remove them? Removal of relationships is quite simple. Find the key field that relates it to another form. The key field is indicated by having the names of the fields flashing when the cursor is in it. With the cursor in the field, press the DELETE key. A confirmation message will appear before the relationship is removed. All fields in the related database file and secondary or tertiary files, etc. will also be removed. In one-to-many relationships, the effects of the DELETE key is slightly different. The relationship definition is stored in the first occurrence of a key field on the form. If the cursor is in the first occurrence of a key field and the DELETE key is pressed then the relationship is removed and all related fields will be removed. If the cursor is in subsequent occurrences of a key field then deletion will not remove the relationship but will remove all the related fields from that occurrence on. The relationship will still be maintained in previous occurrences of the key field. Saving a Form -------------- Upon completion of the form, press F10. This performs a validity check on the form. If any errors have occurred, a message will appear and appropriate actions must be taken to correct them before the form may be saved. If there are any errors in any of the fields, the cursor will usually drop to that field. Refer to Appendix C: Warning and Error Messages, for the meaning of these messages. At this point, a descriptive file name may also be given for the form. A database file is automatically created when the form is saved. B. Form/Modify ============== To modify a form: 1. Use Form/Select to select the form you would like to modify. 2. Use Form/Modify to start modifications. The form will be displayed, allowing you to modify the form along with the underlying database. Field definitions may be changed by Page 30 moving the cursor over to the appropriate field and pressing F7. Any definition may be altered except for the field name. The only way that a field name may be altered is by deleting the field and creating a new field. Please note that by doing this any data already entered into that field will be deleted. The Foreign Database Relationship Definitions may be altered by moving the cursor over to the key field and pressing ALT-F7. 3. Press F10 to save the modified form. Note: If the fields in the primary database file associated with a form are modified (fields deleted, fields added, field lengths changed or field types changed), the changes will also occur to the database. If a field is shortened, data values may become truncated. The original database files are stored as backup database files with the extension .BAK and if there is any associated memo field, it will have the extension .TBK. C. Form/Select ============== When using the Form/Select command, you are simply choosing the form that you want to use for subsequent operations. Examples of such operations may include data entry, querying, report generation, label generation and mail merge. The Form/Select command will open the database file(s) that have been defined in the form. All other objects and commands in the menu are dependent upon the selected form. If no form is selected, then you may not access any other object on the main menu. Thus, before any other operations are performed, you must select a form. Error checking automatically occurs at the time of selection. All the formulas are checked along with the various fields. If there are any errors, a message will appear and appropriate action must be taken to correct the problem before the form can be executed. The most common error occurs when a field has been deleted or modified. Related forms that reference this field will then generate an error unless they are updated. The form may be UNEXECUTABLE and/or UNMODIFIABLE depending upon the nature of the error. Also the form name will be in lower case in the work area if an error occurs. Refer to the appendix for a complete list of error messages and appropriate steps to correct them. Note: You must select the form that you want to use from a directory. This directory will only list the forms that you have previously created and saved. If there are no forms created and you try to select a form, you will get an error message. To create your form, see Form/Create. Page 31 To Select a Form: 1. Choose Form/Select. At this point a window will open and a directory of the existing forms will be displayed. The description of each form is also shown. This window may not be able to show all the forms that you have created. However, by using the UP or DOWN keys you may scroll through the entire list of forms. To alert you to the fact that there are more forms on your list, you will see arrows along the border of the window, pointing in the direction that you may scroll. 2. Place the cursor bar over the form that you want to select and press ENTER. At this point, you have chosen the form that you want to use. Your work area will indicate the name and description of the form that you have chosen. It will be displayed beside Selected Form towards the top of the work area. Also note that the data directory will be displayed at the bottom of the work area. This indicates the directory that your form resides in. Now that you have selected your form, you may perform various operations on it. D. Form/Execute =============== The Form/Execute command is used for data entry and editing of data. When you select Form/Execute, a blank data entry screen will be displayed and new data may be entered. If some data is already entered, you will be at the end of the file (EOF). The bottom four lines of the screen make up the information area. The first line is the database information line. Reading from left to right, the information identifies the: 1. Field you are reading from. The information is in the form of: database file name -> field name. 2. Field you are writing to. 3. Current record number in the primary database file. The notation is in the form of: current record/total records. If you are at the end of the file, the record you are at would be EOF. This means that data entered into the form will be appended (i.e. added to the end of the file.) 4. The word "Del" if the current record is marked for deletion. In most cases, the database information line will indicate that the field you are reading from is identical to the field you are writing to. However, if the cursor in on a key field that relates to a secondary database file, this is not the case. The second line in the information area is the message line. It provides messages as necessary, to prompt you for further action. The third line in the information area is the active keys line. Page 32 Not all the active keys are shown but only the main ones. Any active keys that are CTRL or ALT keys may be displayed by pressing down the CTRL key or the ALT key. The display for the active keys line will change. The active keys of the Form/Execute command are as follows. ================================================================ Key Action ---------------------------------------------------------------- ENTER or TAB finished entering data into a field and moves cursor to next field. SHIFT-TAB moves the cursor to the previous field. RIGHTKEY moves the cursor one position to the right in a field. If the cursor is at the end of a field, it will jump to the next field. LEFTKEY moves the cursor one position to the left in a field. If the cursor is at the start of a field, it will jump to the previous field. UPKEY moves the cursor up to the field on the previous line. DOWNKEY moves the cursor down to the field on the next line. HOME moves the cursor to the start of a field. END moves the cursor to the end of a field. BACKSPACE deletes the character before the cursor. DELETE deletes the character under the cursor. INSERT toggles between insert mode and overwrite mode. CTRL-C recalculates calculated and derived fields. CTRL-D marks a record, that has been saved, for deletion. However, if you use CTRL-D while appending a record and you have not saved the record yet, the record will be cleared and not saved. Also, if there are any linked records, they will also be marked for deletion if the question, "Delete linked record if primary record deleted?", was answered YES. Pressing CTRL-D on a record already marked for deletion will undelete it and remove the mark. CTRL-L prints the current record based on the selected label. This will work only if a label has been previously selected. CTRL-M prints the current record based on the selected mail. This will work only if mail has been selected for purposes of a mail merge. CTRL-R prints the current record based on the selected report. This will work only if a report has been selected. CTRL-U undo key. Any changes made to a record will be undone as long as it has not been saved. CTRL-Y clears the data in the field where the cursor is at. Page 33 CTRL-HOME moves to the first screen of the first record. CTRL-END moves to the append record. F1 context sensitive help. F2 used to view an underlying database file, whether it be the primary or foreign database. The database, which is being read from, will be displayed in table view in sequential order. ALT-F2 used to view the database file being read from in indexed order in table view based upon any index. CTRL-F2 used to view the underlying database file in indexed order in table view based upon the current index and field value. F5 used to bring up pre-set data in a field whose value has been defined as a copied, defaulted or sequential. F8 brings up the search menu. F10 saves the current records and moves to the next one. PAGEDOWN moves to the next screen of the current record or the first screen of the next record if the current screen is the last screen of the current record. PAGEUP moves to the previous screen of the current record or the last screen of the previous record if the current screen is the first screen of the current record. CTRL-PAGEUP moves to the first screen of the previous record. CTRL-PAGEDOWN moves to the first screen of the next record. ESC aborts the process of data entry and returns to the main menu. ================================================================= Appending and Editing Records ----------------------------- Upon execution of a form, PRO-dVelBase places the record counter at the end of file (EOF). At this position records will be added (appended) to the primary database associated with the form. The natural direction of cursor movement is from left to right then top to bottom. The UPKEY moves the cursor to the closest field on the prevous line while the DOWNKEY moves the cursor to the next line. The cursor will jump over fields that are write-only or calculated fields. The allowed data in a field is determined by how the field was defined. If you enter a value into a key field and this value is present in the related database, then the fields of that related database Page 34 will be automatically retrieved and displayed on the form. If it is not present, then the field values entered will be appended to the related database. If the relationship was defined as being copied instead of linked, this will not happen and the field values will only be stored in the parent database. Upon completion of the last field on a form, the record(s) will be automatically saved and the record counter incremented. Remember that if a form is a multi-file form, all updated records will be saved in an all or none fashion. If the form is aborted by pressing ESC before it is finished, all the field values will not be saved. This process is called a transaction. If you would like to save a form before all the fields are filled, press F10. F10 causes a save to be made unless the cursor is in an one-to-many area (where it means go to the next field beyond the one-to-many area) or if in a memo field (where it means go to the next field). Pressing F10 will evoke error checking to ensure all required fields have data in them and all error formulas evaluate to be TRUE. Previous forms may be viewed by pressing the CTRL-PAGEUP key (or just PAGEUP if the form only has one screen). The fields on the form may then be edited and it will be automatically saved when the cursor passes the last field on the form or if F10 is pressed. If you are using the PAGEUP, PAGEDOWN, CTRL-PAGEUP, CTRL-PAGEDOWN, CTRL-HOME or CTRL-END keys to move between different forms and changes are made to the fields, a warning will be given to indicate that a field was changed on the form and the option will be given to save the changes or discard them. No such warning is given if F10 is pressed to save a form. If a form is being appended and one of these keys is pressed, the contents of the form is not saved onto the disk but is retained in memory in a separate buffer. The records must be explicitly saved by pressing F10 or completing the last field on the form. Editing Character Fields ------------------------ Character fields appear left justified and may have a picture present for formatting within the field display. They are the only field types that allow horizontal scrolling if the field length is greater than the field display. Editing Numeric Fields ---------------------- Numeric fields are displayed right justified within the display and have the decimal place in a fixed position. Upon entering a numeric field, the cursor initially drops to the first position on the left. After the number is entered, the field is right justified upon exiting the display. Page 35 Editing Logical Fields ---------------------- Logical fields have either a TRUE or FALSE value. A TRUE value is represented by "T" or "Y". A FALSE value is represented by "F", "N" or a null field value. Editing Single Key Fields ------------------------- Single key fields only have one character. The valid characters depend upon how the field was defined. Editing Date Fields ------------------- Date fields are displayed with one of three templates: "mm/dd/yy", "dd/mm/yy" or "yy/mm/dd". The template is determined by how the field was initially defined. These templates indicate which positions the day, month and year should go. A date field cannot be partially filled. Editing Time Fields ------------------- Time fields are displayed with one of four templates: "hh:mm", "hh:mm:ss", "hh:mmp" or "hh:mm:ssp". The template is determined by how the field was initially defined. These templates indicate which positions the hours, minutes and seconds should go. The "p" in the template indicates that time is in 12 hour format and an A or P should be entered at this position to indicate whether the time is am or pm. A time field cannot be partially filled. Editing Memo Fields ------------------- Memo fields are displayed as windows on the form and the active keys are slightly different in them. If the length of a memo field is greater than what can fit into the window, the field may be scrolled vertically in the window. Page 36 The following table indicates the keys that have a different action in memo fields than in other fields. ================================================================= Key Action ----------------------------------------------------------------- ENTER places a carriage return at the current cursor location. TAB moves cursor 10 spaces to the right. SHIFT-TAB moves cursor 10 spaces to the left. RIGHTKEY moves the cursor one position to the right. If the cursor is at the end of a field, it will jump to the next field. LEFTKEY moves the cursor one position to the left. If the cursor is at the start of a field, it will jump to the previous field. UPKEY moves the cursor up to the previous line. If the cursor is at the first line, it will jump to the field above it. DOWNKEY moves the cursor down to the next line. If the cursor is at the last line, it will jump to the field below it. HOME moves the cursor to the start of a line. END moves the cursor to the end of a line. F10 finished editing the memo field and cursor jumps to next field. PAGEDOWN displays the previous window screen. PAGEUP displays the next window screen. ================================================================= Editing Fields in a One-to-Many Area ------------------------------------ Data entry in a one-to-many area is similar to data entry in other fields with a few exceptions. First, pressing F10 will only allow the cursor to jump to the next field beyond the one-to-many area. Second, if the area is enclosed by a scroll window, the UPKEY and DOWNKEY will cause the window to scroll if at the top or bottom. Third, if the cursor is beyond a one-to-many area and the UPKEY is pressed, the cursor will jump to the last line that has any field values entered and not the last empty line. Fourth, calculated formula fields only evaluate for the current line. Table View ---------- In table view, each record is shown in a row and the fields are displayed in columns. All field values are displayed with their templates except for memo fields, which are only displayed with the four letter word "MEMO" (if text is present in the field) or "memo" (if text is not present). Record numbers are listed down the left hand side. A vertical Page 37 line separates the record numbers from the fields of the database. Deleted records are identified by a diamond that is positioned in place of this vertical line and if the cursor is over a deleted record the word "Del" appears near the bottom. Pressing F2 activates table mode for the database file that is being read from. In most fields, it is the same as the one being written to. The only field where the read is different from the write is the key field. PRO-dVelBase was designed in this way to allow "look-ups" in foreign database files. When the database is displayed in table mode, you may either view or edit the database. Initially, you will be in the view mode. You are not allowed to edit the fields until you use the edit key (E) to get into edit mode. The active keys in the table view mode are as shown in the following table. ================================================================= Active Keys Action ----------------------------------------------------------------- HOME moves to the first field. END moves to last field. F1 context sensitive help. F8 brings up the search menu. PAGEUP scrolls up 20 records. PAGEDOWN scrolls down 20 records. UP, DOWN, LEFT, RIGHT, TAB, SHIFT-TAB, CTRL-LEFT, CTRL-RIGHT moves the cursor in the direction of the arrow. CTRL-HOME moves to the first record. CTRL-END moves to the last record. CTRL-D marks and unmarks the current record for deletion. Advances cursor to next record. E used to enter into table edit mode. T or ENTER used to transfer the record, which the cursor is on, into the form. V used to select which fields should appear in the table and what order they are to appear in. A used to append any records onto the end of the database. ================================================================= Altering Table View ------------------- The following procedure allows you to select the fields that you want to view and to alter the order in which you view these fields. This is done in the table view mode. 1. Press V. Two windows are displayed. The left window Page 38 displays all the fields present in the database. The right window is initially empty. 2. Place the cursor, which is in the left window, on the first field that you want view. 3. Press ENTER. A check mark will appear beside the field name, and this field name will be displayed in the right window. 4. Place the cursor over the next field that you want. 5. Press ENTER. A check mark will appear beside this field. Also, this second field will be displayed in the right window. 6. Continue this process until you have selected all of the fields that you want to be seen in table view. 7. To alter the order in which the fields appear in table view, use the RIGHTKEY to move the cursor to the right window. Locate the field you would like to move and place the cursor over this field. Press ENTER to lock the field onto the cursor. Use the UP or DOWN key to move the field to the position that you would like it to be viewed in. Press ENTER again to unlock and place your field into position. 8. If you have selected a field that you want to view in the table view mode and then decide that you do not want to view this field, you may remove it from the view. Place the cursor in the left window over the field you want removed from the view. Press ENTER and the field will be removed from the view. 9. Press F10 to save the view or ESC to abandon. Editing and Appending in Table Mode ----------------------------------- In the table view, editing fields occurs by pressing E while appending occurs by pressing A. This allows for editing of records directly from the table. This has the advantage of being able to see and edit many records at a time. It has the disadvantage of not having the changeable field definitions apply to the fields. While in table edit mode, only the fixed field definitions apply. A record is automatically saved after the field on the last column is filled, if F10 is pressed or if the cursor is moved to another row. Page 39 The active keys in table edit mode are as follows. ================================================================= Active Keys Action ----------------------------------------------------------------- HOME moves to the start of a field. END moves to the end of a field. CTRL-END moves to the last record. CTRL-HOME moves to the first record. CTRL-D marks and unmarks the current record for deletion. Advances cursor to next record. CTRL-LEFT or SHIFT-TAB, CTRL-RIGHT or TAB moves the cursor left or right one field. ENTER moves the cursor one field to the right. ESC returns to the table view mode. F1 context sensitive help. F8 brings up the search menu. F10 saves current record. LEFT and RIGHT moves the cursor left or right within the field. PAGEUP scrolls up 20 records. PAGEDOWN scrolls down 20 records. UP and DOWN moves the cursor up or down one record. ================================================================= Indexed Table Mode ------------------ While in the form, the table may also be brought up in indexed order instead of sequential order. This is done by pressing CTRL-F2 or ALT-F2. The indexes used belong to the database being read from. A field value also limits the records displayed. CTRL-F2 displays the database being read from based upon the index of the current field being read from. If no index is present, an error message will appear. The current value in the field acts as a filter to limit the records displayed in table view. If a null value is present, all the records are displayed. In date fields, records with null date values are displayed using the field value of "00/00/00". ALT-F2 allows any index present in the read database to be used as the primary index. Pressing ALT-F2 will display all fields in the read database that have an index. One may be selected and an optional filter value entered to limit the number of records displayed. Entering a null value for this filter will cause all the records to be displayed. The active keys in indexed table mode are exactly the same as in the sequential table mode except that appending is not allowed. Also, if a filter value is used, the search menu (F8) is not operational. Page 40 The Search Menu --------------- It is possible to perform searches while you are using the form, table or query. If the search menu may be called up, you will see the search key (F8 key) listed as one of the options on the active keys line. Press F8 to display the search menu. The following 5 submenu commands are on this menu: Goto, Search, Search & Replace, Search & Delete, Search & Undelete. Goto ---- Goto is one of the submenu commands of the search menu. It allows you to move directly to any record number you desire. The Goto command is executed as follows: 1. Press F8 to bring up the search menu. 2. Select Goto. 3. Type the record number you want to go to. 4. Press ENTER. Search ------ To perform a search: 1. Press F8 to bring up the search menu. 2. Select search. The fields of the database will be listed in a window. 3. Select the field that you would like to do the search on. 4. Enter the search string, that is, the value you would like to search for. If null string is used for the search string, then all records will be retrieved. Searching for null dates is done by using the search string "00/00/00". 5. Next you will see the following prompt, "Ignore case? (Y/N)". For a case sensitive search, type N. For a case insensitive search, type Y. Note: Case sensitive or case insensitive searches may only be done in a character or a single key field. A case sensitive search means that if the data you are searching for is written in all upper case letters, you should use upper case letters in your search string. Basically, you enter the search string using the same case (lower, upper, or mixed) as the data was originally entered into the form. Thus, if the data is in lower case and the search string is in upper case, the search string will not be found. A case insensitive search means that the search will be conducted using the letters that you have entered, Page 41 ignoring whether it is in upper or lower case. However, if you have created an index in which you ignored the case and you use this index for doing your search, then the search will be case insensitive. 6. If you are doing a search in a field that is indexed, you have the option of using the index to perform the search. You will see "(I)ndexed or (S)equential search?" Enter either I or S depending on the type of search you want. Using the index to perform the search will be much faster. Remember that an index is a table of record numbers arranged in sorted order to permit rapid access to a specific record. Therefore, when you use an index to do your search, the search will not proceed in the order in which the record appears in the database. Note: If you decide to do a sequential search instead of an indexed search, you will be allowed to choose whether you want to ignore the case while doing the search. A search will be made until the first field is found that contains your search string. For example, using a search string of "smi" in the surname field will find all the surnames that start with "smi", such as smith, smitty or smithers. If you want an exact search, enter a space after the string set. When the first field that contains your substring is found, PRO-dVelBase moves this record to the top of your screen and your cursor will be on this field if in table mode. On the message line, the third line from the bottom of the screen, you will see the prompt, "Continue? (Y/N)". Press Y if you want to continue. Press N if you do not want to continue. For an exact search: 1. Type your search string. 2. Type a space after your search string. If you do not type a space at the end, then the search will bring up all the occurrences of the search string. For example: Smith(SPACE) will find Smith Smith(without a space) will find Smithers, Smithen, Smith 3. Press ENTER. Your cursor will highlight the record that contains your search string. Note: If a null string is used, then the search will bring up the entire database, one record at a time. Page 42 Search & Replace ---------------- This command is a submenu command of the search menu. It is similar to the search command but it also allows you to replace all the search strings with a value derived from a replace formula. Perform your Search & Replace command as follows. 1. Press F8 to bring up the search menu. 2. Select Search & Replace. The fields of the database will be listed in a window. 3. Select the field that you would like to do the search on. 4. Enter the search string, that is, the value you would like to search for. If the search string is a null string (no value) then this becomes a global operation and all of the records are changed. Press ENTER. 5. Next you will see the following prompt for character fields, "Ignore case? (Y/N)". For a case sensitive search, type N. For a case insensitive search, type Y. 6. Enter the replace formula, that is, the value you would like to see in place of your search string. The formula must evaluate out to the same type as the search string. The replace formula has a maximum length of 255 characters and pressing CTRL-Z allows it to be edited in a window. Press ENTER. 7. As a precaution, you will also be asked, "Verify each change? (Y/N)". Press Y and you may verify each change. The search will stop at each spot where a record is found and it will ask whether you want to replace that field or not. Also the new value to replace the current field value will be displayed as a confirmation. If you answer NO to the above question, all found records will be updated without confirmation. You will not be asked whether you want to replace that field or not. Note: When replacing an exact word, include a space as part of the search string. Otherwise, the replace string will replace any occurrence of your search string. e.g. To perform a global update on a COST field, increasing each field by 10%, the following may be done. Use a null string as the search string in the COST field and the replace formula is COST * 1.1. Search & Delete --------------- To search and delete a record: 1. Press F8 to bring up the search menu. 2. Select Search & Delete. The fields of the database will be listed in a window. 3. Select the field that you would like to do the search Page 43 on. 4. Enter the search string, that is, the value you would like to search for. Press ENTER. 5. Next you will see the following prompt, "Ignore case? (Y/N)". For a case sensitive search, type N. For a case insensitive search, type Y. 6. As a precaution, you will also be asked, "Verify each change? (Y/N)". Answer YES and you may verify each change. The search will stop at each spot where a record is found and it will ask whether you want to delete that record or not. If you answer NO to the above question, the entire database will be updated globally. You will not be asked whether you want to delete that record or not. Note: When searching for an exact word, press the space bar to include a space as part of the search string. Otherwise, the search find all occurrences of your search string. When you delete a record, you are simply marking it as being deleted. Records that you have marked for deletion will have the word "Del" in the right lower corner when the cursor is over them and a diamond will appear beside the record number on the left side if in table view. In order to remove deleted records from the database, the database packing function must be performed. However, once you have packed your database you will not be able to undelete a deleted record. Search & Undelete ----------------- This function works in a similar way as the Search & Delete. The Search and Undelete function simply unmarks a record that was previously marked for deletion. This command will only work if you have not yet packed the database containing the deleted records. E. Form/Unselect ================ Form/Unselect is the opposite of Form/Select. Form/Unselect simply removes a given form from the work area. Also all associated objects in the work area are also unselected. F. Form/Delete ============== This submenu command is used to delete forms. When it is selected, you will be asked if you would also want to delete all the associated data files with the form and all the associated files in the catalog. The associated files in the catalog include any queries, reports, labels, mails, appends and posts that were created with the currently selected form. The default Page 44 to these questions is NO. Deleting the form only will only delete the .SCN, .FLD and .CAT files. Deleting the data files will delete the .DBF and .DBT files. Please be careful if you select this because there may be alot of data present in these files that you may not want deleted. Page 45 Chapter 3: Query ================= A query is a request by a user for information from the database. This request lays out the limits, conditions or criteria that needs to be met in order for data to be found and displayed. Therefore, the query acts as a filter. It will filter out as well as sort the appropriate records for subsequent operations. These operations may include the report generation, mail merge, label generation, append and post. There are eight submenu commands under the query menu. They include: Select, Execute, Create, Modify, Unselect, Delete, Add to Cat. and Remove from Cat. Whenever a query is created, it is specific to a given form. Therefore, the form must be selected before a query may be created. Like the form, a query must be created and saved before it may be selected and executed. Note: You only create your query once and save it. Thereafter, you just need to select it whenever you want to execute it. You may alter the query after it has been saved. This is done using the Query/Modify command. A. Query/Create =============== This command is used to create a query. All queries are specific to a given form. Therefore, before you may create your query, if you have not already done so, select the form that your query will be associated with. To Create a Query: 1. Select Query/Create. 2. Give this query a name. The name must be a dBASE name and it must follow dBASE conventions. Please refer to Appendix A: dBASE Naming Conventions. Try to make the name as descriptive as possible. For example, a query of all the names between the letters A to M might be called NAME_A_M. 3. Press ENTER. You will see a screen with all the field names across the top. The field names are written in the following format: database file name -> field name. This is done because for any given form, there may be fields that come from more than one database file. This is usually the case if your form is a multi-file form and consists of databases files that are related. The first line under the field names is the line that indicates the numerical view order of selected fields. The next line indicates the numerical sort order of the fields. Page 46 The following tables indicate the active keys on the query editor. ================================================================= Active Key Action ----------------------------------------------------------------- E or ENTER moves cursor to edit line. RIGHT moves the cursor bar right one cell. LEFT moves the cursor bar left one cell. S used for sort order. V used for view order. UP moves the cursor bar up one cell. DOWN moves the cursor bar down one cell. HOME moves the cursor bar to the last field. END moves the cursor bar to the first field. ESC abandons the query operation. F9 displays a list of the field if on edit line. ALT-F9 displays a list of the functions if on edit line. CTRL-F9 displays a list of the operators if on edit line. F1 context sensitive help. F10 saves the query. ================================================================ Query Editor ------------ The query method used by PRO-dVelBase is a query-by-example. The table consists of 10 rows going down and the number of columns is equal to the number of fields in the form. We will refer to the area in which you enter the conditions as cells. In this table, the query is created by placing conditions that must be met before a record will be filtered. Conditions are placed in each cell and all these conditions must be evaluated as being TRUE before the transaction will be included in the query. A condition consists of a relational operator and an expression. Here are the relational operators that will be accepted: ================================================================= Symbol Meaning ----------------------------------------------------------------- = Equal to <> or # Not Equal to < Less Than > Greater Than <= Less Than or Equal To >= Greater Than or Equal To $ Contains ================================================================= Page 47 An expression is a string that contains a sequence of operators, functions, field names and constants. All expressions evaluate to a specific type, such as numeric, character, date or logical. Therefore, field names and constants used in an expression must be of the same type. For example, you may add a numeric field only to a numeric field. To further clarify this point, the condition to query a surname field must contain an expression that returns a character type. An expression that returns a numeric type is not acceptable. Single key and time fields are considered to be equivalent to the character field type. It is not possible to query a memo field. Any query conditions placed in a memo field will simply be ignored. Multiple AND conditions may be placed on the query table. All conditions are on the same row. For example, if you want to search for all the Smiths living in Washington, then in the SURNAME cell, the condition would be ="Smith". In the CITY cell, the condition would be ="Washington" and these 2 conditions would be on the same line. This would retrieve a record or records that have the surname Smith and the city name Washington. Multiple OR conditions are placed going downwards. For example, the search of all the records in which the surname is equal to Smith or Jones. The conditions would be placed in the SURNAME cell with the condition ="Smith" on line 1 and ="Jones" on line 2. For characters, the search is case sensitive. To perform a multi-condition AND search on a given field, a "&" is placed between conditions and the conditions are placed in a single cell. For example, to find all the surnames greater than Jones and less than Smith, the following conditions would be entered into the SURNAME cell: >"Jones" & <"Smith". The "&" character is reserved for this purpose and cannot appear in the condition for any other reason. If a condition does not have anything before the operator, PRO-dVelBase assumes that the field name of the current cell is to be used in the expression. If anything is present before the operator, then PRO-dVelBase will evaluate the condition as a complete expression. For example, if the expression, SUBSTR(SURNAME,2,2) = "on", was used then all surnames whose second and third letters have "on" (such as Jones, Bones, Bonn) will be found. This expression may be placed in any cell and evaluated directly because the expression, SUBSTR(SURNAME,2,2), occurs before the equal operator. With this query-by-example method, very complex queries may be created and saved. Upon execution of the query, all the records that satisfy these conditions will appear in table view. Note: For a form that has a one-to-many relationship, the Page 48 query will only be applied to the first record of the one-to-many relationship. In order to query all the records of a one-to-many relationship, another form must be used that reverses the link so that a many-to-one relationship exists. To Select Fields for Viewing ---------------------------- The following procedure allows you to select the fields that you want to view and to alter the order in which you view these fields. 1. Press V. Two windows are displayed. The left window displays all the fields present in the database. The right window is initially empty. 2. Place the cursor, which is in the left window, on the first field that you want view. 3. Press ENTER. A check mark will appear beside the field name and this field name will be displayed in the right window. 4. Place the cursor over the next field that you want. 5. Press ENTER. A check mark will appear beside this field. Also, this second field will be displayed in the right window. 6. Continue this process until you have selected all of the fields that you want to be seen in table view. 7. To alter the order in which the fields appear in query view, use the RIGHTKEY to move the cursor to the right window. Locate the field you would like to move and place the cursor over this field. Press ENTER to lock the field onto the cursor. Use the UP or DOWN key to move the field to the position that you would like it to be viewed in. Press ENTER again to unlock and place your field into position. 8. If you have selected a field that you want to view in the query and then decide that you do not want to view this field, you may remove it from the view. Place the cursor in the left window over the field you want removed from the view. Press ENTER and the field will be removed from the view. 9. Press F10 to save the view or ESC to abandon. Once you save your changes, you will return to the previous screen. Note that the first line indicates the order that you have chosen for the fields to appear. This order is indicated by a number under the selected field name. Note that all the field names are shown across the top of the screen but not all field names will have a number under them. Only those that you have selected to be viewed will have a number to indicate the view order that you have assigned to it. If no fields are assigned to a query view, then PRO-dVelBase will display all the fields in the form. Page 49 To Sort the Fields ------------------ Proceed as follows: 1. Press S. Two windows will open. The left window will display the field names that you may choose from. The right window will display the fields that you have chosen. 2. Place the cursor over the field that you would like to sort by. 3. Press ENTER. A check mark will appear beside the field name and this field name will be displayed in the right window. 4. Place the cursor over the next field that you want. 5. Press ENTER. A check mark will appear beside this field. Also, this second field will be displayed in the right window. 6. Continue this process until you have selected all of the fields that you want to be sorted by. 7. If you want a sort to be case insensitive, press the SPACE bar instead. This will place a double arrow beside the field name instead of a check mark. 8. To remove a field from the sort, place the cursor over the field name in the left window and press ENTER. 9. To alter the order in which the fields are sorted by, use the RIGHTKEY to move the cursor to the right window. Locate the field you would like to move and place the cursor over this field. Press ENTER to lock the field onto the cursor. Use the UP or DOWN key to move the field to the position that you would like it to be viewed in. Press ENTER again to unlock and place your field into position. Upon saving your changes, you will return to the previous screen. The second line indicates the order that you have chosen for the fields to be sorted. Note: PRO-dVelBase will only allow you to sort up to 10 fields and their combined lengths cannot exceed 100. The sort order will also be correct even if the fields have different field types. The only field that cannot be sorted is a memo field. A sort does not physically rearrange the records in a database file. An index is created and the records will appear to be sorted when they are printed. Saving The Query ---------------- Proceed as follows: 1. Review your work on the screen. If you are happy with the view order, sort order and conditions that you have assigned for the query, press F10. 2. Give the query file a description. The maximum field length Page 50 is 40 characters. Try to make this file description as descriptive a possible. For example, if this query was a query of all the Smiths living in Los Angeles, simply call this file description, "All Smiths living in LA." 3. Press ENTER. The query is saved as a file with a .QCF extension (meaning query condition file). When you have created a query, you may select it and execute it. Upon execution, all the records that are evaluated as TRUE, will be displayed in a table view. B. Query/Modify =============== The modify selection on the query menu will allow you to modify the selected query. The rules for modification are the same as for creation. C. Query/Select =============== When using the Query/Select command, you are simply choosing the query that you want to use. Each query is associated with a specific form. Therefore, PRO-dVelBase will not grant you access to the Query/Select and Query/Create commands unless you select the form first. If no form is selected, all of the Query menu selections are turned off. Once you select your form, PRO-dVelBase only turns on the Select and the Create submenu commands. Then, when you select your query, PRO-dVelBase turns on the other Query submenu commands. However, you may only select a query that you have previously created and saved. Query selection places the query into the work area for use. Error checking occurs also on selection and if an error occurs the name will be put in lower case and a file description of UNEXECUTABLE and/or UNMODIFIABLE FILE will be given. If the file name is present in the catalog but the file does not actually exist then the message "FILE DOES NOT EXIST" will appear. This type of error may occur if a file is deleted from the DOS level instead of using PRO-dVelBase to delete it. To Select a Query ----------------- 1. Select Query/Select. At this point a window will open and a directory of the existing queries will be displayed. Note that the description is also shown. 2. Place cursor bar over the query that you want to use. 3. Press ENTER. At this point, you have chosen the query that you want to use. The name and description of the query that you have chosen will Page 51 appear in the work area. D. Query/Execute ================ Upon execution of a query, PRO-dVelBase will display in table view all the records that are evaluated as TRUE. The method that PRO-dVelBase uses to query a form is that it evaluates each record and if it is found to be TRUE, its record number is stored in a separate index file with a .QIF extension (meaning query index file). If this was the first time this query was executed or if the databases on the selected form were updated, then PRO-dVelBase will query the entire database. If the query had already been executed and no updates made on the databases, then you will be asked if you want to re-query the database. The querying process may be paused by pressing SPACE or aborted by pressing ESC. The active keys in the query view are exactly the same as those in the table view except appends are not allowed. You may also edit any of these records by pressing E. This allows you to enter the Query edit mode. Editing records at this point will not update the index therefore the record will still be displayed even though it may evaluate to be FALSE. A re-querying of the database is necessary. Pressing Esc will take you back to the main menu. In a one-to-many relationship, all the fields in the first linkage are present. Subsequent records in the relationship will not display the values present in the parent database file and the record number is displayed with a ditto (") to indicate that it is the same as the first linkage. It is not possible to edit the blank cells in a one-to-many relationship. E. Query/Unselect ================= The work area will display the name and file description of the query that is currently selected. Queries act as filters to select the appropriate records for subsequent operations. If you do not want your records to be filtered out, then you need to use the Query/Unselect command to remove the active query. When you use Query/Unselect, PRO-dVelBase will resume using all the records in the form for all subsequent operations. To clarify this point, suppose you created a query named NAME_B_M. This query is described as "Customer with Surnames between B and M". If you have selected this query, the query name (NAME_B_M) and the file description (Customer with Surnames between B and M) will be displayed in the work area. If you decide to print some labels, only the records of the customers whose surname fall between B and M will be printed. PRO-dVelBase Page 52 has used the query to filter out the records that you want to generate labels for. If you wanted labels for all the customers, then you would simply use the Query/Unselect command to remove the filter. F. Query/Delete =============== This command may only be accessed if you have selected a query. PRO-dVelBase will display this query in the work area. If you select the Query/Delete command, the query will be deleted after a confirmatory message. G. Query/Add to Cat. ==================== When a query is created, it is automatically added to a catalog. This catalog is the file that contains the names of related operational files, such as query, report, mail, label, append and post, for a selected form. There is one catalog for each form. All queries that were created under a particular form would be listed in the catalog for that form. This would keep the operational files of one form separate from the operational files of another form. To Add a Query to a Catalog --------------------------- Proceed as follows: 1. Select Query/Add to Cat. A window will open to display a list of all the queries that are present on the current data directory. 2. Place your cursor over the query you would like to add. 3. Press ENTER. A window will open to allow you to enter a file description. 4. Enter a brief file description. PRO-dVelBase will permit you to enter up to 40 characters for your file description. 5. Press ENTER. You have now added your query to the catalog. If you select Query/Select, you will see all the queries that are in the catalog. H. Query/Remove from Cat. ========================= This command is used to remove a specific query from the catalog. When you remove the query from the catalog, you are not deleting it from your hard disk. Rather, you are simply removing from it from the catalog so that it will not be displayed or used. Page 53 To Remove a Query from the catalog ---------------------------------- Proceed as follows: 1. Select Query/Remove from Cat. You will see the prompt, "Are you sure? (Y/N)" 2. Press Y if you are sure that you want to remove it. Otherwise, press N. If you pressed Y, you would have removed your query from the catalog. Page 54 Chapter 4: Report ================== A report may be defined as a document produced from all of the records or a subset of the records from a selected form. If a query filter is selected, this will limit and will also sort the records to be printed. There are eight submenu commands under the report menu. They include: Select, Execute, Create, Modify, Unselect, Delete, Add to Cat. and Remove from Cat. A. Report/Create ================ This command is used to create a report. To create a report: 1. Select Report/Create. 2. Give this report a unique name. This name must follow the dBASE naming convention as it will be a file name. If you are not familiar with these conventions, refer to Appendix A: dBASE Naming Conventions. The Report Definitions screen will now appear. This screen consist of a series of questions regarding the report as a whole. Number of columns: The first question asked is number of columns. On a standard printer this will generally be 80. If you have a wide carriage printer or if you are printing in condensed mode, you may increase the number of columns in the report. The range for the number of columns is between 40 and 255. ==> The default is 80. Number of rows: The next question is number of rows. For a standard printer, this will be 66. The range for the number of rows is between 20 and 100. ==> The default is 66. Maximum number of records per page: You may limit the number of records printed per page of the report. On certain preprinted forms, only a certain number of records may be printed per form. The range for this is between 0 and 99. If 0 is selected, then PRO-dVelBase will print out as many records as will fit on the page. ==> The default is 0. Page 55 Continuous paper? (Y/N) If you will be using continuous paper, answer YES. If NO, you will be prompted to insert a new piece of paper after each page is printed. ==> The default is YES. Print blank records? (Y/N) If there are blank records in the database file, should these be printed? Generally you will not want blank records printed. ==> The default is NO. Print deleted records? (Y/N) If records have been marked as deleted, should they be printed? Generally you will not want deleted records to be printed. ==> The default is NO. New page after each group? (Y/N) PRO-dVelBase is able to sort the database information by groupings. If you would like each grouping to be on a page of its own, then answer YES. ==> The default is NO. Prevent isolated group header? (Y/N) Group headers are printed at the beginning of each new group. If you want to prevent a group header from appearing by itself without its associated table, then answer YES to this question. ==> The default is YES. Name of Printer File? (A-Z) Printer files are created when a printer is defined. These are given the names A to Z. PRO-dVelBase automatically assigns a .PRT extension to them. Select the appropriate printer that you want this report printed under. Press F9 to display all the printer files that have already been created. ==> The default is A. Printer Port (A) LPT1 (B) LPT2 (C) LPT3 (D) COM1 (E) COM2: PRO-dVelBase allows printouts to occur through any of 3 parallel ports or 2 serial ports. They are numbered as LPT1, LPT2, LPT3, COM1, COM2 respectively. Generally, output will be through LPT1 but please check the configuration of your computer. You may also allow different reports, labels or letters to be printed through Page 56 different ports. For example, an invoice report may be printed out through a printer connected to LPT1 while a label printed out through a printer connected to LPT2. ==> The default is (A) LPT1. If a serial port (COM1 or COM2) is selected, the next series of questions will appear. Baud rate (A) 300 (B) 600 (C) 1200 (D) 2400 (E) 4800 (F) 9600: This indicates how fast the output through the serial port will be. The selections are 300, 600, 1200, 2400, 4800, 9600. Check your printer manual for these rates. ==> The default is (F) 9600. Parity (A) Odd (B) None (C) Even: ==> The default is (B) None. Stop bits (1-2): Stop bits may be 2 or 1. ==> The default is 1. Data bits (7-8): Data bits may be 7 or 8. ==> The default is 8. After you have answered the report definition questions, you will see the report editor. If you want PRO-dVelBase to bring up the questions again, you may press ESC. This will take you back to the report definitions screen. Report Editor ------------- The report editor is a full screen editor and operates through the "band" method. There are 4 different types of band: report band, page band, 5 group bands and a table band. The bands are laid out symmetrically so that there are an equal number of bands on the top as well as the bottom. The top group of bands are called the header bands. The bottom group of bands are called the footer bands. Page 57 The following table shows how each band works. ================================================================= Contents Within How it Will be Printed ----------------------------------------------------------------- report header band printed once only at the beginning of the report. report footer band printed once only at the end of the report. page header band printed at the top of each page. page footer band printed at the bottom of each page. group header band printed at the beginning of each group. group footer band printed at the bottom of each group. table band printed in the body of the report itself. ================================================================= Note: The contents of each band may be text and/or fields. Text information is printed exactly as is while fields are printed according to their value at the current record. The bands are initially set with one space in the report bands, page bands and the table band. The group bands are closed. Bands may be opened or additional spaces inserted by pressing ENTER while in the INSERT mode. The maximum number of spaces allowed in the bands is determined by the number of rows defined per page. Bands may be closed by pressing CTRL-Y while the cursor is in the band and removing the lines. Field Placement --------------- There are 3 different kinds of fields that may be placed on reports. They are database, calculated and system fields. This differs from forms, which only have 2 kinds: database and calculated. 1. Database fields are fields that come from a database file. 2. Calculated fields are virtual fields that are evaluated from formulas. 3. System fields will print current date, time, record number, record count or page. The following are steps to place a field: 1. Move the cursor to the spot where you would like to place that field. 2. Press F7, select the kind of field to be placed and answer the questions on the Report Field Definitions. Again, as in the form, the types of questions will depend upon what kind of field is being placed and how the previous questions were Page 58 answered. Filename->Fieldname: Select from the list which field to place. If this field has never been defined, the list will appear automatically but if it has previously been defined, press F9 to view the fields. This question only appears for the placement of database fields. Fieldname: This question only appears for the placement of calculated fields. Calculated fields must be given a unique fieldname. All calculated fields on a report are given the database name "CAL_R". This allows them to be accessed in formulas by other calculated fields. Calculation type? (S)um (A)verage (H)igh (L)ow (C)ount (F)ormula: These calculation types apply for calculated fields and have similar meaning to the corresponding types in forms. The first five types are summary calculated fields. Their exact effect depends upon which band they are placed in. Placing a summary field in the table band will only summarize the records in the table band if a one-to-many relationship is present. Placing a summary field in any of the footer bands will summarize the records for that particular group, page or the entire report. A formula calculated field placed in the table band evaluates each record separately in a one-to-many relationship. Also, calculated fields that reside in any footer band can only reference other fields that either reside on the same band or in the table band. They cannot reference any fields that reside in any other footer band. If they do, inaccurate values may result. Formula: Formulas are used for calculated fields and the effects of formulas differ depending upon the calculation type. In Sum, Average, High and Low calculated fields, the formula is first evaluated for each record then the appropriate summary operator is performed. In Count, the formula must evaluate to a logical TRUE or FALSE. If it evaluates to TRUE then the record is included in the count. In formula calculated fields, the evaluated results of the formula are displayed directly. What is the field type of the calculated field? (C)har (N)umeric (D)ate: Formula calculated fields can also have a type. The result of the calculation may either be character, numeric or a date. Page 59 Is this field printable? (Y/N) This question is asked for calculated fields, making it possible to hide them from printing. This produces the same effect as hidden calculated fields in forms and they act as temporary variables for calculations involving other fields. ==> The default is YES. What is the length of this field? Calculated fields have to be given a length. This is the length that will be printed. Using calculated fields makes it possible to place long database fields on the report by decreasing the field length. This will result in a truncation of the field value. How many decimal places are there? Numeric calculated fields require the number of decimal places be set. The maximum number of decimal places is 14. Picture: A picture dictates the format for how the data in the field should be displayed. The picture here is similar to the picture in forms in that special characters will be placed at the appropriate positions. The only difference is that only the character X is used to indicate the position in the string. The reason for this is that a report picture only acts as a template and not a mask. For example, a phone number field would have the picture XXX-XXXX. Display current (D)ate, (T)ime, Record (N)umber, Record (C)ount or (P)age? This question is only asked for system fields and their meaning is fairly self evident. The only one that may require explanation is current record count. The record count refers to the number of records printed so far. This type of field would be useful to number the records printed. Reset count after each group? (Y/N) The record count may be reset after each group. This feature is useful if you want each group to be a separate block with its own numbering. ==> The default is NO. Reset page after each group? (Y/N) The page count may be reset after each group. ==> The default is NO. Page 60 Date format? (A)-MMDDYY (B)-DDMMYY (C)-YYMMDD (D)-MON DD, 19YY (E)-MON DD/YY: Date fields may be printed in one of five formats. A picture may be used for the first three formats to place in the appropriate delimiter such as a slash (/). ==> The default is A. Time format? (A)-12 hrs. (B)-24 hrs.: Time fields may either be printed in 12 hour or 24 hour format. In 12 hour format, an AM or PM is automatically placed after the time. Display seconds? (Y/N) In time fields, the number of seconds may also be displayed. ==> The default is NO. Place at (C)urrent location or (A)djacent to previous text/field? Specify where you want to align your field in relation to other text or fields on the same line. Placing it at the current location means that the field will appear on paper in exactly the same location as it appears on the screen in terms of its horizontal alignment. Placing it adjacent to previous text or field, means that the field will be flushed immediately adjacent to any text or field on that line. ==> The default is at the current location. Justify (R)ight, (L)eft or (C)enter? Should the field be right, left or centre justified? Character fields are normally left justified while numeric fields are normally right justified. Remove trailing blanks? (Y/N) If any trailing blanks are present, should they be removed? Generally fields that are not fully filled have spaces added to the end to fill them. If these spaces are not removed, they will be printed. ==> The default is NO. Insert commas? (Y/N) Numeric fields may be formatted so that they have commas at every third place. This is useful for monetary values. For example 1000000 would be printed as 1,000,000. ==> The default is NO. Page 61 Preceding '$' sign? (Y/N) Numeric fields may have a dollar sign before them. Again this would be used for monetary values. ==> The default is NO. Number of columns in memo: and Number of rows in memo: These two questions are asked for memo fields and allow you to set the number of columns and rows to be printed in a memo field. The following table shows the active keys on the report editor. ================================================================= Active Key Action ----------------------------------------------------------------- ESC takes you to the previous screen and allows you to exit this section of the program, without saving. ARROW CURSOR KEYS moves the cursor in the direction of the arrows. PAGEDOWN moves down 20 lines. PAGEUP moves up 20 lines. HOME moves the cursor to the extreme left. END moves the cursor to the last text or field on the line. INSERT toggles between overwrite and insert mode. DELETE deletes the text or field on the cursor. BACKSPACE deletes the previous text character. RETURN or ENTER inserts a blank line when in insert mode only if the bottom line of the report is empty. F1 context sensitive help. F3 starts and ends blocking of an area. F4 inserts a printer control character (an inverted question mark) that causes the next character to act as a sequence of control characters to the printer depending upon how it was defined in the printer definition file. F5 copies blocked area. F6 moves the blocked area. F7 places and defines a database, calculated or system field. F10 completes and saves the report. CTRL-Y clears text only from the current line. ================================================================= One-to-many Relationships ------------------------- The records in a one-to-many relationship are set up in the same way as on a form. Simply place all the fields for one record on the screen, then use F3 to block them. Move the cursor to the Page 62 second line and press F5 to copy the block as many times as is required. When finished, press F3 again to unblock the fields. Remember that the maximum number of records on the many side of a one-to-many relationship allowed by PRO-dVelBase is 20, therefore only 20 lines may be copied. Group Expressions ----------------- The report can be grouped by entering group expressions into the group header band. This is done by pressing CTRL-G. A group expressions window will open and up to 5 group expressions may be used. The maximum length of an expression is 255. Pressing CTRL-Z will allow longer expressions to be edited in a window but generally group expressions only consist of a field name. Note that if group expressions are used, a query must be used and sorting must occur by these fields. Grouping occurs whenever the value of the group expression changes. The expression is evaluated for each record and all records that evaluate to the same value are grouped together when they are printed on the report. This type of feature may be used, for example, in a report that breaks down sales by region or by salesmen. When finished editing the group expressions, press F10 and the expressions will be placed in the appropriate group header band. Saving the Report ----------------- Once the report is completed, press F10 and give the report a descriptive, meaningful file description. An automatic check of the report is also done at this time. If any errors were detected, you will not be able to save the report unless the errors are corrected. B. Report/Modify ================ Once a report has been created, it may be modified. Modification follows the same rules as creation. The definitions for each field may be changed by placing the cursor over the field and pressing F7. The answers to the questions may then be changed. C. Report/Select ================ The process of selection places a previously created report into the work area. If any errors are present in the report, the report name will be placed in lower case in the work area and the description will state that it is an UNEXECUTABLE and/or UNMODIFIABLE report. If the file name is present in the catalog but the file does not actually exist then the message "FILE DOES NOT EXIST" will appear. This type of error may occur if a file is deleted from the DOS level instead of using PRO-dVelBase to delete it. Page 63 D. Report/Execute ================= Execution of the report allows it to be printed either to a printer, the screen or a disk file. If a query has been selected then the query acts as a filter to allow only a certain group of records to be printed. A query will either be done automatically if the data files are more recent than the query index file or if they are not, you may choose to re-query the database files. If the output is directed to a disk file, a name must be given to the output file. If the report was defined as using non-continuous paper, a prompt will be given when the end of page is reached and a new one is required. Remember, if group expressions are present in the report, an appropriate query must be selected that will arrange the database in an order that corresponds to the groups. E. Report/Unselect ================== This removes the report from the work area. F. Report/Add to Cat. ===================== Any report file with a .RPT extension may be added to the current catalog. Be careful though because a given report may not refer to the selected form. G. Report/Remove from Cat. ========================== The report file may also be removed from the catalog so that it no longer displays but still exists on the current DOS directory. H. Report/Delete ================ Deleting a report removes it from the DOS directory and from the catalog. A confirmatory message will first be given before proceeding. Page 64 Chapter 5: Mail ================ PRO-dVelBase is capable of performing a mail merge. The process of mail merge means taking a standard letter and merging it with the records in the selected form to produce a series of personalized letters. This feature is obviously quite useful in many applications such as advertising. The letter may either be created with PRO-dVelBase's internal word processor or an external word processor. If created with an external word processor, the file would have to be stored as a generic text file and then imported into PRO-dVelBase. Because of the way mail merge is handled, field placement occurs a little different than with reports or labels. Fields must be placed by using dBASE expressions and sandwiching them between the ASCII 20 character. There are nine submenu commands under the mail menu. They include: Select, Execute, Create, Modify, Unselect, Delete, Add to Cat., Remove from Cat. and Import Text File. A. Mail/Create ============== To create a letter for mail merge: 1. Select Mail/Create. 2. Give this file an 8 character name, following dBASE naming conventions. 3. At the Mail Definitions screen, answer the questions that you are prompted with. Left/Right/Top/Bottom margin: This refers to the margins of the page. The range that you may choose from is 0 to 40. ==> The default is set for 10 for left/right and 6 for top/bottom. Number of rows per page: Most letter size paper has 66 rows per page while legal size paper has 84. The range allowed is from 33 to 255. ==> The default is 66. The following questions have the same meaning as in the report. Please refer to that section for detail explanation of these questions. Continuous paper? (Y/N) Print blank records? (Y/N) Print deleted records? (Y/N) Page 65 Name of Printer File? (A-Z) Printer Port (A) LPT1 (B) LPT2 (C) LPT3 (D) COM1 (E) COM2: Baud rate (A) 300 (B) 600 (C) 1200 (D) 2400 (E) 4800 (F) 9600: Parity (A) Odd (B) None (C) Even: Stop bits (1-2): Data bits (7-8): The following table shows the active keys in the mail editor. ================================================================= Active Key Action ----------------------------------------------------------------- ESC takes you to the previous screen and allows you to exit this section of the program, without saving. ARROW CURSOR KEYS moves the cursor in the direction of the arrows. PAGEDOWN moves down 20 lines. PAGEUP moves up 20 lines. HOME moves the cursor to the extreme left. END moves the cursor to the last text on the line. INSERT toggles between overwrite and insert mode. DELETE deletes the character on the cursor. BACKSPACE deletes the previous character. F1 context sensitive help. F4 inserts a printer control character (an inverted question mark) that causes the next character to act as a sequence of control characters to the printer depending upon how it was defined in the printer definition file. ALT-F4 inserts an expression character (ASCII 20). All text sandwiched inbetween two expression characters is handled as a dBASE expression. F9 list fields. CTRL-F9 list operators. ALT-F9 list functions. F10 completes and saves the letter. CTRL-Y clears the text from current line. ================================================================= The mail editor is a mini word processor with word wrap features. The width of the screen is dependent upon how the left and right margins were set. The maximum size of the letter is limited to 20,000 characters. Field placement is different than on the report or label editor. Fields must be placed as dBASE expressions sandwiched inbetween two expression characters (ASCII 20). The expression character is generated by pressing ALT-F4. Page 66 It was designed in this way to allow letters to be created on an external word processor and imported to PRO-dVelBase. Formatting of the fields is also accomplished through dBASE expressions instead of answering a series of questions. When the letter is finished, press F10, give it a descriptive file name and save it. Again, error checking is also performed at this time and the letter cannot be saved until the error is corrected. B. Mail/Modify ============== Mail modification follows the same rules as mail creation. C. Mail/Select ============== Mail selection places the letter into the work area for use. If there are any errors in the letter, an error will be reported, the file name will be in lower case in the work area and the file description will read UNEXECUTABLE and/or UNMODIFIABLE FILE. If the file name is present in the catalog but the file does not actually exist then the message "FILE DOES NOT EXIST" will appear. This type of error may occur if a file is deleted from the DOS level instead of using PRO-dVelBase to delete it. D. Mail/Execute =============== The selected letter will be printed either to the printer, file or screen. Any formulas present in the letter will be automatically evaluated and the appropriate values inserted before printing occurs. If a query is selected, only the filtered records will be printed. E. Mail/Unselect ================ Unselection removes the mail from the work area. F. Mail/Add to Cat. =================== Any mail file with a .LET extension may be added to the current catalog. Be careful though because a given mail may not refer to the selected form. G. Mail/Remove from Cat. ======================== The mail file may also be removed from the catalog so that it no longer displays but still exists on the current DOS directory. Page 67 H. Mail/Delete ============== Deleting a mail removes it from the DOS directory and from the catalog. A confirmatory message will first be given before proceeding. I. Mail/Import Text File ======================== External word processors may be used to create a letter. Many stand alone word processors have much more powerful features than the mini word processor in PRO-dVelBase and thus are more suited for long letters. Also, this feature allows previously created letters to be used. In order for PRO-dVelBase to use these files, they must be stored as generic text files. This precludes the use of any special formatting codes in the stand alone word processor. Also, the size of the file imported is limited to 20,000 characters. Page 68 Chapter 6: Label ================= PRO-dVelBase is capable of printing labels. Labels are self-adhesive and can be pasted onto envelopes for mailing. The differences between the report and label generation are as follows: Label generation is capable of printing multiple copies of each record while report generation can only print one copy of each record. Label generation is capable of printing different records across the page while report generation can print only one record across the page. There are 8 submenu commands under the label menu. These include: Select, Execute, Create, Modify, Unselect, Delete, Add to Cat. and Remove from Cat. The selections under the label menu is similar to those under the report menu. A. Label/Create =============== Labels are created as follows: 1. Select Label/Create. 2. Give this file an 8 character name, following dBASE naming conventions. 3. Answer the questions on the Label Definitions Screen. Pressing F9 will automatically display many common label formats and selecting one will fill in the appropriate numbers for the questions. For dot-matrix printer labels, also set the number of labels across and down along with the inter-label spacing. For laser printer labels, ensure the printer is printing at 6 lines per inch. This is done by defining a printer definition file under Options/Printer Setup. Note that many laser printers cannot print on the top and bottom margins therefore the rows per page would have to be set at 60 instead of 66. Be sure that the labels used are approved for laser printers. Number of copies of each label: This refers to the number of copies of the label(s) that you would like to produce. The range that you may choose from is 1 to 255. ==> The default is set for 1. Page 69 Number of label columns: This refers to the number of character spaces across the label. Each character occupies one column. The range that you may choose from is 10 to 78. ==> The default is 35, which is for a 3 1/2 inch across label. Number of label rows: This refers to the number of rows there are down the label. The range is 1 to 18. ==> The default is 5, which is for a 15/16 inch down label. Number of rows per page: This refers to the number of rows that exist down the page. The range is 1 to 100. ==> The default is 66. Number of labels across: This refers to how many labels there are going across your page. Some labels are 1 across, meaning they exist in one long strip. The labels would be positioned one above the next. The range is 1 to 10 across, therefore, you may use labels that are from 1 to 10 across. ==> The default is 1. Number of labels down: This refers to the number of rows of labels that exist on one page. The range is 1 to 30. ==> The default is 11. Number of columns between labels: This refers to the number of spaces between each label in a horizontal direction, going across the page. Each space occupies one column. The range is 0 to 50. ==> The default is 0. Number of rows between labels: This refers to the number of rows that exist between each label in the vertical direction, going down the page. The range is 0 to 50. ==> The default is 1. Page 70 Left margin of page: This refers to the number of columns, or spaces, from the left edge of the paper to the edge of the first label. The range is 0 to 100. ==> The default is 0. Top margin of page: This refers to the number of rows from the top edge of the paper to the top edge of the first row of labels. Some printers, including some laser printers, are unable to print to the very edge of the paper. Therefore, to accomodate for this, some labels are made so that there is a margin on the top and on the bottom. The range for the top margin is 0 to 50. ==> The default is 0. Continuous paper? (Y/N) Answering NO to this question will cause PRO-dVelBase to prompt you to insert the next page. ==> The default is YES. Print blank records? (Y/N) You may specify if you want blank records printed. If you print the blank records, there will be a blank label where your blank record is. If you do not want to waste a label, then you should not print the blank records. ==> The default is NO. Print deleted records? (Y/N) If records have been marked as deleted, should they be printed? Generally you will not want deleted records to be printed. ==> The default is NO. Name of Printer File? (A-Z) The following printer questions have the same meaning as in a report file. Please refer to the appropriate section in Report/Create for the meaning of these questions. Printer Port (A) LPT1 (B) LPT2 (C) LPT3 (D) COM1 (E) COM2: Baud rate (A) 300 (B) 600 (C) 1200 (D) 2400 (E) 4800 (F) 9600: Parity (A) Odd (B) None (C) Even: Stop bits (1-2): Data bits (7-8): Page 71 The following table shows the active keys on the label editor. ================================================================= Active Key Action ----------------------------------------------------------------- ESC takes you to the previous screen and allows you to exit this section of the program, without saving. ARROW CURSOR KEYS moves the cursor in the direction of the arrows. HOME moves the cursor to the extreme left. END moves the cursor to the last text or field on the line. INSERT toggles between overwrite and insert mode. DELETE deletes the text or field on the cursor. BACKSPACE deletes the previous text character. ENTER inserts a blank line when in insert mode, only if the bottom line of the label is empty. F1 context sensitive help. F3 starts and ends blocking of an area. F4 inserts a printer control character (an inverted question mark) that causes the next character to act as a sequence of control characters to the printer depending upon how it was defined in the printer definition file. F5 copies blocked area. F6 moves the blocked area. F7 places and defines a database, calculated or system field. F10 completes and saves the label. CTRL-Y clears the text from a line. ================================================================= After answering the last question, the label editor will be brought up and a label will appear with the dimensions specified on the previous screen. This editor works exactly like the report editor. Text may be placed in it and fields may be placed by pressing F7. Again you may define database, calculated or system fields and the questions will be exactly the same as for the report editor. The differences between the label editor and the report editor are: 1. The size of the screen is smaller than for the report editor. 2. No grouping is allowed. 3. Memo fields may not be placed. 4. Calculated fields will have a database name of "CAL_L" instead of "CAL_R". The label is saved by pressing F10 and giving it a file description. Again, error checking is performed when F10 is pressed. Please refer to the Report chapter for a more complete Page 72 explanation of the editor. B. Label/Modify =============== Label modification follows the same rules as label creation. C. Label/Select =============== Label selection places the label into the work area for use. Error checking occurs also on selection and if an error occurs the name will be put in lower case and a file description of UNEXECUTABLE and/or UNMODIFIABLE FILE will be given. If the file name is present in the catalog but the file does not actually exist then the message "FILE DOES NOT EXIST" will appear. This type of error may occur if a file is deleted from the DOS level instead of using PRO-dVelBase to delete it. D. Label/Execute ================ Execution of a label allows the output to be directed to either the printer, screen or file. E. Label/Unselect ================= Unselection removes the label from the work area. F. Label/Add to Cat. ==================== Any label file with a .LAB extension may be added to the current catalog. Be careful though because a given label may not refer to the selected form. G. Label/Remove from Cat. ========================= The label file may also be removed from the catalog so that it no longer displays but still exists on the current DOS directory. H. Label/Delete =============== Deleting a label removes it from the DOS directory and from the catalog. A confirmatory message will first be given before proceeding. Page 73 Chapter 7: Utilities ===================== Utilities consists of a group of less used yet still important functions. These include: Append, Post, Setup, Re-index, Database Pack, Clone and Zap. A. Append ========= The process of appending consists of copying records from one database file to the end of a second database file. The file copied from is called the source database and consists of the primary database file of the currently selected form. The file copied to is called the target database. One of the main purpose of the append command is to store records that are infrequently used. For example, in an invoicing system, records older than one month may be appended from the current "working" database file to a "history" database file. This will keep the current working database small and easily manageable and also keep records indefinitely if necessary. Generally, the history database file is a cloned copy of the working database to ensure the fields of each database file are the same. There are eight submenu commands under the append menu. They include: Select, Execute, Create, Modify, Unselect, Delete, Add to Cat. and Remove from Cat. Append/Create ------------- To create an append file: 1. Select Append/Create. 2. Give this file an 8 character name, following dBASE naming conventions. 3. Answer the questions on the Append Database Definitions screen. Target Database: The target database is the database file being appended to. A pick list will appear with the names of all the database files (with extension .DBF) on the current directory. If no other database files are available, an error message will be generated. Select the file you would like to append to. If a target database was previously selected, press F9 to view this pick list. Do you want to delete transferred records? (Y/N) The appended records may be marked for deletion on the source database. Since this command works at the database file level, any linked records on the selected form will not be deleted. Only the records in the primary database file Page 74 will be deleted. You would generally want to delete the records in the primary database file and pack them after appending to ensure that only one copy of the record exists. ==> The default is YES. Do you want to update a logical field on the transferred records? (Y/N) Another way to know which records have been appended from the source database is to update a logical field. For example, if a logical field with the name APPENDED existed on the source database, all records appended would be updated to TRUE on this field. This method allows a way to mark the appended records without deleting them. ==> The default is YES. Logical Fieldname: If the answer was YES to the previous question, a logical field must be selected. If no logical fields exist on the source database file, you cannot proceed. A window will open and the field may be selected. If a logical field was previously selected, press F9 to open this window. After all these questions have been answered, the append editor will appear. It consists of two windows. The window on the left contains all the fields in the source database while the one on the right will contain all the fields on the target database. The process of creating an append file consists of placing fields in the target database in which you would like to append records to. The cursor bar is in the target database window. Moving the cursor bar up and down causes the arrow head in the source database window to move correspondingly. Place the arrow head over the first field you would like appended from. Press F9. This will open a window listing all the fields in the target database that have the same field type. Select the field you would like appended to. The field names do not have to match but the field types do. Continue this process until you have finished. If any mistakes were made, move the cursor to the appropriate field and press the DELETE key. When finished press F10, give it an appropriate file name description and save it. The created append file has an extension .APP. Append/Modify ------------- Append modification follows the same rules as append creation. Append/Select ------------- Append selection places the append file into the work area for use. Error checking occurs also on selection and if an error occurs the name will be put in lower case and a file description Page 75 of UNEXECUTABLE and/or UNMODIFIABLE FILE will be given. Append/Execute -------------- Execution of an append results in records from the source database being appended to the target database. If a query is present in the work area, only the records filtered by the query will be appended. You may want to execute the query first to see if the appropriate records will be appended. Append/Unselect --------------- Unselection removes the append file from the work area. Append/Add to Cat. ------------------ Any append file with a .APP extension may be added to the current catalog. Be careful though because a given append may not refer to the selected form. Append/Remove from Cat. ----------------------- The append file may also be removed from the catalog so that it no longer displays but still exists on the current DOS directory. Append/Delete ------------- Deleting an append removes it from the DOS directory and from the catalog. A confirmatory message will first be given before proceeding. B. Post ======= Posting is very similar to appending in that two database files are involved: a source database and a target database file. The difference lies in the fact that posting does not add new records to the target database; the records are only updated. Posting requires a key field on the target database to be indexed. When posting occurs, the records to be posted from the source database will update the corresponding records on the target database if a match is found on the key field. The type of update depends upon the field type. A numeric field type may be added, subtracted or replaced. A non-numeric field type may only be replaced. A typical example of posting occurs in an invoicing system. If you have two database files with one containing the sales made and the other containing the current inventory. The two databases have a common key field called ITEM_NUM, which Page 76 represents the item number. This field must be indexed in the inventory database file. The invoice database will contain all the sales made during the day. Posting will then allow the number sold of each item number in the invoice database to be subtracted from the number in stock field in the inventory database. This procedure will ensure that the number in stock is a correct count. There are eight submenu commands under the post menu. These include: Select, Execute, Create, Modify, Unselect, Delete, Add to Cat. and Remove from Cat. Post/Create ----------- To create a post file: 1. Select Post/Create. 2. Give this file an 8 character name, following dBASE naming conventions. 3. Answer the questions on the Post Database Definitions screen. Target Database: The target database is the database file being posted to. A window will appear with the names of all the database files (with extension .DBF) on the current directory. Select the file you would like to post to. If a target database was previously selected, press F9 to open this window. Do you want to delete transferred records? (Y/N) The posted records may be marked for deletion on the source database file. Since this command works at the database file level, any linked records on the selected form will not be deleted. Only the records in the primary database file will be deleted. ==> The default is YES. Do you want to update a logical field on the transferred records? (Y/N) Another way to know which records have been posted from the source database is to update a logical field. For example, if a logical field with the name POSTED existed on the source database, all records posted would be updated to TRUE on this field. This method allows a way to mark the posted records without deleting them. This is generally the preferable way to mark a record as being already posted because a query may be set up so that all records that have the POSTED field as TRUE will not be re-posted. ==> The default is YES. Page 77 Logical Fieldname: If the answer was YES to the previous question, a logical field must be selected. If no logical fields exist on the source database file, you cannot proceed. A window will open and the field may be selected. If a logical field was previously selected, press F9 to open this window. After all these questions have been answered, the post editor will appear. It consists of three windows. The window on the left contains all the fields in the source database, the window in the middle contains the operation to be performed and the one on the right will contain all the fields on the target database. The process of creating a post file consists of placing fields in the target database in which you would like to post records to and giving the appropriate operations to them. The cursor bar is initially in the middle operation window and the default operation is NONE. The NONE operation means that nothing will be done to the target field if the record is posted. Moving the cursor bar up and down causes the arrow head in the other two windows to move correspondingly. Initially define the key field. Move the cursor over to the key and press K. Press the right arrow and move the cursor to the target database window. Press F9 to view the indexed fields in the target database. Select the appropriate one. The entire process of creating a post file consists of placing fields to be posted to in the target database window and setting the appropriate operation in the middle operation window. The keys used to set the appropriate operation are K for Key, N for None, R for Replace, A for Add and S for Subtract. When the cursor is in the target database window, F9 will list the fields that have the same field type as the corresponding field in the source database window. To delete a particular field, press the DELETE key. When finished press F10, give it an appropriate file name description and save it. A post file is saved with extension .PST. Post/Modify ----------- Post modification follows the same rules as post creation. Post/Select ----------- Post selection places the post file into the work area for use. Error checking occurs also on selection and if an error occurs the name will be put in lower case and a file description of UNEXECUTABLE and/or UNMODIFIABLE FILE will be given. Post/Execute ------------ Execution of a post results in records from the source database being posted to the target database. If a query is present in Page 78 the work area, only the records filtered by the query will be posted. You may want to execute the query first to see if the appropriate records will be posted. Post/Unselect ------------- Unselection removes the post file from the work area. Post/Add to Cat. ---------------- Any post file with a .PST extension may be added to the current catalog. Be careful though because a given post may not refer to the selected form. Post/Remove from Cat. --------------------- The post file may also be removed from the catalog so that it no longer displays but still exists on the current DOS directory. Post/Delete ----------- Deleting a post removes it from the DOS directory and from the catalog. A confirmatory message will first be given before proceeding. C. Setup ======== Setup takes a pre-existing database file with extension .DBF and creates a field definition file (.FLD), screen definition file (.SCN) and a catalog file (.CAT). These are the three additional files necessary to define a form. This set is necessary before any database files created by dBASE may be used by PRO-dVelBase. When Setup is selected, a directory of all files with .DBF extension is listed. The appropriate file may then be selected. A warning will be given if the file already has an associated .FLD file. This is because Setup will delete the previous .FLD file. A form will then be created with all the fields listed vertically and their field names as labels for the field. Follow the instructions under Form/Create and Form/Modify to modify this form. When finished press F10, give the form a optional file description and save it. The form is now ready to use by PRO-dVelBase. Page 79 D. Re-index =========== This command rebuilds the index files associated with the primary database file of a form along with all the linked database files if desired. Generally, PRO-dVelBase automatically handles updating of the index when new records are added or when pre-existing records are modified. Sometimes the index files may get corrupted. Symptoms of a corrupted index file include inaccurate sorts on indexed table view and inability to access linked records through a key field. The Re-index command will fix this problem. E. Database Pack ================ Packing is a command that physically removes the records that have been marked for deletion from the database file. It is generally recommended to backup the files first before packing in case you change your mind about the pack. Related files may also be packed at the same time and PRO-dVelBase will prompt you if there are any related files. F. Clone ======== Cloning is a command that allows the structure of the primary database file to be reproduced. The currently selected form is used for cloning. A name must be given for the new form along with an optional file description. Each field of the primary database file will be reproduced along with its fixed field definitions. Changeable field definitions, formulas, error formulas, error messages, help messages, calculated fields and scroll windows are dropped along with any relationship definitions and indexes. The new form also does not have any data in it. One of the purposes of cloning is to produce a database file that can act as a target database for the append command. This allows records to be saved in a "history" database file and deleted from the current "working" database file to keep it small. G. Zap ====== Zapping a database file along with Form/Delete are potentially the most dangerous commands in PRO-dVelBase. Zapping completely removes all the records in the primary database file of the selected form in one swoop. A warning message will be given before being allowed to proceed. Page 80 Chapter 8: Options =================== Options consists of a group of functions that affect where and how PRO-dVelBase will operate. They include: Change Dir, Recalc, Flush, Printer Setup, Save Options and Quit. A. Change Dir ============= It is generally recommended that a particular application be stored on a separate subdirectory rather than on the same one that PRO-dVelBase resides on. The data directory may be changed using this option. The naming convention follows standard DOS convention. Drives may also be changed by using the commands A:\ for A drive, B:\ for B drive, etc. B. Recalc ========= The recalc command toggles automatic recalc ON and OFF. With the recalc ON, all calculated and derived fields will be automatically recalculated each time a field is updated in the form. With it OFF, recalculations only occur when a record is saved. In both cases manual recalc with CTRL-C always works. The default is ON. It may be turned off if PRO-dVelBase is running on a slower machine and many calculated or derived fields have been defined. C. Flush ======== The flush command causes all DOS buffers to be immediately flushed once a write occurs. Normally writes to a disk are stored in a DOS buffer and only when the buffers are full does a disk write occur. This situation is dangerous if you experience a power failure before the buffers are flushed. The default for flush is ON and this guarantees that a write will write that data immediately to the disk. You may elect to turn off the flush if you are using a floppy disk system as flushing does slow the saving of each record. D. Printer Setup ================ Printer Setup allows printer definition files (drivers) to be defined. These files have the extension .PRT and store the control codes for various print styles for your particular printer. This is an important preliminary step in installing PRO-dVelBase. Up to 26 different printer definition files may be created with the names A.PRT to Z.PRT. Define as many printer definition files as you have printers connected to your computer. The control codes stored in these files are used by report, label Page 81 and mail. Printer Setup/Create -------------------- Selecting this command will take you to the printer definition screen. First a Printer File Name has to be assigned. The name must be a letter from A to Z. PRO-dVelBase defaults to the next available letter. Next, the name of the printer must be entered. Editing of the fields occurs by pressing E or ENTER. The control codes for each of the given printer effects must then be entered in decimal form with a space between each code. Most printers that require multiple numeric control codes start with the escape code (ASCII 27). If a certain effect does not exist, you may leave the codes out. Please refer to your own printer manual for the correct codes. Initialization codes refer to the codes that must be sent to the printer to clear and reset the printer. PRO-dVelBase automatically sends these out before a report, label or mail is printed. Termination codes are the codes that are sent to the printer indicating the print job is finished. These are automatically sent out when a report, label or mail is finished printing. Enter the control codes for each of the effects listed. Generally an effect has a turn on code and a turn off code. If a printer has a special effect, such as superscript, subscript, etc., the codes may be entered at this point and the name of the effect also saved. The printer control codes may also automatically be filled in by pressing F9 and selecting your printer. Many printers have control codes that are the same as the printers listed. Epson control codes are commonly used by many printers and selecting an Epson printer is usually a safe bet if your printer is Epson compatible. Many laser printers are compatible with the H.P. LaserJet therefore selecting one of the LaserJet options may work. The codes are sent to the printer when the inverted question mark (ASCII 168) is encountered in a report, label or mail. The screen code defines which printer control codes will be sent out to the printer. Note that an upper case letter will turn on the effect and a lower case letter will turn off the effect. When finished, press F10 to save the printer definition file. Printer Setup/Modify -------------------- A previous defined printer definition file may be selected from the directory and modified. Page 82 Printer Setup/Delete -------------------- A previous defined printer definition file may be deleted. E. Save WorkArea ================ This command saves: 1. The contents of the work area. 2. The status of the recalc, flush and insert. 3. The current data directory. This information is stored in the file PRO.OPT and the next time PRO-dVelBase is executed, the saved options are automatically loaded. F. Quit ======= This commands exits from PRO-dVelBase and automatically closes any open files. Page 83 Appendix A: dBASE (TM) Naming Conventions ========================================== 1. PRO-dVelBase uses dBASE naming convention for all file names. 2. A database file name may contain up to 8 characters. Therefore, PRO-dVelBase will not even permit you to enter file names having more than 8 characters. 3. The first character of a database file name must be a letter of the alphabet. 4. Except for the first character, the rest of the characters may be letters, numeric digits, or the underscore(_). 5. Database file names may not contain spaces or symbols other than the underscore. The reasons for this is that DOS uses the period (.), the slash (/), the colon (:) and the semicolon (;) for special functions. Therefore, these symbols should not be used in file names. As a precaution, PRO-dVelBase will prevent you from making these errors. Note: All file names will be forced to upper case regardless of what case you use to enter them. 6. There are also a few names that are reserved by PRO-dVelBase for internal usage. These names are CAL_F, CAL_R AND CAL_L. They are used for calculated fields therefore cannot be used as file names. 7. Field names follow the same convention except they may be up to 10 characters long. 8. All file and field names used by PRO-dVelBase must follow these conventions. Page 84 Appendix B: Formulas and Functions =================================== PRO-dVelBase allows you to perform mathematical operations as well as complex queries. This chapter will help you to understand the basis for these procedures. It will help you in writing formulas and conditions using operators and functions. A. Formulas =========== A PRO-dVelBase formula is equivalent to a dBASE expression and consists of a string that contains a sequence of operators, functions, field names and constants. All expressions evaluate to a specific type, such as numeric, character, date or logical. Therefore, field names and constants used in an expression must be of the same type or convert to the same type. For example, you may add a numeric field only to a numeric field. To further clarify this point, the condition to query a surname field must contain a formula that returns a character type. A formula that returns a numeric type is not acceptable. The only exception to this rule is that a whole number may be added to or subtracted from a date type resulting in another date type. PRO-dVelBase has two additional types not found in dBASE: single key and time. These two types are considered equivalent to character fields. Also memo fields cannot be used by formulas and formulas cannot evaluate to a "memo type". B. Constants ============ Constants have a fixed value and do not change. There are 4 types of constants that may be used in expressions. They are the numeric, character, date or logical constants. Numeric Constants ----------------- The numeric constant represents a number. Some examples are: -7, 8.5 and 20. Character Constants ------------------- Character constants are letters enclosed within a set of quotes. They may be enclosed within double or single quotes. However, the set of quotes used must have its matching counterpart at the beginning and at the end. For example, "Mary Brown" or 'Mary Brown' would be legitimate. Also, if you need to use a set of quotes within a set of quotes, you would distinguish them by Page 85 using one type of quotes inside another type of quotes. For example, "Mary Brown's" or '"Please"' are legitimate character constants. Logical Constants ----------------- Logical constants are the values in a logical field. For example, .TRUE., .FALSE., .T., .F. would be legitimate logical constants. Note that the periods must be included as part of the constant. Date Constants -------------- Date constants cannot be represented by themselves. A date constant can only be represented by using the function CTOD to convert a character constant in the form "CCYYMMDD" to a date. For example the date July 4, 1990 is represented by the function CTOD("19900704"). C. Operators ============ Operators are the symbols that manipulate elements of formulas. These symbols may represent numeric, string, relational and logical operations. For example, the + (plus) sign is a numeric operator in the expression: 72 + 24. The order of precedence refers to the priority of an operator when evaluating a formula that contains more than one operator. The operator with the higher priority will be evaluated first. For example, in the expression 4 + 8/2, the divide operator has a higher priority than the plus operator. Thus, 4 + 8/2 = 8. All formulas are evaluated from left to right. Subformulas that are enclosed in () brackets will be evaluated first, with operators being evaluated by their order of precedence. Page 86 The four basic types of operators are numeric, string, relational, and logical operators. Their order of precedence is shown on the following table. ================================================================= Type Operator Name Symbol or Name Order of Precedence ----------------------------------------------------------------- Numeric Sign (positive or negative) of a numeric expression Unary +,- 7 Exponentiation ** or ^ 7 Multiply * 6 Divide / 6 Add + 5 Subtract - 5 String Concatenate I + 5 Concatenate II - 5 Relational Equal to = 4 Not Equal to <> or # 4 Less Than < 4 Greater Than > 4 Less Than or Equal To <= 4 Greater Than or Equal To >= 4 Substring Comparison $ 4 Logical And .AND. 3 Or .OR. 2 Not .NOT. 1 ================================================================= Page 87 The following table shows some examples of operators. ================================================================= Operator Type Action Example ----------------------------------------------------------------- Numeric operates on numeric values 4 + 4 returns 8 String Concatenate I connects two character values end to end, retaining all spaces in each value. "Mary " + "Brown" becomes "Mary Brown" Concatenate II connects two character values into one, moving all spaces at the end of the first character value to the end of the combined value. "Mary " - "Brown" becomes "MaryBrown " Relational used to return a logical (TRUE or FALSE) result. These operators operate on numeric, character or date values. However, both values being compared must be of the same type. These operators are primarily used in data search routines. 9>20 returns FALSE The Substring Comparison operator operates on two character values. It is used in logical conditions. It returns a TRUE value if the first value is equal to, or found within the second value. "Mary" $ "Mary Brown" returns TRUE Logical will return a logical result and ties two logical values together. Primarily used in data search routines. 1=1 .AND. 2=2 returns TRUE ================================================================= Page 88 D. Functions ============ Functions may be recognized easily because they always have a function name and are followed by a left and right bracket. There may also be specific values or parameters enclosed within the left and right brackets. These are called arguments of the function. The arguments must be of a specific type, such as a character, numeric or date type. You will note that some functions do not have arguments in their brackets. However, to differentiate a function from a field name, functions that have no arguments will have a set of empty brackets following their function name. The following is a list of the functions that PRO-dVelBase will permit you to use. The function names may be either upper or lower case. ABS(Num_Value) -------------- Returns the absolute value of a numeric value. e.g. ABS(-30) returns 30 ASC(Char_Value) --------------- Returns the ASCII value of the leftmost character of a character value. e.g. ASC("Jones") returns 74 AT(Char_Value1, Char_Value2) ---------------------------- Returns the starting position, as a numeric value, of the first character value within the second character value. e.g. AT("XYZ","ABCXYZ") returns 4 CDOW(Date_Value) ---------------- Returns the name of the day of week as a character string from the given date. e.g. CDOW(DATE()) If the date is Monday, December 3, the function returns "Monday". CHR(Num_Value) -------------- Converts a numeric value to its corresponding ASCII character value. e.g. CHR(80) returns "P" Page 89 CMONTH(Date_Value) ------------------ Returns the name of the month as a character value from the given date. e.g. CMONTH(DATE()) returns "January" if today is January 10, 1990 CTOD(Char_Value) ---------------- Character to Date. Converts a character value to a date value. The character value would be in the format "CCYYMMDD". e.g. CTOD("19901203") DATE() ------ Returns the system date in the format CCYYMMDD. DAY(Date_Value) --------------- Returns the numeric value of the day from the given date. e.g. DAY(DATE()) returns 10 if today is January 10, 1990 DBF() ----- Returns the name of the current database file that you are using. e.g. DBF() returns "CUSTOMER.DBF" if you have selected and are currently using the customer database. DEL() ----- Returns a "*" to indicate that the current record is marked for deletion. Returns " " if the current record is not marked for deletion. DELETED() --------- Returns TRUE to indicate that the current record is marked for deletion otherwise it returns FALSE. DOW(Date_Value) --------------- Returns the day of week as a numeric from the given date. Sunday is considered day 0. e.g. DOW(DATE()) If the date is Monday, December 3, the function returns 1. DTOC(Date_Value) and DTOS(Date_Value) ------------------------------------- Date to Character and Date to String. Converts a date value to a character value. The resulting character value will have the format "CCYYMMDD". e.g. DTOC(DATE()) If the date was December 3, 1990 the resulting character value would be "19901203". Page 90 EXP(Num_Value) -------------- Exponential value. Returns the exponential value of the numeric value. e.g. EXP(1) returns 2.71828183 FIELD(Num_Value) ---------------- Returns the fieldname of the field whose position, in the database structure, corresponds to the position of the given numeric value. e.g. Field(3) If PHONE_NO is the third field defined in a file called CUSTOMER.DBF, then this returns "PHONE_NO". IIF(Condition, Expression1, Expression2) ---------------------------------------- Immediate-if function. Returns Expression1 if and only if the condition evaluates TRUE; otherwise, it returns Expression2. e.g. IIF(cost>1000,"high cost","low cost") Returns "high cost" if and only if cost is greater than 1000; otherwise, it returns "low cost." INT(Num_Value) -------------- Converts the given numeric value to an integer. e.g. INT(4.1797) returns 4 ISALPHA(Char_Value) ------------------- Returns the logical value, TRUE, if the Char_Value begins with an alpha character. Otherwise, the logical value, FALSE, is returned. e.g. ISALPHA("123-xyz") returns FALSE ISLOWER(Char_Value) ------------------- Returns the logical value, TRUE, if the leftmost character is in lower case. Otherwise, the logical value, FALSE, is returned. e.g. ISLOWER("aBcD") returns TRUE ISLOWER("ABC") returns FALSE ISUPPER(Char_Value) ------------------- Returns the logical value, TRUE, if the leftmost character is in upper case. Otherwise, the logical value, FALSE, is returned. e.g. ISUPPER("abc") returns FALSE ISUPPER("ABC") returns TRUE LEFT(Char_Value, Number of characters from left) ------------------------------------------------ Returns the given character value, showing only the specified number of characters from the left. e.g. LEFT("Mary Smith",4) returns "Mary" Page 91 LEN(Char_Value) --------------- Returns the length of the character value. e.g. LEN("Mary Smith") returns 10 LOG(Num_Value) -------------- Returns the natural logarithm of the numeric value. e.g. LOG(2.71828183) returns 1 LOWER(Char_Value) ----------------- Converts all upper case alpha characters to lower case. e.g. LOWER("Mary Smith") returns "mary smith" LTRIM(Char_Value) ----------------- Trims leading blanks from the character value. e.g. LTRIM(" Hello") returns "Hello" MAX(Num_Value1, Num_Value2) --------------------------- Returns the larger of the two numeric values that are compared. e.g. MAX(4.97, 5.97) returns 5.97 MIN(Num_Value1, Num_Value2) --------------------------- Returns the smaller of the two numeric values that are compared. e.g. MIN(4.97, 5.97) returns 4.97 MOD(Num_Value1, Num_Value2) --------------------------- Returns the integer remainder that is left when you divide Num_Value1 by Num_Value2. e.g. MOD(16,4) returns 0 MOD(15,4) returns 3 MONTH(Date_Value) ----------------- Returns the numeric value of the month from the given date. January is considered month 1. e.g. MONTH(DATE()) returns 1 if today is January 10, 1990 RECCOUNT() ---------- Record count function. Counts the total number of records in the database and returns the number count. e.g. RECCOUNT() will return 8000 if there are 8000 records in the database. RECNO() ------- Record number function. Reads the record number of the current record and returns that record number. Page 92 RIGHT(Char_Value, Number of characters from right) -------------------------------------------------- Returns the given character value, showing only the specified number of characters from the right. e.g. RIGHT("Mary Smith",5) returns "Smith" ROUND(Num_Value1, Num_Value2) ----------------------------- Rounds off Num_Value1 so that it will show the number of decimal places as specified by Num_Value2. e.g. ROUND(4.797970, 3) returns 4.798 RTRIM(Char_Value) ----------------- Trims trailing blanks from the character value. e.g. RTRIM("Smith ") returns "Smith" SPACE(Num_Value) ---------------- Adds the number of spaces as specified by the numeric value. e.g. FIRST_NAME + SPACE(1) + LAST_NAME If the first name was Mary and the last name was Smith, this returns "Mary Smith". SQRT(Num_Value) --------------- Returns the square root of the numeric value. e.g. SQRT(9) returns 3. STR(Num_Value, Length, Decimals) -------------------------------- String function. Converts a numeric value into a character value and displays it according to the specified length and decimal place stated in the function. The length indicates the number of characters allowed, including the decimal and unary +/-. The decimals indicate the number of decimal places allowed. If the resulting string contains more characters than the length of characters permitted, then *'s will be returned. e.g. STR(4.16345,5,3) returns "4.163". SUBSTR(Char_Value, Starting Position, Number of Characters) ----------------------------------------------------------- The substring function returns a portion of the given character value. The character value will start at the Starting Position and be the length specified by the Number of Characters. e.g. SUBSTR("XYZ",2,1) returns "Y" SUBSTR("Mary Brown",7,3) returns "row" TIME() ------ Time function. Returns the system time as a character value in the format HHMMSS. e.g. TIME() returns "090000" if it is 9:00 a.m. Page 93 UPPER(Char_Value) ----------------- Converts a character string to upper case. VAL(Char_Value) --------------- Converts a character value to a numeric value. e.g. VAL("-7") returns -7 VAL("18") returns 18 YEAR(Date_Value) ---------------- Returns the numeric value of the year from the given date. e.g. YEAR(DATE()) returns 1990 if today is January 10, 1990 Page 94 Appendix C: Warning and Error Messages ======================================= Warning messages indicate something adverse may happen if you proceed. It is possible to proceed as long as you are aware of the consequences. Error messages will not allow you to proceed unless the error is corrected. A. Warning Messages =================== Error Formula Evaluates to False. Save Anyways? An error formula on the form evaluates to FALSE. The record may be saved anyways or else the field value re-entered. Field Definition File Already Exists. Continue Setup? A setup was attempted on a .DBF file that has an associated .FLD file. The previous .FLD file will be deleted if the Utilities/Setup operation is done. Foreign Key Field of Different Length. Relate Anyways? The common key fields between a parent and child database files should be of the same length. If they are not of the same length, errors may occur in linking the two databases. Try to change the field length of the key in either the parent or child so they match. Possible Circular Reference. Save Anyways? A circular reference is when a formula refers to another field with a formula that ultimately refers back to the original formula. Saving a circular reference will generally cause errors but there may be some cases where a circular reference would be the best way to define the form. It is recommended to try the form out with a few records first to see if the calculations evaluate correctly. Record(s) Changed. Save? The current record(s) has been changed. The changes may be saved or discarded. Required Field Not Filled. Save Anyways? A form has a required field that was not filled. The record may be saved or the required field have a field value entered. Warning. Destination Will Be Overwritten. Proceed? An attempt was made to copy or move a block to a target with text or field present. The target text or fields will be overwritten. Page 95 WARNING! Data Files Will Be Deleted. Continue? Deleting the form will delete the primary database file. A lot of data may be lost therefore be careful when you choose this option. WARNING! File Exists. Replace? The output file name already exists on the current directory. This will overwrite the previous file. WARNING! This Will Remove All Records. Continue? The zap function will remove all records from the primary database file. A lot of data may be lost therefore be careful when you choose this option. B. Error Messages ================= The following is a list of PRO-dVelBase error messages. Catalog File Not Found. The associated catalog file with .CAT extension could not be found for the selected form. It may have been deleted from the DOS level. The catalog file may be re-created by modifying the selected form and saving it again. Database File is Missing a Field. One of the field description files (.FLD extension) has an extra field not found in its corresponding database file (.DBF extension). This is generally remedied by running the Utilities/Setup function on the offending database file and re-creating the field description files and screen description files (.SCN extension). Database Not Located While Evaluating Formula. The database file specified in the formula was not located. Duplicate Key Value Not Allowed. The entered field value is already present in another record within the database file. This error message will only occur if the current field is indexed and the "Prevent Duplicate Entries" question is answered YES. Error Building Index. This error indicates that there was either insufficient memory or there were too many records in the database to index. Error Closing Index. This error indicates that an index file could not be closed. Try reindexing the form. Page 96 Error in Printer Code String. The string of printer codes entered has one or more wrong codes. Executing Null Formula. Execution of a null formula was attempted. Expecting ',' or '(' While Evaluating Formula. The formula is probably missing a comma or a left bracket. Extra Field(s) in Selected File. The currently selected object has extra field(s) not found in the selected form. This may be corrected by modifying the object and deleting the extra fields. Field Not Found in Underlying Form. The cause of this error depends upon where it was generated. If in the form, it generally indicates a field that has been deleted in a related form. If in one of the other objects, then it indicates the field has been deleted from the currently selected form. This error may be corrected by deleting the offending field. Field Length Mismatch or Field Type Mismatch. A discrepancy exists between the field length or type described in one file compared with the field length or type described in a second file. In the form selection, this may occur when one of the fields in the field description file (.FLD extension) has a length or type that does not match the length or type in the corresponding database file (.DBF extension). This is generally remedied by running the Utilities/Setup function on the offending database file and re-creating the field description files and screen description files (.SCN extension). If this error occurs in other objects, the object should be modified, and the offending field corrected or deleted. Generally this error occurs when a field length or type has been altered and there are related files that also reference the altered field. Field of Given Type Not Present. This error occurs in the process of creating an append or post file. It indicates that no fields of the appropriate field type are present. Fieldname Already Used. Each field in a database file must have a unique name. File Closing Error. Unable to close a file. Generally indicates a corrupted file or a disk failure. Page 97 File Creation Error. This error may be caused by a full directory or a disk failure. File Does Not Exist. The selected file does not exist on the current directory. This may occur if the file has been deleted from the DOS level but its name is still in the catalog. The file name has to be removed from the catalog and the file re-created. File Name Already Used. The given file name already exists and a different one must be used. File Name is a Reserved Word. An attempt was made to use a reserved word as a file name. The words "CAL_F", "CAL_R" and "CAL_L" are reserved by PRO-dVelBase for internal use to represent calculated fields. File Not Found. The requested file was not found on the current directory. File Opening Error. The requested file does not exist and may have been deleted at the DOS level. Another possible cause is an insufficient number of file handles. Try increasing the number of opened files allowed by using the command "FILES=60" in the CONFIG.SYS file and restarting the computer. File Reading Error. A read error could be caused by a disk failure. File Writing Error. A write error will occur when the disk is full. Formula is Not Complete. The formula could not be evaluated because it is incomplete. Help File Not Found. The PRO.HLP file could not be found. Illegal Date in Formula. An illegal date was encountered when evaluating a date value. Incompatible File Type. An attempt was made to open a non-PRO-dVelBase file. The offending file must be deleted and re-created. Index File is Missing a Key. The index file key of a database record does not exist. Page 98 Insufficient Memory. This is a fatal error and immediately exits to DOS after it is encountered. Either the amount of RAM has to be increased, any TSR programs unloaded or the form has to be simplified and some of the relationships removed. Insufficient Space for Field Placement. There is insufficient space at the current position of the form to place a field of the defined length. INTERNAL ERROR NUMBER: This indicates an unresolvable error. Please contact Accelerated Software with the details. Invalid Date. The entered date is incorrect. Invalid Number. The entered number is incorrect. Invalid Operation for Given Field Type. This error may occur in creating a post file. Only numeric field types are allowed to be added or subtracted. Invalid Time. The entered time is incorrect. Key Field Already Defined. Only one key field allowed in a post file. Key Field Must Be Indexed. This error may occur when creating a post file. The field in the target database must be indexed before it can be posted to. Key Value Doesn't Exist. The entered value does not exist in the related database file. This error will only occur if question "Reject if value doesn't exist in related database" is answered YES. Key Length Over 100 Characters. Index files allow for a maximum of 100 characters for the key. Logical Update Field Not Present in Source Database. If a logical field is to be updated when appending or posting, the field must first be defined in the source database. Maximum Number of Printer Files Already Defined. A maximum of 26 printer files allowed. Maximum of 127 Fields. A maximum of 127 fields allowed per database object. Page 99 Maximum of 5 Database Files Per Form. A maximum of 5 related database files allowed per form. Maximum of 20 Records in a One-To-Many Relationship. The current version of PRO-dVelBase allows a maximum of 20 records on the many side of a one-to-many relationship. Memo File Missing. The memo file (with .DBT extension) for the current database file could not be found. It was probably deleted at the DOS level. Multiple Field Copies Not Allowed. This error occurs in the process of creating or modifying a form. Multiple copies of the same field are found on the form. The user was probably attempting to create another field with similar definitions by blocking and copying. This process, however, will create another field with the same field name which is not allowed. The offending field must be deleted and re-defined. No Files Found. No files could be found on the current directory. No End Formula Character. The dBASE expression is not sandwiched between the formula character (ASCII 20) in a letter. No Index For Present Field. Unable to perform an indexed search because current field lacks an index. No Indexes For Related Database. Unable to view the related database in indexed table view because it does not have any indexes. No Key Fields Present. This error occurs in the process of relating forms and indicates that no indexed fields are present. No Record(s) Found. No records found that match specified criteria. No Selected Label. There is no label file in the work area. No Selected Mail. There is no mail file in the work area. No Selected Report. There is no report file in the work area. Not a Textfile. Cannot import a non-textfile into mail merge. Page 100 Only One Memo Field Allowed Per Form. Only one memo field may be defined per form. Overflow While Evaluating Formula. The formula is too large or too complex for PRO-dVelBase to evaluate. Overlay File Not Found. Overlay file for PRO-dVelBase could not be found. Parameter or Operator has the Wrong Type. Type checking for formulas occur at run time. The formula has a type mis-match. Path Not Found. The specified path could not be found. Picture Too Short. A picture must have the same number of user entered characters as the length of the field. Posting Requires a Key Field. An attempt was made to save a post file without a key field. One key field must be defined for each post file. PRO.PRT File Not Available. Unable to find the main printer definition file. Range Error. The field value is beyond the specified range allowed for the field. Right Bracket Missing in Formula. The formula requires equal number of right brackets as left brackets. Selected Object Not Found in Catalog. Catalog does not contain any of the selected objects. They must either be created or added to the catalog. Text File Too Long. Maximum length of text file allowed is 20,000 characters. Trying to Move Block Beyond Screen Window. The marked block will partially end up outside of the screen window. Unable to Create an Empty Database File. This error occurs when a form is saved with no fields in the primary database file. Each database file must contain at least one field. Page 101 Unrecognized Function in Formula. The formula has a function that is not recognized as a legitimate PRO-dVelBase function. Unrecognized Operator in Formula. The formula has an operator that is not recognized as a legitimate PRO-dVelBase operator. Unrecognized Value in Formula. The formula has a value that is not recognized as a field, string, number, logical value or function. Unterminated String in Formula. A character string must terminate with a right quote. Page 102 Appendix D: PRO-dVelBase Files =============================== The following is a list of files present in PRO-dVelBase or created by PRO-dVelBase. The '*' will indicate any legitimate DOS file name. Generally the name should also follow dBASE naming convention. A. Program Files ================ PRO.EXE, PRO.OV1 and PRO.OV2 These files represent the main program file along with its overlays. PRO.HLP PRO-dVelBase help file. PRO.PRT Main printer definition file. B. Created Files ================ *.APP An append file stores the names of the database files and their associated fields which are to be appended. *.BAK and *.TBK These are the backup files to the main database files created when a form is modified. *.CAT This is a catalog file and contains the names of all associated files for a given form. *.DBF and *.DBT These are the main database files along with an associated memo field file. These files are dBASE compatible. *.FLD This is the field definition file and contains all of the Fixed Field Definitions for a given form. *.LAB A label file which stores a given label design. *.LET A letter file used in the process of mail merge. *.NDX These are the index files for PRO-dVelBase and they are dBASE compatible. Page 103 *.QCF This is a query condition file and stores created queries. *.QIF This is a query index file and is created once a query is executed. It stores the record numbers of the records which match the conditions stored in *.QCF. A.PRT to Z.PRT The user created printer definition files. *.PST A post file stores the names of the database files and their associated fields which are to be posted. *.RPT This is a report file and stores the format of the previously created report. *.SCN A screen definition file stores the Changeable Field Definitions for a given form, field locations on the screen and the relationships with other database files. Page 104 Appendix E: Trademark Notices ============================== 1. PRO-dVelBase is a trademark of Accelerated Software 2. dBASE is a trademark of Borland International Inc. 3. Clipper is a trademark of Nantucket Corp. 4. CodeBase is a trademark of Sequiter Software. 5. dBXL is a trademark of WordTech Systems Inc. 6. Fox Base is a trademark of Fox Software. 7. MS-DOS is a trademark of Microsoft Corp. 8. PC-DOS is a trademark of IBM.