Jump to article
V.O.I.C.E Virtual OS/2 International Consumer Education
June 2006
A A A
Feature articles

- Mark Dodel: And the Os/2car goes to...
- Ed Durrant: Mobile Broadband under OS/2 in Australia - Using an LG 8330 3G phone with OS/2 and eCS
- Lon Hooker: USB Removables - Care and Feeding FAQs
- Peter Koller: Maul Publisher goes Unicode
- Mark Dodel: OS/2 and Wireless "g"
- Arthur van Egmond: How to create a Database using Lotus Smartsuite
- Tips
- Letters, Addenda, Errata

Active GUI element

Static GUI element

Code

WPS object

File/Path

Command line

Entry-field content

`[Key combination]`

more

# 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 name | Type | Length
--- | --- | ---
Title | Text | 50
Genre | Text | 15
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.

### Menu

First we make the menu.

- Select the File menu
- Select New database
- Click OK
- Give this database the name menu.dbf
- Click OK

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.

- Name the field Menu
- Set the size to 1
- Click OK
- Now click Design and get rid of the ugly header and the 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.

- Create a macro with the name Sure! and the command Exit
- Create a macro with the name Nope! and the command View - Menu
- Create a macro with the name Exit

Now create a new form.

- Name this form Alert
- Make it like we have made the Help form. This time title it Alert screen
- Add the following text to this screen:
  Alert! You are about to exit Approach
  Have you saved all your work?
  Perhaps you have left another database open?

  Put two buttons on the alert screen, one for `Sure!` and one for `Nope!`. Just for fun, make the text on the `Sure!` button green and the text on the `Nope!` button red. `:-)`

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 Name | Type | How to create
--- | --- | ---
Contacts | open | browse to database and select Contacts
Genre | open | browse to database and select Genre
Dvdformat | open | browse to database and select Dvdformat
Dvdextra | open | browse to database and select Dvdextra
Movies | open | browse 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
Attribute | Value
--- | ---
Width | 1.0
Height | 10.0
Top | 2.0
Left | 1.0
Style | Sunk
Background | Transparent
Border Color | Maroon

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

    M
    y
    D
    a
    t
    a
    b
    a
    s
    e
    s

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
Attribute | Value
--- | ---
Width | 3.5
Height | 1.0
Top | 3.0
Left | 4.0
Style | Rise
Fill Color | Light Yellow (default)
Border Color | Orange
Shadow Color | Maroon

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
Attribute | Value
--- | ---
Width | 3.0
Height | 0.7
Font | Gill Sans 16pt
Style | Bottom Text Relief
Text Color | Maroon

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
Attribute | Value
--- | ---
Width | 14.3
Height | 3.8
Top | 10.6
Left | 4.1
Style | None, No Label
Fill Color | Transparent
Border Color | Transparent
Shadow Color | Transparent

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.

- Adjust the page name to the form name
- Set all margins to 0 cm
- Set all forms to Show Form as Dialog
- On each form we create a rectangle:

  Table 14. Form Rectangle Dimensions
  W | H | T | L
  --- | --- | --- | ---
  10.0 | 7.0 | 0.0 | 0.11

  Use copy and paste to copy the rectangle over all forms.
- Create a header for each form with the title of the form just as we did so many times now: Dvdregionscreen, Helpscreen, Printscreen, Explanationscreen
- Enter a ½ point line under the title.

#### 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

##### References

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