Note: Thank you to Colleen Hammersley for bringing this issue to my attention, and to Howard Schlossberg for proposing a clever and elegant work around that addresses the issue on the Windows platform. (We’ll also look at another fix that works cross-platform.)
Today we’re going to explore a windowing behavior change that was introduced in FileMaker 12. If your solutions display multiple on-screen windows, then you may at some point find yourself facing this issue.
Welcome back for a second dip into the developer bag of tips and tricks. A few of today’s entries assume familiarity with part 1, so if you haven’t already checked it out, I invite you to do so before proceeding.
We begin today with a couple ExecuteSQL tips.
Tip #1: Get to know COALESCE
The SQL Coalesce function takes a series of arguments and returns the first non-null result, like so:
COALESCE ( arg1 ; arg2 [; arg3 ; etc.] )
For example, given a table of customer data including these records…
Over the last few years I’ve been collecting miscellaneous tips and tricks with the thought of eventually aggregating them into a blog posting, and it appears that day has finally come. Ideally there will be something new and useful here for developers of all levels, from the neophyte to the most grizzled of FileMaker veterans.
We start off with a couple FM 14-specific tips.
Tip #1: Tool tips in layout mode
In layout mode in FileMaker 14 you can hover your mouse over any badge and an informative tool tip will display.
Tip #2: Shift-Return in the Script Workspace
Did you know you can add new line above the current line in the Script Workspace by pressing Shift-Return? It works with the standard Return key, as well as the numeric Enter key.
Before… line 1 is active, and the user is about to press Shift-Return
After… a new top line has been added, and existing entries have moved down to accommodate
I had a client request recently to toggle an image button to a second image on mouse over. Under normal circumstances, this would not be difficult to accomplish since you can assign one image to the Normal state, and a different image to the Hover state, like so.
However, if you go this route, you can’t use container fields as the source for the images, but instead must make static file selections, like so:
Since this button would be appearing on dozens of layouts throughout a 20+ file solution, the client wanted to be able to store the two images in container fields, so an authorized user could easily swap in new container images (in browse mode, not layout mode) and have the changes ripple through their entire solution.
Recently a colleague contacted me with some questions about making ExecuteSQL found-set aware, and I realized that while I had addressed aspects of the problem in various blog postings over a multiyear period (for example, here and here), I had not assembled my thoughts into one cohesive, up-to-date article.
Well, that shortcoming has now been addressed, and today we’re going to look at three demo files, all of which rely on the summary list field type (introduced in FM 13).
Editor’s note: Today it’s my honor and privilege to present an article by guest author Beverly Voth, whose contributions to the FileMaker community are numerous and much appreciated. It’s great to have her back here on FileMaker Hacks.
FileMaker 13 & 14 have some features that can be leveraged to allow onelayout to toggle between Form View & Table View and look differently! We will be using the Starter Solution, “Contacts”. Feel free to download the demo file (Contacts14.fmp12.zip) or you may start with a fresh copy of the database file or another Starter Solution and follow the steps below.
NEW to Starter Solutions? Select “New From Starter Solution” under the File menu. Click the “Contacts” icon and click on the “Choose” button. A dialog will come up with the “Save As” field filled in with the name of the database file you chose. Navigate to where you wish to save the file and click the “Save” button. This will open the file to the default layout and be ready to change.
The default layout for Contacts is “Contact Details”, is in Form View and shows two “tab-like” buttons called: Contact List & Contact Details. These are buttons that Switch you to different layouts. Notice how the placement is the same and there is a smooth transition between the two layouts. You may also notice that the view is “locked” to just allow View as List or View as Form (respectively). You cannot select other views on these two layouts.
Note: Interface file #1 requires FM 14 or later; interface file #2 works with FM 12 or later.
What do you get when you combine the Separation Model + FM 14 placeholder text + ExecuteSQL + a million-record table + a variable array + Get(CalculationRepetitionNumber) + the Mod and Ceiling functions + a couple custom functions, with blazingly fast (local, LAN and WAN) performance thrown in for good measure?
Provide a data-mining interface to query a million-record table (cc_transactions) containing 20 years’ worth of credit card transactions. The client wants to be able to pick a date via a calendar widget, and see transaction info for that date, summarized by card type, transaction type and region.
This is a separated solution, with cc_transactions living in a file called z_data.fmp12, and a specific requirement for this project is to not make any schema changes to the data file. The data-mining will take place in a separate interface file… or in this case, two interface files, since we’re going to look at two methods. Continue reading →
Four years ago I posted the original version of this article, exploring a technique that worked fine in FM 11, but that broke and required extensive work arounds in FM 12 and 13. What you’re about to read is a revision of the original article, showcasing a FM 14 method made possible by a new feature: placeholder text.
FileMaker developers take it for granted that calculated sub-total and total fields will update automatically when portal rows are created, edited or deleted. This becomes problematic when one a) uses the separation model, and b) either intentionally or unintentionally holds the parent and related child records open (uncommitted) while editing, but still expects to see these aggregates update in real time.
Today’s demo file is FM 14 Aggregates and Separation, and for demonstration purposes it has interface elements in both the Data file and the Interface file. Normally of course, in a separated solution, you wouldn’t have interface elements in the Data file (since that would defeat the purpose of separation). Here we are looking at a newly created parent record in the Data file, with a couple newly created portal rows, and the three calc fields below the portal are tracking the changes in real time. Continue reading →
“It is a riddle wrapped in a mystery inside an enigma but perhaps there is a key. The key is how you get the PDF into the container field, and which platform and FM version you use to accomplish this.”
If you work with digital images, you’ve probably noticed the trend towards using PDFs as an image file format. I doubt many of use could have foreseen this happening when the PDF format was introduced in 1993, but in many organizations PDF has become the standard file format for images.
The good news is that the GetThumbnail function now works with PDFs in container fields in FileMaker 14, and in this article I’m going to suggest a couple reasons why you might find this useful, and explore some nuances between how PDFs in container fields behave on the Mac vs. on the PC.
Today’s demo file, pdf thumbnails, is a 45Mb download, consisting of 32 PDF images (average size 160K, average dimensions 1160×880) as well as six stored PNG thumbnails of varying dimensions for each PDF. Continue reading →
15 May 2015: Demo and article have been updated to make the technique 100% portable.
This is a quick follow up to the Ralph Learmont technique I posted the other day, in response to a comment seeking a generic technique to de-duplicate from within a found set.
Clearly there are various ways to skin this particular cat, and I have a suspicion my initial attempt can be further improved upon, but using Ralph’s demo as a starting point here’s what I’ve come up with: De-Duplicate-A-Found-Set.zip
Bear in mind that the goal here is for the script to be 100% portable, i.e., you should be able to copy and paste this script into your solution and run it without any modification at all.