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

Level: Any, Version: FM 8 or later

Logarithms I Have Known And Loved

Kevin Frank

They say you never forget your first time, especially if it’s your only time. Maybe that’s why my first (and, so far, only) logarithm stands out so vividly in my mind.

It was a quiet Thursday in October when the call came. A colleague was building a cat breeding database and wanted advice on how to solve a problem. If a given cat is assigned “position 1” in his or her family tree, the cat’s ancestors can be assigned tree positions like so:

2010-12-31-a

Additionally, each generation can be numbered as follows:

Continue reading “Logarithms I Have Known And Loved” →

Level: Any, Version: FM 11 or later

GetFieldName: New in FM 10, Improved in FM 11

Kevin Frank

Demo file: getfieldname – requires fm11 or later

When FileMaker introduced the Set Field By Name script step in version 10, they wisely included a complementary function, GetFieldName, to help prevent database breakage due to field renaming.

Brittle code:

Robust code:

This is the standard use for GetFieldName, and it’s a very good use… in fact, in FM 10, that was just about all you could do with it.

I had high hopes when I first heard about this function, because the ability of a field to know its own name presents some intriguing possibilities. Unfortunately in 10, GetFieldName(Self) did not resolve properly in unstored calculations, as per the highlighted field below.

The good news is that this shortcoming was fixed in FileMaker 11.

This means that for the first time, we have the ability to modify the output of a calculated field merely by renaming the field itself. And while I offer no apologies, I do ask the reader’s indulgence for what follows. We’re exploring a “proof of concept” involving the GetFieldName function, which is not necessarily the optimal solution to this particular reporting challenge. Still, I think it’s worth exploring.

Here’s an example of how we might take advantage of this new capability. Below is a table of tour bookings. Pax is tour-speak for “number of passengers”, and currently we’re looking at some March departures for three consecutive years.

Note the three rightmost columns: pax_2009, pax_2010 and pax_2011, which exist to help produce a report comparing three years side by side. These are calculated fields, and in the Dark Ages (i.e., FileMaker 10 and earlier), we would have defined these fields along these lines:

   pax_2009:  if ( year ( date_depart ) = 2009 ; pax ; "" )
   pax_2010:  if ( year ( date_depart ) = 2010 ; pax ; "" )
   pax_2011:  if ( year ( date_depart ) = 2011 ; pax ; "" )

But now in this enlightened (post-10) era, we can define all three fields identically as:

   Let ( [
      a = Year ( date_depart ) ;
      b = GetFieldName ( Self ) ;
      c = Right ( b ; 4 )
   ] ;
      If ( a = c ; pax ; "" )
   )   //   end let

And while we’re at it, let’s make sure the storage type for these fields is “unstored”. In a nutshell, each field will compare the rightmost four characters of its name against the year of the date in date_depart, and if they’re the same, the field will show the pax value; otherwise it will show nothing.

We’ve also defined three summary fields to total these three fields, which allows us to produce a comparison report, showing totals for last year, this year, and next year side by side.

What happens next year, when we want to increment each of our pax_YYYY fields by 1? We simply rename the fields (pax_2011 to pax_2012, pax_2010 to pax_2011, and pax_2009 to pax_2010). Give that a moment to sink in: we can now update our business logic by renaming fields.

What about the column labels in the report? Can we use GetFieldName to make them update automatically? The answer is a resounding yes. And we can use “merge variables” (another new-in-FM-11 feature) to help. Here’s our report in layout mode:

The year column labels are merge variables named $$year1, $$year2 and $$year3, and we could have populated them via script, but how boring would that be? Instead we use conditional formatting — not to format the labels, but to cause the merge variables to refresh so they always indicate the correct years.

The conditional formatting formula is basically the same for all three labels, so let’s just look at how we’ve applied it to $$year3:

To reiterate: no conditional formatting has been applied. We just wanted a way to “tickle” the merge variables on the report. And there’s even a bit more trickery perhaps worth drawing attention to: the Let statement is being used to update (or create) the $$year3 variable, and that’s all the Let statement is doing, which is why the actual calculation part of the statement is empty.

As far as I’m concerned, this use of conditional formatting  is taking “cleverness” a bit too far (given that a script runs to generate this report, why not just set the variables there?), and I intend to sternly reproach myself at the earliest convenient opportunity.

But using GetFieldName to bind $$year3 to the rightmost four characters of the pax_2011 field name, in such a way that renaming the field doesn’t break things? Nothing clever about that… that’s just common sense.