1 2 Previous Next 20 Replies Latest reply on Apr 15, 2011 9:41 AM by Terri

    Audit Trail

    Terri

      Title

      Audit Trail

      Post

      I found a post from last year that details specific auditing software brought to you by such companies as NightWing enterprises. Unfortunately my company will not approve a consulting firm at this time. Is there any other way within FM Pro 11 that will allow me to track changes to records, including who, what, when?

      Thanks!

        • 1. Re: Audit Trail
          philmodjunk

          Check out the auto-enter field options.

          These can log when a record was created. WHen it was modified and by whom.

          Since this only records the most recent changes, more sophisticated approaches use scripting to maintain log files of the needed info to track record edits. This can be simple or complex depending on the needs of your system and how many different tables you need to set up with an audit trail.

          • 2. Re: Audit Trail
            bumper

            The software from Nightwing is an unlocked demo file that you can add to your own solution at no cost. It is not a beginner's task, but if you take your time it works and works quite well considering the cost. The latest version is called UltraLog, which I believe works with FMP 9 and above.

            • 3. Re: Audit Trail
              Terri

              Unfortunately my company also blocks any unauthorized downloads and getting that approved is unlikely. Phil, can we discuss the scripts? I need something more than just the who and when. I really need to know who changed it and why. In a perfect world I would have a window pop up everytime a field is changed that allows the user to put a comment (such as changed per X) and then that is recorded in another layout by record. Or what about a portal for each field or record? I'm just full of ideas. The good news is I think it is for one table. The bad news is I have no idea how to start.

              • 4. Re: Audit Trail
                philmodjunk

                I think it would be easier to make a private download of the file so that you can examine it and see concrete examples of how this might be done. You might even be able to bring in a sample on your lap top as a demo to get approval to include the nightwing produced code as part of your solution.

                If you truly must "roll your own", you'll need to familiarize yourself with script triggers, variables and possibly script parameters. You'll need to work out exactly what data you want to log.

                Just to get you started, an OnObjectSave trigger can be set on a field to perform a script that pops up a custom dialog where the user must enter a reason for the change. A parameter to this script can capture both the original value of the field and also the name of the current field. Then your script can create a new record in a "change log" table where you've defined fields to record: The SerialNumber ID of the record changed, the name of the field changed, it's original value, the reason for the change, the accountname and/or user name of the person making the change and the date/time the change was made.

                • 5. Re: Audit Trail
                  Terri

                   I have asked the IT department to look into NightWing. As with most big companies, that could take a while. Could you help me work through a quick solution? I would like a different layout, in a new window, to pop up once a record is changed. That other layout is simple, the record name and a comment box where the user can write a brief description of what they changed, and that comment section keeps a running record of what is changed. It doesn't have to be field, just by record. The comment section would look like this:

                  4-5-11 - created record - tander

                  4-15-11 - changed due date - tander

                  4-25-11 - changed delivery date - tander

                  Make sense?

                  Thanks!

                  • 6. Re: Audit Trail
                    philmodjunk

                    For a script, I'd use this:

                    Show Custom Dialog ["Reason For this change/new record ?"]  //use gChangeNote--a global field as an input field.
                    Freeze Window
                    Set Variable [$ID ; YourTable::Yourprimarykey ]
                    Go To Layout [Change Log]
                    New Record/Request
                    Set Field [ChangeLog::ID ; $ID]
                    Set Field [ChangeLog::ChangeNote ; YourTable::gChangeNote]
                    Go To layout [original layout]

                    Define adate field in ChangeLog that auto-enters the creation date so that the date a change was made is automatically entered with each new record. OnRecordCommit can be the script trigger that performs this script.

                    You can relate your existing table to change log like this:

                    YourTable::Yourprimarykey = ChangeLog::ID

                    A portal to ChangeLog from a layout based on YourTable will list all the changes made to that record. (You can also sort the portal rows by change date in descending order to list the most recent change at the top of the portal.)

                    • 7. Re: Audit Trail
                      Terri

                      For some reason when the dialog box opens and I entered the reason for the change, both ok and cancel do nothing. In fact, I have to force quit FM to get anywhere.

                      For simplicity, the field that is related in these two tables is called Media Code. My tables are called Catalogs and ChangeLog. Could you update your script steps with those identifiers (I know, elementary but it helps). I struggle with set variables. Could you explain what the set variable is doing and why? In the meantime I'll research my FM Bible.

                      Thanks!

                      • 8. Re: Audit Trail
                        philmodjunk

                        Are you able to close the dialog? That should happen automatically. With the script example above I'd remove the cancel button. If you want to allow users to cancel without logging a change, you'd need to use get (LastMessageChoice) to check to see which button was clicked.

                        Here's a demo file you can download and experiment with:  http://www.4shared.com/file/mY3ZtnLm/SimpleAuditTrail.html

                        • 9. Re: Audit Trail
                          Terri

                          I had to download the file as a zip file. Hopefully you can still view it.

                          You will never believe how long this message became until the light went on and I realized what the problem was. I have the script trigger set for the change log layout as well (instead of just the catalogs layout). Once I fixed that I no longer had the dialog box issue.

                          I need some more help.

                          As you will see in the Master Global FH11 layout, I have many, MANY fields pertaining to each record. At the creation of a record you could imagine I get interrupted often before completing the entire data entry. I don't want this change log script to run every time a keystroke is made. I often enter data into a field and then hit enter. Key in a date and hit enter. As you can imagine, that change log will become my nemesis. So, I have a plan. I need the dialog box to say "Is this a new entry?" If yes, then it does nothing and stays on the same layout. Otherwise, "Is this a change to an existing record?" If yes, then it prompts the change log. I'm not sure how to modify the script to react correctly with the push of the change button. I think it will probably still be a pain to have to bypass the dialog box every time I create (or enter) info for a new record but as long as the new entry is the primary button it should just be an "enter" "enter" which could be done quickly.

                          2. I love your Portal on your file you sent me! And although mine looks similar, it isn't working. Well, when I hit the primary button on the dialog box, it goes to that layout and has my change note. But when I make another change to the same record, it only shows that change note (in other words, it doesn't compile them like yours does). Each change note is a new record. Please help with that. I suspect this may be because I have the gChangeNote and ChangeNote used incorrectly, but even when I play with that it doesn't work.

                          3. I modified the script so that when a change is made it goes to the change log layout at the end of the script. This is just to help me make sure the change log is working right. Eventually I can change that back. 

                          I only saved a few records on this file, but in truth, we have about 3 years worth of data, so there are actually 1300 records. But in order to keep the printed version to as few a pages as possible, I don't want the layout changed. Therefore, I was debating on how to view this change log for each record. I'll probably just add a button close to the media code or book name that someone can click on and it will take them to the change log related to that record. That is simple enough for even a novice like me. If you have a better idea, let me know.

                          That was a long explanation to a couple of simple questions (if you can find them imbedded in the message). I'll be experimenting with it while I wait for your correct solution (as always).

                          Thanks! (P.S. That "script cannot be found alert" doesn't seem to really mean anything and only shows up on files that I copy from the original and put on my desktop.)

                          http://www.4shared.com/file/wnxc8j4w/ChangeLog2fp7.html

                          • 10. Re: Audit Trail
                            philmodjunk

                            Simply pressing Enter to move from field to field should not trigger this script nor should every key stroke trigger it. OnRecordCommit performs the script when you click on the layout background, when you navigate to a different layout or record or when you close the file.

                            Why not log the change on record commit, but modify the layout design so that the record cannot be commited until the user either clicks a "save" button or tries to navigate away from the layout or record?

                            This may sound weird but it works: Put an empty web viewer object on the layout behind all the other layout objects sized to cover the entire body of your layout. You can specify options for the viewer's border to make it invisible while in browse mode. This web viewer will intercept all mouse clicks to the layout back ground so the record will not be committed until the user either clicks a button to commit the changes or tries to leave the current layout or record.

                            2) Since the portal is based on a relationship linking your current record to the changelog records, you have to have a field in your layout's table that uniquely identifies each record. The value in this field is copied to a variable and taken to the change layout and then entered into the new change log record to link it to your current record being changed. The portal has also been set to sort records by creation date in descending order so that the most recent change is listed at the top of the portal. If you do not have room on your current layout for this portal, you can always create a second layout to the same table and place your portal on that layout. You can also script a button that takes you to the change log layout where you can display the change log records sorted in list or table view format if you prefer.

                            PS. Couldn't open the file as it is locked with a password.

                            • 11. Re: Audit Trail
                              Frinholp

                              In the meantime I'll research my FM Bible.

                              I see Phil is helping you at the moment with a solution, but if you  own a copy of the FM Bible, there is some useful reading on this topic  towards the end of the book. It even covers Nightwings solution.

                              Integrity and Security - Making FileMaker Systems Fail-Safe which discusses a few methods to log changes and even roll-back facility.

                              Lee

                              • 12. Re: Audit Trail
                                Terri

                                Regarding record commit: that is a great idea.

                                Regarding the portal. The field that uniquely identifies each record is the media code, which I assumed was represented in the script by the: set variable [$ID; Value:Catalogs::MediaCode] and my relationship reflects this. So now what?

                                Sorting records, done.

                                This portal is on the changelog layout rather than the catalogs layout due to space.

                                Try to open the file again. Select guest rather than entering a username and password.

                                http://www.4shared.com/file/wnxc8j4w/ChangeLog2fp7.html

                                • 13. Re: Audit Trail
                                  philmodjunk

                                  This portal is on the changelog layout rather than the catalogs layout due to space.

                                  That's the problem. Don't put this portal on the changelog layout. It won't work there. It must be placed on a layout that refers to your original table. You can create as many layouts to the same table as you need. On the change layout, you can perform a find for all records with a given valud in meidaCode and display the change records in a list or table view instead of using a portal to show the change history if you want.

                                  • 14. Re: Audit Trail
                                    Terri

                                    Hi Phil,

                                    Just getting back to this. You are right, putting that portal on the catalogs layout fixed the problem. Everything was working wonderfully until I added a portal to the change layout. I honestly don't know if that would have anything to do with this error, but when I change anything on the record (which does prompt the dialog box) and then enter the reason and hit enter I get this message:

                                    “Media Code” is defined to require a value, but it is not available on this layout. Use another layout to assign a value to this field.

                                    Media Code is on this layout. Nothing else changed. It was working great before. Not sure what is going on.

                                    I've attached the file for reference. Note, nothing is formatted the way I really want, this is just for testing. Use Guest to open it. Disregard first script error message.

                                    http://www.4shared.com/file/BeAGO94r/ChangeLog_Copyfp7.html

                                    1 2 Previous Next