Cascading Delete Self-join or Same-Table relationships are very useful (two table occurrences from the source table related to each other), but be careful! Don't ever turn on the option to allow deletion of related records! If you do, whenever you delete a parent record you may delete more than you want. What happens is that since your related records are also your parent records, deleting records can cascade out of control. I have seen the deletion of a single record cause the deletion of all records in all tables.
Level: Beginner Version: FileMaker 15 Category: General Tuesday, May 16, 2017
I was asked to write an article about my experiences, troubles, pitfalls and recommendations to those starting out with FileMaker. I find many times, and this applies to any industry, when experts help beginners, the experts forget what it was like to be a beginner. While the expert is generous with his or her advice, although perfectly accurate, it sails over the beginners head. The beginner just doesn’t have the experience and tools to digest and disseminate the advice offered.
On many of the FileMaker help forums, I see many people who get a copy of FileMaker, jump right in to making their first database and quickly run into trouble and frustration. I know their pain, because I made some of the same errors. Hopefully some of the tips, based on my experience will help the beginners avoid my errors.
My Background I started with FileMaker about 5 years ago, purely by accident, jumping in with FileMaker 12. I own a small heating oil/petroleum delivery business that mostly involves the delivery and tracking of automatic deliveries of petroleum products and service to heating and air conditioning equipment. I work by myself and do all the deliveries, service, bookkeeping, accounting, customer interactions - well everything. Before FileMaker, I tried to track this information with a couple of flat file databases (Microsoft Works-yeah Works), combined with multiple spreadsheets and Quickbooks. I dabbled with Access and originally found it very daunting to try to develop with that software.
I knew I needed a better system and had 2 choices. Build my own, or use industry specific software. The industry specific software available was very expensive to me, contained many features I would never use, and did not have features I wanted. Talking with vendors at trade shows, pricing them out to modify their solutions would cost me well over $30,000 dollars. Because I enjoyed the process of creating my own, and am a bit of a control freak, I decided to purchase a copy of Access and start my building my own.
I went online, typed in ‘Microsoft Access’. The first result was their website, and the second result was “Why is FileMaker better than Microsoft Access”. I don’t know the marketing genius who purposely or accidentally made that happen, but I clicked the link, read the article, and decided that FileMaker would be perfect for me.
Rather than describe every mistake I made, I would rather tell you what I feel would be a better way if I were starting over. Based on your computer experience, some of these steps will go easier than others.
Disclaimer As a disclaimer I just want to note that I have no financial interest to any websites, forums, people or products I mention. As of this writing, I have personally never met any of the people, only corresponding via email with a few.
Please don’t open up FileMaker and try to make a database. It’s a foreign language to you. You’ll quickly discover you just don’t know what to do and/or how to do it. Or even worse, you’ll be somewhat successful, only to discover you’ve gone completely down the wrong path, and need to do a complete rebuild or two, wasted days/months even years of time, and/or have lost all your data.
So let’s jump right in! The very first thing I would do is obviously purchase and download FileMaker Pro Advanced (latest version and latest updates). I strongly recommend Advanced if you value your time, quicker development, and want to lessen your level of frustration. The Script Debugger and Data Viewer alone are essential elements to understanding what is happening with your scripts, and invaluable when picking through samples of other people’s databases.
The very first resource to purchase (I like the book version) is "FileMaker Pro 14: The Missing Manual" (FileMaker 14 was the lastest version at the publishing of this article). I feel it’s imperative to completely understand how to use FileMaker as a user first. This lengthy book will take you through using FileMaker, step by step. From there you will learn the essentials of building and modifying a database. This resource is presented in a way for the beginner to learn the terminology, methods, and basic to intermediate use and development of FileMaker. You just have to put in the time and learn.
Now that you have your basic understanding, the second resource I found to be very essential is this white paper: White Paper for FMP Novices by David Kachel. It’s an excellent resource. Even though it was published in 2011, I find it is still relevant. I will note it’s kind of dry and technical, but getting through it and understanding it will help guide you down the road. I actually just reread it and found a number of things I forgot.
Okay, if you made it through the first two, you’re on the right track, but still not ready to build. The temptation is hard to resist. Just one more recommendation. My final and best recommendation is to move onto some serious training. It’s important to sample the various sources available and find the proper program. What’s proper? With any teacher, their teaching style, their knowledge of the product, and their availability to answer questions. When I found this resource, my knowledge, ability and confidence soared. It has saved me tons of time & frustration. My favorite learning videos come from FileMaker expert John Mark Osborne. I purchased the 3 part training series for FileMaker 12 (40 plus hours). As you work through, you will build a contacts and invoicing solution with all the bells and whistles-with techniques from beginner to advanced. It really is a virtuoso performance from a FileMaker expert, who explains things simply and completely. I watched it completely through and built the sample database. There is so much information on there, that I watched it a second time, and built myself a reference database (fairly plain) as in index. So when I get into a jam I can look up formulas, techniques, methods. I have the videos on my iPad, and still watch/refer to them frequently. There are a lot of ‘aha’ moments.
You're Getting Close Now…you’re ready to build. Take the data modeling advice from the three sources,mentioned above, to map out your database. This is a very important step that shouldn't be skipped. Good planning leads to a successful database. Don't be tempted to just start programming! Create a requirements document and an ERD at the very least.
Building Tips When you are ready to build, here are some to help you construct your ideal solution. Many of these are found in the white paper and the other two learning sources mentioned.
Back up frequently Your definition of frequently is simply how much do you want to re-do if you have a crash, lose data, or delete something you can’t recover. While building layouts, there will be lots of tweaks, so more frequently. As you use the database, it will be less. Here’s a script I found that was posted by world famous FileMaker expert and uber contributor to FileMaker help forums Phil Caulkins aka PhilModJunk. Here’s the thread from the FileMaker Community forum. Please read both pages as there were some modifications.
Clean as you go After you start developing you change your scripts, layouts, fields and calculations. Many times I would keep the old layout, script, or field ‘just in case’. This leaves a lot of clutter. Mark them for deletion and either move them to a folder (out of the way), or leave them in an older back up copy if needed to be retrieved. One thing I did was to create a clone of the file. Any scripts I made in the working file I didn’t use any more and wanted to keep (because I thought they were works of art or I wouldn’t remember how I did them), I would put them in the cloned file. Now all field and layout references stayed in the scripts for easy deciphering.
Fully document the field names and scripts This is important for any field that isn’t immediately obvious. I embarrassingly had a field in my database for 2 years called ‘Field so layout works’. I have no idea how/what/why it’s there. After checking on the Database Design Report (FM Advanced), and some testing, I deleted it. For scripts you should really document what the script is for, from where it’s called (button, trigger, or another script), and dependencies like script parameters. Also document any script steps that aren’t immediately obvious. Documenting is even more important for the beginner. Because you are not a full time developer it is easy to forget the things you don’t do every day. And in my case, development would pick back up after a few months and I would waste too much time re-orienting myself with the particulars of a script or field.
Use the Script Debugger and Data Viewer Always use the Script Debugger on Loop scripts steps until fully tested, and especially on modifications. I’ve had to force quit too many times. A few minutes to run the script just isn’t worth possible data corruption. The Data View is really a great place to test calculations. When the calculation is working correctly, copy and paste to the proper place. Going in and out of Manage Database and drilling down to change it a dozen times just takes too long.
Learn about and use Custom Themes There is plenty of information on the use and value of custom themes. Here’s a YouTube tutorial, but searching will provide you with many resources. This has more to do with design and John Mark Osborne’s videos (mentioned earlier) cover good design in fine detail.
FileMaker Go design When designing your FileMaker Go layouts connect your database to your device so you can see real time changes to the layouts. I initially wasted some time copying the database to the device, open the database on the device and discover something is off a few pixels, or just doesn’t look right. Connect the device to the FileMaker Network (peer to peer sharing) and speed up your development. You can connect easily from the File menu. File>Sharing>FileMaker Network, and follow the prompts
Keep it Simple It’s been discussed on this site and many others that if you have to keep coming up with more convoluted ways to solve a problem, and that creates another problem which requires some magic to solve, you probably need to step back and look at the problem another way. It may be time to get some help (see next tip).
Get help from the forums I’ve used the forums for help many times. They are a great resource with many super knowledgeable people who offer help, advice and even make sample files or modify yours. It’s important to remember everyone there likes to help, but volunteer their time (freely), so asking specific questions (and thanking them!) is important. Questions like “I need a database, how should I start” don’t usually go over well. I feel it’s best to try to solve the problem yourself through trial and error, and researching sources online. Then ask if there is an easier way (there almost always is). Also posting a sample file gets quicker results. There is nothing like the feeling of working on a problem for hours (or days) with complicated workarounds, scripts and relationships, etc. only to ask if there is a better way and within minutes someone responds with something short, simple and sweet.
I frequent the forums and read almost every post. You never know what you may learn. My favorite forums are:
Starter Solutions Regarding Starter Solutions - I wouldn’t. FileMaker provides Starter Solutions when you purchase their product. I think they are fine to look through for ideas, but my recommendation is to not use them. I feel they show you what FileMaker can do, but not what you should do or would do. Even worse I’ve seen on the forums where someone will try to take 2 or 3 Starter Solutions and connect them all together. If it actually worked, you will end up with a very complicated database, with concepts you don’t understand, and things you just don’t want or need. Build it from scratch via one of the above-mentioned tutorials and you’ll thank yourself down the road as you customize your solution. Plus doing is the best way to learn rather than copying.
Read/Grab/Learn There are also many blogs and websites to find helpful information. On there you will find many concepts and files. Download everything you see. Pick through them to learn the techniques. Not everything will be applicable, some you’ll never use, but you can learn from them and pick up tips on naming conventions, layout design, script writing, etc. There are far too many to list and maybe I will compile a list of my favorites if I am asked to do another post.
Know when to bring in help If your database will be used by multiple people, or deployed to FileMaker Server or any other web services, I would recommend hiring a developer. Multi user solutions require some very important considerations. Specifically security, performance, record locking, and syncing data. You’ve gone far enough on your own, don’t let everything, especially the data get destroyed. Bring in the pros.
Conclusion I really enjoy being a FileMaker enthusiast. It really is a fun hobby for me and is much better for my sanity than my former hobby (tournament chess). I’ve built three databases (the database for my business, an Index database for FileMaker reference, and an Event Planner). The solution I built for my business works great and allows me to be very efficient. I plan a complete rebuild of all three (I’ve been trying to get time for a year now) using FileMaker 15 to take advantage of custom themes, slide panels, and popovers-just to name a few. Hopefully I’ll follow my own advice.
This blog is completely free. Please support it by clicking on one of the advertisers at the left side of the window or becoming a patron. Thanks so much!
Great testimonial, thank you for sharing it Steve! There are some great referenced resources I need to explore. JMO has transformed my FileMaker skills, especially in teaching me how to plan, and given me a lot of 'aha' moments as well (but I still have a lot to learn and re-learn)!
Thanks for the kudos Sarah but also give yourself credit for your dogged pursuit of FileMaker knowledge. And, you will always something to learn in FileMaker. Once you think you know it all, you're in big trouble. Always be humble in your knowledge.
Steve says he's just a beginner but I think he's being humble. I think the truth is that he remembers how it is to be a beginner. This makes him an ideal expert to guide a beginner down the right path.