1. Pages:
  3. 1
  4. ...
  5. 24
  6. 25
  7. 26
  8. 27
  9. 28
  10. 29
  11. 30
  12. 31
Level: Intermediate, SQL, Version: FM 10 or later

Custom Functions for Internal SQL

Kevin Frank

Recently I’ve mentioned several custom functions that can make your life easier when working with FileMaker’s internal SQL engine (a.k.a. FQL). This article’s accompanying demo file, SQL Sandbox, includes some sample data, an interface for testing SQL code, a number of code examples, and the following four custom functions:

Q – wraps an expression in single quotes. I created this because I hate visually parsing single quotation marks. As I wrote last month,

...first I want digress for a moment to complain about discuss single quotes, a.k.a. apostrophes. They can be difficult to see when they appear adjacent to double quotes — I liken them to an annoying cloud of gnats — so to avoid confusion, I wrote a custom function which I call simply “Q”:

In a nutshell, this CF supplies the surrounding single quotation marks so we don’t have to type them or think about them, and ironically, the above image illustrates the problem perfectly. A single quote surrounded by double quotes is extremely difficult to parse visually. But once you’ve written the CF, you may never have to deal with this problem ever again (until you edit another developer’s SQL code at any rate).

Additionally, this CF escapes any internal single quotes or apostrophes by prepending another one… e.g., O'Malley becomes O''Malley (yes, this is a good thing if you don’t want SQL code to break… don’t worry: the extra apostrophe won’t show up in your data).

GFN – like GetFieldName(), but only returns the field name itself — essential if you want to protect your code from the hazards of field renaming. Note: since this CF uses the GetFieldName function, it requires FM 10 or later.

Guess which one breaks when someone decides to rename creditCardType to ccType, or idCustomer to idContact? On a related note, in addition to protecting you from ex post facto field renaming, GFN also prevents you from misspelling or entering non-existent field names in the first place.

Also, FQL has over 250 reserved words, including “date”, “action” and “group” — you can see the complete set of reserved words on pages 55–57 of the FileMaker 11 OBDC and JDBC Guide. If you’ve used a reserved word as a field name in your FM database, you normally have to remember to wrap that field name in double quotes… GFN does away with all that by automatically wrapping your field name in quotes for you. There’s no harm done if the field name isn’t a reserved word, and that way you don’t need to worry about it.

(I once wasted an hour debugging a SQL statement referencing a field named “group”, before realizing “group” was a reserved word. An hour of my life, gone forever. But it inspired me to add the quote wrapper to GFN, so something good came out of it.)

GFN’s protective quote wrapper will also deliver you from certain other evils, e.g., in FM 11 (with its newly-rewritten SQL parser), the necessity to escape field names if they begin with an underscore, e.g., “_pk_serial”.

DateToSQL – takes a date in a variety of input formats, and outputs it in this format: DATE '2011-02-28', which is apparently the only format that is acceptable to both the new (FM 11) SQL parser, and the SQL parser used in FM 10 and earlier versions.

The input formats are: US-style dates (MM/DD/YYYY), European-style dates (DD/MM/YYYY), and SQL-style dates (YYYY-MM-DD). For example, say you want to grab a list of foreign keys for all customers who have placed an order in the last 30 days…

Assuming today’s date is 26 March 2011, the final line will be rendered as DATE '2011-02-24' and the SQL gods will smile on you. It’s true, they will.

The preceding code examples have consisted of simple SELECT statements; here’s a more complex UPDATE statement, utilizing all three of the CFs we’ve looked at so far. The aim here is to update the tax rate on any unbilled or future-dated invoice line items, if the user changes the default sales tax rate in Settings (this is a single-user system, so I have the luxury of not having to worry about potential record locks by other users).

And finally, we come to a custom function I don’t actually need very often, but that sure comes in handy when I do…

FoundSetToSQL – the concept of a found set does not exist in SQL, but sometimes we need a SQL command to operate only on the records in our current found set. What to do? Well, it turns out that we can say, “Hey, SQL, only process records if the value in a certain field matches one of the values in this list: a,b,c,d,e,…”, and we can build that list from values in the current FileMaker found set. Take for example this found set of 14 records:

FoundSetToSQL ( customers::id ) will return the following…

'000940','000540','001443','001545','000458','000639',
'000794','001629','000048','000973','000735','001139',
'001224','001449'

…as one unbroken string of text. And this can be used by the SQL “IN” operator as part of a WHERE clause, as per this statement, which generates a list of unique cities in the found set.

Warning: FoundSetToSQL is a) not fast with large found sets, and b) subject to the standard 10,000 iteration recursion limit… you don’t want to point it at a found set with more than 10,000 records. There are various ways to work around these limitations, but that is a topic for another day (the second half of this article addresses ways to build a multiline key of values from the current found set).

In closing, I will risk stating the obvious: these custom functions are not the last word on the subject by any means (especially FoundSetToSQL). Feel free modify and extend them as you see fit, and as always, use at your own risk.

