Editor’s note: Today I’m pleased to present a guest article and accompanying demo file written by Joel Englander on using ExecuteSQL + a global multi-line key as an alternative to FileMaker’s built-in portal filtering mechanism, with dynamic portal sorting included as an added bonus.
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
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).
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 →
I’ve said before, and no doubt will say again, that one of my favorite things about this blog is how much I learn from your feedback and the demo files you send me.
Recently I received a file from Otmar Kramnis of the Hochschule Luzern demonstrating the fastest SQL-based method I have yet seen to solve the challenge we looked at in part 1 and in part 2, and with a few minor modifications, this is the demo we’re going to focus on today: Outer Join Demo 7
As you may recall, the aim is to show a week’s worth of daily sales totals for all employees whether they had any sales or not.
Or, to restate the problem in more generic terms: we need to show all values from table A, whether or not there are any matching values in table B. This is known as an “outer join”, or more precisely a “left outer join”, since we want to see all values in the “left” table (Employees), whether or not they have corresponding matches in the “right” table (Sales).
Disclaimer: This article contains speculative and experimental techniques that are in the proof-of-concept stage. Use at your own risk and test thoroughly.
Earlier this month I had the honor and the privilege to do a presentation on the topic of Radical Separation at the PauseOnError un-conference in Portland, Oregon, which included a demo file resembling this one: virtual-calcs-part-1-v2
Before the conference I posted a pseudo-F.A.Q. which included the following…
Q. What’s your experience with the Separation Model? A. I’ve used it heavily over the last seven years, for a variety of vertical market applications, custom projects and, recently, on a vertical market FMGo app.
Editor’s note: Today we have a guest article written by John Weinshel, whose knowledge of SQL in general, and FileMaker+SQL in particular, runs deep. John’s contributions in various online forums are always worth reading, and it’s a privilege to present his thoughts on this topic here.
The new ExecuteSQL() function in Filemaker 12 does not work dynamically (with the question mark) as expected with the IN function (nor with BETWEEN). For example, we might expect the following statement to return all the ID’s for contacts whose first name is either John, Mary, or Renee:
FROM Contacts c
WHERE c.fname IN ( ? )
""; ""; " 'John','Mary','Renee' "
…but it doesn’t. The following does work, but it’s not dynamic:
FROM Contacts c
WHERE c.fname IN ( 'John','Mary','Renee' )
There seem to be many questions about the usage of SQL (Structured Query Language) with the ExecuteSQL function in FileMaker 12. This tutorial attempts to explain some of the SQL terms, if you are new to writing SQL statements. Since there are already many examples of how to write the ExecuteSQL queries, links to these will be listed at the end of this article. If you don’t need to learn the terms, jump right to the Helpful Example Databases section, below. There you will find links to solutions that help you create and test your queries.
This is not a complete SQL guide, as other databases may use other syntax. This is not a complete FileMaker and SQL guide, as FileMaker may be an ODBC source and the SQL queries made against it may vary from the terms used by ExecuteSQL(). This is not a complete FileMaker and ESS guide using SQL calls (if using Import or Execute SQL script steps or ExecuteSQL() function or ESS). It may not have all the nuances needed for other data sources. This is the ExecuteSQL() function reference for which you’ve been waiting. The FileMaker 12 ODBC and JDBC Guide is helpful, but it has uses outside (and beyond) the ExecuteSQL() function. Any discrepancies between the reference and the function will be noted here, if possible.