What to do with a Database?

More recently, I used some of the simple databases in the line work, starting with FileMaker and then Access. While FileMaker was okay, the move to Access left all the employees wondering "WHY?", it was terribly to use and did absolutely nothing to turn me on to databases.

One day, I had some free time on my hands and started playing around with some of these lesser used applications. To my wonder, I found a few useful applications for a database, something that might even be of help to the normal OS/2 user!

I should note, of course, that some of these are more like light-weight databases or even database front-ends rather than the serious, dedicated engines like DB2, Oracle or some of the "free" ones like MySQL or mSQL. (You can see an old comparison of databases in OS/2 e-Zine). However, since the ones I'm going to talk about don't actually need a separate database engine, I think they do qualify as a database, though you certainly wouldn't be storing terabytes of data on them.

IBM Works

Don't be scared! Create a new one. As I've discovered, it's dead easy. First thing you'll see is a dialog to create the fields.

[click on image to get larger version]
This is just a list of the important information you might want to record. For example, if you're using it for a contact list (you can do that!), you might want the person's name, address and phone number. Let's use this example for now, so I can explain the options, in case it's not clear. A "Field" is simply the variable or the item that you want to store.

• Field Name: This is just what you want to call this field. For example "Address".
• Field Type: What information type is it? For example, a phone-number field would be "Numeric", the address might be "Text".
• Field Width: This is how long the field should be. For example, you might want to limit a phone number to 7 digits.
• Display Width: This is separate from the field width, this is simply how much is shown on screen. The field width can be longer, of course.
• Default Value: This is the starting value when you start a new record. Taking the phone number example again, you might like to default it to "555-5555" so that someone can see the format that is expected.
• Field Conditions: This allows you to check the field to make sure it is correct. For example, a US postal code is 5 numbers, a Canadian postal code is 6 alphanumerics. You might check the country listed in the address and make sure that the postal code is a valid one.

Anyways, just play around for a few minutes, and you'll get the idea. Once you have entered all the requisite information, just click "Add". Keep doing that until you have entered all the pieces of information you might like to store.

When you're done that, click on "Save" which will take you to an almost blank screen. Now comes the fun part. Click on the screen and IBM Works will plunk down all your fields, including labels. This is just like drawing now, just drag and move the fields anywhere on the screen to come up with a pleasing layout to you. You can even add colours and change the font.

Now what's more important is, what can I do with a database? Here's what I came up with.

[click on image to get larger version]
Yup, a nice little database to keep a track of all my registered shareware. (I have a tendency to misplace the registration codes unless I keep in all in one place). If you want to view a short summary of the information, you can click on "Report-New" which will generate a summary of the information in your database. You can decide which information to include and how to show it. In the case of database for a contact list, you could request to show short list with 3 columns, Name, Address and Phone Number, even though the actual database contains much more information on each person (e.g. birthday, cell-phone, ICQ, etc).

Admittedly, IBM Works is a lightweight, and if you're keeping more data and need to perform searches on hundreds of entries, you'll want something more heftier. By the way, if you're interested in this database, let me know and I can e-mail it to you or put it up on-line. Without my registration codes, of course!

DBExpert

Instead, I take a look at Lotus Approach, which will accomplish pretty much the same thing, with much more hefty use of resources. On Sundial Systems web site, you'll find a link to an actual application for DBExpert, a database for running a veterinary office. It's free for your use, and includes a 1200 item inventory of things typically used in the office.

Lotus Approach

The version I'm using is from Smartsuite 1.7.2, but to be honest, I've never had a problem with any of the previous revisions.

Approach, like DBExpert, is sort of a front end for bigger database engines, if you wish, but it will also function standalone. For a small home or business user, it will be just fine, so don't worry if you don't have DB2 installed. Just don't go about doing this sort of thing if you're running a global insurance company, for instance.

I'm not going to go into the details of using Approach, that's a tutorial for another day. Suffice to say, I was pleasantly surprised by how easy and friendly it was compared to Microsoft Access.

[click on image to get larger version]
The idea behind the design of your database is the same. Just specify the fields and literally draw what you want your interface to look like. Usually, no programming is required. So what did I come up with on Approach?

[click on image to get larger version]
What I created was an electronic catalogue for my library of research papers. IBM Works could not be used for this as I have hundreds of papers, and it doesn't have a nice search function. With Approach, it's as easy as clicking the "Find" button. Then enter the search criteria in the relevant fields and have it go. Easy!

For this one, I admit there was some slight programming (which I culled from the 'net), but only on the button that says "Load PDF Document", as I had to have Approach call up Acrobat Reader and load up the appropriate file.

I showed this to my friend and colleague (a Windows user), and he was amazed when I told him how easy it was. (Not including the code for the button, and entering the data, about 5 minutes). He wanted a copy, but of course, I told him he would have to run Smartsuite. But you, loyal OS/2 user, can have it free for the asking! If there's enough interest, I'll put it on-line for a free download.

Other uses

But if you need to make your own, don't be afraid. There is no programming involved and it's very simple. The only hard part comes when you have to enter all your data.

Do you have other applications that you use a database for? How about applications that you might like to see? Please share it with us!