1 2 Previous Next 15 Replies Latest reply on Mar 10, 2012 5:16 AM by pantarhei

    Record Locking...  Record Locking...

    paintboothguy

      Title

      Record Locking...  Record Locking...

      Post

      Good Morning All, 

       

      I need to be able to lock records, and I have found what seems to be the most popular way to accomplish this:

       

      http://fmforums.com/forum/showtopic.php?tid/210439/

       

      However, when I go to 'manage privileges'>'privilege sets', i see three entries:

       

      [full access]

      [data entry only]

      [read-only access]

       

      No matter which one I select, by pressing 'edit', I am unable to select 'records' in 'data access and design.' (they are 'greyed out.')

       

      Hence I am unable to complete the instructions in the thread above.  Can anyone tell me what I am missing?

       

      Thanks, 

      Andrew

        • 1. Re: Record Locking...  Record Locking...
          Steve Wright

          You need to create your own privilege set rather than using the pre-defined ones.

          • 2. Re: Record Locking...  Record Locking...
            paintboothguy

            Dear SWS, 

            I have created a new privilege set and continued with the instructions.  But when I get to this part:

             

            Then in the Manage Accounts & Privileges-->Privilege Sets-->Edit Privilege Sets--> Records, select Custom Privileges. In the Edit column for the table you want, select limited. Pick the field “Lock” from the list of fields.

             

            The table i 'want' is 'Quotes.'  When I select it, all columns EXCEPT the 'edit' column allow a change, and I am thusly unable to complete the instructions.

             

            Any Ideas?

            -Andrew

            • 3. Re: Record Locking...  Record Locking...
              mrvodka

              Make sure that the priv set has access to View to record first before changing the Edit settings.

              • 4. Re: Record Locking...  Record Locking...
                paintboothguy

                Good Morning, 

                 

                I have completed the instructions for record locking. (http://fmforums.com/forum/showtopic.php?tid/210439/)

                 

                However, the script does not lock the record, and I am able to make changes.

                 

                I believe this is because the account that has the new privilege set I defined in the above instructions is not the account I am using.  I can't figure out how to change accounts, or how to use the database via a specific account, as opposed to 'Admin.'

                 

                Can anyone help?

                 

                Thanks , 

                Andrew

                • 5. Re: Record Locking...  Record Locking...
                  Jade

                  For testing purposes, open your file while holding down the option key (shift key on Windows).  You will be prompted to enter the account name and password.

                   

                  To change the account permanently, select "File Options…" from the File menu and check "Log in using:" and "Account Name and Password" in the Open/Close tab.  Then enter the account name and password.

                  • 6. Re: Record Locking...  Record Locking...
                    paintboothguy

                    Jade, 

                     

                    Thank you for your response.  I am now able to log in as the user whose privilege set includes the record locking capability.  However:

                     

                    I need to have the ability to unlock a record:

                     

                    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 have completed this step.  While logged in as 'Admin' (full access), I ran the script specified in the instructions from my previous post.  I also created a script that uses set field to change the value of the field titled 'Lock'  to "1".  When I then sign in as 'User', (privilege set includes the record locking capability) ALL of the records are locked (when I attempt to make any change, I get an error message "your access privileges do not allow you to perform this action.")  When I run the script to change the field "Lock" to "1" it changes the content of the field, but I still am unable to make changes to the records.

                     

                    I am not sure where I have gone wrong.  Just to be clear, my partner and I use this database, and we need to protect records from accidental changes, none of this is a security issue, we just want to freeze records by pressing a 'lock' button.  We also need to be able to manually unlock the record.

                     

                    • 7. Re: Record Locking...  Record Locking...
                      Jade

                      Well, try this:-

                       

                      Edit the Privilege Set of your user.

                      Select "Custom privileges…" for "Records:"

                      Select your table that you want to lock/unlock

                      Set "View" to "yes"

                      Set "Edit" to "limited…" and enter Lock = 1 in the "Reords can be edited when" 

                      Set "Create" to "yes"

                      Set "Delete" to"limited…" and enter Lock = 1 in the "Reords can be deleted when" 

                       

                      (This assumes that Lock is 0 when you do not want the user to edit or delete the record.)

                       

                      Repeat the above for each table that you want to lock this way.

                      • 8. Re: Record Locking...  Record Locking...
                        paintboothguy

                        Jade, 

                         

                        Your solution worked for values that are calculated using fields in the 'quotes' table/layout.  However, there is a portal in this layout, and the solution does not work in the portal (i.e. I am unable to make changes.)  I can not view the table that reports to the portal in the 'custom record privileges' dialogue, so I can not set similar 'limited' access for 'edit' and 'delete.'

                         

                        Sorry if this is getting complicated.

                         

                        -Andrew

                         

                         

                        • 9. Re: Record Locking...  Record Locking...
                          Jade

                          Hi Andrew,

                           

                          Are you logged in as Admin with [Full Access]?  All Tables (not table occurrences) should be visible in the "Custom Record Privileges".

                          • 10. Re: Record Locking...  Record Locking...
                            paintboothguy

                            Jade, 

                             

                            Sho-Nuff, I am logged in as Admin with Full Access.  I guess I was referring to a table 'occurrence.'  I think I may be a little confused.  The portal on the Quotes layout gets its data from a copy of the line items table.  On the relationships graph, it is labeled 'Line Items 3.'

                             

                            I guess I assumed that if your solution worked for the calculated values, but not the info in the portal, I needed to apply the same custom privelages to Line Items 3, but it was not there!

                             

                             

                            • 11. Re: Record Locking...  Record Locking...
                              Jade

                              Try applying the custom privileges to the Line Items table.  Does that work correctly now?

                              • 12. Re: Record Locking...  Record Locking...
                                paintboothguy

                                Jade, 

                                 

                                Thanks for your help from some time ago.  I have still not completely solved this dilemma and am re-visiting it today.

                                 

                                So far, the problem with the privileges is in the portal, not the calculation fields in the invoices layout (they seem to lock just fine.).  When the Line Items table is not editable (Line Items::Lock = 0), previously entered products that are visible in the portal are not modifiable, but I am able to add a new item. Once it has been added, that item is not modifiable.  So, it's trying to work, but technically not locked from additions.

                                 

                                Can you figure what I'm missing?

                                 

                                Thanks!

                                Andrew

                                • 13. Re: Record Locking...  Record Locking...
                                  philmodjunk

                                  This is a known limitation. You can set up field validation checks on each portal field that check's your lock field and denies changes if the lock is true. You can also use a script trigger to check the lock status when this is the bottom blank row (Portal's key field will be empty) and denies entry if the lock condition is true.

                                  • 14. Re: Record Locking...  Record Locking...
                                    BERGSTEN

                                    Hi there!

                                     

                                    Looking for some help with this as well. I have a schedule table that we put rental days on and a linking table in between from the invoices table as rentals occur on multiple days. We want to keep a backlog of days on the schedule, but the other day someone entered a delivery in January of 2011 instead of 2012 and we nearly missed the delivery. EDIT: As a side, if I lock the linking records, will this have an affect on the invoice table as well?)

                                    I've set the lock for my main table (schedule), but like Andrew, I need to be able to lock the portal (Invoices/Schedule link). I've put a lock field in the linking table, but now I'm not sure how to proceed. I have 30k+ linking records-- do I have to set all the linking table's records from 2011 to Lock=0 by hand by running the script from this article on each record? 

                                    I only need to lock one field from being entered/changed and it will prevent the rest of the data from being looked up. It's a prmary key field that  just looks up and displays the info from the related fields. I can then add additional info from the line items table, but these fields are useless without the key so I'm not worried about those being edited. 

                                    I know I will also have to use the script triggor Phil i refering to in the last post here to ultimately prevent new entries in the portal but for now I just need to figure out how to lock the existing records.

                                     

                                    Thank you!

                                    Kim

                                    1 2 Previous Next