6 Replies Latest reply on Apr 17, 2013 7:08 AM by alquimby

    Best way to lock a record so once created it can't be changed.


      I have a frustratingly eclectic background with FileMaker and I need some advice on a strategy to lock records (or portions of records) once the record has been created.


      My needs are simple but that doesn't mean it's easy! Here's what I imagine being able to do. The database is for tracking firearms so the ATF puts some rather severe constraints on me.


      I have a form that has 3 sections; 1) all the info about the gun (serial number, caliber, country of origin, etc.) 2) I have a section that deals with the ACQUISITION of the fiream (date, who made the record, who did it go to, were they an FFL holder or an individual, etc.) 3) the DISPOSITION of the firearm (just the same fields as the "acquisistion" side). The ATF (technically the BATFE) requires that once a transaction is recorded it can NEVER be altered (as the DBA, I can, of course). So what I envision doing is this...


      They fill out the "gun info". Once they're happy with their entry they swipe a switch that locks that part of the record. Then they do the same for the acquistion portion of the record. Once they are satisfied and all data validation is satisfied, they swipe a "lock switch" on that part of the record. When they go to sell the firearm, it's the same as the acquisistion side... fill in the record... are you happy... swipe the switch... and CLICK... it's locked for ever. Some parts are never or rarely locked like having the ability to add a "note" to the record.


      My problem is that while I've been using FileMaker since BEFORE version 1.0 (I used to date the rep for Nashoba Systems!) and have used almost every version, its been for mostly mundane LAN admin "ditties". I delve deep into a few features, know the basics of scripting but what I'm trying to do now is like trying to overhaul an engine, looking at a tool box and saying "hmmm.... will I use a hammer... or should I start with a belt sander? Duhhhhh."


      There's so many functions and tools and approaches I could take it makes my head spin. I'll figure it out but I hate spending days of work only to find I chose the wrong STRATEGY, if you know what I mean.


      Here's one thing I did that "almost" worked. I made any one of those parts that I wanted to lock and I put a great big button over all the fields to lock (including the "lock" switch) behind the button. Then I used "conditional formatting" to make that big button transparent if the lock was open and opaque if it's locked... of course that didn't work... the fields are accessabe no matter if they're in front of an object or not. But it COULD HAVE been cool! Oh well.


      I see a bunch of "functions" in my tool box for managing security and could go that route but it just doesn't feel right. Is there code that I could come up with that says "If FIELD-A = 'true' then prohibit modification of FIELD-B" 'cause that's what I'm trying to do. I don't particularly want the code (although that would be nice), I just want to know if can be done and pointed in the general direction.





        • 1. Re: Best way to lock a record so once created it can't be changed.

          I use two layouts.  The first (restircted) does not allow the field to be entered in the browse mode only find mode (this is set up in layout mode, in the inspector, with a check box not checked  "browse").  The second layout (unrestricted) allows to enter the field in the browse mode ("browse" box checked in inspector).


          You can control access to the second layout as much as you need from the first layout.  Sometimes I just want to keep people from inadvertantly changing fields so I just put a "edit" button on the restricted layout.  That edit button takes them to the second unrestricted layout.  Other times I want only restricted people to be able to change a field or fields, then I will password the unresticted layout and the edit button will require a password to get to the unrestricted layout.


          Hope this helps,


          • 2. Re: Best way to lock a record so once created it can't be changed.

            Kevin --


            Both you and Dale are describing methods to prevent users from altering data based on the interface you provide for them.  This is not really security in the stricter sense; maybe we should call it "intentional interface limitations".  Remember, a savvy user can create their own file, use the username/password that already exists in the system you created, and make a layout that does whatever they want it to do.  Add in ODBC, PHP, and XML connections, and your system begins to look very insecure indeed.


            I suggest you go into the current security setup in FM12 (File>Manage>Security) and learn what can and cannot be done there.  This is where you can set up a flag field (1/0) that prevents anyone, even you, from editing a specific record.  (OK, you might be able to edit a record by resetting the flag, but you can make that pretty hard on yourself, too.)


            Then, go back through your system and consider setting up tables that are associated with the main record (like notes for an item/acquisition/disposition), which enable editing privileges that are different from their parent record.  That will restore some of the flexibility you need while protecting the original data.


            You might also consider some sort of audit trial mechanism, creating a log in a distinct table/file every time someone views/edits/deletes a record and what the prior information was before that action. 


            I hope this gives you some ideas on what can be done to make your system both secure and easy to use -- the real challenge of software design -- which FileMaker can handle pretty well.


            -- Drew Tenenholz

            • 3. Re: Best way to lock a record so once created it can't be changed.

              Both Drew and dhowbad, you both have great ideas. Just what I was looking for. Let me noodle those ideas over and see what seems to be the better approach.


              I was running into some interesting data data normalization issues and the ATF has so many requirements and so many "real life" issues that 3FN was hard to achieve. The "gun" and it's serial number are key... in fact primary key... wait... wait... not necessarily. Two manufacturers could have the same serial number so the PK is Mgr+serialNo, well sort of... until a gun is say rechambered to use a differenct size ammo. <heavy sigh>


              Issues like that... but what I thought about doing is like Drew mentioned and really having the "transaction" as the top of the food chain and just use a "portal" linking "things that happen to the gun" and "the gun" itself. Now copying the data to the portal, I can control at the "portal level" whether you can edit it or not. So that's bringing some fresh ideas to one of my old ideas.


              More soon...


              Thanks again for recharging my brain!

              • 4. Re: Best way to lock a record so once created it can't be changed.

                In a cash book database I have similar needs:  there is certain data (eg. amount of a transaction) which must not be changed once a record has been reconciled; there is other data (eg. expense category) which must not be altered once the record has been included in a tax return.  The system I use is similar to the flag notion Drew mentioned:


                1.     I have two boolean calc fields which return 1 when the record is reconciled (LockField 1) or included in a tax return (LockField 2)

                2.     For the fields which must not change I have set validate by calculation with a calc that referes to the relevant LockField (or fields if both apply), along the lines of:


                         If ( LockField 1 = 1 or LockField 2 = 1 ; 0 ; 1 )


                       The validation test will then prevent alteration if the relevant LockField is in place.


                Does that help at all?

                • 5. Re: Best way to lock a record so once created it can't be changed.

                  Belt AND suspenders.


                  RLA (security privileges) are probably needed. If you're dealing with actual government data, you should have real security.


                  But having interface-based restrictions is good too in order to provide a nice user experience (the .1% who figure out how to bypass the interface don't deserve the accomodation!).


                  Personally, I don't like different layouts...I like tab panels. You can have panels hidden, one with fields that allow entry, the other with fields that don't. An OnRecordLoad trigger detects the Lock field and navigates accordingly (as does a "lock swipe").


                  Script triggers are also a reasonable idea, by having an OnEntry trigger on the fields, you can check to see if the record is locked and exit out of the field.

                  • 6. Re: Best way to lock a record so once created it can't be changed.

                    Have you considered something like this:


                    You have an auto entry modification timestamp (can't modify auto) for each record. Your privilege for that table could be something like Records can be edited when Get (CurrentTimestamp) [or you could use Get (CurrentHostTimestamp)] < your modification timestamp plus 5 seconds, or so.


                    Al Quimby