Dear group,
As usual I am here with some questions to improve my clinical reserach
DB running on FM 9 advanced Server (Win 2003) and FM 9 advanced (Win
XP) on 10 clients.
At this time, the DB use an autoenter auto-incrementing serial number
as primary key.
Every time I create a new record, a new number is created on 10
different tables so that they can relate together. This works pretty
well, except when I have to delete a record (usually the last one). In
that case I have to re-adjust manually the serial number in all the
tables, to avoid that some serials will be lost or a different
counting among the tables.
This means also that no one but me can insert the new patient in the
database, to avoid confusions and problems. As you see this is
completely unacceptable.
Looking on-line I have found a discussion on a public FM mailing list
(Filemaker Talk run by Yahoo, I guess) presenting the same problem.
Here I report the solutions suggested, to discuss what is the best in
your opinion and what solution you would adopt:
I Method
For my opinion the best way to solve such a problem is by use a
Number field with an auto-enter calculation as follows:
If ( IsEmpty ( Self_Prefs:: Rec_Nr ) ; 1 ; Max( Self_Prefs:: Rec_Nr )
+ 1)
It means that you must create a self relation to the table (or file),
the one I call Self_Prefs. That relationship is based on a number
field which has always the same number, so as any reference to it
will display the whole contents of the table. Without this
relationship if you isolate one record and create a new record, that
new record will take the number next to the one you isolate (find),
if your table or file name is Prefs.
As you see the only calculation you really need is,
Max( Self_Prefs:: Rec_Nr ) + 1),
but what happens if the table or file is empty? The relationship will
not work. Thats the case of the "if" function, it gives an initial
number so to begin the process. If delete any record, and not the
last record only, the next new record, regardless if it is in a found
set or or all the records, will take the correct number.
No Scripts
No Buttons.
II Method
>
> > I would recommend using an auto enter calculation instead of the
> > serial number. You can then do a simple formula:
> >
> > Get(TotalRecordCoun t)+1
III Method
> > > Simple approach to serial number issue:
> > >
> > > Setup a separate table with a field to track serial numbers. Then
> > each time a new serial
> > > number is needed, check the current number in the serial number
> > table and increment by
> > > one. Save the new number to the serial number table, and use it
> > for your product tracking.
> > > The serial number table will always contain one field unless you
> > want to track other
> > > numbers, date of last serial#, etc.
I hope there is nothing bad in quoting as above, since the list and
the mails are public.
Thank you as always for any advice,
Diego
|
