Sbase Personal 4 Edited by DIT 17-02-96 Also available as an AmigaGuide 1 GETTING TO KNOW SBase 4 The Workbench Database Data Types Compatibility Installation of SBase 4 The Multi-Layered Database About this Manual The Primary Work Surface The Secondary Database Management Surface The Form Designer System Automation and Customizing Loading SBase 4 The SBase 4 Window The SBase Menus Ghost Items Selecting Commands Sub-menus Shortcuts The SBase Work Area Opening a File SBase 4 Requester Boxes Using Requester Boxes Using the Keyboard with SBase 4 Changing the Directory Path Names Choosing the CLIENTS File Record View Using the Browsing Controls Table View Page View Using a Form Exiting from SBase 4 Invoking the SBase 4 Form Designer Where To Go From Here 2 UP AND RUNNING IN 15 MINUTES Step 1 Name the Database File Step 2 Define the Fields of the Database Step 3 Create an Index Step 4 Enter the Records Step 5 Layout the Merge Letter Step 6 Save the Merge Letter Step 7 Merge the Data and the Letter Step 8 Print the Form Letter 3 DEFINING NEW FILES Files and Forms Entering the New File Name Creating an Empty Copy Passwords The File Definition Requester Creating a New Field Selecting the Field Type Text/Logical Field Numeric Field Rounding Money Format Selecting Numeric Format Type Selecting Numeric Format Features Overflow Internal Storage Date Fields Changing the System Date Day Month Year Order Month Style Year Style Separators Dates As Numbers Time Fields Changing the System Time External File Fields Adding Fields Other Data Attributes Required Read Only Fields Virtual Fields Multiple Response Fields Defining a Multiple Response Field Saving the File Definition 4 CREATING INDEXES Overview Using Indexes Unique Index Normal or Duplicate Index Indexing a New File Composite Indexes Example Reindex 5 MODIFYING A FILE Changing Field Characteristics Adding a New Field Deleting a Field Renaming a Field Changing Field Types Saving the Modified File Definition Rules for Changing Fields Text / Logical Numeric Date Time Validation, Calculation and Constant Read Only Required Virtual 6 ENTERING DATA Entering New Records in Page or Record View Entering Text All Fields or Selected Fields? Entering New Records in a Form Editing Existing Data Selecting Another Record Editing Controls Field Selection with the Mouse Page and Record View Field Markers Moving from Field to Field Editing within a Field Undo Editing Entering Dates Entering Times Entering Numbers Required Fields Read Only Fields Validated Fields Calculation Fields Constant Fields Creating Duplicate Records The Clipboard Field Level Cut, Copy and Paste Cut Copy Paste Pasting Multiple Records 7 DATA ENTRY VALIDATION Types of Validation Ranges Lists Patterns Cross-File LOOKUP Constructing a Validation Formula Validation Help Message Further Information 8 DERIVED VALUES Field Calculation Formulas Constructing a Calculation Formula Using Dates and Times in Formulas Using the System Variables When is a Calculation Performed? Linked Calculations Calculation Count Self-referencing Formulas Checklist Constants Generating Serial Numbers Using the Ternary Operator Extended Ternary Operations Self-referencing Ternary Operations Further Information 9 USING FUNCTIONS Where To Use Functions Types of Function How to Use Functions Special Functions 10 RETRIEVING DATA INTERACTIVELY Opening File Opening the Current File Current File and Open Files Changing the Current File Closing a File Opening Forms Report Forms (SBase Pro 4 Only) Open Index Changing the Index Reversing Index Order Browsing Controls Forms Transaction Paging (SBase Pro 4 Only) Key Lookup Entering Partial Values Filter The Filter Requester Fields Box Operators Value Box Text box Using the Operators Text String and Pattern Matching: LIKE LIKE Syntax LIKE Examples The Relational Operators Parentheses The Mathematical Operators Filter Command Line Editing Using a Filter Suspending a Filter Modifying or Re-activating a Filter Optimizing the Filter Filters and Forms 11 WORKING WITH DBASE FILES Opening a dBase File Using an Index with dBase Files Saving a dBase File Definition Modifying Field Display Formats Creating Virtual Fields for dBase Files LOOKUP with dBase Files dBase Files in Forms 12 SELECTING VIEWS AND FIELDS Selecting Fields The Selected Fields List Storing the Selected Fields List Record View Table View Page View Scrolling the Screen Switching Between Views and Form Display Show Field Names Paging Paging in Table View Paging in Record View 13 PRINTING DATA AND FORMS Page Setup and Font Selection Creating or Modifying Printer Control Files Printing Forms Form Fonts Printing Views Show or Hide Field Names Single Records and Forms Printing the Current File Using Project Print File 14 DEFINING AND USING QUERIES Query Applications Opening a Query The Query Definition Requester Defining the Query Title, Date, and Page Number Query Command Panel Selecting Query Output Destination Defining Query Output Selecting File and Fields Defining Derived Columns Defining Column Headings Defining Column Position Defining Field Length for Output Defining Numeric Field Format for Output Inserting Lines Page Oriented Output Printing Long Text Fields Additional Format Parameters Defining Report Features Defining a Query Filter Defining a Sorting Order Setting the Number of Characters on which to Sort Single and Multiple File Sorting Sorting on a Multi-field Index Running a Query Saving a Query 15 REPORTING Query Report Definition Defining Global Report Functions The Concept of Record Groups Defining Record Group Functions Reporting on Derived Columns Overflow in Subtotals or Totals Totals for Non-Printing Fields Report Example Global Report Totals, Averages, and Record Counts Adding Group Totals, Averages, and Record Counts Example Output Avoiding Meaningless Command Lines 16 REORGANIZING THE DATABASE Restructuring a Database File Defining the Query Fields line Reindexing Multi-file Operations Reorganizing a Database Recovering Data by Reorganization Creating an Empty Duplicate File Splitting a Database File Merging Database Files 17 MULTI-FILE APPLICATIONS Files for Multi-File Applications Tombstone Data (Only in Sbase Professional 4) Transactional Data (Only in Sbase Professional 4) Table Data Avoiding Repeating Fields in a File Definition Data Integrity Defining Common Reference Fields Common Reference Field Validation Common Reference Field Cross-File Lookup Forms for Multi-File Applications Summary Data Entry in a Multi-File Application Preparing for Data Entry Using Lookups Entering Data into Multiple Files Transaction Blocks (Only in Sbase Professional 4) Cross-File References for Non-Form Data Entry Using Virtual Fields Defining and Using Multi-File Queries Defining a Multi-File Query Fields Line ON File Entering ON File Defining a Multi-File Query Report Line Defining a Multi-File Query Filter Optimizing Queries Optimization with Operators Using OR in the Query Filter Line Sequencing Precision Defining a Multi-File Query Order Line Defining and Using Multi-File Updates Setting the Update Filter Setting the Update Definition Optimizing an Update 18 LINKING FILES Cross-file Validation Text Field Attributes LOOKUP Requesters (Only in Sbase Professional 4) REQUEST 20 (Only in Sbase Professional 4) Cross-file Calculation Cross-File Calculations and Forms 19 UPDATING The Update Menu Commands Starting a New Update Selecting File and Fields Setting the Update Filter The Update Definition Requester Using the Colon Separator Forcing Calculations to Evaluate 20 REMOVING RECORDS Removing Records with Cut Removing All Records Removing Selected Records Example 21 IMPORT Overview When to Import dBase Files Uses of the Import command Import File Types ASCII Files ASCII Delimited Files ASCII Delimited File Separators Quotation Marks ASCII Fixed Length Files Converting Files Converting ASCII Delimited Files Converting Database Files Converting Spreadsheet Files Merging Files General Considerations Merging ASCII Files Using Process Import 22 N>EXPORT Preparing to Export a File Using Process Export 23 EDITING TEXT " link SB1:Chapter23/Main} The Text Editor Window Selecting Full Screen Closing the Text Window Managing Text Files from the Project Menu Working with Non-SBase Files Creating a New Document Entering Text Word Wrap Paragraphs Editing Text Moving the Insertion Point Basic Editing Controls Reformatting Deleting Small Areas of Text Cut and Paste Mark Block Clear Block Cut Copy Paste Cut, Copy and Paste with Other Files Cut, Copy, Paste Key Equivalents Inserting Text Insert Typeover Inserting New Lines Document Layout Showing the Ruler Settings Altering the Ruler Settings Multiple Rulers Example Setting Margins Text Styles Search and Replace Search Repeating a Search Replace Control Key Equivalents 24 PRINTING TEXT Further Information 25 MAIL MERGE Preparing for Mail Merge Creating a Form Letter Inserting Date and/or Time Using the & Character in the Text Fixed Length Fields Running Mail Merge Setting the Mail Merge Filter Previewing Mail Merge Blank Lines Mail Merge Applications Mail Merge with Multiple Files Programming Mail Merge (Only in Sbase Professional 4) 26 LABEL PRINTING Storing Label Formats on Disk Further Information 27 COMMUNICATIONS Local or Remote Using Utilities Communications Wild Card Transferring a File The Transfer Status requester Remote Transmission Cable Connections Communications under DML in SBase Pro 4 28 INTEGRATING TEXT AND IMAGE FILES Text Files Image Files ANIM Files (Only in Sbase Professional 4) Sound Files External File Field File Name (same disk, same directory) File Name (same disk, different directory) File Name (different disk) External File Window Text Text Types Supported Graphics Image Types Supported Viewing External Files Playing ANIM Files Image Scaling and Aspect Ratio Image Scaling Aspect Ratio Gray scales and Color Mapping Image Justification Parameter Priorities Images in the Form Designer External File Fields on Forms Editing External Text Files on Forms Printing External Files Querying External Files Searching External Text Files 29 FUNCTION KEYS (Only in Sbase Professional 4) Defining a Function Key List Function Key Assignments Function Keys for Commands Examples Function Keys for Text and Program Editing Editing a Function Key String Function Key Files Saving Function Key Files 30 HOUSEKEEPING DATABASE FILES Removing Database Files Removing Index Files Project Remove Index Backing up Data Operating System Commands Directory List Type Delete Rename Copy Documentation Status File Status System Project Print Command 31 CUSTOMIZING YOUR SYSTEM The S:SBase4.INI File System Options Ansi File Conversion Currency Symbol Number Format Display Speed Hide Panel Calculation Count Buffer Count Start Directory Date/Time Format Numeric Format 32 COMMAND REFERENCE SUMMARY Menu Commands Project Menu Edit Menu Record Menu Process Menu Set Menu Utilities Menu DML Menu (Only in SBase Pro 4) Key Controls SBase 4 Menus Browsing Controls Data Entry Text Editor Controls Requester Response Keys Selection Keys 33 GETTING TO KNOW THE FORM DESIGNER Use of External Fields Designing Screens Designing Forms for Printing Starting the Form Designer Relationship to SBase The Form Designer Menus The Toolbox Opening a Form Selecting Objects Selecting a Single Object Selecting a Group of Contiguous Objects Selecting a Group of Discrete Objects Deselecting an Object Selecting an Object for Moving or Sizing Creating a Form Where To Go From Here 34 THE FORM DESIGNER TOOLBOX Hiding The Toolbox Graphic Objects Boxes Lines Areas Images Text Selecting Font Changing Text Attributes Data and Logical Objects Moving Objects Sizing Objects Color Selector Border Color Selector Pen and Paper Palettes Color Palette Redefining the Color Palette Area Pattern Palette Line Pattern Palette Object Editing Tools Field and Area Borders Field Justification Text Style Currency Calculation (SBase Pro 4 Only) Read Only Object Display Only Object External File Image Scale 35 DESIGNING AND EDITING FORMS Basic Design Concepts Editing Aids Ruler and Grid Snap to Grid Crosshairs Auto Field Names Reduced View Placing Objects on the Form Defining the Object Hierarchy Placing Graphic Objects Placing Images Placing Text Selecting a Font Placing Fields External File Fields Fields from dBase Files Saving Forms Editing Forms Adjusting Form Page to Match Printer Page Selecting Objects Undo Cut, Copy, Paste, and Clear Editing Text Altering an Image Changing File and Field References Managing Pages New Page Opening and Saving Pages Page Clear and Page Erase Page Next / Previous / Go to ... 36 TRANSACTION LINES IN FORMS (Only in Sbase Professional 4) The Transaction Lines requester Editing Transaction Lines A Transaction Form Example Linking Files Setting the Data Entry Order for Transactions Copying Fields Creating a Table Form 37 DEFINING DATA ENTRY ORDER IN FORMS Example Changing Data Entry Order 38 LINKING FILES WITH FORMS Overview Linking Files The files available for linking are listed in the left-hand panel. Master File Common Reference Fields Adding a File to the Link Structure Editing a Link Structure 39 FORM CALCULATION FORMULAS (Only in Sbase Professional 4) Creating a Calculation Formula Calculations Formula Evaluation Variable Calculations Assignment Calculations Evaluation Control Prefixes 40 IForm;Validation>FORM VALIDATIONS (Only in Sbase Professional 4) Creating a Validation Formula Changing a Validation Formula 41. N>COMMANDS AND CONTROLS IN FORMS (Only in Sbase Professional 4) Pushbuttons Editing Pushbuttons Using Pushbuttons Commands Without Pushbuttons Auto Execution When is a Command Executed? Interfacing to a DML Program Programmability: A Caution Check Boxes Defining a Check Box Editing a Check Box Radio Buttons Defining a Radio Button Editing a Radio Button Using Form Controls in DML 42 GENERATING REPORTS (Only in Sbase Professional 4) Overview Procedure Summary Example Report Form Example Report Program Editing Report Forms Sorting Filter Field Selection Placing Fields in a Select Box Sizing the Select Box Heading Report Heading Column Headings Date, Time, and Page Number Footing Sorting by Group Multiple Groups Before Group Subtotals and Other Report Functions Report Function Syntax Global Before Report After Report Options Page on Record Page on Group Page before Report Page before Totals Summarize Test Saving the Report Form Formatting Report Output Running a Report Adding Filter Conditions Adding Order Conditions Print/Display Option Modifying a Report Program Output Device Filter and Order 43 PRINTING FORMS Printer Setup Printing Forms 44 HOUSEKEEPING IN FORM DESIGNER Directories and Subdirectories Listing the Directory Status Removing Forms and Pages 45 LEARNING TO USE SBase 4 Lesson 1: Defining a File New File Name Requester Reference Name Number, Type, Bank_name, Bank_code, Phone, FAX, SS Number, Street, City, ST, Zip_code Date_opened Balance Interest_rate Interest New Indexes Lesson 2: Entering Data Opening a File Adding New Records Lesson 3: Editing a Record Duplicating a Record Lesson 4: Using the Browsing Controls Select First Record Select Next Record Select Previous Record Select Last Record Rewind: Scanning in Descending Order Fast Forward: Scanning in Ascending Order Retrieving Individual Records: Key Lookup Pause and Stop Current Record Lesson 5: Working with Filters How Filters Work Defining a Filter Extending a Filter Overriding the Filter with Key Lookup Lesson 6: Importing Data Lesson 7: Selection and Output Selecting Fields for Output Showing Selected Fields in Table View Printing Selected Fields Lesson 8: Printing Labels Defining the Label Layout Defining the Label Dimensions Defining Print Parameters Alignment Test Printing Saving the Labels Format Lesson 9: Mail Merge Typing the Merge Document Inserting Merge Fields Running Mail Merge Lesson 10: Globally Updating a File Lesson 11: Querying the Database Selecting Query Defining a Title Selecting Fields for Output Adding Reporting Functions Defining a Filter Defining a Sorting Order Viewing Query Output Saving the Query Lesson 12: Creating Text Type Forms To enter text: Now we will but a block of color behind the text. Now we will draw four more graphic objects on the form. The next graphic element we will add is lines. Next, more text will be placed on the form. Incorporating Database Fields Lesson 13 Using External Files in a Form Define the External File Field in the Database Modify the Form Lesson 14 Viewing dBase files with SBase 46 N> FORM DESIGNER COMMAND REFERENCE Project Menu Edit Menu Page Menu Define Menu Report Generator Menu (Only in Form Designer Pro in SBase Pro 4) Toolbox APPENDIX A. Installation of SBase 4 Select Installation Mode Relocating SBase on a Hard Drive Working with International Character Sets APPENDIX B: Error Messages Error Message Numbers APPENDIX C: Functions APPENDIX D: Reserved Words APPENDIX F. SBase 4 FILE TYPES System Files Database Files Other Application Files APPENDIX G. Technical Support 90-Day Free Phone Support Free Mail Support Free FAX Support Contract Phone Support On-Line Support APPENDIX H. Startup, Tooltype and .INI File Options Command Line Options Tooltypes Options .INI file Options Automatic Loading of a DML Program