This article is reprinted from the month year edition of
TechNotes/product.  Due to the limitations of this media, certain
graphic elements such as screen shots, illustrations and some tables
have been omitted.  Where possible, reference to such items has been
deleted.  As a result, continuity may be compromised.  

TechNotes is a monthly publication from the Ashton-Tate Software
Support Center.  For subscription information, call 800-545-9364.

Using Your Relations
Lena Tjandra

The purpose of this article is not to favor one method over the other,
but to enlighten the reader on how the second, less understood method
can be implemented.  First, this article will use a specific and
simplified example; therefore, in order to apply what you will learn
in this article to your actual application, you should already have
some understanding of what a relationship is, how it works, and how
you want yours set up.  That is, this article will not tell you how
you should select your fields and database or how you should design
your relationships.

Suppose you have several database files that are related through the
SET RELATION command.  How would you append records to the files? 
More specifically, how would you append records to the files without
breaking the relationship?

Theoretically, you are not allowed to add or modify records to related
database files, because the definition of your relationship can not be
clearly defined to the system.  For example, suppose you have a
one-to-many link from database A to database B and you want to add
records to the files, as in an inventory system where you have one
part that may consist of multiple subparts.  How would the system be
able to distinguish whether you want to add one part to database A,
and continue adding multiple subparts to database B only, or if you
want to start a brand new part entry altogether?  Well, unless there
is some intervention from the user, it will not know what to do;
hence, you have to simulate this process yourself.

One method is to use memory variables exclusively to obtain all
necessary input, and then replace them into the corresponding database
without any active relationship.  Another method is to keep the
relationship intact and actually append to the databases.  Before we
continue our discussion on how to append records into related files,
let's briefly examine the pros and cons of both methods.
Using Memory Variables
Basically, this method is a very simple one in concept.  There's no
need to worry about the proper relationship while you are accepting
input to be appended to the database because there is no
relationship.  The idea is to use memory variables to accept user
inputs, then verify that the user really wants to append the new data.
If so, simply select the appropriate database one at a time, add a
blank record, and replace the fields with the appropriate memory
variables.
The advantage of this is that if the user changes his mind about
adding the new information, then no harm has been done since we have
not touched the database.  Therefore, we don't have to worry about
removing the proper records from the database files.  However, the
disadvantages are if you have several files to append to and several
fields to replace.  You would need to create lots of memory variables
to match your fields and as a result,  your code will be harder to
follow and your application may run slower.  In addition, if you like
using a format file for data entry, you would no longer be able to do
this convieniently without loosing the editing capabilities, such as
PgUp and PgDn.

There is a way to keep the editing keys in a format file that acts on
variables.  See Tom Woodward's article, "Taking the Reigns of Control
in Your Application" in our October '89 edition.

Using a Relationship

Contrary to the above method, this one is not so straight forward. 
However, once you understand how a relationship works in dBase IV, it
is very easy to implement.  Not only that, but it will save you from
defining extra variables, and issuing unnecessary replace statements. 
Furthermore, you can also make use of the format file.

Although many will argue that using memory variables is much safer
than inputting directly into the database, the new dBase IV command
BEGIN/END TRANSACTION, will make retractions much easier than those
who prefer to use memory variables.  In fact, you can also use
BEGIN/END TRANSACTION with the first method, but why double your
work?  Why not just input directly into the database, and let
BEGIN/END TRANSACTION take care of any mishaps or retractions?

Briefly, for those of you who are not familiar with this new
enhancement, Begin/End Transaction simply records any changes you make
to a database file, and provides the option to ROLLBACK the changes. 
Once the changes have been committed, however, the option to ROLLBACK
is removed.  The dBASE IV Language Reference manual, page 2-38 to 2-41
provides a more detailed explanation of this command.

Now, let's suppose that we have a banking application.  A typical
banking application would have many options (opening and closing an
account, account inquiries, transactions, customer information, etc). 
However, for the sake of this article we will only need to discuss the
implementation of opening a new account and adding another person into
an existing account, although we will only elaborate on the first
task.

