5 Replies Latest reply on May 13, 2011 3:43 PM by easyway

    Locking data after entry



      Locking data after entry


      I'm having difficulty in uncerstanding how to lock data after it's entered so that it cannot be inadvertently (or maliciously) be changed.

      I've read about "triggers" which will check a "Lock" field but I can't see how to arrange a script which will prevent the field from being modified. We have multiple users who need ongoing access to records and they need to be able to modify some fields but there are some fields which must not be changed once the correct data is entered.

      Can anyone help?



        • 1. Re: Locking data after entry

          Option 1: Move the fields that must be locked into a separate table linked in a one to one relationship to your original table. Since you can lock an entire record in FileMaker, you can now lock records in this related table to prevent changes while still permitting changes to the fields in the original table. This method does not require any scripting.

          To lock records, 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 detailed description of how to set this up.

          Option 2: You can set up a validation rule on each field that rejects any changes made to the field if a lock field is set to a value that marks the record as locked. This avoids splitting the records into two tables, but also allows the user to attempt edits of the field, only to reject them while throwing up an error message when the record is comitted.

          Option 3: You can use the OnObjectEnter script trigger to perform a script that checks the Lock Status of the record. If the record is locked, the script uses either go to field or Go To Object to move the cursor/focus to the next unlocked field or some other object (such as a button) on the layout. This can be easier to implement, but can also confuse the user when a cursor "jumps" to an unexpected location on your layout.

          Option 4: Use OnRecordLoad and OnRecordCommit triggers to perform a script that selects a different layout depending on whether the record is or is not locked. The layouts can be exact duplicates in appearance, but behavior settings on the locked record layout can be set to deny access to the fields while in browse mode.

          Sometimes Options 2 and 3 or 2 and 4 are both used, with the validation rule as an "insurance policy" that insures the locked fields can't be changed should the developer accidently leave a "loop hole" in the layout design that permits access to the field.

          • 2. Re: Locking data after entry

            Thanks PhilModJunk for the help which sounds fine. I like the sound of option 2 which would throw up an error message when an attempt isa made to change a committed field, however I can't find any help in Filemaker to explain how to set up a lock field. I've looked through all the help content to no avail. This might sound a bit lame to an experienced FM pro but how do I do this? I've worked with Filemaker for some time and have been able to solve most problems but not this one.

            Any further help would be much appreciated

            • 3. Re: Locking data after entry

              A "lock" field is just a data field where you enter/select a value or a calculation field that returns a value. Your validation field option then uses a calculation that checks for a value in this field to control whether changes made to a data field are accepted as "valid".

              Say you have a check box field named "lockfield" with just one text value to select and it enters the text "locked" into it.

              Open Manage | database | Fields and double click a field definition to bring up the Field Options dialog for that field.

              Click the validation tab and select the "by calculation" option.

              Enter IsEmpty ( Lockfield ) as its validation calculation and set the other options you need to control whether this is a warning that can be overridden or is a ironclad validation rule that won't accept any changes when the lock field has a value in it.

              • 4. Re: Locking data after entry

                I use a combination of techniques

                I have seperate security tables where I keep track of field level audits and access by user id.

                Then I have script triggers on  the fields to check the security and either return access denied messages or process the on enter values into global fields where I write out audit records into an audit table. So I capture (userid, current layout, current record, current field, old value, changed value, timestamp)

                By applying these script triggers to all fields on my layout I can then manage which fields I want auditing or security on in a seperate security layout.

                I create a table of only global fields and form a relationships between the security tables and the global table. (usually by UserID, record and fieldnames)  You can decide which triggers  to use for which functions.

                This also makes setting variable access for different users fairly simple without having to open up access to database design functions  to other users as you can restrict the security admin layouts to only certian users.

                You can go crazy (as in overboard) with this concept too because it allows you to lockdown and audit virtually anything in the database in so many ways. So if you are up to it I suggest starting small with only a couple of fields.


                • 5. Re: Locking data after entry

                  Thanks to you both for the replies and help. I managed to use your method, PhilModJunk and it's working fine. I'll keep both sets of solutions for future reference as I think varying conditions may need different treatment in future.

                  Thanks again to you both