11 Replies Latest reply on Aug 20, 2009 5:44 AM by MikeyG79

    Can you "lock" a record?

    LarsBC

      Title

      Can you "lock" a record?

      Post

      Hello,

       

      I've got a small problem. I have a database where I need the regular users to be able to go in and add and edit records - but only up to a certain point. I need to be able to change the status of a record so it goes from "in use", and users can edit it, to "locked", where only admins can edit it, on a record-per-record basis. Is this possible? I haven't seen anything in the users guide about anything like that.

       

      The logic behind it is that each record is an order, and users can delete and edit it while it's still pending, but once it has been delivered, it's historical data that needs to be preserved as it was at the time of delivery.

       

      Thanks,

      Lars

        • 1. Re: Can you "lock" a record?
          ninja
            

          Howdy LarsBC,

          Thanks for the post.

           

          You'll find some guidance both here:

          http://fm.lithium.com/fm/board/message?board.id=FM-en-4&message.id=18507#M18507

           

          and in the FMP help for accounts and privileges via calculation.

           

          Set a field that toggles between "in use" and "locked" (radio button perhaps?) and use that field in the users access privileges.

           

          Let us know how you progress!

          • 2. Re: Can you "lock" a record?
            LarsBC
              

            Thanks ninja! It seems really complicated from the link you provided - and like an issue that's brought up over and over again with no easy answer. Not being a regular IT or database person, I feel in slightly over my head. It seems to me like access privileges are generally set for a table or a field as a whole - not for individual records in a field. I'll search the help file for mentions of accounts and privileges via calculation.

             

            Thanks again! :)

            • 3. Re: Can you "lock" a record?
              LarsBC
                

              After a quick look, it does say in the help file:

                

              "To allow or prohibit record viewing, editing, and deleting privileges to certain records within a table, you need to specify a Boolean calculation that determines whether the privilege is permitted. For each record in the database, access is allowed when the calculation evaluates to True or to a non-zero result, and access is prohibited when the calculation evaluates to False or zero."

               

              That sounds all well and good, but it doesn't explain where I need to go or what buttons I need to press to set up this boolean calculation, nor where or how I connect the boolean value to the privilege set. It later says:

               

              "Then use the following calculation when defining custom record access privileges:"

               

              But when I look in the custom record access privileges options under Manage Accounts and Privileges, all I see are drop down selections, nowhere to enter calculations.

              • 5. Re: Can you "lock" a record?
                LarsBC
                   Thanks, I see the calculation options now. One thing puzzles me though - if I set a field to “Prohibit modification of value during data entry”, then when can you ever modify it? When are you not doing data entry?
                • 6. Re: Can you "lock" a record?
                  mrvodka
                     Since it is value is going to be changed by a script, you will be fine.
                  • 7. Re: Can you "lock" a record?
                    LarsBC
                      

                    I'm not sure how to work the calculations either. The calculation field lets me type in "[Field name] = 1" as a calculation, but it doesn't let me type "[Field name] = [Field Text]".

                     

                    I want the calculation to recognise that when a specific field has a specific value, the record should not be editable, but I want that value to be text rather than just 1 or 0...

                    • 8. Re: Can you "lock" a record?
                      LarsBC
                        

                      OK, I see. Maybe you can explain in more detail what it means when it says to use a script to set the Lock field to 0. Does it mean add a button and set that button to run the script? Would you have to make a second script to set the Lock field back to 1, should you want to?

                       

                      Quote referenced: "When the circumstances occur that cause you to want to lock the record, use a script set to “Run script with full access privileges” to set the field “Lock” to 0. If “Lock”=1, the default entered value, the record can be edited. If “Lock”=0, the record cannot be edited."

                       

                      I'm starting to understand how this works, but I still don't get why you can only use the values 1 or 0 in the calculation.

                      • 9. Re: Can you "lock" a record?
                        mrvodka
                          

                        LarsBC wrote:

                        OK, I see. Maybe you can explain in more detail what it means when it says to use a script to set the Lock field to 0. Does it mean add a button and set that button to run the script? Would you have to make a second script to set the Lock field back to 1, should you want to?

                         

                        Quote referenced: "When the circumstances occur that cause you to want to lock the record, use a script set to “Run script with full access privileges” to set the field “Lock” to 0. If “Lock”=1, the default entered value, the record can be edited. If “Lock”=0, the record cannot be edited."

                         

                        I'm starting to understand how this works, but I still don't get why you can only use the values 1 or 0 in the calculation.


                         
                        Well yes. Some action should cause it to lock correct? So in your case, when it has been delivered, it should set the field to lock. I dont even think you need a field to state that it is "in use" etc.
                         
                        Personally I would do this way...
                         I would create a checkbox field using a value list called flag which stores the value of 1. This way your flag field will either be empty or have a value of 1 ( which will render as checked )
                        Next instead of Lock=1 and all that when putting in the calc for limited editing, I would just type in NOT Lock as the calc.
                        Turn off the auto-enter of 1 for the lock field, which Steven suggested to incorporate.
                         
                        Now, when a new record is created, those users with that privilege set will have access to edit the record because a new record will have the Lock field empty. The NOT Lock translates to not [ Empty ] ( because lock is empty ), which results in 1 ( true ).
                        Now when you finally decide to lock the record, the lock field will ahve the value 1 in it. NOT lock now translates to not [1], which results in 0 ( false ).
                         
                        When you are editing the limited  access in the privilege set, you will see in the bottom left hand corner explaining how this access stuff works.
                        Non-zero values are true ( access is allowed )
                        zero and empty values are false ( access is denied )
                         
                        It uses a boolean test. If all this boolean stuff confuses you, just do a search and read up on it.

                        • 10. Re: Can you "lock" a record?
                          LarsBC
                             Thanks mr. vodka, that's great advice! Ideally I'd like for the calculation of the privilege access to be based on an already existing field (ie. when Order Status = Delivered -> lock), but it sounds like the check box is a simpler and viable alternative.
                          • 11. Re: Can you "lock" a record?
                            MikeyG79
                              

                            Another (not so great option IMHO) would be to use different layouts. 

                            If the user is allowed to edit, go to a layout with editable fields

                            If not, go to a layout where the fields aren't editable.