Experiences of a FileMaker Pro Database Developer

Loopy Windows

This might be obvious to many, but sometimes the obvious just isn’t so obvious especially if you are deep in your project.
I have a list of records that have 12 fields that I needed to split into 12 separate records within the same table. I wrote a script to loop through the original records and create 12 new records for each one of the originals.
I spawned a new window to create and dump the new records into, so I could loop through the original set of records. This worked fine in my testing, but for some reason I must have “found” the records for my test. I just didn’t realize I did.
If the original window is showing all records, any records you create in the newly spawned window will update the original window. I guess this makes sense since you are showing all. It just took me a while to figure out that this was the problem. The result, in my case, was an infinite loop.
Lesson learned: If you want to run a looping script on all records in a table, and this looping script creates new records in same table, MAKE SURE YOU DO A FIND FIRST. Even if that find is suppose to find all. Don’t use Show All.

Post to Twitter

Annoying Windows Flash

Ever notice when a record is refreshed there is an annoying flash when using FileMaker in Windows? The Refresh Window command only refreshes data. It does not redraw the screen. However, under Windows, whenever the data is refreshed an annoying flash occurs, especially when the window is not maximized. There are many situations where we need the data refreshed and updated, but there has been no way to prevent the flashing.

Good news, with FileMaker 11 the Freeze Window script step updates the screen without the flashing. Just add a Freeze Window script step whenever you need data refreshed within your scripting. As long as you do not need to Flush cached join results or Flush cached SQL data, then use Freeze Window instead of Refresh Window.

When a script ends that has a Freeze Window step in it, and a Refresh Window step was not used after the Freeze Window, FileMaker Pro automatically refreshes the screen. I find this quite interesting that this particular refresh window will not cause the window flashing. Here’s hoping that FileMaker will fix the flashing caused by the Refresh Window command in the near future.

Special thanks to Darren Terry, Jason DeLooze and Howard Schlossberg for helping me clarify, test and sort this out.

Post to Twitter

