When we envision data management solutions, we tend to think of perfect-use scenarios, where no one ever creates a duplicate record, no one accidentally hits the New Record button, no project is ever canceled, and certainly no errant script accidentally miscopies data. All of our users are savvy, and all of their actions are intentional.
We've seen a lot of cases where developers put most thought into how records are added and retained, how data is modeled, and how data will link in a dynamic way -- but not much thought is put into the removal of data from the system, and the consequences that might follow. Unfortunately, some of the biggest database administrative headaches we’ve seen are a result of careless DELETE actions, and the removal of important data from a system.
In order to prevent such mistakes in the first place, it’s important to understand what it means to delete a record and to establish good business-rules as a foundation to your system.
So, what does it mean to delete a record in a robust database system? Let’s use this simple FileMaker Invoice table with line-item records as an example.
And an equally simple entry screen:
As you can see, there’s nothing exceptional about this interface and it should be fairly familiar.
When you were starting out and you were the only person doing the invoicing, it was easy to keep track of everything. But as your company has grown, you have found that the simple interface is no longer cutting it. As new users access your system, data begins to go missing and records become unorganized.
First and foremost, let's identify the problem: the Delete Record button.
There are going to be end-users out there who mentally align the “Delete Record” button with the “Undo” action. With this button automatically in the toolbar (and no “undo” button automatically in the same toolbar), their first impulse is going to be to hit that button to fix a mistake or to leave a screen they accessed accidentally. Despite the warning that the action cannot be undone, they end up deleting a record anyway.
By default, FileMaker is not going to know who, when, or why a record was deleted. It is simply going to be struck from existence. The record could be painstakingly re-entered or restored from a backup, but that Delete Record button is the first thing to scrutinize when looking at any layout. Naturally, the “Delete Found Records” menu option should also be heavily limited.
Of course, the best (and easiest) way to tackle Delete Record issue is through the Security layer. By removing Delete access to a table, you can nip that problem in the bud, rendering the button inactive. Or, you could build out your system by adding a field that traps the created Account Name and the created Timestamp, you could even say that someone is allowed to delete a record that they themselves created within the last half hour. This prevents the issue of someone accidentally creating a record but then having no way of cleaning it out of the system other than going to a manager or administrator.
As a rule of thumb, you should set the bar fairly high for deleting a record in a primary table like invoicing, so that it’s only permissible when the record was created (or duplicated) by mistake. Any other record should be maintained in the system.
But what if you don’t want to see it?
Most invoices aren’t really that critical to keep on your radar after they’ve been paid and closed out. But that doesn’t mean we should delete invoices once they’ve been paid. For this reason, we often put a “Status” field on our top-level table records.
By indicating the status of a record, we can begin to use canned finds to only show records that are active for us. Furthermore, “what is active” can change from department to department.
For the purpose of this example, let's say your sales personnel are only interested in new invoices, accounting is interested in invoices that have been submitted, the office manager might need to access paid invoices, and of course, the owner will want to see everything.
Changing an invoice status to “canceled” allows us to kick out an invoice from default views without actually removing the record from the system. An accounts manager might keep tabs on canceled invoices for follow-up purposes. By removing the “Delete” option and instead flagging the record as canceled, we move the administrative headache of restoring accidentally removed records from the database by simply making it a matter of changing the state of the record.
By utilizing default finds and/or dashboarding, we can give each user of the system a peek at their subset of records. Unlike using FileMaker’s record-level locking at the security level, default finds and well-constructed, business-rule driven dashboards allow users to continue to have access to data even if it’s not on their immediate to-do list.
As we mentioned previously, a salesperson might generally only need to see the New (open) invoices that they’re currently working on by default. However, they might want to see the customer’s history while creating the invoice.
So when they log in, they might see this list of New Invoices:
By using FileMaker’s find features, they could call up a list of all Amalgamated Widgets invoices, and see the client history, grouped by invoice status. This way, they can see what’s outstanding, review past orders, or have a better idea of what the client’s payment history looks like.
When you begin to combine default found sets with FileMaker’s record-level locking, you can create a carefully-curated environment based on the end user’s role. A common request we receive is to restrict members of a sales team from accessing each other's sale records and invoices. By locking down those records at the security level, you can then use the default finds and dashboarding to help curate lists without having to choose between scrolling through data you don’t want and deleting old records.
Another major concern with data deletion is what happens to “child” records when a “parent” record is deleted. In our invoices example, the invoice would be the parent record, and the line items would be the child record, as the line items exist as related to the invoice. Typically, when you delete a parent record, you also delete all of its children records. This is called a “cascading delete” and prevents the child records from being “orphaned” by remaining in the system when the parent record is no longer there to hang off of.
This is another reason to severely restrict deleting records. In a situation where the delete was accidental, we either have to locate and re-assign the orphan records, or re-create the orphan records when they were eliminated as part of a cascade delete.
Child records themselves, however, can be a slightly different business-rules beast than the parent records. After all, when constructing an invoice, items may be kicked out for any reason, and certainly shouldn’t take an administrative action to remove.
We’re all familiar with the trash-can icon at the end of a portal row:
Imagine the user experience nightmare if every line-item on an invoice needed a special permission set to remove!
By utilizing the status of the invoice, we assume that the average user should not be able to delete a line item after the invoice has been submitted, while still allowing upper management to strike items from a submitted invoice in order to correct a mistake or appease an irate client. But again, it’s worth considering when that grace period stops. Imagine the accounting nightmare to your totals if a manager could delete a line item from a closed invoice months after the books have been closed.
One simple trick to the “never delete anything, ever” conundrum with a child-record user interface would be to simply replace the “Delete portal row” button with a button that would, instead, break the relationship between the parent and child record while still allowing the link to be restored, creating a sort of “partial orphan” out of that child record.
Based on the invoice graph above, we know that the Parent table Invoice relates to Line Items on a simple InvoiceID join. Assuming that the InvoiceID is a basic integer serial number, the “delete portal row” button could be re-pointed to, say, a non-integer value. This will break the link between the parent and child, while still making it easy to retrieve.
All you have to do is create a field on the parent table to act as the archive value. On the Invoice table, I created a simple calculated field called LineItemArchive. It’s a calculation with the following value: InvoiceID + .1. Creating a secondary relationship called invoicelineitem_removed, I join this new field to the InvoiceID field on the line item table.
Then, archiving the record is as easy as changing the value of the related line item’s InvoiceID field from the InvoiceID of the parent table to the LineItemArchive value, and you can restore just as easily.
This is a low-overhead and easy way to delete-proof your system, but also value-add to have the ability to treat the delete button more like a recycle bin.
As mentioned previously, setting delete privileges on the security layer is the best way to prevent users from accidentally deleting a record, and using status fields and default finds can help the user experience within your database.
Of course, none of these solutions adjust for another headache – when a user accidentally overwrites a record, or knowing who and when and what was deleted.
In an upcoming article, we’ll walk through specific solutions to these problems including robust changelogging and system logging, all of which build on the idea of mitigating data loss when a record is accidentally deleted or overwritten. In the meantime, hopefully you are thinking about the larger issues surrounding the delete button, and ways that you can build better, more robust systems by considering those issues.
If deleting and overwriting records is causing you administrative headaches, our team of FileMaker Consultants are happy to review your system and provide professional recommendations to solve your immediate challenges, improve user experience and the overall return on your software investment. For more information about how we can help your business, visit our FileMaker Services Page and contact us today.