Print Your Database in the Format You Want

Create a custom database and print it out in the format you want using the built-in Database program and Smart Clip.

By Paul Merrill

When I started at The HP Palmtop Paper, I inherited the job of creating the Product Index you see on the second to the last page of every issue. I would have to go over each article, letter or tip in the issue and make a list of the products mentioned. I would then search past issues for the information and then cut it into a WordPerfect file on my desktop PC. If the product was new, I would consult product literature and/or phone the vendor for the information, and then enter it into the WordPerfect file. Finally, I would open a copy of the Quark file containing the last issues Product Index. I would delete all the products we weren't going to use this issue. I would then cut and paste the new ones and format them.

This whole process was tedious and inefficient. We decided to create one centralized database of product contact and pricing information and work from it each issue.

Setting up the database

The first task was to find a good database program. We started looking for a desktop database program, but realized that we already had a good one right on our Palmtops in the Palmtops built-in DataBase application. If we used this application, we could keep the Product Index database on our Palmtops and use it with the Connectivity Pack version on our desktop PC.

The first step in creating our Product Index database was to decide the type of information we wanted to collect. We would need data fields for the date we entered or updated the information, the product name, price, company name, address, phone and fax numbers, and e-mail address. We would also need a field for a brief, one-sentence description, as well as a Note field for a longer product description. Once we had decided on the information we needed, we began to build the Database.

The Data Item (data entry) screen for the finished product contact database is shown at the bottom of this page. Here's how I created it.

I started the HP 100/200LX Connectivity Pack software on my desktop PC, opened the DataBase application and followed the steps described below. NOTE: You access the MENU key on the Connectivity Pack version of the built-in applications by pressing (ALT). Whenever you see (MENU) in the instructions, press (ALT) if you are using the CPACK DataBase.

  1. 1. From the DataBase application press (MENU) File Define New Database. You should be presented with the Add Field dialog box. Well create a text field to hold the name of the product.
  2. 2. Key in the name of your new data field. For this example, well enter the name Prod.
  3. 3. Press (TAB) once to go to the Field Type section of the Add Field box. Use the ArrowKeys to select the type of field you want. (See database fields, types of in the Users Guide index for more on field types.)
  4. 4. After you have typed in the Name of the field and selected its Type, press (ENTER). The Palmtop will add the data field to the new database and then pop up an empty Add Field box for you to define more fields.



Custom Database : Graphic

 The screen graphic above shows the fields we used in this database. All fields are text fields except for the D (Long Description) field, which is a Note field. We chose text fields because of the flexibility they give us to enter both letters, numbers and characters.

Add Field Screen in Database Program: Graphic

  1. 5. When you have added as many fields as you want, press (ENTER) (F10), name the file, and press (F10) again.
  2. 6. Now position and size the new data fields you have created. Press (TAB) to highlight the data field you want to move or size. Then use the following keys: Press the ArrowKeys to move the data fields. Press (SHIFT) (ArrowKey) to move data fields in smaller increments. Press (CTRL) (ArrowKey) to change the size of the boxes. (It may be easier to add and position/size the data fields one at a time, instead of adding them all and then trying to position/size them.)
  3. 7. When you're finished positioning and sizing your data fields, press (F10), give you new DataBase a name and press (F10) again. That's all there is to it. For more information on setting up a Palmtop DataBase, see Data-base: Definition in your HP 100/ 200LX Users Guide.



Setting up a Smart Clip to export and format database information

After creating the Product database, I spent a couple of weeks keying in the product information. Then I had to develop a method of selecting and printing the desired information to a file in a format as close as possible to the published Product Index. The format for individual product entries is as follows:

"Product Name -- $price

Brief product description of no more than two lines. (See page xx)

 Contact: Company Name, Address, Phone and Fax numbers, E-Mail address."

 I could have tagged the desired products in the DataBase and use the Print option to copy them to a text file. But when I opened the file in a word processor, I would have to do a lot of editing. Id have to delete unnecessary information, add words and phrases, and move text around. Still a lot of work.

What I decided to do was create a Smart Clip and export only the data I wanted, in the format I wanted. In addition, I could use the Smart Clip to add the word Phone before the phone number, Fax before the fax number, and (See page xx) after the product description. Here's how I set up the Smart Clip:

  1. 1. At the All Database Items screen, press (F5) to access the Smart Clip feature and (ALT)-(D) to define a new Smart Clip.
  2. 2. Select the data fields you want to export. Press (F2) to go to the Define Field screen. Press (DownArrow) to move to the first data field you want to export and press (F10). It will appear on the Define Smart Clip screen.
  3. 3. Continue the process of pressing (F2) and selecting data fields until you have all the fields you want. I selected the following fields: Prod, Price, Brief, Company, Country, Phone 800, or, Fax, e-mail. The Define Smart Clip screen appears as shown below, with the data fields concatenated one after the other.
  4. 4. Modify the Define Smart Clip screen so the data appears as you desire. Use the Arrow keys to highlight a data field and then move it to the desired position using ENTER and TAB keys, or the Spacebar.



