As useful as the Database application is, there is one thing that is sorely missing, the ability to do arithmetic. As a matter of fact, one of the first things I looked for in the Users Manual was a way to get a printed report that contained totals and subtotals. Others have asked for similar features such as "time-arithmetic" to calculate and report billable time.
Not having found an answer in the built-in Database, many users have turned to "real" database programs such as dBASE III or PC-File. This solution adds the cost of the software, requires additional memory, and reduces the need for the built-in Database.
THE WHOLE IS GREATER THAN THE SUM OF ITS PARTS
The answer to this problem is already contained in the HP Palmtops. The Database application excels in the area of data handling. On the other hand, Lotus 1-2-3 excels in number crunching. When you put the two applications back to back and play their strengths off against each other, you'll see the synergistic principle in action.
To see what I mean, take your 100/200LX in hand and follow along. We'll create a simple Database, move the data to Lotus and calculate totals. Finally, we'll create a Lotus macro to automate the process. Once you see where we're headed, you'll be able to expand on these ideas and modify the solution for your own situation.
Start with a Database
We'll create a very simple Database. Start the Database program and press (MENU) File Define New Database. Then in the "Add Field" dialog box enter fruit as the Field name. Press (ALT)-(G) to establish this as a Category field and press (ENTER). This field could equally well be designated as a Text field, but a Category field will make data entry simpler.
Next, key in quantity as a second field name. Press (ALT)-(U) to make this a numeric field. Press (ENTER)- (F10) and save the file, with a suitable name, e.g. FRUIT.GDB. (On the 200LX, press (CTRL)-(D) to get rid of the card image.) You will then be at the All Database Items screen of your new database.
Now we need to set up the database Sort. Press (F7) and, if needed, type fruit in the 1st sort field. Press (<Tab>) and then (<Spacebar>) until a check mark appears in the Ascending check box. Press (<Tab>) again and put quantity in the 2nd sort field, and a check mark in the Ascending check box. Press (F10), or (ENTER), to save the Sort order.
Key in the following sample data. Since fruit was defined as a Category field you can key in the name of a fruit once then use the pull-down category box for subsequent entries.
Fruit Quantity
apples 10
apples 100
apples 1000
oranges 20
oranges 200
oranges 2000
pears 30
pears 300
pears 3000
Define a Smart Clip
We'll now use the 100/200LX Smart Clip feature to copy our Databased information to Lotus 1-2-3 to do our arithmetic. First, press the (<Shift>)-(<Spacebar>) to mark all the records. Then press (F5) to bring up the Smart Clip dialog box and (ALT)-(D) to define a Smart Clip. In the Define Smart Clip screen press (F2) to choose a field. Make sure the fruit field is highlighted. Then press (ALT)-(W) and set the width to 15. Press (<Tab>), (<DownArrow>) twice to highlight the Right radio button, and then press (ENTER) to complete the field definition. Then press (F2) again, highlight the quantity field name, press (ALT)- (W), set the field width to 15 and the radio button to Right as above. Complete the selection by pressing (ENTER). On the same line you should see:
> fruit<> quantity<
Then press (F10) to execute the Smart Clip definition. Give the Unnamed Smart Clip the name FQ or whatever you choose and press (ENTER). Place the cursor on All Fields and press (ENTER) to return to the All Database Items display. This will define a Smart Clip and copy the selected database items into the clipboard from where they can be pasted into Lotus 1-2-3. After adding new items to the database, all you have to do to copy the new information to Lotus -- from the Database list screen -- is to press (<Shift>)-(<Spacebar>) to mark all the items, (F5) to open the Smart Clip, select FQ and press (ENTER). All the information gets copied to the clipboard in the format that was defined in the Smart Clip and is ready to paste into a Lotus spreadsheet. There is another way to copy Database information to Lotus, by using the Smart Clip to print to a file. From the All Database Items list screen, press (MENU) File Print. In the Print dialog box, set the "Print" to All items; "Print to," to File, "Style" to Custom; and the custom category field to FQ (the Smart Clip created above). It's essential to use the Custom mode of printing and the Smart Clip so that the items will be printed side by side. When you've made all the selections press (ENTER) to print the file, giving it the name FRUIT.PRN. This file can then be read into Lotus.
The reason for choosing a field width of 15 above is to insure that the fields will not overlap vertically when they're clipped and pasted into Lotus. (When you start working with your own database, you'll have to experiment to find the best width for your data fields.)
Integrating Lotus 1-2-3
Now start Lotus 1-2-3 and save any work in progress before creating this new worksheet. To make sure the new worksheet is clear, use the erase command: press (MENU) Worksheet Erase Yes. For this example, format the whole worksheet to display two digits after the decimal using the command: (MENU) Worksheet Global Format , (comma) 2 (ENTER). If you used the clipboard method above, put the cursor in cell A1, then press (Fn)-(+) (PASTE) to copy the contents of the clipboard into the worksheet. Alternatively, if you used the second method, printing the contents of the database to the FRUIT.PRN file, use the command: (MENU) File Import Text to bring the file into the Lotus worksheet.
If you look in the upper left corner of the screen, you'll see:
' apples 10
The leading apostrophe is a tip-off that the contents of the cells in column A are really "long labels" -- that is, long strings of text information. You can't do any arithmetic with the numbers in these strings until you convert them to numeric "values". Here's the easiest way to perform the conversion.
Press (MENU) Data Parse, which will bring up the Parse Settings dialog box. Ignore the "Parse Settings" box that appears on the screen. You won't need it. Press F or (ENTER) to activate the Format-Line option. Press C or (ENTER) to create a Format-Line. Then press I to start the Input-Column option and at the "Enter column of labels to parse: A1" prompt, press (.). Then, in succession, you press (Fn)- (<RightArrow>) (END), (<DownArrow>), (ENTER) to mark column A. Press Output-Range, type A2 as the output cell, and press (ENTER). Finally press G (Go) to complete the conversion. To get rid of the format- -line, put the cursor on cell A1 and press (MENU) Worksheet Delete Row (ENTER). Notice what has happened. The "long labels" have been reduced to a column of labels and another column of numbers.
Creating Grand Totals
At this point, if all you want is a grand total of all the fruit, simply put the cursor in cell B10 and type in the function @SUM(B1..B9). As soon as you press Enter, the answer 6,600.00 will appear. It's that easy.
Lotus Macro to Create Subtotals
In this example, it's a simple matter to inspect the spreadsheet, insert a row after apples, oranges, and pears then use the @SUM() function to create a subtotal for each. However, inspecting long lists of data, inserting rows and keying in the @SUM() function is time- -consuming and error producing. A better solution would be to create a Lotus 1-2-3 macro that will do this for you.
The second sidebar at the end of this article shows a macro that will produce subtotals and a grand total for the "fruit" database. This solution is available as CBRK.WK1 in TLG20.ZIP on the Palmtop Paper on Disk and is offered "as-is". It will work for the current "fruit" example and any similar problem. The restrictions are that the category data must go in column B and the numeric data must go in column C in the spreadsheet. The good news is that the solution works. The not-so-good news is that it produces only a single level of subtotals. Ideally, a report generating program should be able to handle any number of "nested" subtotals. In reality, the maximum level of subtotals that can be handled by a combination of the Database and Lotus 1-2-3 is three. The Database engine imposes this limit since it can only sort three fields, which is one more than Lotus 1-2-3 can do. The ultimate solution should also allow for any number of input fields, produce page and column headers, and do the actual printing. Such a program is beyond the scope of this article. However, watch for it on this year's Subscribers Disk.
How to use the Subtotal Macro
To try the subtotal worksheet, start 1-2-3 and either key in the code or retrieve the CBRK.WK1 file from TLG20.ZIP on Palmtop Paper OnDisk. Issue the command (MENU) Worksheet Global Format , (comma) 2 (ENTER). Put the cursor in cell A1 and issue the command (MENU) Range Name Label Right and highlight the range from A1 to A45. Press (ENTER) to set the range names for all the variables and procedures. Press (ALT)-(I) and you should see the following message in cell A50:
Paste or import the text file and press ALT-I again.
Assuming that you've developed the "fruit" database as described above, copy the data into the worksheet. Either press (Fn)-(+) to paste the clipboard or press (MENU) File Import Text to import the FRUIT.PRN file. The screen should look like this:
B C D
50 apples 10
51 apples 100
52 apples 1000
53 oranges 20
54 oranges 200
55 oranges 2000
56 pears 30
57 pears 300
58 pears 3000
Press ALT-I again. The indicator, in the upper right corner will ask for "One moment, please...". Very soon the screen will change to the following.
B C D E
50 apples 10.00
51 apples 100.00
52 apples 1,000.00 apples 1,110
53 oranges 20.00
54 oranges 200.00
55 oranges 2,000.00 oranges 2,220
56 pears 30.00
57 pears 300.00
58 pears 3,000.00 pears 3,330
59 *TOTAL* 6,660
Here's How the Subtotal Program Works
The cells A2 through B6 hold the names and contents of several variables used by the program. For example, COMPAREA will keep track of the "apples" and "oranges". The TOP and BOTTOM variables will keep track of the ranges for subtotals. The program begins by turning off the screen and panel. It goes to cell B50 and names this cell "ControlCode". It checks to see if the cell is blank and if it is, issues the prompt to "Paste or Import". Otherwise it parses the data string in the rows below cell B50. (To enhance this, you might insert a row after row 13 and add the command:
'/dpfe{BS}.{END}{D}~o~g
This would allow you to edit the format-line before proceeding with the program.)
The PREP procedure sets the indicator; puts zero in the SUBT and TOT ranges; and stores the ControlCode ("apples") in the COMPAREA range. It then goes to the TOP of this range and gets the address.
The LOOP procedure starts by moving down one cell to B51. If the contents of this cell are not the same as the contents of COMPAREA, it will perform the procedure called "DiffCode". If this same cell is not blank, the program will perform the LOOP procedure again. Otherwise it will start the EOJ (End Of Job) routine.
The EOJ (End Of Job) routine calls the SUBTOT procedure to complete the computations. Then it calls the TOTAL procedure. It recalculates the entire worksheet; turns the screen and panel on; and sets the indicator to its default value.
The DiffCode procedure simply runs two other procedures: SUBTOT and RESET, and then returns to where it left off.
The SUBTOT procedure begins by moving the cell cursor to the right and up. It sets the BOTTOM variable to be the address of this cell. Then it recalculates the contents of cell B30 (the FORM cell) which will, in turn, compute the subtotal for this range of numbers. It next adds this subtotal to the grand total, copies the name of the COMPAREA to the right of the current cell and copies the value of the subtotal to the right of this. The last thing it does is return the cell cursor to its former position.
The RESET procedure "updates" the COMPAREA variable to hold a new "ControlCode" and resets the TOP variable to a new cell address. It clears out the SUBT variable. (Clearing this variable is not strictly needed, but old coding habits say "do it anyway".)
The TOTAL procedure comes at the end of the program. It puts the word "*TOTAL*" in the appropriate cell and puts the value TOT in the cell to the right of this.
This should give you some ideas of how to link the Palmtop's built- in Database and Lotus to create reports with totals and subtotals. With a little experimenting you can adapt this solution to any number of situation.
Happy programming.