Audits - Recording field changes

[Karend440]

Hello!

Wonder if anyone has run into this. I have a layout, and within, a portal showing related records. (Parts for a particular job) When an update for a part occurs, I need that field, the user name, date and time to be logged - like an audit. The script I am using works fine, HOWEVER, from inside the portal (where the changes occur) it keeps logging the changes for first record in the portal or just repeating it into the audit log - not the one being updated. If I update the first record in the portal, that works ok... if I take the table/layout out of a portal, it works just fine, logging individual records. It's being in the portal that appears to be the problem. Each record has a unique ID number.

Anyone ever run into this?

Thanks, everyone, someone?

Karen

[brian rich]

Hi Karen

The best audit logging scheme I've seen is Ray Cologon's Ultralog system which you can find at this link.

http://www.nightwing...sX/demoX01.html

It uses a custom function, a log data field and some supporting fields and is free to use ( but check out his terms of use to be sure it suits your situation.) It works automatically for both user changes and scripted changes, so every time a record is modified, it logs details of the fields that have changed into the log data field. I create an audit layout for each table I install it in which contains the log data field and the other supporting fields, so it's easy to check out the changes made to any record.

If you install Ultralog in the related table, and just put the field you are trying to audit into the autoenter calculation in the log data field, I think this will do what you want - no scripts required - however you do need FMP Client Advanced in order to install the custom function.

HTH

Brian

[Karend440]

Thanks Brian! Yes, I've seen the Ultralog but figured since I already had this simple one 'almost' working it might of been something simple I was just missing. It befuddles me not understanding why it works off a layout with just that table fields in, yet when I put it into/display it through a portal it doesn't.

Just like your comment, I've heard other good things about the ultralog as well - so I'll give it a shot and try to incorporate it. Thank you for your advice! Appreciate it!

Regards, Karen :)

[Karend440]

Hi Brian,

I was wondering if you wouldn't mind helping me out with this? I am trying to incorporate the UltraLog into the solution I have, but I must be missing something. I've combed everywhere over the internet to find any type of instruction on how to do this. Using FM Advanced, I pulled in the custom functions, table and fields into the db I already have. I just can't make the 'connection' between it and my existing one. I listed the field I am trying to audit into the custom function "Superlog and Scriptlog" however, I don't know if that's correct. Is this, within the custom function where I am to add these? Also, do I need to relate the Ultralog table to mine, or do I pull in it's fields into the table I already have. I just getting lost somewhere with this. Custom functions were never something I really got into, but I can make a killer layout.. LOL I am just missing the meat of this here, somewhere...Thank you very much.. certainly appreciate your time! (and wisdom!!)

Regards,

Karen

[brian rich]

Hi Karen

Quickstart for Ultralog:

1. import the custom function Ultralog into the database where you want logging - more reliable than installing it by cutting and pasting

2. Open Ray's Ultralog demo, go to File>Manage>Database to open the manage Database dialog. Select the Ultralog table and display the fields.

3. Select the two fields LogData and ModStamp, and click the Copy button on the bottom right of the dialog. Close the dialog box.

4. Open your database; go to File>Manage>Database to bring up the Manage Database dialog and select the table where you want to install logging. In the field view of this table, the paste button should be active - click on it and the two fields you copied from Ultralog should appear in the list of fields.

5. Double-click on the field Logdata to bring up the Options pane. Select the Autoenter section, the click on the Specify button beside the Calculated Value - you should see the following:

UltraLog( LogData; ModStamp; "TheText¶TheText[2]¶TheNumber¶TheDate¶TheTime")

Replace the list of field names between the quotes with the name of the field you wish to log - afterwards it should look something like this:

UltraLog( LogData; ModStamp; "MyPartField")

(You shouldn't need the ¶ character unless you are auditing more than one field, in which case you'll need to seperate the field names with a ¶ )

Close the Manage Database dialog - logging is now installed.

Now go to the layout menu and create a new layout based on the table you want to audit. Drop an occurrence of the field Logdata on this layout. Suggest that you make this field as wide and high as your screen will allow, and set the font size to 9 point, as Ultralog generates a lot of (useful) data.

Switch to an approriate layout and make some changes to one or more records in the table you are trying to audit. Now switch to the audit layout and you should see the changes recorded.

Superlog and Scriptlog are not needed to make this work. You don't need to set up any relationships or scripts to get this far. You should not need to do anything other than the above.

There is more you can do to make this more sophisticated. However see if you can get the above working first. Let me know how you get on.

HTH

Brian

[Karend440]

Hi Brian -

Wow! I can't thank you enough! Very easy to follow directions, and it worked THE FIRST time like a charm! Really, I would of never figured it out, and it just gets so frustrating when you spend all that time on it and get nowhere. I really do appreciate your guidance here, tremendous help! Hopefully someone else will be able to use this instruction as well! Scripting and custom functions are just not my thing, I wing it most of the time because I have to, to get by, sometimes it works and sometimes not, and I move on.. LOL...

Thank you so much again! Best Regards!

Karen

[Daniel Jones]

Dear Brian

 

I have read this post with interest as im currebtly trying to install ultralog onto my own filemaker database. It has many fields that i need to audit and keep rack of changes, by whom and when. I have attempted to follow the instructions that you kindly posted previously but only seemed to be able to get a date/time recorded and only for one field.. Im sure im doing something wrong but im a bit of a novice when it comes to filemaker and wondered if you could be of any help? 

 

Thanks

Regards

 

Dan Jones

 

[brian rich]

Sorry Don, been on holiday for some while, just back in harness.

 

The Ultralog function takes three parameters:

 

UltraLog( LogData; ModStamp; "TheText¶TheText[2]¶TheNumber¶TheDate¶TheTime")

 

The last parameter is a list of the field names that you want to log - note that you don't need the table name.  As written above, the ¶ character is used to separate the field names and makes them into a list.  Note this must be the pilcrow character itself; just pressing the return key will not give you the correct list structure.

 

Personally, I use another text field defined in a separate one record prefs table, and populate the field using a custom function to collect the field names from the table I want to log. Much easier if you need to add or change fields during development, as the names in the list don't update automatically as you change field names in a table.

 

HTH

 

Brian