I chose this example because it allows me to demonstrate a complex
relationship.  For instance, in this application, we will relate four
database files together using a combination of one-to-many,
many-to-many, and many-to-one links.
First,  take a look at Figure 1 to see what our database files look
like.

Figure 1

Structure for database: CUSTOMER.DBF
Field  Field Name  Type       Width    Dec    Index
    1  CUST_ID     Character      9               Y
    2  LAST        Character     20               N
    3  FIRST       Character     20               N
    4  ADDRESS     Character     20               N
    5  CITY        Character     15               N
    6  STATE       Character      2               N
    7  ZIP         Character      5               N

Structure for database: CUST_ACC.DBF
Field  Field Name  Type       Width    Dec    Index
    1  ACCT_NO     Character      6               Y
    2  BALANCE     Numeric       12      2        N

Structure for database: ACCOUNT.DBF
Field  Field Name  Type       Width    Dec    Index
    1  CUST_ID     Character      9               Y
    2  ACCT_NO     Character      6               Y
    3  DATE        Date           8               N

Structure for database: TRANSACT.DBF
Field  Field Name  Type       Width    Dec    Index
    1  ACCT_NO     Character      6               Y
    2  DATE        Date           8               N
    3  TRANS_TYPE  Character      1               N
    4  AMOUNT      Numeric       12      2        N

The Customer file contains information such as name, address, customer
identification number, etc; therefore, each record should be unique. 
That is, the customer ID, which is considered the key field, should
not occur more than once in the file.

Cust_Acc contains the account number and the balance.  It has a
similar characteristic to the Customer database in that only unique
account numbers are allowed in the database.

The Account file, however, contains an account number and the owner of
the account; therefore, this file can have duplicate customer
identification numbers and duplicate account numbers since one
customer can have several accounts and the same account can be shared
by several people.

The Transact file keeps track of all the transactions for all the
accounts, so there will be duplicate account numbers here.  This file
is useful for printing out monthly statements.

Remember, these file structures are simplified for this article, and
in actuality, there may be many more fields and database to deal with.

Now that we know what our files look like and what their
characteristics are, let's discuss what kinds of relationships we
need.

Looking back at our tasks, we need at least two different kinds of
relationships.  One for adding a new account, and another for adding a
person into an existing account.  We will call these two relationships
CustInfo and Shr_Acct respectively.  Let's examine our first task,
adding a new account.

This option should also be able to allow several people to open a
joint account, but since we want to keep our application simple and to
the point, we will not make our application too integrated.  Instead,
we will assume that only one person can open an account but more
people can be added to an existing account later on.  

Let's take a look at the CustInfo.PRG file (Listing 1) and see how we
can setup the kind of relationship we want above.  Line 5 to 18 sets
up the relationship, but we still need to establish the links between
the files.  We need a many link into Account and Transact.  We can
accomplish this by using the SET SKIP TO command as on line 23.  This
line tells dBASE to set a many link into Account and Transact so that
for every Customer, there maybe multiple accounts, and for every
account there may be many transactions.  To get a better understanding
of this, see your dBASE IV Language Reference manual for SET SKIP.

Listing 1 CustInfo.PRG 

 1 * CustInfo.PRG
 2 
 3 SET FIELDS TO    && Clear field list.
 4
 5 SELECT 1
 6 USE Customer ORDER Cust_ID
 7 
 8 USE Account.DBF  IN 2 ORDER Cust_ID
 9 USE Cust_Acc.DBF IN 3 ORDER Acct_No
