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

- Mark Dodel: Our Future
- Roderick Klein: Software Patents: Here we go again. . . - An urgent Message from the VOICE President
- V. Weise: Spell check in EPM with easily changeable dictionary files
- Andreas Peters: DBox2 - Video Streaming on OS/2 and eCS
- Robert Henschel: The ThinkPad On Screen Display Utility
- Peter Brown: Adventures in AMD 64 Land
- Arthur van Egmond: How to create a database using Lotus Smartsuite - Part Three
- 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 Three

by Arthur van Egmond, © April 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 on our tour on how to create a database using Lotus Smartsuite. If you did your homework you now have three databases: Genre, Dvdextra, and Dvdformat. This issue we are going to create the database Contacts.

As I have explained before, we are going to use this database to store our contacts. We then use this information in our database Movies to keep track of where our movies are located and who has them.

## Contacts Database Fields

Create a new database with the name Contacts. Add the following fields to the database:

Table 1. Field definitions for the Contacts database
Field | Type | Length | Options
--- | --- | --- | ---
Firstname | text | 15 | -
Middlename | text | 30 | -
Nameaffix | text | 10 | -
Familyname | text | 30 | -
Streetname | text | 50 | -
Housenumber | text | 05 | -
Zipcode | text | 10 | -
City | text | 30 | -
State | text | 05 | -
Land | text | 30 | -
Dateofbirth | date | fixed | -
Telephonenumber | text | 15 | -
Celphonenumber | text | 15 | -
Faxnumber | text | 15 | -
Emailadress1 | text | 30 | -
Emailadress2 | text | 30 | -
Homepage | text | 30 | -
Picture | photoplus | fixed | OLE Disable
Combined | text | 70 | -
ContactID | numeric | 10 | autoenter serial, unique, filled in

Most of the field names are self-explanatory.

The field Middlename stores most name parts except the first name. If you like, you could use initials instead.

The field Nameaffix accepts things like von, van de, etc. I left most of the fields as text because, e.g., the Housenumber could be "1A." If you use numeric for the Housenumber, there is no way you can type the `A`.

The field Combined is the difficult one in this database.

- On the tab Default value select Create Formula
- Click on Formula
- Under Functions doubleclick on Combined
- Under Fields doubleclick on Firstname, Nameaffix, and Familyname

As you can see, the function is not yet complete. The OK button is disabled and the flag has a red cross. After Firstname and Nameaffix we add Chr(32);. The whole string looks like this:

Combine(Firstname; Chr(32); Nameaffix; Chr(32); Familyname)

Now we can press the OK button and our formula is done.

After you have added the fields, set the background to the default color.

Make the header and the menu bar. You have to move some of the fields to another location to make room for the header and the menu bar.

You can also create the styles for the button and the button text.

Select all fields to adjust the Font and Font Color style for text and labels en masse.

## Position the Fields on the Form

Table 2. Entry Field positions
Field | Position (cm) | Display type
--- | --- | ---
| W | H | T | L |
Firstname | 5.0 | 1.0 | 3.0 | 7.0 | text, First Capitalized
Middlename | 5.0 | 3.0 | 12.0 | 12.5 | text, First Capitalized
Nameaffix | 2.5 | 1.0 | 4.25 | 7.0 | text, all lowercase
Familyname | 5.0 | 1.0 | 4.25 | 12.5 | text, First Capitalized
Streetname | 5.0 | 1.0 | 5.5 | 7.0 | text, First Capitalized
Housenumber | 2.5 | 1.0 | 5.5 | 12.5 | display as entered
Zipcode | 2.5 | 1.0 | 6.75 | 7.0 | display as entered
City | 5.0 | 1.0 | 6.75 | 12.5 | text, First Capitalized
State | 2.5 | 1.0 | 8.0 | 7.0 | text, ALL UPPERCASE
Land | 5.0 | 1.0 | 8.0 | 12.5 | text, First Capitalized
Dateofbirth | 2.5 | 1.0 | 9.25 | 4.0 | DD-MM-YYYY [1]
Telephonenumber | 5.0 | 1.0 | 9.25 | 7.0 | numeric telephone [2]
Celphonenumber | 5.0 | 1.0 | 9.25 | 12.5 | numeric telephone [2]
Faxnumber | 5.0 | 1.0 | 10.5 | 7.0 | numeric telephone [2]
Emailadress1 | 5.0 | 1.0 | 11.75 | 7.0 | text, all lowercase
Emailadress2 | 5.0 | 1.0 | 11.75 | 12.5 | text, all lowercase
Homepage | 5.0 | 1.0 | 10.5 | 12.5 | text, all lowercase
Picture | 2.5 | 3.0 | 3.5 | 4.0 | [3]
Combined | 10.5 | 1.0 | 13.0 | 7.0 | display as entered
ContactID | 2.5 | 1.0 | 8.0 | 4.0 | display as entered

