VW TT WW
InFocom
Z Z : = |
COND
~ TMOOSCNVH SXINMO
ea ike) Shh) ie iGh itt ibt Jkh Gh Gh kh Gk Gh ea ke hU6GhhCUhURlUrhU CU OU
(Se
is a trademark of Infocom, Inc.
merstone
HANDBOOK I
OWA ge
! / Ue ~ @ \\ a e ~ as . > <3 “ | ‘ Pg a — - aah mS - fo 7) af Re ; aS, » e f 4 : _ i. . G <4 ald ay, ‘ Ft . se . . A : = . .> .L ar re Kies
Cornerstone
designed and built by: Brian Berkowitz (principal designer) Richard Ilson (principal designer) Paul DiLascia Craig Leckband Ed Black (project manager) Chris Reeve (consultant) Tim Anderson (consultant)
Cornerstone | documentation: Richard Ilson (editorial manager) Elizabeth Young (production manager) Written by: Paul DiLascia Barry Jacobson Larry Martin Mike Quinn Mark Sawtelle Graphic design: Marsha Caine Typography: Woodland Graphics, Bedford, MA Printed and bound by: CSA Press, Hudson, MA Illustrations: Jim Carson © 1984 Diagrams: Suzanne L. Breault © 1984 Copyright © 1984 Infocom, Inc. All rights reserved
This software product is copyrighted and all rights are reserved by Infocom,
Inc. The rights and obligations of the user of this software product are governed by the terms of the Infocom License Agreement furnished with this product. Lawful users of this product are licensed to read the programs on the diskettes furnished with this product from their media into the memory of the computer solely for the purpose of executing the programs. Copying, duplicating, selling, or otherwise distributing this product is in violation of the License Agreement and is a violation of law.
This manual is copyrighted and all rights are reserved by Infocom, Inc. This manual may not, in whole or in part, be copied, photocopied, reproduced, translated, or reduced to any electronic medium or machine-readable form without Infocom’s prior written consent.
Cornerstone is a trademark of Infocom, Inc.
Rolodex is a registered trademark of Rolodex Corporation.
dBASE II is a registered trademark of Ashton-Tate.
PFS is a registered trademark of Software Publishing Corporation.
MailMerge is a registered trademark of MicroPro™ International Corporation. 1-2-3 is a trademark of Lotus Development Corporation.
Printed in U.S.A.
Infocom, Inc. 55 Wheeler St. Cambridge, MA 02138
First printing January 1985,
/ ' te by bewh.ce - a
Waa
.
~ 5
4)
aa
3
Faber tas deere
x 17
|
‘n
th
-
" 1
;
ae 7]
Me
a
=
’
td)
woo uw
Ww Ww WwW lw
Preface
An Overview of Cornerstone Introduction for the Impatient User Viewing Your Data
Entering and Updating Records Selecting Records
Advanced Reporting
Printing
Designing Forms
Planning Your Database Defining Your Database Customization and Initial Mode Index
109 135 161 187 195
OWNER’S HANDBOOK I
TABLE OF CONTENTS
Chapter 1 Chapter 2 Chapter 3 Chapter 4 Chapter 5 Chapter 6 Chapter 7 Chapter 8 Chapter 9 Chapter 10 Chapter 11
aN LL Ta Fi
al
raz
WZ Et ira
EE
»
we
f i
we
mys bee
ae
UL
it
i ) ey 7
i! Jo) Ji) ) es 7s
Ti! ns
Rs ss 2
(wl
Preface
This Owner’s Handbook comes in two volumes. Volume I, Preface “Working With Cornerstone,” covers the major uses of the
system. Volume II, “Additional Information and Advanced
Concepts,” contains information less central to daily use
(such as how to use Cornerstone’s conversion and backup
utilities).
The examples in Volume I frequently refer to the Sample Database included as one of your application diskettes. The Sample Database serves as an order entry system at Omnifex, a fictitious auto parts supplier. It contains three files: Customer, Order, and Part. The Customer file contains the names and addresses of Omnifex’s customers. The Order file contains orders from these customers. The Part file contains information about the parts sold by Omnifex. The Sample Database is also used in examples throughout the Beginner's Guide, which provides a tutorial introduction to Cornerstone.
In this Owner's Handbook, we have highlighted key concepts with a <> pointing hand = in the margin. al
Paragraphs printed like this one describe minor details or other points that you may want to skip.
An index is included at the end of each volume in the Owner's Handbook.
Although you can read the Owner’s Handbook from cover to cover (we hope you will), the chapters are more or less self- contained, so feel free to read whatever sections interest you, in whatever order you like. Once you become familiar with Cornerstone, this Owner's Handbook and the Quick Reference Card will serve as your primary reference tools.
ili
ett Lf deg agtiag wagig igi } ae Eps ) I PLP Le we web re FREh| we a al niall wl Poh
ae Pak op) ep mie
a OVERVIEW CORNERSTONE _— cittprex
2 An Overview of Cornerstone
CHAPTER 1
CONTENTS
Welcome to | a Cornerstone &
Support for You | 4 Getting Started 5
What | aa Cornerstone Is 6 |
What y, eal Cornerstone ST Is Not 8 pe
SIT
Where To Go eo From Here 9 4
et eet et ee ee J
wd i
‘ \
Wold Wd Wb id Wt
An Overview of Cornerstone 3
You think we don’t know how much you already have to read? You think we expect you to leap into this manual with a smile on your face? We know better.
This manual was written by people who hate reading manuals too. We've tried our best to make this one readable by keeping it simple. Important concepts are illustrated with clear examples. Less important details are separated out. If you spend some time with this Owner's Handbook you will be rewarded with an understanding of the powers of Cornerstone, and the knowledge to use it effectively in your business.
An Overview of Cornerstone
Welcome to Cornerstone
4 An Overview of Cornerstone
ed)
j
A ay i a
4
Support for You = Your Cornerstone box comes with two support systems. One consists of all the printed materials: the Owner's Handbook, the Beginner's Guide, the Quick Reference Card, and the keyboard template. The other system is a built-in manual, our on-line help system. Whenever you're using Cornerstone, you are only one keystroke away from help.
The Owner's Handbook describes all of Cornerstone’s features. It's more than just an explanation of commands: it’s a repository of accumulated wisdom from the developers and early users of the system. The Beginner's Guide introduces you to database concepts and teaches you the major features of Cornerstone. While especially useful if your familiarity with computers and databases is limited, it’s also a quick way for any user, novice or experienced, to get an overview of Cornerstone.
"
4
“
a N
The built-in support system is accessed via two keys: (HELP) and (OPTIONS). Pressing the (HELP) key explains what you are doing and what you can do next. It’s like always having a reference manual open to the right page. (oprions) tells you exactly what choices are available from your current position.
Eueve
When you're entering data, (OPTIONS) will tell you about any S| restrictions that exist and will show you all of the allowed he values, even when those values come from other files. The ™s
more you work with Cornerstone, the more you'll appreciate this feature.
At
Jf
r
Atay f
4 |
Al ATs
Pn, on, on) n,n) 2
id it
i) i ta
AW lw io lb Wi id i id ft
An Overview of Cornerstone 5
Everyone has a different style of learning and a different level of experience in using computers, so there’s more than one way to use these support materials. However, we do have a few recommendations:
O Take a look at everything in the package.
O
Read the “Read This First” brochure to find out about the installation procedures.
Read the Beginner’s Guide and work through the tutorial lessons. The few hours this takes will be well worth your time.
If you're too impatient to do that, or if you already have a fair amount of background with databases, you can skip to the “Introduction for the Impatient User,” which explains all essential Cornerstone terms, keyboard usage, and what you see on the screen.
Play with the Sample Database. All of the examples in the Beginner's Guide and the Owner’s Handbook refer to this database, which details sales of a fictitious auto parts company, Omnifex.
Explore the Client Tracking system, which has been designed as both a demonstration system and a usable application. It will maintain a Rolodex Desk Top File™ type of directory for individuals and companies, and keep a history of all client contacts. In addition, you can tailor it to meet your specific needs.
Finally, even if you think you know everything about databases, please read the chapter “Planning Your Database.” This chapter explains how to go about designing your Cornerstone database.
Getting Started
6 An Overview of Cornerstone
What Cornerstone Is
| Once you've gone through the Beginner's Guide or the
“Introduction for the Impatient User,” you will probably have a good idea of how Cornerstone operates, but it will take
| some time and practice to understand everything you can do | with it. The following application examples should help, but
remember, we can't tell you everything Cornerstone will do for you—you will find that out for yourself over time.
At first the only people at Infocom using Cornerstone were those responsible for developing and marketing it. One of the principal developers, who moonlights as our softball coach, built an application that computes (and, unfortunately for some of us, prominently displays) the individual and team batting averages after each game. Another of the main developers built a sophisticated time-billing system for a consulting business.
Then one of the marketing people (whom we can never get off the phone) built a Rolodex system to keep track of the companies and individuals he contacts in the course of his work. Many other people in marketing copied or adapted the same system (which is now part of the Client Tracking system).
Over time, other people within the company came by to look at Cornerstone, and they thought up new uses. Our president started using Cornerstone to keep track of his large science fiction library. Then, after he became more familiar with Cornerstone, he built something a bit more serious: an application for analyzing sales of Infocom’s products by type of computer, by product, and by customer. (He gets the raw data from our minicomputer using Cornerstone’s Convert utility. )
oh oh
g
Me
rr
| Md AA A at i j T T -
|
7a ELUE
aa
rf
|
rf
4 = =
sue
CeCe ee en i)
i
Av Ad (i id i id Ww t
An Overview of Cornerstone
Our product support representative realized she could
use Cornerstone to keep a history of all written customer contacts and to monitor any problems encountered by our interactive fiction customers. Previously this work was done by hand.
Our most sophisticated in-house application was
developed for use by our personnel department, replacing a minicomputer time-sharing service with a personal computer and hard disk. Virtually every feature of our old personnel system was replicated, and new features were added as well. Our personnel manager, who has little experience with computers, recently realized the ease of developing new reports and is expanding the functionality of the system. One of her latest reports prints detailed descriptions of all the currently unfilled positions within the company.
The list keeps growing. Our purchasing manager (who had never used a computer before in her life) is starting to use Cornerstone to track purchase orders, keep an inventory of equipment, and monitor equipment maintenance schedules. Our public relations manager keeps a detailed history of her contacts with media organizations. People who travel often on business are using Cornerstone to keep track of their billable expenses. Cornerstone is even used to keep a history of its own testing, with detailed notes recording feedback from testers.
Applications developed outside Infocom have also varied widely. A loan officer for a large New England bank built a “Loan Officer Support System” that monitors all of the paperwork associated with different types of loans. This system is now in use throughout the bank. A wholesale manufacturer of fine chocolates is using Cornerstone to
8 An Overview of Cornerstone
What Cornerstone Is Not
keep track of inventory and monitor machine maintenance. A physician at a major hospital is using Cornerstone to analyze survey results, while his colleague uses it to catalog
_ journal references. The controller of a small metal products
manufacturing company uses Cornerstone to do job costing: calculating materials, labor needs, and costs for various production runs.
You get the idea—a wide range of information needs can be
| addressed by Cornerstone. But don’t feel you have to think
of everything overnight. Cornerstone has been designed to grow with you. You can add new files, new types of information, and new reports as you go along.
First, Cornerstone is not a spreadsheet. If you use a spreadsheet for doing “what if” and “sensitivity” analyses, you'll probably want to keep it. On the other hand, if you've been using your spreadsheet for storing quantities of data and preparing reports, you now have a more appropriate tool with Cornerstone. And remember, the Convert utility lets you transfer data to or from most popular spreadsheets.
Cornerstone is also not a word processor. While Cornerstone’s text-handling capabilities exceed those of other databases, they don’t rival most word-processing programs. You could write block style letters with Cornerstone—you could even send form letters that substitute different people’s names and addresses. But for most writing you'll want a word processor. One thing to keep in mind: it’s easy to include a Cornerstone report
in a larger word-processing document.
Vy ‘
Sa a A) aN a
- oN 4 | 1 ]
=
J
=
-
WWW Aw tata ta a ih
An Overview of Cornerstone
The Owner's Handbook describes every aspect of Cornerstone, but that doesn’t mean you need to read each volume from cover to cover. Volume I covers all of the major functions, while Volume II contains more advanced concepts that are less central to everyday use.
The next chapter summarizes the essentials for
using Cornerstone. So if you're one of those people who started here without looking at the Beginner's Guide, the “Introduction for the Impatient User” is for you. Even if you went through the tutorial lessons, the next chapter is good for refreshing your memory. After that, take the approach that feels right for you. You may want to start using
Cornerstone right away, and use the Owner's Handbook as a
reference manual when necessary, or you may want to read through some or all of the Owner's Handbook first. In either case, look at the chapter “Planning Your Database” before building your own database.
We hope you enjoy working with Cornerstone.
Where To Go From Here
Riguel geal eulae GRU tl FontA! aL at A IRL OT ET a a a DAW aD aD Wr Be wh H) rir a wae Ww Ww) WD) WL WL wpe Ww
INTRODUCTION | FORTHE 2
IMPATIENT USER _ cisrrer_
12 Introduction for the Impatient User
CHAPTER 2
CONTENTS
Introduction iS How to Run Cornerstone 113 Structure of a Cornerstone Database | 14 Cornerstone Modes 16 Cornerstone
Keys and
Screen (18 Issuing a Command 20 Entering Values
in a Form 21 Exiting
Cornerstone 23
The (HELP) Key 23
The (OPTIONS) Key 24
The Sample Database 24
Don’t Pull the Plug
ot
;
"
' 4 =a
nn
tr —
zi
AT AT AT I tt A8 a8
u = ih Tl
il ‘al
Ma) tm) i) io) Lud dm) td Led Sued Seed in) iad ed add
Wold iw i i la i lg
Introduction forthe Impatient User 13
This chapter covers the minimum you need to know to
use the Owner's Handbook, and is intended to be a quick alternative to the Beginner’s Guide. You might also want to use this chapter as a refresher. If this chapter does not meet your needs, use the Beginner's Guide instead. It covers the same topics in more depth and introduces additional aspects of Cornerstone.
Two-diskette System
To start Cornerstone on a two-diskette system (assuming you've installed Cornerstone; if not, read “Read This First”), boot the system and get to the DOS prompt (A)). If you want to try out the examples in Owner's Handbook I, insert the Sample Database Copy diskette into drive A. Otherwise, insert some other Cornerstone data diskette in drive A. Next, insert the Cornerstone system diskette into drive B and enter the DOS command:
A)CORNER
Hard-disk System
To start Cornerstone on a hard-disk system, boot the system and get to the DOS prompt (C)). (If you don’t see the C), type the DOS command: C: and then press (ENTER). )
Change the current directory to the Cornerstone system directory with the DOS command:
C)CD \CORNER
To run the system with the Sample Database (from which the examples in Owner's Handbook I are taken), enter the DOS command:
C)CORNER SAMPLE
Introduction for the Impatient User
Introduction
How to Run Cornerstone
14 introduction for the Impatient User
Structure of a Cornerstone Database
To run the system with one of the other databases supplied with Cornerstone, or to run it with one of your own
_ databases, follow the CORNER command with the name of
the directory that contains the data files you want to use. For example, if you have database files in a subdirectory named ACCOUNTS, enter
C)CORNER ACCOUNTS
' to run Cornerstone with the data in that subdirectory.
A database is an organized collection of information. In Cornerstone, you organize your database into files. Each file contains records. A record describes a single person, place, or thing. The Sample Database, for example, is organized into Customer, Order, and Part files.
The structure of records in a file is determined by the attributes you tell Cornerstone the records will have. An attribute is a quality or property of people or things. For example, the records in a file containing information on a fleet of cars might have as attributes the year, make, model, vehicle identification number, and mileage for each car. In the Sample Database, records in the Customer file have as attributes the name, address, phone number, and total purchases for each customer.
Because all records in a file must have the same structure, you only define that structure once, when you define the file. What makes one record different from the next are the values you enter for attributes. For example, all records
in the Sample Database’s Customer file have the Name attribute, but the value for that attribute is different in each record.
a — ;
NADAS
j i
Y
#3
#
fi
He
“4
i>
ey Pai He ne +} ) 1) i
WoW lw WoW di ld te i i ii ds Se te ie le ed Sed Med id
Introduction for the Impatient User 15
: e
In Cornerstone, an attribute can have one of seven data types: String, Number, Integer, Date, Time, Boolean,
and Enumerated. (For example, an attribute containing alphabetic characters, such as a person’s name, would be a String; an attribute referring to an amount of money would be a Number. )
Subrecords
Within a record, Cornerstone allows you to group related information as a subrecord, consisting of one or more subattributes. For example, a purchase order often has a separate line for each item ordered, with a name, quantity, and price for each. A subrecord allows you to enter all three pieces of information for each item ordered.
A database of baseball players could have a subrecord for baseball seasons. The subrecord might have subattributes for the year, number of at-bats, number of hits, and batting average. Within a single player’s record, then, you could enter a group of statistics for each season.
Derived Values
You can define an attribute to derive its value from information elsewhere in the file, or even from another file. Derived values are automatically filled in and kept up-to- date by Cornerstone. When you define an attribute to be derived, you supply a derivation expression, which tells Cornerstone how to derive the value.
To derive information from one file into another, you must define a relationship between the two files. The relationship acts as the path on which the information is sent.
ia a ee
——— EEE
16 Introduction for the Impatient User
Cornerstone Modes
DEFINE
INITIAL
UTILITIES
CONVERT BACKUP
Lad
SELECT UPDATE
Edit mode is used Format made is EDIT throughout the available under FORMAT system View, Update
Select, and Print modes
The use of Cornerstone entails just four basic tasks: entering (and updating) records in your database, selecting the records you want to see, looking at the records you have selected, and printing reports. You will perform several other tasks less frequently, such as designing formats and backing up your database. Cornerstone has a mode for each task. Generally, you enter a mode by issuing the command of the same name. (To enter Select mode from View mode, for example, you issue the SELECT command. )
na at a
4g
MM
Ee
vi- M an fe
Hi
VC CC et et i nn i)
Introduction for the Impatient User
Initial mode is used to start and finish each session with Cornerstone, use the utilities (Convert, Backup, and Recompute), delete formats and selection criteria, issue commands to make new records, and reach Define and View modes.
View mode is used to look at records, organize the display into columns or rows, create reports, sort records, and issue commands to reach Update, Select, Format, and Print modes.
Select mode is used to specify criteria to select records.
Update mode is used to create, change, and delete records.
Edit mode is used to enter or change values in forms.
Print mode is used to set up specifications to print the information displayed in View mode.
Format mode is used to design forms with which to display data.
Define mode is used to define or alter the structure of your database.
Convert mode is used to convert data from other products for use in Cornerstone, and vice versa.
Backup mode is used to make backups of your database.
iW
18 Introduction for the Impatient User
Cornerstone Keys and Screen
Error line Command tine Prompt line Menu window Top status line Data area
Bottom status line
_ we refer to keys by their function in Cornerstone, not by the
fh)
Throughout the Owner's Handbook and in system messages
eee:
J
characters or names on the keys themselves. The keyboard template tells you the function of keys F1 through F'10, and the Quick Reference Card shows the location of all keys that have a special function in Cornerstone.
ol il ff
¥ ~—
No CHDICE is HIGHLIGHTED. You should enter or select an option.
bl
=:
UPDATE — Which records do you want to UPDATE?
;* b LS [re=ea) NEW-RECORD CURRENT-RECORD ALL a ! FLAGGED UNFLAGGED Fae . VIEW CUSTOMER 15 Records NOT SORTED = NAME i ) " Newton Auto Rentals = Lange's Motorworld a) Watertown Car Center - Bergman's Antique Autos = Medford Motor Service * Roman Olds Dealers «ae Tosh’s Service Center wh Roxbury Auto Body and Repair Cai Hal's Foreign Auto Repair = Lexington Auto Service ii LF Concord Bus Ling my : | T Motors Hanover Sarvice Station ih creas Bunker Hill Garage ‘| mI F ne phe Sa Se ee ee NRC RPESS RAMEE OATHS Me Alcea hee Re Sera File: CUSTOMER Previous moda: Initial Files viewed: 1 =
CoC CMC MRC MRC RC MRC MR eet et ee ee) ee ne)
Introduction for the Impatient User
From top to bottom, the Cornerstone screen consists of:
Error line. If you enter an invalid command or try to enter something into a form that doesn’t make sense, Cornerstone will display an error message indicating
the problem.
Command line. This line displays the command as you enter it.
Prompt line. The prompt tells you what to do and may briefly explain the options in the menu.
Menu window. This window displays the options to select from.
Top status line. This line tells which mode you're in and gives other information depending on the mode.
Data area. This area, which takes up most of the screen, displays your data. It also displays help and options messages when you press (HELP) Or (OPTIONS).
Bottom status line. This line tells you what file you are currently working with, the mode you will return to when you press (DONE), and the number of files you are viewing.
iy)
ee
f
=
Gena oo ot
20 Introduction for the Impatient User
Issuing a _ A Cornerstone command consists of a command word Command followed by options. Both command words and options are chosen from the menu. You build a command by highlighting a choice from the menu and moving it to the command line. | There are two ways to do this: | 0 Use the arrow keys to highlight the option you want, then press (SPACE) to move it to the command line. | © Type the option—it will be highlighted as soon as you have typed enough characters to differentiate it from
| the other options (one or two characters is usually enough). Press (SPACE) to move it to the command line.
r |
When you add an option to the command line, Cornerstone may display a new set of options to pick from. In most cases the prompt line tells you to highlight an option in the new on menu and add that option to the command line. If you press —_ (ENTER) before the command is complete, a message in the rh
error line will tell you that the command can’t be executed.
r r rie ala ! ff. a Et >
=
Ho3
Until you press (ENTER) to execute the command, you can edit the command line by pressing (RUB-WORD), which i deletes the last option from the command line; (BACKSPACE), sc which deletes the last character from the command line; or th (CANCEL), which erases the entire command line.
When your command line is complete, a message in the Wc prompt line will tell you to press (ENTER) to execute the
command. ~
\
a) im) led i) i ied te id te te te ee
t
CCRC eC eC eet ee et ee |
Introduction for the Impatient User PAI
Whenever you have to fill out a form, either Cornerstone will put you in Edit mode automatically or the EDIT command will be available in the menu.
Once you are in Edit mode, the first field in the form is highlighted. Anything you type is entered as a value in the highlighted field. Simply type the value, then press (ENTER) or (NEXT) to store the value and move the cursor to the next field. When you have entered values in as many fields as you want, press (DONE).
Here are some of the keys and control-key combinations you can use to edit a form:
(NEXT) Move to the next field
(PREVIOUS) Move to the previous field
(ARROW-RIGHT) Move to the next character
(ARROW-LEFT) Move to the previous character
(ARROW-UP) Move to the previous line
(ARROW-DOWN) Move to the next line
(DONE) Leave Edit mode and return to the previous mode
(ENTER) Move to the next field; leave Edit mode (same as (DONE) if you are on last field in form)
(CANCEL) Recover the previous value in a field, or clear all values from the form
(BACKSPACE) Delete the character to left of cursor
(RUB-WORD) Delete the previous word
Entering Values ina Form
22 Introduction for the Impatient User
| (DELETE-CHAR) Delete the character at cursor | (ins/ovs) Change from inserting characters to overstriking, and vice versa (FIRST) Move to the first field in the form (LAST) Move to the last field in the form (OPTIONS) Display a message showing what you can enter in the field (HELP) Display a message describing the current field Ctrl-A Move to the beginning of the line Ctrl-E Move to the end of the line Ctrl-K Delete from the cursor position to the end of the value
If the value you enter is longer than the width of the field, the value will continue on a new line automatically (as ina word processor).
Fields that are defined to take multiple values will expand when you press (ENTER), allowing room for more values. Similarly, when you enter the last value in a subrecord,
aS Soon as you press (ENTER) Cornerstone automatically expands the form to accept another subrecord. If you then continue to press (ENTER) without typing values, Cornerstone erases the empty value or subrecord and moves to the next field.
You can’t edit a derived attribute, or one that was defined to be non-editable. The field cursor jumps over these fields instead of resting on them.
Hist rps! pa
SD
y 7
wee
r f
ties
(ft At.
ce.
TA
f
T
f)
=.
“|
Ss
im
f
Psi
ret * i Mrs “Y
a ie
lab in) ted tad tm Sd Ua ied Ud add
VCC et Ct eet eet eet ee eee
Introduction for the Impatient User
Whenever you enter a value into a form, Cornerstone tells you if the value is not allowed. This ensures that values you enter are consistent with your database definition. For example, a field may have one or more constraints that prevent you from entering certain values. You can always press (OPTIONS) and (HELP) to see what values are allowed for the current field.
A value is stored as soon as you move the cursor off the field or press (DONE). If you begin editing a value and then change your mind, you can get back the original value if you haven't moved the cursor off the field. Just press (CANCEL) followed by (ENTER), which executes the CANCEL VALUE command.
To end a Cornerstone session, issue the EXIT command Exiting
in Initial mode. You can get to Initial mode by issuing the Cornerstone ALL-DONE command (where available) or by repeatedly
pressing (DONE).
r= The EXIT command is the only safe way to exit cs) Cornerstone. <2 Any other way may ruin your data.
You can press the (HELP) key any time. When you are The (HELP) Key entering a command, pressing (HELP) explains each menu option. When you enter values in a form, pressing (HELP) tells you about the field you’re on, and if you press (HELP) a second time, you get tips on editing. If you get an error message while using Cornerstone, press (HELP) to learn what to do. And if you misplace your keyboard template, press (HELP) two or three times to learn the location of Cornerstone command keys and control characters. To clear the Help message from the display, press (CANCEL), (DONE), or (ENTER). Anything else you do will both clear the Help message from the screen and be taken as input.
23
24 Introduction for the Impatient User
The (OPTIONS) Key
The Sample Database
Don’t Pull the Plug
The (options) key serves two purposes. When there are more options in the command menu than fit in the menu window, you can press (OPTIONS) to expand the window and see all the options. When you are entering values ina form in Edit mode, pressing (opTIONS) displays a message telling you what kind of entry you can make in that field.
Before you jump right in and define the structure of your own database, see what a typical application looks like by experimenting with the Sample Database. The database is for a fictitious car parts company called Omnifex. It has three files: a Customer file, which contains information about each customer; an Order file, which contains the orders placed by Omnifex’s customers; and a Part file, which contains records for each part and tool in the Omnifex catalog.
When you have become familiar with the Sample Database, read chapter 9, “Planning Your Database,” before you go on to define your own database or convert data to a Cornerstone database.
r= Leave the system only with the ExIT command. 2 If you mistakenly begin an operation that takes a long time to complete, you can press Ctrl-Break (hold down Ctrl and press the Break key) to safely stop the operation and exit Cornerstone. Any other means of ending a session will ruin your data.
ad
~
|:
ya
as
ii Ms
Fy"
a 4
=
yh
Ts
A
-
MAMMALS,
ad
ab) AA Lak La) Lal dat) tad) ta) td tad td i td
Introduction for the Impatient User
«> Make back-up copies of your files regularly with the Backup utility. <2 If your diskettes or hard disk are physically damaged, if you experience a power failure while using Cornerstone, or if you exit Cornerstone improperly, you may ruin your database. Be sure you always have a back-up copy.
Don't attempt to manipulate your Cornerstone files with DOS commands: you will ruin your data.
r= Never remove a diskette from a drive at any time during your Cornerstone session, even if the drive light is off and the diskette is not spinning. <1 Remove a diskette only after you have left Cornerstone and the DOS command prompt has re-appeared. (Exception: you may remove diskettes during the Backup process when told to do so.)
“El
ED
29
SP a a rr
aa at
P.? "md iv ee ee
=
ag AY (268.
a
% aw.’ ¢ |
i
ra
CHAPTER
TAM
28 Viewing Your Data
CHAPTER 3
CONTENTS
Introduction
Entering View Mode
Scanning Your Data
Changing the Appearance of Your Data
Producing Columnar Reports Producing Detailed Reports
Saving Formats
Using Saved Formats
Sorting
Viewing Other Files
The View Mode Menu
32
34
36
37 38
A2
me ef wl
i.
r —-
tt sts
dof
44 or
tsi ff.
A
4
Viewing Your Data 29
Viewing Your Data
In this chapter you'll learn how to look at the informationin | Introduction your files. With Cornerstone, you can design an unlimited
variety of reports for the screen or printer, displaying
exactly what you want to see, where you want to see it.
View Mode
In View mode you create new reports in a matter of seconds, displaying some or all of your data. You can then save these reports for repeated use.
In View mode you can also jump from one file to another. While you’re busy looking at the orders of your best customers, you might get an urgent call: someone needs to check the price of a new part. Without losing your place in the Order file, you can view the Part file, answer the question, and return to your work in the Order file.
View mode is also the starting point for many other database operations. These include adding or changing information in your files, selecting only those records meeting specified criteria, developing custom-tailored report formats, and printing reports.
30 Viewing Your Data
a fo
- ee pad Ceerteed ed §
Entering View To enter View mode, select the view command from the :
Mode Initial menu. Cornerstone then displays a menu showing the ai! names of your files and subfiles. You must select one to ye ™ complete the command. YH
| The figure below shows the screen display after entering
| View mode with the Customer file from the Sample
_ Database. The menu area displays the available commands
_ from View mode, the status lines tell you about your current position, and the data area shows some of the information contained in the Customer file. To the left of the first customer name is the record cursor indicating the current record, the record the system is ready to work on.
A Af st
a 4
Select a command from the menu, Use arrow keys of type the command.
gis
ears | UPDATE SELECT VIEW COLUMN DETAILED +
USE SAVE REPORT SORT PRINT ALL-DONE wl , VIEW CUSTOMER 15 Records NOT SORTED r =e
be! |
)) Newton Auto Rentals hy Lange's Motorworld ; CHT, Watertown Car Center a Bergman's Antique Autos Py j Medford Motor Service we epee Roman Olds Dealers Rector ee Tosh's Service Center b } | Roxbury Auto Body and Repair ee Hal's Foreign Auto Repair ina Lexington Auto Service hs, i Concord Bus Line T Motors Pec Hanover Service Station m, Bunker Hill Garage = — yr
| < .
} r} <- SSL nT NED ttt aS Som
File: CUSTOMER Previous moda: Initial Files viewed: 1 wy
- —r"
WWW WW We a a
The way your information is displayed on the screen is the current report format. In this case, the report format consists of a column of customer names with a header, NAME, describing this information.
To leave View mode, press the (DONE) key or select ALL-DONE from the menu.
You enter View mode looking at all the records from the file you've specified. However, the records may not all fit on the screen at once. You can think of the display as a scroll with the records printed one after another, and the screen as your window on the scroll. The entire scroll is as long as all the records, and can be up to 255 characters wide. The screen is of course much smaller, so it generally shows only part of the scroll.
Cambridge Commons Cab Co Cambridge Commons Cab Co. 140 Cambridge Commons Cab Co.
Lexington Auto Service $ Hanover Service Station 40 $ Bunker Hill Garage 109 $ Concord Bus Line 218 $ i, Roman Olds Dealers 200 Si, Tosh’s Service Center 130 $ Tosh’s Service Center 0 $ Cambridge Commons Cab Co. 20 $ Lexington Auto Service 55 $ Lexington Auto Service 0 s
o”
Concord Bus Line 32
Viewing Your Data 31
Scanning Your Data
32 Viewing Your Data
Changing the Appearance of
Your Data [ >
Producing Columnar Reports
The group of records that you're working with is called the _ working set. The working set initially consists of all the _ records in the file, but you can restrict the working set to | just records you're interested in. (See chapter 9, “Selecting | Records.”)
_ There are two basic ways to move through your report:
O You can move the record cursor with any of the motion keys: (NEXT), (PREVIOUS), (FIRST), Or (LAST).
O You can move the scroll with any of the scrolling keys: {PAGE-FORWARD), (PAGE-BACKWARD), (SCROLL-FORWARD), or (SCROLL-BACKWARD).
Cornerstone lets you view the information from your files in an unlimited number of display formats. You should feel free to change and to experiment with formats. c= Changing
the format has no effect on the data in your files. <1
Cornerstone provides three ways to display data: columnar formats created with the COLUMN command, row-oriented formats created with the DETAILED command, and custom-
tailored formats created with the REPORT FORMAT command.
The sections below describe the COLUMN and DETAILED commands. Chapter 8, “Designing Forms,” describes the use of the REPORT FORMAT command.
The COLUMN command is used to create columnar reports that show one or more attributes from a file. Each attribute you select is displayed in a column, and the attribute name appears in the header above the column.
iff
Ai A}
Lit
i
-
~ —=—
“Hi
Viewing Your Data 33
For example, suppose sales have been falling off lately and you've decided to call your customers to remind them that you're still in business. Using the Customer file, issue the | command
COLUMN NAME AREA_CODE PHONE
(If there are more attributes in the file than can be displayed in the menu, just press the (options) key to see all the choices.) Once you've selected all the attributes you're interested in, press (ENTER).
That’s it—the report you wanted appears on the screen, as shown below.
Select a command from the menu, Use arrow keys or type the command.
Ee] UPDATE SELECT VIEW COLUMN DETAILED USE SAVE REPORT SORT PRINT ALL-DONE
VIEW CUSTOMER 15'Records NOT SORTED NAME AREA CODE PHONE y Newton Auto Rentals 617 266-7715
Lange's Motorworld 617 731-3730 Watertown Car Center 617 357-8423 Bergman's Antique Autos 4i1 266-4420 Medford Motor Service 617 848-4003 Roman Olds Dealers 411 731-1220 Tosh’s Service Center 411 957-5600 Roxbury Auto Body and Repair 617 426-1230 Hal's Foreign Auto Repair 4i1 266-9636 Lexington Auto Service 617 367-0700 Concord Bus Line 411 273-2301 T Motors 411 . §67-9416 Hanover Service Station 41) 567-9416 Bunker Hill Garage 411 762-1980
$$ File: CUSTOMER Previous mode: Initial Filesviewed: 1 -
=~ 3 = - =ay 3 ~<a = = 3 3 = 2 a va 2 a za a 2 2 2
34 Viewing Your Data
| The column format is the easiest way to look at just a
_ few attributes froma file. If the attribute values are not extremely long, each record will fit on one or two lines, so
_ several records will fit on the screen at once. You can use the scrolling and motion keys to scan your report beyond the first screenful of information.
You can use Format mode to change the position of the columns or the a. header (or both), change (or eliminate) the header names, or insert additional columns into an existing report. The width of your columns 1s determined by the default display widths established when you defined the attributes.
if
f
} {
%
a — —
Producing Although the columnar format is handy for viewing a few H Detailed attributes, the screen becomes crowded as more attributes jj acme Reports are displayed. When this happens try using the DETAILED at
hy
command, which displays each attribute on a separate line. DETAILED works much the same as COLUMN, with one minor ai difference: The DETAILED command gives you a choice of showing SOME-ATTRIBUTES Or ALL-ATTRIBUTES from the file. hit
fi
To see only some attributes, select DETAILED SOME- ATTRIBUTES. This lets you select attributes from the menu.
ca
Choose as many as you want, then press (ENTER). The figure We below shows the results of the command Ree Sr | hh
DETAILED SOME-ATTRIBUTES NAME AREA_CODE PHONE oe
; =a
Compare this to the column format containing the same rH information. Sar
we
J id
UAW AW WW i
Viewing Your Data
Select a command from the menu. Use arrow keys or type the command. ae] UPDATE SELECT VIEW COLUMN DETAILED USE SAVE REPORT SORT PRINT ALL-DONE VIEW CUSTOMER 15 Records NOT SORTED ) NAME. Newton Auto Rentals AREA CODE: 617 PHONE: 266-7715
NAME, Lange's Motorworld AREA CODE: 617 PHONE: 731-3730
NAME: Watertown Car Center AREA CODE: 617 PHONE: 357-8423
NAME Bergman’s Antique Autos AREA CODE: 411 PHONE; 266-4420
File: CUSTOMER Previous mode: Initial Files viewed; 1
To see every attribute in the file, select DETAILED ALL- ATTRIBUTES. If you press (ENTER) at this point, all the attributes appear in the order of their definition. To change that order, press (SPACE) before pressing (ENTER) and a menu of attribute names will appear. Select only those attributes you want moved to the top of the report. All remaining attributes will be displayed following the ones you select. A detailed display of all attributes from the Customer file is shown below.
39
Ss ee
36 Viewing Your Data
Selact a command {rom the menu Use arrow keys or type the commend
= UPDATE SELECT VIEW COLUMN DETAILED USE SAVE REPOAT SORT PRINT ALL-DONE
VIEW CUSTOMER 15'Records NOT SORTED
) NAME. Newton Auto Rantals
STREET: 75 Monroe
CITY: Newton
STATE: MA
ZIP: 02210-1387
AREA CODE: 617
PHONE; 266-7715
EXT: 217
CONTACTS. Paul Henri John Cain James Carter Elice Keith
TERMS: NET30
DISCOUNT: .200
NOTES; Reliable pickup
TOTAL PURCHASES; $ 224.80
File: CUSTOMER Previous mode: Initial Files viewed 1
You can switch freely between COLUMN and DETAILED formats. Information in a COLUMN format lets you compare records easily. The DETAILED format lets you see more information.
| You can store any report format for later use. In working with your database, you will probably design certain commonly-used formats for different needs. For example, you might have one format to display cumulative sales by customer, and another format to print mailing labels for these customers. With Cornerstone you can create a format once, save it, and recall it whenever you need it again.
Viewing Your Data 37
a = 3 3 = 3 = ==] =| 3 = = = = a 2 2 a as 2 -
To save a format, issue the command SAVE FORMAT
Cornerstone prompts you to enter a name for the format. You can enter any name you want. Since Cornerstone allows you to use as many as 32 characters in a format name, these names can be quite descriptive. This makes it easier to use your database. The name Active_Customer_Accounts tells more about the format than AcCustAc. (Use underbars (_) or hyphens (— ) instead of spaces in format names. )
Suppose you want to create a format called Cumulative Sales that shows the total number of orders and total value of purchases from each customer. After viewing the Customer file, issue the commands
COLUMN NAME NUMBER_—ORDERS TOTAL_PURCHASE SAVE FORMAT CUMULATIVE_—SALES
The first command creates the format. The second command saves it for later use.
Once you save a format, subsequent changes are saved only if you issue the savE command again. You can save the changed format under the original name (in which case the original format is overwritten) or under a new name (if you want to keep the original format).
To use a saved format, select USE FORMAT from the menu. Using Saved Then choose one of the formats you've previously saved Formats from the menu.
r= A format is tied to a particular file. <2 You can USE a Seal format for a file only if you saved it in that file.
Whenever you decide to use a new format, Cornerstone checks to see whether the current format has been saved. If it has not, Cornerstone | warns you, and gives you an opportunity to save it.
38 Viewing Your Data
Sorting
If you name a format View, the system will automatically use it whenever you enter View mode. For example, you might want to see the names and phone numbers of your
| customers every time you enter View mode. To establish
this as the default display format you need only issue the following commands
COLUMN NAME PHONE SAVE FORMAT VIEW
If you don’t have a format named Vrew, Cornerstone uses a column format showing the first (non-subrecord) attribute.
If you created a format, intending to keep it for future use, but forgot to issue the SAVE FORMAT command, try issuing the command USE FORMAT PREVIOUS-VIEW. Cornerstone
automatically keeps the last unsaved format used in View mode under the name Previous-View.
When you save a format in View mode, Cornerstone saves more than just the record display information. The format can also contain
O sorting instructions
O headers, subtotals, and totals (see chapter 6, “Advanced Reporting”)
Q printing instructions (see chapter 7, “Printing”)
You save all this information with the SAVE command and restore it with the USE command.
To delete formats you no longer need, select DELETE FORMAT from Initial
mode, and then select the format to delete.
Often you will want to arrange the records in your report in a particular order. You might want to alphabetize by customer name, or arrange customers from oldest to newest, from largest to smallest purchasers, or by last
purchase date. You might even want to arrange your records
¢ iis #\ Fi FV So ® = = bp &
r
a ;
40) || — a St
oul [ee
naar
~
teal Sj 3
Af
tit
a
fi
r a
nl
WWW WW ea
on the basis of more than one attribute. (A telephone directory, for example, is ordered by last name and, within the same last name, by first name. )
With Cornerstone you can sort your records on the basis of any non-subrecord attribute. Cornerstone imposes no limit on the number of attributes you can sort on.
To sort your records, select the SORT command from the View mode menu. Cornerstone then displays a menu of attributes for you to choose from. After selecting an attribute, you must select a direction: ASCENDING (a to z, lowest to highest number) or DESCENDING (z toa, highest to lowest number). Once you've selected an attribute anda direction, the menu of attributes appears again, allowing you to enter additional attributes and directions. You can select as many attributes as you wish, but one, two, or three suffice for most purposes.
Suppose you plan to take a trip around the country and you want to call on your best customers in each city. You could probably use a report grouping customers by city, with customers from each city ordered from largest to smallest purchasers. To do this, issue the commands
COLUMN NAME CITY TOTAL_PURCHASES SORT CITY ASCENDING TOTAL_PURCHASES DESCENDING
The figure below shows the result of issuing these commands. The right corner of the top status line indicates that the current format is sorted.
Viewing Your Data
39
40 Viewing Your Data
My bil Pes ho * Li Lio 24 ; eae f Lint ai i Poe BLT = a ( Select a command from the manu. Use arrow keys or type the command. Hy = UPDATE SELECT VIEW COLUMN DETAILED rom | USE SAVE REPORT SORT PRINT ALL-DONE it H | VIEW-CUSTOMER 16 Records SORTED * a | NAME CITY TOTAL PURCHASES — me NS he gk a at aly a an Re a a ay ee t ) Hanover Service Station Boston S$ 6,244.20 } Bunker Hill Garage Boston $ 4,508.70 lil Hal's Foreign Auto Repair Boston $ 963.20 eT) Concord Bus Line Cambridge $ 4,831.64 / Cambridge Commons Cab Co Cambridge $ 2,263.55 { ch Roman Olds Dealers Cambridge $ 2,055.80 “ ) Tosh’s Service Center Cambridge $ 1,942.40 | T Motors Cambridge $ 1,895.32 Lol Bergman's Antique Autos Cambridge $ 0.00 iy | Lexington Auto Service Lexington $ 2,538.00 , Medford Motor Service Medford S$ 377.00 i sree Newton Auto Rentals Newton $ 224.80 he Roxbury Auto Body Repair Roxbury S$ 819.00 hy Ronnie's Rickshaws Solana Beach $ 0.00 mol wa’ File. CUSTOMER Previous mode. Initial Files viewed: 1 Aias aay fe,
Certain operations, such as adding new records or selecting all the records, can disturb the sorted order. If the sorted order has been disrupted (if the status line says NOT
ff.
r
9 Lo SORTED), you can re-sort by issuing the SorT command ar without having to respecify attribute names. Cornerstone Piro
remembers the last sort command.
After certain commands and database operations, Cornerstone cannot tell if the records are still sorted. To be on the safe side, it assumes that the records are no longer sorted. For example, when you add a new record, it is automatically placed at the end of the report. If the record belonged at the end (Zbigniew Zelany’s record, for instance) the order is actually correct. Sort again if you're not sure.
fp
At
tif
+ =
me
! “
Viewing Your Data
ry When you save a sorted report format (when the status = I line says SORTED) you also save the sorting information,
both attributes and directions. <1 When you later use the
format, the system automatically sorts the records before
displaying them, including any new records that were
created after you saved the format.
ee ee ee
Even your default View mode format (the one named View) can be sorted. Sorting may take some time. How much time depends on the size of your file and your computer hardware. If your file is relatively small, you may prefer a sorted default View mode format; if the file is large you may prefer an unsorted default format. To change a format from sorted to unsorted, issue any command that disrupts the sorted order (such as SELECT ALL) and then save the format under the previous name. Ifa sort is taking a long time and you want to terminate it before its completion, just press Ctrl-Break (hold down Ctrl and press the Break key) to exit Cornerstone.
If you sort on the basis of a multi-valued attribute Cornerstone uses Just the first value. If you want to sort on the basis of a subattribute ina subrecord, you must first use one of Cornerstone's aggregate functions to extract a single value. Consult the chapter on “Entering Information Into Forms” in Owner's Handbook IT for details.
n
Any of Cornerstone’s basic data types can be sorted. For most, the meaning of ASCENDING or DESCENDING is straightforward. The one data type where the results may be surprising is Enumerated. Cornerstone sorts enumerated attributes in the order in which the values were defined.
=| = = =) a val = a 2 a | a a
42 Viewing Your Data
Viewing Other Files
Suppose you're looking at the Customer file when the phone rings. There's been a mix-up with the orders for Tosh’s Service Center, and the folks down in accounting want you to check Tosh’s old orders. You could press (DONE), returning you to Initial mode, and then view the Order file. However, you're in the middle of creating a new report for the
_ Customer file and don’t want to lose your position. The
better approach is to use the VIEW command from the View mode menu.
From View mode, you can move to any other file with the VIEW command. When you finish with the second file, pressing (DONE) returns you fo your original position in the previous file. If you like, instead of returning to your original file, you could move to a third file. (Up to eight files can be viewed concurrently in this fashion. The bottom status line displays the number of viewed files. )
To view another file, select VIEW, and then the file you want to view. If the file you choose is related to the current file, you have two options: You can view ALL of the records in the file, or you can view ONLY-RELATED-RECORDS in the file. (If the new file is not related, Cornerstone will display all records in that file. )
If you choose to view only the related records, you will be asked to select one of the following options:
QO CURRENT-RECORD. Selects all records related to the current record.
QO FLAGGED. Selects all records related to any of the flagged records (flagged with the (FLAG/UNFLAG) key).
QO UNFLAGGED. Selects all records related to any of the unflagged records.
O ALL. Selects all records related to any of the records in the current working set.
pelts: = ri * & ne - «
om call
“¥
RR
EA =
Coe
es
Al.
AHA SL
(i
vie
2 =!
Meson =H
—-
“
WW WW AW AW We at
Viewing Your Data
To look at Tosh’s orders, you could enter
VIEW ORDER ALL-RECORDS
This tells Cornerstone that you want to view all records in the Order file. You would then have to select the records for Tosh’s Service Center. If Tosh’s record in the Customer file was your current record, you could enter
VIEW ORDER ONLY-RELATED-RECORDS CURRENT-RECORD
This would place you in View mode with the Order file, looking at only Tosh’s order records.
In general, all of the View mode commands are available to you whether you have viewed one file or many files, but you cannot update records in a file you are viewing more than once. To update such a file, you must press the (DONE) key until you reach the original occurrence of that file, and then enter Update mode.
Viewing Subfiles
You can directly view a subfile (file of subrecords) as you would any other file. In the Sample Database, for example, you can view the subfile Line Items. While viewing a subfile you can change the display format in any way you choose.
You cannot change the information in a subfile directly. You can only update the subrecords in a subfile by updating the records In the parent file. For example, you can only update Line Items by updating the line items in the individual Order records.
Returning Home
When you have been viewing more than one file, pressing the (poNE) key returns you to the immediately preceding mode. Repeated use of (DONE) will return you to earlier and earlier modes. If you've finished viewing all the files, it’s quicker to select ALL-DONE from the menu, which completes all activity and returns you to Initial mode.
43
44 Viewing Your Data
The View Mode Menu
| You’ve probably noticed that many choices in the View mode
menu have yet to be discussed. Each of the following commands is described in a separate chapter:
UPDATE. Used for actual manipulation of data: entering records, deleting records, and changing records. See chapter 4, “Entering and Updating Records.”
SELECT. Used to narrow your working set of records to those meeting specified criteria, such as customers in Cleveland who have never gone to a Cleveland Indians game. See chapter 5, “Selecting Records.”
REPORT. Used to prepare more complex reports containing subtotals, totals, and headers, and to design free-form report formats. See chapter 6, “Advanced Reporting.”
PRINT. Used to print your report or write it to a disk file. See chapter 7, “Printing.”
r 4 pote F 3 bmettinay ted © ’ =
~—t ¥
7
a he
.
oa
: fy iA fi # St ». ,
wr
AAAS.
ff
= 3
hy Bt “hr
=
ENTERING f AND UPDATING RECORDS
CHAPTER_
wuweus
Wwe ee
f if bead” a { Ls j
46 Entering and Updating Records
CHAPTER 4
CONTENTS
Introduction
Two Brief Examples
The UPDATE Command
Creating Records
Derived and Initial Values
Updating Initial Values With RECOMPUTE
Creating Subrecords
Changing an Existing Record
Deleting Records
The Update Format
Printing the Update Set
Hl
a),
-~ =.
5,
~ —
oo —
LAA AF
j
Le
4
ft.
~
f
HAA
—— sz
hh
j
f
WW Ww eae
x"
it ayy ba |
Entering and Updating Records 47
Entering and Updating Records
|
Once you have defined your database, you'll want to enter Introduction some records into it. And sooner or later you'll want to make
changes to records you have entered. Update mode allows
you to add new records to your database, change existing
records, and delete records. You enter Update mode by
issuing the UPDATE command in View mode.
Your most frequent uses of Update mode will be to create Two Brief new records and to change existing ones. Here is an Examples example of each, using the Sample Database.
A letter has arrived from a new prospect, Ronnie's Rickshaws, announcing the company’s decision to open an account with your firm. You are in View mode looking at the records in the Customer file. To create a record for Ronnie's Rickshaws from View mode, you enter
UPDATE NEW-RECORD
The system displays a new record form, and the first attribute, Name, is highlighted. This means Cornerstone
is waiting for you to enter a value there. You just type in “Ronnie’s Rickshaws,” then press (ENTER). This action stores the value and highlights the name of the next attribute, Street. Again, you simply type in a value, then press (ENTER).
48 Entering and Updating Records
Entar values for the record.
EDIT NAME SUNS OS
|Name: ia
STREET
CITY:
STATE
ZIP. 00000-0000
AREA CODE
PHONE:
EXT:
CONTACTS:
TERMS: NET30
DISCOUNT: .000
NOTES
TOTAL PURCHASES: $ 0.00 NUMBER OF ORDERS 0 TOTAL UNITS 0
File: CUSTOMER Previous mode. Update Files viewad: 1
You complete your new record by continuing to type information in each field and pressing (ENTER). When you type a value in the last field and press (ENTER), or when you press (DONE), Cornerstone stores the record automatically. Cornerstone then asks you if you want to create another record. If so, you simply press (ENTER) again. Since you only have one new customer to enter in our example, you answer NO and return to Update mode.
When you press (DONE) again, you return to View mode and see the new record you created for Ronnie's Rickshaws.
5
f im Bes Pie
as Sem Las ioe, Nat ee Be
~ rw
z.
~—
ff,
= ce ae ad « = 4 4
‘
_
At
HAS
if
LAL
¥
A
a a F —4 aj 24S
A
Ta. ee a ee
wi ww
\u
Ww Ww Ww
Entering and Updating Records 49
Select a command from the menu. Use arrow koys or type the command.
Ba UPDATE SELECT VIEW COLUMN DETAILED USE SAVE REPORT SORT PRINT ALL-DONE VIEW: CUSTOMER 16 Records NOT SORTED
Hal's Foreign Auto Repair
Lexington Auto Service
Concord Bus Line
T Motors
Hanover Service Station
Bunker Hill Garage
Cambridge Commons Cab Co D Ronnie's Rickshaws
File: CUSTOMER Previous mode: Initial Files viewed: 1
When Ronnie’s Rickshaws informs you a few months later that they are moving, the record in the Customer file can be taken care of just as easily. This time, move the record cursor to Ronnie’s Rickshaws, and issue the command
UPDATE CURRENT-RECORD
To change Ronnie’s address, use the EDIT command. (You can do this by just pressing (ENTER), since the EDIT command is already highlighted.) Press (ENTER) a couple of times to move the form cursor to the Street field, and type in the street address. Pressing (ENTER) again stores the new value and moves the cursor to the next field, City, which also must be changed. Finally, you press (DONE) to leave Edit mode and return to Update mode.
50 Entering and. Updating Records
The UPDATE _ As you have just seen, the UPDATE command is paired with Command _ an option that specifies the set of records you want to _ update, called the update set.
UPDATE CURRENT-RECORD is used to update the current record (the one next to the record cursor) in the View mode display. The update set in this case consists of that one record. This command is also useful for examining a record even if you don’t intend to update it.
UPDATE FLAGGED and UPDATE UNFLAGGED let
you update only the records that are flagged, or left unflagged, in View mode. The update set then consists of just those flagged, or unflagged, records. You can learn more about flagging and unflagging records in chapter 5, “Selecting Records.”
UPDATE ALL lets you update all the records that make up your current working set. These records then constitute your update set. (If you have used selection, the working set will be a subset of all the records in the file. )
UPDATE NEW-RECORD lets you add one or more new records to your file. This command displays a new form where you can fill in the values for your new record. The update set in this case consists of the new records you create.
Only one record at a time is displayed in Update mode. Whenever you have more than one record in the update set, you can move from record to record by using the (NEXT), (PREVIOUS), (FIRST), and (LAST) keys in Update mode.
'
~—; ver
Ann
—_
= ale 4)
S_3
Ea =
BIEL
ee
F
ft
=
it
-
it
Pd
At
Ais
aaa
|
he ee i i) i i A i
Entering and Updating Records
You issue the EDIT command in Update mode to change values in an existing record. (You can issue the EDIT command merely by pressing (ENTER).) You then enter or change values one field at a time, moving from one field to the next.
Cornerstone will not let you update records in a file which you are viewing at another level. For example, if you view the Customer file, then view another file, then view the Customer file again, the system will not let you then enter Update mode. To make changes in the file, press (DONE) until you return to the first level of interaction with that file.
For your convenience, Cornerstone lets you issue a Creating command to create a new record from three places. Records
O In View mode, you can issue the UPDATE NEW-RECORD command. Use this command if you want to create a new record while you are viewing a file.
© If youare already in Update mode, you can create a new record by issuing the NEW-RECORD command.
O You can also issue the NEW-RECORD command in Initial mode. This command requires you to select the file you want to create records in.
Whichever way you choose to create a new record, the result is the same: Cornerstone puts you in Edit mode with a new record form to fill out.
Entering Values in a New Record
When you create a new record, you enter values into a form. As with any other form in Cornerstone, you need only type a value in the field, then press (ENTER) to store the value. The cursor then moves to the next field, except when
O The present field can take multiple values. When you press (ENTER) the field expands to accept another value. If you press (ENTER) again without entering
ol
52 Entering and Updating Records
another value, the field contracts and the cursor a moves to the next field. Li | i +
© The next field takes a derived value, or was set to be
non-editable. A derived value (see Derived and Initial —— Values, below) is taken from elsewhere in the Hi database—you don’t have to enter it. A field is set to » aa be non-editable in Format mode with the SET-CHARAC HT command (see chapter 8, “Designing Forms”). In both 5 ane | these cases the cursor skips over the field. ial © You are on the last editable field in the form. In this aT case pressing (ENTER) is the same as pressing (DONE): a you leave Edit mode and return to Update mode. AT Before it stores a value, Cornerstone checks to see that the i ane | value meets any constraints that may be in effect. If the ase value doesn’t meet a constraint, Cornerstone will prevent iy arta you from entering that value. For example, the Discount ai attribute in the Customer file of the Sample Database is ae defined so that you cannot enter a value greater than 1.00. Dt This constraint prevents an entry that would have Omnifex i ae paying its customers to take its goods. it Some of the constraints may have been supplied when you a defined the file; others are built into the system. The rR purpose of constraints is to insure that you enter values in consistent with your database. You can always learn exactly f<- which constraints are present for a particular field by KE pressing the (opTions) and (HELP) keys. Furthermore, you bE can always change any constraints that were entered when , ty the attribute was defined. Chapter 10, “Defining Your Par: Database,” tells you how to create and alter constraints. hi) Whenever Cornerstone prevents you from making an entry, y
it tells you why with a specific error message. If you press a (HELP) immediately after you get the error message, Me,
i i i i i i ii i Ae
Ww
\w lw
\y
\u \
Entering and Updating Records Od
Cornerstone will explain the problem and suggest how you can correct the situation. You can also press (OPTIONS), and Cornerstone will tell you what is valid in the field. If you must edit the value, you can use the (BACKSPACE), (DELETE), (INs/Ovs), and control keys.
There are two built-in constraints that are always present.
0 Cornerstone won't let you enter a value of the wrong data type. If the field is expecting a date value, like “11/25/84,” you can’t enter “Fenway Body Shop” or “$19.95.”
© Cornerstone won't let you enter a value that doesn’t make sense. You can’t enter “Feb 31, 1984” as a date, for example, or “25:30” as a time.
You may also encounter constraints that were supplied when the file was defined.
O Cornerstone will prevent you from entering a value if it falls outside any defined minimum and maximum values. (You can’t enter 101 for the attribute Zest Score, for example, if the attribute is defined to take no value greater than 100.)
O When the attribute is defined to take a unique value, you won't be able to enter a value that is the same as one already in the file.
O You will get an error message if the field is mandatory (that is, it must have a value), and you did not type ina value. You won't be able to leave the form until you enter a value or issue the CANCEL FORM command.
O The value you are entering may be restricted to be the same as some value in another file. In the Sample Database, for example, a customer name in an Order record must equal one of the values for the Name attribute in the Customer file.
54 Entering and Updating Records
=o
If you are creating many records with the same value for a particular attribute, you can use the Copy command (CTRL-D) to copy the value from the previous record.
at
(( >" | «Cornerstone automatically deletes empty records. = If you issue a command to create a record but don’t enter any ' values in the form, Cornerstone will delete the record when _ you leave Update mode. You can delete records that are vot empty with the DELETE command (see Deleting Records,
=
~
_ below). ha Derived and | Cornerstone may fill in some fields automatically, depending hy) ; Initial Values on how you defined your database. The system derives the
_ value from other information in the database, saving you
i. _ time and eliminating the possibility of error. ae si
| Derived Values — In the Sample Database, when you fill in the name of your | customer in an Order record, Cornerstone checks to see i that it matches the name of a customer in the Customer file, ae then takes the address and automatically displays it in the \) ae
| Order form. The address in the Order file is a set of derived Pa fields. A derived field displays a value (or a combination of rr values) that actually reside elsewhere in the database. You hi can’t edit a derived field—the cursor jumps over it when you saa are filling in values. If you want to change a derived field, Bis you must change it where it actually resides. In the example so above, you would have to change the address fields in the ; te
Customer file.
Cornerstone is able to display information from the Customer file in the Order file because a relationship was defined between the two files. The relationship provides the path on which a value in one file is derived from information
" Sa in another file. You can learn more about relationships and ST
derived attributes in chapter 9, “Planning Your Database.”
WAG AG WA A
NY
Entering and Updating Records
Initial Values
When you create a new record, some fields may already have values filled in. These are called initial values, because Cornerstone automatically fills them in when you create a record, based on an initial value expression you entered when you defined the attribute. An initial value is helpful for the head start it gives you when you create a record. Unlike a derived value (which is merely a “display” of another value or combination of values), an initial value can be edited, because it is actually stored in the record where you see it. The only difference between a value you type in yourself and an initial value is that Cornerstone fills in the value for you when you create the record.
In the Order file of the Sample Database, the Order Number and Order Date fields have initial values that are filled in when the record is created. The Order Number field gets its initial value from the UNIQUE_ID function, and the Order Date field gets its initial value from the TODAY function.
In the Sample Database, the Terms field is not filled in
when the new record form is first displayed, although it was defined to take its initial value from the related Customer record. Terms will differ from customer to customer, so the system doesn’t know which terms value to use until you enter the name of the customer. When you identify the customer by entering the customer Name, Cornerstone fills in the initial value for the Terms field (obtained from the related Customer record).
Terms is not a derived field, because you might want to change a customer's regular terms for a special order. If Terms were a derived field, you would only be able to change the Terms in the Order file by changing the Terms in the Customer file, and this would change the Terms for all the orders from that customer.
39
56 Entering and Updating Records
}
Updating Initial = Normally you write an expression that computes the initial
Values With RECOMPUTE
value when you define the attribute in Define mode, but you | can change that expression by issuing the INIT-VALUE
| command in Update mode. For example, the Order Date
_ field has an initial value taken from the TODAY function. But Suppose you want to enter several orders that were taken yesterday. You can use the INIT-VALUE command to change the initial value expression from TODAY to yesterday's date—say, “1/1/85.” Making that one change in the initial value expression saves you from editing the value separately in each record. Now every record you create will automatically have yesterday's date on it. Then, when you have created the records you want with the new initial value, you use the INIT-VALUE command again to change the initial value expression back to TODAY.
Cornerstone fills in an initial value only when the initial value satisfies all the constraints present for the attribute. For example, if an attribute 1s constrained to be greater than zero, but the initial value expression for a particular record returns a value of less than or equal to zero, Cornerstone will not fill it in.
Cornerstone enters initial values automatically when you create a record. Unless you deliberately edit a value that was filled in by an initial value expression, however, it will not
change even if you change the value or expression on which it is based.
To recompute an initial value, use the RECOMPUTE command. Enter Update mode with the record in question, then enter Edit mode. Next, press (ENTER) until the field you want to recompute is highlighted. Return to command level (by pressing the (COMMAND) key), and issue the RECOMPUTE command. This will change the value in that field to one based on current values.
=.
-
Al,
vr be
—
3
f
=
FALSE AS
es
ft At
aa
=
Tee eee ee ee ee ee
VWWWWA
Entering and Updating Records
In Edit mode under Update mode the RECOMPUTE command works for one value at a time, one record at a time. ‘To update one or more initial value fields in a group of records, use the UTILITIES RECOMPUTE command in Initial mode. This command lets you specify one or more attributes to recompute, and can recompute an entire file at once.
If your database has subrecords, you create them when you | Creating create or update the parent record. As with any other form Subrecords in Cornerstone, you need only type in a value and press
(ENTER) to move to the next field. When you enter the last
value in one subrecord, Cornerstone automatically expands
the form to accept another subrecord. If you continue to
press (ENTER) without typing any values in a subrecord,
Cornerstone takes that to mean that you don’t want to enter
any more subrecords, and stops creating new subrecord
forms.
Although you can view records in a subfile directly, you can only update a subrecord while you are updating the parent record.
OM,
58 Entering and Updating Records
Changing an Existing Record
Deleting Records
Before you use Update mode to change an existing record, select in View mode the record or records you want to update. In View mode, you can
© Move the record cursor to the record you want to update and issue the UPDATE CURRENT-RECORD command.
0 Flag, or leave unflagged, the records you want to update. You can then issue the UPDATE FLAGGED or UPDATE UNFLAGGED command as appropriate.
O Issue the VIEW command again to select the related records of another file, then issue the UPDATE ALL command. <> The UPDATE ALL command does not necessarily update all the records in a file. <2
Rather, it updates all the records in your working set. If you used any kind of selection to narrow your working
set from the entire file to just a subset of the file, then
UPDATE ALL updates just that subset.
O Issue the SELECT BY-FORM command and enter some selection criteria, then return to View mode and issue the UPDATE ALL command.
Once you are in Update mode, you can edit the record by pressing (ENTER).
The DELETE command in Update mode lets you delete records. This command takes one of two options. DELETE CURRENT-RECORD deletes the displayed record. DELETE ALL- UPDATE-RECORDS deletes all the records in the update set. When you mark a record for deletion with the DELETE command, the word DELETED appears in the status line for that record. The record is only “marked” for deletion; it is not actually deleted from the database until you leave Update mode,
i |
E
= 0
‘ r
Ts
= = —
f E
r
2 —
Ph)
= =
f
SB
#.
ft
—
i
Bf
it
rr
LASS
me
it
=
zl
WW dd ener
\y,
WWW WW
Entering and Updating Records 59
If you want to delete more than one, but not all, records in your update set, you can delete records individually, moving from one to another with the (NEXT), (PREVIOUS), (FIRST), and (LAST) keys.
If you change your mind and decide you don’t want to delete a record, you can issue the UNDELETE command.
If you have marked any records for deletion, Cornerstone won't actually delete them without giving you one last chance to change your mind. Before you leave Update mode, the system reminds you that you have a certain number of records marked for deletion, and asks you to confirm that you want to delete them.
See the message below Do you still wish to leave Update Mode? YES NO
UPDATE CURRENT-RECORD DELETED
1 record in the udpate setis marked for deletion. This record willbe permanently deleted when you leave Update Mode.
_ SSS ee ee Fila CUSTOMER Previous mode. Viow Files viewed: 1
60 Entering and Updating Records
| If you answer YES and press (ENTER), the records are
_ permanently deleted from the database: they're gone for good. Answering NO leaves you in Update mode, where you can undelete the records if you want. Simply choosing NO
_ does not undelete the records; you must do this with the UNDELETE command.
{x} Although Cornerstone warns you when it is about to delete a record, you must Judge for yourself what effect the deletion may have on your database. Keep in mind that the record you delete may supply values to other records in the database. For example, suppose the Omnifex corporation discontinued part J-1111. If the record for J-1111 were deleted from the Part file, the past orders for the part would no longer include a description of it, because the description was stored in the record you deleted.
|
The Update | When you enter Update mode, Cornerstone displays records Format using the format called Update. If there isn’t a saved format called Update for the file, Cornerstone displays the records in the DETAILED ALL-ATTRIBUTES format, which displays all the attributes in the file, one attribute per line.
| You can design your update format so that it shows as many or as few attributes as you want, and in any order. You can limit the update format to include just those fields in which you plan to enter a value. You can’t enter a value in a derived field, for example, so you may decide to leave it out of the display. For easier entry of values, you might want to place a field that appears at the end of the DETAILED format at the
| beginning. You might want to rearrange the fields so they print out in the right places on a pre-printed form,
y bh
r D j ¥ mt ote Tp —| a -
|r ~~
=a
fg
+
fa
2
q
r rir > oa
peel Geral bot B.
"
if
z
= a:
Entering and Updating Records
A quick way to design your own Update format is with the DETAILED ALL-ATTRIBUTES command, This command lets you change the order in which attributes are displayed. The DETAILED SOME-ATTRIBUTES command, on the other hand, lets you eliminate attributes from the display. Only the ones you list in the command line will appear in the display. Chapter 3, “Viewing Your Data,” tells you more about the DETAILED command.
If you want a more elaborate update form, such as one with more than one attribute ona line, or one with constant text, use the FORMAT command. This command brings you into Format mode, which you can learn more about in chapter 8, “Designing Forms.”
Saving and Using an Update Format
You can save any format you design and give it a name with the SAVE command. If you save a format under the name Update, Cornerstone will use that format every time you enter Update mode.
You can design other formats in Update mode for special purposes, but you may not want to see them automatically. Save these formats under names other than Update, using a name that indicates its purpose, such as Jnvoice or Label. To display your records in such formats, issue the USE command with the name you gave to the format when you saved it: USE INVOICE.
WWW ed a aa
If you forget to save a format you designed in Update mode, Cornerstone saves it for you automatically and gives it the name Previous-Update. Cornerstone saves only one format
Ww Ww
= under that name at a time, however.
62 Entering and Updating Records
=. mi
4 r
| Tips for Designing Update Forms
| When you create a format that you plan to call Update, make
| sure it includes all mandatory fields. After all, you can only
| enter a value in a field if the field is present! You can learn which fields are mandatory by looking at the file definition
_in Define mode. Also make sure that your Update format
displays field names, since a new record form without field
| names Is totally blank. The COLUMN command creates a
_ format without field names, so avoid it when designing your
AA
= —
t
= ~ =<
tT.
z ——s
Update format. ts Printing the You can get a printout of a single record or all the records a Update Set in your update set by using the PRINT command. The PRINT at
CURRENT-RECORD command prints the record currently displayed. The PRINT ALL-UPDATE-RECORDS command prints your entire update set. You might want to use this command if you just entered several new records or edited some old ones and want printouts of them.
Pi
r = =<
i
x
eke ee
*
ii
o- aa
SELECTING RECORDS
CHAPTER
64 Selecting Records
CHAPTER 5
CONTENTS
Introduction
Two Brief Examples
Selecting Flagged Records
Using a Selection Form
The CALCULATE Command
Viewing Related Records
Multiple Selection Forms
Negating a Selection Form
Saving Selection Criteria
The Selection Format
Printing the Selection Criteria
79
80
Tee ee ee ee ee ee ee |
Selecting Records
65
Selecting Records
|
When you view a file from Initial mode, your working set Introduction consists of all the records in the file. But often you want to
work with only some of the records in a file. For example,
you might want to write a report on March orders only, or
look at your West Coast customers, or pick out the records
of people who telephoned you this morning. Cornerstone
gives you several ways to narrow your working set to just
the records you want.
One of the most powerful ways to select records is tomake | Two Brief entries ina selection form. Here are two examples that use | Examples the Sample Database.
Suppose you want a list of your customers in the city of Cambridge. Since the View mode display shows only the customers’ names, you issue the COLUMN command to show the name and city of each customer:
COLUMN NAME CITY
Next, you issue the command SELECT BY-FORM
to enter Edit mode under Select mode, where you get a selection form to fill out. The values you enter in this form are used to select records for viewing when you return to View mode. In this case you want to select all customers who are in Cambridge, so you move the cursor to the City field and type in the name of that city:
CITY: Cambridge
You press (DONE) to leave Edit mode, then (DONE) again
to leave Select mode. When you return to View mode, Cornerstone applies the selection criteria and displays only those records with a value of “Cambridge” for the City attribute.
66 Selecting Records
| Here's a second example that continues the previous
| scenario. Your working set contains just the Cambridge
| customers, but now you want to see the Cambridge customers who have ordered $2000 or more worth of
| material from your firm. You issue the command SELECT BY-FORM
Cornerstone puts you back in Select mode, and displays the form in which you entered “Cambridge.” To change the form, you issue the EDIT command, and continue pressing (ENTER) to move the cursor down to the Total Purchases field. Here you enter an expression that tells Cornerstone to look for values of 2000 or more in this field: the number 2000 followed an ellipsis (...).
Sera
ra
He
.
TOTAL PURCHASES: 2000...
To select the records, you press (DONE) twice to return to View mode. Now the working set contains fewer records. To see that the total purchases are indeed from $2000 up, issue the command
COLUMN NAME CITY TOTAL_PURCHASES After displaying the list of Cambridge customers with $2000
or more of total purchases, you can get back all the records in the file by issuing the command
SELECT ALL The remainder of the chapter explains how to use all types
of selection in Cornerstone, including details on selection by form, of which you have just seen two examples.
a a
ee ee
Www A Aw a in
Wy
When you want to work with a small number of records, and you can tell which ones you want just by looking at them, you can select them quickly by flagging them. To flag a record in View mode you move the record cursor to it then press the (FLAG/UNFLAG) key. The flag appears as the letter “F’” to the left of the record. A flag is like a temporary check mark. It is not entered as data in a record, and it disappears when you issue the SELECT command or press (DONE) to leave View mode. You can flag as many or as few records as you want. To make your working set consist of the records you flagged, issue the command
SELECT FLAGGED If you want to work with all but a few records ina file, you
can flag the few records you don’t want to work with and issue the command
SELECT UNFLAGGED
You've seen how to select records using a selection form. A selection form for choosing September 1984's orders of $1000 or more looks like this:
ORDER DATE: 9/1/84 ... 9/30/84 TOTAL AFTER DISCOUNT: 1000...
Selecting Records 67
Selecting Flagged Records
Using a Selection Form
68 Selecting Records
ea
oo —
fl mote — =
To perform this kind of selection, issue the SELECT BY-FORM command from View mode. Now you're in Select mode, and the values you enter in the selection form determine which records will constitute the working set. You enter values in a selection form as you enter values in any other form: enter
_ Edit mode and type them in. Each field in the selection form corresponds to an attribute in the file. Cornerstone compares the value or expression you enter in the field with the value for the corresponding attribute in each record. A successful match means the record is selected.
No
rior te sy
y
=" t
= =
Properties of Selection Forms Each field in a selection form corresponds to an attribute in a file, but they differ in two respects.
O You cannot directly enter a value in a record for a derived attribute, but you can enter a value in its corresponding field in the selection form. This means you can Select records based on the value of a derived attribute.
O You can enter multiple values or expressions in a selection form even if its corresponding attribute can take only single values.
r
rf
CITT
onl 1
Af
Tee ee ee ee ee ee ee ee
Selecting Records
When you enter multiple values in a field in a selection form, Cornerstone selects those records whose value for the corresponding attribute matches at least one of those multiple values.
ORDER DATE: 9/1/84 ... 9/30/84 TERMS: COD
NET30 TOTAL AFTER DISCOUNT: 200...
To be selected, a record must meet at least one of the selection criteria in each field in the selection form. In the example above, Cornerstone selects only the records which have an order date from 9/1/84 to 9/30/84, and have terms of either COD or NET30, and have a total purchase after discount of $200 or more.
When you return to View mode, the status line displays the number of records that meet the selection criteria. Sometimes the number is expressed as a range. (For example, the range “11...16” means that at least eleven but no more than sixteen records meet the selection criteria. )
The range on the status line will become narrower as you scroll through the records or issue certain commands. To speed response time, Cornerstone checks to see whether a record meets the selection criteria only when it displays the record, or when you issue the SORT,
REPORT TOTAL, or PRINT commands (which force Cornerstone to check all the records). So the range becomes a single number only when you issue one of these commands, or when all of the records have been displayed.
If no records in the file meet the selection criteria you entered, Cornerstone warns you of the fact, and no records are displayed. Perhaps the fact that no records meet the criteria tells you what you want to know. If not, you can issue the SELECT BY-FORM command and return to Select mode to edit the selection form as necessary. You'll notice that when you already have some selection criteria in effect,
69
70 Selecting Records
CEE
| they are displayed when you return to Select mode; when | you have no selection criteria in effect, Cornerstone displays
_ a blank selection form for you to fill out. [=] When you select by form, you always select from the mtittal set, not the working set. The initial set consists of the records you entered View mode with. Generally, your initial set is the entire file, but if you entered View mode with the ONLY-RELATED-RECORDS option, the initial set consists only of those related records If you select by form and edit the selection form, the new criteria will select from the entire initial set, nof just from the records that were selected by the original form.
; i
—
J; >= a -
od -
)
+ +
i
To abort a selection in progress, press Ctrl-Break. You are then asked to enter YES to return to DOS, or No to continue the selection from where you stopped it.
¥ = @
f
=
Selection Expressions
There are several special expressions that you can enter into a selection form. Each provides a different strategy for selecting records.
>
Exact match. If you know the exact value you want to match, you can just type that value in the appropriate field. (Upper and lower case are treated the same.) For example,
CITY: Cambridge
PTT ITT
will select all records with the value “Cambridge” for the City attribute. You can use this kind of selection with values of any type.
s 4
Wild card. If you want to select records based ona partial match, or if you want to find a record, but you remember only part of a value, you can use an asterisk () to match any character or characters in a String.
hy
Tithe ee ee eee ee ee ee
4
yw
wy
SelectingRecords 71
For example, NAME: Cor«
7 4
matches “Cornerstone,” “cord,” “coral,” “corroborate”—any value that begins with “cor.” You can put the asterisk at the beginning of the value:
STATE: «A
This expression matches “CA,” “LA,” “MA,” “PA,” “VA” —any state whose abbreviation ends in “a.” You can also put an asterisk at each end of the value:
COMPANY: «ELECTRONICS»
This expression matches “Acme Electronics,” “Electronics Emporium,” “Bill’s Electronics, Inc.” — any value that has “electronics” anywhere in it. You can even put the asterisk in the middle of the value:
LAST NAME: *«Wsn«
This expression matches “Washington,” “Wilson,” “Newman”—any name that has a “w” somewhere before an “n.”
You can use wild card matching for String values only.
Ranges. If you want to match values that are within a certain range, use an ellipsis (...). For example,
PRICE: 100...
selects records where Price is greater than or equal to 100.
a — -
72 Selecting Records
a Of) oe 22 a=. pe er
rr
ORDER DATE: ... 1/30/85
=
selects records whose order dates are on or before January 30, 1985.
"
—w'\r >
LAST NAME: A...L
f
selects records whose Last Name field has a name beginning with anything from “A” through “L.”
Pal
The examples above are inclusive ranges, that is, they ie select the bounds of the range as well as anything in — between. To make a range exclusive (so it does not Pa select the bounds), just add the word EXCLUSIVE =, after the range expression. For example, a e LAST NAME: A... L EXCLUSIVE Pi
selects records whose Last Name values begin with
4 ‘. letters from “B” to “K.” — The expression —— SALARY: ... $29500 EXCLUSIVE
selects records whose Salary values are less than $29500.
mr
If it’s easier for you to think of open-ended ranges as “less than” and “greater than” or “less than or equal to” and “greater than or equal to,” you can enter the abbreviated forms for those expressions: LT, GT, LE, and GE. Here are some pairs of equivalent expressions:
-
ft
ms
WWW Wane nee
y
‘ma ht
\e
SelectingRecords 73
PRICE: LT 99.95 PRICE: ,.. 99.95 EXCLUSIVE
NUMBER OF ORDERS: LE 50 NUMBER OF ORDERS: ... 50
COMBINED SALARY: GT 50000 COMBINED SALARY: 50000 ... EXCLUSIVE
AGE: GE 65 AGE: 65...
In date and time fields, you can enter BEFORE and AFTER to designate open-ended ranges:
TIME SHIPPED: BEFORE 14:00 TIME SHIPPED: ... 14:00 EXCLUSIVE
ORDER DATE: AFTER MAY 11 ORDER DATE: MAY 11... EXCLUSIVE
You can use range expressions for String, Number, Date, and Time attributes.
Empty fields. To select records that have no value for a particular attribute, just enter the word EMPTY in that field:
CONTACTS: EMPTY
Non-empty fields. To select records that have any value for a particular attribute (that is, the attribute is not empty), use the asterisk («) alone. For example,
CREDIT CARDS CARRIED: «
will select records that have at least one value for Credit Cards Carried.
7A Selecting Records
(This is an example of » what not to do.)
NOT. If you want to select records that do not have a certain value or range of values, just type NOT in front of the expression:
CITY: NOT The Bronx
DUE DATE: NOT AFTER TODAY
Don’t enter more than one NOT expression in the same field: they will effectively cancel each other out. Records not selected by the first expression would be selected by the second. For example, if you try to select only records with the name “Harry” by entering
NAME: NOT Tom NOT Dick
the first expression will select Dick and Harry (because neither is Tom), and the second expression will select Tom and Harry (because neither is Dick). So the selected records are Tom, Dick, and Harry. You can solve this problem with the NEGATE command— see Negating a Selection Form, below.
Literal. Cornerstone interprets a “NOT” at the beginning of a String field to mean you want to select all records that do not have the value following the “NOT.” But what if you want to select records in which a certain attribute has the value “Not applicable”? When you enter the selection expression
NAME OF SPOUSE: Not applicable
Cornerstone selects records where Name of Spouse has a value other than “applicable.” To select records
with the value “Not applicable,” type a backslash (\) before the word “not.”
Lae eDD P Pen ee ee eee ee
\y
NAME OF SPOUSE: \Not applicable
The backslash tells Cornerstone to treat the word that follows literally, instead of as a special selection term. Similarly, you can use the backslash in front of BEFORE, AFTER, GE, GT, LT, LE, *...’ EXCLUSIVE, ‘«’, EMPTY, and ‘\’, all of which have special meaning in a selection form, as you have seen.
You can always press (OPTIONS) to see what kinds of selection expressions you can enter.
You can select records on the basis of more elaborate criteria by first creating a derived attribute with the CALCULATE command in Select mode.
Suppose you are working with the Customer file in the Sample Database and you want to narrow your working set to customers who purchase an average of $5000 or more per order, Since the total amount of each order is an attribute in the Order file (Total After Discount), you must derive the information from that file. You can create an attribute whose value is derived from the average (computed with the AVG function) of the total purchases. So, you would issue the CALCULATE command, which gives you a form in which you type the name of your new attribute and its derivation.
Name: AVG_TOTAL_PURCHASE Derivation: AVG(ORDER. TOTAL_AFTER_ DISCOUNT)
A new field will be added to the bottom of the form. Enter the range you want to select
AVG TOTAL PURCHASE: $5000 ...
Selecting Records 7O
The CALCULATE Command
Selecting Records
Viewing Related Records
Multiple Selection Forms
_ Another way to narrow your working set is to issue the VIEW command from View mode or Update mode. When you issue this command to view a related file, the system asks you whether you want to view all the records in that second file, ‘or only the records that are related to a certain record or group of records in the first file. At that point you can Narrow your working set by telling Cornerstone that you
| want to view, for example, records related to the flagged records.
Many of your selection needs will be met using just one selection form. But you can add additional forms to your selection criteria for more elaborate selections. Let’s say
you want to print address labels for a special mailing to customers who have placed more than 10 orders, or who have purchased more than $1000 worth of merchandise. If you entered these criteria into a single form, you would get only records that satisfy both criteria.
To select records that satisfy one or the other criterion, enter the first criterion
NUMBER OF ORDERS: 10...
in the form, then press (DONE) and issue the OR-FORM command. Cornerstone displays a new form where you can enter the second criterion,
TOTAL PURCHASES: $1000...
The second form is called an OR-FORM. It searches the working set for records that meet its criteria, then adds these to the records selected by the previous form or forms. In our example, the OR-FORM looks for records with Total
WWW es ade
wy
Selecting Records
Purchases of $1000 or more and adds these to the records selected by the first form, which selected records where Number of Orders was 10 or more.
Now let’s modify our example slightly. Let’s say we want to send the mailing only to customers in Boston. One way to do that is to add the criterion
CITY: Boston
to each of the forms already described. But a quicker way is to press (LAST) to move to the last form and issue the AND- FORM command. Like the OR-FORM command, it adds a new selection form to the set. But the AND-FORM tells Cornerstone to narrow the number of records that are selected. To be selected, a record must meet the criteria specified in the previous forms and the criteria in the AND- FORM. In our example, you would simply enter “Boston” in the AND-FORM to select customers who have placed 10 or more orders or have purchased $1000 worth of goods, and are located in Boston.
When you issue the AND-FORM command, the new form is added immediately following the form that is currently displayed. Since an AND-FORM selects records that meet the criteria in both the AND- FORM and in the previous forms, the order of the selection forms is significant. You'll probably want an AND-FORM to follow all other forms, so make sure the last form is displayed when you issue the AND- FORM command. (Similar comments apply to OR-FORMs. )
77
78 Selecting Records
Negating a Selection Form
If vou want to delete a selection form from a set of selection
i . . . ~ = r a criteria, issue the DELETE command in Select mode. You can use this command to delete either the current form or all the
selection forms ina set.
| When you have more than one selection form ina set of
selection criteria, the Select mode status line tells you how
| many forms there are in the set, which one is currently
displayed, and whether the form you see is an OR-FORM or an AND-FORM. You can move from form to form with the (NEXT), (PREVIOUS), (FIRST), and (LAST) keys.
Just as you can tell Cornerstone to select records which do not have a particular value (by typing NOT before the value), you can tell Cornerstone to select all records that would not be selected by the form by issuing the NEGATE command.
Returning to the example, suppose you have sent out your mailing to your Boston customers and now want to send it to the remaining customers. You issue the NEGATE command on the AND-FORM where you entered the criterion “Boston.” When you press (DONE), Cornerstone selects customers vot located in Boston.
The word NEGATE in the status line tells you that the form has been negated. To regain the original, unnegated form, you issue a second NEGATE command.
~~
Wa ee ee
Ww Ww Ww Aw Aa Aa Naat
When you type expressions in a selection form, you create
a set of selection criteria. You can save a set of selection criteria for later use with the SAVE command. When you issue this command you enter a name for the set of selection criteria. For example, to save the selection criterion
TOTAL PURCHASES: ... $1000
under the name Cheapskates, you enter the command SAVE SELECTION-CRITERIA CHEAPSKATES
You can issue the SAVE command in Select mode or in View mode. When you issue the command in View mode, you save the selection criteria currently in effect.
Once you have created and saved some selection criteria, you can use them later with the USE command in View mode or Select mode:
USE SELECTION-CRITERIA GOOD_CUSTOMERS
The effect of the USE SELECTION-CRITERIA command is the same as entering Select mode, filling out the form, and returning to View mode: Cornerstone selects the records that meet the criteria saved under that name.
r= A set of selection criteria is tied to a particular file. =2 You can USE a set of selection criteria on a file only if you saved it in that file.
When a set of selection criteria contains more than one form, the SAVE SELECTION-CRITERIA command saves all the forms under one name, and the USE SELECTION-CRITERIA command selects records on the basis of criteria in all the forms in the set you specify.
Selecting Records
Saving Selection Criteria
RSs)
(i)
80 Selecting Records
The Selection Format
Once you save a set of selection criteria, subsequent
changes are saved only if you issue the SAVE command again.
| You can save the edited selection criteria under the original name (in which case the original selection criteria are overwritten) or under a new name (if you want to keep the original selection criteria).
| If you want to change a set of selection criteria permanently, | you must issue the SAVE command again under the same name.
If you create some selection criteria and don’t save them, Cornerstone will save them automatically under the name Previous-Select. Cornerstone saves only the last unsaved selection criteria, so you should get in the habit of saving them yourself.
You can delete any selection criteria you have saved by issuing the DELETE SELECTION-CRITERIA command in Initial mode. For example,
DELETE SELECTION-CRITERIA ORDER PAST_DUE
deletes the selection criteria called Past_ Due that you created for the Order file.
In Select mode you type selection criteria in a selection form. Like the forms in View mode and Update mode, your selection form can display as many or as few attributes as you need, and in any order and arrangement you like. When you enter Select mode, Cornerstone displays the selection criteria in the format you created and named Select. If you don’t have a format called Select, Cornerstone displays the selection criteria using the DETAILED ALL-ATTRIBUTES format, which displays all the attributes, one per line.
sees
- i --
{
Tt f.
ft
=.
=
z.
ft
al +
if
fr
a +
aft
2 =I ji S —
Selecting Records 81
You can also display selection criteria using any format you saved in View mode or Update mode. In Select mode, issue the USE command to recall a format. For example,
USE FORMAT INVOICE
displays your selection criteria using the format you named Invoice.
—
3
a
|
4
a
_
| If a file doesn’t have a format called Select, you can create
nail one in Select mode with the DETAILED or FORMAT
—s commands. The DETAILED command allows you to re-order the fields in the form, and display as few or as many fields as
—a you need. You might want your selection form to display only
a the fields you plan to select on, or to display them ina
= special order. You might move a field to the top of the form
a if you plan to select by it frequently.
a
oe
a
ea
The FORMAT command lets you design more elaborate selection forms. This command brings you to Format mode, in which you can design your selection form to appear however you like. You can put more than one attribute ona line, or add constant text to the form. (You can learn more about Format mode in chapter 8, “Designing Forms.”)
Whichever format you choose for your selection form, Cornerstone will select records based on the values and expressions you enter in the selection form. The format does not affect which records are selected.
If you want to create a report using selection criteria and a report format that is sorted, or includes totals or subtotals, use the selection criteria first, then the report format. This prevents Cornerstone from having to compute the sorting, totals, or subtotals for the records that won't be selected.
When you use a format that does not display a field in which you typed
_ some selection criteria, those criteria are nevertheless in effect. : a Cornerstone will warn you if this should happen.
82 Selecting Records
Printing the When you produce a report, you may want to print the — Selection selection criteria used to create the report. You can get a Mm Criteria printout in Select mode with the PRINT command. This = | command lets you print one form or all the forms in the i
selection criteria. Loe
he,
TA
_it
=>
Ps
zs >) = a =
REPORTING
CHAPTER
84 Advanced Reporting
a)
CHAPTER 6 ol CONTENTS —_
J_I_T_4
Introduction 85 | a Using the — REPORT Pt Command &6 — i Totals | 86 aaa | hs Subtotals 88 — Aggregate — Functions 91 att What's a HT Report? 92 =— bi
Creating Derived a Attributes 93 — =
Putting It All _ Together 95 = = = ms
=
fii
”
| A
: aE t
"i
WNW NNW a Na aaa ta aed
By now you should be familiar with the following basic reporting commands: COLUMN, DETAILED, SORT, SAVE, and usE. If you're not, consult chapter 3, “Viewing Your Data.”
Your reporting needs will sometimes exceed the capabilities of these commands. You may want to include subtotals for different groups of records, or totals for the entire report, or both. Perhaps you want a report arranged in a way that’s neither columnar nor row-oriented. Once you've learned the basics of Cornerstone, you can use advanced reporting features to produce just the report you want.
Cornerstone’s interactive style also makes it easy for you to develop sophisticated reports gradually, one step at a time. This saves you time at the outset, because you don’t have to plan out every last feature, and later on, because it’s easy to make changes.
The REPORT Command
This chapter focuses on the REPORT command, found in the View mode menu. REPORT actually consists of several options that allow you to produce summaries (subtotals and totals), calculate derived attributes, and control the layout and design of the report. The layout and design option is only discussed briefly in this chapter; you will find a full discussion in chapter 8, “Designing Forms.”
Advanced Reporting 85
Advanced Reporting
Introduction
86 Advanced Reporting
Using the REPORT Command
Totals
To use any of the reporting features, select REPORT from the View mode menu. A new menu appears, listing the command options, and you must select one. Remember: you
_ can press (HELP) to get more information about the various
options. The table below briefly describes each of the reporting options. TOTAL. Performs summary operations on all records in the current working set.
SUBTOTAL. Performs summary operations on groups of records.
HIDE. Suppresses display of report elements (e.g., headers, subtotals, totals, records).
sHow. Displays hidden report elements.
CALCULATE. Defines new derived attributes that can be used in the report.
FORMAT. Controls the layout and design of any report element.
Totals present summary information about all the records in the working set. For example, suppose you want to see the total number of units and total value of orders from all your customers. Using the Customer file from the Sample Database, enter the two commands
COLUMN NAME TOTAL_UNITS TOTAL_PURCHASES REPORT TOTAL SUM TOTAL_UNITS SUM TOTAL_PURCHASES
==}:
oP
— —s
nae ~_ ~
=
4 —
, ow if
7”
hy
Put,
Advanced Reporting 87
Select a command from the manu, Use arrow keys or type the command.
sa UPDATE SELECT VIEW COLUMN DETAILED USE SAVE REPORT SORT PRINT ALL-DONE VIEW CUSTOMER 16 Records NOT SORTED . NAME TOTALUNITS TOTAL PURCHASES Tosh‘s Service Center 242 $ 1,942.40 Roxbury Auto Body and Repair 117 $ 819.00 Hal's Foreign Auto Repair 141 $ 963,20 Lexington Auto Service 433 $ 2,538.00 Concord Bus Line 493 $ 4,891.64 T Motors 308 $ 1,895.32 Hanover Service Station 478 $ 6,244.20 Bunker Hill Garage 416 $ 4,508.70 Cambridge Commons Cab Co 77 $ 2,263.55 Ronnie’s Rickshaws 0 $ 0.00 H 3,15) $ 28,815.61
File. CUSTOMER Previous mode; Initial Files viewed: 1
The accompanying figure displays the results of these commands. The first command creates a columnar report with the desired attributes. The second command creates the totals, which are displayed at the end of the report. This second command line may seem complex, so let's look at it piece by piece.
When you select REPORT TOTAL from the View mode menu, 4 new menu appears. This is a list of all the aggregate functions available with the TOTAL command. Cornerstone offers a wide range of choices, including (arithmetical) sum, (arithmetical) average, standard deviation, variance, minimum value, or maximum value. See the section below, Aggregate Functions.
sWW i
1+ \s
\
| ]
88 Advanced Reporting
When you use the TOTAL command, you choose an — aggregate function and then choose the attribute you want it it applied to. In the example, the aggregate function SUM is aa
_ used twice, once for TOTAL_UNITS and once for TOTAL Lis PURCHASES. You can include as many aggregate functions as WT you want. When you've chosen all you need, press (ENTER). i
| Cornerstone displays the totals at the bottom of the report. Hl You can recompute the totals at any time by issuing the a command REPORT TOTAL without selecting an aggregate ‘
_ function. This is useful if you’ve added new records or =. performed a selection after computing totals, and you want — to compute new totals for your new working set. ay aa
Subtotals Subtotals present summary information about groups of as records. For example, now that you've seen total purchases — for all your customers, you might want to check how much is
was ordered by customers in each city. You could select just the records from a particular city and then compute the totals, repeating this operation for each city, but this would be cumbersome. Also, you probably want all the information to appear in the same report. Subtotals provide the solution.
ah
> 4
Start with a report showing the relevant attributes. Subtotals or totals can be computed for any attribute, whether or not it’s in the report format, but for purposes of clarity you will generally want to include any attribute that is being summarized. For the above example, start with the Customer file, and enter the commands
COLUMN NAME TOTAL_UNITS TOTAL_ PURCHASES REPORT SUBTOTAL SUM TOTAL_UNITS SUM TOTAL_PURCHASES
=; i = =
Advanced Reporting 89
|
a 1
As you see, the REPORT SUBTOTAL command works much | like the REPORT TOTAL command. You tell Cornerstone which aggregate function(s) to use (SUM in this case), and which attribute(s) to apply them to. After this point, though, the SUBTOTAL and TOTAL commands differ.
=~ i
When you enter the subtotal command above, Cornerstone prompts you to select the attribute for grouping the subtotals. This is called the grouping attribute. In this case you want all records from the same city to be grouped together, so select crry. (If your records aren't sorted by City, Cornerstone will ask you whether you want to sort before calculating the subtotals.) The figure below shows the new report with subtotals.
Selact a command from the menu Use arrow keys or type the command.
5] UPDATE SELECT VIEW COLUMN DETAILED USE SAVE REPORT SORT PRINT ALL-DONE VIEW CUSTOMER eMatcterelaths SUBTOTALED *
NAME TOTALUNITS TOTAL PURCHASES
sR aN Ror ng gw mn fee rs ae rs ry
Subtotals for Boston 1,035 $ 11,716.10
Www a a tg lg
— Bergman's Antique Autos 0 $ 0.00 | Roman Olds Dealers 291 $ 2,055.80 2 Tosh’s Service Center 242 $ 1,942.40 —_ Concord Bus Line 493 S$ 4,891.64 f T Motors 308 $ 1,895.32 FF Cambridge Commons Cab Co. 177 $ 2,263.55 ——_ Subtotals for Cambridge 1,511 $ 13,048.71 | Lexington Auto Service 433 $ 2,538.00 Subtotals for Lexington i
“ ——————— i
sal File: CUSTOMER Previous mode: Initial Files viewed: 1
90 Advanced Reporting
as =
| Perhaps you've noticed a similarity between sorting and (> | subtotaling. « Sorting orders the records on the basis of a particular attribute or attributes, grouping together those with the same value. Subtotaling computes summaries for | groups of records with the same value for the grouping attribute. =
ee =>
Since Cornerstone computes and displays subtotals each time the grouping attribute changes, you will generally want to sort before computing subtotals. In the example, if customers from different cities are interspersed, Cornerstone will compute subtotals each time the city changes.
Td
Ff
m4 =
As with REPORT TOTAL, if you issue the REPORT SUBTOTAL command without selecting an aggregate function, | Cornerstone will recompute the subtotals.
‘"
= a = > = a
7 y
Advanced Reporting
The table below describes the aggregate functions that you Aggregate can use with the SUBTOTAL and TOTAL commands. Functions Aggregate functions used with the TOTAL command are
applied to all records in the working set. Aggregate
functions used with SUBTOTAL are applied to each group of
records having the same value for the grouping attribute.
sum. Computes the sum of values in the group. For Number or Integer attributes only.
AVERAGE. Computes the average of values in the group. For Number or Integer attributes only.
MINIMUM. Computes the smallest value in the group. MAXIMUM. Computes the largest value in the group. FIRST. Takes the first value from the group.
LAST. Takes the last value from the group.
count. Counts the number of records in the group. Unlike all the other functions, COUNT doesn’t require an attribute since it just counts the records.
STANDARD DEVIATION. Computes the standard deviation for the group. For Number or Integer attributes only.
Www ly le a ld le ie eee
——
s
4
a
VARIANCE. Computes the variance for the group. For Number or Integer attributes only.
\
| \y
f -
{ —
>
92 Advanced Reporting 2s See mers
T_T 7.
ra
NET PRESENT VALUE. Computes the net present value for the group. For Number or Integer attributes only. This function requires you to supply an interest rate. Cornerstone will assume that the records are consecutive time periods, and that the first record
is time zero. Be sure to set the interest rate appropriately (12% per annum ts .12 if each record represents a year, .01 if each record represents a month). Select only those records representing the time periods you're analyzing, and sort by date.
ST_S_if
What's a You've now encountered all the elements of a Cornerstone Report? report: record data, header (created automatically by the COLUMN command), subtotals, and totals. Cornerstone considers each of these as a separate report element. A report can contain any or all of these elements.
Whichever elements you include in the report, Cornerstone saves them all together with SAVE FORMAT, and restores them all with USE FORMAT. Two other pieces of information are also saved with the report format: sorting specifications (see chapter 3, “Viewing Your Data”), and printing characteristics (see chapter 7, “Printing”).
Hiding Report Elements
Cornerstone allows you to work with each report element individually. You can modify the appearance of any report element: For example, you can change the location and
= = = = = = = = .
Www eee ee
yi
WWW
\4q—— 1 y:
e
Advanced Reporting
width of fields, add labels and constant text, or suppress field names. For details on using Format mode to alter the appearance of a form, see chapter 8, “Designing Forms.”
You can also suppress the display of individual report elements. For example, suppose you will be going over last year’s sales figures with the board of directors, and want to show them only the subtotals for each city and the overall totals. (They're not interested in the gory details. ) In this case, you would want to hide the record data. Starting with the earlier report, just select
REPORT HIDE RECORDS
HIDE does not affect the report element—it only removes it from the display.
The SHOW command redisplays previously hidden report elements. To show the record data, just enter
REPORT SHOW RECORDS
You can even save a report with hidden elements and display them later when you use the report.
The CALCULATE command lets you compute new data on the Creating basis of existing data in your files. Working with the Order Derived file, you might want to compute the average unit price for Attributes
each order. Just enter
REPORT CALCULATE NEW-FORMULA Cornerstone then asks you for a name and a derivation (formula). In this case, you could enter
Attribute Name: Average—Unit_Price Derivation: Total_After_ Discount / Total_Qty
93
94 Advanced Reporting
Using REPORT CALCULATE is identical to defining a derived attribute in Define mode. This command saves you time by
allowing you to define an attribute without leaving View _mode.
Any new attribute you create with REPORT CALCULATE is appended to each record, and is permanently added to your
' file. You can delete it in Define mode, as you would any
|
other attribute.
You can also use REPORT CALCULATE to explore and test out predictions. With the REPORT CALCULATE OLD-FORMULA option you can change the derivation of an attribute, examine the results, and then change it back, if necessary.
For more detailed information on derivation expressions and the use of derived attributes, see chapters 9 and 10, “Planning Your Database” and “Defining Your Database.” Also refer to “Entering Information Into Forms” in Owner's Handbook II.
Designing Custom Report Formats
The REPORT FORMAT command takes you to Format mode, where you can freely modify the appearance of any of the report elements (record, header, subtotal, total). In Format mode you can modify the appearance of the report element as a whole (such as where the fields appear) or of the individual fields (such as the width of the field display). Chapter 8, “Designing Forms,” describes Format mode.
a =
‘q a =. =. = ca — = = = = = = = s =
Advanced Reporting 99D
The reporting process involves four basic steps: Putting It All Together
© View the file you plan to work with.
© Select the records you want. (This may be all the records in the file, or only those records meeting specific criteria. See chapter 5, “Selecting Records,” for details. )
0 Design the report format (appearance). This includes choosing which report elements you want (records, header, subtotals, total), which attributes should be displayed, and how each element should appear.
O Print the report, if necessary.
With Cornerstone, steps 2 and 3 are separate and can be performed in either order. You could select just the record for Roxbury Auto Parts, design a report format (even including subtotals and totals), and then select all the records. Or you could design a report format and then eliminate unnecessary records. It’s generally better to select the appropriate records first and then use the desired format, since you will have to re-sort or recompute subtotals after selecting records.
eee ee ee
——
ti \
wu Wk
(
i F am -
Paar a]
CS = = as =
CHAPTER_
98 Printing
CHAPTER 7
CONTENTS
a = = a
Introduction 99 }
Printing —
Reports | 99 ft
Editing the Print | hi
Form 100
The Print Mode
Menu 103
Tips on Printing | 105
Printing in Other Modes 108
ALT TE PT PPT PS
Printing 99
Printing This chapter discusses the various ways of printing Introduction information with Cornerstone. The major use of printing is, of course, to produce printed reports. But Cornerstone can also print a variety of other information, including file definitions, newly updated records, selection forms, and empty data entry forms. Although one hears talk of the “paperless office of the Printing future,” most computer applications, and most database Reports
applications, still involve printing. You might, for example, want to print periodic reports (such as monthly sales figures), mailing lists, order forms, or form letters. All can be easily printed using Cornerstone.
The term printing a report means printing data from a set of records in View mode. The report can include one or more report elements (records, header, totals, or subtotals).
The basics are simple. Select the PRINT command from the View mode menu. PRINT PRINTER prints on your printer; PRINT DISK writes to a disk file.
Cornerstone has many built-in defaults for printing your report (for example, spacing, page size, and margins). You can modify these as you like.
swW WW dee
Cornerstone also has information about a variety of
a printers. You can add information about your printer, if i necessary. Both the report layout and printer options can be mm
modified with the PRINT SETUP command.
Editing the Print Form
"When you choose PRINT SETUP you are placed in Print mode
with a new set of commands and a form to edit, called the
print form. This form contains quite a few options (it extends
beyond the first screen), which determine the appearance
| of your report. To edit the form, just press (ENTER) to issue the EDIT command.
The following is a brief description of each printing option. Most are self-explanatory. <> Remember, you can always press the (HELP) key for advice. =<
Number of copies. Sets the number of copies to be printed.
Left margin. Sets the number of spaces for the left margin. Notice that there is no right margin option. Your report format determines the width of the report (up to 255 characters per line).
Top margin. Sets the number of lines for the top margin.
Bottom margin. Sets the number of lines for the bottom margin.
Number of lines per page. Sets the page length. Most printers use 6 lines/inch, therefore the default is set at 66 for 842” x 11” paper. You can use this option in conjunction with Maxinnun number of records per page
to generate mailing labels. See the section below, Tips on Printing.
Print header on every page? Answer “Yes” or “No.”
Print title on separate page? Answer “Yes” or “No.”
-
PAP PPP Pay
Wile We ee ee ae ee
ay
Www
- =
“| a4 ‘S
|
Keep records together? Answer “Yes” or “No.” Answering “Yes” will prevent Cornerstone from splitting a record on to two pages, where possible.
Maximum number of records per page. Sets the maximum number of records per page. Entering “1” starts each record on a new page.
Number of blank lines between records. Sets the number of blank lines after each record. (You can also insert blank lines into your record form itself, using REPORT FORMAT. This often improves the legibility of the report. )
Start new page for subtotal group? Answer “Yes” or “No.” Answering “Yes” starts a new page after each set of subtotals.
Extra lines between subtotals at top. Inserts the specified number of blank lines between a group of records and its subtotal information.
Extra lines between subtotals at bottom. Inserts the specified number of blank lines between subtotals and the next group of records.
Page number style. Determines the position of the page number. Options are “none” (no page number), “top,” or “bottom.”
Print date on top of page? Answer “Yes” or “No.” Answering “Yes” prints the current date at the top of every page in the report.
Printing
101
102 Printing
Use pre-printed form? Answer “Yes” or “No.”
Answering “Yes” sets a number of characteristics for using pre-printed forms. See the section below, Tips on Printing, for details.
Stop every page? Answer “Yes” or “No.” Answering “Yes” interrupts printing after each page, allowing you to feed individual sheets of paper.
Title style. Specifies the style of typeface for printing the title. Options are plain, bold, underline, italics, shadow, and special (defined by you). Any choice must be supported by your printer.
Header style. Same as title style, for printing the header.
Record style. Same as title style, for printing the records.
Subtotal style. Same as title style, for printing the subtotals.
Total style. Same as title style, for printing the totals.
Once you're in Print mode (via the PRINT SETUP command) you can print your report in either of two ways: by selecting PRINT from the Print mode menu or by returning to View
mode (by pressing (DONE)), and selecting PRINT from that menu.
Cornerstone saves whatever printing instructions you establish when you save the report format in View mode. If you wish to save the instructions (as you will if you've gone to the trouble of changing them), press (DONE) after editing the print form, then select SAVE FORMAT.
Wee ee
a Wa
yi
aw
: i
This section describes the other commands in the Print mode menu: DEVICE, FORMAT, SHOW, and PRINT.
Devices
The DEVICE command is used to supply information to Cornerstone about your printer, such as how it underlines, italicizes, and issues a carriage return. In addition, DEVICE can be used to select a printer if you have more than one attached to your system, or to specify the style of printing (draft or letter-quality).
A device is a set of printer instructions, and each device has its own name. You can specify which device (which set of printer instructions) to use whenever you print. If you don’t choose a device, Cornerstone will use its default.
The DEVICE command has two options: CREATE and ALTER. DEVICE CREATE is used to enter information that will be stored under a new device name. DEVICE ALTER is used to select or change the information about an existing device.
Pre-defined devices for several printers have been supplied with Cornerstone. DEVICE ALTER will display the list. If one of these is your printer, complete the command by selecting the appropriate name. This will establish that printer as your current device, which is the device automatically chosen by Cornerstone for all printing operations. You can check the current device name by referring to the Print mode
status line.
Even if your printer isn’t listed, one of the existing devices may meet your needs. Before defining a new printing device, try selecting one of the pre-defined devices. If this doesn’t work well, consult your printer manual for specific information.
Printing 103
The Print Mode Menu
104 Printing
Selecting either DEVICE command puts you in Edit mode, | editing the device form. The various elements of this form are described below:
Name. This is the name for the device. In most cases you'll just want to use the name of your printer.
Physical device. This specifies the physical location of the output device. The options are LPT1 (use this if your printer is using the normal printer port) or LPT2.
Initialization string. This specifies an initialization string to be sent to the printer when you start printing. This is useful if you need to initialize the printer or select options.
Reset string. This specifies a reset string to be sent to the printer when you are done printing. This is useful if you need to reset any options you set up with the Jnitialization string above.
Line feed string. This specifies the hexadecimal characters that the device interprets as a line feed. The standard value is carriage return followed by line feed, which is ODOA in hexadecimal.
Print style. This section of the device form specifies the hexadecimal strings for turning on or turning off various print style instructions such as boldface, underline, italics, shadow, and special.
Form feed string. This specifies the hexadecimal characters that the device interprets as a form feed. The standard value is hexadecimal OC.
= a a = “5 on — 5 5 tia = in i lin, = = S —
Printing
te
T
ld
yw ty ly
=
44
as |
are
ro | } :
= a x ,
=
1 ht
wat ae
If you want to delete unnecessary devices (you'll only need the ones for your printers), select DELETE DEVICE from the Initial mode menu.
Titles The FORMAT command from the Print mode menu is used to create or change a title for your report. The SHow command is used to display the title. Titles can be constant text, for example “MONTHLY REPORT,” or they can contain summary information computed from records in your report.
For example, you could insert two summary fields, MIN ORDER—DATE
and MAX ORDER_DATE, into your title form, and have the ttle:
“SALES Sh ROM === rT © eee For more information on this use, see chapter 8, “Designing Forms.”
The PRINT command can be used to “print” to a disk file,
as well as to your printer. If you select the PRINT DISK command, Cornerstone creates a text file on your current data disk. The PRINT DISK command requires you to enter a name for the text file. You can simply enter a file name or you may enter an entire file specification, with a drive designator, path name, file name, and extension. The name you enter is subject to the usual restrictions on DOS file specifications (listed in your DOS manual).
Mailing Labels Tips on Printing To produce mailing labels start with a row-style format
containing the appropriate fields. Use the SET-CHARAC
command in Format mode to suppress display of the field
names (you don’t need “Name:” before each person's
name). You can establish the appropriate label size in two
different ways.
106 Printing
0 You can make the form the same number of lines as the label (adding blank lines if necessary); or
© You can set the page size in the Print form equal to the number of lines in the label, and set the maximum number of records per page to “1.”
The first approach is easier if your addresses do not contain multi-valued attributes (such as street address). The second approach is safer if you do have multi-valued fields.
Cornerstone will print only one column of labels per page.
One easy way to display the city, state, and zip on the same line 1s to define a new attribute that combines these three attributes, using the STRING function. For example, create a derived attribute called City_State_Zip, and enter the derivation expression: STRING/(City,", "|State,” ",Zip). This puts a comma followed by a space between the city and state, and two spaces between the state and zip.
Pre-printed Forms
Cornerstone has some special features to let you conveniently insert data into the correct place on pre-printed forms. If you answer “Yes” to the question Use pre-printed form? Cornerstone makes two changes to its normal handling of forms:
TAP ATA AAA AAA aaa
First, it suppresses display of all field names. (If you need to add a field name to your form you can always use constant text in Format mode. )
Second, Cornerstone prints each field or constant text starting on the exact line specified in your format. How does this differ from the normal case? Normally Cornerstone inserts as many lines as are necessary to display the information from your records. For example, if your form includes a multi-valued field or subrecord, Cornerstone will make additional room for each value. Cornerstone will also insert extra lines for any single-valued attribute whose
. = = — 5 *
Printing
ee eee ee eee
a ee
pet
i.
ms
value is longer than the specified field length. In this sense the length of the normal Cornerstone form is not fixed, but changes with the length of the data.
When you choose the pre-printed form option Cornerstone prints each field (or constant text) starting on the exact line you specify in your format. Values will be extended, if necessary, into subsequent blank lines, but are terminated at the next line containing another field or constant text.
Sometimes a blank line in your Cornerstone format corresponds toa pre-printed or otherwise reserved area in your existing form (“Don't write in shaded area” or “For office use only”). To prevent extension ofa field into such an area, you must put some constant text on that line in the Cornerstone format. A period or dash in the corner will suffice,
Chapter 8, “Designing Forms,” tells you how to set up your data and design Cornerstone forms for printing on your pre- printed forms.
Form Letters
You can even print form letters with Cornerstone. The repeating part of the form letter could be either constant text (entered in Format mode) or a string attribute (or attributes) that you copy from record to record with the COPY command (crrL-p). Using attributes allows you to make editing changes in individual letters, which can be useful if there are slight changes from one letter to another. Fields containing the variable information (such as name or address) can be placed wherever necessary in the form.
You may find it more convenient to use a separate word processing system to create and process your form letters. You can still use Cornerstone to store names and addresses. The chapter on the Convert utility in Owner’s Handbook II
Printing
Printing in Other Modes
_ describes how to select Cornerstone data and merge the
| data with other word processing and form-letter programs.
|
| Interrupting Printing . If you start printing a report and want to terminate printing | prior to completion, just press (CANCEL) or Ctrl-Break.
. . . The PRINT command appears in other modes besides View. In each it performs a specialized function. More information
_ about the following uses can be found in the corresponding
chapters.
Define. The print command in Define mode prints the file and attribute definitions.
Update. The print command in Update mode prints either the record currently being updated or all records in the update set.
Select. The print command in Select mode prints either the current selection form or all selection forms in the current selection criteria.
Format. The PRINT command in Format mode prints either a blank copy of the form (one without data) or a grid that marks off horizontal and vertical spacing. A grid is useful when designing your Cornerstone forms to match existing pre-printed forms.
vc a i sk Ye)
CHAPTER _
DOSE IAE OWNS
cc Mec AM oho OM a Wy Wa Wk Wl Aid Wi Ml kW Wa a a
110 Designing Forms
CHAPTER 8
CONTENTS
Introduction
Overview and Concepts
Using Format Mode
Commands
Manipulating Fields
Setting Field Characteristics
Manipulating Lines
Changing Whole Forms
Manipulating Subforms
120
125
The CALCULATE Command
Printing Subtotal,
Total, Header, and Title Forms
Working with Two Forms
Leaving Format Mode
Tips for Form Designers
When to Use Format Mode
126
126
130
130
131
132
133
a 7 = = ~ = — : i: = : = =
4
aay
=H j W
ee
Me
—
Vd
a)
TH
2 a
aw
— ii
|
sin tile a4
i A
DesigningForms 111
Designing Forms
As you become a more demanding Cornerstone user, you Introduction will want to create your own custom reports. You may
decide that you don’t like a field in a particular place and
would rather move it somewhere else, or that you want to
draw a line under a particular field. This chapter introduces
Format mode, which lets you do this sort of thing and more.
Although Format mode gives you complete control over the appearance of your data, Cornerstone is designed so you can handle most situations without invoking Format mode. The COLUMN and DETAILED commands provide an adequate solution for most reporting needs. It’s when you want a special report, one that must look perfect, that you need the flexibility Format mode provides.
Format mode is fun to use once you become familiar with it. You can experiment with different designs, and when you've arrived at one you like, you can save it for future use. You need never worry that anything you do might harm your data—it can’t. The figure below contrasts a detailed form (created using the DETAILED command) with a form designed in Format mode.
112 Designing Forms
Form created using the DETAILED command
User-designed format containing the same information
NAME: Medford Motor Service STREET: 975 Abbey Rd
CITY: Medford
STATE: MA
_ ZIP: 17013-9994
AREA CODE: 617 PHONE: 848-4003 EXT: 84 CONTACTS: Charles Brown Linus Sewell TERMS: COD DISCOUNT: .000 NOTES: Charles manages the hockey team that put the boss's son's team out of the divisional playoffs last season, TOTAL PURCHASES: $ 377.00 NUMBER OF ORDERS: 1 TOTAL UNITS: 27
Customer Profile
Customer name: Medford Motor Service Address: 975 Abbey Rd. Medford, MA 17013-9994 Phone: (617) 848-4003 x84
People to Contact; Charles Brown Linus Sewell
Standard terms: COD Usual Discount: .000 , Order history information
Total purchases: S$ 377.00 Number of orders: Total units:
Notes: Charles manages the hockey team that put the boss's son's team out of the divisional playoffs last season.
Hy)
j
De
AR ST ST SS ST
AT IT
TAT AT TAT TY IP JIT
— >] - ‘ y
ly We te te i We
i td |
i |
\
|
iad
y
Sy Sy la
=| = \ be |
4 oy
4
wy
( \ aad ‘a
You can get to Format mode from several different places in Cornerstone. Which mode you come from determines the form(s) you can format.
From View mode: The REPORT FORMAT command lets you format any element of the report: records, subtotals, total, or header. You select the form you want to format as part of the command. (You can even format two forms at once—see the section below, Working with Two Forms. )
From Update mode: The FORMAT command in Update mode lets you format the update form, which you use to create or change records.
From Select mode: The FORMAT command in Select mode lets you format the selection form, which you use to enter criteria for selecting records.
From Print mode: The FORMAT command lets you format the report title, which can be used when printing reports.
As you can see, the command you use to get to Format mode is generally FORMAT. The only exception occurs in View mode, where the command 1s REPORT FORMAT. Whichever mode you come from, once you're in Format mode, designing the form works pretty much the same way. There are some minor differences when you're working with subtotal, total, header, and title forms.
What’s a Form?
You use forms throughout Cornerstone to supply information that would be inconvenient to enter on a command line. For example, you fill out a form whenever
Designing Forms
Overview and Concepts
113
114 Designing Forms
i &
iM
j
you create a new record or supply Cornerstone with | information—such as when you customize the system or define a new attribute within a file.
| ORDER RECORD
ORDER FORM
57 Deer St. Bedford
MA 01111
*OMNIFEX Corporation
eeneee eeeeeneeeneeneeeeeenvrneanenne
ORDER NO; 392
J-1126 * JOAN DOE Dil Filter D uble Stage ed 5 PART NUMBER PART DESCRIPTION QTY AT PRICE 5.00 Oil Filter 15,00 J-1926 Double Stage 5 5.00 15.00
Transmission
ine LE-COE pees. lwid—Automatic Simm 3 fe 99 f 2 mol! Transmission Fluid--Au 3 99 2.50
Total After Discoupt: 17.50
17,50
A form is a window or template that shows some of the data in your files. The form merely determines the appearance of the data. Nothing you do in Format mode has any effect on the data in your database. For example, if you delete a field from a form, this simply tells Cornerstone you don’t want to see that information. The data is still there; it simply isn’t (> | shown, c> The format used to display data is independent of the data itself. <1
In Format mode, forms are displayed without any data in the fields. Instead, underbars appear where you would enter data. This lets you see how wide the field is. All forms contain the same basic elements.
se, 5 FE “a x Sr =, : = =
WW
>
\,
—— WW —
uy
“y
ml
&
_ |
Wy
ie po |
W
%
Wy \y
oS Ay YY
\y
os Yi ta
Designing Forms
Use FUNCTION keys or TYPE in text; press <COMMAND>= to issue commands. |
2 ORDERNO: _______ *OMNIFEX Corporation wee ew wanes ee a | ee ee re od —|
NAME) 255 2c Mirae Ue er agus Pen Oe STREET SC 2S She Sea aa ed Soa eA Lines CIV $2 = eS ee STATES 22" Zi Pearse AREACODE ___ | PHONES eae EXT2s Se ORDER DATE: Ve eee om Fields TERMS {p20 = eee DISCOUNT: __-~____— PART NUMBER ’ PART DESCRIPTION ; ary AT ; PRICE Ne ay Dy om 29 mein, Stir tres ame gesetare teratier oe Bw Poet af YO SS mR mr Og ERS — he et awe wee ww ee ee td en a | ~------ 1 1 ' ' ——— = Subform File. ORDER Previous mode: View Files viewed 1
Fields. Fields are holes or slots used to hold data. You enter data where the underbars appear in Format mode. For records, each field corresponds to an attribute in your file. For example, the Name field in the format in the figure above corresponds to the Name attribute in the Order file. When you use commands such as COLUMN and DETAILED, Cornerstone uses the names of the attributes you select as field names, but there’s no rule that says the field name must be the same as the attribute name. You can change the name of a field to be whatever you like. (You'll learn how to do this later on in this chapter. ) <= With Cornerstone, fields and attributes = I are not the same thing. Attributes belongs to files; fields belong to forms. A field is simply a place to display or print the value of an attribute. =
BS
116 Designing Forms
.
J
’
¥
LU
i
| j nel ¥
Constant text. Forms can contain text that appears whenever the form is displayed or printed. The text might give the form a name or supply instructions for someone filling out the form. You can put whatever text you want in forms. In the figure, “O MNIF E X Corporation” is constant text, as is the row of asterisks across the top of the form. The text is called constant because it’s the same for every record, unlike the data used to fill out the form, which can be different for each record.
Lines. Format mode provides commands that let you create, move, and delete lines within the form. Each line in a form can contain as many fields or as much constant text as fits on the line, or none at all. The number of lines that appear in the form in Format mode is sometimes less than the number of lines that appear when the form is filled out, since Cornerstone creates extra overflow lines when a value is too long to fit in its field.
Subforms. A form may contain subforms corresponding to subrecord attributes (such as Line Items) in the file. The fields that appear in the subform correspond to (sub)attributes in the subfile. A subform 1s like a form within a form (just as a subrecord is like a record within a record). Cornerstone uses the subform as a template to display each subrecord.
= : = 7 Tt az a ~ — —
You needn’t worry about the size of the form unless you are printing reports that must go ona pre-printed form, since Cornerstone automatically handles subrecords, multi-valued fields, and values that overflow.
be
ee
ma |
rane
la
my
MW
|
4a
pies
‘Y
Wa
“= |
i‘
‘\ my
i
1"
\
www
_—
S|
Y
4
a
Designing Forms 117
When you first enter Format mode, the cursor is positioned | Using Format on the first field in the form. You can move the cursor Mode
around the form with the arrow keys, or you can use the
motion keys (such as (NEXT) and (PREVIOUS)) to move among
the fields in the form. You can also use any of the scrolling
keys to scroll the display.
In Format mode, if the cursor doesn’t lie within a field, anything you type simply goes into the form as constant text. For example, if you wanted to create a title for your form, you could just move the cursor to the top and type the title. You can also use most of the editing keys used in Edit mode, such as (RUB-CHAR), (RUB-WORD), and (DELETE). You can also use the (INs/ovs) key to change the type-in style, just as in Edit mode. You cannot type text into a field, since this is where data is displayed.
You can issue commands in Format mode, but you must first press the (commaNnp) key. Otherwise, Cornerstone will treat what you type as constant text. When you press (COMMAND), Cornerstone displays a menu of commands, and you select the command you want in the usual fashion. For convenience, many of the commands in Format mode are assigned to control keys, so you only have to press the key to execute the command. Your Quick Reference Card lists the commands and the keys they are assigned to.
118 Designing Forms
Ay
j
a
Commands | This section contains descriptions of all the commands in Format mode. You may want to skim it first just to get an idea of what's available. You can consult it again later if you | need more information about a particular command.
| The number of commands may seem large at first, but you'll find that you use some more than others, and that you can accomplish most tasks using just a few commands.
Manipulating The following commands work with a field in the form. To Fields use these commands, first move the cursor to the field, then press the (COMMAND) key and issue the command.
HAE EF Ae
SS el = a a a: a z= aa i Si
>
Designing Forms
INSERT FIELD
The INSERT FIELD command lets you add a new field to the form. When you issue the command, you must select an attribute from the menu. This is the attribute whose value is to fill the field. The menu shows only those attributes that don’t already appear in the form. (When working with subtotal, total, header, or title forms, this command works a little differently—see the section below. )
After you issue the command, Cornerstone prompts you to move the cursor to the place you want the field to appear. Move the cursor to the desired location and press (ENTER). The new field is inserted at the cursor location.
MOVE FIELD The MOVE FIELD command lets you move a field to another place in the form. It has three options.
NAME-ONLY moves just the field name.
FIELD-ONLY moves just the field itself (i.e., the underbars).
BOTH moves the entire field, and is the default option.
Whichever option you select, the command works this way: Cornerstone prompts you to use the arrow keys to move the field. The field moves around the form as you move the cursor. When you are satisfied with the position, press the
Bky
120 Designing Forms
Setting Field Characteristics
| (ENTER) key to complete the command. You can press (CANCEL) at any time to cancel the command and send the
| field back to its original position.
{x} For both the INSERT FIELD and MOVE FIELD commands, Cornerstone will do its best to fit the field where you want it (it will even shrink the field width or suppress the field name if necessary), but if there’s no room, the field will go on a new line under the cursor. Sometimes Cornerstone will shrink the width to a scant one character in addition to suppressing
the field name. When this happens, the display can be confusing, especially if the field is adjacent to other fields in the form. If it appears that no field was inserted, look again! You can always press (FIRST) and then tab through the fields using (NEXT) to see where all the fields are.
There is only one restriction that Cornerstone imposes when you move a field (or line): you may not move a field into or out of a subform. Cornerstone will display an appropriate error message if you attempt to do so.
DELETE FIELD
The DELETE FIELD command lets you remove a field from the form. To use it, move the cursor to the field you want to delete and issue the command. Deleting a field from the form has no effect on your database. When you delete a field all you're doing is telling Cornerstone not to display that field in the form. You can always INSERT it again later, if necessary.
The commands we've described so far affect the position of individual fields in the form. You can also change the display characteristics of the fields themselves.
SET-CHARAC
The SET-CHARAC command lets you set various characteristics of the field, such as its name and width. The cursor must be in a field when you issue this command.
it
Tt
Bi
AT ST ST TTF UF
[
: = = = aI .
Designing Forms
Enter values into the form.
EDIT Name SUL ONES
(EGTA NUMBER OF ORDERS.
Display name?: Yes
Width: 5
Allow overllow?. Yes
Number of post-decimal digits: 0
Display commas? Yes
Display dollar sign?: No
Display zero for numbers less than ona?: Yes (a.g., 0.123} Display negative numbers using parentheses?, No
Use scientific notation?. Never
File: CUSTOMER Previous mode: Format Files viewed: 3
When you issue this command, Cornerstone gives you a form to fill out. The fields in this Cornerstone form let you specify the characteristics of the field in the form you are designing. The top line of the form shows the name of the attribute (or aggregate expression, in the case of a summary form) whose value is displayed in the field. The figure above shows a sample field characteristics form. Different fields have different characteristics depending on the type of data that goes in the field, but all fields have certain basic characteristics.
Name. Every field has aname. Whenever Cornerstone creates a field, it selects a name for the field, typically the name of the attribute the field gets its value from. You can change the name if you wish. Just enter the name you want in the form.
12]
122 Designing Forms
Display name? You can tell Cornerstone not to display the field name —just answer “No” here. The field always has a name, though, even if it’s not displayed. In Format mode you can always find out the name of a field (even when it’s not displayed) by moving the cursor to the field and looking at the status line.
Width. The width of the field is the number of characters used to display its value. You can set the width to whatever value you want as long as there’s room in the form. The width of the field is used for display purposes only, and has no effect on the actual size of data in your file. You can also use the ADJUST- WIDTH command to set the field width (see below).
Allow overflow? Normally, if the value in your file is too big to fit in a field, Cornerstone will create extra overflow lines as required to display the excess data. You can prevent this from happening by answering “No” here. If you answer “No,” any extra data that doesn’t fit in the field won't appear.
Editable? You can prevent anyone from entering or changing the data in the field by answering “No” to this question. This characteristic is useful when you intend to use the form to update records. For example, you might design a special update form for data entry personnel in which they cannot edit certain fields. Not all fields have the Editable? characteristic. For example, since derived fields are never editable, Cornerstone doesn’t ask.
ToT PPT PPP
-
a Va Na aa
‘y
4 Lh, Yy
~
a
‘
\
= S| SH] awk
\
i
=e 4 A a
Designing Forms
The other field characteristics all relate to display styles and are self-explanatory. For example, Display dollar sign? tells Cornerstone whether or not to display a dollar sign with the value of the field. These characteristics are the same as the ones used when you create an attribute in Define mode.
{X] When you set a display characteristic for a particular field ina form, the value you set overrides the value you set for the attribute, but just in the current form, For example, if you answered “No” to Display commas? when you defined the attribute, but answered “Yes” when you set the field characteristics for the field, then commas will be displayed whenever you use that form. Commas will not be displayed in other forms (unless you set the field characteristics in the other forms),
ADJUST-WIDTH
Once you start experimenting with Format mode, you may find yourself changing the widths of fields frequently. For this reason, Cornerstone provides an ADJUST-WIDTH command that lets you set the width of a field more conveniently than with SET-CHARAC.
To adjust the width of a field, just move the cursor into the field and issue the ADJUST-WIDTH command. Cornerstone then prompts you to use the (ARROW-RIGHT) and (ARROW-LEFT) keys to increase or decrease the width. This way you can see how wide the field is without bothering to figure out the actual width. Press (ENTER) to complete the command or (CANCEL) to restore the original width.
123
The commands INSERT, DELETE, and MOVE also work Manipulating
with lines. Lines
INSERT LINES The INSERT LINES command lets you add blank lines to a
form. First, move the cursor to the spot where you want to insert lines, then issue the command. You have the option of inserting the lines BEFORE or AFTER the line the cursor is on. When you issue the command, Cornerstone inserts a
124 Designing Forms
Changing Whole Forms
blank line, and prompts you to press (ARROW-DOWN) if you
_ want to add more. The lines are highlighted as you add
them. Press (ENTER) to complete the command. If you press (CANCEL), the command is canceled and no lines are inserted.
When the cursor lies within a subform, lines are inserted into the subform, except when the subform lies at the end of the form. In this case, lines inserted AFTER the last line in the subform are inserted into the form, not the subform.
MOVE LINE
The MOVE LINE command lets you move a line ina form, including any fields or constant text it may contain. To move a line, go to the line and issue the command, then move the cursor to the new location and press (ENTER). You can press (CANCEL) to cancel the command.
DELETE LINES
The DELETE LINES command lets you delete one or more lines from the form, including any fields or constant text the lines may contain. DELETE LINES works similarly to
INSERT LINES. To use it, first move the cursor to the line you want to delete and then issue the command. The current line becomes highlighted, and you can use (ARROW-DOWN) or (ARROW-UP) to highlight more lines. When you press (ENTER), the highlighted lines are deleted; if you press (CANCEL), they aren't deleted.
The commands described in this section change the entire form. You should be cautious when you use them, since you will lose any work you have done designing the form.
DETAILED and COLUMN These commands work much as they do in View mode and
elsewhere in Cornerstone. Chapter 3, “Viewing Your Data,” explains how they operate.
PRARAARAARAAAR AAA AAA RAR RRR TAs
i
Mela a lad Vd ala alee ee
= |
“
7_
1 AL
:
i
\
ay
4 =
|
& Vv
There are a couple of minor differences with these commands in Format mode. If you issue either COLUMN or DETAILED when the cursor lies within a subform, the command affects only the subform. Also, when you use the COLUMN command, Cornerstone doesn’t automatically create headers the way it does in View mode, unless you issue COLUMN when the cursor lies within a subform.
DETAILED ALL-ATTRIBUTES Is Often a useful starting point when designing a form, since it gets you all the attributes in the file. You can then delete any fields you're not interested in.
DELETE FORM
If you decide that your form is a mess and you want to start over from scratch, use DELETE FORM. This command replaces whatever form you had with a blank one.
None of the following commands are available when you are designing subtotal, total, header, or title forms, since these forms can’t have subforms in them.
INSERT SUBFORM To add a subform to your form, move the cursor to the spot where you want to put the subform and issue
INSERT SUBFORM. You must select a subrecord attribute from
the menu, and you have the option of inserting the subform BEFORE or AFTER the cursor.
Cornerstone will insert a blank subform (one with no fields or text), so you have to use DETAILED, COLUMN, Or INSERT FIELD to create some (sub)fields in the form. You can tell the cursor is in the subform when the word SUBFORM appears in the status line. The name of the subrecord is also displayed as part of the field name.
DesigningForms 125
Manipulating Subforms
126 Designing Forms
|
| DELETE SUBFORM
To delete a subform, move the cursor anywhere inside the subform and use DELETE SUBFORM.
The CALCULATE The CALCULATE command lets you add a new derived Command attribute or change an existing one. It works just as
REPORT CALCULATE does in View mode, except that after you issue the command, you have a chance to move the cursor to the spot where you want to add the formula field. Format mode is a good place to use CALCULATE, since you can get the field to appear where you want it. (In View mode, Cornerstone just tacks the field to the end of the record form. ) Chapter 3, “Viewing Your Data,” explains how this command works.
Printing If you want to print a copy of your form, issue the PRINT FORM command. Cornerstone will print the form as it appears in Format mode, with underbars appearing in the fields instead of data.
In Format mode, you can also print a grid, whichis a set of rules you can use as a design aid in creating Cornerstone forms for printing on pre-printed forms. To use this feature, insert the pre-printed form in your printer and issue the command PRINT GRID. Cornerstone will print the grid right over your pre-printed form. You can then use the row and column numbers in the grid to help you locate fields in the right place. The Format mode status line also shows row and column numbers.
: : “ = = = = a = : : . : a
Jt
a
aye
|
wu
_)
a ia yt
A
Vs Ys =
~
{
=
}
>
by"
Designing Forms 127
Pre-Printed Forms You can use Cornerstone to print information on pre-printed forms. This section describes how to do this.
Most pre-printed forms allow only a specific amount of space for each field. This is especially a problem for subrecords, which are generally multi-valued. Getting Cornerstone to print information on pre-printed forms is a two-step process. First, you design an appropriate Cornerstone form. Second, you answer “Yes” to the question Use pre-printed form? when you set the print characteristics in Print mode. This section shows you how to design the form.
Gs ACME Order Form
Order Number: Date:
Total Price Payment due by the 3rd ofthe month or Old-fashioned (0 Payment Made C,0.D. order form
128 Designing Forms
| Let’s see how one Cornerstone user, Fred (from Omnifex
_ Auto Parts), used Cornerstone to print orders on the pre-
| printed forms he got from his uncle Richard. The form Fred got (shown above) allows space for exactly ten line items. Since some orders will contain fewer than ten line items,
_ and others might contain more, the problem is to make Cornerstone always use exactly ten lines for line items.
The figure below shows a Cornerstone form in Format mode that Fred designed to fit the Acme forms. The form has nine blank lines following the line items subform. These lines tell Cornerstone to reserve space for line items. The field names (in parentheses) are only included for reference. When Fred tells Cornerstone to print his orders using the pre-printed forms, the field names are not printed, since they already appear on the pre-printed form.
(Order Number:) _-----~— (Dates) =
(Name?) = ee ee (Street:)S eee ee ees ee ee (City;State; Zip} 22 >S =e ee ee ees
Cornerstone form designed to print on the (Total Price:) Acme pre-printed order form (see previous page)
x
mM A
|
|
)
SRR AAAS
fi
/; et de A bc |
a
ie ‘ <4 i
When Fred uses this form, and answers “Yes” to the Use pre-printed form? question in Print mode, Cornerstone prints the form in a special manner.
i
0 Every field in the form starts on the row that it appears on in Format mode. For example, since the Total Price field appears on line 21 of the form, it will
‘tie always be printed on line 21 on the pre-printed form, 7 regardless of how many line items there are.
st O Blank lines are interpreted as space holders for multi- Via valued fields and subrecords that precede them. If
there are fewer values or subrecords than there are lines, Cornerstone will leave blank lines; if there are more, Cornerstone will not print the extra values or subrecords. In the latter case, Cornerstone will truncate the value(s) without warning. If you intend to use pre-printed forms frequently, you may wish to set the Maximum number of values characteristic for your subrecord attributes. In the example, Fred should set the maximum number of values for Line Items to 10. This way, Cornerstone will prevent him from creating an order with more than 10 line items, and he will
toring’ 6
= macs,
4 never have to worry about an order being truncated
ae because it doesn’t fit in the form.
i O Field names are not printed, regardless of the setting — of Display name? in the field characteristics form. This i is because pre-printed forms usually have the field lm names already printed on them. You can always include | a field name as constant text if you want it printed on al the form.
m7
V
ie
ee ee
130 Designing Forms
Subtotal, Total, Header, and Title Forms
Working with Two Forms
There are some differences in the way Format mode works with a subtotal, total, header, or title form. For the sake of discussion, we classify subtotal, total, header, and title forms together as summary forms.
INSERT FIELD
This is the only command that operates differently for summary forms. A field ina summary form is used to display aggregate information, rather than an attribute. Therefore, instead of selecting an attribute when you insert the field, you select an aggregate function and an attribute, just as you do with the REPORT TOTAL command in View mode. The aggregate and attribute determine the value that appears in the field. You can learn more about aggregate functions in chapter 6, “Advanced Reporting.”
The following commands are vot available in Format mode when you are working with summary forms.
O COLUMN DETAILED CALCULATE
Oo O O INSERT SUBFORM Oo
DELETE SUBFORM
Sometimes when you design a report format, you'll want to make fields in different parts of the report line up. For example, you might want to make a field in the record form line up with a field in a subtotal or total. To make it easier to do this, Cornerstone lets you format two forms simultaneously, using the REPORT FORMAT command in View mode. For example, to format both the record and subtotal forms, issue the command
REPORT FORMAT RECORDS SUBTOTALS
DesigningForms 131
Use FUNCTION keys or TYPE in text; press <COMMAND > to issue commands. FORMAT RECORDS NAME (OhYES 1 2
FORMAT SUBTOTALS
Subtotals for
File: ORDER Previous mode: View Files viewed: T
The figure above shows what Format mode looks like with two forms. Each half of the screen is a self-contained Format mode that works just the same as when you're only designing a single form. To switch from one form to the other, use the OTHER-FORM command.
When you're done designing your form, press the (DoNE) key| Leaving Format to leave Format mode. This will return you to whatever Mode
mode you were in before you entered Format mode. Any
changes you made to the form are reflected when you
return. Be sure to use the SAVE command in View, Update,
or Select mode to save your changes, otherwise the result of
your efforts may be lost.
132 Designing Forms
KS
iH
| Tips for Form | This section provides some suggestions that we hope will Designers give you ideas for designing your own forms. They cover some common situations that arise. As you become an experienced Cornerstone user, you'll probably invent your own techniques for designing forms to meet your particular reporting needs.
Constant Text Here are some ways of using constant text in forms:
As a title. You can use text to create a name or letterhead in your form, such as “Tweedle-dee Consultants, Inc. —Client profile.”
As instructions. You can use constant text as instructions or commentary in a form, such as “Enter your name here ------- yaa
As text in a form letter. You can intersperse fields and constant text to get a form letter effect. For example, you might begin a form with “Dear --------,”. (The underbars represent the Customer Name field, say.) Another trick you can use in title or header forms is to create a line such as
"WOTOeRS Tent ons titel Sg) Sa i where the two fields might be defined as MIN(Order_ Date) and MAX(Order_ Date). With this technique you generally don’t want the field names displayed.
To draw lines. You can use rows of dashes, equal signs, asterisks, or any other character to draw lines that can be used as separators in the form. This technique is used in the Fine format for the Order file that comes with the Sample Database.
As bullets for subrecords. If you type a character, such as **’ or ‘)’, at the left of a subform, it will appear next to every subrecord.
— mz La — oe = [a amet e rT ~ = re ee r ar —_— re es Pr ee me = =~ = a i
Cee
Pee
Common Mistakes
Typing underbars (_) to lengthen a field. If you type the character underbar (_) into a form, this character will be displayed as is. Typing underbars does not create or lengthen a field. To create a field, use INSERT FIELD. To lengthen a field, use ADJUST- WIDTH Or SET-CHARAC.
Inserting a line in a form versus a subform. When you use the INSERT LINES command with a form that contains subforms, the lines are inserted in the subform if the status line indicates SUBFORM when you issue the command; otherwise they are inserted in the (parent) form—with the following exception: When the last line in a subform is also the last line in the parent form, inserting lines AFTER the last line in the subform causes the lines to be inserted in the parent form. To insert lines at the end of the subform, first insert a line at the end of the form, then move inside the subform and INSERT LINES AFTER the last line. You can then delete the last line in the parent form.
Below are a few situations when Format mode is useful.
When COLUMN and DETAILED aren’t what you want. You may be dissatisfied with the position of the columns or headers in a column format. If so, you can issue the command REPORT FORMAT HEADER RECORDS to format the header and record forms. You can move, and change the width of, the fields in the records form, and you can adjust the header so that the field names remain aligned with the fields. You can also use Format mode to add a column to a column form.
Designing Forms
When to Use Format Mode
133
134 Designing Forms
To design special formats. You can use Format mode to create customized formats. You can type your | business logo in the form, or draw lines to separate subtotals and totals.
To design a form to use witha pre-printed form. You can use Format mode to design a (Cornerstone) form for printing information contained in your database on a pre-printed form.
To customize a data entry (update) form. If you (or others who use your system) like to enter data ina particular order or format, you can design a data entry form that mimics this format. See chapter 4, “Entering and Updating Records.”
The COLUMN and DETAILED commands in View mode suffice for most reporting needs. You can use Cornerstone without ever entering Format mode, but once you do, you'll probably want to customize all your formats. Feel free to experiment with different designs. Format mode will bring out the artist in you.
DATABASE
C
: y ai
:
4 7 i
4 13 1 jal |
[ee 1) SF ma hil La
we
14 ea 14 4 ee i + a id Sa me | ‘ Weel bd
oe |.
CE ac rans |
au ‘i
4 | a
Lesion A
Eg <a = ;
es eos qs =
q 4!
, \
PLANNING ( ) YOUR
HAPTER__
136 Planning Your Database
CHAPTER 9
CONTENTS
Introduction
What's a Database?
Files and Attributes
Every Attribute Has a Type
Derived and Initial Values
Getting it Together: Relationships
Multi-valued Attributes
Subrecords and Subfiles
A True Story
Bon Voyage!
i r— rr a -— r — rr re a aa aii aa = ne = = —— te te a =
q
LU eee
WY
L
|]
“|
G
ea
Vw
4
i
1 |
4
ee
A"
as A
i
Planning Your Database 137
Planning Your Database
In this chapter, you'll learn how to design your own Introduction database. With Cornerstone, you're the architect. You
decide what information is important. You decide how to
organize it. And, if you’re thinking, “Me? Design a
database?” —read on. Soon you'll have plenty of ideas about
how Cornerstone can help you manage your information.
We'll show you how Fred designed his Cornerstone database to keep track of customers and orders for his auto-parts business. You'll see how he used various features of Cornerstone to streamline his operation and allow him more time to spend fixing up his ’71 Plymouth Duster.
Using Fred’s system as an example, we'll teach you about files, attributes, relationships, and a few other database concepts you'll need to know to design your database successfully and get it running.
Our chapter begins by exploring just what a Cornerstone database 1s.
A database is a collection of files. Each file contains records. | What’sa The structure of a record is defined by its attributes, each of Database? which has a value.
Let’s explore these essential elements one at a time. (| A database is a collection of files.
Consider what goes on at Fred’s auto parts company, Omnifex. Fred used to run his business from a three-drawer steel filing cabinet. One drawer was labeled “Customers,” another “Orders,” and the third, “Parts.”
When Fred got Cornerstone, he made these the basic files in his database: a Customer file, an Order file, and a Part file.
138 Planning Your Database
_{ | Each file contains records.
Fred has a fair number of reliable customers who order parts. Whenever a customer calls to place an order, Fred pulls out an order form and takes down the necessary information. In his old order entry system, he would then put a copy of the order in the file cabinet. Each order was a separate sheet in Fred's filing cabinet. Now, each corresponds to a separate record in his Order file. The same is true for customers and parts. At last count Fred stocked 74 different parts, so his Part file contains 74 records, one for each part. Likewise, each record in Fred’s Customer file contains information about a particular customer.
|| The structure of a record is defined by its attributes.
Every record in a file has room for the same pieces of information, just like a pre-printed paper form. These pieces of information, called attributes, constitute the record. For example, the records in Fred’s Customer file have attributes Name, Street, and City, to name a few. These and the other customer attributes together define a customer for Fred’s
purposes.
Fred’s Part and Order files have their own attributes, which are different from those he defined for his Customer file.
Attributes are the building blocks that determine what information is contained in the records ina file. They describe entities such as customers or parts, and typically correspond to properties of real-world things (which is why they’re called attributes). You can create whatever attributes you want to describe