11 Replies Latest reply on Jan 29, 2010 8:10 AM by tritechsol

    Records mysteriously being deleted (NOT the usual suspects)

    tritechsol

      Title

      Records mysteriously being deleted (NOT the usual suspects)

      Post

      This is one of the oddest things I've encountered in my 12+ years of FileMaker development.

       

      I have a database in use at a client site where records in the "Employees" table are somehow being randomly deleted. I've confirmed that they exist one day and then are gone the next (often times the record exists in the morning but is gone several hours later).

       

      No one has delete access but me so I know this is not a user error.

       

      I've run the DDR report through Inspector and evaluated every relationship (there are NO cascade deletes) and script (no script deletes records in that table) that could possibly be causing this. This is a separation model system so I checked both the UI file as well as the data file. Absolutely nothing is set to delete those records.

       

      As with many FileMaker solutions, this is a mission critical database and I'm now concerned about the data integrity. While I am currently running maintenance on the files, I'm wondering what I'm overlooking?

       

      Any and all recommendations welcome. Surely I'm missing something?

       

      Much thanks,

       

      Yvonne Garcia

        • 1. Re: Records mysteriously being deleted (NOT the usual suspects)
          StellaLuna
            

          Hi Yvonne, I would first check all scripts which allow deletion and make sure they either are fired from button on layout based upon the table occurrence they should be deleting or that they include a go to layout first.  Then I would check all GTRR steps.  If go to related is fired and there are no related records, script can continue and delete the parent record it is sitting on.  Then I would check all portals with button to delete and make sure it says delete portal row and not delete record.  Corruption usually doesn't present as you suggest; it sounds more like something is deleting them.  :smileyhappy:

          • 2. Re: Records mysteriously being deleted (NOT the usual suspects)
            tritechsol
              

            Hi, Stella,

             

            That's the odd thing. According to Inspector, there is only one script that uses that table and it doesn't delete anything. And the table is pretty insular so there are no GTRR scripts that go to it, nor is the data used in a portal row anywhere. And it does not display any data via a portal row either so I know it isn't the 'delete portal row' problem. It has one delete button and I'm the only one with access privileges to delete in that table. And I'm using custom menus so the 'delete record' is not available via the menu bar either. I've triple-checked all of this, which is why it is such a mystery. I was expected to find something I had overlooked in the programming but have not yet come across it.

             

            I appreciate the response and will go back and check everything again today. I would love it to be something I overlooked since then I don't have to worry about data integrity.

             

            Thanks,

             

            Yvonne

             

             

            • 3. Re: Records mysteriously being deleted (NOT the usual suspects)
              philmodjunk
                

              Just throwing out a possibility here....

               

              Is it possible that the record is being overwritten instead of deleted?

               

              Two possible scenarios:

              1. An import records uses an update matching option to overwrite the data with completely different data
              2. A user enters data that unknowingly changes the values to the point it no longer "looks like" the same record.

              In light of your two file structure, you might check to see if each Table Occurrence in your interface file points to the correct table in the data file as that might explain a possible "item 2" scenario.

               

              • 4. Re: Records mysteriously being deleted (NOT the usual suspects)
                jhogan1
                   There is the other much more frightening possiblity that your access has been compromised.
                • 5. Re: Records mysteriously being deleted (NOT the usual suspects)
                  tritechsol
                    

                  Well, thankfully this was not a security breach. Nor was it a damaged file. It was a broken script that was going to related records via a specific layout in another file  that would then delete a record. The layout it was going to was inadvertently deleted so instead of deleting the record from the corrent table, the go to related records step was landing on a layout for a different table. Since the scripts didn't invoke the problem table, it wasn't showing up in the Analyzer report as being dependent on that table. I finally looked at all the problems in the solution and found the 'unknown layout' in a script. Fixed that and the problem has resolved.

                   

                  Thanks for the various suggestions. 

                   

                   

                  • 6. Re: Records mysteriously being deleted (NOT the usual suspects)
                    philmodjunk
                      

                    Go To Related Records is a very useful tool, but which is very poorly documented and some of the gaps in the documentation can be hazardous to your database. To learn more about GTRR, click the following link:

                    The Complete Go To Related Record

                    • 7. Re: Records mysteriously being deleted (NOT the usual suspects)
                      tritechsol
                         Thanks for the link; I'll check it out.
                      • 8. Re: Records mysteriously being deleted (NOT the usual suspects)
                        LaRetta_1
                          

                        ygarcia wrote:

                        Well, thankfully this was not a security breach. Nor was it a damaged file. It was a broken script that was going to related records via a specific layout in another file  that would then delete a record. The layout it was going to was inadvertently deleted so instead of deleting the record from the corrent table, the go to related records step was landing on a layout for a different table.


                        This is the reason that error trapping is so vital ... and to trap on EVERY script step and not just a few.  Whether there were no records when evoking a GTRR (or more likely no related record matching the FIRST record of a set) or whether the layout was accidently deleted (or corrupt), it could have been caught (and the script stopped) if Error Capture was on before the GTRR or layout switch.  Many of these 'strange' mass deletions come from just such issues not properly trapped in the script so the script continues what it planned to do ON THE WRONG layout (which usually means the wrong table occurrence).  The results are disasterous.

                         

                        We had an in-depth discussion of the consequences here

                         

                        I provided a script on that post which showed how to protect (generally speaking) from unforseen issues like deleted layouts.  I recall that, three days later, someone posted a comprehensive Error Trapping process expanding upon my concept and including a great process to log all errors.  

                         

                        UPDATE:  Yes, it was Matt P. here


                        • 9. Re: Records mysteriously being deleted (NOT the usual suspects)
                          LaRetta_1
                            

                          By the way, I am guilty that, when providing a find script for others, I use error trap right before Perform Find and then test If [ not Get ( FoundCount ) ] or If [ not Get ( Last Error ) ] and neither really protect.  I think we Developers provide those types of scripts because they are easier and because, as I indicated on the referenced post, each script should be treated as a 'special needs child.'  But we are passing on improper error trapping techniques because of our laziness when we type. 

                           

                          I am pleased that Matt made it public where a lot more developers might see it and be aware of the risks and solutions.

                          • 10. Re: Records mysteriously being deleted (NOT the usual suspects)
                            RickWhitelaw
                              

                            "UPDATE:  Yes, it was Matt P. here"

                             

                            Unfortunately the file is only available to subscribers. It seems right on 'tho after watching the video.

                             

                            RW 

                            • 11. Re: Records mysteriously being deleted (NOT the usual suspects)
                              tritechsol
                                

                              Thanks, LaRetta, for the resource. Yes, I error trap but obviously nowhere near enough! I'm guilty of the same trapping you mention in your response. I'll get to work on that.