@database FinalCalc.hyper
@font topaz.font 8                                                                                                              

@remark Warning!  Do not edit this file!

@index Main

@$VER: FinalCalc.part7.guide 1.04 (14 February 1996)
@author "Khalid Aldoseri"
@(c) "Copyright © 1995-1996 Khalid Aldoseri"


@node Main "Database..."
@toc "FinalCalc:guide/main.guide/Main"
@{b}@{u}
	Database:
@{uu}@{fg highlight}
	@{" > " link part7.1} What is a Database?
	@{" > " link part7.2} The Record
	@{" > " link part7.3} The Field
	@{" > " link part7.4} Database ranges
	@{" > " link part7.5} Defining and using Criteria
	@{" > " link part7.6} Finding records
	@{" > " link part7.7} Extracting records
	@{" > " link part7.8} Sorting the database
	@{" > " link part7.9} Accessing the Database from Formulas
@endnode

@node part7.1 "What is a Database?..."
@toc "Main"
@{b}@{u}@{fg highlight}What is a Database?@{ub}@{uu}@{fg text}
FinalCalc can treat data in a project range as a database.  That is, data made
up of multiple entries treated as records in a database.

A project can have a single database defined for use.  A FinalCalc database is
defined one or more columns, representing 'fields', and one or more rows,
representing 'records'.
@endnode

@node part7.2 "The Record..."
@toc "Main"
@{b}@{u}@{fg highlight}The Record:@{ub}@{uu}@{fg text}
A record is the basic object in a database.  It is an entry made up of one or
more pieces of data, called fields.  In FinalCalc, a record is stored in a
single row, over one or more columns, each column containing a field.

To start using a database, you must first inform FinalCalc of where you wish
to store the records in the project.  This is done by defining the Database
Input Range.

Once you have the position of the database defined in the project, you can
start using the database menus to handle that range as a database.

You can enter a record directly into the project as normal project cells.  The
fields in a record can contain string or numeric cells, be empty, or even
contain formulas.

You can also use the 'Data - Record' menu to manipulate the database as records:

@{b}@{u}Record Insert:@{ub}@{uu}
Inserts a blank record at the current cursor position in the Input Range.
If the cursor is outside the Input Range you will be informed of that and
the operation aborted.  The Input Range will be expanded by one row to
reflect the increase in its size.

@{b}@{u}Data Record Delete:@{ub}@{uu}
Deletes the record at the current cursor position in the Input Range.
If the cursor is outside the Input Range you will be informed of that and
the operation aborted.  The Input Range will be shrunk by one row to reflect
the decrease in its size.

@{b}@{u}Data Record Clone:@{ub}@{uu}
Places a second copy of the record at the current cursor position in the
Input Range after inserting a new record.  If the cursor is outside the
Input Range you will be informed of that and the operation aborted.  The
Input Range will be expanded by one row to reflect the increase in its size.
@endnode

@node part7.3 "The Field..."
@toc "Main"
@{b}@{u}@{fg highlight}The Field:@{ub}@{uu}@{fg text}
A field is part of a record.  It describe one item of a group of items that are
collectively known as a record.

You can enter record fields directly into the project as normal project cells.
A field can can contain string or numeric cells, be empty, or even contain
formulas.

As FinalCalc does not force you to enter a specific type of entry in each
field, you can have different types of entries in the same field in different
records.

Although you can enter formulas in record fields, it is not advisable as they
can be moved around and changed by the Record and Extract tools.
@endnode


@node part7.4 "Database ranges..."
@toc "Main"
@{b}@{u}@{fg highlight}Database ranges:@{ub}@{uu}@{fg text}
The project's database is defined as the Input Range.  The 'Data - Define' menu
brings up the 'Edit Data Ranges' requester, which defines where the database
ranges for the current project are.

@{b}@{u}The Input Range:@{ub}@{uu}
The Input Range defines where the database is stored in the project.  It is
generally defined as a simple range.  You must define this before any of the
database tools will work.

