3 Replies Latest reply on Sep 10, 2013 4:31 PM by philmodjunk

    Script Trigger Needed "Upon Record Exit"



      Script Trigger Needed "Upon Record Exit"


           I need to document the Account Name and Timestamp when an employee loads a record, and another timestamp when they exit that same record. An elapsed time documents how long the employee had that confidential record open for viewing.

           The purpose is to document both authorized and unauthorized access to confidential records. All employees have access to all records as they may have a need to know, but if they access a record they shouldn't have seen, then the database must document that access. It isn't necessary to modify the record, just to look at it. The employees, in turn, worried about discipline actions, asked for another feature that tracked how long they were on that record. If they were accidentally on a wrong record for a few seconds, the discipline should not be as severe as being on the wrong record for 30 minutes.

           I am using a relationship of two tables Client::Accessed By, with a portal on the client record. Two script triggers document when the user loaded the record (Time In) and when they exit the record (Time Out).

           The Time In script runs on "Upon Record Load" and works fine. It goes to the bottom of the portal, creates a new record, and sorts the new record to the top.

           The Time Out script would work if there were a script trigger that ran "Upon Record Exit," but that command doesn't exist. It goes to the top record and enters a value in the Time Out field.

           I tried "Upon Record Commit" but this command requires some record modification to work properly. If the record isn't modified, it will alter the last access record upon record load, before the Time In script creates a new access record.

           A snapshot is attached which shows the problem. If you look closely, you will see the Time In value on the top record is equal to the Time Out value on the second record. This pattern is repeated throughout the set of records. Every time the record loads, the Time Out script (based upon On Record Commit) runs first, changing the value of the top most Time Out record. Then the Upon Record Load script runs, creating a new top most record.

           I don't want the Time Out field to change upon Record Load or Record Commit. I want it to change upon Record Exit.

           Is there a work around for the missing command "Upon Record Exit"?


        • 1. Re: Script Trigger Needed "Upon Record Exit"

               Wouldn't it make more sense to make records that the current user is not authorized to view inaccessible? this can be done on a record by record basis and there are methods available that can screen out these records so that they don't even get the access denied screen for such records.

               What you describe could be set up but seems unnecessarily complex and one that could create major problems with your personnel if it doesn't work absolutely perfectly.

               You can set up your layout for viewing these records to be a form view record so that they can only see one record at a time. Then the OnRecordLoad trigger can be used to track who is viewing a given record and for how long a time. Each time they change records, the script checks a global field's value for the ID number of the record being left, logs the exit of that record and then updates it with the ID of the new current record.

          • 2. Re: Script Trigger Needed "Upon Record Exit"

                 I do have some of the features you suggest. I have a client list layout where the provider selects the proper client which, in turn, opens that one single client record. in form view. The menus prevent the provider from navigating from one record to the next, printing, or exporting those records. To find another client, they have to go back to the client list and start all over. It doesn't restrict the client list based on Account Name, but I can see how that might work in either portal filter or relationship criteria.

                 In any treatment setting, the person who has a "need to know" can suddenly change if the authorized provider is unavailable, and treatment is necessary. Preventing a new provider from accessing a record they weren't previously authorized to access could interfere with proper treatment, and finding a database administrator to change that access on short notice would be a problem too. This very small program (9 employees with no database administrator) does a lot of evening, weekend, and holiday care.

                 I suppose the provider him/herself could change the access ... and maybe that is the best work around, with each access change documented.

                 I do wish there were a counter part to OnRecordLoad which would be OnRecord Exit.

                 In your last paragraph you say two things I don't know how to do ... how the OnRecordLoad command can be used to track elapsed time, and how to log "the exit of that record." The first item suggests there is a timer function (I haven't used it if there is one). Is there a timer function, and what signals the timer to stop?

                 The second item suggests that record access can only be tracked if the user modifies a field. It is the nosey person who makes no record changes whom we are trying to track. Documenting who changes a record WOULD be useful, but my customer has asked for documenting those who simply view records.

                 Thanks for your assistance.

            • 3. Re: Script Trigger Needed "Upon Record Exit"

                        I suppose the provider him/herself could change the access ... and maybe that is the best work around, with each access change documented.

                   Yes that sounds like a much better approach as you then have very specific access setting changes being made to access specific records for a specific reason rather than a person just being able to access records at random.

                   If you haven't seen this section in FileMaker Help, I suggest that you: See "Editing record access privileges" in FileMaker Help and check out this particular sub section: "Entering a formula for limiting access on a record-by-record basis" for a description of how to set this up.

                   how the OnRecordLoad command can be used to track elapsed time

                   This is not a command. It's a script trigger that causes a script to be performed. It can log the current time in a variable when the record is first accessed and again when OnRecordLoad is next triggered. The difference will be the elapsed time and your script can log all the relevant information by creating a record in a table and entering the information. At no point in this process does the fact that user does or does not modify information play a part.


                        and how to log "the exit of that record

                   You don't actually. You log the entrance to the NEXT record and then "look back" to get the data about access to the previous record. That's why you would log each record's ID in a global field or variable. When the user accesses the first record, the script logs that record ID, say the value 123 in a variable and the time, say 4:00:00 pm in another. The next time the trigger is tripped, the script logs a record as Record 123 accessed by user A, on this date and time and spent (current time minus 4:00:00) seconds accessing record 123.

                   You would also need several more triggers to handle all the ways that the user might exit without going to a different records such as OnLayoutExit and OnLastWindowClose.