OVERVIEW.............The Database Utilities Main Menu
CREATE DATABASE......Creating a new Database file
DISPLAY STRUCTURE....Displaying or Printing the Database file Structure
MODIFY STRUCTURE.....Modifying the Structure of a Database
COPY STRUCTURE.......Copying the Structure of a Database
APPEND RECORDS.......Appending Records from another Database
COPY RECORDS.........Copying Records to another Database
IMPORT RECORDS.......Importing Records from another Database
COPY FIELDS..........Copying data between fields in a database
DELETE RECORDS.......Deleting records in a database
RECALL DATA..........Recalling deleted records in a database
SORT DATABASE........Sorting a database to a new database
JOIN DATABASES.......Joining a database to another database
COUNT RECORDS........Counting the records in a database
SUM RECORDS..........Summing numeric fields in a database
AVERAGE RECORDS......Averaging numeric fields in a database
DISK DIRECTORY.......Display a Directory listing of Database or Dos files
INSERT BLANKS........Inserting Blank records into a database
PACK DATABASE........Removing records marked for deletion from a database
ZAP DATABASE.........Removing all records from a database
IMPORT MEMOS.........Importing or Exporting memos to ASCII files
PURGE RECORDS........Removing duplicate records from a database

OVERVIEW

The Database Utilities Main Menu

Description:

The DATABASE UTILITIES MENU provides a wide variety of
utilities specifically relating to database modification
and reporting. This menu includes the following
selections:

ЪДДґ DATABASE UTILITIES ГДДДї
і Create Database і
і Display Structure і
і mOdify Structure і
і Copy Structure і
і Append Records і
і coPy Records і
і iMport a Data File і
і Replace Data і
і copY Fields і
і dElete Records і
і RecaLL Records і
і SorT Database і
і Join with Database і
і CouNt Records і
і sUm Data Fields і
і aVerage Data Fields і
і disK Directory і
і Insert Blank Record(s) і
і Pack DataBase і
і Zap Database і
і Import/eXport Memos і
і PurGe Duplicate Records і
АДДДДДДДДДДДДДДДДДДДДДДДДДДДЩ

To invoke the DATABASE UTILITIES menu, use the dot-prompt
command UTLITIES, the function DC_UTIL() or select
DATABASE UTILITIES from the database assistant UTIL sub-
menu.

-- Setting Scoping Conditions --

Most UTILITIES selections will ask you to enter a scoping
condition for the specific function selected. An input
screen similar to the below example will ask for a set of
conditions relating to the database. For example, if you
wish to COUNT records in a database, a set of conditions
must be entered to determine how you wish to perform the
count.

