Jump to article
< >

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 5

by Arthur van Egmond, © July 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. Today we continue with our main database Movies.

The last time we created the input form. We adjusted the list form and we created the forms Dvdregion, Help, Print, and Explanation. There was no more time to create macros, etc. We will see if we can do this today.

Anyway, this time we are going to create at least the following forms:

Sort to Order

Let's start with the Sort form. The quickest way to create the Sort form is to copy the Print form that we created last time. We will be using the same font size for our text. So make a copy of the Print form and rename this to Sort. Adjust the header on this screen to Sort screen.

Now we have to ask ourselves the question On which fields do we want to sort our database? We can set a default sort so when the database is opened it is automatically sorted by this field. We should have the option to sort the database at least by these fields:

Although it is possible to sort by every field, you should ask yourself if that is really necessary. Why do you sort your data in a certain way? To access information very quickly. What are the most common things you want to know? I think the fields mentioned above.

Let's add text fields. We can first adjust the present text fields and add two more. Create these fields:

Table 1. Positions for sort fields
Sort by field W H T L
Title 4.25cm 0.75cm 1.25cm 0.25cm
Genre 4.25cm 0.75cm 2.00cm 0.25cm
Release year 4.25cm 0.75cm 2.75cm 0.25cm
Movie ID 4.25cm 0.75cm 3.50cm 0.25cm

For now we are finished with this form.

In Search of . . .

Now we can create the Search form. This is a duplicate of the Input form but before we duplicate the Input form, it's wise to create buttons and macros first. Otherwise we would have to add them twice, and I admit that I'm a little bit lazy. :-)

Create the following macros:

Table 2. Required macros
Macro Key Command
First - record - goto first record
Last - record - goto last record
Previous - record - previous record
Next - record - next record
New - record - create a new record
Delete - delete - delete current record
Show All - find - find all
Find - find - goto find and wait for input
Print - view - print
Help F1 view - help
Close - close
Dvdregion - view - dvdregion
Sort - view - sort
Explanation - view - explanation
List F3 view - list
Input F2 view - input

Create the buttons for First, Last, Previous, Next, New, Delete, Show All, Find, Print, Help, and Close. Look for the position and size in the contact database, apply the macros and add text to the buttons. Remember to use your styles as this saves you time. When you have finished the buttons, you can copy the Input form. Change the header to Find and change the form and pagename to find.

Actors and Extra Info

Now we are going to do the Actors form. I save the Extra Info form for the last because it takes quite some time to organize.

Create two new forms: Actors and Extra info. Set the form to show this form as dialog.

On the Actors form and the Extra info form, change the background, pagename and margins. Create a rectangle with the parameters below on both forms. Use the same color for the fill color as the background color.

Table 3. Actor and Extra Info forms, rectangle position
Parameter Value
Width 20.0cm
Height 7.00cm
Top 0.00cm
Left 0.11cm

Draw a horizontal line with the following parameters:

Table 4. Actors form, parameters for horizontal line
Parameter Value
Width 20.0cm
Top 1.00cm
Left 0.11cm
Line width 0.50 point
Color yellow (button color)

Draw a text box with the following parameters and the text Actors for the movie:

Table 5. Actors form, text box parameters
Parameter Value
Width 4.00cm
Height 0.50cm
Top 0.25cm
Left 0.25cm
Color green
Fillcolor none
Border none
Borderline none

Now draw a box:

Table 6. Actors form, box parameters
Parameter Value
Width 6.00cm
Height 0.75cm
Top 0.15cm
Left 0.50cm
Color yellow (button color)
Fillcolor none
Borderline hairline

Put the field title inside this box. Make the field title Read-Only in this form. Now add the following fields:

Table 7. Actors form, field positions
Field W H T L Layout
Actor01 5.00cm 0.50cm 1.25cm 5.00cm same as other fields
Actor02 5.00cm 0.50cm 2.00cm 5.00cm same as other fields
Actor03 5.00cm 0.50cm 2.75cm 5.00cm same as other fields
Actor04 5.00cm 0.50cm 3.50cm 5.00cm same as other fields
Actor05 5.00cm 0.50cm 4.25cm 5.00cm same as other fields
Release Year 2.50cm 0.50cm 5.00cm 5.00cm same as other fields

