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.
Level: Intermediate Category: General Tuesday, January 16, 2018
How you design a FileMaker solution often depends on how it will be accessed. Is it a single-user solution running off the local hard drive? Is it a multi-user database with all users accessing from the LAN? Maybe there is a occasional remote access from someone at home? What if the company is distributed across the United States and needs a completely cloud based solution? Maybe some users won't always have an internet connection? It could be that some users don’t even have FileMaker? These are some important questions to ask yourself or your client before beginning the design phase of a solution.
Single-User The easiest design scenario is single-user. There is no need to worry about network speed, global field behavior, web browsers or record locking. Just design a solution that works on your desktop or mobile device and it will work on just about any desktop or mobile device. Sure, you need to consider screen resolution for desktop monitor and device variation, as well as fonts and graphics for cross-platform compatibility, but the way you design your solution will not be affected by the network available, unless you are using a Web Viewer or other remote content to supplement your single-user solution. However, it is important to test your single-user solution with a reasonable number of records to make sure it performs adequately.
Multi-User If you are creating a multi-user solution, the differences, when compared to a single-user solution, are vast. Global fields are unique to each guest and are initialized upon closing of the file and default, on open, to the last value entered while in single-user mode. Record-locking needs to be considered in scripts that edit or delete record(s) or they may fail or, at the very least, confuse the user with FileMaker oriented error messages. The screen resolution is often forgotten but even two people with the same computer screen could have the resolution set differently and therefore a different amount of screen real estate and thus, different requirement for the FileMaker interface. All in all, when designing a multi-user solution, almost every aspect of FileMaker gets more complicated including security, scripting, calculations and layout design.
This reminds me of a class I was leading fifteen years ago. I remember it like it was yesterday. There was a guy in the front row paying close attention to everything I said and furiously taking notes. When I got to the part in the class about saving found sets for future retrieval, I saw his demeanor change from interest to horror. It was at the point when I mentioned the record locking issues that occur when trying to mark records by flagging each record that I saw the look on his face distort. Apparently he had created a feature for placing a value on a record to bookmark it. Since he wasn't familiar with record locking and was testing in single-user mode, he hadn't realized it would cause an issue. At the end of class he thanked me profusely since he was about to release his solution the following week.
SIDE TIP: Marking records in a multi-user solution also fails when two or more people try to mark records for future retrieval. If the same flag is used, then all marked records are retrieved for all saved sets for all users. Trying to mark records with unique values becomes cumbersome at best and still fails when record locking is considered. The only solution is to think outside the box and use features that don't cause record locking.
LAN vs WAN If you are designing a multi-user solution for use on a LAN (Local Area Network), it is very different than designing the same solution for a WAN (Wide Area Network). In a WAN scenario, you need to think about your lowest common denominator, which could be as low as 3G. I’m not talking about occasional remote access. Users may not expect optimal performance when accessing a solution remotely once a week. What I'm talking about is consistent, daily remote access. If the performance isn't solid, users will complain. And, they may not complain at the beginning, but enter a few thousand records and performance could change drastically going through that narrow pipe.
One of the best WAN tips I can give budding FileMaker developers is to use a main menu on iOS devices, especially when network access is expected to be slow or there are a lot of records. The main menu should be based on a table with one record, like a preferences or interface table. Otherwise, you might start on a layout showing a found set of records with lots of fields, making your solution slow to open. You might think, "I'll use an open script that triggers on the first window opening to change layouts". Unfortunately, open scripts run after the default layout is loaded. The default layout is the one the developer had selected when he last closed the database in single-user mode. Instead, use the "Switch to layout" option in File Options. In the stack of things for FileMaker to do when opening a file, this option goes before open scripts and before the default layout is loaded. If you don't use this feature, you may be perplexed as to why your solution loads so slowly,as you will never see the default layout but it will load the record data.
SIDE TIP: I don't usually like main menus because they require the user to click several times to get where they are going (click to get to the main menu layout and click again to navigate). I also don't like providing navigation on every layout because one error can require repetitive work on every layout where the navigation appears. And, don't get me started on adaptive navigation uses global fields. While they can solve the problem of multiple layout changes, they can also slow down your solution significantly. I usually opt for a Custom menu approach to navigation since it allows changes solution wide and even supports keyboard commands.
Avoiding Speed Bumps Be stingy with your use of unstored calculations when designing a multi-user solution and especially when remote access is involved. Unstored calculations don't just exist in Manage Database, but in Conditional Formatting, Hide Object and Placeholder Text. The more results that need to be calculated upon each record navigation, the more sluggish a solution will feel. This is the very reason why creating reports with calculations and relationships is doomed for failure. It may work fine in a single-user solution with a few hundred records but over a network and with many more records, they often slow to a halt. It's best to use the tools provided by FileMaker which are subsummary parts and summary fields.
I remember a solution I was asked to fixed at a well known apparel company in Los Angeles. The client complained it was slow. When I got there, they showed we what was happening. Every time a particular layout was navigated, it took a good minute for it two draw. So, as usual, I duplicated the layout and started deleting objects till I narrowed down the issue to a single field. When I inspected the culprit calculation, it was a formula linked to a related calculation of a related calculation that summed a value in another table. Actually, it dove deeper down the relational tree but I don't have that much room here in this article.
You would probably never create a relationship of calculations like my example but almost the same speed degradation can occur by over taxing the calculation engine with unstored calculations. Stored calculations are not the issue, since they don't recalculate the result unless one of the referenced fields is modified. Instead, watch out for calculations based on global fields, summary fields, related fields or other unstored calculation fields since they will automatically be set to unstored and will recalculate every time the screen refreshes. As mentioned previously, this list also includes Conditional Formatting, Hide Object and Placeholder Text. I'm not saying don't use these features, just don't abuse them. Some of the biggest culprits include adaptive layouts, reporting without sub-summary parts, summary fields in browse mode and other general ignorance of FileMaker functionality.
Not all advice is as specific as avoiding unstored calculations. I have discovered that speed issues often occur when developers try to improve upon the FileMaker interface (FileMaker developers are always trying to make FileMaker look like iTunes). FileMaker is about FLF (Find, List, Form). First someone finds records, then they are displayed in list view and then the user clicks on a records to see the detail of the form view. Rinse and repeat! Ignoring the tools built into FileMaker not only lead to speed degradation but also difficulty in updating the solution at a later date. I'm not saying you shouldn't add some bells and whistles but choose wisely and always keep in mind the FileMaker fundamentals.
Remote Gotchas Access over a LAN is usually pretty good no matter what features are employed, if constructed with good practices in mind. When the user is remote, all of the data has to move across the internet. While a LAN has fairly consistent pipeline, a WAN is dependent on the state of the internet and your connection to the internet via WIFI or cellular. Therefore, design aimed at optimizing throughput is of the utmost importance for remotely accessed solutions.
SIDE TIP: In addition to be used remotely most of the time, hand held devices also have slower processors and other miniaturized hardware, preventing them from computing data as quickly as a desktop machine.
The first step is to determine how a solution will be used remotely. WAN users may only need limited functionality and perform routine tasks over and over. If this is the case, design a subset of the interface to accomplish these tasks only. It’s all about thinking about how your solution will be used and not just applying the same desktop functionality to an iPhone. Designing layouts specifically oriented towards the screen size of a mobile device is what developers always talk about but also consider what features really need to be present.
Another gotcha comes with popup menus. Depending on the number of items displaying in a drop-down list or pop-up menu, when it is built based on the contents of a field, you might have to wait a while the first time it displays. Value lists display a lot faster than in previous versions but this is still a consideration. I remember in past years, I often had to build pickers based on portals because they displayed much more quickly. With FileMaker 16, pickers are much more versatile with Window Cards. They take a little more work then a value list but can speed up list of values and even add additional functionality. Now, if you don't need the advantages of a picker, by all means, go with the simpler value list.
I can't go over every consideration for multi-user design but, in general, you have to be careful of techniques that only perform well in a LAN but not on a WAN. These techniques often come in the form of interface tricks that rely on calculations and/or relationships to change the interface based on interaction of the user. Many developers design completely adaptive interfaces. This can really tax the system on startup when the interface is being set as well as each time a new layout appears. I generally avoid these techniques, especially in a WAN environment. Speed is king!
Ports While we are talking about multi-user considerations, it is important to mention the port that needs to be opened to access a solution outside a firewall. If your solution is being hosted by a third party FileMaker hosting company, there is no need to worry about ports. If you are hosting the solution yourself from your office or colocating your server, port 5003 must be open in your firewall for remote access via FileMaker Pro, FileMaker Pro Advanced or FileMaker Go.
There are other ports that can be opened as well but they are unimportant for standard FileMaker access. Access to solutions using ODBC or JDBC or even access for the Admin Console (16000 and 16001) to configure FileMaker Server all require various ports to be opened.
Typing in the IP Address or domain for your FileMaker Server followed by 16000 takes you to the Admin Console start page from a remote machine:
Typing in the IP Address or domain for your FileMaker Server followed by 16001 takes you to the Admin Console from a local machine:
Add on "admin-console" and you will go directly to the Admin Console logon page:
Rather than discuss all the ports and their functionality, be familiar with port 5003 which allows FileMaker Pro or FileMaker Advanced to access FileMaker Server. For whatever it's worth, here's a listing of the ports and their use:
Web Deployment Access via a web browser is an entirely different consideration than every other multi-user scenario discussed. There are two methods for accessing FileMaker via a web browser: WebDirect and Custom Web Publishing (CWP).
Hosting Services One of the decisions that should be made before the development process begins is how the solution is going to be hosted. If the solution is single-user, there is no need for a server but most people create multi-user solutions. There are basically two choices: host the solution your self or have someone else host it for you.
If you host the solution yourself, you will need to purchase a license for FileMaker Server and install it on a dedicated server machine. Dedicating a server is not required but highly recommended to avoid conflict crashes. Crashing a guest to the database will not cause corruption, just a little data loss if a record is not committed. The same is not true for a FileMaker Server host. If a conflict occurs when one application butts heads with another application, a crash could occur, followed by possible corruption. Running a lean server cuts down on conflicts considerably which is why the best practice is to only run FileMaker Server on your server machine. In addition, don’t turn on file sharing, virus checking, screen saver or any other options you might think are necessary. All you need is FileMaker Server and the basic operating system.
Since setting up a FileMaker Server is expensive and complex, some people opt to have another company host the solution for them over the internet. Access to the data is noticeably slower over the internet but FileMaker Pro will cache data locally so as you use it the speed will improve. While there is no beating a LAN for speed, what if you need to have some or all users remotely access. The hosting service is most likely going to have a better pipe to the internet than your company. Suddenly a hosting service with it’s monthly fees looks pretty good. It’s all about determining how people will be accessing the solution and then comparing the price and headache of deploying FileMaker Server versus letting someone else do the work for you.
Don't forget the maintenance required to upkeep a machine that is crucial to your business. Determine if you really want to stay up on operating system requirements and FileMaker releases or if you want someone else to take care of the headache for you. An uninterruptible power supply (UPS) is also recommended in case the power goes out. And, when the power is on, you have energy costs. When you start adding everything up, the costs can be prohibitive for a small company with one solution. Larger companies likely already have the infrastructure in place.
FileMaker Cloud FileMaker Cloud is a new service from FileMaker, Inc., introduced in September, 2016. At the writing of this article, it is only available in the United States, Canada, Europe, Japan, and Australia and does not support PHP or XML driven web sites. FileMaker Cloud runs on Amazon Services so you know you are getting a good pipeline to the internet. It also can be quickly scaled for a growing business, at an additional cost of course. With the new licensing scheme introduced with FileMaker 15, it's definitely important to consider this option over third party hosting companies. While hosting companies can place as many solutions from as many different companies on a single iteration of FileMaker Server 14 and earlier, FileMaker 15 limits servers to solutions from a single organization. Another interesting option of FileMaker Cloud is the hourly versus annual pricing which could benefit light users greatly.
This blog is completely free. Please support it by clicking on one of the advertisers at the left side of the window. Thanks so much!
These are all extremely important factors. Thank you for highlighting. Recently I've had the need to access small local businesses entirely from mobile platform- using Google Maps. Obviously connectivity is paramount but so is having your service or product appear on a map, along with reviews and recommendations.