@database @node main Chapter45 "SBase Personal - 45. LEARNING TO USE SBase 4" @{" 45 LEARNING TO USE SBase 4 " link 0} @{" Lesson 1: Defining a File " link 1} @{" New File Name Requester " link 1.1} @{" Reference " link 1.2} @{" Name " link 1.3} @{" Number, Type, Bank_name, Bank_code, Phone, FAX, SS Number, Street, City,ST, Zip_code " link 1.4} @{" Date_opened " link 1.5} @{" Balance " link 1.6} @{" Interest_rate " link 1.7} @{" Interest " link 1.8} @{" New Indexes " link 1.9} @{" Lesson 2: Entering Data " link 2} @{" Opening a File " link 2.1} @{" Adding New Records " link 2.2} @{" Lesson 3: Editing a Record " link 3} @{" Duplicating a Record " link 3.1} @{" Lesson 4: Using the Browsing Controls " link 4} @{" Select First Record " link 4.1} @{" Select Next Record " link 4.2} @{" Select Previous Record " link 4.3} @{" Select Last Record " link 4.4} @{" Rewind: Scanning in Descending Order " link 4.5} @{" Fast Forward: Scanning in Ascending Order " link 4.6} @{" Retrieving Individual Records: Key Lookup " link 4.7} @{" Pause and Stop " link 4.8} @{" Current Record " link 4.9} @{" Lesson 5: Working with Filters " link 5} @{" How Filters Work " link 5.1} @{" Defining a Filter " link 5.2} @{" Extending a Filter " link 5.3} @{" Overriding the Filter with Key Lookup " link 5.4} @{" Lesson 6: Importing Data " link 6} @{" Lesson 7: Selection and Output " link 7} @{" Selecting Fields for Output " link 7.1} @{" Showing Selected Fields in Table View " link 7.2} @{" Printing Selected Fields " link 7.3} @{" Lesson 8: Printing Labels " link 8} @{" Defining the Label Layout " link 8.1} @{" Defining the Label Dimensions " link 8.2} @{" Defining Print Parameters " link 8.3} @{" Alignment Test " link 8.4} @{" Printing " link 8.5} @{" Saving the Labels Format " link 8.6} @{" Lesson 9: Mail Merge " link 9} @{" Typing the Merge Document " link 9.1} @{" Inserting Merge Fields " link 9.2} @{" Running Mail Merge " link 9.3} @{" Lesson 10: Globally Updating a File " link 10} @{" Lesson 11: Querying the Database " link 11} @{" Selecting Query " link 11.1} @{" Defining a Title " link 11.2} @{" Selecting Fields for Output " link 11.3} @{" Adding Reporting Functions " link 11.4} @{" Defining a Filter " link 11.5} @{" Defining a Sorting Order " link 11.6} @{" Viewing Query Output " link 11.7} @{" Saving the Query " link 11.8} @{" Lesson 12: Creating Text Type Forms " link 12} @{" To enter text: " link 12.1} @{" Now we will but a block of color behind the text. " link 12.2} @{" Now we will draw four more graphic objects on the form. " link 12.3} @{" The next graphic element we will add is lines. " link 12.4} @{" Next, more text will be placed on the form. " link 12.5} @{" Incorporating Database Fields " link 12.6} @{" Lesson 13 Using External Files in a Form " link 13} @{" Define the External File Field in the Database " link 13.1} @{" Modify the Form " link 13.2} @{" Lesson 14 Viewing dBase files with SBase " link 14} @endnode @node 0 45 LEARNING TO USE SBase 4 This chapter contains 14 lessons designed to give you practice in using SBase. The lessons cover those database management activities that are performed most frequently, but do not explore the more advanced area of working with multiple files. This is discussed in Chapter 17 Multi-file Applications. The lessons begin with setting up a new file and entering some data into it. Next we cover the browsing controls and the use of the Filter to select records. After showing you how to import a file of example data we explain how to select fields and switch indexes, then print out a list. We then show you how to print mailing labels and do a mail merge and an update. A guided tour of SBase 4's query feature concludes the chapter. Prerequisites. Before attempting the tutorials you should familiarize yourself with certain aspects of the system. The first chapter in the manual, Getting to Know SBase 4, explains some important features of both SBase and Workbench, so please read it before proceeding. You may also benefit from exploring the example application included with the package, and we advise you to copy the example data into the same directory as you use when going through the tutorial. You should copy the example file Accounts.csv into your tutorial directory before starting Lesson 6. It can be found in the IDEAS directory of the Example disk. @endnode @node 1 Lesson 1: Defining a File In this example you are going to set up a file to hold details of bank accounts. The file includes examples of several types of field. Briefly, there are five stages to the tutorial: 1. Select New File from the Project menu and type in the file name. 2. Type in a password. 3. Enter the field names and details. 4. Specify index fields. 5. Save the file definition. 6. Print out the file definition. Start by selecting New File from the Project menu. @endnode @node 1.1 New File Name Requester If you are running SBase under the AmigaDOS 2.0, the file requester will include drive buttons and string gadgets for writing in the file path. This file requester will appear as shown below. *** @{" See Fig 45-1 " system "ps sb2:fig45-1"} *** For those running AmigaDOS 1.3, the file requester will appear as follows. *** @{" See Fig 45-2 " system "ps sb2:fig45-2"} *** You now have to type in a file name. For this tutorial we are going to create a file called Accounts. Type in `Accounts' and click on OK. SBase 4 now displays the Password requester. Type in a single password, so that you will have all levels of privilege. Keep it simple and easy to remember as you'll need to enter the password each time you open the file. Note that passwords are case sensitive. Once you've typed in your password click OK. SBase 4 displays the File Definition requester: *** @{" See Fig 45-3 " system "ps sb2:fig45-3"} *** The File Definition requester shows all the controls you will use to complete the process of defining the fields for the file. For each field, you just type in the name, set the desired characteristics, and add it to the definition so that it appears in the large panel at the top of the requester. When you've finished adding fields, you specify which ones are to be indexed. Then you can start adding data to the file. The fields for the Accounts file are: - Reference - Name - Number - Type - Bank_name - Bank_code - Date_opened - Balance - Interest Rate - Interest @endnode @node 1.2 Reference This is going to be the reference number for each record and will be a required field. We'll set up this field as a text field because it will contain both alphabetic and numeric characters. The procedure for defining a field is quite simple. First press the Clear button, then type `Reference' into the field gadget box. If the insertion point is not already showing, click in the Field box to make it appear., Next, you have to define this field as a text field, so click the Text radio button. SBase 4 then displays the Text format requester. *** @{" See Fig 45-4 " system "ps sb2:fig45-4"} *** This requester enables you to set the length of each Text field and various other attributes such as the number of responses the field will accept. At the top, you will see the field length box. The default value is 20 characters, which is the value you can see on your screen. The Reference field only needs a length of 5 characters, so click in the editing box at the top of the requester, erase the existing figure using first the RIGHT key then the Backspace key, then type in `5' and complete the entry by pressing ENTER. To ensure that any letters typed into the field are always converted to uppercase, click the Upper case radio button. We can ignore the other Text format options, so you can now click the OK button to clear the screen and return you to the File Definition requester. Now we have to make the field a Required field, so that it can't be left blank by mistake. So click the Required check box. SBase 4 places a tick in the box to show that this attribute has been selected. This field is added to the file definition by clicking on the Add button at the bottom left of the requester. Do this now and watch the screen. SBase 4 displays the field name in the File Definition Panel along with its attributes and field length. Check that these details are correct before moving on to the next field. @endnode @node 1.3 Name The next field to enter is Name, which will also be a text field, but not a required field. This field will hold the name of the account. Click on the Clear button, and click in the field box if the insertion point is not already there, then type in Name. Click the Text radio button to call up the Text format requester. You'll see that SBase 4 again displays the field length of 5 characters, which is the length we used for the previous field, but we want a field length of 20 characters. Use the RIGHT and Backspace keys to delete the `5', type `20' and press RETURN. We do not need the upper case option for this field, so ensure that the Standard radio button is selected. When you've done this, click OK to exit back to the File Definition requester. Now you can add this new field by clicking on the Add button. If you then look at the File Definition Panel you will see that the field you have entered is shown on the second line. @endnode @node 1.4 Number, Type, Bank_name, Bank_code, Phone, FAX, SS Number, Street, City, ST, Zip_code You can create these eleven text fields just as easily as the last two. For each field, follow this routine: 1. Enter the field name, 2. Click the Text radio button. 3. Enter the Field length: Number 10 Type 1 Bank_name 20 Bank_code 10 Phone 12 FAX 12 SS_Number 11 Street 20 City 10 ST 2 Zip_Code 5 4. If the Standard radio button is not selected, select it. 5. Click OK. 6. Click Add. @endnode @node 1.5 Date_opened This field is different from those you've created so far: it's a Date type field. Start off the same way, by entering its name into the Field box. Now you must define the format in which the dates typed into the field are to be displayed. Click the Date / Time radio button. SBase 4 displays the following requester: *** @{" See Fig 45-5 " system "ps sb2:fig45-5"} *** If you look at the requester you will see the current system date is displayed in the default format. This will vary depending on the format set in your S:SBase4.INI file. The display of the system date is used here as a guide to the way the field will display dates. You can change both the day/month/year order and the format of the date. Every selection is reflected at once in the date display, so you can experiment freely until you are satisfied with the results. Select the order `Month Day Year' (this is necessary even if your national conventions are different for the rest of the tutorial to work properly). Then choose the style of month and year display you prefer. Choose a separator from the next group of options. When you've finished making selections, exit from the Date format requester by clicking OK. Finally, add the field to the file definition by clicking on the Add button. @endnode @node 1.6 Balance This field will contain the balance for each record within the Accounts file and therefore we must define it as Numeric. Once again the field name is entered in the same way as before, by clicking on the Clear button and typing the word Balance into the Field box. However, this time you must click the Numeric radio button. SBase 4 displays another requester which looks like this: *** @{" See Fig 45-6 " system "ps sb2:fig45-6"} *** As with the Date format requester, SBase 4, will show the default format. We will change this to include a currency symbol and a separator for thousands. Move the pointer and click the $9 radio button, then click the (,) check box. Once you've done that click the OK button, and when you're back at the File Definition requester click the Add button to add this field to the file definition. @endnode @node 1.7 Interest_rate This field will also be set up as a Numeric field, and we will introduce a validation check to limit the range of values that can be entered. We'll define this range as being greater than zero and less than 15. Click on the Clear button and type in the field name in the usual way, then click the Numeric radio button. Notice that SBase 4 has carried over the format and the features from the previous numeric field. This can be very useful when you have to create many fields with the same format one after another. For Interest Rate, we will need to change the Number format. First, the number is not going to need provision for a sign, as it's never going to be less than zero. So, click the right-hand sign button - the one that does not show a sign symbol. This field uses a percent format, so click the 99% symbol button. Then deselect the (,) check box by clicking on it. Next you need to shorten the number format. Click the lower of the two arrow buttons on the left- hand side of the number format panel. As you do this you will notice that the number format changes from 99999.00% to 9999.00%. Click the button until the number is reduced to 99.00%. Once you've done that click OK. Now click the Add button. SBase 4 adds the Interest_rate field to the File Definition Panel, and clears the Field box. Next you're going to add a range check to this field. First you must make the Interest_rate field the selected field. Click its name in the File Definition Panel to reselect it and copy its name into the Field box. Now click the Validated check box: SBase 4 displays the Validation Formula requester. *** @{" See Fig 45-7 " system "ps sb2:fig45-7"} *** This requester enables you to create a validation formula that will be used by SBase 4 each time a value is typed into the Interest_rate field. The formula will ensure that every interest rate is within the permitted range. The formula you want is: Interest_rate.Accounts >= 0 AND Interest_rate.Accounts <= 15 Do this in stages. Move the pointer into the Field list box and click Interest_rate. Next, click the >= button. Now you have to enter 0 in the Value box. The insertion point is automatically positioned in the Value box, so type in `0' (omit quotation marks) and press ENTER to copy the entered value into the large text box. Your Validation Formula should read like this: Interest_rate.Accounts >= 0 To make a link to the next part of the Formula, you click the AND button. Now repeat what you did before: click Interest_rate, then (slightly different) on the <= button, and then in the Value box, where you type `15' and press ENTER. Your formula should now look like this: Interest_rate.Accounts >= 0 AND Interest_rate.Accounts <= 15 If you've made any errors, you have a choice of how to correct them. Either click directly in the text box and edit the formula directly, or click Clear and repeat the steps above. Once the formula is correct, click the OK button, and SBase 4 will return to the File Definition requester. Notice that Validation is now shown as selected. SBase 4 updates the File Definition Panel to show VAL under the Attributes column, but it does not show the Validation formula; you can use Utilities Status File to look at it later. @endnode @node 1.8 Interest This is the last field we need to enter for this file, and it's going to be a calculated field. This means that SBase 4 will calculate a result for the field based on the values of the fields referred to in the formula, in this case Balance and Interest_rate. So, click Clear to remove Interest_rate from the Field Box, and then click in the box and type in the field name Interest. Click the Numeric radio button. The format needs to be reset from the format used for Interest_rate. Click the $9 and (,) buttons to set the currency sign and thousands separator, and the - button to provide for a minus sign. Then click the upper of the two arrow buttons to the left of the number format panel three times to expand the format, which should look like this: -$,99999.00. When it's right, click OK to exit back to the File Definition requester. Click Add to add the field to the file definition, then click on the field name Interest in the File Definition Panel to reselect it. Next, click Calculation. SBase 4 displays the Calculation Formula requester, which works exactly like the Validation Formula requester you have just used. What we want SBase 4 to do is to calculate how much interest is due for each balance based on the current Interest_rate, and then display this value in the Interest field. So, the formula to calculate the amount of interest is: Balance.Accounts * Interest_rate.Accounts / 100 You should be able to do this without detailed guidance. Click Clear to empty the Text box. Now click the field name Balance, the * button, the field name Interest_rate, and the / button. Then click in the Value box and type in the value `100'. Press ENTER. The Formula should read as above. Once it's correct you can click the OK button. That completes the entry of the fields, so now you can click OK in the File Definition requester. @endnode @node 1.9 New Indexes SBase 4 now clears the requester from the screen and displays the New Index requester. *** @{" See Fig 45-8 " system "ps sb2:fig45-8"} *** This is the last part of the process of setting up a file, which is to specify the field names you want to use as indexes. If you look at the screen you will see a list of the field names you have just entered. What we have to do now is to decide which fields to use as indexes. For the Accounts file we want indexes on Reference, Bank_name, Date_opened, and Balance. You can only specify one index at a time. So, move the pointer and click Reference. As you do this SBase 4 displays this field name in the box at the bottom of the list. Also notice that SBase 4 has highlighted the Normal radio button, indicating that duplicate index entries may be allowed. Since the purpose of the Reference field is to provide a unique identifier for each record in the Accounts file, the index should not allow duplicate entries. Click the Unique radio button to indicate this. Now click the OK button. SBase 4 redisplays the list of fields without the reference field. You can go on and specify as many more fields as you want as indexes. Repeat the exercise for Bank_name, Date_opened, and Balance, this time creating a Normal index for each one. Notice that each time SBase 4 redisplays the New Index requester, the field name you used to specify the previous index has been removed. When you have finished selecting fields for indexing just click the OK button. This informs SBase 4 you have finished making your selections and it can proceed to save the File Definition and create the Index Files. Once the indexes have been built SBase 4 displays the file in the format of the current view. The file will be indexed on the first index you specified, in this case Reference, as shown by the message on the title bar at the top of the window. You can toggle to the other indexes by pressing the `-' key. SBase 4 also displays a blank record showing the field names you set up in the File Definition. @endnode @node 2 Lesson 2: Entering Data In this example you will use the Accounts file you created earlier to create, edit, duplicate and remove records. Select Record View from the Set menu for this exercise. @endnode @node 2.1 Opening a File If the Accounts file that you created in the last lesson is not already open, you need to open it. Select the Open File command from the Project menu. Click Accounts in the left-hand list box, then click OK. When you have opened the file, you will see the field names down the left-hand side of the work-area, with no data on the right-hand side. This is because the file is empty. @endnode @node 2.2 Adding New Records Press the right mouse button and select the Record menu. This is the menu you use when you are creating, duplicating or saving records. The command to use when you are entering a record for the first time is New, which is the first command on the menu. Select New now. If you look at the screen, you will see that SBase 4 is displaying the cursor in the first field of the record. The end of the field is shown by a vertical bar. You are now going to enter your first record into a SBase 4 file. Here is an example record: Reference: R001 Name: John B. Sloop Number: 0405332 Type: D Bank_name: National City Bank_code: 401362 Date_opened: Jul 6, 1986 Balance: $2400 Interest Rate: 5.67 Start by entering the Reference, R001. When you've typed the example data, press ENTER. The cursor moves to the next field. You can then continue and type in the rest of the data. Remember to make sure you are entering the data against the correct field. Don't worry about making mistakes, as these can be corrected as you go along by using the DEL or BACKSPACE keys. When you press ENTER after typing the Interest_rate, SBase 4 calculates and displays the Interest automatically. Because there are no more fields requiring data, SBase 4 now displays a requester asking whether you wish to save the record. You may click on OK, but most people just press ENTER at this point because their hands are still on the keyboard. Do this now. After saving the record you have just entered, SBase 4 presents a requester giving you a choice between entering another new record or ending data entry. Type in another two records: Reference: R002 Name: Sarah Armstrong Number: 6902237 Type: D Bank_name: People's Co-Op Bank_code: 336512 Date_opened: May 12, 1984 Balance: $5000 Interest Rate: 6.75 Reference: R003 Name: Mary Garcia Number: 9784421 Type: C Bank_name: National City Bank_code: 401362 Date_opened: Feb 21, 1989 Balance: $749 Interest Rate: 5.67 You can enter these records in exactly the same way as you did before, but remember to look at each field as you type in the data, to make sure it's the right one. Remember also to save each record before moving on to the next. When you've entered all three records, end data entry. @endnode @node 3 Lesson 3: Editing a Record Suppose you want to change some of the details in John B. Sloop's record. How do you do it? First of all you have to select his record. Press the left direction key, to display the previous record in the file, as many times as necessary to display the required record. Select Current from the Edit menu. SBase 4 positions the insertion point in the first field of the record. Look at the record, and find the Date_opened field. You are going to change this from `Jul 6, 1986' to `Jul 7, 1986'. Press ENTER six times to move the insertion point down to the Date_opened field. Use the right direction key to move along until the insertion point is immediately to the right of `6' and press Backspace to delete it. Then type `7' and press ENTER. That's it, you've now edited the record. Save it by selecting Save from the Record menu. The example you've just worked through is an easy standard procedure for editing a record. It is often easier to edit a record simply by clicking in the field to be changed, making the necessary changes, then saving the record with the AMIGA+S shortcut. Records may be edited in Record and Page Views, but not in Table View. There are many more editing features available to you than are described here. See Chapter 6, Entering Data, for more details. @endnode @node 3.1 Duplicating a Record If you want to create a new record that has very similar data to another, you can do so using a convenient shortcut, the Duplicate command. What you're going to do now is duplicate Sarah Armstrong's record and create one for her sister. So select Sarah's record by pressing the right direction key. Now move the pointer to select the Duplicate command from the Record menu. SBase 4 displays a message to say that a copy of the record has been made in memory. You can now edit it. You need to change the information in five of the fields, Reference, Name, Number, Date_opened, and Balance. Reference: R004 Name: Janice Armstrong Number: 6902551 Date_opened: Mar 15, 1984 Balance: $3200 If you look at the screen you can see the cursor is already at the Reference field. Use the right direction key to move to the right of the data, press BACKSPACE to delete the `2' and type `4'. When you've done that press ENTER to move the cursor to the next field, Name. At the beginning of the field, press the DEL key five times, then type `Janice'. Now change the Number, Date_opened, and Balance fields on your own. That completes the editing for this record so you can now save it in the usual way. @endnode @node 4 Lesson 4: Using the Browsing Controls The browsing controls at the bottom of the SBase 4 window give you immediate access to your data. These controls, modeled on those of a VCR or tape recorder, are designed to be used with the mouse, but some have keyboard alternatives. We mention each alternative the first time the browsing control is described, but subsequently assume that the mouse is being used. @endnode @node 4.1 Select First Record When you want to display the first record in the file click the First Record button, third from the left. Do this now, and you will see record R001 appear. The current index determines the order in which records appear. Select the Project Open Index command, and click the Date_opened fieldname in the left-hand list. Click OK. SBase 4 shows in the title bar that the Accounts file is now indexed on Date_opened. However, the current record is the same. Click the First Record button again, and record R004 will appear. This is the record with the earliest date in the Date_opened field. @endnode @node 4.2 Select Next Record This very frequent browsing action is performed by clicking the right arrow button, seventh from the left, or pressing the RIGHT direction key. Each time you click Next Record, SBase 4 displays the next record in the order of the current index, shown in the window title bar. Do this now, and record R002 appears. Repeat the action twice to show records R001 and R003 in that order. Click Next Record again. The `End of File' message appears temporarily, informing you that there are no more records to be displayed. @endnode @node 4.3 Select Previous Record The opposite to the last command, Previous Record displays the record before the current record in the order of the current index. Click Previous Record, fifth from the left, or press the LEFT direction key. SBase displays record R001. Repeat the action three times to show records R002 and R004, followed by the `End of File' message. @endnode @node 4.4 Select Last Record You can choose the last record in the file by clicking the Last Record button, ninth from the left. Do this now, and SBase 4 displays record R003. @endnode @node 4.5 Rewind: Scanning in Descending Order The VCR model used for the browsing controls includes an equivalent to the Rewind function. By clicking on the Rewind button, fourth from the left, you instruct SBase 4 to display the records one after the other, starting from the current record and moving in the reverse order of the current index. With record R003 on the screen, click Rewind. SBase 4 displays records R001, R002, and R004 followed by the `End of File' message. @endnode @node 4.6 Fast Forward: Scanning in Ascending Order The opposite of Rewind is Fast Forward. This command allows you to scan through the file in the order of the current index. Click the Fast Forward button, eighth from the left. SBase 4 displays the remaining records and then the `End of File' message. @endnode @node 4.7 Retrieving Individual Records: Key Lookup We use the term `key lookup' to describe the action of retrieving a specific record from a database file by means of an index key. Key lookup works by asking for a value for an indexed field, which SBase 4 then uses to read the record from the disk. The process resembles using a book index to find the page number for a particular reference, then turning to the correct page. Key lookup is a very fast way to retrieve individual records, but of course you can only enter a single field value (unless you have set up indexes based on multiple fields - see Chapter 4 Creating Indexes). Press the `-' key to cycle through the available indexes until the title bar shows that the Accounts file is indexed on Reference. The key lookup function always uses the current index. Click the Key Lookup button, third from the right, or press the `?' question mark key. SBase displays a requester asking you to enter the Key Value. Type `R002' (omit quotes), and press ENTER. SBase 4 displays record R002. Repeat the action, this time typing `R004'. Again, the matching record is displayed. Now, repeat the Key Lookup button, but just type in `R' and press ENTER. SBase does two things: - Informs you that there is no key with the exact value `R'; - Displays the record closest in sequence after `R' which in this case is record R001. This very useful feature of partial key entry allows you to move very rapidly to any section of a file, and then use the other browsing controls to look at nearby records. Now press the `-' key again to change to the Date_opened index. Click the Key Lookup button, type `2/21/89' and press ENTER. SBase 4 displays record R003. (Note that you can type in a date in any format provided the day/month/year order corresponds to that of the default date, which you can view with the Set Date Format command.) @endnode @node 4.8 Pause and Stop The two leftmost buttons in the browsing controls allow you to interrupt the display temporarily or permanently. Their respective keyboard equivalents are SPACEBAR and CTRL+C. These controls are mainly used with Fast Forward and Rewind, and are useful when you are scanning through large files. - Note that you can also vary the speed of the display from the Set System Options requester. @endnode @node 4.9 Current Record The central Current Record button is mainly used when the screen is temporarily displaying non-record data. Examples are the output from the Utilities Status File or Utilities Type commands. Current Record re-reads and displays the current record. For keyboard equivalents you may use either the UP or the DOWN direction key. @endnode @node 5 Lesson 5: Working with Filters The SBase 4 filter system allows you to choose subsets of records from your file. Filters are used throughout SBase 4, so it is important that you explore and master filter techniques. Before continuing, press the `-' minus key to switch to the Reference index, which we shall be using later in the lesson. @endnode @node 5.1 How Filters Work As a first step, click the Filter browsing control button, second from the right, or press the = equals sign key. SBase 4 displays a requester showing the fields from the current file in a list box, together with a set of operator buttons. There is a text box extending across the requester, in which your selections will be combined to create a filter command line. When you click OK, SBase 4 displays the first record in the file that matches the criteria you have defined in the filter command line. You may then use the other browsing controls to review the other records that match the criteria. Most of the Process menu commands use the filter command line that is set up in the browsing filter as the default for their specific activities. This makes it easy, for example, to preview a set of records to be discarded using the browsing filter, then actually remove them with the same filter line as part of the Process Remove command. Filter command lines are constructed from expressions that combine fieldnames, operators, values, and functions. An example of a simple expression would be: Balance.Accounts > $500 More complex filters can combine several expressions with the AND, OR, and NOT keywords, andin SBase Professional 4 may draw on the full range of DML programming language functions. @endnode @node 5.2 Defining a Filter We are going to search for those account records where the record balance is between $2000 and $4000. So we'll use the Balance field, the `>' and `<' operators, and the Value box that appears below the operators. You'll see the results of your selections appear as a filter command line in the text box. Move the pointer into the list of fieldnames and click the Balance field. The fieldname and its file extension are copied into the text box: Balance.Accounts. Next, move the pointer to the right and click the `>' button. This also appears in the text box. Now you must type in `2000'. This goes in the Value box. SBase 4 has already activated the Value box by placing an insertion point in it, and while this is present you can press ENTER without exiting from the Filter requester. Type in `2000' (no quotation marks). Press ENTER - this is essential, or the contents of the Value box will not be copied into the text box. Your filter command line should read: Balance.Accounts > 2000 If it does not, click Clear and start again. Next, you need the AND operator. Click the AND button and SBase 4 copies it into the Text Box. Now you must click the Balance fieldname again. This is important. Every expression must have `two sides,' so that SBase 4 can resolve it into either true or false, so a filter command line like this is not allowed: Balance.Accounts > 2000 AND < 4000 So, click Balance, as before, then follow it with `<', then type `4000' into the Value box. Press ENTER. Your filter command line should look like the one shown in the diagram below. *** @{" See Fig 45-9 " system "ps sb2:fig45-9"} *** If there are any mistakes click Clear and repeat the steps above until you get it right. You have built your filter command line, so now you can put it into action. Click the OK button. SBase 4 activates your filter and displays the first record in the file that matches its criteria, which happens to be record R001. Click Fast Forward. SBase 4 shows the other record that matches the filter, R004. Now de-activate the filter. Click the Filter button so that it's not highlighted. Click First Record followed by Fast Forward. You'll see all the records in the file appear as normal. @endnode @node 5.3 Extending a Filter Let's go back and add another condition. Click the Filter button to recall the requester. The filter command line is retained for you to modify. Click the AND operator, then on Date_opened, then on `>'. Type `1/1/86' and press ENTER. Your line should read: Balance.Accounts > $2000 AND Balance.Accounts < $4000 AND Date_opened > "1/1/86" Click OK. This time SBase 4 finds only one record that matches all three criteria, record R001. If you try to select the next record you will see the `End of File' message, which tells you that there are no more records that match the filter. @endnode @node 5.4 Overriding the Filter with Key Lookup The only browsing control that does not use the filter is the Key Lookup button. See this for yourself now. Select Key Lookup, then type in `R003' and press ENTER. SBase retrieves this record even though it does not match the filter criteria. However, the filter remains active and the other browsing controls will find only matching records. Before going on to the next lesson, turn off the filter by clicking the Filter button. @endnode @node 6 Lesson 6: Importing Data The ability to import data into SBase 4 from another system allows users both to switch to the SBase 4 system without re-entering data, and to work effectively with other applications software. In this lesson we see how you set about importing a straightforward text file, which will allow us to expand the example Accounts file and make the following lessons more useful. Before you continue, ensure that the supplied example file Accounts.csv is in the same directory as the Accounts file you created in the first lesson. If you have been using the Set Field Selection command, ensure that it is deselected (there should be no check mark against it when you display the Set menu). Now, select the Process Import menu command. SBase 4 shows the Import File Type requester. Of the many possible selections, we want the default, ASCII Delimited Merge. The radio button for this is already highlighted, so you can immediately click OK. SBase 4 shows a Filter requester like the one you used in the previous lesson. If you have carried straight on from the previous lesson, the last filter command line will be shown. Since you want to import all the records from the Accounts.csv file, you must remove it. Do this by pressing DEL if the line is highlighted, or clicking Clear if it is not. When the text box is empty, click OK. Note Throughout SBase, Personal 4 whenever you are presented with a filter requester and you want to process all the records, click OK without specifying a filter command line. SBase 4 shows a file selection requester. Change the pattern in the text box to show *.csv, and press ENTER. The list of files should show Accounts.csv. Click this filename, then click OK. It should take SBase 4 only a few seconds to read the data from Accounts.csv and create new records in the Accounts file. All the Accounts file indexes will be automatically updated. Use the browsing controls to display records from the expanded file. The Utilities Status File command shows how many records are in the new file. @endnode @node 7 Lesson 7: Selection and Output SBase 4 allows the user a very wide choice of selection options in addition to the filter method of record selection that was covered in an earlier lesson. You can select and arrange fields to suit your requirements, and then output them in any order. In this lesson we shall see how you choose a group of fields for output, and how this feature can be combined with index selection to create an instant report. This lesson includes a printing exercise. If you do not have a printer installed for Workbench, or your printer does not work properly, you should try to resolve the difficulties before starting the lesson, as we do not want you to become distracted during it. @endnode @node 7.1 Selecting Fields for Output Choose the Field Selection command from the Set menu. SBase 4 displays a requester with two list boxes. The left-hand box shows a list of the fields in the Accounts file; the right-hand box, which shows the current list of selected fields, is empty. You are going to select a list of fields from the left-hand box. First, click on the Date_opened field. SBase 4 copies the fieldname into the right-hand box. Next click on the Name field. Again, SBase 4 copies the fieldname into the right-hand box. Now click on these fields in succession: Type, Balance, Reference. Your list of fields should be: Date_opened Name Type Balance Reference If your list differs, click Clear and start again. The Delete button removes the last item from the list of selected fields. Notice that we have altered the order of the fields. You may select fields in any order with this feature. When you have chosen the fields correctly, click OK. SBase 4 redisplays the current record showing only the selected fields. You have already practiced switching from one index to another using both the File Index Open command and the `-' minus key. Select the Reference index and use the browsing controls to review the file. You will see that SBase 4 now only displays data from the selected fields. Note You can save the selected fields list in the file definition with the Project Save command. Next time you open the file, the Field Selection requester will show the saved list as the default. @endnode @node 7.2 Showing Selected Fields in Table View Table View is especially useful in conjunction with the selected fields list. For example, you can select just those fields you want to see in a sequence that suits a particular index order, then review them in Table View's columnar format. Select the Set Table View menu command now. SBase 4 4 shows the current record across the top of the screen with the names of the selected fields above them. Press `-' to cycle through the indexes until the Date_opened index is selected. Select First Record (HOME). Redisplay by selecting Current Record (UP direction key). Select Fast Forward (PGDN). Use other browsing controls to step through the file in different ways using Table View. Note Table View is a form of output. SBase 4 does not retain all the data you can see on screen in memory, so you cannot point at a record in Table View and click to edit it. Editing is only possible in Record or Page View. @endnode @node 7.3 Printing Selected Fields The Project Print menu command allows you to print out data from selected fields and records in any of the three SBase 4 views: Page, Record, and Table. First you must define your printer and page layout. Since users have a variety of printers and stationery, we shall not attempt to give detailed instructions here. The following procedure is an outline only: 1. Select Printer Setup from the Set menu, and if more than one printer is available, click the one you wish to use. 2. Click OK to select the indicated printer, and to proceed to the Page Setup requester. 3. Define your page size and the margins you want, in inch or centimeter values. 4. Click on the Form Feed button so that it is checked if your printer can make correct use of the form feed character (ASCII 12). 5. Click on the Single Sheet button if you want the printer to wait at the end of each printed page for you to perform some manual operation. 6. Select the font you wish to print in. Click on the upward arrow to the right of the font name box to cycle through the list of available fonts, and stop when the one you want is displayed. 7. You could change the Initialize sequence for your printer now, but you should not normally need to do so. 8. When you are satisfied with all the settings on the Page Setup requester, click OK. You are now ready to print your selections. Choose the Project Print menu command. SBase 4 displays a Filter requester. Unless you want to select a group of records from the file, just click OK. The next requester allows you to specify any index for ordering the output. The current index is the default, in this case Date_opened. Let's make use of the Descending order feature, which will cause the records to be printed starting with the most recent date. Click the Descending order check box. When you're ready, and sure that the printer is on-line, click OK. Provided your printer has been correctly installed, SBase 4 will produce printed output. @endnode @node 8 Lesson 8: Printing Labels Label printing is an integral part of the SBase 4 system. You can define label formats to match practically all types of label stationery. Label formats are re-usable, and are stored on disk as files with an SBB extension. In this lesson we shall use the Accounts file again. Although it does not contain address information the exercise is still realistic: labels are often used to identify folders for client records. Ensure that you have the Accounts file open. The printer should also be connected and on-line, as for the previous lesson, but you can print on ordinary paper rather than label stationery if you prefer. To start the exercise, select the Labels Edit command from the Process menu. SBase 4 now shows the Label Definition requester. The label definition procedure is very simple; in outline: 1. Position the required fields on the label layout 2. Define label dimensions 3. Define print run parameters (number of copies, etc.) 4. Test alignment 5. Print labels @endnode @node 8.1 Defining the Label Layout If a selected fields list is in use it is shown as the default for the label layout. Since we will not be using this, click Clear to remove it. Move the pointer into the field list box and click the Reference field. SBase copies it into the label layout and moves the highlighted selection to the next position across. Click the Name field. (If you position a field wrongly, highlight it and click the Delete button on the requester.) Click in the label layout panel below Reference to make it the active data entry area. In the fields list, click the Date_opened field. Then click in the label layout panel below Date_Opened leaving one blank line. Click the Number field, then the Type field, then move move the pointer below Number and click on the Bank_name field. That completes the layout. Note SBase 4 appends the second, third and fourth fields on a line to the first field, separating fields with a space. @endnode @node 8.2 Defining the Label Dimensions The next task is to define dimensions. As we do not know the details of your labels, these instructions only provide an outline of the procedure. First, set the number of characters for the left margin. This tells SBase 4 where to start printing the first column of labels. Do this by typing the required value into the box at the top left-hand corner of the label layout. Note All measurements are in characters, where a character has a fixed width defined by the font selected in Page Setup. Next, define the number of characters to be printed on each label line. Type the value into the box above the label layout. Now you must specify the gap between labels. This tells SBase 4 where to start printing the second and subsequent labels across. Type the value into the upper right-hand box. Lastly, you must tell SBase 4 how many lines to print before starting the next row of labels. Type this value into the box at the lower left-hand side of the label layout. @endnode @node 8.3 Defining Print Parameters The label print parameters are four in number: Number of copies Number of columns Number of rows per page Number of lines for the top margin These values are set in the lower left-hand area of the requester. First, define the number of label columns you want. There is no real limit, as no currently supported printer is likely to allow more than the 99 columns SBase 4 allows. Unless you have mounted multiple column label stationery on your printer, leave the number of columns set to one. You may also define up to 99 copies per label. For this exercise, leave the value at its default of one. The remaining two parameters are relevant only if you are printing onto separate sheets of labels - not if you are using continuous stationery. The number of rows per page tells SBase 4 to eject a page after that number of label rows have been printed. The top margin specifies the distance from the top of the label stationery to the start of the first label. @endnode @node 8.4 Alignment Test When you have defined all aspects of the label, it's advisable to test the alignment of your stationery in the printer, as even a slight error can result in pages of useless output. Position the stationery so that the print head will begin printing on the first line of the first label (this may apply only to continuous stationery). Now click the Test button on the requester. SBase 4 outputs one page of labels as you have defined them, using the character `X' to show where data will appear on each label line. You may need to adjust your label definition and retest before proceeding to the full print run. @endnode @node 8.5 Printing When you are satisfied with the label test, click OK on the requester. SBase 4 presents a Filter requester. Click OK unless you wish to select a subset of records. SBase 4 begins to print data from the file onto the labels. When label printing ends, SBase 4 returns to the work area, using the set of fields used for the label format as the selected fields list. If your print run fails, for example because the label stationery jams, you do not need to repeat the whole print run. You can use the filter to specify an index key value at which to restart printing. If the current print run stopped after record R003, you would enter a Filter command line like this: Reference.Accounts > "R003" Note To optimize performance, you should ensure that the specified field is the current index. @endnode @node 8.6 Saving the Labels Format When your labels print run has finished, access the Process menu, and the Labels menu item and the Save As command. A requester comes up for saving the Labels definition. Type in `Accounts' and press OK. SBase 4 creates a disk file called Accounts.sbb which contains all the details of the format, but not the filter command line. When you want to use an existing label format, select Labels Open from the Process menu and select from a list of SBB files. @endnode @node 9 Lesson 9: Mail Merge Instead of the usual address merge, we shall use the information in the Accounts file to produce a short memo for inclusion in a mailing to existing clients. Ensure that the Accounts file is the current file before continuing. @endnode @node 9.1 Typing the Merge Document First, you must open the SBase 4 Text Editor window. Select Text Editor from the Utilities menu, then click the sizing gadget near the top right-hand corner of the window to expand it to full size. Type in the following text, pressing ENTER only when you end a paragraph: FOR YOUR INFORMATION We are pleased to enclose details of exciting additions to our range of financial services for the personal investor. Our files show that you have an account with under the name , account number . Please inform this office if any of these details have changed. @endnode @node 9.2 Inserting Merge Fields Now you can insert fieldnames from the database file so that SBase 4 will know which data goes where when you do the merge print run. Click just before the word `under'. Select the Edit Merge Field command, and when the field list requester appears click the Bank_name field. SBase 4 inserts &Bank_name& in the text: Our files show that you have an account with &Bank_name& under the name , account number . Repeat the merge field insertion, positioning the Name field just before the comma, and the Number field just before the period: Our files show that you have an account with &Bank_name& under the name &Name&, account number &Number&. If necessary edit the text until the paragraph is correct. Select Edit Reformat if you need to tidy up the paragraph. Now save the text file. Choose Save As from the Text Editor's Project menu and name the file Acc_memo.sbt. Click OK. Return to the database window by selecting the Project Exit command. @endnode @node 9.3 Running Mail Merge Now that you have created a document into which SBase 4 can merge field data, you can run the merge operation from the Process menu. Before doing this, resize the SBase 4 window so that it occupies about half of the screen. Select Process Mail Merge. SBase 4 displays a Filter requester. Click OK to pass through the requester. Since there is already a document in memory, SBase 4 assumes that it is to be used for the merge operation. The next requester asks whether you wish to display or print the output. In this exercise we shall be content to preview the results, so click No to preview. SBase 4 selects the first record from the Accounts file, merges the data from the Bank_name, Name, and Number fields into the Acc_memo document, and displays the results. The Pause button is switched on. Tap SPACEBAR to preview the next merged document. You can continue previewing for as many merged documents as you like. If you want to stop before the last record has been merged, click the Stop button or press CTRL+C. Expand the window to its full size and click the current record button to redisplay the current record. @endnode @node 10 Lesson 10: Globally Updating a File Let's suppose there has been a general hike in interest rates of one per cent. You need to increase the value of the Interest_rate field by 1 in every record in the file that holds details of a Deposit account - type D accounts. You can do this in SBase 4 with the Process Update command. Select Update Edit from the Process menu now. SBase 4 displays the Update filter requester. We need to build a filter command line that will select just those records that have `D' or `d' in the Type field. To do this we shall use the LIKE operator rather than the equals operator, because LIKE is case-insensitive - it will find both upper and lower case matches. Move the pointer onto the fields list and click the Type field. SBase 4 copies it into the text box. Now click the LIKE button. In the Value box, type `d' and press ENTER. The filter line should read: Type.Accounts LIKE "d" If it does not, click Clear and start again. When the line is correct, click OK. SBase 4 displays the Update Definition requester. Move the pointer onto the fields list and click Interest_rate. SBase 4 4 copies it into the text box and adds an equals sign. Now click Interest_rate again. The update command line reads: Interest_rate = Interest_rate To complete the updating expression, click the + button and then type `1' in the Value box (omit quotes) and press ENTER. Your update command line should read: Interest_rate = Interest_rate + 1 What you have created is a command that instructs SBase 4 to add 1 to the value in each selected Interest_rate field. SBase 4 will read each record, carry out the command, and replace the record in the file with the updated value. When your update command line reads correctly, click OK. SBase 4 quickly processes the whole file and returns to its normal state. @endnode @node 11 Lesson 11: Querying the Database This lesson in this tutorial covers the SBase 4 Query command. This command is capable of creating sophisticated multi-file queries, but we shall only be using the example Accounts file. See Chapter 17 Multi-file Applications for more details of advanced features. Query is used for producing reports with a more detailed structure than is possible with Table View or Process Print. You can control the layout of fields, add reporting functions such as totaling, and specify multi-level sorting, as well as creating filters like those used in earlier lessons. Let's see how this is done. @endnode @node 11.1 Selecting Query Select the Query command from the Process menu. SBase 4 displays the Query Definition requester. The requester has several controls. At the top are the controls for specifying a title for the query, which we shall explore next. Below these are four text boxes, with push buttons on their left. These buttons invoke the requesters for building up the different parts of the query: fields, report functions, filter, and sorting order. At the bottom left of the requester are the controls for specifying the destination for query output: screen, printer, disk file or SBase 4 file. @endnode @node 11.2 Defining a Title To define a title for the query, click in the text box at the top of the requester. Type `Accounts List by Type'. Click the Page and Date check boxes to select them. When the query is output, each page will include this title followed by one blank line, as well as the page number and system date. @endnode @node 11.3 Selecting Fields for Output Click the Fields button at the left of the requester. SBase 4 displays the Query Fields requester. You need to select some fields from the Accounts file, so move the pointer onto the fields list. Click Type, Reference, Name, Date_opened, Balance, and Interest one after the other. SBase 4 copies the fields into the text box, separating them with commas: Type.Accounts, Reference.Accounts, Name.Accounts, Date_opened.Accounts, Balance.Accounts, Interest.Accounts When the fields line is correct, click OK. SBase 4 copies the line into the Query Definition requester. @endnode @node 11.4 Adding Reporting Functions Invoke the Query Report requester by clicking the Report button. You are going to add some statistical functions to the query output. First, click the Report button next to the fields list. This copies the REPORT keyword into the report line, indicating that the following functions are intended to operate on the report as a whole. Now click Count to copy the COUNT keyword into the report line and request a count of the number of records output. To request report totals, click Sum, then the Balance and Interest fields in the fields list. For averages, click Mean, then the same two fields, Balance and Interest. The report line should read: REPORT COUNT SUM Balance.Accounts, Interest.Accounts MEAN Balance.Accounts, Interest.Accounts You will not be able to see the entire line, but this does not matter. Next, you need to request some reporting at subtotal level. We are going to sort and group the records by Type, so click Group to copy the GROUP keyword into the report line, then click Type in the fields list. This indicates that the following functions will apply to records when they are grouped on Type. Now repeat the actions for REPORT, so your final report line should look like this: REPORT COUNT SUM Balance.Accounts, Interest.Accounts MEAN Balance.Accounts, Interest.Accounts GROUP Type.Accounts COUNT SUM Balance.Accounts, Interest.Accounts MEAN Balance.Accounts, Interest.Accounts You are instructing SBase 4 to produce global and subtotal values for the Balance and Interest fields, and record counts for every group and the report as a whole. When the report line is correct, click OK to copy it into the main Query Definition requester. @endnode @node 11.5 Defining a Filter Queries may have the usual kinds of constraint applied through a filter command line. In this case, we shall simply exclude accounts which were opened before a certain date. Click the Filter button to invoke the Query Filter requester. Move the pointer onto the fields list and click Date_opened. Then click the >= button, type `1/1/85' into the Value box and press ENTER. Your filter line should read: Date_opened.Accounts >= "1/1/85" Make sure the line is correct, then click OK to copy it into the Query Definition requester. @endnode @node 11.6 Defining a Sorting Order The last query line to be defined is the Order line, which specifies how the output is to be sorted. We shall ask for the records to be sorted in descending order of the Balance field - from largest to smallest - and by Type. Click the Order button to invoke the Query Order requester. Click the Type field, the Ascending button, the Balance field, and the Descending button. SBase's Order line should read: Type.Accounts ASCENDING, Balance.Accounts DESCENDING As you did before, click OK to copy the correct Order line to the main Query Definition requester. @endnode @node 11.7 Viewing Query Output The default query output destination is the screen, and it is always advisable to check your query definition visually before committing to what might be a long print run. Review the contents of the four text boxes. When you are satisfied that they are what you intended, click OK. SBase 4 removes the Query Definition requester and displays the results of the query on the screen. When the output is broken by a subtotal level, as it is in this query, SBase 4 prints the reporting results in a fixed format: subtotal, mean, record count . The same format is used at the end of the query for the global report results. @endnode @node 11.8 Saving the Query Finally, save the query you have just created so that you can open it again for reuse. Select the Query Save As command from the Process menu. Type in a name for the query, Accounts, and press ENTER. SBase 4 creates a disk file with an SBQ extension, which will be listed every time you select the Process Query Open command. The query file will open the Accounts file for you if it is not already open. See Chapter 14 Defining and Using Queries, for more details of this command. @endnode @node 12 Lesson 12: Creating Text Type Forms In this lesson we will create a form which only incorporates text. Lesson 13 will discuss incorporating picture files on a form. Both lessons apply to both the SBase Personal 4 product and the SBase Professional 4 product. In the SBase Professional 4 the DML language expands the capabilities of the Form Designer. These features are discussed in the DML Users Guide that accompanies SBase Professional 4. Again we will use the Accounts file for example. If SBase 4 is open, close the program and double click on the Form Designer icon. This will open the Form Designer. In SBase Personal 4 the Form Designer screen appears as shown below. The Form Designer Pro screen of SBase Professional 4 differs slightly from the Form Designer in SBase Personal 4. The Form Designer Pro adds a CAL button, VAL button and FNC button all of which interface with the DML language of SBase Professional 4. The DML Users Guide has a discussion on how to incorporate these features into a form. Forms are associated with databases, thus once the Form Designer is open, we next need to open the appropriate database file. This the file to be used the form we will create. Thus for this lesson we will open the Accounts file. To open a database file in Form Designer: On the Project Menu, access the Open/SB File command. You can also use the Amiga Key and O keyboard shortcut. This will bring up the standard file requester, click on the Accounts file name. The text style form we will create is a bank account signature card. It will be very simple, but it will illustrate the basic elements of form: design - Laying out broad areas of color - Drawing boxes - Changing Text - Importing fields The finished product appears as shown below: When laying out a form, it is usually best to have a end product in mine. However, the Form Designer allows you to change your mind. You can move, re-size, or even remove any element on the form. The Form Designer has a built-in priority of design elements. These can be altered. To see the default priority, select the command Hierarchy on the Page menu. To see how this works: We will first enter the text for the title of the form and then draw a solid area of color which will automatically place itself behind the text. @endnode @node 12.1 To enter text: - First, select a font by pulling down the Define menu and selecting the command Font, or by pressing the keyboard shortcut Amiga key with F. This will bring up the font selector shown below: As you select different fonts, an example of the font is shown in the bottom part of the font selector. In addition, the available point sizes for the font are shown to the right of the font names. - In our example we select Garnet, point size 16. Click on the OK button to select and use the font. - Click on the Text icon. - Move the pointer to approximately position X= 2.00 and Y =.3 and press the left mouse button. This will put an outline square on the screen for entering text. - Type in the text "Customer Address and Signature" without the quotes - Move the pointer to approximately position X=3.3 and Y = .6 and click the left mouse button. - Type in the text "Card" without the quotes. @endnode @node 12.2 Now we will but a block of color behind the text. - Click on the solid box icon. - Click on any of the solid colors. - Move the pointer to approximately to position X =1.6 and Y =.16, click the right mouse button. - While holding down the left mouse button, drag the pointer diagonally down and to the right to approximately position X=5.7 and Y = .8 As you are dragging the mouse pointer, an expanding outline rectangle is drawn on the screen. - Release the mouse button, and the rectangle will be filled with the selected color and the text will automatically be placed on top of the rectangle. @endnode @node 12.3 Now we will draw four more graphic objects on the form. - Click on the open box icon. - Click on the rounded corners icon. - Click on a different color. - Move the mouse pointer to approximately position X = 1.3 and Y = 1.0 - Click and drag the mouse pointer to approximately position X = 3.3 and Y = 1.5. - Release the mouse button and an outline rectangle with rounded corners will be drawn. - Move the mouse pointer to approximately position X = 4.5 and Y = 1.0 - Click and drag the mouse pointer to approximately position X = 6.5 and Y =1.5 - Release the mouse button and a second outline rectangle will be drawn. - De-select the rounded corners icon by clicking on it again. - Click on a different color. - Move the pointer approximately to position X = 1.3 and Y = 1.7 - Click and drag the mouse pointer to approximately position X = 3.25 and Y = 1.9. - Release the mouse button and a square cornered outline rectangle will be drawn. - Move the pointer approximately to position X= 4.5 and Y = 1.7 - Click and drag the mouse pointer to approximately position X = 6.4 and Y = 1.9 - Release the mouse button and a fourth outlined rectangle will be drawn. @endnode @node 12.4 The next graphic element we will add is lines. - Click on the line icon. - Click on the second finest line width icon. - Move the pointer to approximately position X= 1.25 and Y = 1.15 in the first outline rectangle we drew. - Click and drag the mouse pointer to the right to the other side of the rectangle. - Release the mouse button. - Move the pointer to approximately position X= 1.25 and Y = 1.33 in the first outline rectangle we drew. - Click and drag the mouse pointer to the right to the other side of the rectangle. - Release the mouse button. - Move the pointer to approximately position X= 4.5 and Y = 1.15 in the second out line rectangle we drew. - Click and drag the mouse pointer to the right to the other side of the rectangle. - Release the mouse button. - Move the pointer to approximately position X= 4.5 and Y = 1.33 in the second out line rectangle we drew. - Click and drag the mouse pointer to the right to the other side of the rectangle. - Release the mouse button. @endnode @node 12.5 Next, more text will be placed on the form. First, we will select a new font. - Pull down the Define menu and access the Font command. - Select cedfont, point size 9 - Enter the following text at the following approximate positions: Text Position Name X=.2 Y=1.0 Address X=.2 Y=1.2 City ST Zip X=.2 Y=1.3 Phone X=3.6 Y=1.0 FAX number X=3.6 Y=1.2 SS# X=3.6 Y=1.4 Signature X=.2 Y=1.7 Co-signature X=3.6 Y=1.7 - Pull down the Project menu and save the new form. @endnode @node 12.6 Incorporating Database Fields Now we can place the fields on the form. In summary, the process is to open the database or databases from containing the desired fields, click on the FLD icon, then click on the form where you want to place the field, then select the field name from the Field Selection Requester, then click on the OK button. The steps to add fields to our example form are as follows: If you have not done so already, open the Accounts database with the command Open/File from the Project menu. Click on the FLD icon. Move the pointer to the approximately to the position X= 1.4 and Y = 1.1 and click on the form with the left mouse button. This will present the Field Selection Requester shown below: - Find the field called "Name" and click on its name, it should now appear in the gadget at the bottom of the requester. - Click on the OK button. - Repeat this process for for the following fields placing them approximately at the indicated position. Field Position Street X=1.4 Y=1.2 City X=1.4 Y=1.4 ST X=2.4 Y=1.4 Zip-code X=2.6 Y=1.4 Phone X=4.6 Y=1.1 FAX X=4.6 Y=1.2 SS_number X=4.6 Y=1.4 If you find that the fields are not placed within the boxes to your liking, you can move them by clicking on the Move icon and then clicking on the particular field name and repositioning it. Other methods for moving objects on forms include: Shift-Click allows you to select multiple objects (including field names) to be moved. Shift-drag will create a selection box around a group of objects that can be moved together. - Save the revised form with the Save command on the Project menu. You can now print out each record in your database within the form and use the hard copy as signature card. In the next lesson, we will show you how to put a scanned image of the customer's picture and signature on the form so the form can be used on-line from the bank teller's terminal. @endnode @node 13 Lesson 13 Using External Files in a Form SBase has the ability to use external files such as picture files and even sound files. External files are IFF files that are created by third party products that can be loaded into a form created in SBase. In our example, we will add two scanned image picture files to the Signature card form created in Lesson 12. The two images are a photo of the primary signer on the account and a scan of their signature. If your local bank were using SBase, they would then be able to have your signature card on line right at the teller's window instead of storing a hard copy in a file cabinet as is typically done at banks. @endnode @node 13.1 Define the External File Field in the Database - Open SBase 4 , and then open up the Accounts file. - Select Modify/File command from the Project menu. - Click on the External field attribute button. - Click on the name gadget and enter the name for the first external field "Photo" without the quotation marks. - Click on the Text field button, and change the length of the field to 40 characters. - Click on the OK button in the Text field requester. - Click on the ADD button in the File Definition requester. Add the second external field in the same manner except use the field name "Signature" without the quotes. When both are defined, - Click on the OK button for the File Definition requester. - Click on the OK button for the Save File Definition requester. @endnode @node 13.2 Modify the Form To open Form Designer from within SBase 4, select the Modify/Form command on the Project menu. Next, open the Signature form with the command Open/Form on the Project menu on the Form Designer. In order to add the scanned photo and signature of account holder, we will first re-arrange the existing form to make room for the new fields. First we want to move the title of the card to the left: - Click on the Move button of the Form Designer - Press the Control key, move the mouse pointer just beyond the upper left-hand corner of the title block. - Click the mouse left mouse button and drag the pointer down and to the right to create a drag box around the title block. - Move the box to the left to approximately position X=.4 and Y=0 - Click outside the box to release the drag box. Now we are going to draw a frame for the photo. - Click on the Open rectangle draw button. - Place the mouse pointer at approximately position X=4.7 and Y=.1 - Click and drag down and to the right creating the frame for the photo. Be sure to make it large enough. To prepare for the scanned signature file, we need to re-size the signature box. - Click on the re-size button. - Click on the bottom right corner of the signature box and drag it down and to the right to enlarge the box. - Do the same for the co-signer signature box. Now we are ready to place the external files for the scan photo and scanned signature on the form. - Click on the FLD button. - Click inside the photo box and the field name requester will pop up on the screen. - To select the name Photo and either double click on the name or click on the name and then click on the OK button. Repeat the process for the signature field. - Click on the signature box on the form. - Find the field called Signature, click on the OK button. - Save the modified form with the Save command on the Project menu. Now you are ready to enter the name of the photo and signature scanned image files into the revised database and then display this additional information in the modified form. Close the Form Designer with the close gadget. You will be returned to the Accounts database, if not open it now. To add the information in a Record do as follows: - Find the John B Sloop record. - Click on the Photo field - Enter "JBS,s" without the quotes. JBS is the file name for the scanned photo of John B Sloop. The s is added to the end of the file to indicate the file is to be scaled to fit the frame. - Click on the Signature field. - Enter "JBS_sig,s" without the quotes. This is the name of the scanned signature for John B Sloop and again the s is added to indicate the picture file is to be scaled. Repeat the process with the additional records as follows: Record Photo Field Signature Field Sarah Armstrong SA,s SA-sig,s Mary Garcia MG,s MG-sig,s Janice Armstrong JA,s JA_sig,s To view the scanned photos and signatures: - Open the form with the command Open/Form on the Project menu. - Select the name Signature - Click on the OK button - Click on the Camera button to see the scanned images for the record. You can move between records with the VCR buttons and use the filter, query and look up functions as well. You have just explored one of the most revolutionary features of SBase, that ability to display graphic images in a record. It is also possible to add IFF sound files to a record as well. They should be entered as file names into their own external field. @endnode @node 14 Lesson 14 Viewing dBase files with SBase You can use SBase to view dBase files without having to convert them into the SBase format. You can also convert them and import them into an SBase database. This is discussed in Chapter 11 Working with dBase Files. On the example data disk that accompanies SBase is a small dBase file. It contains ficticious information concerning suppliers typical to a small manufacturing concern as well as an index on the Product field of the database. In this exercise, we will open the dBase file, brows through it with the VCR controls and then use the Look up command to find records for certain types of items to be supplied or purchased. To begin, open up SBase 4 in the usual manner. - Pull down the Project menu and access the command Open/dBase File You will be presented with the typical file selector with drive button and a drawer and file gadget. If you have copied your data example files on a hard drive, find the appropriate drawer otherwise, put the data example disk in any disk drive and find the file called "supplies.dbf" - Double click on the name "supplies.dbf" to open A reminder requester comes stating "Access to file is not allowed". In this mode, we can only Read a dBase file we cannot add or modify records. At this point, you can view the records of the dBase file in the order they were entered on the disk. To open the index for the file: - Use the Open/Index command on the Project menu. All of the available indexes will be listed. - Click on the index file "byprod.ndx Now the display will be alphabetically by the Product field. You can now use Look up command to find a supplier of a specific type of commodity. - Click on the Lookup button. - Enter "Pizza" in the Record key string gadget that appears on the screen without the quotes. - Hit enter or click on the OK button. The first record with entry Pizza will be presented on the screen. To find a different product, - Click on the Lookup button again. - Enter "Manuals" in the Record key without the quotes and click on the OK button. There are two records that match this Lookup criteria, use the forward VCR control to see the next match. The Filter and Query functions of SBase can also be used to more accurately find particular matching records in a dBase file. With virtually no effort, you have the ability in SBase to read dBase type files without being required to set up a database in SBase itself. This is particularly useful in a network environment. If you connect your Amiga computer to a network with computers running a dBase file, you can then read the file over the network using the Amiga. This can be done with the non-networking version of SBase. Oxxi's Amiga Client Software product which allows Amiga computers to participate on Novell networks is one possible way to connect your Amiga computer to a network with other Amigas and other types of computers. Call Oxxi for further details. @endnode