Title: How to generate numbers in desktop Master-Detail databases
Question: Sometimes you need a small desktop master-detail database application, with some specific requirements found in Client-Server Databases.
Answer:
1) Your Master table need an unique ID number. In access or paradox you
can generate an auto-increment field, but if a record is deleted it can
mess up your data.
Oracle, Interbase etc. use for this a generator or counter stored on
the database which fires everytime a new record is inserted.
2) The same applies to the detail table to number the items in sequence.
Unfortunately on the desktop databases it is not that simple
Approach:
MASTERTABLE
Create a table which stores the next ID number(1 record, 1 field). When
inserting a new record;
a) Open the counter table
b) Retrieve the number
c) Increase the counter
d) Close the counter table;
DETAILTABLE
Run a query on the items in the DetailTable to obtain the maximum
value on the items, increase the maxium with 1, and store the value as
the new ItemNo
EXAMPLE:
From time to time you have do safety inspections with your boss and
others. They point out the problems which have to be fixed. The list is
long and every week it is growing. How to keep track of your progress?
Certainly the DBA will not let you poke in the Corporate Client Server
Environment for such "simple" thing. You have to use plain access, or
paradox or so.
SETTING UP THE DATABASE
Create your MasterTable:
WT_ID --- integer {Unique Number}
Member1, Member2, Member3, Member4 ---- character_field(20) {Inspection team}
Owner ---- characterfield(20) {responsible for the area}
InspectionRoute ---- character_field (50)
Date ---- datetime_field
Put a key on ID
Create your CounterTable
NextRecord -- integer;
fill in your starting value (100);
Create your DetailTable
WT_ID ---- integer
NR ---- integer
COMPLAINT ---- characterfield(40)
ACTION ---- characterfield (20) {the guy or dept who has to clean the mess}
TARGET_Date --- datetimefield
COMPLETED_Date ---- datetimefield
Put a combined key on WT_ID and NR
PROGRAMMING
Now we will start with the Delphi Part
Needed: DataModule with:
1x TDatabase { DbWalk (handy if you use passwords on your tables)}
3x TTables {taINSPECTION, taITEMS, taNEXT}
2x TDataSource {dsINSPECTION, dsITEMS}
1x TQuery {LastItemQuery}
This is the code for the datamodule
-----code fragment -----------------------------
unit dmWalk;
interface
uses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms,
Dialogs,
Db, DBTables;
type
TDmWalkThrough = class(TDataModule)
DbWalk: TDatabase;
NEXT: TTable;
NEXTREC: TIntegerField; // Number stored
----------------------------------------
taINSPECTION: TTable;
taITEMS: TTable;
----------------------------------------
dsInspection: TDataSource;
dsItems: TDataSource;
----------------------------------------
//Properties Inspection Table
taINSPECTIONWT_ID: TIntegerField;
taINSPECTIONDATE: TDateTimeField;
route: TStringField;
member1: TStringField;
member2: TStringField;
member3: TStringField;
member4: TStringField;
LocOwner: TStringField;
----------------------------------------
//Properties Items Table
taITEMSWT_ID: TIntegerField;
taITEMSNR: TIntegerField;
taITEMSCOMPLAINT: TStringField;
taITEMSACTION: TStringField;
taITEMSTARGET: TDateTimeField;
taITEMSCOMPLETED: TDateTimeField;
--------------------------------------
// items Counter query
lastItemQuery: TQuery;
LastItem: TIntegerField;
procedure taINSPECTIONNewRecord(DataSet: TDataSet);
procedure taITEMSNewRecord(DataSet: TDataSet);
private
{ Private declarations }
public
{ Public declarations }
FItemNo: Integer;
end;
var
DmWalkThrough: TDmWalkThrough;
-----------End Code fragment --------------------------
SYNCRONIZING
The Items table must be syncronized with master "INSPECTION" table
to do this set:
1) Mastersource = dsInspection
2) Masterfields = WT_ID {click on ... to invoke the field link
designer}
3) IndexFieldName = WT_ID
To syncronize the Items Counter Query "LastItemQery" set:
1) DataSource = dsItems
2) SQL = SELECT max(Items.NR) as lastNum from ITEMS
WHERE WT_ID =:WT_ID
3) Params : DataType = ftFloat
Name = WT_ID
ParamType = ptUnknown
4) With the fields editor bring in lastNum and Name it LastItem in the
Object Inspector
EVENT HANDLERS
Genearting an Unique number for the Master Table
procedure TDmWalkThrough.taINSPECTIONNewRecord(DataSet: TDataSet);
begin
with NEXT do
begin
Open;
try
edit;
taINSPECTIONWT_ID.Value := NEXTREC.Value; // retrieve number
NEXTREC.Value := NEXTREC.Value + 1; // update counter
Post; //close the Counter table
taINSPECTIONDATE.Value := Date; // Today
menber1.Value := 'Boss'; //Put in some default values
member2.Value := 'Small Boss';
LocOwner.Value := 'Me';
finally
Close;
end;
end;
end;
Generating the sequence of the item Numbering
procedure TDmWalkThrough.taITEMSNewRecord(DataSet: TDataSet);
Var
MasterRecordExist : boolean;
procedure GetItemNumber;
begin
MasterRecordExist := false;
LastItemQuery.Close;
LastItemQuery.Open;
FItemNo := lastItem.Value + 1;
if FItemNo 1 then
MasterRecordExist := true;
end;
begin
GetItemNumber; // Fire LastItem query
if MasterRecordExist then
begin
taITEMSWT_ID.Value := taINSPECTIONWT_ID.Value;
taITEMSNR.Value := FItemNo;
end;
end;
FINISHING TOUCHES
The rest is standard, you have to create a master-detail form, with a
DBNavigator, dbedit fields and a DBGrid. For the WT_ID field of the
master use a DBText field to prevent editing of the ID.