In front of these fields draw a text box with the text Actor 01, Actor 02, Actor 03, Actor 04, Actor 05, and Release Year. Use yellow as font color like it is the label of the field.

Draw the following lines with a width of 0.5 point and yellow color (button color):

Table 8. Actors form, line positions
Type W H T L
vertical - 6.05cm 1.00cm 10.0cm
horizontal 9.89cm - 1.86cm 0.11cm
horizontal 9.89cm - 2.62cm 0.11cm
horizontal 9.89cm - 3.73cm 0.11cm
horizontal 9.89cm - 4.12cm 0.11cm
horizontal 9.89cm - 4.87cm 0.11cm
horizontal 9.89cm - 5.61cm 0.11cm

Draw a text box Other info with the following parameters:

Table 9. Actors form, Other info text box parameters
Parameter Value
Width 3.50cm
Height 0.50cm
Top 1.25cm
Left 10.5cm

Now as we did with the Storyline on the first page we need to create a box for the Other Info field.

Draw the following hairlines:

Table 10. Actors form, hairline parameters
Color Type W T L
darkgray horizontal 9.25cm 1.75cm 10.5cm
black horizontal 9.24cm 1.77cm 10.51cm
darkgray vertical 5.00cm 10.5cm 1.75cm
black vertical 4.99cm 10.51cm 1.77cm
white horizontal 9.25cm 6.75cm 10.5cm
lightgray horizonal 9.24cm 6.73cm 10.51cm
white vertical 5.00cm 1.75cm 19.75cm
lightgray vertical 4.99cm 1.77cm 19.72cm

Finally create the Other info field:

Table 11. Actors form, info field parameters
Parameter Value
Width 9.17cm
Height 4.93cm
Top 1.78cm
Left 10.54cm
fill color none
border color none
borderline none

Create two buttons:

Table 12. Actors form, button parameters
Button W H T L Macro
Close 2.50cm 0.50cm 6.00cm 0.25cm create macro close1:
view - switch to input
Explanation 2.50cm 0.50cm 6.00cm 3.00cm (use macro already created)

Now on the input screen do the following:

Now, the button is part of the input form and not part of the menu.

On the Explanation form, add a button Close (create a macro Close2 with the command view - switch to actors).

Testing Actors

Now save the Approach file and test the buttons.

Click on Browse. In the input screen, click on Actors. If correct, you get the new screen you just created. Now click on the Explanation button. As you can see the Actors form disappears and the Explanation form appears. Click on the Close button. When correct the Actor form appears again while the Explanation form disappears. Click on the Close button to return on the Input form.

Extra Info, Cont.

Now we can do the Extra Info form.

You can copy and paste the horizontal line (see Table 4) from the Actors form to the Extra info form. Also copy and paste the text field Actors for the movie. Change the text to Extra info for the movie and position it at the same location. Copy and paste the box around the Title field as well as the title to the Extra Info form. Our header is now finished. We can now add the fields and labels on this form.

We need to add all the region, audio, language, subtitle, and format fields. Therefore, we need five squares to divide the screen into equal portions.

Draw four vertical yellow (button color) hairlines:

Table 14. Extra info form, hairline parameters
H T L
5.00cm 1.00cm 4.00cm
5.00cm 1.00cm 8.00cm
5.00cm 1.00cm 12.0cm
5.00cm 1.00cm 16.0cm

Now you can copy and paste the horizontal line at a top offset of 1.50cm and 6.00cm. This is the layout we will be working with.

Create text boxes with a yellow font color and alignment set to center:

Table 15. Extra Info form, text box parameters
Box W H T L
Dvd Region 3.89cm 0.50cm 1.50cm 0.11cm
Audio 4.00cm 0.50cm 1.50cm 4.00cm
Language 4.00cm 0.50cm 1.50cm 8.00cm
Subtitle 4.00cm 0.50cm 1.50cm 12.0cm
Format 4.00cm 0.50cm 1.50cm 16.0cm