ЪДДДДДДДДДДДДґ Set a Scoping Condition ГДДДДДДДДДДДДДї
і і
і Which record? ( CURrent / ALL / Number ) : ALL і
і FOR condition: BALANCE ) 100 і
і WHILE condition: YEAR (= 1988 і
і Start at (C)urrent record or (T)op of file: T і
і How many records? ( ALL / Number ) : ALL і
АДДДДДДДДДДДДДДДДДДДДДДДДДДДДДДДДДДДДДДДДДДДДДДДДДДДДЩ

-- WHICH RECORD? --

If you wish to perform the function only on the currently
selected record enter (CUR). If you wish to perform the
function on all records in the database enter (ALL). If
you wish to perform the function on a specified record,
enter the record number.

-- FOR CONDITION --

Enter an expression which must evaluate true for each
record to include in the operation. This is usually an
expression that includes field names from the parent and/or
child work areas and operators and values to specify a
range of values in the database. Only records in which
the expression evaluates true will be included.

-- WHILE CONDITION --

A WHILE condition is similar to a FOR condition except that
the operation will be terminated on the first record in
which the condition evaluates false. This is usually an
expression that includes field names from the parent and/or
child work areas and operators and values to specify a
range of values in the database.

-- START AT CURRENT RECORD OR TOP OF FILE --

Enter (C) if you wish to start the operation at the
currently selected record. Enter (T) if you wish to
start at the beginning of the database.

-- HOW MANY RECORDS? --

Enter (ALL) if you wish to include all records in the data-
base in the operation. Enter a number if you wish to
perform the operation only on a specified number of records
starting at the Current record.

NOTE:

If you entered an invalid FOR or WHILE condition statement,
you could get repetitive errors for each record until the
end of the file is encountered. Errors in condition
statements can be caused by using improper field names,
functions, operators, etc. If you have entered an invalid
condition an error message will appear in the middle of the
screen at the time the condition is being evaluated. dCLIP
will not allow an invalid condition filter to be created,
therefore you may be stuck in a loop to correct the
condition. If for any reason, you cannot create a valid
condition, press ESCAPE to set no condition.

Any time you are prompted to enter a condition, you may press
key F2 to activate the RELATIONAL QUERY-BUILDER. This is a
friendly way of creating a complex condition from a set of
point-and-shoot menus. The condition expression created in
the query builder will be returned into your current input
window to allow you to make modifications or accept the
condition.

CREATE DATABASE

Creating a new Database file

Description:

This selection is used to create a new database file. The
file created will include only the structure of the
database but will contain no records. After entering the
name of the new file, you will be prompted to enter the
Field name, Field type, Field length, and Field decimals
for each field you wish to include in your database
structure. Enter the fields as you decided to name them
when you planned your data structure.

You must make sure that you select the data driver (RDD)
first that supports the type of file you wish to create.
This is accomplished by the SET RDD TO (data driver)
command at the dot-prompt or from the Database Assistant.

If you make a mistake, you can abort the operation at any
time or use can use the MODIFY STRUCTURE selection. After
you have added the last field to your database structure,
press key (X) to exit. dCLIP will create a new, empty
database file from the information you entered.

DISPLAY STRUCTURE

Displaying or Printing the Database file Structure

Description:

This selection will display the structure of the currently
selected database. A structure listing will be displayed
and you will be allowed to scroll through the list of
fields to view the structure of the entire database. After
the structure is displayed on the screen, you can press key
(P) to output the entire data structure to the printer.

For more information, see the DISPLAY STRUCTURE command or
the DC_DBSTRU() function.

MODIFY STRUCTURE

Modifying the Structure of a Database

Description:

This selection will allow you to modify the structure of
the currently selected database file. Modify structure
allows you to change the structure of an existing database
file. You can add, delete or change the name of data
fields. You can lengthen, shorten or change the type of a
data field in the event that the existing structure is not
suited to your needs. Modifying a structure is a process
that can cause you to lose data if you are not careful.
Data can be lost or truncated any time a field type is
changed, or a field is shortened or deleted. See the
MODIFY STRUCTURE command or the DC_MODSTRU() function for
more information.

COPY STRUCTURE

Copying the Structure of a Database

Description:

This selection will copy the structure of the currently
selected data file to a new data file. If the "Copy To"
file already exists, the process will abort you will be
prompted to "overwrite? (Y/N)". This selection is useful
when it is desired to create a new data file in which the
data fields are similar to an existing file. First, COPY
the structure, then MODIFY the structure to suit your new
requirements. Enter the file name as
(Drive:\Directory\Filename). If an extension is not
included in the file name, then the default extension for
the currently selected DEFAULT data driver (RDD) will be
used. If no path is included then the new file will be
created in the DEFAULT directory.

NOTE: The new file will be of the type supported by the
currently selected data driver and will contain the same
field names but not necessarily the same database archi-
tecture as the original file.

APPEND RECORDS

Appending Records from another Database

Description:

This selection will append records from any database to
the currently selected database.

Enter the file name preceded by the optional Disk Drive
number and Directory. The currently selected DEFAULT data
driver (RDD) will be used to open the file which is being
appended from. Only the fields with the same name will be
appended to the current data file and only the records that
that satisfy the scoping conditions.

HINT: It is recommended that you COUNT the data records in
the data file being appended from based on the scoping
conditions. This action will insure that you know exactly
how many records you will be adding to your file.

COPY RECORDS

Copying Records to another Database

Description:

This selection will copy records from the currently
selected database to a new database.

Enter the file name preceded by the optional Disk Drive
number and Directory. The currently selected DEFAULT data
driver (RDD) will be used to determine the architecture
of the database that is created. Only the records that
satisfy the scoping conditions will be copied to the new
database file. If no path is included in the file name,
the new file will be created in the DEFAULT directory.

IMPORT RECORDS

Importing Records from another Database

Description:

This selection will import data from any other work area
into the currently selected work area, even though they
may have entirely different data structures.

Data from fields of different TYPES will be transferred
with IMPORT. Two windows with field lists are displayed
and fields are matched for the transfer of data followed
by selection of a scoping condition.

Subportions of any field can be transferred by selecting
start and end delimiters. For example, if you wish to copy
data from a field which contains last name, comma, first
name to two seperate fields, one for last name and one for
first name, use the EXTRACT option of IMPORT.

Fields may also be concatenated into a single field. For
example, if you wish to copy data from two fields which
contain an area code and a telephone number to a single
field which has the area code in parenthesis followed by
the number, use the EXTRACT option of IMPORT and add the
desired framing characters.

For more information, see the IMPORT command or the
DC_IMPORT() function.

COPY FIELDS

Copying data between fields in a database

Description:

This selection provides for copying of data between fields
in the currently selected database.

Two windows with field lists are displayed and fields are
matched for the transfer of data followed by selection of
a scoping condition.

Data may be copied only between fields in the same record,
however the copy may be for the entire database. Data
from fields of different TYPES can be transferred or
subportions of any field can be transferred by selecting
start and end delimiters. For example, if you wish to
restructure a database to convert a field which contained
last name, comma, first name to two seperate fields, one
for last name and one for first name, use the EXTRACT
option of COPY Fields.

Fields may also be concatenated into a single field. For
example, if you wish to copy data from two fields which
contain an area code and a telephone number to a single
field which has the area code in parenthesis followed by
the number, use the EXTRACT option of COPY Fields and add
the desired framing characters.

See the COPY FIELDS command or DC_COPY() function for more
information.

DELETE RECORDS

Deleting records in a database

Description:

This selection will delete all records in the currently
selected database that satisfy a specified scoping
condition.

-- Example --

To delete all customers in the CUSTOMER.DBF file who had
no sales activity for the past year. Enter the FOR
condition as follows:

FOR Condition : SALES_Q1+SALES_Q2+SALES_Q3+SALES_Q4 = 0

The records that meet the condition will be marked for
deletion. They can be permanently removed from the file by
the PACK utility.

RECALL DATA

Recalling deleted records in a database

Description:

This selection will recall all records in the currently
selected database that were previously marked for deletion
and that satisfy a specified scoping condition.

-- Example --

To recall all customers in the CUSTOMER.DBF file in zip
code area 80000 - 89999. Enter the FOR condition as
follows:

FOR Condition : ZIP )= '80000' .AND. ZIP (= '89999'

The records that meet the condition will be recalled.

SORT DATABASE

Sorting a database to a new database

Description:

This selection will allow you to sort on any data field or
expression in the current database file and create a new
data file from the sorted data. All the fields in the
current file will be copied into the new sorted file,
however, only the records that meet the selected scoping
condition will be copied to the new file.

A temporary index will be created to determine the sorting
order of the new database. The index-builder utility
provides a pick-list of fields from the current work area
to create the sort order.

The new database will be created using the architecture
supported by the currently selected DEFAULT data driver
(RDD). If no path is included in the file name, the file
will be created in the current DEFAULT directory.

JOIN DATABASES

Joining a database to another database

Description:

This selection will join the information in two open work
areas to a third database. The new database will be made
up of fields from the currently selected work area and a
secondary work area. Be careful when using the JOIN
function because you can accidently create very large data
files. When joining two data files the process goes as
follows: The record pointer is set to the first record in
the current file. Each record in the second file is
evaluated for the scoping condition. If the specified
condition is true, a new record is added to the new file.
When all records in the second file are scanned, the
record pointer in the current file advances one record, and
the process is repeated.

CAUTION: Care should be take when JOINing two files. It is
possible for two database files to be JOINed such that the
new file exceeds the available disk space. (Two 100 record
database files produce a 10,000 record file if the specified
condition is True for all records in both files.)

JOIN is the only selection which requires that you enter a
FOR condition. The condition should be entered as follows:

FOR Condition : CUST_NAME = B -) CUST_NAME

