Filemaker 12 has now been out for a few months and we have had a chance to use all the new features in anger. But which is the stand out improvement to FileMaker
The new themes allow you to create a reasonable looking system very quickly and then at the touch of a menu item you can change the whole look of a layout. This is nice but if you have a standard look and feel you want to keep, then this is either not useful at all or you will need to create your own css template which is currently not easy.
Having Modal Windows is a nice little add on. If you use plugins to generate complex dialog messages you can now have a window popup instead. This is easier to control, keeps the look the same and doesn’t fall over when the plugin is missing (although the install plugin script step is now essential to make sure this does’t happen).
The new enhancements to the container fields make them more useable as you can now view all the pages of a pdf and store everything remotely so no file bloat.
The improved charting not only looks more professional but is easy to code, and the ability for users to create their own from a table view is great.
However, I think the best new feature is the calculation ExecuteSQL function (not to be confused with the old execute SQL script step).
For those of you who don’t know SQL, FileMaker now allows us to proform SQL SELECT statements within a calculation and return the results. A Select statement is a query on the database like a filemaker find but returns the values for the fields specified for a particular search criteria.
This feature opens up a whole new way of working in FileMaker. We can now have much simpler Table occurrence graphs as we only need to have relationships where we need to see a portal on a layout. An example of where this could be used is if you wanted to see a list of outstanding invoices for a particular customer. We might have had a relationship to from the customer table to the invoice table based on the customer ID and a Paid flag. This would only work if the paid flag was a stored field, if it was a calculation based on the sum of payments then it wouldn’t work. With a SELECT statement we can get information from any field in any table no matter where the calculation is stored. We can also use un-stored calculation results in our select statement. To get the Total of outstanding invoices for a customer we might use “SELECT sum (Out_Standing_Invoice_Amount_Field) FROM Invoice_Table WHERE Invoice.CustomerID = Customer.CustomerID and Invoice.Out_Standing_Amount_Field > 0”. This means that the Paid flag is not actually required in this instance. If you wanted to have a portal showing a list of the outstanding invoices then you could have a field with “SELECT InvoiceID FROM Invoice_Table WHERE Invoice.CustomerID = Customer.CustomerID and Invoice.Out_Standing_Amount_Field > 0” This would result in a list of invoice ID’s which you could then use to create a standard filemaker relationship from this field to the InvoiceID field in the invoice table for the portal.
This functionality makes this kind of data manipulation easier and simpler by reducing the number of relationships and calculated fields. This leads to a simpler and cleaner relationship graph and so should make the solution run quicker. This is why I have chosen this as my favourite new feature in Filemaker 12