A couple days ago I received a package from April L’Assange. She is (in her own words) “currently not working for FileMaker Inc.”, but she had something I might be interested in. In the package I found a DVD with screenshots, sample files and a program, supposedly FileMaker 12 in alpha status. The files I saw were really exciting. Some of the things I found are described here. I might add more soon.

New File Format

First the bad news. I could not open the sample files (extension fpc) with my current FileMaker version. It looks like the file format will change with the new version; understandable with all the new features that were added to the program.

On the other side I was able to open fp7 files. A dialog popped up, asking my if I like to convert the file. Press OK and the database will be converted into a fpc file.

Manage Database

Developer Access

Before FileMaker 12 you had to go to the actual file to manage the fields and tables in this file. Now you can manage the database from all related FileMaker files as long as they are defined as external data sources and you have full access to the current and the related files.

This saves a lot of time when you manage bigger projects, especially when you try to separate data from layouts.

Table Comment

A minor new feature are table comments. You can add a comment to your table (similar to comments for fields), allowing better documentation of your database.

Field Alias

Each field may have an alias. There are different reasons why and when to use an alias. For example:

  • The alias is used as the field label when the field is added to an layout. Without an alias the original field name is used.
  • In import/export and sort dialogs you may use alias instead of field names. In case you use cryptic field names with pre- suffixes (_uc_TestSample_limit_n) alias are a great help. The common user will see a more readable name like “Sample Qty” and, if exported into Excel, the column will have the same name.

In privilege sets you can set the option whether field names, aliases, or both are accessible. This will definitely help an inexperienced user to select the right field in the sort dialog.

With this feature comes a the function GetFieldAlias( field ), returning the alias assigned to the field. There is no function like GetFieldNameByAlias. One reason might be, you can assign the same alias to different fields.

Field and Table related Script Trigger

You can assign script triggers directly to a table or field. These triggers do not depend on a layout. You might know this type of behavior if you worked with zippscript or similar plug-ins in the past. For example, a trigger OnRecordSave assigned to the table will fire whenever the record is saved.

Relationships

Search in Relationship Objects

A feature I was waiting for made it finally into the relationships tab register. You can search now for table or table occurrence names, field names used in relationships and text elements. Found objects are marked with a red dot in front of them (color can be changed). You can jump easily to the next found object with keyboard short-cut.

Settings in Search Option allow to search everywhere or just in specific objects (table names, table occurrences, field names, …).

Global Variables in Relationships

An amazing new feature is the use of global variables in relationships. They work like global fields but are not assigned to a specific table.

They can be used as stand-alone relationship (global variable to table; 2nd button) or within the relation definition between to table objects.

Grid and Guides

Not a particular important feature but it helps to keep some order in the relationship graph: the grid. Similar to the grid in layouts, all objects will snap to the grid when this option is activated. You can even define the grid size.

Like in layout mode you can now insert t-square lines. Also, when you move an object with the mouse, guide lines of object directly above, below, to the left and right will be show. The current object will snap to these lines, making it easy to align objects to each other. If you worked with Apple’s Interface Builder you might recognize this feature.

Information Button

A new [i] button (5th of the tool buttons) gives you interesting internal information about an object. Select a table occurrence and click the [i] button. A new window will show you source table, data source, and file access protection. All this was already available when you hover with the mouse over the little arrow in the top left corner of the table object. But the new window shows more information:

  • Scripts using this table occurrence
  • Associated layouts
  • Field definitions and aliases used in the relationship
  • Related value lists

From here you can jump directly to the related dialog window, but it will close the Manage Database dialog.

As I understand, this information feature shall be available in almost everywhere in FileMaker (Inspector, Calculation Dialog, …). Still, I had problems to find it except in the Relationship Diagram.

SQL API

The SQL API is now full integrated into the FileMaker environment. Previously you needed a plug-in like FMSQL or DoSQL to access the SQL API. You can now run a SQL-query with the function Sql. For example, to get a list of client ids with open projects, you may run this function call:

sql( "SELECT id FROM Client WHERE projectStatus = 'open'" )

The Truncate Table command in SQL will now delete a table quickly. It will not erase each record individually but drop the table definition. It will also drop any related index. There is also a new script command that does the same.

The SQL API supports views as well (see next).

Views

A new, experimental feature are views. Views are subsets of tables, limiting the accessible fields and also filtering the records from this table. Views are very helpful in different situations, for example:

  • Limiting the access of fields available for the common user for export/import.
  • Programming multi-client capable databases. A view will show only the records for the related client. Searches and other functions like total record number will be based on the view, not on the actual table.

Value Lists

Based on …

A value list may now be based on global variables, global fields, or unstored calculations. If you use any of these in a value list definition, then FileMaker will display a warning dialog, that the value list cannot be indexed at this time. This is a similar message as before, but now you can actual proceed and still use the value list. Accessing the value list may take a little bit more time, but with a global field that contains only ten items, this delay is not recognizable.

