Jump to article
< >

Active GUI element

Static GUI element


WPS object


Command line

Entry-field content

[Key combination]


How to create a Database using Lotus Smartsuite
Part Four

by Arthur van Egmond, © June 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 me in our tour through creating a database using Lotus Smartsuite. Over the past three installments we have created a few databases.

We started simply enough with the database Genre. You did your homework and you made two other databases, Dvdformat and Dvdextra. In Part 3 we created the database Contacts.

Heavy Lifting

Now it is time for the real work. We are going to create a database Movies. We will not finish this database this time because there simply is too much to be done for one part.

We create the menu which allows us to open all our databases. We can then place just one object on our Desktop to access all our databases.

We are going to create ten forms, two reports, and about 30 macros. A lot of work.

In this article we create the database, the main form, the DVD region screen, the help screen, the print screen, the explanation screen, and adjust the list form.

We will create some of the macros.

Make the Database Movies

So let's start. Create the database and add all the fields as shown in Table 1. Save the database as movies.dbf.

Table 1. Fields attributes of the database Movies
Field nameTypeLength
Time Time Fixed
Dvdformat Text 15
Dvdextra Text 15
Numberofdisks Numeric 3
Imdbcode Text 25
Borrowedto Text 70
Storyline Memo Fixed
Region01 Boolean Fixed
Region02 Boolean Fixed
Region03 Boolean Fixed
Region04 Boolean Fixed
Region05 Boolean Fixed
Region06 Boolean Fixed
Region07 Boolean Fixed
Region08 Boolean Fixed
Regionfree Boolean Fixed
Audiomono Boolean Fixed
Audiostereo Boolean Fixed
Audiodolbysurround5.0 Boolean Fixed
Audiodolbydigital5.1 Boolean Fixed
Audiodolbydigitalex Boolean Fixed
Audiodolbydts Boolean Fixed
Audiodolbydtses Boolean Fixed
LanguageNl (Dutch) Boolean Fixed
LanguageEn Boolean Fixed
LanguageFr Boolean Fixed
LanguageGr (Germany) Boolean Fixed
LanguageOther Boolean Fixed
SubtitleNl (Dutch) Boolean Fixed
SubtitleEn Boolean Fixed
SubtitleFr Boolean Fixed
SubtitleGr (Germany) Boolean Fixed
SubtitleOther Boolean Fixed
FormatPal Boolean Fixed
FormatNtsc Boolean Fixed
Format04:03 Boolean Fixed
Format14:09 Boolean Fixed
Format16:09 Boolean Fixed
Actor01 Text 30
Actor02 Text 30
Actor03 Text 30
Actor04 Text 30
Actor05 Text 30
Releaseyear Numeric 4
Cover PicturePlus Ole Disable
Otherinfo Memo Fixed
MovieID Numeric 10 auto enter serial, unique, filled in.


First we make the menu.

We won't need any fields in this database because we are only using it to access other databases. However, Approach doesn't allow a database with no fields (I wonder why :-)) so we add just one field.

We now know how to create a more interesting header. We also create the rectangle on the left side of the screen. We are not going to use this for all our buttons, only the Exit button and maybe the Help button. Let's just see.

Now change the background to our favorite blue and create the rectangle and header like we have done so many times now. Don't forget to set the margins to 0cm. You can delete the worksheet (select the worksheet, press the right mousebutton, click Delete) because we don't need it in this database. Although we use it in a different way, it is still a database.

Let us make the Exit macro.

Now create a new form.

Create a macro with the name Alert and the command View - Alert.

Create a button on the main screen, attach the Alert macro to it, and add the text Exit.

Okay, we have given ourselves a little reminder. I think in this situation it is okay. You can use it more but don't use it too often.

Now let us create some more macros:

Table 2. Required macros
Macro NameTypeHow to create
Contactsopenbrowse to database and select Contacts
Genreopenbrowse to database and select Genre
Dvdformatopenbrowse to database and select Dvdformat
Dvdextraopenbrowse to database and select Dvdextra
Moviesopenbrowse to database and select Movies

All we have to do now is make some decoration in the menu area and create buttons to open the databases.

The Decoration

Draw a rectangle:

Table 3. Menu Decoration - Rectangle
Width 1.0
Top 2.0
Left 1.0
Border ColorMaroon

Now draw a textbox and type MyDatabases but after each letter hit the [Enter] key. It looks like this.

