@DATABASE @NODE MAIN Chapter01 "SBase Personal - 2. UP AND RUNNING IN 15 MINUTES" @{" 2. UP AND RUNNING IN 15 MINUTES " link 0} @{" Step 1 Name the Database File " link 1} @{" Step 2 Define the Fields of the Database " link 2} @{" Step 3 Create an Index " link 3} @{" Step 4 Enter the Records " link 4} @{" Step 5 Layout the Merge Letter " link 5} @{" Step 6 Save the Merge Letter " link 6} @{" Step 7 Merge the Data and the Letter " link 7} @{" Step 8 Print the Form Letter " link 8} @endnode @node 0 In this chapter we give you a taste of database management using SBase. More details about the various features of SBase can be found in later chapters. The example presented in this chapter applies to both SBase Personal and SBase Professional. We have deliberately avoided any references to the DML language found in SBase Professional. The example presented is a database of names and address that are indexed by zip code and merged into a form letter. We will first create a database, then define an index, create a form and finally merge the data into the form. The finished files of the example can be found on the Example Disk under the name Chp_2_database and Chp_2_Form. @endnode @node 1 Step 1 Name the Database File - While pressing the right mouse button, move the pointer to the Project menu and select the New menu and item and File submenu item. - Release the right mouse button. Versions of SBase running under AmigaDOS 2.0 will display a file requester as shown below for defining a new database file name and directory path for the database file. The file requester is a standard file requester including drive buttons and string gadgets for entering the file name. On computers running AmigaDOS 1.3, the file requester lacks drive buttons. *** @{" See Fig 2-1 " system "ps sb2:fig2-1"} *** - Click on the appropriate drive button. - Move the pointer to the File string gadget and enter "Ch_2_database" - Click on the OK button. SBase now displays the Password protection requester. In our example we will not be using Passwords. - Click on the OK button. @endnode @node 2 Step 2 Define the Fields of the Database After assigning and a name, directory path and passwords, SBase presents the File Definition requester shown below. *** @{" See Fig 2-2 " system "ps sb2:fig2-2"} *** The top portion of the File Definition requester is a scrollable window for listing the field names, their attributes and format. Below the scrollable window are check boxes for the various field attributes, a string gadget for entering the field name and an assortment of buttons . When creating a database, it is best to plan the field names and their attributes ahead of time. However, you can alter the field attributes at any time and even add and delete field names. In our example, we will use the following field names. - Title - First_Name - Last_Name - Address_1 - Address_2 - City - ST - Zip_code - To enter the first field, Click on the TXT check box (the default field type is TXT so it may already be checked). - Move the pointer to the Field string gadget and enter "Title" without the quote marks. Click on the Add button. You will notice the Title field is now listed with the default character length of 20. Repeat the process for all of the field names. - To alter the length of a field Click on the name Title in the scrollable window. - Click on the TXT check box. SBase will now display the Text Attribute requester shown below. *** @{" See Fig 2-3 " system "ps sb2:fig2-3"} *** - Backspace over 20 and enter 4. - Click on the OK button. Repeat the process with the following fields changing their character lengths as indicated. - Last_Name 30 - Address_1 40 - Address_2 40 - ST 2 - Zip_Code 10 Once you have altered on the field lengths, click on the OK button to accept the Fields. @endnode @node 3 Step 3 Create an Index Immediately upon accepting the Fields, SBase displays the New Index requester shown below. The top portion of the Index requester is a scrollable window listing all of the field names. On the bottom portion of the Index requester are two check boxes, one for Normal index and the other for Unique Index. To define the Zip_Code index: - Scroll the window down until the Zip_Code field name is visible. - Click on the Zip_Code field name - Click on the Normal Index check box - Click on the OK button. @endnode @node 4 Step 4 Enter the Records We are now ready to enter the data into the database. After you defined the Zip_Code index, SBase will display a blank record for entering data. The Record window shown below can display the fields of the record in the default Record mode with the field names listed in the upper right-hand corner or in Table mode with the field names listed across the top of the window. To change to Table view - Select the Table view menu item on the Set menu. The field names will be shown horizontally across the window. Since the sum of the field lengths is greater than the width of the window, to see all of the fields you must scroll the window to the right with the horizontal scroll gadget at the bottom of the window. - To return to the Record display mode, - Select the Record view menu item on the Set menu. In our example we will enter five records as follows: First Record - Title Mr - First_Name Harold - Last_Name Greenberg - Address_1 6 Springview Rd - Address_2 - City Memphis - ST TN - Zip_Code 66789 Second Record - Title Mrs - First_Name Janice - Last_Name Pollack - Address_1 First Bank - Address_2 845 Tenth Ave - City Dallas - ST TX - Zip_Code 79008 Third Record - Title Miss - First_Name Elizabeth - Last_Name Baldwin - Address_1 909 Main St - Address_2 - City Boston - ST MA - Zip_Code 06087 Fourth Record - Title Mr - First_Name Robert - Last_Name Hatfield - Address_1 78-A St Anne Place - Address_2 - City New Orleans - ST LA - Zip_Code 76231 Fifth Record - Title Mr - First_Name Mike - Last_Name Jones - Address_1 34 Newell Blvd - Address_2 - City Philadelphia - ST PA - Zip_Code 18745 To enter the first record, Move the pointer to right of the field name "Title" and click to make this the active field. - Enter "Mr" without the quotes - Press the Enter key - The active field is now "First_Name", Enter " Harold" and Press the Enter key. Repeat the process until all of the fields have been entered for the first record. After entering the zip code, SBase will present the Save Record requester shown below. *** @{" See Fig 2-4 " system "ps sb2:fig2-4"} *** Click on the OK button or Press the Enter Key. SBase will then display a requester "Continue Data Entry?" - Click on the OK button to clear the window for a new record. You can manually clear the screen to enter the next record, by either selecting the New menu item on the Record menu or pressing the key combination right Amiga key with the letter N. The Record window will be cleared and you can then repeat the data entry process with remaining four records. When you have entered all five records and saved them, select the Project Menu and the Quit menu item to close SBase or use the Amiga Key with Q key. @endnode @node 5 Step 5 Layout the Merge Letter To design the Merge Letter, you must first open the SBase Text Editor. The Text Editor can be opened by selecting the Utilities menu and then the Text Editor menu item or by Amiga Key with H key combination. The blank Text Editor window is shown below. *** @{" See Fig 2-5 " system "ps sb2:fig2-5"} *** Now we will begin entering the text for the letter. Wherever you wish to place a field from the database, you select the Merge Field command on the Edit menu. SBase then displays a requester listing all the field names of the database. To select a field, click on its name and then click on the OK button. When you do so, the field name will appear in the Text Editor window. However, it will be followed by a period and then the database file name. This entire text string will begin and end with the & sign. It will appear as follows: &Fieldname.Filename& By utilizing this format for specifying the merge field, you can access fields from different databases for merging into a single document. To begin our letter: - We first type in the characters "February 14, 1994" without the quotation marks. Then press the Enter key twice to end the line and insert a blank line. Now we will access the database fields to create the inside address for the letter. - Select Edit menu and then the Merge Field menu item. - Select the field "Title" by clicking on its name and then click on the OK button. The line will appears as follows: &Title.Ch_2_data& Now we will add the First_Name and Last_Name fields for the first line of the address. - Press the space bar to insert one space between the Title field and the First_Name field. - Select Edit menu again and Merge Field menu item. - Click on the First_Name field and then the click on the OK button. Now the line appears as follows: &Title.Ch_2_data& &First_Name.Ch_2_data& - Press the space bar again to insert a space between the first name and the last name. - Again select the Edit menu and Merge Field menu item. - Click on the Last_Name field and then click on the OK button. Now the line in the Text Editor window appears as follows: &Title.Ch_2_data& &First_Name.Ch_2_data& &Last_Name.Ch_2_data& Follow the same process to add the Address_1 field on the next line, Address_2 on the third line and City, ST and Zip_Code fields on the last line. The end result should be as follows: &Title.Ch_2_data& &First_Name.Ch_2_data& &Address_1.Ch_2_data& &Address_2.Ch_2_data& &City.Ch_2_data& &ST.Ch_2_data& &Zip_Code.Ch_2_data& You can access the same field more than once in the Merge Letter. The salutation of the letter is set up as follows: - Type in the text "Dear" without the quotation marks. - Press the space bar, then select the Edit menu and the Merge Field menu item. Click on the field First_Name and click on the OK button. - Then immediately type in ":" without the quotes and press the Enter key twice to end the line and insert a blank line before the body text of the letter. Type in the remaining text as follows: Celebrate Valentine's Day with a fresh bouquet of flowers sent to anywhere in the continental US for only $17. Just call in your order to your local FDA florist and your flowers will be delivered within 3 hours of your order! To find the local FDA florist near you call 800-786-7000/ Have a Happy Valentine's Day. @endnode @node 6 Step 6 Save the Merge Letter To save the Merge Letter text file: - Select the Save command on the Project menu. SBase presents the usual file requester complete with drive buttons and a string gadget to enter a file name in this case we will call the text letter file Valletter. - Click on the Save button to save the file. @endnode @node 7 Step 7 Merge the Data and the Letter Before beginning the Merge process, if you intend to print the merge letters, you should properly install the printer to work with SBase. This should be done from your Workbench. If you are merging the data from a cold start so to speak, you first: - Open up SBase with a double click on the program icon and then Open the database file by selecting the Open/File commands on the Project menu. With the file selector open the Ch_2_database file. - Next, you open the Text Editor by selecting Text Editor on the Utilities menu. - To open the specific Merge Letter file, select Open command on the Project menu of the Text Editor. If only one Text file has been created, SBase will immediately open that file, If more than one has been created, SBase will display the typical file requester for selecting files. - Find the Valletter file and click on the OK button. To merge the data: - Click on the SBase window to make the database active rather than the Text Editor active. - Select the Mail Merge command on the Process menu. This will bring up a requester called the Mail Merge Filter as shown below. In this brief example we will not set up a filter. This is used to selectively merge certain records from a database. For example, only send a dunning letter to accounts over 60 days pass due. - In our example, we will simply click on the OK button. @endnode @node 8 Step 8 Print the Form Letter SBase now presents the output requester with the message "Output to printer?". - Click on the Yes button to output to the printer, - Click on No to display to the screen, or - Click on Cancel to abort the merge process. You have now performed one of the most useful tasks for database management. If you simply want to create mail labels, SBase has built in predefined labels under the Process menu of the database. You could make up your own label merge file with the text editor or even utilize the Form Designer to add graphics and lines to make more sophisticated labels. @endnode