Active GUI element

Static GUI element

Code

WPS object

File/Path

Command line

Entry-field content

[Key combination]

more

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:

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:

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

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

Create a label above Picture. Enter Photo:

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:

There are also some extra macros:

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.

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.

Add two text fields to the form.

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

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

Draw a Rounded Square:

Now copy that square and set its position:

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.

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

Draw a horizontal line:

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.

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.

Add all these fields to the card:

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.