Optional Sort

The previous automatic sort feature for value list is now optional. You may decide if and when the value list will be sorted. And it is possible to use a column or expression for the sort command that is not shown in the value list output. Multi-column view (see below) allows other sort options.

Multiple Columns

Instead of only two columns, you can define up to six columns for a value list. You can either define a separator text (e. g. space, tab, semicolon, …) between column entries or check the list view option. In list view, the user can click on any column header to change the sort order.

It is still the value from the first column that is returned as the result of the value list. Fortunately, the first column does not have to be visible.

Value List related Field Format

For fields using value lists is a new formatting option available: Format from Value List. This will display the related content that is shown in the value list instead of the actual assigned value.

For example, you want to select a customer, showing the names of the customer in the value list. When the user clicks a name, than the customer id from the hidden first column is put into the field. For internal use you will need this customer id, but the user is confused to see am id instead of the customer name he just selected. In the past you would place a second field over the field with the customer id to display the customer name. This is not necessary with the new formatting option.

Data Viewer

Some changes happened to the Data Viewer. First, you can change the size of the expression and result field. Just place the mouse in the middle of the gray bar between these two fields and drag it up or down.


Second, you can save your expression as code templates and recall them any time. The templates are stored either in the folder Extensions/Template of your FileMaker program or in the current database. This is a nice feature for any developer who has already a collection of standard or complex calculations. There is also an import function (not working yet), supposedly to download expressions from the Internet. My only question, why is this feature not implemented in every function expression dialog?

New Layout Objects

Horizontal Portal

You already know, repeating fields can be displayed vertical or horizontal in a layout. The same feature is now available for portals. This is an interesting feature, for example to create an image gallery. And it offers new possibilities to create your own flexible menu system. Of course, the width for each record has to be the same, as it applies to the height for the vertical portal. But sorting and filtering is still possible with a horizontal portal.

Layout Viewer

You know the web viewer. Now meet the layout viewer. It is like a window to a different layout. And there is no relation between the layout table and the layout viewer table required.

For example, create a layout named Header with a custom menu, that you apply to every layout. Actual, every layout will have just the layout viewer object, showing the related layout Header. If you have to do some changes in the layout Header, you do it only once in the original layout. All other layouts will display these changes.

Or you like to have a main layout that displays information from different tables. As said before, there is no relation required between between the tables in Main Layout, Layout 1, Layout 2, and Layout 3. Actual, if you like to have any reference between the layout viewers, you have to script them yourself. For example, Layout 1 may show a customer list. Selecting a customer here will not automatically show invoices related to this customer in Layout 2. But you could write a script that would do exactly that.

Another nice thing with layout viewers, you can change the layout displayed in a viewer during run-time. This allows to use individual setups for each user or under different circumstances.

Web Viewer

The web viewer itself is not new. But it is now possible to exchange information between FileMaker and the web viewer content (in both directions). A special JavaScript command run inside the web viewer page can trigger a script in FileMaker (OnWebViewerAction).

Conditional Formatting

More Formatting Options

FileMaker extended the object attributes that can be modified with conditional formatting:

  • Fill and border color and pattern, including transparency
  • Cursor style (arrow, hand, …), great to ‘hide’ invisible buttons
  • Field entry (browse or find mode; whether user can enter the field)

The number of objects you can apply conditional formats has increased. This includes tab register, portal, rectangle, rounded rectangle, …

Formatting Class

FileMaker also introduces Formatting Classes. With this feature you can define different format settings to a class and apply this class to a condition in the Conditional Formatting dialog.

Search Mode

It is now possible to use advanced search option, allowing you to use expressions. Calculations start with a colon, followed by the expression. Use Self to reference the current field.

For example:

  • Search for content that is longer then ten characters → :Length( Self ) > 10
  • Search for content that is smaller then field Limit → :Self < Table::Limit

Scripts

Increased Length for Script Names

Finally, the limit of 100 characters for script names is extended to 250 characters. You might think, 100 characters are long enough. But sometimes, when I use descriptive script names and include required and optional script parameters, 100 characters were not enough. I appreciate the new length for script names.

Search within Scripts

Some developers like to have a free-text script editor. This is still not implemented in FileMaker 12. But we got another useful feature. You can search now within a script. You can search for any commands (“New Record”) or any parameter used with the script command (“$index” or “invoice::printDate”).

Found results are highlighted, the line gets a light gray background. With keyboard short-cuts you move to the next/previous occurrence.

Another interesting feature, particular for beginners is the instant script command help. When you click a script command in the list on the left side, then a description of that command is shown in the lower right section. When you click a command in the current script, then the lower section will display the script command option again.

New Script Commands

Import / Export Records – Not new, but both commands include new features. See paragraph below.

Perform Script by Name – Run a script by using its name.

Script Trigger Control – Trigger Management. See section Script Trigger.

Truncate Table – Drops table definition and related indexes.

Import / Export Records