The current file is referred to in the condition statement
as file A and the second file as file B.

If no path is included in the filename to be created, it
will be created in the current DEFAULT directory. The new
database will have the architecture supported by the
currently selected default data driver (RDD).

COUNT RECORDS

Counting the records in a database

Description:

This selection will count the records in the currently
selected database that satisfy a specified scoping
condition.

-- Example --

To count the number of customers in the State of
Washington with a Sales Backlog greater than $100.00.
Enter the FOR Condition as follows:

FOR Condition : STATE='WA' .AND. BACK_LOG)100

Total record count is: 13

SUM RECORDS

Summing numeric fields in a database

Description:

This selection will display a SUM TOTAl of all the data in
any set of numeric fields in the currently selected
database.

-- Example --

To display the total Quarterly sales for all customers in
the CUSTOMER.DBF file for the State of New York. Enter
the information as follows:

Field Name #1 : SALES_Q1
Field Name #2 : SALES_Q2
Field Name #3 : SALES_Q3
Field Name #4 : SALES_Q4

FOR Condition : STATE='NY'

Sum total for SALES_Q1 is: 12366.94
SALES_Q2 is: 55378.12
SALES_Q3 is: 78409.73
SALES_Q4 is: 62712.91

AVERAGE RECORDS

Averaging numeric fields in a database

