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.
File Maintenance The Save A Copy As feature is available in FileMaker Pro, Developer and Advanced (they've changed the name a lot over the years). This feature allows you to save a compacted copy but not like WinZip or Stuffit. A compacted copy is a duplication of the file compacted and optimized. FileMaker stores data in blocks and, when information is deleted, those blocks may become partially full. Compacting a file merges partial blocks, so there are fewer and fuller blocks -- thus creating a smaller file. Optimizing a file is a lot like optimizing your hard drive. Blocks are swapped so that data in records are grouped together. Scripts, Fields and other areas of FileMaker are also optimized to increase performance by allowing similar data to be accessed sequentially. The File Maintenance feature in FileMaker Developer 7 and FileMaker 8 Advanced simply splits these two tasks into separate features (this feature is long gone in modern versions of FileMaker). The big difference is that the File Maintenance feature works on the current file, so you don't have to swap out the old copy for the new one.
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.
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.
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!
Comments:
Mac
04/05/2022
Are you seriously suggesting that list view design should be driven by the constraints of the software not by the needs of the user and the efficiency requirements of their task? I can't agree. There is obviously a balance, but the benefits of a rich list view are enormous and you simply have to design your tables in such a way that performance meets requirements. If that means shadow tables then so be it, but telling the user they can't sort records is nonsense.
I'm suggesting that you have to be careful or you could get a slow list view. That's all. Sorting a list view can be done but it's typically a report function. If users want to sort list view, they probably are using a database as a spreadsheet. Point them to the find feature so they can properly use a database to filter the records they want to see rather than sorting the entire table and scrolling. This is a common misuse by users who have used a spreadsheet and aren't familiar with a database. Of course, there are always exceptions to every rule but I've found in my experience that most people don't need to sort a list view and when they find out about find, they realize how much more powerful and efficient it is.
Ed Berkel
10/08/2020
If you have several objects that use the same hide formula, group then and then hide the group. FileMaker only does the calculation once that way. (learned from Nick Hunter)
Thanks for sharing and you are indeed correct. A couple of downsides. If you ungroup those objects, the Hide Object formula is lost. FileMaker does warn you so you can copy the formula to the clipboard temporarily and reinstate it when you are done. In addition, some objects with the same formula may not be in close proximity, making it difficult to group them. Just some thoughts about why I don't usually group objects and then add a Hide Object formula to the group.