1 Select the format DD/MM/YY and change this to DD-MM-YYYY.
2 Select Allow Alphanumeric.
3 If Picture is too large, shrink it. If Picture is too small, stretch it.

Create a label above Picture. Enter Photo:

Table 3. Photo label position
W (cm) | H | T | L
--- | --- | --- | ---
2.2 | 0.5 | 3.0 | 4.0

Remember: Save your Approach file every now and then. You don't want to lose your work.

## Help, Find, and Print

Now you can adjust the worksheet like you did before in the other databases. Now that the base is finished, we continue with:

- Helpscreen
- Findscreen
- Printscreen #1
- Printscreen #2

### But First, Action Buttons

Before we continue let us create the buttons and macros for the buttons including the button text.

The extra buttons in the menu are Show All, Find, and Print:

Table 4. Button positions and associated macros
Button Name | Position (cm) | Macro settings
--- | --- | ---
| W | H | T | L |
Show All | 2.5 | 1.0 | 6.5 | 0.36 | macro `Show All`, "Find all records"
Find | 2.5 | 1.0 | 7.0 | 0.36 | macro `View`, "Switch to Find"
Print | 2.5 | 1.0 | 7.5 | 0.36 | macro `View`, "Switch to Print"

There are also some extra macros:

Table 5. Extra macro definitions
Name | Command
--- | ---
`Printcard` | view switch to Printcard / printpreview / Zoom to actual size
`Printlist` | view switch to Printlist / printpreview / Zoom to actual size
`Print1` | print
`New1` | view switch to Find / find all / find and pause for input

Did you notice anything? There is no Find or Print form to switch to, and the Printcard and Printlist are missing also. As a result, the macros cannot be finished. Maybe you skipped them or did something else. Later you can just create or modify them.

### Helpscreen

We already know how create a Help screen, so create one like you did before. Don't forget that you need a button (and a macro) to close the screen later. Add the following extra text to the help screen.

When you press Print, you have the option to print a list or a card.
When you press Find, you can search the database.
When in Search mode, you press New for a new search, Close to return to the Main screen.

### Findscreen

For now let us create the Find form. Choose Form > New Form, add all the fields, the menu, etc., exactly the same way as on the Main form. Too much work you say? Okay, I agree `:-)`.

So choose Form > Duplicate Form instead. And there you have it: An exact copy. That was fast, wasn't it? All you have to do now is rename the form to Find and rename the header to Find.

On the Contacts form adjust or create the `Find` macro and apply it to the Find button. In the Find form we can delete the Find and Show all buttons. In the Find form we have to exchange the `New` macro on the New button for the `New1` macro.

- Create a new form Print
- You can now apply the `Print` macro on the Main form and on the Find form. Adjust background, etc.
- Select Show form as dialog
- Create a rectangle (same as the Help form):

  Table 6. Rectangle attributes
  W (cm) | H | T | L
  --- | --- | --- | ---
  10.0 | 7.0 | 0.0 | 0.11

- Label the header Print screen

Now for a critical decision. Do we create the same button thrice (Printlist / Printcard / Close), or one button and two different ones. Let's do the last.

Table 7. Close Button position and macro
Button | Position (cm) | Macro settings
--- | --- | ---
| W | H | T | L |
Close | 2.5 | 0.5 | 6.0 | 0.25 | macro `close1`

Add two text fields to the form.

Table 8. Printlist and Printcard text positions
Text field | Position (cm)
--- | ---
| W | H | T | L
Print a card | 3.0 | 0.5 | 1.0 | 0.25
Print a list | 3.0 | 0.5 | 2.0 | 0.25

