The Index Indexes makes finds and other features fast. Think of looking for a word in a book by flipping through each page. It’s much quicker to go to the index where it will tell you each page where the word is contained. This is exactly how an index works, it only stores each word once per field across all records. There are two types of indexing: Minimal and All. Minimal means only one index type has been created while All means two index types have been created. The most commonly used index type is a Word index and can only be created for text fields. Up to 100 characters of each word are indexed. Words are defined most often by spaces but can also be determined by commas, periods, colons and other characters. A word index does not differentiate between uppercase and lowercase. Searching for “FRED” also finds “fred”. A Value index is used for searching number, time and date fields as well as relationship key field matching. Up to 100 characters of each return-separated value are indexed. While searching can be done on partial text in a Word index, only complete values can be searched for in a Value index. When you think about it, why would you want to search for a partial number or date. Indexes are utilized in a variety of features other than finds such as value lists based on the contents of a field and unique validation. Understanding how an index works can help you better design a database.
The Wedge Claris Corporation and the FileMaker, Inc. have been housed in a building called the wedge in Santa Clara, California since they spun off from Apple, Inc. The building is nicknamed the wedge because it looks like a wedge. For some years they had an adjacent building called the interstitial but it was let go when the company downsized to focus on FileMaker products.
Level: Intermediate Version: FileMaker 16 Category: General Tuesday, April 10, 2018
I've mentioned FLF (Find-List-Form) in several other articles on this web site but I think it's so important that I wanted to devote an entire article to the subject. So many developers have moved so far away from the roots of FileMaker that they almost don't even know what they are. Then they teach other budding FileMaker developers wacky workarounds for something that is so easy to accomplish with basic FileMaker tools, and these impressionable minds think it's always the right approach. Yes, sometimes we need a good workaround but most of the time we just need good FileMaker fundamentals.
What is FLF Simply, FLF is Find-List-Form. It's an acronym I use to quickly define the process of finding records, displaying a list of hits and finally showing the detail in form view. It's the same formula all search engines utilize. If you go to the Google.Com web site, you type in your find criteria, you are presented with a list of hits and when you click on one of the matches, you visit the web site. Click the back button and you are back to your list of hits and ready to visit another web site. It's a simple but effective approach to surfing. Bing and other search engines work the same way because it's easy to understand. There's no need to overcomplicate the process cause it just works.
That's exactly what I think many FileMaker developers are doing today. They've distanced themselves so far from the original design of FileMaker with ExecuteSQL, complicated Web Viewers and JSON, just to name a few, that they've forgotten how to use the basic FileMaker tools that are the work horse of the platform. Sure, there's a time and a place for these cool technologies but only when FileMaker alone cannot accomplish the job.
As mentioned in the opening of this article, the bigger problem arises when these developers publish these super cool techniques and inadvertently convince amateur developers it's always the proper approach. This creates a community of developers who don't even realize what the basics are because they never learned them. I see it all the time on the forums. Some developers even brag about it. I remember one gentleman saying he doesn't do anything but ExecuteSQL. Shaking my head, I though to myself about the original purpose of ExecuteSQL and how it's been twisted into a solution for anything when a developer doesn't know the basic FileMaker tools.
FLF is much more FLF is so much more than not using crazy find interfaces and algorithms when a straight up FileMaker find will do the trick. It's about using the tools provided by FileMaker before a complicated workaround. But... let's start there. What developers tend to do is create fancy filtering finds. While the work required to create these techniques can be used over and over again, making them cost effective over time, it's the modification that it is inevitably required that becomes the problem. And, it's not just the original developer but anyone who adopts the solution. Now the new developer has to tear apart this complicated find filtering technique when a simple find, list and form would have sufficed.
ExecuteSQL As I said, FLF is much more than interface. I use ExecuteSQL sparingly because most of the time a FileMaker find or relationship can get the job done. ExecuteSQL was introduced in FileMaker to reduce relationship graph clutter. If you need a value from another table but don't want to create a relationship, ExecuteSQL can grab that information for you. But, if you already have a portal or a related field, you already have the relationship. Why complicate a solution with ExecuteSQL when FileMaker alone can already accomplish the task.
Besides, ExecuteSQL is very limited. Any list of data gathered can be displayed but not navigated. A FileMaker find or portal has context and therefore can be navigated using Go to Layout or Go to Related Record. All Execute SQL can do is display the information. That means it's good for grabbing odd pieces of information from another table. My classic example is a preferences section that tracks the preferred behavior for every user on the solution, like which layout they want to start at on open or whether to navigate to form or list when visiting a new layout. Using relationships, preferences require a separate relationship from every table where the behavioral information is needed. That's what relationship graph clutter is and why FileMaker, Inc. introduced ExecuteSQL.
FYI: ExecuteSQL was introduced to FileMaker 12 to help unclutter the Manage Database by reducing the number of one purpose relationships that just supported calculations or scripts. The same is true for a bevy of other features including filtered portals, conditional formatting, hide object, variables and many more, which can reduce the number of fields and relationships needed in Manage Database. Learning the origin of a feature can often assist in understanding when it is best utilized.
Reporting What's so bad about a report based on subsummary parts? Finds and sorts make it incredibly flexible. Still, developers find the need to reproduce Excel style cross-tab reports instead of working with the strengths of FileMaker. This usually requires many unstored calculations and summary fields, often slowing down a speedy developer tested report, once a bunch of records are added to the deployed multi-user version of the solution. Not to mention the inflexibility of requiring a calculation and/or relationship for every variation of the report when a find and sort could solve the problem with a basic subsummary based report.
I think this particular misuse of FileMaker is the fault of the client and the developer. I'm working with a client right now who is used to an old FileMaker solution and wants to replicate the exact functionality to the point of making it inflexible. I've tried to convince them of a more flexible find based solution but they are insistent on displaying their scheduling in portals. I even went so far as to program a new version of their scheduling system but they can't let go of the past, picking apart the interface instead of trying to understand it. Most of the time I can convince the client but this time I had to give in. I think this is where many developers go wrong. Instead of trying to convince their client about a better approach, they take the easier route and just give them what they want. Sometimes you need to guide your client to the FileMaker way of reporting.
Ersatz Systems I never understood why developers felt the need to recreate FileMaker security. Well, if you were back in the dark days of security using FileMaker 6 or before, I guess I can understand. But, why do it nowadays? It does everything you need already! Maybe some developers don't know FileMaker security has changed significantly? Maybe they never understood security? Maybe they just think they can do a better job? I'm here to tell you that attempting to recreate the built-in security in FileMaker with scripting and calculations will make your solution extremely vulnerable to hackers. It should never be attempted!
Let's start by defining "ersatz" which is simply an artificial or inferior substitute or imitation. That pretty much sums it up. It's like coffee compared to chicory or sugar compared to NutraSweet. They get close but nothing's like the real thing. I believe Steven H. Blackwell was the first person to apply this term to artificial security in FileMaker but I could be wrong so don't shoot me.
With ersatz security, developers attempt to authenticate accounts and assign privileges for field, table, record and layout access using scripts and calculations. What ends up happening is multiple users are assigned the same account through a script that authenticates during the opening of the file. Running this script requires unchallenged minimal access to the file, allowing a hacker an easy way into the FileMaker solution by the simple canceling of a script. Built-in security never even lets the hacker in the front door and also secures account and privilege information outside tables and fields where it can be protected properly.
"It's like letting people in your car hoping they don't know how to start it once they are inside... Much better to not let them in your car in the first place." - Wim Decorte
SIDE TIP: Scripts can be canceled in a variety of ways including Advanced Recovery options that allow someone to bypass the opening script but most notably via APIs (Application Program Interfaces) like PHP, XML, AppleScript and REST. All these technologies can control FileMaker so unhindered minimal access to a file spells trouble for someone who can exploit these technologies.
Luckily, I don't see ersatz security very often these days but it's like trying to eradicate a disease. Every once in a while and outbreak occurs and a developer gets on the forums extolling the superiority of ersatz over built-in security. It's a never ending battle fought mostly by security specialist Steven H. Blackwell. Steven has taught us all well so if he's not around to thump an errant developer on the head, someone else will step in and clear the air.
If you want to learn more about ersatz security and why it is vulnerable, read this article length forum post at:
It's an old post from 2006 but there's nothing better out there on the subject.
Conclusion I get it. Developers are going to create solutions by combining multiple features in ways FileMaker, Inc. never imagined. I'm not trying to stop you from thinking outside the box to solve complex problems, I'm just trying to get you to curb your enthusiasm for abusing FileMaker. Or, better said, I want you to consider all the possibilities available before just using your "go to" feature as a crutch. Consider the advantages and disadvantages of every approach to solve the problem at hand and pick the best one. Don't blindly apply a technique because you don't understand FileMaker as a whole. Learn FileMaker by starting with the basics. That way you'll be better able to understand when to use a complex workaround or what solutions will cause problems.
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!
Wow, great article. I like your illustrations and analogies!
I find that straying from the basic core methods leads to "what the hell was I doing/thinking?" a few weeks/months down the road.
I've been engaged in picking up another developer's work, that is filled with the little "diversions" and it costs way more time to figure out with the intention of the code was - "saving clutter from the relationship graph" is a weak reason to ambiguate the logic.
HTML5 UI/UX developer with nine database certifications. Well said!
So many people become too obsessed with complexities that the fundamentals are forgotten.