If you highlight an item in the list and press the Enter key, you'll see all the information in the item. The User's Manual calls this the Data Item view.
If you can answer all of the questions you already know everything about "data-basics". You can skip this article and wade into the article about using the Subset Language in the HP Palmtop's database engine (page 21).
A Baker's Dozen of Questions
1. What are the HP Palmtop's "database" applications?
2. What are the two "views" in each database application?
3. What additional "view" does the HP 200LX have that the HP 100LX does not? What's this new "view" good for?
4. How many pages can a database application have? What key(s) do you press to get to a different page?
5. Where can you find dozens of sample databases for the HP Palmtop?
6. How do you start to create a database and where do you go after you get started?
7. What are some guidelines for creating a custom database?
8. What are the various types of fields you can have in a database?
9. What are the limits to the various types of fields in a database and to a database file itself.
10. What are a couple of ways to get large amounts of text into a Note field?
11. What's a subset and how do you create subsets?
12. What's a "smart-clip" and what is this feature good for?
13. Where's the "report generator" for the Palmtop's database programs?
The Answers: Not Necessarily in the Order of the Questions
This article uses a custom database file as a medium for describing some of the tips, traps and techniques involved with using and creating databases on the HP Palmtops. Even though the topics in the article will emphasize the Database application, they will apply to the Palmtop's Phonebook, NoteTaker and WorldTime databases as well. These four database applications are what I call "feel-alike" instances of the Palmtop's database engine. The fifth instance of the database engine, the Appointment Book, is so unique that it belongs in a class (and an article) by itself.
They're All The Same
Not only do these four applications "feel-alike", they are alike. They're all instances of the same program. The only thing different about them is their data files and apparently the only thing that makes a NoteTaker file different from a Database file is one character. If you change the twelfth character (byte) in any file that has the extension GDB, NDB, PDB or WDB you can open that file in the general Database, Phonebook, NoteTaker or WorldTime. See the Quick Tips article in this issue for the way to change .GDB files to either .NDB or .WDB file.
The database applications feel alike because they all present information in the same way.
When you open a database file in any of these applications, you'll
see a list of data items. The User's Manual refers to this as the List
View (Screen 1).
If you highlight an item in the list and press the Enter key,
you'll see all the information in the item. The User's Manual calls this
the Data Item view.
On the HP 200LX, the Database, NoteTaker and Phonebook also have
a Data Card View which is a split screen view with a partial List View
on the left half of the screen and a limited Data Item View (Screen
2).
The List View lets you scroll through the items in the database
file. It also has a feature called speed-locate that lets you find an item
quickly. Just start typing and the highlighter will jump to an item that
matches the characters you've typed. When the match fails, the Palmtop
will beep..
In the Data Item View you can press the F7 or F6 function keys to move to the next and previous items in the file.
The Data Item view can have up to four screens. Pressing the Page Up or Page Down arrow keys (Fn+UpArrow or Fn+ DownArrow) will cycle you through the screens.
What's a Database Good For?
Most Palmtop users are familiar with the Phonebook database. Many have turned it into a contact manager and some have made it a small project manager. For these folks, the Phonebook is their business. You don't have to tell them what this it's good for: they'll tell you.
Palmtoppers who work with ideas and words seem to prize the NoteTaker database. It's a great way to eliminate all those Post-It notes. A couple dozen keystrokes is all it takes to save, recall, improve or wipe out a passing thought that might otherwise be lost.
Road warriors and world travelers have found the WorldTime database to be indispensable.
But what about the general Database application itself: what's it good for?
The Database application is best suited for storing and retrieving more complicated information.
To get an idea of the kind of information you can store in a database, take a look at a few of the databases at www.palmtop.net/hp_db.htm. There are almost 100 databases at this Web site that contain information ranging from "area and country codes" to "USDA nutritional facts." I'm sure there are thousands of other databases in daily use, but the information in them is confidential. We'll never know what they are or who uses them.
The best place to look for information for a database is in your own areas of expertise. Hobbies, jobs, avocations, and interests all contain data that you use on a daily or weekly basis.
If you're not an expert in an area but would like to be one, the Database application can be a great learning tool. Load in the information and take it with you.
The only type of information the Palmtop databases can't handle is anything that requires computations. You can't use the databases to calculate commissions, track budgets, or tally expenses. If you need that capability, there's always the built-in Lotus 1-2-3 or Quicken programs.
Likewise, if your information is such that it would need more than 99 fields per item or more than 5,000 items per file, then it's time to get an add-in database such as DataPerfect..
(The limit of 5,000 items is stretching things a bit. If you have records with lots of fields or large notes, the 5,000 item limit drops to 3,000 or less.)
Building a Database
Suppose you have some information and you want to try building a database. Where do you start?
Actually, the best place to start building a database is at your desk with your Palmtop turned off. Take a piece of paper and write the words
"I want my database to..."
at the top of the paper. Then write down what you want the database to do for you when it's working.
An Exemplary Database
Let me illustrate the process by describing a database that contains information about the Palmtop's database applications.
Here's what my "want list" contains.
"I want this database to..."
1. serve as a pedagogical tool.
2. contain all known tips and techniques about the Palmtop's database applications.
3. illustrate all the different types of data that the database engine can handle.
4. illustrate the traps and techniques in constructing a database.
5. be modifiable so that others can change the layout, delete and add fields and add yet more tips.
6. be visually appealing and easily navigable.
Designing the Data Item View
In the good old days, designing an input/editing screen for a database was a task that started with paper and pencil. Once you had a detailed picture of the screen's layout you would spend days coding and testing the program that would let users work with the screen.
The database engine in the HP Palmtop makes the task of designing a Data Item (editing) screen relatively easy and code-free.
To create the screen, use the command MENU File Define New Database. You'll see the Add Field dialog box. At that point you can type in a name of a field and press the Tab key to pick a Field type from a list of 10 different data types, namely Text, Number, Date, Time, Label, Group box, Option, Checks, Category and Note.
Once you've chosen the field type you want, you can press F10
(OK) and use the arrow keys to move the field around the screen. You can
also press the F3 key and use the arrow keys to resize the field. If you
hold down the Shift key while pressing the arrow keys you'll get fine control
over the position and size of the data field.
Screen 3 above shows the layout of the Exemplary Database. The
numbers with circles around them relate to the large numbered sections
in the following discussion.
1 I could have squeezed all the fields for the Exemplary Database onto a single page with a couple of lines at the bottom of the page for a Note field. Instead, I chose to use two pages. The "Data Item (2/2)" at the top of the screen shows that this is the second of two pages.
The Check box called "Edited" is the first field on the second page. This field serves two purposes. It reminds me to edit the text and it improves the navigation between pages.
Ordinarily you can move from page to page by pressing the Page Up and Page Down keys. However, if the Note field is at the top of the page, the Page Down key will put the cursor inside the Note field where pressing the Page Down key only moves the cursor in the Note field. The only way out is to use the Tab key to move back to the first page. Putting a field ahead of the Note field gets around this surprising behavior of the PageDown key.
While we're on the topic of Check boxes, here's a tip.
If you're designing a database that has several Text fields for "key words", change the text fields to Check boxes. When you're entering data in the database, it's easier to move to a check box and press the space bar than it is to type in the same key word over and over again.
The second page also demonstrates a couple of tricks you can do with the "Label" Field.
Labels usually describe something on the screen. They don't appear in the List View and they're limited to about 20 characters.
If you need more than 20 characters for a label, you can use several label fields and place them next to each other. If you want a vertical label you can create a label field for each character in the label and put the fields beneath one another.
2 There's a good reason to put the Note field on a separate page. The database will work more quickly that way. This is especially true if the Note field contains a lot of text or if your Palmtop has not been turbo charged with a double speed upgrade.
If you want to speed up a database even more, install the FASTDB.TSR program in the C:\_DAT directory and reset the Palmtop. FASTDB prevents the database engine from "packing" the disk file every time you edit a database file.
A Note field can have almost 30 screens, enough to hold 32,000 bytes of text, but you can only have one Note field per record.
Each Note field in the Exemplary Database contains from four lines to several thousand lines of text. Rather than key in the information, you can use the System Manager's clipboard to copy text from a Memo document and paste the text in a Note field. An easier way to bring text into a Note field is to press the F3 key to get a full screen view of the Note field and press F3 again. Then type in the name of a text file that contains the information and press Enter. This will insert the text into the database.
To make a Note field more readable, format the text before you insert or paste it into the Note field. Use a text editor or word processor that will let you remove tabs and extra spaces from the text. Format each paragraph so it has a hard carriage return only at its end. This will insure that the Note field will wrap the text within its borders and prevent ungainly line breaks.
3 The first page of the Data Item view starts with a Text Field. You can have as many text fields as you want in a database. (up to 99) and a Text field can be anywhere from two to almost sixty spaces wide. When you enter data in a text field, you'll be able to enter about 50% more characters than the width of the field. Text fields have one minor nuisance feature: you can't use the CTRL+ arrow keys to hop from word to word when you're editing.
I included the Date, Time and Number fields to show how the database engine handles these field types.
When you enter a date or a time in its respective field the date or time will change to the format chosen in the Palmtop's Setup application (Ctrl + Filer). The Number field type will also insure that you enter only numbers in the field.
These fields also demonstrate the use of "hot-keys".
Hot-keys let you hop over fields when you're entering or editing information. Ordinarily you move from field to field by pressing the Tab or Enter key. If you want to hop to a specific field, give the field name a "hot-key". For example, the hot-key for the Date field is ALT+D. To create this hot-key, put an "&" (ampersand) before the chosen letter when you're entering the Field name in the "Add fields" dialog box.
Hot-keys are tricky to get right. On the one hand, you can use the same hot key for more than one field. For example the ALT+I key hops to the Time and Intermediate fields in the Exemplary Database. If, on the other hand, you want to avoid duplicate hot-keys, you have to pick another letter in the field name. For instance, to avoid duplicate ALT+N hot-keys for "Number field" and "New", I chose to put an ampersand before the "u" in "Number". An important tip for picking hot-keys is to avoid using the letters F, E, V, S, O, Q, and H since the database itself has appropriated these keys for its pull down menu.
When you're designing a Data View screen it's often a good practice to add all the fields at once and press F10 (OK) when you're done. Then you can move the fields around so they are in the right order.
Take care when moving the fields. The trick is to get the cursor to move from left to right and from top to bottom in the working database. To make sure this happens superimpose one field on top of the other until the horizontal borders seem to grow dim (use the Shift arrow keys for "fine movements") then slide the field left or right.
The Number field on the first page is there mostly to remind me that the database engine doesn't do math which for many database programmers is the greatest shortcoming of the database application.
As a workaround for this shortcoming you can quit the database
engine and load a database file in the DBView add-in program. DBView will
do simple arithmetic on number, date or time fields for either the whole
database or subsets of the database (Screen 4).
4 The Exemplary database contains two sets of Group buttons to
show that you can display these groups either vertically or horizontally.
Group buttons are useful since they give much more flexibility in creating
subsets for a large database (see the article on subsets on page 21). The
only trick to using Group buttons is that you have to put them inside a
group box. Unlike Check boxes, they cannot stand alone but must be wrapped
up in a Group box.
Group buttons are sometimes called Radio buttons since they are like the buttons on car radios. If you depress one button the others will all be off.
You can have as many Group buttons and Group boxes as you need.
Group boxes have an unusual way of handling hot-keys. If you assign a hot-key, say ALT-G, to a Group box hoping that this will put the cursor on the GroupBox label, you'll be surprised to see the cursor jump inside the Group box and land on the Group button that is currently on or to the first Group button if all the buttons are off. If you want to toggle the button off, you'll have to use the arrow keys to move to another button and press Tab or Enter to lock it on. The way to avoid this nuisance feature is to avoid assigning hot-keys to group boxes.
5 The Category field in the Exemplary Database points up the fact that you can only have one Category field per database. If you want more than one, try the following trick: change the category options into a series of Check boxes. After all, the options in a Category field are just a group of check boxes disguised as a pull-down pick list.
This trick of turning category options into Check boxes will overcome another limitation of the category field. Theoretically you can have up to 32 options in a category field. However, the total number of characters for the names of all the options cannot exceed 255.
If you need more than 32 options consider using Check boxes instead. When you're entering data in a working database, it's easier to fill in a check box than it is to pull down a list and look for the right option.
On the other hand, once you type in a category option, you don't have to type it again. Just press the down arrow key to activate the drop down list and type the first letter of the category word as often as needed to find the option you want and press the spacebar.
You can also combine options. For instance, some items in the Exemplary Database are both Tips AND Traps. To combine options just pick more than one category word. The database will add a semicolon between the words automatically.
To delete a category option, you have to pull down the list box and highlight the option and then press the Delete key. You can't delete the word merely by pressing the Delete key when the drop down list is not visible.
6 The region labeled "6" shows another use of Label fields and Group boxes.
I wanted the title "Exemplary Database" to appear in all Data Item screens. To accomplish this I created two Label fields called "Exemplary" and "Database", moved them to the lower right of the screen and then surrounded them with a group box. If you look closely you'll see that the Field Name for the Group box is actually a minus sign.
Modifying the Database
When you get a copy of the GENERAL.GDB file you can make it your own by deleting the superfluous fields, adding your own fields and arranging the others to your own liking.
To do all this get to the List View and use the MENU File Modify command (MENU F F ).
In the "Modify Database" screen the Tab key is the only key that that will let you move from field to field.. The arrow keys will either move the field or resize it.
Use the F7 key to delete fields and the F4 key to change the names of the fields. For instance, you could change the field name, "Text field" to "Title".
If you delete all but the Title, Category and Note fields, you'll have the equivalent of a standard NoteTaker file which will be smaller and faster. To move the Note field to the first page, tab to the note field, cut it with the Fn+ Cut key and press the PageDown key and immediately press the Fn+Paste key. Use the Move and/or Resize key (F3) to put the fields in a NoteTaker layout. Finally press F10 when done. All of this will trim about 4000 bytes off the size of the file. However, even though the database file has the look and feel of a NoteTaker file, you won't be able to open it in NoteTaker: not even if you rename the file to GENERAL.NDB. However, all is not lost. See the Quick Tips article in this issue to discover three ways to convert a .GDB file into an .NDB file.
Generating a Report
Many other database programs have the ability to generate printed, columnar reports, complete with subtotals and grand total. The Database engine in the HP Palmtop does not have such a function. However, several users have found that you use the Smart Clip function to produce the raw material for a columnar report. The Smart Clip function will let you set the spacing for each output field. You can also specify whether you want data to be right or left justified or centered in a printed column. Granted, getting the Smart Clip to produce a readable report using the built in spacing commands takes some trial and error.
Others have found that it s simpler to use the Smart Clip function
to produce a Comma Delimited Format file. They just print the data to a
disk file and then import the CDF file into Lotus 1-2-3 or DataPerfect.
Both of these programs have good to excellent report generating capabilities,
complete with page and column headings, date and time stamps and automatic
page numbers.