10 USE Transact.DBF IN 4 ORDER Acct_No
11 SET EXACT ON
12 
13 *--- Filter only records that have a match.
14 SET FILTER TO FOUND(2) .AND. (FOUND(3) .AND. FOUND(4))
15 SET RELATION TO Cust_ID INTO Account 
16 
17 SELECT Account
18 SET RELATION TO Acct_No INTO Cust_Acc, Acct_No INTO Transact
19 
20 SELECT Customer
21 
22 *--- Set one-to-many links to Account, Cust_Acc and Transact.
23 SET SKIP TO Account, Transact
24 
25 GO TOP
26 *: EoF: CUSTINFO.PRG


Next, we want to make sure that we only see records that are valid,
that is, we only care about a customer that has a valid account number
and an account number that has an owner, and so on.  So, we SET FILTER
to the Account, Cust_Acc and Transact databases as shown on line 14. 
This line tells dBASE IV to only show us records that have a match in
all the databases.  For example, if for some reason an error occurred
in the database where we have a customer in the Customer database
without a corresponding account number in the account database, then
this customer will be omitted from the relationship.  More explicitly,
what we are really saying here is that Cust_ID in Customer must have a
matching Cust_ID in the Account database, and its Acct_No in Account
must have a matching Acct_No in Cust_Acc and Transact.  Therefore, if
there's a break anywhere in the relationship, then the entire record
will not be shown.

As we have done in CustInfo.PRG,  Customer.DBF should be the master
file because we need to first know if that customer already has an
account with the bank.  This is to prevent duplicate customer
information from being entered.  If so, we would proceed to add the
other information to the other files only; otherwise, we would also
have to add the new customer into the Customer database.  Thus, the
CustInfo relationship will allow us to do this unambiguously. 

To join with an account, we should make the Account database the
master file, because, in order to have a shared account, we first have
to ask if that account number already exists.  Once we have verified
that the account exists, we have to check to see if the new applicant
is already an account holder with the bank.  If so, we simply make
sure that he doesn't already own the account that he wants to join
with.  Finally, we can proceed to add the new account holder to the
account database, and the customer database if he is not already an
existing customer.  Lastly, we will update the balance on the Cust_Acc
database, and record the transaction into the Transact database.

Now that you have all the database files linked together, how do you
append to them?  Well, the idea is to append a blank record into each
of the database files you want first, then replace the key field with
a value that would allow the newly appended records to be related. 
For example, suppose Jerry B came in and wanted to open a new account,
but he already has another account with the bank.  What has to be
added?  Well, we would have to add a new record to the Account,
Cust_Acc, and Transact database files.  We don't need to add a new
record to the Customer database since we already have that
information.  