Description:

This selection will display an AVERAGE of all the data in
any set of numeric fields in the currently selected
database.

-- Example --

To display the average Quarterly sales for all customers
in the CUSTOMER.DBF file for the State of New York. Enter
the information as follows:

Field Name #1 : SALES_Q1
Field Name #2 : SALES_Q2
Field Name #3 : SALES_Q3
Field Name #4 : SALES_Q4

FOR Condition : STATE='NY'

Average for SALES_Q1 is: 927.11
SALES_Q2 is: 1588.92
SALES_Q3 is: 2311.02
SALES_Q4 is: 2082.66

DISK DIRECTORY

Display a Directory listing of Database or Dos files

Description:

This selection will display a list of all the database
files in the current DEFAULT directory or list of all
files by wildcard in the current DOS directory.

When prompted to enter a drive, path and filename, if
you leave the name blank then the directory listing will be
a listing of only the database files in the current DEFAULT
directory as follows:

Database Files # Records Last Update Size
PHONECLIP.DBF 5 08/04/87 1090
LBLCLIP.DBF 3 08/04/87 1399
GRPHCLIP.DBF 10 07/29/87 5182
FILECLIP.DBF 15 08/05/87 3838
PROJCLIP.DBF 2 08/05/87 387
BASEBALL.DBF 13 08/05/87 1583

If you enter a wildcard argument with no path, then the
listing will be all files in the currently selected
DOS directory that match the argument.

If you enter a path followed by a wildcard argument, then
the listing will be all files in the path directory that
match the argument.

INSERT BLANKS

Inserting Blank records into a database

Description:

This selection will insert any desired number of blank
records into the currently selected database starting at
the current record. This is accomplished in the following
steps:

1. All records after and including the current record are
copied to a temporary file and deleted from the current
file.

2. The desired number of blank (empty) records are appended
to the current file.

3. The records in the temporary file are appended to the
current file.

PACK DATABASE

Removing records marked for deletion from a database

