13 Replies Latest reply on Feb 26, 2013 10:40 PM by philmodjunk

    Backing up a deleted record? (FMP 10 Advanced)

    douglerner_1

      Title

      Backing up a deleted record? (FMP 10 Advanced)

      Post


           I need to delete a customer. I just delete the customer record, but I'd like to keep a record of it. I could mark the customer as deleted, but that's a nuisance to remember when doing searches and exports.

           What I'd like to do is add a new table of Deleted Customers and move that record from Customers into Deleted Customers.

           Is there an easy way of duplicating the table schema and moving a record between tables like that?

           Or any other good practice?

           Thanks,

           doug

        • 1. Re: Backing up a deleted record? (FMP 10 Advanced)
          philmodjunk

               Import records has a New Table option you can select for the target table and then the import records action both creates a new table and imports records into it, so this is one way you can duplicate a table. Once you have the duplicated table in place, import records can copy the found set of the original table into the new table. If you found set is only the one, soon to be deleted record, you have what you need.

               It's also possible to do this without Import Records. You could link the two tables by a relationshp that matches to the original table's primary key and then set up all fields in the back up table with looked up value settings to copy data over from the corresponding fields in the original record. You then just use Set Variable to capture the primary key field's value in the original table, Change layouts. Create a new record and use set field to set the corresponding field of the backup table and this action triggers a lookup to copy over the data.

               But personnally, I'd take another look at just marking the original record instead of deleting it. Script triggers can be set up, if desired, that automatically constrain any found set produced by a find to omit those "deleted" records.

          • 2. Re: Backing up a deleted record? (FMP 10 Advanced)
            douglerner_1

                 Phil,

                 I understood your first solution.

                 I didn't really understand the middle one.

                 The 3rd one sounds intriguing, but I don't know about Script triggers. Maybe I used to but forgot.

                 I'd like to be as simple as possible, but I don't want to worry about always having to omit deleted customers from the found set of Customers, and doing searches is something I'm likely to be doing a lot.

                 One other complication - there are already multiple tables. For example, Customers and Contacts. Contacts are the people who are contacts at the Customers (which are organizations).

                 I could share an active/deleted radio button between all of them linked to the value set at Customer.  Still, how would I default to alwys searching the active customers and only include deleted customers as needed?

                 Thanks,

                 doug

            • 3. Re: Backing up a deleted record? (FMP 10 Advanced)
              philmodjunk

                   Script triggers are a feature that was first added with the release of FileMaker 10. You can look them up in help.

                   If you specify the OnModeExit trigger for you layout and select "Find". You can set up a script that runs automatically each time you exit find mode (Which is what happens when you perform a find.) The script can constrain the found set produced by the find to only include "active" contacts:

                   Enter Find Mode [] --> clear the pause check box
                   Set Field [YourTable::YourStatusField ; "active"]
                   Set Error Capture [on]
                   Constrain Found Set []

              • 4. Re: Backing up a deleted record? (FMP 10 Advanced)
                douglerner_1

                     Well, I read the help page on script triggers. And I read your example over and over. But I really don't feel comfortable with it or understand it very well.

                     I think for now in the not-to-often case a customer cancels I'm just going to have to export the info in the customers record (and associated tables), save them elsewhere and delete the records in the active database.

                     The problem is one of how much time I want to spent learning to do it a fancy way as to just getting some work done. :)

                     Thanks though. It is something to look back at later.

                     doug

                • 5. Re: Backing up a deleted record? (FMP 10 Advanced)
                  JimMac

                       @Phil.... 

                       The middle idea was very creative!cool  You left out deleteing the original record though.

                       @ Doug 

                       Marking it deleted is very simple plus allows "un-deleting" easier.  You can always use colors in Deleted Records to assure to user if he somehow overrides the Deleted marker.  I have serveral DB's which purge Deleleted Record at end of year by making a "Historical File" of that years Deleted records and actually eliminating them for speed.

                       Jim...

                  • 6. Re: Backing up a deleted record? (FMP 10 Advanced)
                    JimMac

                         I forgot...

                         @Doug 

                         Make a marker either Active or Deleted and all Finds can have =Active without OMIT.

                         Also relastionships can use that in the Link without fear or trouble.

                         Jim...

                    • 7. Re: Backing up a deleted record? (FMP 10 Advanced)
                      philmodjunk
                           

                                The middle idea was very creative!  You left out deleteing the original record though.

                           Deleting the original record? The suggested method is intended to avoid the need to delete any record...

                      • 8. Re: Backing up a deleted record? (FMP 10 Advanced)
                        JimMac

                             Table Original has a record to delete.  Your move to a New Table, call it DeleteTable,  was very slick.yes (refer to your second paragraph idea.)

                             It can also be a great way to do a record UNDO.

                             Original Table must "delete" the record after you Copy (lookup) it to the Delete Table otherwise it stays there.  I agree with you, marking it and not moving it is much easier.

                             Jim...

                        • 9. Re: Backing up a deleted record? (FMP 10 Advanced)
                          philmodjunk

                               Ok, I was looking at the wrong post when I tried to figure out which idea was the "Middle" idea. wink

                          • 10. Re: Backing up a deleted record? (FMP 10 Advanced)
                            douglerner_1

                                 Still a bit not sure what to do. None of the ideas seem particularly easy or reliable to me going forward. If I just mark a record as deleted I am 100% sure I will mess up in the future and accidentally include it with the active records.

                                 Also the record is linked to records in other tables, like the contact(s) for the deleted customer.

                                 A dilemma.

                                 doug

                            • 11. Re: Backing up a deleted record? (FMP 10 Advanced)
                              philmodjunk

                                   A more extreme way to keep deleted records from being accessible is to set up a "lock expression" that checks the value of the field that marks it deleted and denies access to the record if it is so marked. This does not work for full access users, but can be set up to work with all others.

                                   See "Editing record access privileges" in FileMaker Help and check out this particular sub section: "Entering a formula for limiting access on a record-by-record basis" for a description of how to set this up.

                              • 12. Re: Backing up a deleted record? (FMP 10 Advanced)
                                douglerner_1

                                     There is only one user in the case - me. :)

                                     doug

                                • 13. Re: Backing up a deleted record? (FMP 10 Advanced)
                                  philmodjunk

                                       Which does not mean you can't set up the file with a limited access password for every day use, switching to full access only when you need to make design changes.

                                       And there are any number of ways to set up conditional formats to put really obvious flags on deleted records and if you set up scripts for setting up reports, you can build the criteria that omits deleted records as part of the script.