Table 4. Menu Decoration - Textbox
W (cm) H T L Attributes
0.8 9.8 2.1 1.1 Gill Sans 18 point bold. Maroon text. Align Center.

Looks pretty cool. :-)

Let's create the buttons. This time I like to use oval buttons. Create an oval shape.

Table 5. Menu Decoration - Oval Button
Width 3.5
Top 3.0
Left 4.0
Fill ColorLight Yellow (default)
Border ColorOrange
Shadow ColorMaroon

Now copy this button and paste it four times so you have a total of five buttons.

Apply the macros to the corresponding buttons and add a textfield to the button.

Table 6. Menu Decoration - Button Positions
Button Position (cm) Macro settings
  T L  
Genre 5.0 5.5 macro genre
Dvdextra 7.5 5.5 macro dvdextra
Dvdmedia 9.0 5.5 macro dvdmedia
Contacts 3.0 10.0 macro contacts

To make it nicer let us link the buttons movie - genre, genre - dvdextra, dvdextra - dvdmedia with lines. I have used a line of 2pt width and used the same color as the button color.

Table 7. Menu Decoration - Button Lines
Buttons W H T L
Movies - Genre 1.30 1.10 3.96 5.89
Genre - Dvdextra 0.00 1.00 6.00 7.25
Dvdextra - Dvdmedia 0.00 1.00 8.00 7.25

Because we have a shadow under the buttons we should also create a shadow under the lines. Use maroon for this line color.

Table 8. Menu Decoration - Shadow Lines
Buttons W H T L
Movies - Genre 1.13 1.02 3.98 6.02
Genre - Dvdextra 0.00 0.94 6.05 7.30
Dvdextra - Dvdmedia 0.00 0.94 8.05 7.30

Okay let's create some fancy text for the buttons.

Table 9. Menu Decoration - Text
Width 3.0
FontGill Sans 16pt
StyleBottom Text Relief
Text ColorMaroon

When you position the text over the button it looks like the text is carved in the button. Position the text over the button so it's more or less in the centre. It's a bit difficult to exactly position a square over an oval so move the text around until it looks okay.

My text is on

Table 10. Menu Decoration - Text field positions
Text Position (cm)
  T L
Movies 3.15 4.25
Genre 5.14 5.76
Dvdextra 7.16 5.80
Dvdmedia 9.16 5.74
Contacts 3.13 10.26

Create a macro My Menu like we did before. Leave only the Help menu with the About and Help items. Apply this macro on all tabs. Now deselect all the views. Only the main window is left now. Save the file and select View > Browse.

Basically the menu is ready. If required, we can make changes later. You can now test all buttons to see if you can open a database. When finished testing, we continue with the database Movies.

The Movies Forms

As you can see all the fields are present on the main tab. This is a little bit crowded in my opinion. Let's delete some of the fields here. Later we will create new forms for the other fields.

Delete all the fields except Title, Genre, Time, Dvdmedia, Dvdextra, Number of disks, Imdbcode, Film ID, Borrowed To, Storyline, and Cover. Don't worry! Deleting the fields here doesn't remove them from the database.

Okay, that's more like it. Now we can create a few styles. Like I said we will create 11 forms. So maybe it's nice to create a style for this. We simply apply the style to the new form. Now create three styles called Button, Button text and Background.

After you have created and saved your styles select the gray dotted area. Click in the tab styles on background. Now change the page name and the form name to Input. Set all margins to 0cm.

Create the menubar and the header as we did before. Select all fields and adjust the font and font color for data and labels.

Position the fields like this:

Table 11. Forms - Text field positions and types
Field Position (cm) Type Display
  W H T L    
Title 6.0 1.0 3.0 4.0 text Lead capitalized
Genre 6.0 1.0 3.0 12.5 - display as entered
Time 2.5 1.0 4.5 4.0 time HH:MM:SS [1]
Dvdformat 2.5 1.0 4.5 7.5 - display as entered
Dvdextra 2.5 1.0 4.5 12.5 - display as entered
Numberofdisks 2.5 1.0 4.5 16.0 - display as entered
Imdbcode 6.0 1.0 6.0 4.0 text all lowercase
MovieID 2.5 1.0 6.0 12.5 - display as entered
Cover 2.3 3.0 6.5 16.0 - raise fill color transparent [2]
Borrowedto 6.0 1.0 7.5 4.0 - display as entered
Storyline - - - - - [3]
1 This time format is not presently the default but you can adjust it to any format you like. I have selected the time format HH:MM:SS h. With this format I only have to delete the h to get HH:MM:SS.
2 Create a label Cover and put this on top of the pictureplus field.
3 My experience with Approach is that when you type text in a memofield with the format like we use you sometimes don't see the whole letter at the beginning or at the end of a line. What we need is a workaround for this problem. Therefore we are going to create the lines ourselves. See Table 12.

