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.
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.
Designing a single-user FileMaker solution for efficient display is pretty easy. If it works on your computer, it's likely to perform the same on any other computer. The trouble starts when you share a FileMaker solution with multiple people. The rules change. There's record locking for starters but I've covered that already in several articles on this web site. What I want to talk about today is designing an efficient form and list view. It's especially important when displaying single and multiple records on screen at the same time in a WAN environment.
Bread & Butter Form and list views are the bread and butter of interface. I wrote an article titled FLF which discusses the method by which FileMaker is designed to function. Perform a find, see a list view and then click on a record to view the form with all the detailed information. List view is just for identifying a record after a find. It shouldn't be loaded up up with tons of features, sort or perverted in any other way unless you really know what you are doing. It just needs the bare minimum of design to easily allow a user to identify the record they want to inspect in form view.
A lot of times, developers (myself included) will place related fields, unstored calculations (mostly in the form of Hide Object) and summary fields on list view layouts. Often, this is because a client, despite your warnings, insists on treating list view like a spreadsheet. Other times, we just make a mistake and notice the speed issues during the deployment phase. In this article, I'll discuss the most common mistakes in form and list view so as to design a more efficient layout.
Form view has a little more latitude since it's only showing one record at a time but you still need to be careful. Overloading a form view with every trick in the book can also create a sluggish environment. There are plenty of ways to get what you want if you engineer FileMaker smartly. We'll discuss some of these workarounds in the following sections but not every issue can be confronted. That's where good troubleshooting skills come into play. By process of elimination, you can always figure out what's causing the problem and then try to remedy it. The key is, isolating the issue first and then trying to fix it.
Overloaded Form View Typically, I don't see very many problems with form views. You're only showing one record but it can be overloaded with fields, calculations and summary fields. What works well locally or on a LAN might be exacerbated to a point of unusability on a WAN. It all depends on the connection, hardware and design elements. But, let's disregard the network and hardware for now since this is an article on design efficiency. We'll be covering hardware and networking in a sister article coming soon.
FYI: Here's an article from Claris covering the most common ways to optimize network efficiency. I will also be covering this in depth in the next article in this series.
How do you design a form view so it's a efficient? The most common thing I see slowing down a form view are unstored calculations which I talk about a bit in a PodCast titled The Ubiquitous Calculation Engine. Features like Hide Object, Conditional Formatting and Placeholder Text are awesome but they come at cost when abused. That's because they are inherently unstored and calculate everytime the screen refreshes.
FYI: Screen refresh occurs when navigating records, layouts and tabs.
I'm going to focus on Hide Object since it's the main suspect in this crime. Hide Object is one of my favorite features. It helps me hide buttons on blank portal rows, make fields appear when certain field content is entered and hide objects between find and browse mode. The problem is when this feature is abused. It's such a cool feature that it's hard not to abuse it. But, you need to be vigilant and consider how important every feature you implement or the display of records could be sluggish.
How many Hide Object features you can add to a layout all depends on the complexity of the formula and what other features are included on the layout. Do you have a bunch of portals? Do you have a lot of related fields from multiple relationships? Is the Hide Object formula looking through a relationship? Is related data coming from multiple hops away on the dependency tree? How complicated are the match field in the relationships shown on the layout? Are the relationship match fields based on complicated or unstored formulas?
The list goes on and on but notice most are in the area of relationships. Yes, relationships can be slow but usually only noticeable when they are overused on a single layout. It's the same idea with unstored calculations. You might get away with a dozen or two dozen unstored Hide Objects but they will eventually add up. How many unstored calculations depends on the actual formula. The more complicated the formula (and if it's based on related fields) the faster you reach your limit. Add to this the rest of the complexity on your layout (like related fields), your internet connection and hardware and you have a recipe for disaster if you don't design your layouts wisely.
BTW: Hide Object formulas like Get(WindowMode) = 1 to hide a button in find mode has basically no overhead. It's formulas that reference related fields that are the real problem. I always say to my students that relationships are the slowest thing in FileMaker.
I had an in-house client developer recently call me and ask me why their form view was so slow. It worked well on a LAN but sputtered on a WAN. After a few questions about their internet connection, I determined it wasn't likely the network speed. I connected to their database and visited layout mode. When I got there, I felt like I was entering a room for my own surprise birthday party with all those eyes staring at me. What I mean is, almost every object on the layout displayed the "eyeball" flag designating the Hide Object feature.
To make a long story short, we visited as many of the Hide Object formulas as possible. For starters, many of the buttons had the same formula which checked across a relationship for some information. Once wasn't taxing but ten times was. FileMaker doesn't cache the result of the formula and make it available to all the Hide Object calculation dialogs. You have to do this yourself! I suggested to the client to have the result of this formula placed in a global variable OnRecordLoad so the formula calculated once per record and the Hide Object could reference the global variable.
I also made some other suggestions in regards to other features that could be employed. This client was hiding buttons (which I love to do) until field content conditions were met. The problem was not a single Hide Object but the multiple Hide Object features that were implemented. In this case, I suggested the client could test for the conditions inside the script with a an IF statement and show a custom dialog when the conditions were not met. This enabled the unstored calculation to run in a script so the formulation occurred only when the button was clicked. It may not be as sexy as a button the shows and hides, but in this case, an alternative solution was necessary due to speed issues.
Tab Panes in a Tab Control object can defer unstored calculation formulation. Almost every single form view I design has a Tab Control because I like how they allow more information in a smaller space but also because unstored formulas don't calculate until they display on screen. It's the same as if they are on a different layout or off screen above or below the scrolled area. If an object isn't displaying, FileMaker doesn't calculate it. FileMaker is smart and efficient but only if you know how it thinks.
TIP: Popovers can also defer unstored calculation formulation till the Popover displays.
There are many other ways to make a layout perform efficiently. I'm just focusing on one of the areas I've found most troublesome for myself, my clients and students. If you don't know the efficiency culprit(s) then duplicate the layout and start removing pieces until it responds more efficiently. By process of elimination, you can then focus in on the objects that are causing the most slow down and remove them or analyze why they are so slow. With this regimented approach to troubleshooting, it's easy to isolate a problem and address it.
List View Efficiency In some ways, designing list views is more treacherous than form views. Form views generally have more screen real estate and therefore more objects to possibly slow down display. However, list views display multiple records at a time. If you have 25 records showing for your current window size then any feature that causes slow down is multiplied by 25. In other words, what works well on a form view may cause a slow display of records in list view due to the multiplier of many records.
So, list view has the same issues as form view. Again, related fields, summary fields and unstored calculations are the main culprits. I try to keep my list views as simple as possible and only include fields from the current table. Keep in mind that FileMaker loads the entire record so don't make your tables too wide. For example, if you have 200 fields in a table but are only displaying half a dozen on the list view layout, all 200 fields need to be downloaded from the server to display the 25 records.
If you notice a slow down, you might want to narrow your table with a one-to-one relationship. For example, if you have a notes field, you could easily create a table for notes and link it to the parent record using a simple one-to-one relationship. This will allow the record to download from the server much faster for a list view since it won't download any related data that isn't displayed on the layout. I don't usually find this to be a problem for remote users unless their connection is really poor. Therefore, I usually design without one-to-one relationships. But, it's important to have good information when troubleshooting speed issues. While the slow down might not be completely caused by the content from a notes field, in conjunction with the other elements on the layout, it could be that last grain of sand on the pile of objects and features that slow down your list view.
A more likely candidate for slowing down a list view are related fields. My solution is, if you don't need them, don't use them on a list view. The primary function of a list view is to identify which record you want to view in form view. If a related field is needed for identification then use it by all means, but you'll likely have to be more frugal in other areas of your list view design. It's all about choices. Choose wisely on a list view as it could very well cause issues in a WAN environment.
Hide Object formulas can make a list view draw like you have a dial-up 33k modem. Honestly, I'm not kidding. The combination of related fields, summary fields and the Hide Object feature can make a list view annoyingly slow over the network. Again, it's the same solution. Don't use too many tricks. How many is too many? It all depends on all the elements on your layout, your structure, the network, hardware and a variety of other factors. You just need to realize there is a limit. If you encounter a speed issue, simply troubleshoot by pulling the layout part as mentioned previously.
A lot of people also like to make a list view layout into a report by adding summary fields to the footer. Summary fields are for reports and not designed for browse mode. FileMaker is just friendly and doesn't stop you from doing ignorant stuff. Besides, you don't need those totals all the time so just make a separate report layout. Separating these functions may seem like a lot of extra work but it's really the correct design methodology. Besides, it doesn't take that long to create a report and your client or boss will be so much happier when he connects to the solution while on the road over a 4G connection to the internet.
Another thing is to avoid dashboards in favor of reports. So many clients have heard the "dashboard" buzzword and want their own. Dashboards are so easy to mess up and require a lot of design expertise to get right. On the other hand, reports are fairly easy to design and display quickly. It's all about designing the way FileMaker was created to work. The only time I see a live dashboard as a handy tool is when it's on a dedicated guest machine and displaying results on a screen for the whole department to see.
BTW: I'm not saying you can't design a good dashboard. I'm just saying it's very easy to design a dashboard incorrectly. If your customer really needs a dashboard, the investigate the proper way to do it.
Caching Why is list view faster once I've viewed the set of records already? The answer is simple. FileMaker caches data that is being used. If FileMaker reads data from the server, it goes into the memory on the local machine for speedy access. As new data comes into FileMaker, old data may be dumped to make room. It's the same concept as to why a long popup menu based on the contents of a table can be slow to display the first time but much faster the second and third times.
Education When clients come from a spreadsheet background, they are used to scrolling through a list to find what they want. Sometimes it's hard to break that habit but you have to try. Start by not allowing users to show all records or sort in list view. They really don't need either. Sorting is for reports and showing all records should be replaced with how to construct a proper find request. Once they understand how to find properly, they'll be happier with their ability to navigate a database.
Hope this Helps! Designing layouts that are efficient across all mediums takes years of experience. Hopefully this article shows you some of the most common downfalls with suggestions on how to fix them. If you have any design stories, I'd love to hear them in the comments below.