FileMaker: Explore

A little bit of SQL in FileMaker 12

by Chad Novotny on Apr 05, 2012, filed under:

One of my favorite new features of FileMaker Pro 12 is the ExecuteSQL function, which allows you to perform a SELECT SQL query against your FileMaker tables. ExecuteSQL allows you to use SQL to create calculations that otherwise might be very difficult or cumbersome. For example, in the past, if you wanted to get the list of values that had been used anywhere in the field areaCode in your contact table, you had to first create a value list using that field, then create the calculation: ValueListItems ( Get ( FileName ) ; “AreaCodes” ). It worked, but it was a bit of a hack to have to create that extra value list.

Now, all you need is:

ExecuteSQL ( "SELECT DISTINCT areaCode FROM contact" ; "" ; "" )
/* The last two empty parameters tell FileMaker to use 
    the default comma and carriage return as column and row separators */

Okay, so it does require learning a bit of SQL (check out the W3Schools’ SQL Tutorial). But many queries are straightforward, and using SQL this way can simplify the relationship graph.

Read more ►

Say I wanted to get all the area codes used in Massachusetts. Using the old value list method, I would need to create a field somewhere to store the abbreviation “MA”, create a relationship from that field to the contact::state field, and define my value list to use only related values starting from the table with the new field. With ExecuteSQL, all I need is to update the formula to:

