Project Files and Database
More often than not, the
primary key field of a database table is composed of a single field which is
autoincremented whenever a new record has to be inserted. However, it is sometimes
necessary to have a primary key field composed of multiple fields. Take for
instance the example of a table containing a list of Companies, whereby each
company has multiple Account records in a related table. Whereas the primary
key field of the Companies table is a single autoincremented field, the Accounts
table has a compound primary key field. The compound primary key field is composed
of an AccountID as well as the CompanyID of the parent Company record:
The process of inserting
records into the Companies table can be accomplished with an ordinary record
form since the primary key is always generated by the database. However, when
inserting new records into the Accounts table, a Custom Insert
has to be performed. Before a record is inserted in the Accounts table, the
form has to receive an input parameter specifying the CompanyID of the parent
company which will then be combined with a user specified AccountID to make
up the compound primary key.The implication here is that the Accounts record
form cannot be accessed directly but rather has to be accessed via a link which
contains the CompanyID of the parent company. One way to do this is to have
a page with the record form for the Company records and also a grid form for
the Accounts belonging to the company as shown below:
In this case, the grid
form with the Account records has a Add new Account link which leads
to the record form used to insert new Account records. The link defines an output
parameter containing the CompanyID of a company so that the CompanyID will be
accessible when adding a new Account record.
The link with the CompanyID
URL parameter leads to the AccountEdit page which has a record form used to
add new Account records. In this record form, the Custom Insert Type
property is set to Table whereas the Custom Insert
property specifies the fields to be used in the insert operation as shown below.
Notice that whereas the AccountName and AccountID values are received from a
form, the CompanyID value is received from the URL parameter specified above.
As such, the user specifies the AccountID and Account Name in the record form
then when the form is submitted, the CompanyID is retrieved from the URL and
used to create the compound primary key.
We do not need to specify
Custom Update or Custom Delete properties
because once the AccountID and CompanyID values have been inserted into the
database table, both values can simply be retrieved from the database when editing
a record. The insert operation is unique because the CompanyID comes from a
different table whereas the AccountID is specified by the user.
you use any of the builders to create record forms, you are allowed to specify
only one primary key field per table. However, after the form has been generated,
you can edit the Data Source property of the record form to add input parameters
for other fields that make up a compound primary key.