Unresolved (

Comic relief at xkcd.com

Deep in a calculation, we’ve all been there trying to find the missing left or right parenthesis, among other things. After racking our brains, we sometimes just need a little relief and some humor to break up the day. Check out other comic relief at xkcd.com.

Post to Twitter

Configuring FileMaker Server Cache

Fine tuning FileMaker Server cache can greatly improve performance in a large or heavily used solution. So, what is the optimal cache setting? I find that the general rule for setting cache is to set the RAM to the recommend setting that FileMaker Server suggests. I would leave the interval set at the default 1 minute, increasing the interval could leave you at a greater risk of loosing data.

Depending on what your solution is designed to do and how active the solution is, and whether you are writing or reading more data, you may be able to adjust the cache to tweak performance. Think of it this way, if you are writing more data by doing more edits or creating more records, then a smaller cache size may improve performance. Smaller chucks of data are written at a time. If you are reading more data, like running reports, then a larger cache size may improve performance since more data will be accessed from RAM instead of having to keep reading from disk.

In Windows Server 2003 or 2008, I recently confirmed that the memory usage on fmserver.exe is directly related to cache size. Whatever the cache is set to, that will be the minimum memory usage plus whatever extra ram is needed by FMS. The cache memory is reserved, regardless if it’s filled up or it’s empty. It kind of works like a RAM disk.

If you pop up Task Manager and click on the Processes tab and find fmserver.exe, watch the memory usage. Then go to the Admin Console, Configuration, Database Server, Databases and change the Cache size and click save. Watch the memory usage for fmserver.exe and you’ll see it change as you change the cache. There is need to restart FMS. Now you can tweak the cache setting until you find the optimum performance.

Post to Twitter

Improve Finds

When the constrain find option first came out, I was able to drastically speed up some search routines. Finds with exact field matches (==) were being used and it was SLOOOW when running on a table with over a million + records. Removing the exact field matches in the first search pass to find a subset of records, then adding a Constrain Find using the exact field match (==) to run on the subset of the records, greatly improved the performance of the search.

Post to Twitter

Prevent Error 101, Server-Side

When running a script to loop through records and you have the Exit after last checked, you get a 101 error message, record is missing. I “understand” this is supposedly expected behavior, however to me it’s an erroneous error since you have the box checked to exit after last. It’s kind of like “duh, I know the next record is missing, that’s why I’m exiting this loop.”
This is a problem when running the script server-side. The schedule indicates an error and the log viewer shows “FileMaker Script Error.” The script actually performed OK and was successful, but reported the 101 error and shows the error status.
I tried trapping for error 101 and then just exiting the script, but the error was still reported to the FMS log and displayed in the Schedule Window as “FileMaker Script Error.”
The only way around the error is to put a condition to check if you are on the last record, something like:
Exit Loop If [Get ( FoundCount ) = Get ( RecordNumber )]
Put this exit loop condition right before the Go To Next Record script step.

Post to Twitter

Data Interchange

Recently I’ve been working on exports from FileMaker that are being used to import into other systems. FileMaker is great at accepting and interpreting many formats, but not so true the other way around. I find it interesting how inflexible those other systems are.

When exporting data in CSV format, FileMaker puts quotes around the data in every field, including number fields. Excel seems to only quote data when there is a comma in the data. I was told by another developer of another database system that the CSV format I’m supplying is not in “standard CSV format.” But, there is NO industry standard CSV format and it’s acceptable to put quotes around the data of every field. In order to make the client happy and conform to their inflexible CSV import, I have to open and save the file in Excel before posting to their FTP site. An extra step that’s totally unnecessary.

I had similar experiences with tab delimited files exported from FileMaker. A client requested the files as pipe delimited, but tab would also be acceptable although not preferred. Most of the work I do is on a Mac, but I make sure its compatible across both platforms. I usually export in a Unicode format. However, I was told there were no carriage returns, so I exported as ASCII (dos) format. I was again told there are no carriage returns in the document and everything is in one long line. They also wanted the file saved with a txt extension instead of a tab extension stating tab was specific for Tableau. This is just not true. Although .tab may be an extension for Tableau, it’s not the only application associated with the extension.

The actual problem with the file is the issue with Carriage Returns (CR) and Line Feeds (LF). Older Macs use a Carriage Return and newer Macs use a Line Feed, like their Unix counterparts, including Linux. Windows requires both Carriage Return and Line Feed (CRLF). If you transfer the files via FTP in ASCII mode the conversion between platforms is supposed to be taken care automatically. This does not always work from my experience. I’m looking for a solution now and will post it once I have it. In the meantime, I’m exporting on a PC.

Although FileMaker is flexible with file formats it can import, many systems are not. Keep this in mind when exporting data for use in other systems.

Post to Twitter

Charting

FileMaker Pro version 11 has new charting options. These are nicely integrated directly in the layout options. For simple charts, FileMaker’s built-in options are a great choice.

I needed something a bit more powerful and interactive. My solution was to use Highcharts, interactive JavaScript charts. Here’s an example of a chart created from one of my FileMaker databases using Highcharts: Highchart Example.

By clicking the legend, you can turn the graphs on and off. You can also drag and select to zoom in on specific areas of the chart.

In order to integrate with FileMaker,  use the substitute function and building pieces of the chart using calculations formatted as html snippets. Next, build a master chart with variables that when processed with the substitute function, the variables are replaced with the html snippets. Click here for an example of the master chart that I used. You will need to view the source in order to see the html.

I dump the master chart into a global text field and then process that field with the substitute function. Use a separate substitute for each variable.

Make sure to check out the demo gallery to see all the charting options available.

Post to Twitter

Beware if you change a server-side script name

If you setup server-side scripts and everything is working fine. Then, for some reason, you rename the script, the server schedule will “loose” the script and an error will be generated saying it cannot be found.

This is so “unlike” FileMaker where everywhere else we can change layout names, script names, field names, etc. and we just “know” all their references are updated.

Post to Twitter

Plug-in Deactivated

Recently, the Scriptmaster plug-in deactivated itself and would no longer work. I tried checking off the plug-in to activate, but it said something like Java was not running. I thought the problem was due to a recent upgrade with Parallels on my Mac and the Windows version just wasn’t happy. I tried updating the plug-in, removing all other plug-ins and nothing seemed to work.

I’ve been using the Scriptmaster plug-in by 360 Works in order to help stitch together multiple PDF documents. Some PDFs are existing documents and some are generated from FileMaker Pro. I learned the technique from Matt Petrowsky’s Combing PDFs – The Blazing Fast Method. The process works great and is very quick. I also use 3 other plug-ins; Dacon’s Mailit! and FileFire Advanced and Troi’s Dialog plug-in.

A week later, the client’s machine had the same problem. Now I had to solve the issue. I worked with 360’s support and we took all the plug-ins out and then we were able to activate the Scriptmaster plug-in again. I swear I did the same thing and it didn’t work for me, but go figure, that’s the way it happens. After ScriptMaster was running fine again, I added each plug-in back, one at a time. Each time I would quit and restart FileMaker Pro just to make sure all the plug-ins worked and were activated. I was able to put all plug-ins back and everything worked again.

I’m not sure why this happened, a possible crash or something, and I’m not sure why taking these steps got Scriptmaster to work again. It’s possible there is some kind of FileMaker Plug-in preference that got corrupt and just performing these steps solved the issue.

Post to Twitter