ExecuteSQL ( "SELECT DISTINCT areaCode FROM contact 
               WHERE UPPER(state)=?" ; "" ; "" ; "MA" )
/* The fourth parameter is used to fill in the ? in the query.
    It could be a field instead of a text constant */

Here’s one of my favorites: often it’s useful to know the name of the base table (the ones define on the Tables tab of the Manage Database dialog) that is the source for a table occurrence (the tables that appear in the graph). You may have lots of table occurrences, named leads, students, personsOfInterest, relatives, and so one, but each is really an instance of the contact table. Outside the Manage Database dialog, any reference to a table is really a reference to a table occurrence (so Get ( LayoutTableName ) returns leads, for example, not contact).

In the past, it would be necessary to name each consistently to include the underlying base table (so contact_leads, contact_students, contact_personsOfInterest, etc.), and then parse the table occurrence name. Be careful changing table names! But with SQL, you can use the following to get the name of the base table of the current layout:

ExecuteSQL ( "SELECT BaseTableName FROM FileMaker_Tables 
               WHERE TableName=?" ; "" ; "" ; Get ( LayoutTableName ) )

FileMaker_Tables is a special table you can query via SQL to get a list of all table occurrences, and the BaseTableName column tells you the name of the source table.

You can replace Get ( LayoutTableName ) with any table occurrence name to get it’s base table. I like the following calculation field that stores the name of the table as data, which can be extremely useful when exporting and importing data:

Let ( [ 
FQFN = GetFieldName ( Self ) ;
tableOccurrence = GetValue ( Substitute ( FQFN ; "::" ; ¶ ) ; 1 )
] ; 
    
ExecuteSQL ( "SELECT BaseTableName FROM FileMaker_Tables
               WHERE TableName=?" ; "" ; "" ; tableOccurrence )
    
)

The two variables in the Let function first get the fully qualified field name (including table occurrence name) of the calculation, and then parses out just the table occurrence name for use in the query. Since the calculation never makes reference to anything but Self, you can copy and paste this calculation field to any table without needing any editing.

What’s your favorite use of ExecuteSQL?

0 comments

Contract post ▲

Upgrade to FileMaker 12 without Worries

by .(JavaScript must be enabled to view this email address) on Apr 05, 2012, filed under: Tips & Tricks, News, Reflections

This is my first week at The Support Group, and I get to kick it off with FileMaker 12!

FileMaker 12 is here and with it comes a new file format. However, this isn’t your father’s file format change (i.e. fp5 to fp7), full of architectural rewrites, modifying scripts, and changes in behavior. If planned for correctly, it is a fairly painless and worthwhile adventure, chalk full of performance increases and development possibilities (awesome layout themes, better and easier charting, more robust container field behavior and many more).

As with any file format change (whether it’s Word, Excel, or our FileMaker), you’ll need to be aware of some rules:

FileMaker Server v12 only works with v12 clients
FileMaker v12 clients only work with v12 server
Any FileMaker file (v7-v11) opened with v12 will be converted resulting in 2 files, both your old version and your shiny new v12

After you upgrade, give it some testing of course (this never hurts). Since you can’t run FileMaker Server 11 and Server 12 on the same machine, you may need to run separate servers while you test.

Follow these simple rules and you should have no issues, only joy. You can take my word for it, having myself converted and tested a 102 file, 8 GB solution that contains some really old school elements hauled all the way from FileMaker Pro 3 (remember when you had to copy and paste dates, because the set field script step wouldn’t work with dates? - yikes).

0 comments

The Royal Treatment

by .(JavaScript must be enabled to view this email address) on Feb 21, 2012, filed under: Success Stories

The Support Group Helps a National Entertainment Company Manage Royalty Reporting

What’s a well-known, national entertainment organization to do when they need a way to track the music for their many television programs? Turn to FileMaker Pro, of course.

Six years ago the company faced a significant challenge: how best to log all of the music “cues” that are used for their television shows? It is not unusual for a single episode to have anywhere from 30-50 different tracks; given the number of shows the company airs this can add up to an average of 10,000 cue sheets per quarter. The producers need to record the song usage not just for their own internal purposes but so that it can be accurately reported to performance-rights organizations such as ASCAP and BMI. It was clear they needed a database, so they approached The Support Group with their vision for building one. The result was a cue-tracking system that logs each cue sheet by program name, airdate, territory and show number, ultimately allowing each user to enter the list of related cues for each sheet, which consists of the song title, composer name, and publisher information. The collected data is then used to generate thousands of pdf files, which are then reported for royalty collection on a quarterly basis. The new reports are much more efficient and accurate than the old manually prepared ones, and generating them from the database requires considerably less man hours.

Read more ►

It was also the beginning of a long and successful working partnership. Over the years several members of The Support Group team have helped to contribute to the development of the system, most recently New York office Systems Engineer Kathryn Anderson. “Kathryn was a quick learner,” says the company’s Music Specialist, “and she fully understands every aspect of the system.” The client adds that it is sometimes hard for her to articulate exactly what she needs, but “I can tell Kathryn what I’m looking for and she’ll give me a few different options. We have a great working relationship.”

Both the system and the partnership have continued to evolve over the last 6 years. The structure has been updated, features have been added, and new entry controls have been implemented to help maintain the integrity of the data. Script changes were added to make the pdf conversion of their thousands of cue sheet reports a smoother process. Most recently the client began accessing the system on her iPad via FileMaker Go. It speaks a lot to the stability of the system that no additional programming was needed to make it iPad-ready; the client says she installed the application, opened up the system and “it works like the full version.” The client is the only administrator on the system and therefore the only person that can make high-level changes. Because most of the producers work off hours, it is not unusual for new music requests to come in at night or over the weekend. Being able to access the system on her iPad allows her to log the new changes right away from wherever she is.

In addition to Anderson, the client reports that all of the staff she’s worked with from The Support Group has been “responsive and respectful. I’ve never been disappointed with their results.” It is not unusual in her business for emergencies to crop up at the last minute, but according to her, The Support Group has never left us hanging. “Time is of the essence here,” she says, “but we always feel like we are a priority client for them.” She adds, “It’s amazing how I can just ask for 5 key things I’m looking to have implemented and then quickly see a result. I say it all the time - they are truly gifted.”

“Plus,” she adds, “they’re just nice people.”

Contract post ▲

Select Script Options by First Performing Them Manually

by .(JavaScript must be enabled to view this email address) on Jan 16, 2012, filed under: Tips & Tricks, General Tips

Sometimes, when writing a FileMaker script to do a find, sort, or export, doing it manually first can save you LOTS of time.

I was just tasked with creating a script to export all the fields on a layout. The layout, however, is non-trivial and contains fields from multiple different tables, and the relationship graph I inherited is very complicated and nearly impossible to decipher. It would have taken me HOURS to identify the table occurrence and field name of every field on this layout and then set up the export in a script.

What I did instead was first perform the export manually. When exporting manually, the Specify Field Order for Export dialog defaults to showing you all the fields on the current layout, including the related fields (when writing a script, the same dialog only allows you to choose each table, since there is no “current layout” for the script to use). I chose the Move All button to select all the fields on the layout, went back to editing the script, added the Export Records script step, and chose Specify export order. The last, just-performed export was already there by default. I was done in minutes.

You can use the same idea to speed up specifying a sort order (where you can again choose the current layout’s fields) or search (where it’s easier to find fields laid out on the screen, rather than hunting around for them in the Edit Find Request dialog).

0 comments

◄ Previous Entries

More Recent Entries ►

FileMaker: Explore

A little bit of SQL in FileMaker 12

Upgrade to FileMaker 12 without Worries

The Royal Treatment

The Support Group Helps a National Entertainment Company Manage Royalty Reporting

Select Script Options by First Performing Them Manually

Got Questions?

Log In

Categories

Most Recent Entries

Locations