Now create text boxes with a yellow font color and left alignment, and insert a space character in front of the text. The semicolon and the text are two different text boxes.

Table 16. Extra info form, text box parameters
Field W H L T
Region 01 3.00cm 0.50cm 0.11cm 1.50cm
; (semicolon) 0.50cm 0.50cm 3.00cm 1.50cm
Region 02 3.00cm 0.50cm 0.11cm 2.50cm
; 0.50cm 0.50cm 3.00cm 2.00cm
Region 03 3.00cm 0.50cm 0.11cm 2.50cm
; 0.50cm 0.50cm 3.00cm 2.50cm
Region 04 3.00cm 0.50cm 0.11cm 3.00cm
; 0.50cm 0.50cm 3.00cm 3.00cm
Region 05 3.00cm 0.50cm 0.11cm 3.50cm
; 0.50cm 0.50cm 3.00cm 3.50cm
Region 06 3.00cm 0.50cm 0.11cm 4.00cm
; 0.50cm 0.50cm 3.00cm 4.00cm
Region 07 3.00cm 0.50cm 0.11cm 4.50cm
; 0.50cm 0.50cm 3.00cm 4.50cm
Region 08 3.00cm 0.50cm 0.11cm 5.00cm
; 0.50cm 0.50cm 3.00cm 5.00cm
Region free 3.00cm 0.50cm 0.11cm 5.50cm
; 0.50cm 0.50cm 3.00cm 5.50cm

I think you get the idea. Now insert text labels for Audio, Language, Subtitle, and Format (look at the fieldnames if you don't remember what to call them :-)) at the appropriate places. Start at left offsets of 4.00cm, 8.00cm, 12.0cm, and 16.0cm.

Now that all the field labels are in place let's add the fields. Drag the fields Region01 through Regionfree somewhere on the field. It is best to put them under each other, Region01 in top and Regionfree at the bottom.

On the Basic tab of the properties box, change the data entry to checkboxes, set the checked value to Yes and click OK. Set the font to Gill Sans 10. (Because it's a checkbox you cannot change the background and font color. Well, you can set it to any color but the background remains white and the fontcolor black.) Change the width and height to 0.50cm. Use a sunken relief. Position them at a left offset of 3.50cm, and top offsets of 1.50cm, 2.00cm, etc.

Do the same for all other fields.

Now we are going to do something neat. We are going to be sure that only one of PAL (Europe) or NTSC can be selected. Why? Well, in my opinion a movie can only be one of the two. I have never seen a DVD or videotape or whatever that has the two standards on one media. To do this we need to create two macros.

Table 17. Extra Info form, macro parameters
Name Command
PalNtsc movies.formatpal = not movies.formatntsc
NtscPal movies.formatntsc = not movies.formatpal

When you name the macro and choose the command set click on formula.

Do the same for the other macro, only then use formatntsc as the first field.

Select the field FormatPal and apply the macro PalNtsc. Then select the field FormatNtsc and apply the macro NtscPal.

Create two buttons:

Table 18. Extra info form, button parameters
Name W H T L Macro
Close 2.50cm 0.50cm 6.25cm 0.25cm Close1
Dvdregion 2.50cm 0.50cm 6.25cm 3.00cm Dvdregion

On the tab Dvdregion create a button:

Table 19. Dvdregion tab, button parameters
Name W H T L Macro
Close 2.50cm 0.50cm 0.25cm 6.00cm Close3, create new as view - switch to Extra Info

On the Input form create a button like the Actors button:

Table 20. Input form, button parameters
Name W H T L Macro
Extra Info 2.50cm 0.50cm 8.50cm 12.5cm Extra Info, create as view - switch to extra info

Save the approach file and test these buttons as well.

Whew!

For now we have done enough.

Next time we will finish 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
Part 4: http://www.os2voice.org/VNL/past_issues/VNL0606H/feature_5.html