Calculation on the Fly

It is now possible to apply simple calculation during the import/export. For example, you can use the Upper function before the value is imported into the table field. Or you extract the hour information from a timestamp, substitute certain characters from a string, divide a cent amount by 100 to receive a Dollar value, and so on.

It is possible to combine two fields from the source table, e. g. putting date and time together and import it into a timestamp field. An import field can be assigned multiple times. This might be necessary when you import a single timestamp field into a date field and a time field respectively.

Text-based Import / Export Definition

The definition for the import/export command can be a text, allowing you to put the definition together during run-time and pass it to the import/export command. (Do not worry, the traditional way is still available.)

Using a text for the definition gives you much more flexibility over the import/export process. You can limit the fields that are used, create different calculations (see above), or change the import/export field order.

You can create conditions like

Case( gender = 0; "?"; 1; "F"; 2; "M" )

or

If( contactType = "vendor"; vendorID; custID )

The following example shows the field definition for an import command:

street	{{2}}
city	{{3}}
zip	{{8}}
state	{{9}}
country	If {{4}}="US"; "USA"; {{4}} )

On the left side is the field name of the target table followed by a tab. Everything behind the tab till the end of the line is an expression used for the import. {{2}} is a reference to the second field in the source file. The last line uses a simple calculation: If the fourth field has the value “US”, it is replaced with “USA”, otherwise the original field is used.

If the field names from the source file are know, they can be used in the expression. And, as the previous example already implied, it is possible to import constant values. And worth to mention, local and global variables can be used too.

test	$testname
tester	{{tester}}
test date	{{test_start}}
duration	{{test_end}} – {{test_start}}
sex	Case( {{gender}} = "male"; "M"; "female"; "F"; "?" )

This example imports data from an Excel file where the columns have names. You can use names {{gender}} or column numbers {{2}}.

The text definition will be registered internally before the script command is executed. An extensive use of calculations in the import definition will increase the import time.

Script Trigger

I already talked about field and table based script triggers. You define them in the Manage Database dialog. The following script triggers can be used:

Trigger based on fields: OnFieldEnter, OnFieldKeystroke, OnFieldModify, OnFieldValidate, OnFieldSave, OnFieldExit

Trigger based on record/table: OnRecordLoad, OnRecordCommit, OnRecordRevert, OnExport, OnImport

The last two triggers (OnExport, OnImport) are new. The former trigger will be executed immediately before an export, the later right after an import. Other new script triggers are: OnMouseIn, OnMouseOut (mouse inside/outside layout object), OnWebViewerAction, OnWindowResize

Trigger Control

With the new script command Script Trigger Control you can turn script trigger on or off. The control works within the whole module (file). If trigger control is turned off, no trigger will fire. This can be helpful to avoid recursive firing triggers.

There is another new feature regarding trigger control. Some script commands have an additional option No Trigger (e. g. script command New Window or Go to Layout. If the option is active, no trigger will fire when this command is executed. Take the command New Window for example. This command will open a new window with the current layout. Usually you will switch to a different layout with the next script step. But because the current layout might have a layout trigger OnLayoutEnter, the assigned script would run first. With the new option, this trigger will not fire when the new window opens.

Trigger Function

FileMaker added also a new status function Get( TriggerName ), returning the name of the trigger that is currently in progress.

Another status function is Get( TriggerObjectName ), returning the object that is assigned with the current trigger. This is different than the function Get( ActiveLayoutObjectName ) because the object that triggered the script is not always active.

And then …

This was not the first time I met April L’Assange. It happens always around this time of the year. If just FileMaker Inc. would know …

I might see her again, if not next year, then perhaps one year later. Until then, happy April Fool’s day!

7 Responses

Some great ideas there!

The searching relationships and within Scripts would be the most useful I think!

Very cool, Is can be support such as flash movie,rich editor(for send email use) in new version? Thanks.

You suck! This is THE WORST joke ever!

I was beyond elated reading this article only to have it all come crashing down at the end. I would have been able to look my IT friends in the eye when I told them I was a FileMaker programmer!

The only way you can make it up to me is to kidnap all FileMaker programmers and lock them in your basement until the aforementioned features are working.

@donjuan

sorry for my english. What do you mean ? It that all untrue ? hope not !!!

What about filemaker go ? Especially related to:

multitasking
export xml data
controls look&feel (iphone style)

Too bad this isn’t all true. Googled FileMaker 12 and found your post. Thanks for all the thinking and for your great blog posts. I will be reading them going back into your archives.

you should have add to your happy list a new xDBC connector that has real SQL capabilities. The actual one is almost unusable in real scenarios. Very poor JOIN tables support..

[...] seit der letzten Version 11 schon verhältnismäßig viel Zeit bis zur Gegenwart vergangen ist. Auf kegebein.net gab es schon im letzten Jahr einen netten Aprilscherz, der aus FileMaker 12 eine eierlegende [...]

You must be logged in to post a comment.