13 Replies Latest reply on Jun 22, 2017 9:33 AM by piaccounting

    Restrict deletion to the last record of a table

    piaccounting

      Hello all

      I am trying to prevent a record from being deleted unless it is the last record.  I thought I had found a solution, following the ideas
      explained at How to know when last the record in a selection is reached? and Determine Last Portal Row?  to try the privilege
      settings. But then hit a snag.

       

      I created a privilege set and assigned “user” to it. I’ve then gone into “Data Access and Design” and set:

      Records to (initially) create, edit and delete in all tables

      Layouts to All view only

      Value Lists to all modifiable

      Scripts to All executable only

      .. and, eventually, Available menu commands to All

       

      This effectively allowed the required access to my layout.

       

      Then in order to restrict to deletion of last record only I updated:

      Records to Custom privileges.

      In the Custom Record window I set everything to yes, except the delete of my particular table. I Set the option to limited and entered the
      following in the calculation window:

      Get ( RecordNumber ) = Get ( TotalRecordCount )

       

      On logging on as “user” everything appeared okay at first. But then I found I could add new records and delete those.

       

      I am speculating that what is happening is that on opening TotalRecordCount is say 175. I cannot delete record 175. If I add record 176. I
      can delete it (because it is not record number 175). So it appears as though the TotalRecordCount is not being updated. Is my reasoning correct and if so is there a way to force a refresh (that effects the privilege set) when needed?

       

      Presumably, an alternative would be to disable the Menu Commands and add some buttons to the layout to control “add” and “delete”.

       

      Thanks for any feedback.

        • 1. Re: Restrict deletion to the last record of a table
          Magnus Fransson

          Hi piaccounting,

           

          piaccounting wrote:

          I am trying to prevent a record from being deleted unless it is the last record.

          ...

          But then I found I could add new records and delete those.

           

          Those two lines seem to contradict each other. Whichever is correct try with either:

          Get ( RecordNumber ) = Get ( TotalRecordCount ) or

          Get ( RecordNumber ) ≠ Get ( TotalRecordCount )

          to find out which one fits your case.

           

          A warning though. Get ( RecordNumber ) counts against current sort order. That means that it can be fooled, by sorting the records in such order that a "chosen" record becomes the last in the sort order.

           

          With best regards Magnus Fransson.

          • 2. Re: Restrict deletion to the last record of a table
            piaccounting

            To clarify, I only want to be able to delete the last record. The Get ( RecordNumber ) = Get ( TotalRecordCount ) is the one I am using and it does works  ( and trust me I've tried numerous permutations getting my head around it ). There is no contradiction because the calculation does stop me from deleting the last record; however if I add new records (plural) I can subsequently delete those new records; but I do not want to be able to delete them unless the new record (of several new records added) is the last record. The issue appears to be that the security privilege knows the total record count when opened but is not being updated if and when new records are added. Hope that makes sense. Thanks. 

            • 3. Re: Restrict deletion to the last record of a table
              piaccounting

              To put it into context; each record represent one day and there will always be one form for every day, because when a new form is added the date is calculated. The form has a brought forward figure from the previous day. Therefore I do not want to allow deletion of any record other than the last day. Thanks

              • 4. Re: Restrict deletion to the last record of a table
                Magnus Fransson

                Hi piaccounting,

                 

                As far as I know, (and I might be wrong) all Get() functions are always evaluated "on the fly". That is, will always reflect the state as is "right now". (Taking current sort order in count.) That means that you will always be able to delete "the last record" no matter how long ago it was created. In fact you will be able to delete all records in the table, as long as you do it in the order of "the last record first".

                 

                You will probably need some other means to stop users from deleting “confirmed” records.

                 

                With best regards Magnus Fransson.

                • 5. Re: Restrict deletion to the last record of a table
                  Magnus Fransson

                  Hi piaccounting,

                   

                  Actually, reading your last post I imagine that a calculation based on a field "CreationDate" and Get(CurrentDate) might suit you better.

                   

                  With best regards Magnus Fransson.

                  1 of 1 people found this helpful
                  • 6. Re: Restrict deletion to the last record of a table
                    piaccounting

                    Magnus Fransson wrote:

                     

                    Hi piaccounting,

                     

                    That means that you will always be able to delete "the last record" no matter how long ago it was created. In fact you will be able to delete all records in the table, as long as you do it in the order of "the last record first".

                     

                     

                    Hi Magnus - that is the behaviour that I want but not getting. I am going to re-visit it because I am getting different behaviour on different records. On selecting Delete Record from the menu sometimes it asks me if I want to Delete Entire record and then tell me insufficient privilege whist on others I get the insufficient privilege message straight away. A bit of detective work needed. Thanks for your help.

                    Dave

                    • 7. Re: Restrict deletion to the last record of a table
                      Magnus Fransson

                      Hi piaccounting,

                       

                      Just remember, you have to show all records and go to last record, before you can delete.

                       

                      If you show all records, sort them on creation timestamp, in descending order (newest first) and goes to the last record in the found set (of all records), (being the first created record), you will be able to delete that record, because "Get ( RecordNumber )" will equal "Get ( TotalRecordCount )", because sort order dictates that it is the last record in the found set, despite being created first.

                       

                      With best regards Magnus Fransson.

                      • 8. Re: Restrict deletion to the last record of a table
                        piaccounting

                        Magnus Fransson wrote:

                         

                        Hi piaccounting,

                         

                        Just remember, you have to show all records and go to last record, before you can delete.

                         

                        If you show all records, sort them on creation timestamp, in descending order (newest first) and goes to the last record in the found set (of all records), (being the first created record), you will be able to delete that record, because "Get ( RecordNumber )" will equal "Get ( TotalRecordCount )", because sort order dictates that it is the last record in the found set, despite being created first.

                         

                        With best regards Magnus Fransson.

                        Thanks for your feedback.

                        I stripped back the database to carry out a controlled test with 3 records. I got the immediate insufficient privilege on record 1 and 2 and then on record 3 got the “do you wish to delete” question followed by the insufficient privilege. On adding record 4 and 5.  Trying to delete record 4 gave immediate insufficient privilege and record 5 both “do you wish to delete” question followed by the insufficient privilege.

                         

                        The help files suggest that the GET functions are “real time” in this scenario as you thought.

                         

                        You mentioned using current date and creation date; although this would not give me what I want it prompted me to consider whether checking that the date of the record was the maximum date in the table. I already had a MaxDate summary field so I replaced my Get ( RecordNumber ) = Get ( TotalRecordCount ) with Date = MaxDate.

                         

                        It worked. As you indicated maybe there was some discrepancy with record number based on sort (although the table was never sorted – well not knowingly). The MaxDate method should do away with such complexity.

                         

                        Adding a test to make sure a new record cannot be added beyond the current date should be simple now...

                         

                        Thank you for your assistance. Much appreciated.

                        • 9. Re: Restrict deletion to the last record of a table
                          MichaelManousos

                          You can flag the records with a boolean value so whenever you make a new record the previous one you mark it as 1 and the new is flagged 0 you can restrict the delete with the privilege meaning allow deletion if flag field equals 0

                          and when you script your delete when deleting the record to mark the "new" last record as 0 so you can eventually delete one by one from last to newest

                          1 of 1 people found this helpful
                          • 10. Re: Restrict deletion to the last record of a table
                            piaccounting

                            MichaelManousos wrote:

                             

                            You can flag the records with a boolean value so whenever you make a new record the previous one you mark it as 1 and the new is flagged 0 you can restrict the delete with the privilege meaning allow deletion if flag field equals 0

                            and when you script your delete when deleting the record to mark the "new" last record as 0 so you can eventually delete one by one from last to newest

                            I do like that idea. As I already have the MaxDate summary field and I may use it in preventing additional records being added beyond the current date I'll use the "if it's not broken - don't fix it" approach. Because I have scripted brought forward values I will want to restrict editing of records to the MaxDate record; otherwise I have to do the recalculation. Not a problem and will probably work a script for it  even if just for practice. I can think of a few places to use that idea so thanks.

                            Dave

                            • 11. Re: Restrict deletion to the last record of a table
                              piaccounting

                              MichaelManousos wrote:

                               

                              You can flag the records with a boolean value so whenever you make a new record the previous one you mark it as 1 and the new is flagged 0 you can restrict the delete with the privilege meaning allow deletion if flag field equals 0

                              and when you script your delete when deleting the record to mark the "new" last record as 0 so you can eventually delete one by one from last to newest

                              Am returning to this option sooner than I thought as although my MaxDate option works in most practical cases in my solution it is nevertheless flawed because the MaxDate also appears to be calculated on the found set.

                               

                              Using your approach: setting the previous record back to 1, would I believe, require "all records" present. Is this correct? Also (I am just starting to write scripts now) there is a "Show All Record" - is it a Show all records; find record and change flag; return to found set -  type arrangement? Many thanks

                              • 12. Re: Restrict deletion to the last record of a table
                                MichaelManousos

                                freeze window

                                go to a layout (just having the field with the id)

                                show all records

                                unsort records

                                go to record last

                                set field from 1 to 0

                                go to layout original

                                unsorting the records meaning they are 'sorted" with the creation order

                                do you use a primary key a serial number or a uuid? if you use a serial number you can sort them with the serial although I think just unsorting them and go to the last is faster

                                • 13. Re: Restrict deletion to the last record of a table
                                  piaccounting

                                  Perfect. Thank you. My ID field is an auto-enter Serial. If on further testing the unsorting/go to last trips me up I'll know what to do. Thank you for your help and guidance - much appreciated.