Meta-Consulting
Meta-consulting is a service Database Pros offers to help other developers
over programming hurdles via GoToMeeting. Email John Mark Osborne or call John Mark at (909) 393-4664 to find out more about this service.
Freeze Window The Freeze Window script step can speed up looping scripts by as much as 10 times. For instance, in a script that loops through records, each record refreshes the screen. Over hundreds or thousands of records, this can add up and slow down the processing of your script. Using the Freeze Window step once before entering the loop will eliminate the redraw and speed up the loop significantly.
Jaymo David Knight (aka Speedy) started calling me JoMo years ago, because of my initials, and it kinda stuck. Over the years, it's evolved to the current Jaymo but you can call me anything as long as you are friendly. Other nicknames include Hoss, n2 and Wiz.
Level: Intermediate Version: FileMaker 19 Category: General Wednesday, April 14, 2021
FileMaker, Inc. and Claris have been waging a war against Manage Database clutter for over two decades. In the early days of FileMaker, let's say FileMaker 3.0, there was no need to worry about packing too much into Manage Database because people weren't designing complex solutions like they do today. These days, it's critical that you use all the tools at your disposal and don't just cram everything into Manage Database. This article will serve as a history of why features were added to FileMaker, examples of how they can be used, references to articles with more information and general commentary, so you can learn from the wisdom of time.
My Soap Box I've been talking about decluttering Manage Database for years, most recently during my PodCast, Fireside FileMaker. But, it didn't start there. It really began three years ago when I introduced my blog called, the Philosophy of FileMaker (which you are reading now). Just search on the blog web site, in the upper left corner just below the logo, for "declutter" and you'll find dozens of articles that mention this keyword.
Why Care? So... why do we care about clutter? It's about NOT having everything in one place, especially if it's NOT needed or is more efficient elsewhere. Knowing as much about the different features in FileMaker is one of the keys to success. Shoving everything into Manage Database is akin to storing all your belongings in one room of your house and leaving the rest empty. In other words, your bed is best stored in your bedroom. This article highlights the historical need for specific features designed to declutter Manage Database in an effort to make FileMaker more organized and efficient.
Auto-Enter The auto-enter calculation dialog has been around for ages. In FileMaker 7.0, released in 2004, the auto-enter feature gained a new option called, "Do not replace existing value (if any)". This simple check box changed how everyone programs FileMaker. It's probably the most influential feature modification ever introduced. In fact, I uncheck this option 99.9% of the time. It really should default to unchecked but it's been that way so long, it'll probably mess me up if they change it.
BTW: With the default checked option to "Do not replace existing value (if any)", auto-enter only occurs when the target field is empty. Unchecking this option allows the formula to reevaluate whenever one of the fields referenced in the formula is modified, overwriting the current content.
Probably the most common example to demonstrate what this option can do is phone filtering. I wrote a two page article on the subject titled, Phone Filter Part One. I'll cover the simplest version of the technique here but visit the other article for validation, custom recursive functions and more.
Just so it's clear for everyone, when I say filtering, I mean entering a phone number like so:
9093934664
and, getting a result like the following so it's easier to read:
(909) 393-4664
In previous versions of FileMaker, you would need two fields to filter a phone number. The first would be a text field for data entry and the second would be a calculation field with the following formula:
Let(
@Numbers = Filter(TIP::phone_old; "0123456789");
"(" & Left(@Numbers; 3) & ") " &
Middle(@Numbers; 4; 3) & "-" &
Right(@Numbers; 4)
)
FYI: Back in the FileMaker Pro 6.0 days, there was no Filter function so we had to use crazy workarounds to remove any formatting the user might have entered. With a clean slate, the numbers could then be formatted properly and consistently.
With the modern FileMaker, FileMaker Pro 7.0 and later, it's possible to have the formatting written over the data upon exit of the field by unchecking the Auto-Enter option "Do not replace existing value (if any)". It's really that simple and places everything in a single field, thus, decluttering Manage Database.
This simple little check box also enhances indexed searches by allowing a calculation result that references a related field, global field or any unstored field in the formula. Think of it in the same vein as a standard calculation field but the ability to return stored results in all situations. It's also schema level so it's often better than a script trigger that needs to be added to every layout.
FYI: Auto-Enter calculations don't always update when referenced related field values are entered or updated. See the PodCast The Calculation Dependency Tree for more information.
Portal Sorting FileMaker 8 introduced the ability to sort a portal at the layout level. There was already the ability to sort at the schema or relationship level so the portal sorting feature seemed redundant... at first. What it did was declutter the Manage Database relationship graph by allowing a single relationship to be sorted differently (or, not at all) for every portal on every layout. Simple but very useful... making the relationship graph easier to read.
Back in the day (and sometimes today), this feature allowed for easy multiple sort orders when coupled with a hidden Tab Control or Slide Control object. Prior to tabbed objects, developers had all these crazy calculation fields that allowed the sorting of a single portal by different fields. Think about how complex this single formula would be, given all the result types that could be represented in the portal row.
When the Tab Control feature was released in FileMaker 8, I remember watching a presentation, by Andrew Lecates at the Developer Conference, where he meticiuolously outlined how to place multiple portals sorted by different fields in the same physical space using a hidden Tab Control object. The sort would be controlled by a Go to Object script step to select the correct Tab Control pane housing the portal with the correct sort. It was a lot of smoke and mirrors and don't even get me talking about what happens if you need to change just one item in the portals. Despite it's shortcomings, it was far better than a complex unstored calculation.
Slide Control came along in FileMaker 13, allowing for the same functionality but with a much easier method for hiding the Slide Control object. Slide Controls don't have physical tabs and the control dots are easily hidden with a check box preference. So, all you are left to hide is the border and fill of the Slide Control. Easy Peasy!
The point of bringing up the technique of sorting portals by multiple fields is to show that sorting at the portal level not only saves the relationship graph in Manage Database from clutter but it also cuts out clutter in the Fields area of Manage Database. It even promotes efficiency by avoiding the addition of a complicated calculation, leading to speedier solutions. While Claris (FileMaker, Inc. at the time) definitely decluttered the relationship graph, I don't think they were aware of the other areas where this new feature would help.
Conditional Formatting Conditional formatting was introduced in FileMaker 9 and was the original layout level calculation, designed to unburden Manage Database. Up until FileMaker 9, the only place where calculations could reside was Manage Database. Enter the ground breaking Conditional Formatting feature which allows objects, like fields and buttons, to change the font characteristics or fill attributes based on the result of a calculation formula.
In order to emphasize the importance of this feature, let's look at how you would accomplish a simple task before and after conditional formatting. Let's say you have a calculation result you want to change to a red font color if it's negative and green if it's positive. Prior to FileMaker 9, you would have created two calculation fields. The first would result in a calculated total if the result was less than zero and the other if the result was greater than zero. The first field would have a red text color applied and the other green. Since they wouldn't both result in values at the same time, you could overlap them in layout mode and get your color coding.
This is a pretty simple example with only two color changes so it only saved the addition of two extra calculation fields. But, imagine a more complex solution or even different requirements in the same solution. Every calculation you add to Manage Database makes the mess bigger and bigger. This is especially true when you consider that Manage database is schema wide, making the formulas available anywhere when they are only needed on a single layout.
Just for fun, I want to tell a little side story about this feature. Before FileMaker 9 was released, I attended an Alpha release and presentation to a small group of developers (they called it the "Kitchen" back then). They asked for feedback so when I saw them demonstrating conditional formatting without support for the calculation engine, I spoke up and said would it be great if you could calculate formatting changes. I got some negative feedback from Chris Crim (one of the lead developers at the time) so I thought my idea would never bear fruit. To my delight, when FileMaker 9 was released, the calculation engine was included and has become the most common choice for developers. I love it when I'm right!!
Script Triggers I remember back in technical support days at Claris when they passed around a survey asking about what script triggers we would like to see in FileMaker. That was when FileMaker 4.0 had just been released. Not until FileMaker 10 did we see those wonderful scripts firing based on user actions. All of a sudden, it was possible to do things that were never possible before. But, more importantly, we could accomplish tasks much easier without discombobulating a bunch of features together to kinda get what we wanted. That meant less fields, less relationships and less of everything.
Script Triggers have grown since that initial offering and are very complicated. There are Pre and Post Script Triggers, Script Triggers that can interrupt and remove user actions, Script Trigger evaluation orders and even Script Triggers that work only on iOS. Therefore, it's important to read about my warnings regarding Script Triggers in an article titled Trigger Tightrope. While Script Triggers are essential to good FileMaker design, they are not the only tool on your tool belt. So, don't be a One-Trick Pony.
FYI: Even FileMaker Pro 2.0 had Script Triggers in the form of Open and Close scripts. They weren't called Script Triggers back then but they are now!
I'm not going to cover a specific example of how to substitute a Script Trigger for another feature because there really isn't that classic example out there. When I use Script Triggers, it's because I could find no other way to accomplish the task. That's just my developer way. Instead, I'd recommend looking at the various articles on this web site that cover the implementation of Script Triggers like, Window Naming, Back and Forward or Triggers I Can't Live Without.
Portal Filtering FileMaker 11 took the next step in reducing table occurrences from Manage Database when it introduced portal filtering. Up until then, developers would have to create a relationship and a calculation or global field for every filtered portal, cluttering up Manage Database twofold. By allowing filtering at the portal or layout level with a boolean formula, it enabled developers to create a single relationship and move the formula to a layout object. It also allowed for more flexibility because now a single relationship could display different results in multiple portals.
My favorite tweak of this feature is a trick that makes it easier to enter new portal rows. If a portal has ten visible rows, the eleventh row requires a scroll of the portal before data entry commences. A simple technique whereby a single row portal based on the same relationship is placed outside the existing portal (usually at the top) with a filter formula of zero ("0") allows users to enter new rows outside the portal. The idea is that once the fields in the one row portal are entered and then user exits, the filter formula makes the new row disappear. The new related record still shows in the main portal (because it's not filtered) but now the filtered portal is empty, ready for the next row to be entered.
FYI: An example of the technique to add a portal row at the top is shown in this example file from Database Pros, Add at Top.
ExecuteSQL Oh, how so many folks misuse and abuse the ExecuteSQL function. It was introduced in FileMaker 12 to reduce clutter in the relationship graph. That's it! In other words, why make a unique relationship just for a script or calculation. If the relationship doesn't already exist for a portal or related field display, ExecuteSQL is your huckleberry. It allows you to grab a value or values out of a table sans a relationship. Sure, you can do more stuff with it but it's really best at this one task.
The best way to understand ExecuteSQL is from a FileMaker standpoint rather than an SQL point-of-view. In other words, why was ExecuteSQL added to FileMaker in version 12? The answer is NOT so you can perform ANY SQL query in FileMaker you want. It's so you can perform an SQL query to get some information when you don't want to add a table occurrence to the relational graph just for one feature.
But, so many developers misuse ExecuteSQL when they should be using a different feature in FileMaker. The usual reason is they know SQL better than FileMaker so they use ExecuteSQL as a crutch. I personally think it is most applicable when finding a single field value from a single table. If you'd like to discover more about ExecuteSQL and how to use it properly, look at an article I wrote called, Preferences and Options.
Hide Object This is absolutely one of my favorite features! Introduced in FileMaker 13 and loved from the very beginning, Hide Object is one of those calculation decluttering features. One reason I love it so much is because of what I had to do before this feature was introduced. In order to hide an object, a developer would create a calculation, relationship and one row portal that work in concert to hide and show whatever was contained within the portal. And, it worked pretty well. I kinda felt like I was cheating when the Hide Object feature first came out but now I can apply show/hide properties so much more efficiently. Now I don't have to tell clients it's an expensive feature.
It's easy to go crazy with Hide Object. I'm not talking about a simple formula like:
Get(WindowMode) = 1
... that hides a button from find mode. Formulas like these can be placed all over your layouts without noticeable degradation of speed (in most cases). But, when you start adding Hide Object formulas that reference related fields, you're likely to slow down your solution significantly. I remember a solution a client created that contained so many of these Hide Object formulas based on related values that the solution was unusable on a WAN.
I offered the client multiple solutions based on my analysis. The first was to use a conditional statement with a warning message rather than making the button disappear when it could not be used. This transferred the onus of calculating the sluggish formula to the button rather than the layout. In this way, the formula didn't calculate unless the person clicked on the button. Sure, the button didn't disappear but it still told the user it was unavailable when clicked.
NOTE: All layout based calculations, like Conditional Formatting, Hide Object and Placeholder Text, are unstored. In other words, they recalculate every time the screen refreshes. See the article, Designing for Efficiency, for more information of designing efficiently with unstored calculations.
I also noticed the client was using a similar formula on multiple buttons and objects. I suggested they centralize the code, using the Script Trigger OnRecordLoad to set a global variable to the slow formula. This allowed the formula to calculate just once but be referenced in multiple areas on the layout. Now, we don't always think of these solutions until there is a problem but we need to know how to troubleshoot by isolating to identify the issue and then coming up with a creative solution.
Placeholder Text Placeholder isn't as ground breaking as Hide Object, in regards to decluttering, but it certainly has it's place on this list. Introduced in FileMaker 14, Placeholder text allows you to place the result of a calculation in an empty field. It's most commonly used for field labels but any kind of function can be applied to the calculation result. I remember adding this type of functionality prior to FileMaker 14 and resorting to conditional formatting to change the text color to white so it blended into the background. Not only was it a pain to program, it also cluttered up the layout with a text block layered on top of a field.
A little known option for Placeholder text is the ability to change the font characteristics of the text inside an empty field. I didn't think it was possible till someone showed me so I'm going to pass it along. In layout mode, with the field selected, display the Inspector. On the third tab (Appearance), you'll see a couple popup menus at the top. The second popup menu should list "Placeholder Text" at the bottom. After selecting this item, you'll be able to scroll down to the text area and change font characteristic for the Placeholder text that appears in an empty field.
Button Bars and Popovers I'm not a big fan of button bars, introduced in FileMaker 14, because they place all your buttons into a single object, forcing limitations on design. For me, I like my button bars to be spaced and not smashed together. I avoided them for a long time till I was introduced to a technique for creating a Conditional Tab Control. I was using a regular Tab Control and hiding tab panes by resulting in a blank calculation result. This didn't completely hide the tab pane but I though it was all I could do until I combined a Slide Control, a Button Bar and some scripts. You can read the complete story at the article link above but it changed my perspective on Button Bars.
Then came another client project where I needed every button to change the language based on the user. Instead of overlaying a calculation field on top of a button and cluttering Manage Database, I was able to move the formula to a single Button Bar button. Button Bars a little more difficult to work with because they have two selection states but for this particular project, it was well worth the extra hassle.
I include Popovers here because they also allow for calculated text results. While Popovers are not a feature that directly replaces developer feature combinations, they demonstrate the thought process of the FileMaker development team. In other words, let's fix the problem before it's an actual problem. I believe they're thought process now is to incorporate the calculation engine into every new feature whenever it makes sense but don't quote me.
Master-Detail Layout The last decluttering feature on my list is the Master-Detail layout or, as I call it, the found set portal. Introduced in FileMaker 17, it serves two basic purposes. The first is to allow for a list and form view to be combined without any workarounds. The second is to declutter the relationship graph from all the table occurrences needed for pickers. Here's a video on how to create a picker with the Master-Detail layout feature:
Schema vs Layout You may or may not have noticed yet but there is a trend to most of these decluttering features of moving processing from Manage Database to layouts and layout object. When programming in Manage Database (Schema), the results are available everywhere including any layout in FileMaker on any platform, during web deployment and anywhere your FileMaker database is used. Accessibility is reduced when decluttering Manage Database so make sure decluttering is really going to be the best approach. It's about understanding how a feature will be used and designing appropriately.
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!
I got your other message. The example files on my YouTube channel are only available when purchasing the videos where they came from:
Videos by John Mark Osborne
The example of the While function is available from this article:
FileMaker Eighteen
Happy FileMaking!
