Audit Trail - An alternative approach

[Ugo DI LUCA]

Hi,

As this one got deleted and I still need to check what could be with you what could be the Drawbacks of this approach, here it is back.

...with some arragements though, as it is "quite" totally automated from creation to storage.

Thanks for any "constructive" feedback.

getAuditv4.fp5.zip

[Robert Schaub]

Think I like V3 the best

[Mariano Peterson]

Do you know of any way to only update the audit trail when the record has been exited, so that it doesn't create a new line until the record has been exited, in which case it only creates one new line that documents the changes to all four fields?

[Christian Coppe]

Fine, Hugo ! The previous proposed audit trails had sometimes a problem with the first change made in a record (a script was necessary to create a new record), but it seems it's not the case with your proposal. Can I suggest two improvement ?

1) To be totally compliant with some regulatory international rules, the REASON of the change should be documented. I built something to do so, by transforming each field in area to click to start a script which control if a comment field is empty or not. In this last case, it is impossible to change the data. The comment is then added to the audit trail.

2) It always remains the name of the user... This name is the one of the PC, not the true user. No idea to solve that, but I suppose that it should be linked to a login system. I think it's not so simple for a file shared on a network...

[Ugo DI LUCA]

Christian Coppe said:

The previous proposed audit trails had sometimes a problem with the first change made in a record

Yes, that's one of the specifics of this solution.

To be totally compliant with some regulatory international rules, the REASON of the change should be documented. I built something to do so, by transforming each field in area to click to start a script which control if a comment field is empty or not. In this last case, it is impossible to change the data. The comment is then added to the audit trail.

Fine, I had this in mind for some sensitive fields. I didn't added it here, but it can be done too.

It always remains the name of the user... This name is the one of the PC, not the true user. No idea to solve that, but I suppose that it should be linked to a login system. I think it's not so simple for a file shared on a network...

Well GetField( ) would be buggy in this particular case if unstored, so the only way I can think about is to have the g_Username transfered by script to a t_UserName field, and to format this field to auto-enter the g_UserName at record creation.

This would be possible if Navigation from records to records is scripted, with one script step.

Chopper said:Think I like V3 the best

Well, the only change is that while you had to push the History to the repeating Archive fields in V3, the process is here executed dynamically by some preferences.

Mariano Peterson said:

Do you know of any way to only update the audit trail when the record has been exited, ...in which case it only creates one new line that documents the changes to all four fields?

My feeling is that you can't have it done without a script.

Right now, the Audit would look like

[30/01/2004][12:15:40][t_textEntry][Null][substituting][ABC][Mariano Peterson]

[30/01/2004][12:15:28][d_dateEntry][10/01/2005][substituting][09/01/2005][Mariano Peterson]

If instead, each line was designed to look like

[t_textEntry][Null][substituting][ABC]

[d_dateEntry][10/01/2005][substituting][09/01/2005]

...

it would be rather easy to include a script step in any script that would prior on exiting the record (a navigation or any other script from your choice) :

- paste the content of the c_History to a global field

- substitute the "

[Ronald_K]

are all these functions (specifically GetField() included in FM 5.0 or were they introduced in 5.5 (Windows)?

Thanks

Ron

[Ugo DI LUCA]

Sorry, GetField( ) is FM5.5 and later, either Win or Mac.

You'd have to use the lookup solution, which several examples have already been posted here to make it work.

This said, I'm still unsure this method wouldn't fail compared to the "classic" lookup.

[Christian Coppe]

Ugo, a last remark I tried your audit trail by creating new record by three different ways : clicking your button (OK), CTRL+N (OK) and importing some new records from a simple ASCII file. In this last case, I was surprised that the audit trail didn't work. Any idea ? For another possible use, do you think that it works if an "update" is performed by the web ?

Christian

[Ugo DI LUCA]

Well, I'm not surprised it didn't work, Christian.

An Import would just by-pass the rules set, as no fields are entered. It's just as hard as to have a lookup from a SelfJoin appear while importting.

I'd suggest that when the Import gets scripted, a loop follows, going back from the first record imported to the last, entering each field in another loop and setting its value with a script

GoToField[X]

If [not is Empty (FieldX)]

SetField[fieldX, fieldX)

Else

GoToField[Y]

....

This could be long. So if instead of a ASCII file, you were using a FileMaker file, then it could be workable with some setting to the c_History calculation.

It would require that the records to be imported be flagged using a field say n_Import.

Then, when importing, the calc would look to the value imported in the flagImport field, and return ALL fields and their values.

Necessarily, this calc would be hardcoded.

Here's attached an evolution incuding "Import".

I did it quickly, so there's still one line showing up with some uninterresting stuff in it. I'd look to it to see how I can scratch it.

At least, it shows you can have it done too.

Auditv4ImportToo.zip

[Christian Coppe]

Thank you for your explanations, Ugo. Do you think it will work with an "update" command from a web site ?

[Ugo DI LUCA]

Christian,

Check the last attachment back. I've updated it so that you can use it even with a ASCII file.

It relies on the Status(CurrentScriptName), so there's now no need to mess with a flag in both the source and the final file.

Tell if this suits your needs.

[Ugo DI LUCA]

Christian Coppe said:

Thank you for your explanations, Ugo. Do you think it will work with an "update" command from a web site ?

As long as this update is scripted, using the Status(CurrentScriptName) is a guarantee for its success.

Be aware though that the calc could be too long according to the number of fields which value should be stored.

I've tried it with 15 fields just a minute ago, and it worked fine, but I'm unsure what limit would break the calc.

[Christian Coppe]

Ugo,

I have tried your new file and it works indeed. Great ! I have also created a quick web page (in Lasso) to add a new record. The audit trail is active, but the first change, in any field, doesn't render the previous value (other data are OK). Same behaviour by the use of an -update command by the web. Don't worry : I do not know an audit trail in FMP working well for changes made by Internet...

[Ugo DI LUCA]

Christian,

As stated in my first post (or was it before the hacking), this alternate solution was thrown here as an experimental solution.

Even if you found it useful and tried to implement it, I was still sceptic and concerned about its reliability in a real solution.

I linked the current thread into a neighbour forum in case someone could point the weaknesses of the GetField( ), and as expected, Ray offered his time to have a test with it.

Please read his comments.

field Trigger at FileMaker Caf