Active GUI element

Static GUI element

Code

WPS object

File/Path

Command line

Entry-field content

[Key combination]

How to create a database using Lotus Smartsuite

by Arthur van Egmond, © February 2006

Arthur van Egmond lives in the Netherlands. He is married but doesn't have any children yet. He works for the government in the department of Social Security and his main job is to keep the client computers up and running. His first experience with OS/2 was with version 2.1. Since then he upgraded to eCS 1.2. He owns or at least has seen nearly all versions.

Welcome back. Thanks for joining our tour on creating a database using Lotus Smartsuite.

As I have mentioned before, making a database is first planning what information to put in the database. How do I want the database to be organized? Writing this down on paper is a good idea.

It's All About Presentation

The databases we are going to make are:

menu — This is our starting point where we open all other databases we make.
genre — In this database we store the kinds of genres that a movie can have.
dvdextra — Stores the various formats a dvd movie can have. Think about dvd's that contain only a movie or a movie with a menu, etc.
dvdformat — Stores the different formats that are available today. Think about dvdrom, dvd+r, dvd+rw etc.
contacts — Stores names and adresses.
movies — Stores our movies.

Why like this?

Well, the menu we can put on the desktop so we have only one shortcut on the desktop to reach all our databases. (This works for me; I don't like to type too much.) The databases Genre, Dvdextra and Dvdformat store information that is used over and over again. This way you only have to enter the data once; later you just select what you need. Of course there are other techniques to get the same result but for me I prefer this one.

The database Contacts we use for our contacts, like I mentioned before. Have you ever loaned a movie (or anything else for that matter) to someone but you could not remember who it was? Now it's easy. Before you give a movie to one of your relatives start up your database and select their names in the corresponding record. You can never forget again who borrowed the movie.

Make the Genre Database Table

In this part we make the databases Genre, Dvdextra, and Dvdformat. The databases all have the same look. This means all buttons are in the same place, the colors and fonts are the same, etc. To see what the results should look like, open the included examples in Approach.

Please review Part One, Figure 4 — Extended field definition dialog, which was published last month. For Part Two I have created a directory called Os2voice. In this directory I store all files created by this project.

Now create a database and name it genre.dbf; it has two columns for data. The first field is the title where we store the genre names. The second field is an ID number; it's always a good idea to give each record a unique number.

Let us add the first field. Type Genre for the fieldname. It is text we are adding so we can leave this unchanged. Now the size of the field. The default size of 10 characters is a little small, I think. Let us change this to 15 characters. 15 should be enough. We can always extend the size later without losing data.

The second field we name GenreID. For really complex databases an ID with a mix of numbers and letters is required. Our database is not so big so we can use only numbers. Set the datatype to numeric. Ten characters means we can store records from 1 through 9,999,999,999. I doubt there are that many types of genres. Let us set this value to 3. Now we can store from 1 until 999. Seems adequate to me.

Now we need to make sure the GenreID field contains only unique data. Click on the Options >> button. Select Serial number starting at entry field (it defaults to 1). Now click on the tab Validation. Now select Unique and Filled. These two options do the following:

Unique: You are assured each GenreID is used only once. You cannot continue if it is already in use.
Filled: You cannot continue if there is no information in this field.

Since we use auto-enter-serial, Approach automatically increments the number and adds it to GenreID.

Click the OK button.

Changing the Appearance

Basically we are finished and ready to add information to this database. I find data entry a bit boring so instead let us add some color to this database and create a few macros. From now on I describe the actions you should take. If you want to know more about a certain subject, click the Help button.

Design the Header

Click the Design button. Select the header and click on Delete (keyboard). Click with the right mousebutton in the dotted gray area. Select Properties with the left mouse button.

On the tab Margins set all margins to 0cm.

On the tab Basics set Formname and Pagename to Genre.

On the tab Color set the Background color to darkblue. This is the color for the background we are using for all our databases. When you want another color, just remember to use thiat color in all the other databases as well.

Now drag a Textbox.

In Properties click Font
Attribute	Value
Font	Gill Sans 72 Bold
Color	Yellow
Text Relief	Bottom, one
Alignment	Center

Type Genre and deselect the textbox by clicking somewhere in the darkblue dotted area. As you can see you now have yellow letters on a white background. Select the textbox.

In Properties click Color tab
Attribute	Value
Style	None
Fill Color	Transparent
Border Color	Transparent
Border Width	Hairline

Drag the square to the upper left corner until it can no longer move.

In Properties click Size tab
Attribute	Value
Width	22cm
Height	2.5cm
Top	0.0cm
Left	0.11cm

Note: You cannnot change Left to 0.0cm.

On the tab Macro you can add a name for this object. Approach allows you to set a name for each object you create. If you find this too much trouble, you can leave the default. I choose to change the names of all objects I create to a logical name. So I changed this to Header.

How does this look so far? I think better but not quite there. Move the two fields somewhere to the right of the screen for now.

Draw a Rectangle
Attribute	Value
Width	3cm
Height	15cm
Top	0cm
Left	0.11cm

Or, you can just draw a small square, move this to the top left corner, and edit the values.

You can see Approach always uses the same presentation by default. For the text it was sunken and a white background and now you see a white rectangle sunken. Let's change this to another color.

Select No style on the Color tab
Attribute	Value
Color	Light Brown
Border Color	Transparent
Border Width	Hairline

Note: "No Style" is the style that is applied when no other style is specified.

On the Macro tab I have typed Menu.

Now we have a header and a place where we can put our buttons. However, for now let's focus on the fields. You can change some options for all fields simultaneously. Just click with the left mousebutton in the blue dotted area and drag until both fields are selected.

Style the Data Field

Select the Font tab
Attribute	Value
Font	Gill Sans 10
Color	darkblue

Select Label
Attribute	Value
Font	Gill Sans 10
Color	Yellow

Select Color tab
Attribute	Value
Fill Color	Transparent

Deselect the fields and select the Genre field. Now click on the Number tab. Here you can change the way the data behaves in the field. I like to set this to Text - First Capitalized. Now you only have to type in the text without using the Shift key because Approach changes "science fiction" to "Science Fiction," or "action and adventure" to "Action And Adventure."

Click on the Size tab
Attribute	Value
Width	5cm
Height	1cm
Top	3cm
Left	4cm

For the field GenreID
Attribute	Value
Width	2.5cm
Height	1cm
Top	3cm
Left	12cm

Now select the Worksheet. Change the name to List. Click in the square next to Genre. Select dark blue for the background. Select Gill Sans 10 for the font, and set the font color to the same color as the menu background (light brown). Click again in the square next to Genre and set the font color to yellow. Go back to the Genre tab.

Design a Button

We are almost ready to create buttons.

But before we create the buttons themselves let us make a default style for the buttons and the button text. Click Properties on the tab Style.

Click on Create style; name the style Button and the description Button style. Click OK.

Click on Manage styles.

Select Button, Click Edit
Attribute	Value
Text color	Transparent
Frame	Rise
Fill Color	Light Yellow
Label Color	Transparent

Click OK

Select Button text, Click Edit
Attribute	Value
Font Name	Gill Sans
Alignment	Center
Text Color	Maroon
Border	Transparent
Fill Color	Transparent

Click OK

Now to create a few buttons: First - Last - Previous - Next - New - Delete - Help - Close.

Draw a rounded rectangle and on the tab Styles select Button. As you can see the rectangle is changed to the settings you have applied in "button style." All you need to do now is position it and adjust the size. Later when we make the macros, we can link the macros to the buttons.

The buttons are positioned on this grid:

First      W. 2.5cm  H. 0.5cm  T.  2.0cm  L. 0.36cm
Last       W. 2.5cm  H. 0.5cm  T.  2.5cm  L. 0.36cm
Previous   W. 2.5cm  H. 0.5cm  T.  3.0cm  L. 0.36cm
Next       W. 2.5cm  H. 0.5cm  T.  3.5cm  L. 0.36cm
New        W. 2.5cm  H. 0.5cm  T.  4.5cm  L. 0.36cm
Delete     W. 2.5cm  H. 0.5cm  T.  5.0cm  L. 0.36cm
Help       W. 2.5cm  H. 0.5cm  T. 13.5cm  L. 0.36cm
Close      W. 2.5cm  H. 0.5cm  T. 14.0cm  L. 0.36cm

You have noticed a Help button. With this button we create our own help screen. For that we need to create a Form. Click in the menu on Create and select Form. Name the form Help and click Done. Make all changes to the form so they are the same as the Genre form. Now you have a empty darkblue dotted area.

Draw a Rectangle in the dotted area
Attribute	Value
Width	10cm
Height	7cm
Top	0cm
Left	0.11cm

Draw a TextSquare. Type HelpScreen.

Set the TextSquare's properties
Attribute	Value
Font	Gill Sans 10
Color	Maroon
Alignment	Center
Width	10cm
Height	0.5cm
Top	0cm
Left	0.11cm

Draw a Horizontal Line
Attribute	Value
Color	Maroon
Border Width	½ point
Width	10cm
Height	0.5cm
Left	0.11cm

Create a button, use "button style"
Attribute	Value
Width	2.50cm
Height	0.50cm
Top	0.60cm
Left	0.25cm

Making Macros

Now that we have postioned the buttons, we can start making the macros.

A macro is a command that is executed when you select it.

Create a text box and type the following "help" text:

With F1 you get this screen.
With F2 you can go to the main view.
With F3 you can go to the list view.

Set the properties:
Attribute	Value
Font	Gill Sans 10
Color	Maroon

When you press Close, you return to the menu.

Set the properties:
Attribute	Value
Background Color	Transparent, no style
Width	9.05cm
Height	5.50cm
Top	0.50cm
Left	0.15cm

Click in the darkblue dotted area, select Macros in Properties, and select Show this form as dialog.

Now it's time to make these macros and link them to the buttons.

Click on Define Macro.

Click New. Name of the macro Close1 command view - switch current view to Genre. Click OK.

Click New. Name the macro Close command close. Click OK.

Click New. Name the macro List command view - switch current view to List (F3). Click OK.

Click New. Name the macro Genre command view - switch current view to Genre (F2). Click OK.

Click New. Name the macro First command record - go to the first record. Click OK.

Click New. Name the macro Last command record - go to the last record. Click OK.

Click New. Name the macro Previous command record - go to the previous record. Click OK.

Click New. Name the macro Next command record - go to the next record. Click OK.

Click New. Name the macro New command record - create a new record. Click OK.

Click New. Name the macro Delete command delete - delete current record. Click OK.

Click New. Name the macro Help command view - switch current view to help (F1). Click OK.

Click Done.

In the "Help" Form select the button and select On Selected Close1. Draw a textbox, type Close. Automatically the button text style is applied: W.2,5cm H.0,5cm T.6cm L.0,25cm

In the "Genre" Form do the same for First - Last - Previous - Next - New - Delete - Help - Close. Position them on top of the buttons after you have applied the macros.

Fit and Polish

I think we are almost finished. Let's get rid of some things we don't need anymore. In the menu-item view click on Show Rulers to deselect the rulers. In the menu-item view click on Show Actionbar to deselect the Action Bar. In the menu-item view click on Show Statusbar to deselect the Status Bar.

Actually I think we don't need the menu anymore. This database is finished. We don't have to add any more fields or buttons for that matter. We only want to store the different type of genres here so we don't have to type them over and over again.

Click on the Macros tab in the properties window.

Click Define Macro
Click New
For Macro Name type menu
Click on Customize Menus
Select Short Menu
Click on Copy, give the menu the name My Menu
Click OK

Select My Menu.

Click Edit
In the left pane delete all items (select and click Delete) except "Standard Menu" and "Help"
In the right pane delete all items except "Help Topics" and "About Approach"
Click OK
Click Done
Click OK
Click Done

Select the Help tab in Properties under Basics by attached menu bars. Select My Menu. Do the same for "List en Genre".

Click in the menu on View. Click on Show Viewtabs to deselect the tabs.

Save your Approach file.

Click on Vew and select Browse.

Voilà!

The Genre database is finished. Now you can create the databases Dvdextra and Dvdformat the same way.

Hope to see you again in Part Three where we create the database Contacts.

Formatting: James Moe
Editing: James Moe