Kevin Smith

You may want to know how things have gone since I asked for advice about setting up an audit log. I gave the client an estimate for a fairly extensive audit log. She was not prepared to pay for that and as a result has settled on having a audit log for two fields only: "invoice line item amount" and "job name".

"Vortex of Death", yes it certainly sounds like it. Thanks to all of you for telling me about your experiences and encouraging me to scale down my expectations. Here's what I'm now thinking: 1. Don't obsess too much about having it look pretty for the client. If they are so concerned to have an audit log, let them have it in all its naked & confusing glory. 2. Keep it simple by automatically having all fields included in log. 3. Log entries for child tables must have their parent key stored with them. This will ensure that they display in the log for the parent record. 4. All log entries have a calc field that summarise/name the record they're from. This will help the user understand where the field comes from especially if it's from a related table. 5. If user complains about having too many cryptic or irrelevant fields in log then I'll add in a checkbox next to every log entry. Once an entry is ticked/checked then it and all entries like it (for same field name) get put in grey text. Thanks.

Thanks Vaughn and B, It's a request from the client. They want to know how much it's going to cost to have an audit log. I went to read some of the documentation on the Syncdek/FMDataguard site. There's a white paper there detailing the deficiencies of lower end solutions like the UltraLog. That was helpful to know some of the risks e.g. slow performance. Syncdek is great for rollback, but that's not what I'm after. I need to give the users an at-a-glance summary of changes made to their document e.g. an invoice. A document will encompass the current record as well as the related data displayed in that record. From what I can tell, SynkDek's not going to be any more sophisticated than UltraLog. Both of them will just record all changes made to fields. Its up to the developer to create an "abstraction layer" that summarises changes to make them meaningful to user. I now need to think through and decide how user friendly the audit log should be. Thanks for your help.

I've spent a while playing with Ray Cologan's Ultralog demo file. The file contains the building blocks for an audit log. It all seems nice and cosy when you're working on a flat file solution: a) autocalc field records changes to records in the same table and b] Looping script on FMServer can be used to push changes to an archive table. I'm trying to list the features I need to build into my solution and I'm feeling a bit uneasy about the extent of work involved. I've decided that the best way to show the modification log is to use a portal to display the archive records from the archive table. a) ARCHIVE PORTAL INCLUDES RELATED INFO: In the invoice record I'll use a portal onto the archive table to show all the changes made to that record. All records related to the invoice will also have their change data displayed in the invoice's archive portal e.g. invoice line items. b] SPECIAL FIELDS: Some info is stored as a key so this needs special attention. For example a dropdown of staff names stores a key that referencesthe staff record. The user won't be able to make sense of a log entry that says "StaffKey_kt changed from 'STA00035' to 'STA00058' on 1/1/2011 at 17:44". The best way to track changes to this info is to have a calc field called "StaffNameCalc". The audit log will track that field instead eg "StaffNameCalc changed from 'Robin Good' to 'Gorbin Rudd' on 1/1/2011 at 17:44" c) ADDING FIELDS: When I add fields to the Invoice table I must remember to also adjust the calculation that watches out for field edits and adds any changes to the modification log. I just feel I'm scraping the surface. I fear that there is a lot more work necessary to ensure that I display an audit log that will be meaningful to users. I also worry that it's going to add a whole layer of complexity to my system which will complicate future development. Am I overreacting?

Hi Just to let you know I experienced the same problem in FM Advanced 11 and 10. After rebooting things were back to normal. Phew. Kevin Smith

And thanks for putting me straight. I'm learning a lot too. Regards Kevin

Hi (it would be nice to know your name!) I guess I've steered away from it as I did not want to be reliant on both the IT Dept. and another computer system for the external authentication to work. I must also admit to having a mental block, I've been too stuck in following the usual FileMaker login method to think of the advantages of External Authentication; until now! Regards Kevin

Hi, External Authentication exploits the features of your fileserver. Windows server will have "Active Directory" available and on the Mac you'll have "Open Directory". So, if you're in an environment with a fully configured server, you're able to use these features at no extra cost. regards Kevin

Hello James At times it feels a bit like a religious divide between the "data-separationists" and the "single-filers" :-). I'm a single-filer. My upgrade script works OK, but requires updating every time I do a new version with extra tables. In addition, there is often the need to run one-off scripts to massage the data stored in a particular field in order to fit adjustments made to the system. As a result upgrades are rather a pain. I've chosen to stick with the built in password system. This means that the passwords the users have stored for themselves will be lost when the new system is put online. To overcome this they are given an interim password which they need to change after the first login to the upgraded system. I've taken this route in preference to storing user's passwords in a table within the database. The problems with that approach are: a) As a developer I have access to confidential information that I don't want to know. The passwords are stored in plain text. : There are many ways for a determined user to access passwords stored in this way. I may not have though of all of them when I develop my system. c) At some point I may have a client who is especially fussy about security and this setup won't do for them. I'll then need to develop, test and debug a different access rights system for them. d) The same password stored in the FileMaker system may also be employed by a user on many other systems she needs access to, in both her personal and work life. I would not like to have the responsibility for securing that info. Regards Kevin Smith