FoundSetToSQL ( customers::id )’000940′,’000540′,’001443′,’001545′,’000458′,’000639′,’000794′,’001629′,’000048′,’000973′,’000735′,’001139′,’001224′,’001449′
General, Level: Intermediate, SQL, Version: FM 8 or later

FileMaker’s Internal SQL Engine, part 2

Kevin Frank

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).

Level: Intermediate, SQL, Version: FM 8 or later

FileMaker’s Internal SQL Engine, part 1

Kevin Frank

Recently a client asked me if I could help her produce a somewhat unusual report. Normally reports are created to summarize, aggregate or otherwise analyze data; in this case she wanted to report on the database itself. Specifically, she wanted to see all the tables in her database, along with current record count and most recent modification timestamp per table. Ultimately we decided that a simple table view — rather than a formal report — was all she actually needed.

So, I created a new table, “tables”, and populated the table_name field manually, by typing in the table names. You might think that FileMaker’s “TableNames” function could help here, but the function actually returns table occurrence names, rather than table names. Once that was done, how to populate the other two fields?

The thought of creating 50 cartesian join relationships from my “tables” table to the other tables in the system wasn’t particularly appealing. Equally unappealing was the thought of the scripted convolutions I would need to go through to populate the table, though perhaps I would be able to use Evaluate() or GetField() to simplify things.

But fortunately we were using the MonkeyBread plug-in on this project, so another option readily presented itself. Why not use SQL instead? This would allow us to access data in any table without needing to create any additional relationships. So we did. To populate the record_count field was simply a matter of issuing this replace command:

MBS ( "FM.ExecuteSQL" ;
"SELECT COUNT ( * )
FROM " & Tables::table_name
)

In other words, count the records for each table. What about the most recent modification timestamp? That turned out to be a piece of cake also. Here’s the replace calculation for most_recent_ts:

GetValue (
MBS ( "FM.ExecuteSQL" ;
"SELECT zl_modification_ts
FROM " & Tables::table_name &
" ORDER BY zl_modification_ts DESC"
)
; 1 )

In plain English, for each table sort all the records by timestamp in descending order, and then grab just the first one (i.e., the most recent). The above syntax works, incidentally, because the modification timestamp field in every table is named zl_modification_ts. Consistency. It’s a good thing.

Unfortunately, FileMaker’s SQL implementation does not include the SQL “Limit” function, hence the use of GetValue above. But this illustrates a useful lesson — there’s nothing wrong with combining FileMaker syntax and SQL syntax… use whichever one makes the most sense at the moment.

Incidentally, the portions of code shown in grey above are specific to the MonkeyBread plug-in. If we used a different plug-in, the grey portion would be different, but the remainder of the code would not change. That’s because the numerous plug-ins that allow you to tap into FileMaker’s internal SQL engine are really just providing a conduit for your SQL code.

I’ll have more to say about FileMaker Internal SQL in upcoming postings. (For that matter, I also wrote about it the other day.) In the mean time…

Suggested reading:
1. FileMaker 11 OBDC and JDBC Guide (chapter 7: “Supported Standards”)
2. Wikipedia SQL Entry

Links to some of the SQL plug-in vendors (to the best of my knowledge, these are the only ones that are fully FM11-compliant):
1. MyFMButler (doSQL)
2. CNS (MMQuery)
3. Dracoventions (SQL Runner)

Level: Intermediate, Version: FM 10 or later

Filtered Relations, part 4

Kevin Frank

In parts 1, 2 and 3 of this series, we looked at various methods for dealing with a relational challenge (see the first two paragraphs of part 3 for a succinct description). My aim was to explore some outside-the-box ways to generate filtered totals while respecting the normalized data structure of the three tables.

However if WAN performance is a primary consideration, then denormalizing the data model by redundantly storing invoice dates in the Line Items table is worth considering. (You can follow along in this article’s demo file, filtered-relations-04, if you are so inclined.)

a) create a new relationship from Products to Line Items using the following predicates…

b) define a calculated field in Products to sum across this relationship.

Continue reading “Filtered Relations, part 4” →

Level: Intermediate, SQL, Version: FM 8 or later

Filtered Relations, part 3

Kevin Frank

Today we’re going to take a third look at the challenge we encountered in part 1 of this series: Given a simple database consisting of Products, Invoices and Invoice Line Items, how can we show total sales per product filtered by date range?

Reminder: what makes this problem challenging is the fact that the date field lives in the Invoices table, but the product foreign key (fk_product) lives in the Line Items table.

To briefly recap, we solved this problem in part 1 at the cost of adding a couple fields and table occurrences, i.e., by leveraging the relational model. And in part 2 we solved this problem by displaying a related summary field inside a one-row filtered portal. Continue reading “Filtered Relations, part 3” →

Level: Intermediate, Version: FM 11 or later

Filtered Relations, part 2

