7 Replies Latest reply on Oct 2, 2014 12:27 PM by philmodjunk

    Missing Records in Database



      Missing Records in Database


      When attempting to locate specific files (in the form of subject information) using the "find" icon, some claim to not exist, producing a non-existence notice.  However, after reviewing our files, it is clear that we have indeed entered the information at some point in the past.  Is there a reason for this, and if so how does one go about rectifying the issue?

      Thank you,


        • 1. Re: Missing Records in Database

          There are numerous possible issues from what users have done, to how your database is designed to possible damage that may have occurred to the file itself and perhaps something wasn't quite right with your search criteria.

          Here's an incomplete list of why data might seem to "go missing":

          1) The data has been modified to the point that it either no longer resembles the original version or is not being "found" by your search criteria.

          2) A user deleted the record

          3) A script deleted the record.

          4) due to a "Delete" relationship option being selected, either 2) or 3) above deleted a record in a related table and a "cascading delete" deleted this record as well.

          5) There are many ways that a particular interface design might "hide" records with specific values from view. Thus the record(s) may still be there, but not be accessible from the current layout or when the file is open with a specific privilege set.

          6) A damaged field index can keep FileMaker from finding or sorting records correctly. You can use Advanced Recover options to rebuild all indexes in your file. (use the copy file blocks "as is" and Rebuild index options).

          • 2. Re: Missing Records in Database

            Thank you for your timely response!

            From my understanding, our ad hoc solution thus far has been to use the "New Record" option, type in the ID number of the missing record, hit enter, and the record appears.  It doesn't appear to duplicate the record, but doesn't guarantee that it will be there the next time around.

            We believe that the 6th answer on your list is the most plausible, but we're not familiar with  how to rebuild all of the indices in the file.  Could you provide a short step-by-step as to how to do so?

            Thank you!


            • 3. Re: Missing Records in Database

              Your answer suggests otherwise.

              New record adds a new record to your database and increments any auto entered serial number setting--which might then produce a "gap" in the series of serial number values and this might create the illusion of a missing record if you then revert to keep from creating a new record.

              So this would not be a good way to look for a record. Entering find mode, specifying the ID and then performing the find would be far safer.

              And 6) is the least likely reason listed. It can be nearly impossible to rule out the possibility that the record was deleted.

              To recover the file, close it and take it down off the server if hosted with FileMaker server.
              Launch FileMaker Pro or FIleMaker Advanced without opening this file. Select Recover from the File menu. Select Advanced Recover options and select the ones I described in a previous post.

              • 4. Re: Missing Records in Database

                I see, that makes sense as to why the "find" function would be the best first option.  

                In your last explanation, on how to recover the file by taking it down off the server, could you elaborate a bit?  If we can't find the file, how can we close it and take it down?

                Thank you again!

                • 5. Re: Missing Records in Database

                  Why can't you find the file? I am referring to the FileMaker database file and finding that file should be a very straightforward obvious task.

                  For files hosted via FileMaker server, you can use the Server Admin console to close the file and temporarily remove it from the server or you'd use the server Admin to make a back up copy of the file that you can copy across the network to your computer to recover. You should, in fact, already have at least daily back ups on hand so that you can take last night's back up copy and recover it to see if this makes a difference.

                  (And if you have been saving a series of sequential nightly back up files as you should, you can also open and search some of the older back up copies to see if the missing records can be found in the back up copies.)

                  Please note that this particular operation is a long shot. I will not be surprised if it does not result in records becoming "findable" that could not previously be found.

                  • 6. Re: Missing Records in Database

                    After identifying a specific record that appears to be missing from our current filemaker, it appears in a backup from several days ago, but mysteriously disappeared yesterday.  I'd imagine that the case is similar with the rest of the missing files.  Thus, I'm led to believe that it has something to do with one your first two explanations (I'd say third, but I don't see how a script would delete the record on a given day, despite not being adjusted in that time period).  If it is indeed a result of one of those two situations, what would you recommend we do?

                    • 7. Re: Missing Records in Database

                      A script can only deleted a record if someone has created it with the ability to do so.

                      You need to sit down with a consultant who can analyze the design of your database and the way you need to use it to come up with ways to prevent this in the future.

                      One method is to use FileMaker advanced to replace the "delete record" option with a "Delete" option that does not actually delete a record but marks it as "deleted" by setting a value in a field. This then requires additional changes be put in place so that such "marked" records are hidden from the user--creating the illusion that the record was deleted when it was actually hidden. Records deleted by accident can then be restored just by clearing the data from the field that marked it as deleted. Some systems maintain such records indefinitely, others delete them after they have been in "deleted" status for a specified period of time such as 60 days and then a housekeeping script scheduled to run after hours looks for and deletes such "expired" records.

                      It's also possible to set up access privileges for the users such that only certain people can delete record and/or only records with certain values can be deleted. We use that method here to keep receipt records that have entered "printed" status from being deleted by low access level users.