What we would first do is append a blank record to each of the three
database files.  These blank records are not related to each other
until we replace their key fields with some pertinent information such
as the account number (in the Account and Customer files the key field
is the customer's Social Security number, in the Transact and Cust_Acc
files it's the account number).  The key field is usually whatever
field(s) the database is linked by.  When all the records are linked,
all that's left to do is prompt the user to enter the appropriate
information into the corresponding fields.

However, how do we make sure that the information is being entered
into all the newly appended records?  Figures 4 and 5 on the next page
show how the records relate before and after Jerry starts another
account.
You know you want the records for Jerry, but there's three of them,
and the one you want is the last one  the one you just appended.  How
do you position your record pointer to that record so that you can
enter information into the newly-added empty fields?  Basically, you
would do a seek for the person you want first, then you tell the
pointer to skip to the last record for that person.  Each time you do
a skip, you are moving through his related records.  When you have
exhausted all the records related to Jerry B, the Cust_ID will
change.  This is how you know that you have reached the end.  Take a
look at lines 47 through 49 in   NewAcct.PRG (Listing 2); it shows how
to do this with the SCAN/ENDSCAN command.  Remember that this is
possible because we have previously SET SKIP TO the files that may
contain multiple records for a given key field in CustInfo.  If we had
not done that, then we would be defining the link as a single
occurence link.  Thus, if we were to do a SKIP, it would simply show
us the first account belonging to that customer, and omit any
subsequent accounts.

Listing 2 NewAcct.PRG

 1 DO CustInfo       && Set up the relationship.
 2 
 3 BEGIN TRANSACTION       && Begin logging transaction for a new account.
 4    DO WHILE READKEY()<>270     && Continue until <CTRL-W> pressed.
 5       DO SSNo           && Accept social security no. into mSS_No.
 6       READ
 7       IF READKEY()=12    && If <ESC> was pressed, then cancel transaction.
 8          ROLLBACK
 9       ENDIF
10    ENDDO
11    
12    *--- Check for existing SS# from user input.
13    SELECT customer
14    SEEK mSS_No
15    
16    *--- If SS# does not exist in current database, 
17    *    proceed to add a new customer into the database.
18    IF .NOT. FOUND()
19       SELECT Customer
20       APPEND BLANK
21       REPLACE Customer->Cust_ID WITH mSS_No
22    ENDIF
23    
24    mRec = RECNO()  && Remember record # of the new customer added.
25    mAcct No = RANDOM()  && Generate unique random account number.
26    
27    *--- Add the new account to the CUST ACC database.
28    SELECT Cust_Acc
29    APPEND BLANK
30    REPLACE Cust Acc->Acct_No WITH mAcct_No
31    
32    *--- Add the new account to the ACCOUNT database.
33    SELECT Account
34    APPEND BLANK
35    REPLACE Account->Cust_ID WITH mSS_No, Account->Acct No WITH mAcct_No
36    
37    *--- Record the transaction to the TRANSACT database.
38    SELECT TRANSACT
39    APPEND BLANK
40    REPLACE Transact->Acct_No WITH mAcct_No
41    
42    SELECT customer     && Select the master database.
43    GO mRec             && Select the new customer record.
44    
45    *--- Move pointers in the related files, so that the current customer
46    *--- points to all the current corresponding records in the related files
47    SCAN WHILE .NOT. EOF() .AND. mRec=RECNO()
48    ENDSCAN
49    SKIP -1
50    CLEAR     && Clear the screen.
51    
52    DO WHILE READKEY() <> 270   && Keep going until <CTRL-W> has been pressed.
53       DO New_Acct       && Accept customer info, and account info from user.
54       READ
55       IF READKEY()=12    && If <ESC> was pressed, then cancel transaction.
56          ROLLBACK
57       ENDIF     
58    ENDDO
59
60    *--- Update the balance in CUST ACC database.
61    REPLACE Cust_Acc->Balance WITH Transact->Amount    
62 END TRANSACTION  && End logging of transaction for new account
63 CLOSE DATABASES
64 *: EoF: NewAcct.PRG


Note also the role that BEGIN/END TRANSACTION plays.  At anytime
before, during and after the input, a user can choose to abort the
entire process.  If the user chose to abort, then a simple ROLLBACK
command would restore everything to its original form.  In our case,
the database would be restored from Figure 3 to Figure 2.  This
transaction processing feature saves you from having to simulate the
rollback process yourself.      

Figure 2

Customer file  Account file   Cust_Acc file     Transact

#  Name     Cust_ID     #  Acc_No Cust_ID    # Acc_No Balance  # Acc_No Type
1  Jerry B  5211111287  1  10001 5211111287  1 10001  100.00   1 10001   D
1  Jerry B  5211111287  2  20002 5211111287  2 20002   50.00   2 20002   D
2  Linda R  5372222832  3  20002 5372222832  2 20002   50.00   2 20002   D

Figure 3

Customer file           Account file         Cust_Acc file     Transact
#  Name     Cust_ID     #  Acc_No Cust_ID    # Acc_No Balance # Acc_No Type
1  Jerry B  5211111287  1  10001 5211111287  1 10001   100.00 1 10001    D
1  Jerry B  5211111287  2  20002 5211111287  2 20002    50.00 2 20002    D
1  Jerry B  5211111287  4  30003 5211111287  2 30003          3 30003 
2  Linda R  5372222832  3  20002 5372222832  2 20002    50.00 2 20002    D