Graphic Define Smart Clip screen -

 Caption:- after selecting data fields, but before formatting the screen so the data appears as you want it. All fields on one line.

 In our example, the first three fields we selected were Prod, Price, and Brief. I wanted Prod and Price to appear on the first line, and Brief (brief description of the product) to appear on the second line. I highlighted Brief and pressed (ENTER) to put it on the second line, below Prod and Price. After Brief comes Company, which should appear on the third line. I highlighted Company and pressed (ENTER) to move it to the third line.

  1. 5. Insert the additional text you need. Use the ArrowKeys to move the cursor to the desired position and key in characters, punctuation marks, and blank spaces and carriage returns as needed.



In our example, the Prod and Price fields have an em-dash (dash the length of two hyphens) in between. I positioned the cursor on Price and keyed in two hyphens. Ill do a Search and Replace on the two hyphens when I import the product information into my word processor. Next, I moved to the end of the Brief (product description) field, inserted a space and keyed in the phrase (See page xx). Then, I moved the cursor to the beginning of the next line, before the Company field, inserted one Tab, typed in Contact: and inserted another blank space. Finally, I moved to the end of the data fields and pressed (ENTER) three times to put some space between each product listing.

When I was finished moving data fields around and inserting text, the Define Smart Clip screen looked like the screen shown at the top of the next column. (Some data is hidden to the right of the screen due to the screen size.)

Define Smart Clip Screen ( Graphic)

  1. 6. Save your new Smart Clip. When you've got the Define Smart Clip screen just the way you want, press (F10), name your Clip (e.g., Product Index), and press (ENTER) or (F10) to complete the operation. Press (F10) one more time to clear the Smart Clip screen and return to the All Database Items screen.



Making it all work: Using the Smart Clip to print the desired product information to a file.

 The final step was to see if the Smart Clip actually worked. I followed these steps to test the process:

  1. 1. Select desired products. I opened up the All Items screen of the Product Database and selected a few products. (You do this by highlighting an item and pressing (Spacebar). A diamond appears to the left of a selected item.)
  2. 2. Print the Smart Clip to a file. In the All Database Items screen, press (MENU) File Print. You are presented with the Print dialog box, with subsections Print, Style, Print to, and Title.
  3. 2A. The cursor should be resting on the Selected Items button in the Print section. If not, Tab to that section and use the ArrowKeys to make sure that button is highlighted.
  4. 2B. Tab to the Style section and use the DownArrow key to highlight the Custom button. Then press (TAB) twice to go to the pull down menu at the bottom of the Style section. Use the DownArrow to select the Product Index Smart Clip.
  5. 2C. Tab to the Print To section and use the ArrowKeys to highlight the File button. Tab to #Copies and make sure 1 is entered there.
  6. 2D. Tab to the Title section and type in Product Index. We can skip the other print options for this example.
  7. 2E. Press (F10) to print your selected items to a file. Give the file a name when prompted (the file will be saved to the C:\_DAT directory, unless you specify a path and filename at this point). The selected data is saved in an ASCII text file that can be imported into any word processor. (See the output of this clip at the bottom of this page.)



Load the file into a word processor or page layout program for final edit

The above instructions work with DataBase on the Palmtop, or the Connectivity Pack version on a desktop PC. I tested it on my Palmtop, then transferred the ASCII text file to my desktop using the Palmtops memory card and my desktops PCMCIA card reader. (The unedited version of the file is displayed later in the article.) I loaded the file into WordPerfect 6.1 for Windows for a final edit.

I needed to make some small edits because some entries did not have Fax numbers or e-mail addresses. Other than that, the system worked flawlessly and the product information was printed out as I had formatted it. Then I exported the WordPerfect file to Quark for final formatting.

Other uses for the DataBase, Smart Clip team

You can create any number of custom DataBases on your HP Palmtop. In addition, the PhoneBook, NoteTaker, and World Time applications can use Smart Clips to print data out the way you want it. So, for example, you can print out address and phone lists, inventories, itineraries, mailing labels and more, in a format that makes the data easier to understand, and nicer to look at.

What the Smart Clip outputted

 I used the procedure described in this article to print the product information for this issue to an ASCII text file. I loaded it into my word processor and it appeared as shown below, before any editing or formatting. Note that E-mail: was printed out, even if an entry did not have an e-mail address.

Smart Clip Output : Graphic