"SBase Personal - 10. RETRIEVING DATA INTERACTIVELY" 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 Professional Only) Open Index Changing the Index Reversing Index Order Browsing Controls Forms Transaction Paging (SBase Professional 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 10 RETRIEVING DATA INTERACTIVELY SBase 4 provides a number of controls for browsing through files and selecting records. These operate both for single files shown in one of the three default views and for forms which refer to more than one file. Most of the explanations in this chapter apply equally to browsing through both files and forms. Opening Files Retrieving Data Interactively Select Open and File from the Project menu. SBase 4 displays this requester if you are runing AmigaDOS 1.3 *** See Fig 10-1 *** In the box on the left, SBase 4 presents you with a list of all the files in the current directory. The list box on the right allows you to change to another directory. To open a file: If you are running AmigaDOS 2.0, the file requester will appear as follows: *** See Fig 10-2 *** 1. Click a file name and when it appears in the text box, click OK. or: 2. Double-click the file name. If you want to alter the name in the text box, you can: - Click another name to replace it. - Click Clear to empty the box. - Click in the text box and edit the contents. You might need to do this if your file is in another directory and you need to insert the pathname. If you wish to open a dBase file, click the dBase checkbox. See Chapter 11 Working with dBase Files. Opening the Current File When you select a file name and click OK, SBase 4 finds the File Definition for that file and returns you to the Work Area. If the file is being opened for the first time in the session, SBase 4 automatically selects the first index for the file, determined by the position of the index field in the File Definition. The record selected for display is then the first record according to this index. If you have already opened the file during the session, SBase 4 returns to whichever index and record were current when the file was last used. The file you see on the Work Area is known as the current file. You can always tell which is the current file by looking at the title bar of the main display window, which names both the file and its current index: SBase - FASTHD:SBPER4/KTEST1 indexed on firstname There is only one current file at a time, although you may have many files open. Current File and Open Files You can return to the Open File requester and open as many files as you want. Each time you select a file, it becomes the current file. However, all the files you select in one session remain open and available to other SBase 4 functions, unless you specifically close them with the Close command. SBase 4 functions that refer to the list of open files are: Project Remove File Process Update Process Query Utilities Status System Changing the Current File If a number of files are open at the same time, pressing the `+' key on the numeric keypad cycles through them, making each in turn the current file. The other way of changing the current file is to use the Open command: if you have already opened a file, selecting it again from the Project Open File requester makes it the current file. Closing a File You may want to close a file to release some memory back to the system, or perhaps because you have already have a file open when you wish to open another file of the same name in a different directory. 1. Make the file you wish to close the current file. 2. Select the Project Close File command. Note If you want to close all the open files at once, select the Project Close All command. Opening Forms Select Open and Form from the Project menu. SBase 4 displays a standard file selector requester that lists any SBase 4 form files, with the SBV extension, in the current directory. To open a form, proceed exactly as for a file. When a form file is opened, it opens any files it needs that are not already open. For a multi-file form, the initial display shows the first record in the master file and any related records. The master file, as defined in the Form Designer SB File Link command, becomes the current file, indexed on its default index. You may return to the Open File requester at any time to choose another file as the current file. Report Forms (SBase Professional Only) The Form Designer Pro generates a SBV file for both a screen form and a report form. Report form SBV files are also listed in the Open Form requester. If you open a report form, SBase presents you with the option to execute the associated report program. Open Index The index that you select determines the order in which the records in the current file are presented. If, for example, you have a file of clients for which SBase 4 maintains indexes on the lastname field and the city field, you can choose to view or print the records in the alphabetical order of either lastname or city. SBase 4 allows only one current index at a time, as indicated on the title bar of the Work Area. There are a number of important points to note about the restriction to one index: - It does not restrict your ability to specify multiple values when searching for a group of records in the file using a filter. - It does not limit your ability to produce reports with many levels of sorting and subtotaling. - When you are modifying a record or adding new data, all the indexes in a file are updated - whether they are open or not. - You may create an index which applies to more than one field at a time. See Composite Indexes, Chapter 4 Creating Indexes. When you select the Index Open command, SBase 4 displays a requester with a list of all the indexes for the current file. *** See Fig 10-3 *** Choosing an index from the requester is easy: 1. Click the name of the index you want. When the name appears in the text box, click OK. or: 2. Double-click the index name. If you make the wrong selection, change it by clicking on another index, clicking on Clear, or typing directly into the text box. When you click OK, SBase 4 opens the required index and returns to the Work Area. The name of the open index appears on the title bar: SBase - FASTHD:SBPER4/KTEST1 indexed on lastname All browsing operations and Project Print File now follow the order of the index. The Key Lookup button allows you to type in a single index entry in order to retrieve an individual record directly. Changing the Index There are two ways of switching from one index to another. First, you can use the Index Open command and select the required index as described above. Second, you can press the `-' key on the numeric keypad. This cycles through all the indexes in a file, making each one in turn the current index. When you change the index, SBase 4 will not change the current record but all browsing operations will follow the order of the new index. If you need a new index on a field that doesn't have one, you must use the New Index command to build the index. See Chapter 4 Creating Indexes. Each index points to one record in its file. This means you can swap to another index, browse through the file in the order of that index, and then return to the first index and view the record it was originally pointing at. Reversing Index Order The order in which indexed fields are shown is normally ascending. For text fields this means sorting from A through Z. Date fields are sorted from earlier to later, and numeric from smaller to larger. You may prefer to reverse the order used by a particular index during a session, for example if you wanted to step through records using a diminishing numeric or amount value. 1. Select the File Index Open requester. 2. Choose the index to be reversed. 3. Click the Descending order check box. 4. Click OK. The index appears with a (D) after it in the window title bar. The browsing controls will now operate in reverse, so that, for example, Fast Forward with a date index will go from later to earlier. Browsing Controls The browsing controls at the bottom of the screen provide a variety of ways of selecting records according to the current index. Each button has a special purpose, and together they fall into three groups. On the left are the Pause and Stop buttons, followed by seven buttons for browsing through the file; for those who prefer to use the keyboard rather than the mouse, each of these buttons has a keyboard equivalent. On the right are three special purpose buttons: the Key Lookup, Filter, and Camera buttons. The first two are explained later in this chapter; for more information on the Camera button, see Chapter 28 Integrating Text and Image Files. With a single file in Record, Page, or Table View, you select one record at a time. You are not confined to one index. You could browse through the Authors file equally well by Lastname or by Date of Birth, provided you had indexes on both fields. Forms Multi-file forms are more complex. When you create a multi-file form in the Form Designer, you define a link structure that informs SBase 4 how to retrieve related records during browsing. The link structure includes the concept of a master file. This is simply the file that SBase 4 uses to set the default browsing order, and its name is shown in the window title bar as the current file. When you select the next record in the master file, SBase 4 retrieves all the related records from other files. For example, if your form showed details of authors and their books, and Authors was the master file, selecting the next author would also display records from the Books file that were linked to that author via a common reference field such as Author_code. See Chapter 17 Multi-File Applications for more discussion of these concepts. The master file of a form need not be the current file. If you make one of the other files on a form the current file (by pressing the `+' key) then browsing will use that file as the temporary master file. So you could step through the Books file retrieving details from Authors file. Transaction Paging (SBase Professional Only) If your application uses a one-to-many relationship such as Authors and their Books, you may be storing more `many' records than can appear on a form at one time. There are two keyboard shortcuts for displaying the next or previous `page' of transaction-type records in this situation: CTRL+DOWN reads and displays the next page of records. CTRL+UP reads and displays the previous page of records. Note When a SBase 4 insertion point is active (when it is shown on screen) some of the keyboard equivalents have an editing function, and do not operate as record selection keys. Current Record *** See Fig 10-4 *** After some actions the screen does not show any record data. Click this button to redisplay the current record or to restart Table View with the current record at the top. With a form, the up and down parts of the button select the previous and next pages respectively if the form has more than one page. There are two keyboard equivalents for this button: the UP and DOWN keys. Next Record *** See Fig 10-4 *** Allows you to view the next record or set of related records on a form. The order in which the records appear is decided by the current index, which is shown by the message on the title bar of the main display window. In Table View the data for next record appears underneath the previous record. In Page View it replaces the previous record. The keyboard equivalent for this button is the RIGHT key. Previous Record *** See Fig 10-4 *** This gets the previous record or set of related records on a form, relative to the current record. The keyboard equivalent is the LEFT key. Fast Forward *** See Fig 10-4 *** Instead of moving forwards through the data one record at a time by pressing the Next button, it is quicker to use the Fast Forward button. In Table View, SBase 4 gets the next records in the file, displaying them row by row until the screen is full, when it selects the Pause button. The keyboard equivalent is SHIFT+DOWN. Pause *** See Fig 10-4 *** Pause is useful during a fast forward or a rewind when you want to stop to read the screen while searching for a record. This button may also be activated by pressing SPACEBAR. Rewind *** See Fig 10-4 *** This does the opposite of the Fast Forward button. You will see the data appear from the top of the screen, in reverse index order. If you do this immediately after Fast Forward there will be an apparent delay while SBase 4 displays records over the text of the existing display. For the keyboard equivalent, press SHIFT+UP. First *** See Fig 10-4 *** Pressing this button automatically displays the first record or set of related records on a form, according to current index order. The keyboard equivalent is SHIFT+LEFT. Last *** See Fig 10-4 *** Pressing this button automatically displays the last record or set of related records on a form, according to current index order. The keyboard equivalent is SHIFT+RIGHT. Stop *** See Fig 10-4 *** If you want to stop the Fast Forward or Rewind display, click Stop or press the key combination CTRL+C. You must also click Stop if you want to interrupt Pause when it's selected. To stop a process, use CTRL+C. Note You may turn off the browsing controls display from the System Options requester, invoked from the Set menu. All browsing, key lookup and filter controls are available from the keyboard. Key Lookup *** See Fig 10-4 *** Key lookup button Use the Key Lookup button for the retrieval of individual records. You enter a value for the field on which the file is currently indexed, and SBase 4 uses the index to find and display the record. You can only use one index field at a time - the Filter button is used for multiple value searching. With a form, a key lookup retrieves the record or set of related records using a value in the current index of the current file, as indicated in the window title bar. To select Key Lookup from the keyboard use the `?' key. When you click the Key Lookup button you'll see a requester asking for the entry of a key value. The maximum length of a key value entry is 100 characters. *** See Fig 10-5 *** Retrieving a Record by Key This procedure is best illustrated by an example. Suppose you wished to look up the record for Ms Julie Kasper in your Clients file using her reference number. First you would select the Customer_ref index by pressing the `-' key. Then you would click the Key Lookup button, type in the key - KASP00017 - and press ENTER. When you press ENTER or click OK, SBase 4 looks up the first record for which the Customer_ref field has the entered value. If there were more than one with this value, you would have to browse through the file with the Next Record or Fast Forward button to find the exact record you wanted. You are not restricted to one index. SBase 4 allows you to have up to 999 indexes for each file, all automatically updated when you add or modify records. Entering Partial Values You do not need to type the full index key in every case. For example, you could retrieve the record for Herr Huttermann by entering just the three characters `Hut'. All you need do is enter enough characters to identify the record you want. When the key you enter does not exactly match any record in the file, SBase 4 informs you that it selected the next record in index sequence. Filter *** See Fig 10-4 " system "ps sb2:Fig10-4"} *** Filter button This is a very powerful feature. You may have thousands of records in a database file, yet you may often want to review them (or print them out) on a selective basis. The Filter allows you to enter a set of values, as simple or as comprehensive as you like, to restrict the display to only the records that match those values. On a form, each file may have its own filter. Pressing the `=' key has the same effect as clicking the button. The Filter Requester Move the pointer and click the Filter button on the browsing controls. *** See Fig 10-6 *** The requester has three main parts: the Fields box, the operators and Value box, and the Text box. A Filter is created by a combination of clicking on field names and operators, and typing in values. These actions produce the Filter command line in the Text box. Let's look at each component of the Filter requester in turn. Fields Box This appears on the left-hand side of the requester. You can scroll the list of field names up and down in the usual way, using the scroll bar and arrows at the right of the Fields box. You specify that a field is to be used in the Filter by clicking on its name in this panel. The list of field names is complete. It is not affected by whether you have a selected fields list, so the fields that specify the Filter need not be the same as the ones that are shown in the Work Area. Operators These appear to the right of the Fields box, and can be divided into Relational operators, Logical operators and Mathematical operators. We'll see how these are used below. Value Box You use this box for typing in values such as names, numbers, and dates, which will form part of the Filter. When you are matching numeric data that has to be held in a text field because it may include non-numeric characters (such as telephone numbers), you must put the value in quotes. Remember to press ENTER or click the Value button after typing in the Value box. You must do this to ensure that the contents are copied to the Text box. Text box This is the long box that extends across the whole requester. When you make selections with the mouse or type values into the Value box, SBase 4 copies them into the Text box, building up the Filter command line. In this way you get immediate feedback about the results of your selections. The maximum length of the Filter command line is much greater than the apparent length of the box - up to 255 characters are allowed. You can type directly into the Text box if you prefer. Using the Operators The operators are used to relate field names to other fields or values. For example, if we wanted to set up a Filter to show only the addresses in West Germany, we would need the expression: country LIKE "West Germany". Here, country is a field name obtained by clicking in the Fields box; LIKE is selected by clicking on the LIKE operator button; and `West Germany' is typed into the Value box (note that it is not necessary to enter quotation marks if you use the Value box). Text String and Pattern Matching: LIKE <$SPattern matching;LIKE>When you're searching for names or text strings, you should avoid using the equals sign operator, and use LIKE instead. This is because only LIKE accepts matches on either upper or lower case characters; it's said to be `case insensitive.' The equals sign operator, on the other hand, requires exact matches for what you type in: it is `case sensitive.' LIKE also permits searches for text strings using pattern matching characters. These allow you to define text strings with fixed patterns of characters whose content may vary, or text strings of indefinite length as well as content. In addition, you can also use square brackets to define specific ranges of characters to be accepted by the Filter. LIKE Syntax LIKE "*" Searches for empty fields with `*' on its own LIKE "?*" Searches for fields that are not empty LIKE "text" Field with content `text' The asterisk represents a text string of any length: LIKE "text*" `Text' string at start of field LIKE "*text" `Text' string at end of field LIKE "*text*" `Text' string anywhere in field The question mark represents a single character: LIKE "???" Fixed length, any content LIKE "?text?" Fixed length, fixed content LIKE "?text*" Fixed leading length/content, any length A range inside square brackets may be used in place of a question mark character: LIKE "[a-m]" Single character range `a' through `m' LIKE "[adg-j]*" Character `a', `d', or `g' through `j' The caret or up arrow at the start of a bracketed sequence signifies exclusion: LIKE "[^n-w]" Excludes characters `n' through `w' You can combine different elements of the pattern matching syntax: LIKE "a??[c-h]?x*" This matches `a' in the first position, any character in the second and third positions, the range from `c' to `h' in the fourth position, any character in the fifth position, `x' in the sixth position, and any number of characters following the `x'. LIKE is the operator you should use when searching for text strings in fields. LIKE "|text" Soundex searching Soundex searching finds text strings that `sound like' the given string. Pattern matching characters may not be used. LIKE Examples Each of the examples uses the demonstration Clients file. <$M[mark1]> Example 1 Suppose you want to find the records for the clients whose firstname is Robert. You would have to enter the following in the filter command line box: Firstname LIKE "robert" Notice that we don't trouble to enter an upper case `R', as the LIKE operator is case insensitive. You can try this out now if you wish. Once you have entered this filter command line, click OK. SBase 4 will then display all the records where the Firstname field is LIKE `Robert'. Example 2 Supposing that you don't know the full first name you're looking for, but you know that it starts with an `s'. Again you can use the LIKE operator to help you. You would need to create the following filter command line: Firstname LIKE "s*" The `*' is important, because you are telling SBase 4 to find any record that starts with an `s' and can be followed by any number of characters. Example 3 What if you don't know which letter the first name starts with, but you know that it contains an `r' somewhere in the spelling? To find the record this time you would have to use the following filter: Firstname LIKE "*r*" Example 4 Although this is a slight digression, it's useful to be able to NEGATE a LIKE expression. If you want to get all those first names which don't contain an `r', you can change the previous filter command line to read: NOT (Firstname LIKE "*r*") Example 5 Taking our example a step further, supposing you want those records where the fourth character of Firstname is an `r'. In order to do this you would use the following filter command line: Firstname LIKE "???r*" Example 6 Suppose you don't know any of the letters in Firstname, but you know it contains five characters. You could find the record with the following filter command line: Firstname LIKE "?????" If you want to find those records where Firstname is at least five characters long, you can type an `*' after the five question marks. Example 7 LIKE also allows you to search for ranges of characters. Suppose you want to list just those records where Firstname begins with `a', `b', or any letter between `j' and `m'. The filter command line should read: Firstname LIKE "[abj-m]*" Notice that whatever is inside the square brackets represents a single character. If you want to specify ranges for more than one character in a string, you must use a set of square brackets for each one. The Relational Operators = This specifies that the field is to be EXACTLY EQUAL TO the following value or field: Balance = 2000 The field is to be NOT EQUAL TO the following value or field: country "France" > The field is to be GREATER THAN the following value or field: Balance > 1000 < The field is to be LESS THAN the following value or field: Balance < 1000 >= The field is to be GREATER THAN OR EQUAL TO the following value or field: Balance >= 1000 <= The field is to be LESS THAN OR EQUAL TO the following value or field: Balance <= 1000 The Logical Operators AND, OR and NOT These provide links between different expressions like the ones given above. You use them to specify alternatives and ranges for the Filter, either for a single field or for a number of interrelated fields: AND Both of the conditions linked by AND must be satisfied for the record to be accepted by the Filter: country LIKE "West Germany" AND Balance >= 5000 OR Either of the conditions linked by OR is sufficient to make the record acceptable to the Filter: country LIKE "West Germany" OR city LIKE "Geneva" NOT NOT is slightly different. It negates the effect of an expression: NOT (country LIKE "West Germany" AND Balance > 5000); this will find all the records other than the ones where the country is West Germany and the balance is greater than 5000. Note the use made of parentheses with NOT. Parentheses The `(' and `)' operators are used in the normal way, to give priority to one part of the expression over another. ( The following expression has a higher priority for evaluation than the preceding expression. ) Required to end a higher priority expression. The numbers of opening and closing parentheses must be equal. The Mathematical Operators + This is used to express addition: Balance > Amount + 1000 - The minus sign expresses subtraction: Balance > Amount - 1000 * The asterisk is used for multiplication: Balance > Amount * 1.15 / The slash represents division: Balance < Limit / 10 Typing in the Text Box The great power and flexibility of the Filter system is only achieved by giving the user control of every aspect of the operation. Because you can type into the Text box you can include all kinds of expressions which are not available from the buttons on the requester itself, and all kinds of text and numeric values. The price you pay for this flexibility is an increased risk of making mistakes. This is not too serious, as you cannot harm your data by using a Filter for browsing (although you can delete records using the Process Remove filter). However, you may see a SBase 4 error message concerning an error in the Filter command line in two possible situations: - When you click OK after creating or editing a Filter command line. - When you use a Filter after changing the definition of a field with Project Modify File. If you see an error message you must either change the Filter command line or change the file definition. Here's a summary of the editing facilities available when typing inside the Text box: Filter Command Line Editing You can remove everything from the Main Box by clicking on the Clear button or, if you've already clicked in the box, by pressing AMIGA+X. - The DEL key removes the character to the right of the cursor. Holding the key down repeats its action. - The Backspace key removes the character to the left of the cursor. Holding the key down repeats its action. - The keys SHIFT_LEFT and SHIFT+RIGHT move the cursor to the beginning and end of the filter command line respectively. - Provided the cursor is still present, AMIGA+Q will undo any changes you have made. Using a Filter The Filter command line is made active by clicking on OK. Whenever a Filter is in use, SBase 4 highlights the Filter button. You can perform all but one of the browsing operations we've discussed, and SBase 4 will restrict the records displayed to the ones that match the Filter values. For example, if you have specified country LIKE "West Germany" and you click the First Record button, SBase 4 will find the first record in the file, according to the current index, which has the value `West Germany' in the country field. The Key Lookup button is an exception to the Filter effect. You can still recall individual records by entering an index key value, whether or not they match the Filter. Suspending a Filter There will be times when you want to remove the Filter effect so that you can review all the records in the file again. To do this, just click the highlighted Filter button to turn off the highlight. Modifying or Re-activating a Filter If you've set up a Filter and then suspended it, you can bring it back into operation by clicking once more on the Filter button. SBase 4 displays the Filter requester, with the Filter command line in the Text box just as you left it. If you want to, you can add to the Filter command line by clicking on more field names and operators, or edit it after clicking in the Text box, as described above. Whether you've made any changes or not, just click OK to use the Filter again. Optimizing the Filter By using a Filter, you can search for data in any field whether it is indexed or not. However, if it can make use of an index, SBase will retrieve records far more quickly. SBase 4 uses an index to optimize Filter operations when the following conditions are satisfied: 1. The index field occurs at the beginning of the Filter command line. 2. The index for that field is the current index (use the `-' key on the numeric keypad to change the current index). 3. The first operator to occur in the command line is a relational operator; i.e., it must be one of the following: =, <=, >=, <>. The Filter operation will not be optimized when LIKE is used as an operator. Filters and Forms Each file on a multi-file form may have its own filter command line set. When you browse through the data, SBase 4 only retrieves records that match all the filters. Although you cannot store filters as re-usable disk files in themselves, you may assign a filter to a function key and store the function key set on disk. See Chapter 29 Function Keys for further information.