Use the following parameters to draw hairlines:

Table 12. Forms - Connecting hairlines
Type Position (cm) Color
  W H T L  
horizontal 14.5 - 4.0 10.5 darkgray
horizontal 14.45 - 4.05 10.55 black
vertical - 4.0 4.0 10.5 darkgray
vertical - 3.95 4.05 10.55 black
horizontal 14.45 - 4.05 14.47 lightgray
horizontal 14.5 - 4.0 14.5 white
vertical - 3.95 18.47 10.55 lightgray
vertical - 4.0 18.5 10.5 white

Now add a label to the field with the text Storyline.

Table 13. Storyline Label
Width 14.3
Top 10.6
Left 4.1
StyleNone, No Label
Fill ColorTransparent
Border ColorTransparent
Shadow ColorTransparent

Drag the field inside the just created rectangle.

Okay. Our input form is now almost ready, all the fields are in place. :-)

Adjust the Worksheet

Rename the worksheet to List and select our favorite font and colors. All that we have to do now is: Create the forms Dvdregion, Help, Print, and Explanation.

Okay, create four forms and modify them like this.

Dvdregion Screen

On the Dvdregion Screen add this information in a textbox (We also need to add a button Close to it):

Table 15. Dvdregion Textbox
W (cm) H T L Attributes
9.80 5.50 0.50 0.15 Gill Sans 9 point
  1. Playable in all regions. (In this database we use Region Free)
  2. United States of America, Canada
  3. Greenland, Europe, Egypt, Middle East, Lesotho, Swaziland, South Africa, Japan
  4. Southeast Asia, South Korea, Hong Kong, Indonesia, Philippines, Taiwan
  5. Australia, New Zealand, Mexico, Central America, South America
  6. Russia, lands of the former Soviet Union, Eastern Europe, India, Mongolia, Africa
  7. China
  8. (Reserved for future use)
  9. International areas (like airplanes and ships)

Help Screen

On the Help Screen add this information, also use Gill Sans 9 point.

F1 Show this screen
F2 Switch to the input screen
F3 Switch to list view

When you are in the input screen with the New button, you add a new record. When you are in the search screen with the New button, you start a new search of the entire database. Make sure the PATH, DPATH, and LIBPATH statements in the CONFIG.SYS include the path to your favorite browser, otherwise the button Internet will not work.

Print Screen

On the Print Screen add the following information. Use Gill Sans 14 point.

The first part is for Print Card (or List) and the second is for the semicolon. I did it this way so the text would line up better. Otherwise depending on the font you either end up with semicolons that are not lined up, or with text that is difficult to read. Therefore I spilt all the text into blocks.

Table 16. Print Screen Buttons
Button Text W H T L
Print Card 3.50 0.50 1.25 0.50
; (semicolon) 0.50 0.50 1.25 4.00
Print List 3.50 0.50 2.50 0.50
; (semicolon) 0.50 0.50 2.50 4.00

Explanation Screen

On the Explanation Screen enter the following information (use the same settings as the Helpscreen, etc.):

The field Actor1 is meant to be filled with the lead actor, and the field Actor2 is intended for the lead actress. When it's not clear who it is, or there is no lead actor or actress, these fields can be used for other actors or actresses. The remaining Actor fields are used for actors with the other important roles.

That's All for Today

Okay, I think this is more than enough for now. The macros I've promised to make will have to wait until next time. Hope you enjoyed this part also and hope to see you again next time when we continue with our database.

Editing: James Moe

Example database files: databases.zip
Part 1: http://www.os2voice.org/VNL/past_issues/VNL0106H/feature_5.html
Part 2: http://www.os2voice.org/VNL/past_issues/VNL0206H/feature_5.html
Part 3: http://www.os2voice.org/VNL/past_issues/VNL0406H/feature_6.html