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.
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: Intermediate Version: FileMaker 17 Category: General Tuesday, April 9, 2019
Back in college, when I was working in the computer lab on campus, it was a requirement to know Microsoft Word backwards and forwards. That's because half the students in there were creating resumes. The biggest issue was using spaces to line up columns so I spent a lot of time fixing resumes, for which I got little thanks. Wait! What was I going to talk about? Oh yeah... custom menus. The reason I mention Microsoft Word is I loved a feature that allowed you to customize the menus to my preferences so I could remove the clutter I didn't want to see. I haven't used Word in decades so I'm now partial to customizing menus in FileMaker to make it easier to use. I won't be covering the basics in this article so you might want to bone up on custom menus in the online help before reading this article.
Custom Menus are Cool! If you don't use custom menus on every FileMaker solution, I'm here to convince you otherwise! For starters, they allow you to remove unnecessary choices from the menus that just confuse your users. What users end up doing is calling you and asking why a particular option is dimmed. Or, maybe they never call you and just get frustrated from all the grayed out menu items. It's best to limit menus to the available choices instead of presenting the user with a sea of seemingly endless unavailable choices. The reasoning is simple... it relaxes the brain to have fewer choices!
But, that's only one thing custom menus can provide. You can also rewire a menu item to run a script instead of the normal built-in functionality. The name stays the same. The keyboard equivalent stays the same. I just runs your script instead of the normal operation. For instance, I like running my own find script so I can hide the FileMaker error messages when no records are found. I replace those messages with my own user friendly and solution specific messages. For example, I might say, "no customers were located" instead of "no records found". In other words, don't expect users to understand database terminology or even care. They just want to get their job done as quickly so they can spend time with their family. Your job is to make a database friendly so the user doesn't have to know any FileMaker functionality... just your solution.
Custom menus are also great at reassigning keyboard equivalents, creating brand new solution specific menus and adapting to the current privilege set, layout table or record. But that's not really what I'm here to talk about. I'm merely describing the basic functionality of custom menus which you can read just about anywhere. What I want to talk about is optimizing custom menus. So... sit back, relax and enjoy the ride.
Not Security! Before we start our journey, it's important to note that custom menus aren't a substitute for security. Just because custom menus can hide or change a menu item, doesn't mean the feature can't be accessed by the user. A script can still perform an action hidden by custom menus but not when blocked by security. In addition, any third party software like AppleScript or an API like PHP can override custom menus. You may be thinking, "I'd never use custom menus to substitute for security", but I've seen it many times. It's akin to scripted ersatz systems in FileMaker... they just don't substitute for real security.
FYI: A script can override security if it's granted full access privileges from the Scripts menu.
Navigation Now for the cool stuff! One of the best uses for custom menus are for navigation. Lots of people like to use main menu layouts but that requires the user to click on a home button and then click the feature they want, creating a clunky interface. Other folks use horizontal or vertical button bars. The problem with them is they need to be repeated on every layout. Any time a change is made to navigation, every layout has to be monotonously modified. Some developers try to subvert duplicative navigation objects and programming by using adaptive calculations and global fields. Unfortunately, this approach just creates a lethargic interface with all the unstored calculations and programming complexities required for this technique.
I've found that a custom menu navigation menu is the best solution. It's easy to implement, easy to update, allows for keyboard equivalents, takes up no screen real estate and requires less clicks to navigate for the user. I mean, why go through all the trouble of designing a main menu layout or creating a sophisticated button bar when a custom menu can handle the job with such ease. The only thing I can think of is... developers like to charge their clients more money. Sad, but possibly true.
I'm constantly ruminating over more efficient methods for programming a FileMaker solution. I include that with my considerations on speed of operation, cost and ease-of-use. I include all this and more in that swirling mass of ideas that go through my mind when I program. Hopefully, what happens is the right answer comes out of my head that optimizes all these factors. So, when I consider custom menu navigation, it has nothing but an upside, so why not try it on your next solution. You can also include reporting features so there's no need for a reports layout. Just tuck them nicely away under a menu and when a user wants them, they can reveal them by clicking at the top of the screen.
Adaptive Menus Way back in 2005, just after the custom menus features was released, I presented about them at DevCon. The first thing I did was talk about how to make them adaptive to the current context. Context can mean many things but for custom menus it translates to the table from which a layout is displaying records. In other words, you want your menus to adjust to the current layout. As a simple example, you want a custom menu to say "New Customer" on the customers layout and "New Invoice" on the invoices layout. Scenarios can get much more complicated but you get the idea.
If you don't make your custom menus adaptive, you end up creating a custom menu for every table. This becomes a tedious process of duplicating a custom menu and modifying it to meet the needs of the new table. Then, the inevitable occurs. A change is required across all the custom menus and the boring task of updating a custom menu becomes wearisome. If you only had to make the change in a single adaptive custom menu, life would be much less dreary. Think of it as centralizing your custom menu code.
The good things is, adaptive custom menus are pretty easy to implement given the vast array of calculation options. Most of the time, you just need some good naming conventions for tables and layouts along with some fairly simple calculations. Let's attack the customization of the Records menu since it requires the most customization. The first step is to make sure you have a good naming convention for tables and layouts. I like to use one word table names and layouts that always begin with the source table name. Here are some examples of table names I might use:
CUSTOMERS INVOICES PRODUCTS CLIENTS COMPANIES
FYI: In addition to helping you with this technique, naming conventions help you identify objects quickly and easily. I also type my source tables in all caps to make them easier to recognize in scripts and calculations.
TIP: Table, field, table occurrence, script, value list and layout names should be succinct but meaningful. Long names typically clutter up scripts, calculations and the relational graph without providing any additional information.
I use table and layout naming conventions for many tasks other than custom menus. In every case, I always grab the first word from the table name or layout name:
Get(LayoutTableName) returns the table occurrence attached to the current layout. I use the anchor-buoy system of relational design with all my table occurrence anchors named the same as the source table so I don't really need the LeftWords function. See the article Wrangling Relationships if you are unfamiliar with the anchor-buoy system of relational design.
In order to make a single custom menu adapt to each layout, I typically refer to the layout name. I could use the table name just as easily but I've just become accustomed to using the layout name. Here's the basic formula for the New Record menu item as placed in the Item Name section of custom menus:
"New " & LeftWords(Get(LayoutName); 1)
Of course, I don't want it to have the name in plural so it's a good idea to add some additional code:
@Layout = LeftWords(Get(LayoutName); 1);
"New " & Proper(Left(@Layout; Length(@Layout) - 1))
Subtracting one from the length of the table name allows the Left function to return the singular version of the table name. I also take the opportunity to make the table name title case using the Proper function. But, how does this work with table names like "COMPANIES"? The solution is to program in an exception:
@Table = LeftWords(Get(LayoutName); 1);
Toggle = 1 and Right(@Table; 3) = "ies";
Left(@Table; Length(@Table) - 3) & "y";
Toggle = 1;
Left(@Table; Length(@Table) - 1);
The formula above is actually a custom function. Centralizing the code in a custom function makes it easier to update the formula if a file wide change is required. The custom function is called by the following function name and parameters:
It can be called with a toggle of "1" for a singular table name or a toggle of "2" for plural. A plural version of the table name is needed in some cases, such as with the menu item Delete Records... The addition of the custom function dwindles the New Record menu item formula down to a simple formula:
"New " & Proper(TableName(1))
TIP: If you really need a two word table name, just use am underscore between the two words. The underscore will not be seen as a word separator so the LeftWords function can grab the entire table name.
All of the rest of the menu items under the Records menu should fall into place with the information presented so far so I won't go into detail. However, you can look at the example file that comes with this article for specifics on each menu item.
Installing Menus At that same DevCon, I remember talking to a systems engineer who was insistent that the best way to install custom menus was through Layout Setup. After a lengthy discussion, I finally convinced him the best way to install custom menus was through a script. The point I made was the constant irritation of having to switch the menus back to the Standard FileMaker Menus during the development process. Every time a layout loads, the custom menu attached to the layout is loaded, requiring the developer to reset it to the standard menus. Hence, this is the reason why Layout Setup defaults to the "[File Default]" menu set. Should I spell it out? Installing menus via Layout Setup is for beginners creating very simple solutions.
Once you have an adaptive menu set in place, it's really not that hard to install menu sets with a script. I usually do it once on open with a simple script:
If [Get(AccountPrivilegeSetName) = "[Full Access]"] Install Menu Set ["[Standard FileMaker Menus]"; Use a file default: On] Else Install Menu Set ["Users"; Use a file default: On] End If
Testing the privilege set on startup allows you to specify standard menus for the developer and the adaptive custom menus for everyone else. Even if you need to switch the menu set for specialized menu scenarios, all you have to do is run a script to change menu sets upon entering a layout using the OnLayoutEnter script trigger.
What Else? What else can be done with custom menus? Well, the biggies have already been presented but there's nice little touches I can talk about. For example, I like to change the Browse menu item under the View menu to "Data Entry". Call it what the user would be most apt to understand. There are quite a few menu items I remove so users don't get themselves in trouble. The most important examples are the Find/Replace, Replace Field Contents and Delete All Records. While this can be taken care of with security by setting the available menu commands to Editing Only or Minimal, I like to have two locks on my door. I remember a story where I had given a client access to all the menu commands and they destroyed their database with Find/Replace feature. If only I had blocked it with custom menus, I might have averted this situation.
I'm Done! Well, that's about it. There may be a few more tricks or techniques lurking in the back of my FileMaker mind but I can't think of them right now. If you have something you like to do with custom menus, please let me know about it. I look forward to hearing from you!
This is great info. I love creating custom menus, and these are exactly the kinds of things I do. One thing you may have missed, though: you can use local variables in custom menus! When I change the Records menu in much the same way you have here, I always set the singular and plural noun in the menu name. (I use µ to preface menu variables, so they don’t interfere with anything else.) So, the menu name would be set as follows:
Great reminder to use custom menus. I was actively doing this perhaps 5 years ago, but more recently I steer users away form the native menus completely. I admin to using a Menu layout (several in fact for different categories of user)
Since 16 I am moving to using the small card window instead of navigating to Menu layouts.
However, your post is prompting me to revisit custom menus - if only the custom menu UI was a bit more friendly...