Create a text field; position as shown below in Text Field 1.

Copy Text Field 1; position it as Text Field 2.

Table 9. Text field 1 and 2 positions
Text field | Position (cm)
--- | ---
| W | H | T | L
Text Field 1 | 0.5 | 0.5 | 1.0 | 3.25
Text Field 2 | 0.5 | 0.5 | 2.0 | 3.25

Draw a Rounded Square:

Table 10. Rounded Square sttributes
Attribute | Value
--- | ---
Width | 0.5
Height | 0.5
Top | 1.0
Left | 4.0
Style | Rise
Fill Color | (same as background color)
Border Color | Maroon
Border Width | 0.5pt

Now copy that square and set its position:

Table 11. The other square's position
Attribute | Value
--- | ---
Width | 0.45
Height | 0.45
Top | 2.0
Left | 4.0

### Reports

At this point, we have the Main, List, Help, Print, and Find screens. All that remains to do now is create two reports and a customized menu.

- Select Create > Report.
- Type Printlist.
- Click OK.

Repeat the same for Printcard.

Select the Print tab and apply the macros `Printlist` and `Printcard` appropriately.

#### Prinstscreen #1 - Printlist Report

Select the Printlist report.

Left-click in the white dotted area and select Add header. In the header we create two more buttons (same as in the Print form):

- A Close button (macro `Close1`)
- A Print button (macro `Print1`)

In the header we also place Print List but first set the page to A4 Landscape [Letter Landscape in USA] via File > Page setup. Set the margins as shown below. You may have to change these settings for your printer.

Table 12. Report Page Printer Margins
| Left | Right | Top | Bottom
--- | --- | --- | --- | ---
Margins (cm) | 0.5 | 0.5 | 0.5 | 1.0

Now create a text field in the header with the text My Contacts:

Table 13. "My Contacts" attributes
Attribute | Value
--- | ---
Width (cm) | 5.0
Height | 1.0
Top | 1.0
Left | 11.0
Font | Gill Sans 24, Bold, center justify
Style | (no style)
Fill Color | (none)
Border Color | (none)
Border Width | Hairline

Draw a horizontal line:

Table 14. Line attributes
W (cm) | H | T | L
--- | --- | --- | ---
26.0 | 2.0 pt | 12.25 | 1.1

On the left side of the header:

1. Create two text fields Return to main screen and Print this form.

   Table 15. Return and Print positions
   | W | H | T | L
   --- | --- | --- | --- | ---
   Return position (cm) | 4.0 | 0.5 | 1.0 | 1.0
   Print position (cm) | 4.0 | 0.5 | 1.5 | 1.0

2. Create two more text fields.

   Table 16. Text fields 1 and 2 positions
   | W | H | T | L
   --- | --- | --- | --- | ---
   Position 1 (cm) | 0.5 | 0.5 | 1.0 | 4.5
   Position 2 (cm) | 0.5 | 0.5 | 1.5 | 4.5

3. Create two buttons. Same as on Print form, only now with white fill color.
4. Apply the macros `Contacts` and `Print1` to these buttons.
5. Now on the tab Basic select Non printing and Show in Print Preview for all text (except the header) and the buttons.

On the right side of the header:

1. Add a Current Date field. And the page number.

   Table 17. Current Date position
   | W | H | T | L
   --- | --- | --- | --- | ---
   Position (cm) | 2.5 | 0.5 | 1.0 | 24.5

2. Before the Current Date, add the text Printed on and a text field at:

   Table 18. Text field position
   | W | H | T | L
   --- | --- | --- | --- | ---
   Position (cm) | 2.0 | 0.5 | 1.0 | 22.0

3. Before the page number, add the text Page number and a text field:

   Table 19. Text field position
   | W | H | T | L
   --- | --- | --- | --- | ---
   Position (cm) | 0.5 | 0.5 | 1.0 | 24.0

4. Set the page number field to Right Justification.

#### Populate the Form

Okay, we can add some fields from the database table. We can't add all fields, this would be too much.

