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.
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.
Even before Script Triggers were officially named, there were Open and Close scripts. Only till object and layout Script Triggers were introduced did Open and Close scripts get labeled as File Script Triggers. For the most part, all that's changed is how they are specified. To set a script to run on opening of your file, choose the File Options item underneath the the File menu. Select the Script Triggers tab and attach your Open script to the OnFirstWindowOpen event. This script will run only the first time a file is opened in single-user or once for each user in a multi-user scenario. This is in opposition to a script that runs every time a window is opened via the OnWindowOpen trigger, even when a new window is spawned from the same file.
On the other hand, close scripts generally aren’t very important in a FileMaker solution, especially a multi-user solution. However, Open scripts should be employed on just about every solution created. Open scripts setup up your solution for the user, making it easier to use. It's much like the impression the front door of your solution makes on visitors... very important. In the following paragraphs, there are many examples of common Open script snippets you can include in your own solutions but Open scripts are as varied as there are developers and the solutions they create. The ideas in this chapter are just a starting point but the discussions that ensue will deepen your FileMaker theory.
SIDE TIP: Open scripts can manually be interrupted using two methods. The first method uses the Script Debugger. Just turn it on and then open a file. You can either walk through the Open script to see how it functions or simply cancel it using the button with the square icon. A lesser known method is to use the Advanced Options in the Recover feature. Here you can choose to recover a file without actually doing anything to the file. Just choose the "Bypass Startup Script and Layout" option and uncheck everything else.
Go to Layout Selecting the correct layout, on open, can be as simple as a single Go to Layout script step or as complicated as selecting a layout based on the current Privilege Set or device. For example, you might want to send sales people to the sales layout and marketing people to the marketing layout on startup. Or, you may want to choose different layouts based on the device, like a Macintosh versus an iPhone.
It’s important to remember that multi-user solutions act differently than single-user solutions. While a single-user solution defaults to the last layout being used before closing, a multi-user solution loads the last layout the developer was using before the solution was uploaded to the server. This makes an Open script all that more important, even if it just goes to a hard coded layout for all users.
In fact, there is an order of operations to layouts when opening a solution in a multi-user environment. If no layout is specified in an Open Script or in the "Switch to Layout" option in File Options, the last specified single-user layout is selected. If a layout is specified in an Open Script, the last single-user layout is still visited, although briefly and unseen by the user. That doesn't mean record data isn't read, so don't be fooled! Only if a layout is specified with the "Switch to Layout" option in File Options will the last single-user layout be skipped entirely. So it's really clear, the "Switch to Layout" feature occurs before any data is read, essentially bypassing the default or last used single-user layout.
SIDE TIP: FileMaker will load the number of records required to complete a list view, which could exceed 40 or 50 in a large window. Form view is different and will load 25 records at most.
For fast load times, I like to select a layout, in File Options, displaying records from a table with one or zero records. This gives the user instant access to your solution, instead of making them wait for records to load from the default layout. Trust me, this can unbelievably slow for a remote connection, even if you don't see the speed degradation in the comfort of your local development setup. The layout I select in File Options varies depending on the platform. For a desktop machine I usually have a splash screen that leads to a find for common user requested records. On iOS devices, I like to have a main menu that allows a user to perform a find, in order to avoid any long record load times for what is usually a remote deployment.
Window Settings I’ve seen so many sloppy solutions in my years as a FileMaker consultant, I almost expect it. If you want your solution to be perceived as professional, don’t just program it well, make sure it looks good. Dressing your windows properly is one of those often overlooked and underestimated details that polish a solution from a rock to a diamond.
There are many window settings I like to utilize but let’s start with some easy bits and pieces. I almost always start by turning off and hiding elements of the window interface, including the removal of the ability to display the Formatting Bar and hiding of the Text Ruler. Neither of these belong in browse mode in a professionally designed solution. The user shouldn’t be changing text in browse mode as it overrides settings across all layouts. This can cause havoc with layouts designed for printing or viewing with a different text style or size. Users also shouldn’t be changing ruler settings in fields. If they want that kind of control they should get a word processor or the full access password. These settings are reserved for layout mode only!
Next, I like to hide and lock the Toolbar. While the Toolbar can be very important for the developer, all Toolbar functionality should be mimicked with scripts, Get function symbols and calculations. For starters, a solution rarely needs all of the choices provided in the Toolbar, wasting valuable screen real estate. And, if a user customizes their Toolbar outside of your solution, that customization will still display in your solution. While you can block functionality with security, what’s the point of a bunch of dimmed buttons and users asking you why nothing works in the Toolbar. Finally, it is unlikely the Toolbar interface will match your theme for the rest of the solution, looking out of place.
It's also important to hide and lock the Menubar. Even though it only shows on iOS devices and WebDirect, it doesn't cause a problem when the script to hide it is run on a desktop machine. If you aren't familiar with the Menubar, it's that bar at the top of the screen that allows a user to perform a find, close a file, export, save/send records, go to layout mode, etc. Under WebDirect, the Status Toolbar and Menubar are integrated together but still need to be hidden separately. Again, this stuff should either be replicated in your interface or not shown to the user at all.
Testing for the current privilege set, in an IF script step ,on startup allows the toolbars for the developer but not the user. There is no need to include the Show/Hide Text Ruler script step in the conditional statement since it can be turned off for both user and developer. If the developer wants it on, they can easily do so with their full access password abilities.
I personally like the window placed in the upper left corner of the screen on the Macintosh or Windows. This feature is only available under the Windows operating system when using FileMaker 16 or later due to the change from Multiple Document Interface (MDI) to the Single Document Interface (SDI). In FileMaker 15 or earlier, the window will be placed in the upper right corner of the parent application window. I’ve seen some developers place it a dozen or so pixels from the top and left but I think it looks neat and clean right up against the edge and saves screen real estate. It’s really a personal preference with no real advantage so have fun with it.
SIDE TIP: A window can be centered on the screen or application window using the Move/Resize Window script step with the following code for the distance from top and left parameters:
(Get(ScreenHeight) - Get(WindowHeight)) / 2
(Get(ScreenWidth) - Get(WindowWidth)) / 2
Custom Menus Custom Menus are a must for all professional solutions and should be installed on open. Some developers set their Custom Menus at a layout level but this is absolutely the wrong place. If you attach a Custom Menu to a layout via Layout Setup, development requires the toggling on of the standard FileMaker menus every time a layout is switched. That’s just the way they work when hard coded in layout mode. The result is a constant battle with the custom menu settings during the development process. However, if the layout is left at the default, Custom Menus can be installed on open using a simple conditional construct. If the developer logs on with the full access privilege set, install the standard menus, otherwise install the customized menus. It’s also critical to notice the “use as file default” on the Install Menu Set step as it allows a layout set to “[File Default]” to work in coordination with the script.
I push major navigational and reporting scripts to the menus instead of using a main menu layout or including navigation on data entry layouts. Main menus require too many clicks! First you click to the main menu then you click to where you want to click. I prefer creating a custom navigation and report menus and choosing a single menu item or even utilize the keyboard commands that can be attached to custom menu items. If navigation or reporting is "conveniently" located on each data entry layout, it wastes a tremendous amount of screen real estate and effort integrating it into the interface. Even if you don't customize existing menu items, adding a navigation menu will change your development life.
Deployment Even though this blog is focused on programming for the desktop, many of you will deploy solutions on an iPad or iPhone. Different layouts are recommended for each device. While Macintosh and Windows layouts can be interchanged for the most part, iPad and iPhone are enough different to warrant separate layouts. You will most likely have special layouts for WebDirect as well. While Custom Web Publishing with PHP or XML also calls for specialized layouts, they are generally plain layouts with fields neatly but unceremoniously placed on a blank white background, since no user ever sees them.
Anyhow, the point of this section is not to tell you how to design layouts for these different deployments but how to select them on open. There are three possible Get functions that can help you with the decision process: Get(ApplicationVersion), Get(Device) and Get(PlatformVersion). Each function has it’s own strengths and should be used in different scenarios.
“Pro” + version for FileMaker Pro
“ProAdvanced” + version for FileMaker Pro Advanced
“Runtime” + version for FileMaker Runtime
“Web Publishing Engine” + version for FileMaker Server Web Client
“xDBC” + version for xDBC Client
“Server” + version for FileMaker Server
“Go” + version for FileMaker Go on iPhone or iPod touch
“Go_iPad version” + for FileMaker Go on iPad
For example, FileMaker Pro Advanced 16.0v1 will return “ProAdvanced 16.0.1”. Most of the time the version is unimportant so it is lopped off when testing conditionally:
Other times the whole first word is unimportant so further parsing may be required. For example, Pro and ProAdvanced use the same layouts so the first three letters can be grabbed:
Left(LeftWords(Get(ApplicationVersion); 1); 3)
You can see how Get(ApplicationVersion) can be very verbose and not necessarily the best choice if you are just trying to differentiate between Desktop, iPad and iPhone. A better choice might be Get(Device) which returns:
“0” for an unknown device
“1” for a Mac
“2” for a computer running Windows
“3” for iPad
“4” for iPhone or iPod touch
“5” for Android
If you throw in WebDirect, things get even fuzzier. Get(ApplicationVersion) can only tell you a web client is connecting but not the technology they are using (which may not matter), while Get(Device) cares only about devices, lumping WebDirect in with the device where it’s running. Another possibility is Get(SystemPlatform):
“1” if the current platform is Intel-based Macs
“-2” if the platform is Windows
“3” if the platform is iOS
“4” if the platform is FileMaker WebDirect
The negative number is a throwback to when FileMaker differentiated between versions of the Macintosh and Windows operating systems and hardware configurations, allowing for the ABS function to return a single value for Macintosh or Window (i.e. Intel vs PowerPC Macs). Staying on point, you can see the Get(SystemPlatform) clearly detects WebDirect better than the first two choices.
So, what to choose? It all depends on your deployment. If you are deploying for desktop and iOS, Get(Device) works well. Add any other platforms and you probably will use a combination of these three functions to specify to the correct layout on open.
File Name If you change the name of your FileMaker file, you stand the chance of losing connection with Import Records script steps that refer to the renamed file. There are a few steps that could fall victim to a name change but they aren’t worth mentioning since they are unlikely to be used in any sort of script. For example, the Recover feature utilizes the same Specify Output File dialog as Import Records, but would you ever program it into a script? Any script step that utilizes the Specify Output File dialog as a script option is susceptible to losing connection with the renaming of a FileMaker file. However, the one you are most likely to have issues with is the Import Records script step when you are importing from one table to another within the same file.
It’s actually a very common practice to move data from one table to another quickly and efficiently using an import. For example, you might have standardized procedures that get applied to similar jobs which means moving them from a table containing unique records for each procedure to a join table that attaches the records to a specific job. Why loop through the records slowly, set the field values to variables, switch layouts, created new records and set the values, only to repeat the process over and over. An import can do it in a couple steps and much more quickly when many fields and/or records are involved!
The point of all this discussion is the declaration of a global variable on opening of the file.
Set Variable [$$FileName; Value: Get(FileName)]
Other steps, like Export Records, use the Specify Output File dialog but aren't storing the name of your FileMaker file, just the name of the file to be exported. No need to worry there since there's no connection to lose. The only script step that doesn’t use the Specify Output dialog for specifying files is the Open File script step. It uses the FileMaker External Data Source feature which is far more complicated than simply specifying a file name and is unlikely to break. Other script steps don’t use this dialog since they are more versatile and can specify any kind of file. Open File only needs to locate FileMaker files but anywhere on the network or hard drive, requiring a more sophisticated algorithm.
Normally I detest global variables, since they clutter up the Data Viewer. What I really detest is over-reliance on global variables when another features work just as well. But, sometimes global variables are the best answer and this is a great example case. By declaring the file name at startup, the name of the FileMaker file can be changed without an Import Records step losing connection with the current file.
Initializing Global Fields Global fields are a strange beast when compared to other field types. They aren’t really fields since they don’t permanently store data. Upon closing of a multi-user file they are erased. Actually, they are set to the last value that was entered while in single-user mode. In other words, whatever the developer was doing just before he uploaded the file to the server is what will always be the initial value for each user when opening the file. While you could try to remember to set the value manually before uploading it the server, it’s better to initialize global fields on opening of a file so human error is not a factor.
Globals are used all over professionally designed solutions in order to add sophisticated interfaces. A common interface is a search-as-you-type list view that uses a find script and a trigger to filter a list view as you type find criteria. In order to set the global search field to a null value, a startup script is usually employed.
Set Field [CUSTOMERS:xfilter; “”]
Some globals may be initialized with a default value such as the case with the date range added to a filtered relationship. This initialization defaults the invoices being shown for the last 30 days.
Set Field [CUSTOMERS:xdatelow; Get(CurrentDate) - 30]
Set Field [CUSTOMERS:xdatehigh; Get(CurrentDate)]
Other global field initializations may need to be performed separately from the open script, warranting their own script. Examples include clearing global fields upon opening a popover or new window. Some global fields don’t need to be initialized at all since they are never seen by the user. An example of this can be found in the “xfind” field used in the Set Field portion of the Compare & Contrast blog article on this site. Since the global field is only used to bridge the gap between browse and find mode, there is no need to initialize the value. Other times the script used in the technique can initialize the value before the user sees it, such as when occurs when a layout is displayed. Just initialize the global fields before the layout is displayed.
SIDE TIP: Global fields are defined as a single value for every record but there is so much more to know. While global fields retain their value after closing in a single-user scenario, global values are initialized in a multi-user scenario. The value isn’t reset but defaulted back to the value that was present before the file was uploaded to the server for sharing. In addition, global fields are unique to each user on the network and do not cause record locking. Their primary use is interface development. Replace globals with local variables whenever it makes sense, as globals clutter up Manage Database.
Creating Found Sets Another usability feature for well designed solutions is to anticipate the needs of your users on open by locating the most common records they want to view. Maybe they like to see all the orders entered on the current day. Others might like to see the most recently added customers. Whatever the case, found sets are the foundation of a database so why not lighten the load for your users by showing them what they already want to view.
In this open script, let’s show the users all the invoices for the current day. It’s a simple script but will save the users valuable time as well as remind them at their all important orders. This script can actually be written several different ways but the one thing for sure is that users may want to refind the invoices from yesterday without closing and open the file again. Therefore, let’s make it a sub-script that can be run as part of the open script or on it’s own from a button.
The first method seems the easiest. Just perform the find manually and then create a script containing the Perform Find script step. That’s right, the last find performed, whether by script or manually, will automatically be entered into a script that can restore actions. There’s no need to manually program it into the script itself, which is often far easier with complicated find criteria.
While this script works flawlessly, I find it cumbersome to read. I need to double-click the script to read the find criteria or add a comment that describes the find criteria. It's just not convenient for my fast paced development process. On the other hand, this scripted find is self-documenting.
Not only is the script easier to read, it allows for calculated find criteria. Yes, variables can be included in restored finds but you still need to reveal that the variable is utilized in the Perform Find script step to understand that a variable is actually being used. I prescribe to the adage that time is money, so reading a script faster is ideal for me, even if it is a couple of steps longer
On the flip side, if the find criteria is complicated with many requests and omits, the restore method looks a lot better, almost like a modular script, allowing you to reveal the criteria only when you want to see it. Think of it as uncluttering your script. Which scripting approach you choose depends on the needs of the solution at hand. Sometimes reading it easily is the biggest advantage and sometimes hiding the criteria is the best option. It’s all about comparing and contrasting to come to the best fit for the moment.
Regardless of what approach you end up using, the result is the ultimately the same. Finding records on open is a common task that can assist the user immensely and should be included in just about every open script. Simply showing the user the default all records with the last record selected seems so inconsiderate.
The Final Script The final open script is shown here with all the options discussed. Start with what is offered here but develop your own startup script that meets the needs of how you program. That may include code that you add to your starter template as well as programming that is unique to a particular project. The key is to never stop innovating your approach according to your knowledge and the new features offered in FileMaker.
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!
Johns comment is important, since crucial globals might not get set. OnFirstWindowOpen also does not fire if a file is opened through a relationship without a window first, and then the window gets pulled via using Go To Related Record. Even though that would technically be the first window, nothing happens.
there are several other conditions where the OnOpen script will not fire, the latest of which being when you open a card window from another file, and in certain circumstance when you open with an fmp url...