Hi,

I am new to FM7+ (currently trying to learn 8.5).

Is it a reasonable approach to create a single table to hold values
for all the files such as:

- Creation date/time
- Modification date/time
- Reject date/time

etc.

In version prior to 7, this was more or less nosense, as there were
other more important priorities to dedicate a file to (given the 50
files limit). But as in version 8.5 there is no such limitation, it
might be more productive to have a single table for those values and
inlcude the foreign keys for tables that might make use of the records
in this table.

Is this completely mad?

I would appreciate your opinion and ideas about this issue.

Thanks

I don't exactly see what you want. But it is not logical to store a
multitude of dates and times, just to relate to them. And if you need each
record (or each set of date/time) just once there is no reason why you
should split the data from the table where it realy belongs.

Relating data has its purpose in a one to many or a many to many
relationship. When it is strictly one on one just store the data in your
main table.

(Or I'm missing what you are trying to accomplish)


Ursus
"Carlos Pereira" <carlosp (AT) nnhotmail (DOT) com> schreef in bericht
news:tk85q2th3p0064jovbvvbt0t853e0ev495 (AT) 4ax (DOT) com...

Thank you Ursus,

Well, it happens that in many ocassions there are several Creation
dates, several Modifications dates, etc. THe reson for this is that a
record might be created by a user, then modified by a second user,
then desactivated (a client might teporarily be put away, then
recovered), then activated, etc.

As I see, I have the option to create those fields in the original
file as needed (not a good idea, I guess) or create a bunch or fields
to be ready for such situations, and create scripts to move the
dates/times from the original field to the Store fields (there can be
only one Creation date at the time, and the user wants to see the
Latest creation date...

What do you think now?


On Tue, 9 Jan 2007 00:03:18 +0100, "Ursus" <ursus.kirk (AT) wanadoo (DOT) nl>
wrote:

Why?

"Carlos Pereira" <carlosp- (AT) nnhotmail (DOT) com> wrote

I tend to agree with Bill. Why would you want such a detailed reference?

My guess is that if you really really need such a complicated system, I
would choose for a one to many relationship. Using a recordID to link the
records. But again: WHY?

"Bill Marriott" <wjm (AT) wjm (DOT) org> schreef in bericht
news:05qdnYKEIPYryz7YnZ2dnUVZ_qWvnZ2d (AT) comcast (DOT) com...

No, I can understand the desire to track changes to records. What I was
questioning was why the poster felt it was a bad idea to keep them in the
original/source table. I don't see a drawback to having TableName::Modified
existing in several tables. Conversely, I don't see an advantage (in fact,
several disadvantages) to having them all in a separate table.

"Ursus" <ursus.kirk (AT) wanadoo (DOT) nl> wrote

OK. I will try to explain.

A client/contact, etc, may move from Active to Disabled state several
times during its lifetime. The reasons can vary, but this is a real
life situation in the company that is going to use the database I am
building.

I need to record:

Creation User
Creation Date
Creation Time
Modification User
Modification Date
Modification Time
Demoting User*
Demoting Date
Demoting Time

* For Demoting I mean disactivating/disabling the record.

Each of this changes has to be recorded but also need to be available
for auditing (an auditor needs to be able to track who has made the
change and when). The users needs to see only one Creation
User/Date/Time. Thus, this needs to be the last user that modified the
record (or the user who last activated an inactivated record, or the
user who created the record initially - asuming no modifications has
been made).

There is no limit to the number of times that this cicle can go on: a
given record can be enabled/disabled several times (an average of 3
times) in a period, say of a year. I need to keeep track of all this.
I do not know in advance how many changes I need to keep track off.
Therefore, I do not know in advance how many fields I need (assuming
they are kept in the original table). In fact, I do not even know if a
given table is ever going to need this kind of functionality. It can
happen (of course I know for sure some tables do need it).

Does this give you a better picture of the issue?

Thank you for your previous replies.



On Tue, 9 Jan 2007 07:52:13 -0500, "Bill Marriott" <wjm (AT) wjm (DOT) org>
wrote:

The way I see it,

Creation User, Creation Date, Creation Time can be made one time when
creating the record.

From there on it depends on what you want to do with those dates and times.
When you only want to review them physicaly (look at them) I would go for
one large field per item. Where each date (or time or user) is stored but on
a seperate line. The field itself would be not editable, but changed through
a script when the user entered this record. So you would have a main layout,
or a search layout where the user has to push a button to start changing the
record. This way you could do with only six fields extra.

All becomes more complicated if you want to take automated actions dependent
on the contents of a field. You could make 10 fields each item (presuming
you would never go over 10 users or changes)

or you could use a field each with 10 repetitions (i would NOT really
recomend this, but then again I'm still not a personal favorite of
repetitions)

Or you could stick with my first suggestion and create a related table, this
is flexible enough to work with, but might get complicated when you need
automated decisions to be made.

Lets look what others might come up with.


Ursus

"Carlos Pereira" <carlosp- (AT) nnhotmail (DOT) com> schreef in bericht
news:uk47q2tgkkvujgm50tgs0nvcn3jmt1ka6p (AT) 4ax (DOT) com...

I understand your desire, as many types of businesses need to do
extensive logging of changes. If you are set on having this log
recorded in a separate table, I would look at using one of the free
script triggering plugins that have been discussed many times in this
group. That way whenever one of your fields of interest gets changed,
the script would automatically run to create the new related Log record.

But my preference really would lean toward a single text field within
the same file. Record a log of changes all within this field by making
it an auto-enter calc that updates itself whenever your desired trigger
fields (local to that table) are changed. For this you don't need the
plug-in and it should provide you a form that is sufficient for viewing
on screen.


Carlos Pereira wrote:
--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Howard Schlossberg (818) 883-2846
FM Professional Solutions, Inc. Los Angeles

FileMaker 8 Certified Developer
Associate Member, FileMaker Solutions Alliance

Interesting this was just announced today:

http://www.worldsync.com/audittrail/

* Capture all Add, Change and Delete actions to separate table [!]
* Keeps field-level change history
* Provide for field- and record-level data recovery
* Supports custom events like user login/out or layout change



"Howard Schlossberg" <howard (AT) antispahm (DOT) fmprosolutions.com> wrote