FILEMAKER: Dangers Of FileMaker Deletes

From Dwayne Wright - Certified FileMaker 9 Developer
WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright

There are considerable dangers involved with allowing users of FileMaker files the ability to delete records. For starters, there isn’t any "undo" feature for deleted records built into FileMaker. Once a record is gone, it's time to hope there is a recent backup handy.

Another danger involves the way you can setup your relationships between FileMaker files. In the relationships setup dialog box, you can have related records automatically deleted, when a master record is deleted. Sounds good in theory, kind of like cleaning up after yourself. However, what if those related records were related to others and the delete relationship option as on? To make a long story shorter, this little option can be responsible for deleting thousands of records, all with the accidental deleting of one primary record.

If you are not a student of database relationships, I’ll try to make this as painless as possible. You can have multiple FileMaker tab;es linked together. This allows you to organize your data sources into individual tables, such as Clients, Invoices, Inventory and such. With relationships, you can have them linked together so the invoice file can see or capture information it needs like Client information or Inventory levels. With relational cascading deletes, you could delete a client record, that deletes associated invoices, that deletes associated products, back to associated invoices for those products and even back to associate clients to the associated invoices. So you can see that the cascading relational deletes is not something to take lightly.

YOUR OPTIONS NUMBER 1
Add secured access to your database solution. Set the access of each traditional user account to not have the ability to delete... except the accounts that really need delete access of course. DRAWBACK: Well, no one can delete a record. Your database will quickly suffer from blank and incomplete records. You can put in a field that indicates the user wants to delete a record. Then have a cleanup script run at the end of the day. This is not an elegant solution, but for some short lived databases, it may be a viable option.

YOUR OPTIONS NUMBER 2
Add security access to your database solution. Set the access of each account to have editing capability only in the menu commands. DRAWBACK: Many of the normal menu commands are missing such as FIND, FIND ALL and DELETE. You will need to create buttons on all the layouts that restores the things that used to be in the menu. This also does nothing about the UNDO delete issue.

YOUR OPTIONS NUMBER 3
There are plug-in based utilities out there that will automatically copy deleted records to an archive table when they are deleted. Some of these tools even have restore capabilities that you might be able to implement into your solution. DRAWBACK: You have to distribute the plug-in to all users. You have to purchase the plug-in. You have to do some programming and testing to use the plug-in.

YOUR OPTIONS NUMBER 4
Use custom menus for record deletion because this will allow you to write scripts that handle your FileMaker delete operations in a manner that works for you. This can be combined (to a degree) with any of the above options as well.

YOUR OPTIONS NUMBER 5
Backup all information to an archive file before deleting via scripting. This is done by using a combination plate of the above options. For sets of very key data, you can build an archive file that passes the information to it first and then deletes the master. DRAWBACK: It will take time to pass all the data to another file. Of course, if you are having too many deletes by your users, it’s an indication of other possible problems. You will need to build an archive file for each file you want to do this for. You have to store a lot of extra files / records but you do have excellent recover capability for unintended deletes.

YOUR OPTIONS NUMBER 6
This is a variation of option number 5, instead of writing the information to a backup file for each file, write them all to one archive file and save the information as a text array. This is something that advanced users are familiar with. The idea is that you take a bunch of information from a bunch of records and save it as one calculated text string. Each piece of information is separated by a flag or marker. If this sounds a little like HTML, you are right. Web pages and the elements in them do resemble text arrays. This allows you to save more information in a smaller space. It also offers a smaller performance hit to users. DRAWBACKS: You will need to build an engine that can breakup the text array. You will use this engine when you have to restore deleted data. This option does take some time to setup and modification time can hurt if your database isn’t quite mature.

SO WRAPPING THIS RASCAL UP
As you can see, there are many options when it comes to protecting your FileMaker database against accidental record deletion. None of them are perfect and none of them will make everyone happy. However, one of them ( or a customized combination plate of them) will likely take care of your immediate business needs. Personally, I tend to start at the bottom and work my way up to Option 6.

Unless of course, you know that your database solution will be working in a very dynamic office environment. If the company is going to have a large number of temporary or contract employees doing data entry ... I’d recommend leaping directly into options 5 or 6.

=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2008 - Dwayne Wright - dwaynewright.com

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.