6 Replies Latest reply on May 22, 2013 6:33 PM by RickWhitelaw

    Once a record is committed, is it possible to restrict modification of the key field's value

    RyanF

      Title

      Once a record is committed, is it possible to restrict modification of the key field's value

      Post

           Hi,

           I am a newbie to Filemaker (FMPv11) and am working on creating an asset management database (used for managing a tape vault where we can track the location of an asset based on it's barcode information). I'm having trouble figuring out a specifc field behavior and am hoping you can help point me in the right direction. In my solution, our staff needs to be able to enter an asset barcode number into the primary key field. Once commited, I'd like the field to be locked so users can't accidentally modify the data. I however, need a way for them to be able to intentional override the lock if they need to modify the barcode # (we often need to modify the barcode to use our client's barcoding convention instead of our internally generated barcode). I envision it working this way:

           User enters barcode# in primary key field

           After field is commited, field is locked

           If user tries to enter the primary key field again, a dialog box opens up asking/confirming they wish to edit the field data. If yes is selected, user is allowed to mofify field data until commited again.

           Any help on how to set this up would be greatly appreciated.

           Thanks in advance.

        • 1. Re: Once a record is committed, is it possible to restrict modification of the key field's value
          ninja

               So you don't want to really restrict the field...just give a "You sure???" prompt.

               Caution:  After about two days, people start ignoring such prompts.  You are settng yourself up for problems.

               How many times have folks clicked "OK" when asked if they wanted to install Malware?  Many, many, many.  About the same number who click "Accept" on the terms and conditions of software without reading the terms.

               I would recommend having a "Lock Barcode" checkbox instead.  Then use Accounts&Privileges to restirct editing of the record when the value in that field is "Locked".

               As part of the barcode capture (OnModify script trigger) set the Lock Field to "Locked" and put the checkbox out of the way on your layout so that people have to intentionally go get it to unlock the field.

          • 2. Re: Once a record is committed, is it possible to restrict modification of the key field's value
            philmodjunk

                 Ninja's commetns make sense, but sometimes you still want that confirmation check.

                 You could set the onObjectEnter Trigger to perform a script that saves the current value of the barcode field in a global variable.

                 Then the OnObjectSave Trigger could perform a script that asks for confirmation using Show Custom Dialog. If the user decides not to change the field, the script can copy the original value back out of the global field into the barcode field to restore the original value. The script could even create a new record in a related table and store the original value when the user does choose to change the value--thus establishing an audit trail on your barcode field.

                 BUT:

                 I do not recommend that you use this field as your Primary Key field to link to other related tables. Use an Internally generated serial number field instead. DO have such a field for the bar code in your database for data entry and to perform searches to find records, but not as your Primary Key.

            • 3. Re: Once a record is committed, is it possible to restrict modification of the key field's value
              RyanF

                   Thanks for the responses. Ninja, yes, I'd like a "you sure" prompt. You bring up some vaild points for potential issues, however the purposes of this vault system is to update our current system which is running on a very old version of FMP and users are accustomed to the way it is currently setup and we have no issues. On the vault ID field, once the record is commited, if the user tries to even enter the field, a dialog box pops up with the following message "This record already has a vault ID. Are you sure you want to "enter" this field and risk changing the vault ID information for this record?" and there is a button for "enter" and one for "cancel".

                   What is the easiest way to set this up?

                   Thanks PhilModJunk. Good advice for the pk field.

                   Thanks again!

                    

              • 4. Re: Once a record is committed, is it possible to restrict modification of the key field's value
                ninja

                     Make the VaultID field a button which launches a script of the type:

                     Show Custom Dialog [Are you sure you want to do this...this is bad juju going on here]

                     If [Get(LastMessageChoice) = 1 ]

                            GoToField [ Table::VaultID]

                             Exit Script

                     EndIf

                     Commit Record

                      

                     Also: Make sure to remove this field from the Layout Tab order so that you can't inadvertently bypass the button.

                     Note that you can also launch this type of script OnEnter to the field...but I prefer to launch it prior to entry by using the button method.

                      

                     later Edit:  Make sure to "select entire contents" of the field since it is a barcode...it is an option in the Inspector tabs.

                • 5. Re: Once a record is committed, is it possible to restrict modification of the key field's value
                  RyanF

                       Thanks for your help Ninja. Will give this a try.

                  • 6. Re: Once a record is committed, is it possible to restrict modification of the key field's value
                    RickWhitelaw

                         Do not ever give the user access to a primary key field. Period. There really is no reason to do so. Allowing it, warning or not, is asking for trouble. However, if you must, perhaps try an OnObject Keystroke trigger that checks if the PK field is notEmpty and disallow modification.