Have you ever wished you could work with larger database files on your HP Palmtop? Do you need relational database capabilities? Have you ever created a 1-2-3 database, entered a lot of data in it, and run out of system memory trying to open it? If so, read on to learn about an excellent Lotus add-in program named @BASE (pronounced at base). There are a number of add-ins that extend the utility of 1-2-3. @BASE lets you use Lotus to work with very large database data files. You work with database files in the industry standard dBase III Plus format. This add-in program uses Lotus 1-2-3-like menus and is easy to use so there is no need to learn the cryptic commands of dBase itself.
In 1-2-3's heyday, prior to the advent of Windows' spreadsheets, many people used the Lotus 1-2-3/@BASE combination for database purposes. PC Week called @BASE the King of 1-2-3 database add-ins because of its power, functionality and ease-of-use. @BASE operates inside 1-2-3, giving it the power of stand-alone database programs like dBase III. Although the add-in ran well on desktop machines, until recently @BASE did not run adequately on the Palmtop due to the way 1-2-3 was setup to run directly out of the Palmtop's ROM memory. Recently a patch was written to modify 1-2-3, allowing @BASE to be fully capable of running on the HP Palmtop. (Patch included with product.)
Since many of today's most popular Windows-based spreadsheet and database programs can read and write dBase III-compatible files, @BASE eliminates the inconvenient processes of first converting files to the Palmtop's *.GDB format.
@BASE is a true full-featured relational database manager with all the tools you need to build and maintain large, sophisticated databases. It compares favorably with powerful stand-alone database programs like dBase or Paradox. Because @BASE works from within 1-2-3, anyone who knows how to use 1-2-3 can quickly begin working with and analyzing data. Because it works on the Palmtop, users can start carrying larger databases with them wherever they go. @BASE is also useful to 1-2-3-savvy managers who want to integrate dBase data into their Lotus spreadsheets.
As a 1-2-3 user, you get the best of both worlds from @BASE. 1-2-3 gives you the tools for data analysis and report presentation while @BASE gives you enormous data capacity. Since @BASE has the same look and feel as 1-2-3, you'll adapt to it very quickly.
In a short time you can build a worksheet with dynamic links to one or more databases on disk. For instance, you may be supplied with a database file of sales data, and your worksheet might present a sales recap by region. The next month, for up-to-date results after obtaining the new database file, simply load and recalculate the worksheet. Fresh database information is automatically supplied from the link to the disk-based database.
Easy installation, efficient operation
Given its power, @BASE is compact. I keep the following four files in my C:\_DAT subdirectory:
File name Size
BASE.ADN 64,072
BASE.TXT 10,945
BASEFUNC.ADN 11,772
BASEUTL.ADN 16,340
Total: 103,129
The three files with the extension .ADN, are 1-2-3 add-in files and must be attached to 1-2-3 in order to run. In 1-2-3 you attach a file by pressing (Menu) Add-in Attach. Then use the prompts to highlight and attach the files you want. 1-2-3 asks if you want to assign the add-in to a function key, such as F7, F8 or F9. Pick one, such as F7.
BASE.ADN is the main @BASE program and must be attached to use any of @BASE's features. With this attached to 1-2-3 you can perform a number of database activities included opening, browsing and editing (table and record view), querying, bi-directional data transfer, and more.
BASEFUNC.ADN is optional, but with it attached you can use @BASE's special database @functions discussed further below.
BASEUTL.ADN is also optional, but with it attached, you can define and create a .DBF file directly from a 1-2-3 spreadsheet database, import comma delimited and ASCII text files, display information about an @BASE database, and more.
To run @BASE, I usually close all open applications besides 1-2-3. However, I have frequently run @BASE with Memo and Phone open, attesting to its relatively small size. Fully installed, the three attached add-ins take up approximately 90K of RAM on top of the RAM 1-2-3 normally needs. @BASE can have more than one database file open at the same time, with each file open taking less than 5K of RAM.
@BASE is menu driven, like 1-2-3
If you attached @BASE to a function key such as (F7), you would invoke it by pressing (Alt)-(F7) (instead of (MENU)) to bring up the @BASE menu. (See graphic, top of page 34 for an example of @BASE's main menu). While in an @BASE screen you can return to 1-2-3 at any time by simply pressing (ESC) one or more times.
Just as in 1-2-3, you navigate @BASE's menu by pressing the first letter of each menu keyword. Thus to browse a database in table view, you'd press (Alt)-(F7) Data Browse. The resulting @BASE table view presents the database in row-column format just like 1-2-3, with the record numbers indicating the row, and field names indicating the column.
@BASE's Browse screen displays the database file in the row/column format of a Lotus spreadsheet.
Entering data into a database in the @BASE table-view mode is similar to entering data in 1-2-3, except that certain database data-type conventions apply. For example, a label cannot be entered into a numeric field. Also, editing an entry in an @BASE table is the same as editing a cell in 1-2-3. Hence, you must press the (F2) edit key to do so.
You can also view and enter data into a database one record at a time by using the @BASE Data Form view.
Partial screen: @BASE Data Form view lets you view and enter data into the database one record at a time.
Querying a database is a breeze
Sophisticated database programs let you query them (ask them for specific information, like the total of a column of numbers). @BASE responds to both simple and complex requests and its querying process is fully menu driven. Using on-screen prompts and lists, you select field names, logical operators (such as = for equals, > for greater than, etc.), and a value to search for. @BASE prompts you to correct syntax errors in search criteria, helping you to reduce logical errors.
Compared to the complicated method required to query a 1-2-3 spreadsheet, users who like simplicity and power will appreciate @BASE's query method since extremely complex criterion can be specified very easily. A time-saving feature is that complex criterion prompts can be saved in a cell in a 1-2-3 worksheet and can be retrieved for use at any time.
Specifying a criteria to query (or filter) a database in @BASE involves walking through a series of screen prompts and lists, as shown by the series of screens in the next column. The process includes a first screen, which asks you to select the field containing the data you want to query on from a list. The second screen from the top presents a list of filtering operators (i.e., equal to, not equal to, greater than, less than, etc.) . The third screen asks you to specify the filtering value, and the forth screen asks if you are finished specifying filters or if you want to create a compound filter using logical and/or operators.
Bi-directional data transfer
One of @BASE's menu selections allows for bi-directional transfer of data between an existing database file and 1-2-3 worksheets. Records to be transferred can be the entire database or selected records based on criteria you specify. Capabilities of this menu selection include:
The first step in querying an @BASE database is to select from a list, the field containing the data you want to search on. In this case, Employee names is selected.
The next query screen presents a list of logical operators to filter the list. In this case, Equal is selected.
The next screen asks you to specify the filtering value. In this case, we are looking for an employee named Gary.
The fourth screen asks if you are finished specifying filtering values or if you want to create a compound filter.
The Import feature copies selected records from a database file to a Lotus spreadsheet. Notice in this example that it includes only records where EMPL=Gary, since criteria had been established through the criteria prompt screen.
I have used @BASE's bi-directional data transfer capabilities many times when trying to analyze and clean up downloaded corporate databases. It is excellent.
Cross Tabulations
In the May/June 96 issue beginning on page 47, I covered the process of how to create a cross-tabulation calculation of a sales database in 1-2-3. That was an advanced article. @BASE distills all that effort into just a few simple keystrokes. Furthermore, it can easily do more complex cross tabulations with multiple fields which are not possible to perform in 1-2-3.
@BASE lets you cross-tabulate your data either as counts (frequency distributions) or sums (subtotals). You might, for example, have an employee database that contains the following three fields: Salary, Gender and Department. A cross-tab operation could easily identify the number of male and female employees in each department or create a table showing the total salary expense for each department. You can do the same thing in 1-2-3 with a combination of database statistical functions and the Data Table command, but @BASE's method is significantly less complicated and far less time consuming. All cross tabulations appear as results in the active worksheet:
<Screen: @BASE cross-tabulation feature. This example displays the cross-tabulation of a database tracking sales for three representatives (Frank, Gary and Sally). The database is cross-tabulated by Customer (displayed in column A) and Employee (displayed in columns B, C, and D ). Sales figures are displayed below employee names.>
@BASE's cross-tabulation feature is very powerful, and it works well. I still prefer to use it on desk top machines over other programs which also have cross-tabulation features, such as Paradox, Access, Approach, or Excel. In my view, @BASE's cross-tabulation feature is so good it is worth the price of the program alone.
Excellent Manual
The manual accompanying @BASE is very well written and user friendly. It contains two major sections. One section contains tutorials that can help you quickly grasp the concepts and be up and running in a relatively short time. The other major section is reference material for @BASE's menu and @functions. Additionally, @BASE comes with a few sample files that can further your understanding of the possibilities of the program.
@BASE is well suited for the Palmtop since it further expands the Palmtop's use as a portable reference companion using industry standard file formats. @BASE is also a good alternative as a portable data gathering tool.