Kevin Frank

Today we’re going to take another look at the challenge we encountered in part 1 of this series. The challenge is: given a simple database consisting of Products, Invoices and Invoice Line Items, how can we show total sales per product filtered by date range?

One thing is certain: assuming a normalized data model (with the date in the Invoices table), some sort of trick will be required. In part 1, we leveraged the FileMaker relational model to solve this problem, by adding additional table occurrences to the Relationships Graph, as well as some calculated fields in the products table. Continue reading “Filtered Relations, part 2” →

Level: Intermediate, Version: FM 10 or later

Filtered Relations, part 1

Kevin Frank

[I want to start out by expressing major thanks to Jason DeLooze, who graciously read an early draft of this article, and with some outside-the-box brainstorming (a specialty of his), transformed my sluggish approach into a blazing speed demon.]

Many of the relationships we define in FileMaker are predictable and mundane. A field in table A is linked to a field in table B via an equijoin (=) operator. We do it every day and we’ve done it countless times. Of course we often define more complex relationships as well: ones with multiple predicates and/or more esoteric operators than the venerable equijoin.

Even in this (FM 11) era of filtered portals, we solve many design and business logic problems relationally. However, once in a while a challenge crops up that seems like it should be solvable using the FileMaker relational model, but that frustrates our initial attempts to do so. For example, let’s look at three tables from a sales database:

On our Products list, we’d like to be able to set a date range in the header, and then see cumulative sales for each product during that period. Continue reading “Filtered Relations, part 1” →

Level: Beginner, Version: FM 11 or later

Manage Layouts… really

Kevin Frank

With the release of FileMaker 10, the “Set Layout Order” command, which had quietly lived under the Layouts menu for countless generations (only accessible from layout mode), was moved from its comfortable home and given a new name: Manage Layouts

One advantage of this change, was that the command could be accessed from any mode, rather than just when in layout mode. But apart from that, no additional functionality was added above and beyond what had been available in FileMaker 9.

The good news is that Manage Layouts has become much more useful in FileMaker 11, and now, truly does what its name implies, as per the buttons along the bottom of the dialog.

My favorite of these by far, incidentally, is the ability to highlight one or more layouts and then open each of them in a new window. I find this to be a huge time saver.

Another improvement worth noting is that there is now a keyboard shortcut to invoke Manage Layouts: Ctrl+Shift+L on Windows and Cmd+Shift+L on the Mac.

Level: Beginner, Version: FM 8 or later

Dude, that code is sooooo FM3

Kevin Frank

Recently I saw some code that brought nostalgic tears to my eyes. The goal was to parse the file name from a reference in a container field called, appropriately enough, photo_ref. Here’s an example of what the data viewer showed when pointed at that field:

image:/C:/Client/XYZ Corp/photos/andrew wigan.jpeg

And this is the code the developer had written:

Middle (
   Photos::photo_ref ;
   Position ( Photos::photo_ref ; "/" ; 1 ;
      PatternCount ( Photos::photo_ref ; "/" ) ) + 1 ;
   99999
)

In a nutshell: count the slashes, and then retrieve everything to the right of the final slash. Here in a FileMaker 11 solution was code that could have been written in 1995.

To his credit, the code correctly returned “andrew wigan.jpeg”, but I had to wonder whether the developer was aware that there were several things he could have done to make his life easier (and his code more readable).

First, he could have simplified the code by using Let() to eliminate the multiple references to “Photos::photo_ref”.

Let ( a = Photos::photo_ref ;
Middle (
   a ;
   Position ( a ; "/" ; 1 ; PatternCount ( a ; "/" ) ) + 1 ;
   99999
)
)   //   end let

He could also have moved a few more things up into the Let portion of the calc.

Let ( [
a = Photos::photo_ref ;
b = PatternCount ( a ; "/" ) ;
c = Position ( a ; "/" ; 1 ; b ) + 1
] ;
   Middle ( a ; c ; 99999 )
)   //   end let

I find that to be a heck of a lot more readable than the code we started with. However, there’s a different approach that could be used to solve this problem, which strikes me as being both easier to understand and more elegant.

Let ( [
a = Photos::photo_ref ;
b = Substitute ( a ; "/" ; ¶ ) ;
c = ValueCount ( b )
] ;
   GetValue ( b ; c )
)   //   end let

In other words, convert the reference to a list, by transforming the slashes into hard returns, and then grab the bottom value from that list. Once you get comfortable with this technique, you will find many situations where it comes in handy.

For example, if you use the GetFieldName() function, you know that it returns both the table occurrence name as well as the field name itself, separated by “::” like so:

Invoice::id_customer

What if you just want to extract just the field name? You can use a simplified version of the technique we just finished discussing:

Let ( [
a = GetFieldName ( Invoice::id_customer ) ;
b = Substitute ( a ; "::" ; ¶ )
] ;
   GetValue ( b ; 2 )
) // end let

…and the result is “id_customer”.