@{b}@{u}The Output Range:@{ub}@{uu}
The Output Range defines where the Data Extract commands put their output.
It must be defined for the Data Extract commands to work.

@{b}@{u}The Criteria Range:@{ub}@{uu}
The Criteria Range defines where the criteria for finding or extracting data
is stored.  You must define this range if you want to use the Data Find or
Data Extract functions.

The Criteria Range should be as wide as the Data Input Range, and can consist
of one or more rows.   Each of the rows is considered as a 'test', and all
fields in that rows must be satisfied for a record to match the Criteria.

However, if you specify multiple lines of Criteria, each of those lines will
act as an OR switch, so if a record matches 1 or more of the Criteria lines, 
it will be considered as a match.  Criteria lines that are totally empty are
simply ignored.


Each project sheet can have its own database.  The database tools all operate
on the project in the current sheet, or, if a database is not defined for the
current sheet, it will try to access a database on sheet A.

Project @{"range names" link "FinalCalc:guide/part3.guide/part3.4"} will automatically be defined for each of the above ranges.
'DB_INPUT' for the Input Range, 'DB_OUTPUT' for the Output Range, and 'DB_CRIT'
for the Criteria Range.   Databases on sheets other than sheet A get range
names in the format: 'DB_INPUT_B' (for sheet B), 'DB_OUTPUT_B' and 'DB_CRIT_B'.
@endnode

@node part7.5 "Defining and using Criteria..."
@toc "Main"
@{b}@{u}@{fg highlight}Defining and using Criteria:@{ub}@{uu}@{fg text}
When using the Find or Extract database commands, you must first define the
criteria used to do those operations.

Criteria are simply entered as one or more records, with each field either
empty or containing a test that has to be performed for the find or extract
operation to decide whether a record matches the criteria or not.

A field in a criteria record can contain any of the following tests:

a.	Empty:
	If a field is left empty, it is automatically assumed that the test is
	successful.

b.	String Wildcard:
	If a field is entered as a string, then only a field with a string entry
	will be eligible for testing, formulas and numeric fields immediately
	fail.  You can use any valid wildcard pattern.

c.	A Formula:
	If you enter a formula in a criteria field, only numeric fields or formulas
	will be eligible for testing, string cells will immediately fail the test.
	If the result of the math formula is FALSE (a value of zero), then the test
	has failed, otherwise it has succeeded.

	Imporant note: 'FIELD' is a named constant that is available for use in
	formulas during database criteria testing.

	It represents the value of the field in the record currently being tested.
	For example, entering "=FIELD>100"  means that only records with this field
	with a value of more than 100 will pass the test.


You can have multiple criteria in multiple fields.  All the criteria field
tests must succeed for the record to be accepted.

You can also have multiple criteria records.  This is done by defining a
Criteria Range of more than one row, and entering multiple criteria records.
All the criteria field tests on all the criteria records must succeed for the
record to be accepted.
@endnode

@node part7.6 "Finding records..."
@toc "Main"
@{b}@{u}@{fg highlight}Finding records:@{ub}@{uu}@{fg text}
The 'Data - Find' menu allows you to search for a record in the database Input
Range that matches the defined criteria in the Criteria Range.

@{b}@{u}Data Find Next:@{ub}@{uu}
Searches for the next matching record in the Input Range using the Criteria
range and moves the cursor to that record.

@{b}@{u}Data Find Highlight:@{ub}@{uu}
Searches the entire Input Range for records matching the Criteria and 
highlights each matching entry by setting their Style Reverse format.  You
will be informed at the end of the search of how many matching records were
found.

@{b}@{u}Data Find Clear Highlight:@{ub}@{uu}
Clears the highlight from all records in the Input Range.
@endnode

@node part7.7 "Extracting records..."
@toc "Main"
@{b}@{u}@{fg highlight}Extracting records:@{ub}@{uu}@{fg text}
The 'Data - Extract' menu allows you to copy or move records from the database
Input Range that match the defined criteria in the Criteria Range to the
Output Range.

