Is FileMaker accidentally deleting my records?

Why a common fear of FileMaker users almost never happens

We will sometimes get calls from clients who are concerned that their copy of Claris’ FileMaker is accidentally deleting records. Or sometimes we get the companion, related worry that FileMaker is not saving records they have entered. We will explain why these worries are unfounded.

First, is FileMaker accidentally deleting my records?

Answer: probably not.

Records get deleted one of three ways:

1. Manual user deletions. 

The user would select Records > Delete Record… and click the Delete button.

Or there may just be a ‘Delete’ button somewhere on the layout. Clicking it, either immediately deletes the record, with no warning. (Not recommended). Or FileMaker will give you a warning first, “Are you sure you want to delete this record?” Then you confirm it, and the record is deleted.

If the developer has not built in a warning confirmation message, then I suppose it would be possible for records to seemingly disappear. But note, please, that this is not FileMaker randomly deleting records, but simply doing what it has been told to do by the user, namely, to delete a record, with possibly no warning.

2. Scripted deletions. 

A script can delete a record automatically and silently.  If a script fails to get to the right layout or record, it will continue on to the Delete Record script step and proceed to delete whatever record is in the current context.

A common mistake of inexperienced developers is to program a “Go To Related Record” (GTRR) script step, followed by a Delete Record, or even more dangerous, a Delete All Records script step. However, they do not anticipate that sometimes there is no related record. When this happens FileMaker is unable to go to the related table, but stays on the originating table. But since the next script command is then Delete Record, or Delete All (found) Records, FileMaker will dutifully do as it has been told, and delete that record or those records.

So in this case again, FileMaker is not randomly deleting records, but is simply doing as it has been told to do, albeit by a poorly designed script which doesn’t anticipate all possibilities. This is the developer’s fault, not FileMaker’s.

3. Cascading deletes. 

If a relationship is set up with the “Delete related records…” checkbox checked, any related child records will be deleted silently and automatically along with the parent record. A common example of this is the desire to delete an invoice, and all of the related invoice line items. And this makes perfect sense. You don’t want orphan records lying around which have no relation to other data in the file. So if an invoice is deleted, then a cascading delete should be allowed to also delete all related line items.

But this command can have devastating consequences if not properly managed. If a series of related deletes are set up on the relationship graph, or if a ‘delete related records’ is set up on a relationship which is using the cartesian join, also known as ‘connect this record to every record on the other side of this relationship’, then when you delete the parent record, it will also delete ALL the records in the related table. Not good. So the user would go into the related table, and say ‘hey where did all my records go??” But again, this is not a problem with FileMaker, but with bad relationship building by an inexperienced developer.

All of these are examples where a user might suspect that FileMaker is accidentally or randomly deleting the records. But in each case, that is not the case.

NEVER CREATED IN THE FIRST PLACE

Now, to flip to the opposite problem, that is the other related case of missing records, is when a user believes that FileMaker is not saving new records which they have created.

To the user, this one will feel like FileMaker is just randomly deleting records. They will say, ‘Hey, I just created five new customer records yesterday, and now today, none of them are there!”

Here is what often happens: a user will inadvertently put FileMaker into Find Mode. They will then proceed to enter the new customer’s name, address, phone, etc. When they are completed, they will hit return or enter, and then FileMaker will try to execute a find and not find that customer, because they had never been entered in the first place. I have seen clients do this repeatedly and never notice the little tiny magnifying glasses on each and every field while they are in Find Mode. Then they complete their data entry work, and later look for their new customers, and they are not there. It feels like FileMaker deleted them, but they actually never successfully entered a single customer.

So, in each of these cases, we see that FileMaker has been performing as expected, or as programmed, but it has lead to some confusion on the part of the user. FileMaker is a very stable program and does not randomly delete or lose data. It is almost always a case of either an inexperienced developer programming it wrong, or an inexperienced user not realizing what is actually happening.

Sometimes its on purpose!

Now, one story of a case of missing records, from the Minnesota prison system. Our company, HighPower Data Solutions, was hired to consult with the Minnesota prison system. One of their minimum security prisons used FileMaker to track their production and manufacturing. In this place, they trained the residents how to create clothing, and furniture, and other useful items in order to give them skills for when they got out. They were also allowed to use their local copy of FileMaker to track the production process.

They hired me to consult with them because among other things, they were constantly losing large batches of records. Because their system is completely isolated from the internet with no external access whatsoever, I had to go into the prison and work with both the guards and residents to take a look at their FileMaker system.

They had a very large and well-developed system. It was very comprehensive and covered all aspects of building, tracking, and reporting on their entire manufacturing process. It would have put many outside businesses to shame with the level of sophistication of this file. And it was mostly built by the prisoners, one in particular who had IT and software training, but had made some mistakes and ended up in prison. So he had a lot of time and built quite a large system for them.

However, they still had the problem of missing records, and could not figure out why. After examining scripts and relationships for a while, I realized that their copy of FileMaker was still using the default menu that comes with FileMaker. (FileMaker has the ability to make custom menu’s where you can pick and choose which menu options will be available to users.). One of the menu options that is by default turned on in FileMaker is the “Delete All Records” option. Why this is on by default I do not know. It is like the machine in the movies with a self-destruct button! Technically, the command is actually Delete All (Found) Records; that is, it will only delete the records in the Found Set. But the menu says “Delete All Records”.

Well, if you were a bored prisoner who worked with software that said ‘delete all records’, is it just possible that someone might feel like having a little fun by hitting that button? We never proved that was the actual case, but I rewrote their menu’s, and removed that option from the system, and they never had a problem again with large chunks of data disappearing on them!

So all this to say that FileMaker never randomly deletes records, but there is more than one reason out there why it may appear that records have been deleted; sometimes accidental, and sometimes, definitely on purpose!

Best to you on your FileMaker adventures! Feel free to contact us to guide you on your journey.