Version: FM 9 or later – FileMakerHacks

27 Mar 2017: see User-Friendly Excel Exports, part 6 for an updated approach.

Well, we’ve reached what may well be the final posting in this extended series of articles. We’ve covered a lot of ground, and this article assumes familiarity with what has come before (in parts 1, 1.1, 2, 3 and 4).

Today we’re going to look at a couple “Virtual List” implementations of the User-Friendly Excel Export technique, which will, among other things, allow us to eliminate these calculated fields from the Contacts table…

…as well as the corresponding set of fields in Donations.

Bruce Robertson’s Virtual List technique has made a couple previous appearances on FileMaker Hacks…

…so I won’t rehash its merits in detail here, but if you aren’t familiar with it, I recommend you check out one or both of these earlier articles. Continue reading “User-Friendly Excel Exports, part 5” →

Level: Intermediate, Version: FM 9 or later

User-Friendly Excel Exports, part 4

March 19, 2012August 24, 2022 Kevin Frank

27 Mar 2017: see User-Friendly Excel Exports, part 6 for an updated approach.

Colleagues and family members are starting to express concern at my obsessive refusal to bring this series to a close. But we’re getting close. Really. In fact, today, we’re going to look at two cool tricks (and one work around) which are utilized in this article’s demo file, excel exports, part 4, but that could prove useful in a variety of other situations. They are:

Dwindling Value Lists
Using a variable rather than a hard-coded file reference as an import source
Adapting the translation table technique to work with Anchor/Buoy (or any other multiple-table-occurrence-group design philosophy)

Dwindling Value Lists

I’m just going to touch briefly on these today because they are fully deserving of their own topic, but the technique is ideally suited to the task at hand. We want the user to be able to flexibly pick “fields” (actually pseudo-fields, as per earlier articles in this series) to be exported, assigning them to columns A, B, C, etc.

Once a field has been assigned to a particular column, we really don’t need or want to see it in the list of choices any more, do we? Ideally the value list should “dwindle” (shrink) as items are chosen, to make things clearer for the user. (You will note that in the above screen shot, the items in columns A through E, having already been assigned, do not appear in the pop-up menu.)

Continue reading “User-Friendly Excel Exports, part 4” →

Level: Intermediate, Version: FM 9 or later

User-Friendly Excel Exports, part 3

March 8, 2012August 24, 2022 Kevin Frank

27 Mar 2017: see User-Friendly Excel Exports, part 6 for an updated approach.

So far in this series we’ve been looking at exporting data to Excel from a single table (Contacts). Today we’re going to extend the technique to encompass a second related table, Donations, and without too much trouble we can leverage existing work from earlier entries in this series (part 1, part 1.1 and part 2).

To avoid unnecessary repetition, this article assumes familiarity with the aforementioned predecessors, and you can follow along in today’s accompanying demo file, excel exports, part 3, if you are so inclined.

Continue reading “User-Friendly Excel Exports, part 3” →

Level: Intermediate, Version: FM 9 or later

User-Friendly Excel Exports, part 2

February 25, 2012August 24, 2022 Kevin Frank

27 Mar 2017: see User-Friendly Excel Exports, part 6 for an updated approach.

[Note: To avoid a huge amount of redundant repetition, this article assumes that you are familiar with part 1 and part 1.1 in this series.]

Today we’re going to take a look at streamlining the approach introduced in part 1 by reducing clutter on the Relationships Graph, going from this…

…to this, with no loss in functionality.

…and you can follow along in today’s demo file, excel exports, part 2, if you are so inclined. Continue reading “User-Friendly Excel Exports, part 2” →

Level: Intermediate, Version: FM 9 or later

User-Friendly Excel Exports, part 1.1

February 20, 2012August 24, 2022 Kevin Frank

Bruce Robertson emailed me off list with an improved version of my demo from part 1, excel exports, part 1.1, which I am sharing with his permission. The improvements are…

a) live preview fields showing the data to be exported.

b) an “Eval Error Tester” script to make sure the output code in the alias table is well-formed.

Thank you Bruce for making this available.

Level: Intermediate, Version: FM 9 or later

User-Friendly Excel Exports, part 1

February 20, 2012August 24, 2022 Kevin Frank

27 Mar 2017: see User-Friendly Excel Exports, part 6 for an updated approach.

Many end users appreciate FileMaker’s ability to export a found set of records in Excel format — it’s fast, straight-forward and empowering. One request that I have received from clients multiple times over the years is: make the exported field names in the top row of the spreadsheet “human-friendly”…

For example, instead of this:

…perhaps they would rather see this:

Another request has been to allow data from multiple source fields to be combined into single export columns, as per columns A and C here:

And a third request has been to provide an interface so that users can pick and choose fields to export, including (where appropriate) related fields, without having to navigate the complexity of the native FileMaker export dialog. Continue reading “User-Friendly Excel Exports, part 1” →

Level: Intermediate, Version: FM 9 or later

Conditional Invisibility, part 2

August 20, 2011February 7, 2014 Kevin Frank

One of my favorite aspects of FileMaker Pro is reporting, and I appreciate it when a client poses a reporting challenge, because the phrase “we can’t do that” is not in my vocabulary (except when used as an example of a phrase not in my vocabulary). But recently I was given a reporting request that momentarily tempted me to utter those forbidden words.

Background: the client gives away prizes. Each prize falls into a “category”, and each category has one or more “types”.

The request: For a given date range, display and summarize prizes by category, and by type within that category, with each category starting on a fresh page… so far so good, right? The hierarchy is Prize Category → Prize Type, and FileMaker developers can crank out reports like that in their sleep.

But then came the twist: show different columns of information depending on category and type. And yes, the client wanted this all in a single report.

Continue reading “Conditional Invisibility, part 2” →

Level: Beginner, Version: FM 9 or later

Conditional Invisibility, part 1

August 18, 2011August 24, 2022 Kevin Frank

I mentioned this technique in passing a few months ago (Portal Sorting, part 2), but today I would like to examine it in greater detail. Have you ever had a text object or a field that you wanted to selectively change based on some logical condition? For example, say you have a check box, and want the label next to the check box to change depending on whether the box is checked or not, like so:

In the old (pre-FileMaker 9) days, you could have used auto-enter or calculated field trickery, but now, thanks to the modern miracle of Conditional Formatting, you can make this happen at the layout level, rather than having to pollute your table schema.

Continue reading “Conditional Invisibility, part 1” →

Level: Intermediate, Version: FM 9 or later

Separation Aggregation Aggravation

May 29, 2011August 24, 2022 Kevin Frank

7 June 2015: This technique has been substantially revised to work with FileMaker 14; see FM 14: Separation Aggregation Aggravation revisited.

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 called 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). At the left, we are looking at a newly created parent record in the Data file, with a couple newly created portal rows, and the three aggregate calc fields below the portal are tracking the changes in real time. Continue reading “Separation Aggregation Aggravation” →

Level: Any, Version: FM 9 or later

Alternative Locations for Plug-Ins

December 4, 2010 Kevin Frank

The release of FileMaker Pro 9 in July 2007 introduced the ability for plug-ins to be stored in an “alternative” location. The traditional locations,

Macintosh HD/Applications/FileMaker Pro x/Extensions
and
C:Program FilesFileMakerFileMaker Pro xExtensions

…(with “x” representing the FileMaker Pro version number) are still valid, but the new locations are guaranteed to be writable, whereas OS-level security settings may prevent users from being able to install plug-ins in the traditional locations.

The alternative locations are:

Mac OS X
Macintosh HD/Users/[user]/Library/Application Support

Windows 7
C:Users[user]Local SettingsApplication DataFileMakerExtensions

Windows Vista
C:Users[user]AppDataLocalFileMakerExtensions

Windows XP
C:Documents and Settings[user]Local SettingsApplication DataFileMakerExtensions

Note: on the Windows platform, the Local Settings folder may be invisible. You can fix this by going to the “Folder Options” control panel and checking the “show hidden files and folders” option. Of course this will make all other hidden files and folders visible as well, so take that into consideration.