Before you add any fields, select Reports > Turn of columns. A little trick I use is that I change the fill color to light gray; now it's easy to see where your field is. Further, use the standard font and size. Set the fields to Read-only on the Basic tab. This is a print form, not an add form. Deselect the enclosed field label and set the color to Transparent.

Table 20. Field positions for Printlist
Field | Position (cm)
--- | ---
| W | H | T | L
Firstname | 5.0 | 0.5 | 2.35 | 1.1
Nameaffix | 2.5 | 0.5 | 2.35 | 6.2
Familyname | 5.0 | 0.5 | 2.35 | 8.8
Streetname | 5.0 | 0.5 | 2.35 | 13.9
Housenumber | 2.5 | 0.5 | 2.35 | 19.0
Telephonenumber | 5.0 | 0.5 | 2.35 | 21.6

Actually, I intended to add City as well but since there is no more room, we leave it like this.

#### Printscreen #2 - Printcard Report

Now you can see why I also make a card for printing. On this card, we can place all fields.

Select the Printcard report. Remember to set the paper size to A4 [Letter in USA].

Draw a line of 1 pt Height, position the Top to 13.5cm. If it's not possible to draw a line, click on the tab body and a rectangle will appear. Drag the bottom line down. 13.25cm is about half the paper. Drag the body so it lines up with the line. Now it does not show, but when you add two records to the database you see that Approach prints two records on one page.

You have the option to add field labels to your report. I don't find it fancy to add the field labels therefore I don't use them. But if you like, you can activate them on the Font tab. If you use the field label, remember that the height of the field should be 1cm.

Remember: Set all fields on this form to Read-only.

Add all these fields to the card:

Table 21. Field positions on Printcard
Field | Position (cm)
--- | ---
| W | H | T | L
Picture | 2.2 | 3.0 | 1.5 | 1.0
Firstname | 5.0 | 0.5 | 5.5 | 1.0
Middlename | 5.0 | 0.5 | 5.5 | 6.5
Nameaffix | 2.5 | 0.5 | 5.5 | 12.0
Familyname | 5.0 | 0.5 | 5.5 | 15.0
Streetname | 5.0 | 0.5 | 7.0 | 1.0
Housenumber | 2.5 | 0.5 | 7.0 | 6.5
Zipcode | 2.5 | 0.5 | 7.0 | 9.5
City | 5.0 | 0.5 | 7.0 | 12.5
State | 2.5 | 0.5 | 8.5 | 1.0
Land | 5.0 | 0.5 | 8.5 | 4.0
Dateofbirth | 2.5 | 0.5 | 1.5 | 4.0
Telephonenumber | 5.0 | 0.5 | 10.0 | 1.0
Celphonenumber | 5.0 | 0.5 | 10.0 | 6.5
Faxnumber | 5.0 | 0.5 | 10.0 | 12.0
Emailadress1 | 5.0 | 0.5 | 11.0 | 1.0
Emailadress2 | 5.0 | 0.5 | 11.0 | 6.5
Homepage | 5.0 | 0.5 | 11.0 | 12.0

The fields ContactID and Combined were left off the report. The first is a unique record number, the second is used in our database Movies and doesn't add extra value to our report since the three names are already on the report.

As you can see, there still is some empty space on the right side. We need to add buttons to go back and to actually print the record. Since there is no header, we can put them in the body. Remember to select Non-printing and Show in Print Preview. Also add the current date with the phrase Printed on: preceding it. For this report, we don't use a page number.

Our reports are finished.

## Finishing Up

Now finally let us create a macro for the menu.

Again, copy the short menu and adjust it like you did before, leaving only the Help with About Approach and Help. Don't close the menu.

It would be nice if we could import and export a photo to the database. Why did we create the photo field if we couldn't import any photo into it in the first place?

- Click above the left pane on Add menu. This adds a standard menu
- On the right side, type &Photo
- Above the right pane, click on Add Item
- Select Import picture from the list
- Click on Add Item
- Select Export picture from the list
- Click OK, Done, OK, Done
- Apply the `My Menu` macro to all tabs
- Deselect all View options
- Save the Approach file
- Select View > Browse

Okay, you can sit back and relax now.

Next time we will start with the Movies database and we will create the menu. Hope to see you next time.

Formatting: Christian Hennecke
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