Description:

This selection will remove all records marked for deletion
from the currently selected work area. The pack function
not pack using the normal Clipper dbPack() function but
instead packs the database as follows:

1. Displays a progress odometer during the packing process.
2. Optimizes size of memo fields (removes dead space).
3. Creates a backup file.

ZAP DATABASE

Removing all records from a database

Description:

This selection will permanently remove all records from
files open in the current work area. This includes the
current database file, index files, and associated memo
file. Disk space previously occupied by the ZAPped files
is released to the operating system. ZAP performs the
same operation as DELETE ALL followed by PACK, but is
almost instantaneous.

To ZAP in a network environment, the current database file
must be USEd EXCLUSIVEly.

IMPORT MEMOS

Importing or Exporting memos to ASCII files

Description:

This selection will IMPORT or EXPORT ASCII files to
selected memo fields in the currently selected database.

This is a menu-driven utility that allows the operator to
easily import data from ascii text files into memo fields
or export memo fields to ascii text files.

A selector menu first appears to allow you to select the
memo field to work with. If there are no memo fields in
the currently selected database then you must press the
(ESC) key to abort the process.

After selecting a memo field, another selecor menu will
appear to allow you to select a reference field from the
currently selected database. The field is displayed in a
browse-style data window along with the truncated contents
of each memo field.

As you move up and down through this window with your
cursor keys, the first 10 lines of the current memo for
each record are displayed in a memo window.

After selecting the desired memo to import or export, press
key (I) to IMPORT an ascii text file to the current memo or
key (E) to EXPORT the current memo to an ascii text file.

If you select IMPORT, then you are provided the following
options:

1. Replace the current memo contents with the ascii
file contents.
2. Insert the ascii file contents ahead of the current
memo contents.
3. Append the ascii file contents to the end of the
current memo.

If you select EXPORT, then you are provided the following
options:

1. Create a new ASCII file.
2. Overwrite an existing ASCII file.
3. Append to an ASCII file.

PURGE RECORDS

Removing duplicate records from a database

Description:

This selection will remove records from a database that
have duplicates based on a test of specified fields. The
purged records will be marked as *deleted* but will not
actually be removed from the data file until the file is
packed. This will allow you to BROWSE the file and monitor
the purged information before completing the final
reduction.

PURGE actually creates a temporary index file based on an
expression that is created from the pop-up index builder.

Purged records can be written to a new data file for
recovery at any time.

OVERVIEW

The Database Utilities Main Menu

Description:

See Also:

CREATE DATABASE

Creating a new Database file

Description:

See Also:

DISPLAY STRUCTURE

Displaying or Printing the Database file Structure

Description:

See Also:

MODIFY STRUCTURE

Modifying the Structure of a Database

Description:

See Also:

COPY STRUCTURE

Copying the Structure of a Database

Description:

See Also:

APPEND RECORDS

Appending Records from another Database

Description:

See Also:

COPY RECORDS

Copying Records to another Database

Description:

See Also:

IMPORT RECORDS

Importing Records from another Database

Description:

See Also:

COPY FIELDS

Copying data between fields in a database

Description:

See Also:

DELETE RECORDS

Deleting records in a database

Description:

See Also:

RECALL DATA

Recalling deleted records in a database

Description:

See Also:

SORT DATABASE

Sorting a database to a new database

Description:

See Also:

JOIN DATABASES

Joining a database to another database

Description:

See Also:

COUNT RECORDS

Counting the records in a database

Description:

See Also:

SUM RECORDS

Summing numeric fields in a database

Description:

See Also:

AVERAGE RECORDS

Averaging numeric fields in a database

Description:

See Also:

DISK DIRECTORY

Display a Directory listing of Database or Dos files

Description:

See Also:

INSERT BLANKS

Inserting Blank records into a database

Description:

See Also:

PACK DATABASE

Removing records marked for deletion from a database

Description:

See Also:

ZAP DATABASE

Removing all records from a database

Description:

See Also: