Category Archives: General

Resolved: My Very Own Twitter Impersonator


Dear Friends, Colleagues and Fellow FileMaker Enthusiasts,

You know the old saying that imitation is the sincerest form of flattery? Well, I’m here to tell you that having experienced it first hand, I’m not feeling even remotely flattered. Pissed off? Yes. Violated? Yes. But not flattered, and not amused. Here’s the short version:


1-6-2013 3-36-38 PM

The Impostor:

1-6-2013 3-38-08 PM

Here’s a longer version: Last year a colleague wrote to ask why I wasn’t using my logo on my Twitter postings. Me: “I don’t do Twitter.” Him: “Sure you do, I’m one of your followers!”

Continue reading

Fixed Width for EDI and Other Reporting

Editor’s note: Today it’s my pleasure to present a guest article written by Beverly Voth. Like many other developers, I have enjoyed and benefitted from her ongoing contributions to the FileMaker community.

I do a lot of text manipulation for EDI (Electronic data interchange – and plain text exports with fixed-width field data. Some varieties of EDI use XML, but this article is about plain text. EDI may or may not use the fixed-width format. Fixed-width reports may or may not use delimiters and various “padding” characters.

I created two FileMaker custom functions to help me calculate fixed-width and EDI text for export, and if you wish, you can follow along in today’s demo file, Fixed Width EDI.

Continue reading

Combinations and Pascal’s Triangle, part 2

Yesterday we looked at counting all possible combinations of four modifier keys: Alt, Control, Shift and Window (abbreviated A,C,S,W). Today, we’re going to look at a related problem: how many different ways can we combine two of those four keys?

Of course, when your total “set size” is only four, it’s not that hard to identify them (AC, AS, AW, CS, CW, & SW) and then count them (6). But why go to all that trouble when a modified version of Pascal’s Triangle can not only do the work for you, but answer the question for any set size, and any number of choices within that set.

Let’s start with the original and slice off the sections in red:


This gives us a simplified triangle, which we can use to answer the question I posed above: Continue reading

FileMaker’s Internal SQL Engine, part 2

When my kids were little, I sometimes found myself echoing that familiar parental refrain: “Just because you can do something, doesn’t mean you should.” And I think a related question can be asked with regard to FileMaker’s internal SQL engine: Given FileMaker’s almost infinite flexibility, why bother with SQL?

Three reasons come immediately to mind: 1) efficiency; 2) power; 3) SQL is the most widely used database language, and worth becoming familiar with. Number 3 is self-explanatory, and I’ll address #2 below, but what do I mean by #1? How is SQL more efficient? In a nutshell, it allows you manipulate data via text commands without having to “establish context” (by going to a particular layout or record), and also without having to add table occurrences or relationships to your Relationships Graph.

Let’s say I have a scheduling solution that ships with demo data. I want to ensure that all dates in the system are current, so that when the user looks at the calendar, they will see appointments for the current month, and dates for invoices, payments, purchases, etc., are contemporary as well. Now there’s no reason this can’t be handled the “traditional way”, but what a load of drudgery.

Go to layout
Show all records
Replace field contents of date field
(repeat as necessary for multiple date fields in a given table)
Go to another layout
Repeat ad nauseam…

You could easily end up producing a mountain of script steps to accomplish what could have instead been done with a single Set Variable script step:

…which contains multiple SQL calls.

Note: I am using the doSQL plug-in in these examples, but the code inside the parentheses would be the same, regardless of which SQL plug-in I chose to use.

A similar situation arises if we want to delete all test data prior to shipping a new version of our product. Sure, we could navigate to umpteen layouts, repeatedly issuing Delete All Records commands, but doesn’t this seem more elegant?

Incidentally, for a couple of the tables, rather than deleting all the records, I delete only the ones that meet the condition imposed by the WHERE clause. And of course the WHERE clause could be much more complex than the one I’m using here.

The two examples we’ve looked at demonstrate the economy of using internal SQL to accomplish tasks that would not pose particular challenges for the average FileMaker developer. But so far we haven’t waved a magic SQL wand to solve a difficult problem. I said in the second paragraph that “power” is one of the reasons to use internal SQL, so let’s look at a problem that is difficult to solve using traditional FileMaker methods. Not impossible, just difficult.

Consider a database with two tables, Donors and Donations, related in the usual manner. We would like to see who is donating during a given date range, how many times and how much.

Rather than using a standard summary report, we want to show the equivalent information inside a portal. So, we need an interface that will allow the user to enter a date range, and then see each unique donor, and the count and total dollar amout of donations made by the donor during the date range, like so:

As I say, it’s possible to do this using “pure” FileMaker, in fact I built this demo when FileMaker 7 was first released in 2004, but it took a fair amount of trial and error to get it working properly.

With internal SQL, on the other hand, this can be knocked out in a few easy steps.

1. Build a multiline key of donor ids for the specified date range.

2. Relate this key to the primary key in the Donors table, and base the portal on that relationship.

3. Define a calculated field in Donors to count the related donations for the specified date range.

4. Define another calculated field in Donors, to sum the related donation amounts for the specified date range.

That’s all there is to it, and if you don’t want to take the time to build it from scratch, you can download this demo: sql-summary-report-in-portal (requires either doSQL, or the SQL plug-in of your choice, but if you don’t use doSQL then obviously you’ll need to modify the plug-in calls accordingly).

Finally, the DateToSQL custom function in the above code samples, will be discussed in my next posting (and the Q custom function was explained here).