This week FileMaker made a huge announcement:
Then we’ll open it up to general discussion and Q&A.
Part 1: demo of migration of simple access database
This month, we’re lucky to have David Simpson, a FileMaker and SQL developer with 23 years experience. He’s showing the FMPro Migrator solution which converts Microsoft Access databases into FileMaker (and much more)
How does it do this? It creates a file from the Access solution similar to what the FileMaker DDR file is. It analyzes all of the objects in the database: tables, relationships, forms, queries, value lists, etc. Some of these objects are stored very different in Access, such as Value Lists. Access does not have the concept of named value lists, so Migrator handles this, creating a de-duplicated list.
It collects the VisualBasic code and converts this to FileMaker scripts, it uses the QuickKeys software on the Mac (and different software on Windows) to create Table Occurrences! It even sets the color for each occurrence on the graph, which is cool. Then it creates the relationships on the graph.
Layouts are created from scratch. The solution converts the objects, including graphics, fields, buttons (that call scripts) colors, lines, fields with value lists and calendar pickers. All looks very faithful to the original in Access (for better or worse… usually the latter)
It’s all really cool to watch it all happen when QuickKeys does it’s magic, vastly faster and more accurate than a human could do, but still kind of a hack. I mean, what I wish for is that FileMaker would allow you to create a layout in HTML or XML or some other text format that allows find-replace. I can see some great ramifications for mass change in FileMaker databases using this method, which is the idea behind FMPro Script Diff. This is another product in the .Com solutions array, using much of the same technology.
When layout objects are pasted, there is a spot where you can substitute fonts and other details in the text file before the XML code is built, put on the clipboard, and pasted in FileMaker. [did you know that when you copy layout objects to FileMaker that it creates XML code, which allows you to copy and paste between versions, between pro and advanced, etc.]
All the data is also brought in, via the ODBC connection. This requires the Actual ODBC drivers on the Mac, which offer some other advantage, despite the overhead of this. The data import works with unicode, and container fields, so it’s much more than a text export and import could achieve.
As the meeting rolls on, I’ll write some of my thoughts and observations here.
A sample version of this database is hosted on the beezwax server for a short time to play with connect to:
fmnet:/fmnexus.com from within FileMaker, and open either PiVOT 9 or 10 to match your version of FileMaker. User: portland, pass: beezwax. This allows you to explore the graph, fields, scripts, and functions demonstrated at the meeting.
If you didn’t realize, FileMaker plug-ins can use SQL code against FileMaker data, such that scripts can call those plugins to query records, create, edit, and delete. Also, some design features can be accessed, such as creating and deleting tables. Rick’s solution uses this power to great effect.
First off, by using the DoSQL Plug-in, Rick gains great access to speed and features that don’t exist natively in FileMaker. This certainly adds some layers of complexity, and that is largely managed by a series of custom functions that are called in scripts. The custom functions build sql code based on the need, for selecting and returning data, creating records, deleting or updating, etc. Then, these functions call a simplified single custom function that contains all actual calls to the plug-in. This is great, as it would allow a change to a different plug-in to happen in one place, should that ever become desired.
One of the things he shows is how using SQL calls with portals is so great for searching on partial words, and on the middle of word, as well as searching on one or more fields in the table, which can be controlled by selecting fields from a value list. Also sorting the portal is handled in SQL by the use of a custom function, using a very clever method. Basically, the portal itself has just one sort field, but the contents of that field are set by the custom function that looks at the records currently in the portal.
As each line in the portal is clicked, a field is set with the ID of that line, which is used with conditional formatting to hilight the line, and also keeps that key value to do work on the line, such as navigation elsewhere, or showing more detail for that selected record.
All the basic functions use SQL in the background, but don’t require a great knowledge of SQL, which is nice. If you want to delve in to the SQL, you can also easily do that by either typing code into a field.
Actual implementations based on this framework have stayed very close to the original. Simple solutions can be built in about an hour per table, which is really fast. After that time, I guess the work is just the specific custom needs for each module, which would be greatly simplified because of the tools in here.
Naming conventions are very clear, and require very little explanation. This is both for the graph, fields, scripts, and layouts. One cool idea is naming scripts with a clear English name, then some single characters for unique aspects about the script, such as .f.d.c. for a script that runs with full access and both creates and deletes records, and then a number of spaces at the end of the script. In this way, you can avoid using folder names, and instead have an empty script called ‘Customer Stuff ‘ (with 4 spaces at the end) and then if you want to see all your separator scripts, you can just type 4 spaces in the search. Tricky.
Navigation is multiple window friendly. It remembers navigation settings and hilights in each window that is open, which is very polished.
Icons in the menubar are in small or large sizes, The large size is shown when you’re actually in that module/table. These are set with a function. Further, they can be screen resolution dependent based on the size of the layout you choose in preferences.
On the graph, everything to the left of the PiVOT table (the main table in the template) is Utility. Everything to the right is Data. Another great idea here is for any relationships that use referential integrity (that checkbox that deletes related records from table B when they’re deleted in table A). The idea is that these TOG are in a separate, clearly marked portion of the graph, so you never have to go searching for them. This is a great idea.
We weren’t able to “live blog” last night’s meeting, so here’s a recap:
We had 43 people sign the guest list, and there may have been a few who didn’t sign. It was standing room only!
Alexei Folger, Sr. Systems Engineer from FileMaker gave an overview of FileMaker 10’s new features, including:
• New Interface
• Script triggers
• Subsummaries in Browse mode
• Saved Finds
• Send Mail via SMTP
• Advanced Recover
Alexei spent some time discussing the new advanced Recover options, which gives you a more detailed log about the recovery process, and lets you control more of what happens in that process. One useful item is a checkbox to rebuild your indexes (Now or Later). If you use the Save as compressed option combined with rebuild index later, you could safely save a nice compact copy of your file for archiving. Alexei will be doing a session on the new Recover features at DevCon this year, should be a good one.
Looking at the new status toolbar, keep in mind that it is tied to the application, not the file. I.e. you cannot customize the toolbar and then distribute a file with those customizations. It’s quite handy though to have one-click access to data viewer and debugger, as well as all the Manage… menu items.
Note that if you use custom menus to rename a menu item, that name will be reflected in the toolbar button. Buttons that a user’s privileges have blocked will be dimmed. Hiding/locking the status toolbar via script behaves just as the old status area did.
On to the big one, Script Triggers: Alexei said there were two and a half ways to trigger a script. First there are field/object triggers (OnObjectEnter, OnObjectModify etc); and then there are layout triggers (OnRecordLoad, OnLayoutLoad etc). She showed some examples: typing shift-arrow up or down to increment a date field; keyboard shortcuts to emulate spreadsheet behavior in a portal, (go to the same field in the next row, or fill down); and adding notes to a text field for an activity log.
Another very welcome new feature is that you can now work with Sub-summarized records in Browse mode. The sort order is maintained and updated as you edit. E.g., if you’ve sorted by state, and you change a record from AL to AK, that record will jump to its correct summary group.
This can work well in conjunction with script triggers, e.g. you can click a radio button field to trigger a script that re-sorts based on your selection. Or, a simple script to go to next record makes it a snap to assign records to different groups - kind of have to see this one in action.
Alexei recommended that FileMaker Technet members download the recent white paper on script triggers.
Oh, and the “half” trigger she mentioned is the new “Install OnTimer Script” script step. This starts an “egg timer” that will run the specified script after the specified interval. Lots of potential there!
[For more on script triggers, I highly recommend the recent FileMaker 10 posts at SFR.]
A couple of new features clearly target end user rather than developers: saved Finds and table view editing. Saved Finds, be aware, are tied to accounts. They can’t be exported/imported to another file. One quirk: Finds that are saved while the file is not hosted will be available to all accounts when the file becomes hosted.
Table view has a new option: the user can add a column (field) to the table, even when that field is not on the layout! This cause a minor stir in the audience, which prompted Alexei to demonstrate that when access to a field is restricted via account privileges, not only would the field not be visible in table view, but it won’t even appear in the list of available fields the user can insert. So don’t panic.
The new SMTP mail feature is just what it says: you can send mail directly from FileMaker, no mail application required. [See: Zawinski's Law] You’ll still need a plugin to send multiple attachments, HTML and that sort of thing.
There are more features that will have to be saved for future meetings (e.g. new functions, and set field by name).
Server 10 was just briefly touched on. Server can now import/export and email. It has some better scheduling for multiple backups, and better log viewing.
The door prize for this meeting was a copy of FileMaker Pro 10 ADVANCED. Congratulations to winner James Robbins!
A big thank you to Alexei Folger for a fantastic presentation! Thanks to Phil Smith/FileMaker, Inc. for the pizza and door prize. Thanks to Fiddlehead Software for the beverages. Thanks to MacForce for the top-notch facility. Thanks for coming, everyone!