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.
FileMaker, Inc. FileMaker, Inc. or FMI is a wholly owned subsidiary of Apple, Inc. It was once named Claris when it also sold MacWrite, MacPaint, Claris Emailer, ClarisWorks, etc. but changed it's name in 1998 when it focused on FileMaker. FileMaker started out as a DOS application called Nutshell but was shaped into the powerhouse it is today by Claris and FMI.
Level: N/A Version: FileMaker 15 Category: General Thursday, April 6, 2017
In the last few years, I've subscribed to the KISS methodology (Keep It Simple Stupid). Call it wisdom or humility, I'm not sure which. All I know is, after over two decades in the FileMaker market, I've discovered the simplest solution is most often best for my clients. It costs less and performs better, in most cases. I know all the tricks, having practically wrote the book on the subject, but there is a time and a place for complex methods. What I'm here to convince you is, choose complicated techniques carefully.
Why Simplicity? I used to be known as the "Tips & Tricks" guy. Just take a look at the Scriptology Book Companion CD if you don't believe me. That's all my work and some of it makes me giggle thinking about the workarounds we all used to employ. But, those days are long gone. FileMaker has grown up and filled in many of the holes we used to plug with sleight of hand. Even with a mature FileMaker programming environment, developers still try to push FileMaker beyond it's original design. Some is good and some is bad.
I'm all about combining components in FileMaker to come up with a solution. It's the strength of FileMaker that features are not just single function items from a pull down menu. FileMaker is meant to be programmed. But, sometimes developers take it too far and need to pull back on the reins a bit. What changed my mind about bending FileMaker too far was working on legacy systems where the developer employed every blog trick under the sun. While these techniques look cool in single-user mode, they often slow down over a network and can be downright show stopping over a remote connection. They can even break or become obsolete down the line when new versions of FileMaker are released.
This reminds me about a common technique called a "constant relationship" utilized in FileMaker 6 and earlier. By creating a calculation equal to "1" in both files/tables, a portal could be created showing all related records. When FileMaker 7 was released, it became obsolete (just use the Cartesian Product operator "X") and even caused severe slow downs in some scenarios. I must admit I was guilty of using this technique. Consider it part of my FileMaker education. There weren't any viable alternatives available so I can't blame anyone for employing this technique. Maybe this is the fault of FileMaker, Inc. or the developers who created the workaround but the point remains the same, specialized techniques can become obsolete and even break a solution. Keep an eye out for them in legacy solutions, especially if there is more than one file.
However, this is completely different than poor programming choices. I remember working on a solution that took a full two minutes to open on a remote connection in the same version of FileMaker it was developed. It worked great in single-user mode when the developer demonstrated it for the client but when the solution was deployed and records were added, it became slower and slower. The developer couldn't or wouldn't fix it, I don't know which, but the client came to me for my help. I looked over the system and showed them why it was so slow but told them it wasn't fixable for a reasonable price. It had to be rewritten from scratch!
What the developer had done was create a completely dynamic system so any interface element could be updated in a preferences section. On top of that, he had created a non-standard interface for selecting multiple levels of related records that blew my mind. It was criminal what the developer had done. Sure, it looked pretty but it performed terribly. He had to set hundreds of global fields and variables on startup to the results of calculations, often based on screwy relationships. The worst part was, it was one of the big FileMaker firms who had contracted an amateur FileMaker developer to do the work... obviously unsupervised. Turns out this developer was a farmer by day and developer by night. I kid you not. This is a true story I will never forget.
What to Say? The most common response I give to clients with over-tricked solutions is it's going to be less expensive to start from scratch. I lose a lot of clients that way but it's the truth. I could string them along trying speed up or replace slow features but it often comes down to time. It's often faster to start over than to try and do surgery on an ill conceived solution. It's your duty as a developer to tell the client the truth, not just do what your client asks.
That's really why the FBA (FileMaker Business Alliance) and certification test were conceived. I can't tell you how many times I've seen databases that looked like they were designed by a kindergartner. And, clients actually paid for their services! In an effort to make FileMaker a more professional development platform, checks and balances were created with a developer program that requires examples of past work and certification tests to confirm you know your stuff. Do they ask about Virtual Lists on the certification test? No, because they are rarely used except in specialized circumstances (I, personally, have never deployed a virtual list solution despite liking the technique). Unfortunately, amateur developers read about techniques like Virtual Lists in a blog and think it is the best way to program a FileMaker database. Sad but true.
This reminds me of another story where I was helping a longtime FileMaker client connect a web form to FileMaker. Why he didn't use FileMaker as the back end for the form is simply because he was dazzled by these so-called "web" guys. They built the form (which looked great, btw) and then my client asked me how do we get the information into his existing FileMaker database? I said the standard method is through name value pairs. So, we had a conference call with the "web" guys and I mentioned name value pairs, for which I received a bewildered thousand yard stare. Mind you, I'm not a web wizard. But, all these guys knew how to work with was their form creation software. They knew nothing about the foundation of the web. My point here is, you need to know the basics before you can really be good at something. The same is true for FileMaker. Be proficient with the baseline of FileMaker functionality before you start employing high end techniques like ExecuteSQL or, as mentioned above, Virtual Lists. Walk before you run. Otherwise, you will never fully understand my beloved FileMaker.
What is FileMaker? Let's get back to the basics. I'm not telling you to stop deploying "cool" techniques. I'm just saying we all may have gone too far to some degree and need to remember what FileMaker does best. FileMaker is structured around find, list and form. A user performs a find, is presented with a hit list of matches and then clicks to display details. It's basically the same thing as a web search and Google doesn't need any fancy interfaces to make this tried and true design work for millions of users. Still, this is one of the most common abuses of FileMaker functionality. What's wrong with find, list and form? It's a simple concept that serves database users well. It doesn't require any special programming and performs speedily. Yeah, an iTunes like interface might look really sexy to your client but is it worth the potential downfalls?
I remember one client I was assisting in their development process with my meta-consulting service. He had to make everything work according to his vision. He had a base layout with one record and global relationships to everything. While I've done this from time-to-time to provide dashboard like functionality, his entire solution was built upon this premise. I kept telling him it was a bad idea and he kept ignoring my warnings. Don't get me wrong, his solution looked awesome but the result was a system that wasn't adaptive. Everything worked on relationships so anytime he wanted additional functionality, he had to modify or add a relationship. If he had listened to me, a simple find would be all he needed to adapt his solution. Don't underestimate the power of a simple find. It is woven into the fabric of FileMaker. Practically everything in FileMaker is molded with find. Finding a subset of records is really what differentiates FileMaker from a spreadsheet, more than any other feature.
Another of the most common programming mishaps is creating non-standard reports. Reporting is accomplished using sub-summary parts and summary fields, not relationships and aggregate functions. Why developers try to recreate something that already works so well and is so flexible, is beyond me. Yes, sometimes clients want cross-tab reports or other non-linear summarization of their data. But, have you explained to your client that stretching FileMaker in this manner could slow down your solution as records are added? It can also make it non-adaptive to future changes so make sure your client knows. Your client might be willing to consider a different approach. It's important to consider the advantages as well as the disadvantages of an approach to a solution.
I could go and on with examples. Maybe some of you reading this article can share your experiences in the comments below.
Convincing the Client I've worked with many clients over two decades and sometimes I just can't convince the client to go simple. They see an incredible interface on the web or an app and want it for their FileMaker solution. Despite my pleas, they go ahead with a non-standard interface which requires lots of time to program and un-database like wrangling of FileMaker features. The end result is what I told them. It performs poorly. But, not only that, these systems are often difficult to update, requiring the same changes multiple times over in different areas. The structure might even be compromised making it difficult to create ad hoc reports. It's a nightmare I've seen so many times, I can see it coming from a mile away. Still, I fight the good fight and many clients do listen to my warnings. While their solution may not be as sexy, it performs well, adapts to changes or additions easily and allows for reporting without splitting data. Do your best to convince your client!
Complexity FileMaker is not Oracle. It's easy to program. That's not to say you can't create complex solutions with FileMaker, just that you don't have to be a computer science major to understand what's going on. Unfortunately, the waters have been muddied a bit. Somehow a bunch of super geniuses started using FileMaker and publishing complex relational designs, hard to read scripting styles and over complicated calculation formulas. I'm not saying some of this stuff isn't incredible. What I'm saying is some of this stuff can be programmed in a more straightforward manner so us regular developers can understand it. Whatever happened to variable names in plain English so I can follow a script easily? $x, $a1 and $st may make the code shorter and make sense to a mad scientist but it prevents us common folk from understanding what you guys have done.
This point is especially poignant when you adopt a FileMaker solution and it takes you hours or days just to comprehend the original developer's approach. And, it's not just about meaningless naming conventions. This reminds me of yet another story that best gets my point across. I was on a forum and posted a response to a question. It was a straightforward script, easy to understand and met the skill level of the person asking the question. Another developer answered, stating with arrogance, that he had a shorter script (yes, I could sense his superior attitude in his writing). When a forum friend counted the lines, it was exactly the same length, but that's not really the point, I just wanted to bring it up (devil emojicon needed here). That response brought on another holier-than-thou response that I'll never forget. This developer said "I don't do finds, don't do sorts, don't create windows, don't need extra fields and find a hole if available". All I have to say is that's shortsighted. If you don't use all the features available to you in FileMaker, how can you arrive at the best solution. The same forum friend said something else which makes a great point, "your script is also a lot more complex using ExecuteSQL and identifying variables with single letters".
Complexity doesn't always mean good. FileMaker was created to make it easy for non-programming folks to create solutions. Yes, FileMaker has become a lot more sophisticated since then but that basic premise still exists. FileMaker is the easiest development platform out there with the ability to grow with your needs. That doesn't mean you don't need to pay attention to it's roots. Use what FileMaker provides as a platform and add on complexity where it makes sense. Don't be that guy who ignores or is even unaware of the complete feature set in FileMaker.
Conclusion Don't make FileMaker more complex than it has to be. Performance, adaptiveness and cost reduction should be top priorities. Also remember that you are likely not the only developer to program a solution! What if someone adopts the solution. Don't make their life difficult! Use standard programming techniques and conventions. Thanks from the bottom of my heart.
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!
There's one thing I think should be emphasized: teach the end users to take full advantage of the Filemaker find features. Extending and constraining the found set gives the user tools to break down complex finds.
Thanks for your feedback. I'm going to disagree, to some extent, about teaching the user to take full advantage of all the FileMaker features. In my experience, users just want to know just enough to get their job done. Therefore, I think it's best to determine how users do their job and teach them exactly what features they need to know. Even better is to script complex find procedures for them so it walks them through the process.
Thanks for the good summary article, John Mark. It reminds me of the oft-quoted remark by Albert Einstein: "Make things as simple as possible. But no simpler." Another, earlier (c 1300s!), version might be Occam's Razor - the simplest answer is usually the most likely. In this mood, what are your thoughts about replacing many calculations with auto-enter (text, number, date or time) calc which only update when needed? It appears to reduce the load (or increase the speed!) on opening, scrolling, searching etc. Thanks again! ps I've used your Scriptology suggestions for years...
Love the quotes. Thanks for sharing!
The only time I use auto-enter calculations is when their benefits are clear. For example, one benefit of an auto-enter calculation is you can edit the calculation result. If I don't ever need to edit the result, a calculation field makes more sense. Another example would be the need for a stored calculation that references global or related fields. In your particular case, it sounds like you have lots of unstored calculations so auto-enter seems to make sense so the formulas don't recalculate every time the screen refreshes (e.g. scrolling). However, I can't be sure without more information about your solution. Hope this helps.
Ahhhhh... the good old days when I had an entire year to write a book.
Great article, makes me contemplate on the subject - keeping it simple and as close as possible to FileMaker native.
Your training has transformed my development skills John Mark! I'm so thankful for your blog and YouTube videos and other training resources (Udemy courses) that I can use to refresh what I learned in the online classes.
Thanks for your kind words. It's be pretty hard to overcomplicate a find script. I'm sure it can be done but usually they are under-complicated in that they don't replicate certain aspects of find mode. The example I show in my blog entry titled the "Ultimate Find" has just about every standard feature you might need. Where I would be concerned is overcomplicating the interface for a find to make it look more like an iTunes dwindling value list (I saw a blog article on this recently). Find mode works great as is. No need to give find mode a face lift at the expense of your client. The farthest I would go is the script for filtering list view shown in my "Script Tightrope" blog entry.
Agreed. My corollary is "let FileMaker be FileMaker". Instead of devoting lots of arcane code towards making FileMaker behave like some other program (web browser, iPhone, project planning software, word processor, search engine, etc), use FileMaker's native behaviors wherever they will do the job. Find Mode. Sorting. Direct data entry into the new record. Etc. There are times for departing from it, and crafting something else, but the native behaviors are nothing to apologize for, and they work quite well.
This is great! I'm finding all kinds of like minds out there!
I am an amateur who designs for myself, but I hang out here and at FileMaker Experts because I learn lots in lurking.
I am posting to be sure that I understand your phrase "Legacy program." I have a database I use to handle our family's over 7000 volume book collection. It went relational back when that was first available. I upgrade every two or the versions and have FileMaker convert to the new program. Is this what you are talking about when you use that term.
Yes, your system sounds like a legacy system. In computing terms, it means an old method or technology that is still being used. I'm not sure what you have upgraded in the solution but that could help it be more modern. However, unless you overhaul the entire system or start from scratch, it likely has some legacy aspects. For example, if you have more than one file or are using globals (when variables should be employed), your system could still be considered legacy. But, if it ain't broke, don't fix it. In other words, if it meets the needs of your clients and it isn't hampered by slow or cumbersome processes, then it may be better to leave it as is. Hope this helps.
I was especially interested in your comment about FileMaker's ability to find a subset of records being its most distinguishing feature - from spreadsheets.
Can you elaborate?
Are there other basic principles you teach that you consider to be equally important when learning FileMaker?
Thanks for helping all of us on our FileMaker journey.
People often attempt to erroneously use a spreadsheet to perform tasks really meant for a database. While spreadsheets have tried to make themselves more database-like, finds aren't part of their basic nature. I'm no spreadsheet expert but last I looked, creating found sets of rows wasn't a strong suit of spreadsheets. This power allows a database like FileMaker to change how records are sorted, printed, totaled, exported, imported, etc.
Thanks for your views on this. It gives us intermediate developers a better perspective on learning FileMaker. As you said there is a lot of elaborate solutions out there in blogs, etc. It is very reassuring to know that using FileMaker basics is also considered a good practice and worth learning well.
Having taken several of your online classes, I am excited and appreciative that you are doing this blog as you have so much talent and experience to share with the FM community. Thanks for taking this task on.
No one more qualified than you John Mark to talk about the long process of FileMaker becoming more mature. I am not an every day user, but have watched the evolution since version 3--I gleaned that from version history because I can't remember.
Jaymo. Good one. You generously say folks can call you anything they want as long as they're friendly. I would add...AND they don't call you late for dinner. Keep up the great work. I hope this blog keeps going.
Glad everyone is liking this article. I felt strongly about the subject matter but was a little concerned I might offend someone who likes to overcomplicate FileMaker design. Glad I have found some folks with the same mindset. Oh, and the blog will go on as long as I keep getting feedback like this. Thanks again!
Loved the article. I have been using FMP for 18 years, and it was very good to be reminded that simple really is better.