@{b}@{u}Data Extract Clone:@{ub}@{uu}
Searches the entire Input Range for records matching the Criteria and will
copy each matching entry to the Output Range.  If the number of columns in
the Output Range is less than the number of columns in the Input Range, then
this will only copy as many fields as will fit in the Output Range.

If the Output Range cannot hold all the matching records, the Extract will
stop and inform you of that after filling the Output Range.

You will be informed of how many matching records were extracted.

@{b}@{u}Data Extract Delete:@{ub}@{uu}
Searches the entire Input Range for records matching the Criteria and will
copy each matching entry to the Output Range.  If the number of columns in
the Output Range is less than the number of columns in the Input Range, then
this will only copy as many fields as will fit in the Output Range.

Matching records are DELETED from the Input Range after they are copied.
Before this operation commences you will get a requester asking you if you
really want to go ahead.

If the Output Range cannot hold all the matching records, the Extract will
stop and inform you of that after filling the Output Range.

You will be informed of how many matching records were extracted.
@endnode

@node part7.8 "Sorting the database..."
@toc "Main"
@{b}@{u}@{fg highlight}Sorting the database:@{ub}@{uu}@{fg text}
You can sort the records in a database into a specific order based on one or
more fields.

To sort the database, you must first define the Data Sort settings.  Use the
'Data - Sort - Define' menu to bring up the 'Edit Sort Keys' requester, which
defines what 'keys' to use when sort.

A sort key is the name of the column of the field to use when comparing records
when sorting to decide which record goes first.  You can define up to 5 sort
keys, which are tested in order.

When a sort is performed, the 1st sort key will be used to sort the data.  If
two entries are identical in the 1st sort key, the 2nd sort key is used to
decide their order.  If the 2nd key cannot do that, the 3rd key is used and so
on.  At worst, if they are identical in all 5 sort keys, the original order of
the records is preserved.

By default, keys are sorted in ascending order, (i.e. 'A' is before 'B'), but
if you turn off this setting for a specific key, that key is sorted backwards.

The exact way two fields are compared uses the following logic:

 -	Comparing two string cells:
	Sort order is ASCII based, non case-sensitive.

 -	Comparing two numeric cells:
	Sort order is actual value of cells.

 -	Comparing a string to a numeric cell:
	Numeric cell goes after string cell.

 -	Comparing an empty cell to a string cell:
	Empty cell goes after string cell.

 -	Comparing an empty cell to a numeric cell:
	Numeric cell goes before empty cell.


Once you have defined the sort keys, simply use the 'Data - Sort - Sort' menu
at any time to perform the sort operation.
@endnode

@node part7.9 "Accessing the Database from Formulas..."
@toc "Main"
@{b}@{u}@{fg highlight}Accessing the Database from Formulas:@{ub}@{uu}@{fg text}

You can access the database in formulas via the following functions:

	DSUM(offset)
	Gives you the sum of a specific field (defined by offset, e.g. offset 0 is
	the first field in a record, 1 is the 2nd, etc.) in all records in the
	Input Range matching the Criteria.

	DMAX(offset)
	Returns the maximum value in the field in all the matching records.

	DMIN(offset)
	Returns the minimum value in the field in all the matching records.

	DAVG(offset)
	Returns the average value of the field in all the matching records.

	DCOUNT(offset)
	Returns the number of cells in the field in all the matching records.

	DSTD(offset)
	Returns the Standard deviation of a selected field.

	DVAR(offset)
	Returns the Variance of a selected field.

	Both the Data Input Range and Data Criteria Range must be defined.  If
	either of them isn't, these functions will generate an ERROR.

@{"Appendix 1" link "FinalCalc:guide/appendix1.guide/Main"} contains a full description of all these formula functions.
@next "FinalCalc:guide/part8.guide/Main"
@endnode