Angelo Luchi:Hosting

This month, Angelo Luchi presented on the various ways you can host your FileMaker data. The meeting was sponsored by MSN Media, who provided the pizza and drinks.

He covered many of the usual suspects, including:
• Local hosted server
• Remote hosted server (like his Drooling Dog hosting, Portland’s own ODI Technology)
• Peer to peer (sheer evil - supports up to 10 users, but never ever a good idea IMHO)
• Web hosting, including PHP, Lasso, IWP, and others
• Mobile device, such as iPhone, iPad, Crackberry
• Citrix / Terminal Services

There are may things to think about in all this. Latency is an important factor, not just the raw bandwidth.

Backups are critically important. If you’re not doing an offsite backup, you should. Use Amazon S3 for your data ($.10 per GB per month, which is crazy cheap) 360Works SafetyNet, SugarSync, DropBox, or other solutions like this that move data to the cloud.

Hosted databases should be optimized for speed. Don’t store large files in container fields. Use solutions like SeedCode calendar or fmSearchResults that have very simple TOs, and rely of local processing of data in variables or global fields, and simple portals. The perfect example of what works poorly in a hosted environment is a portal that shows 20,000 rows of data. I think this is bad design no matter how you slice it, again IMHO.

Terminal Services is the Microsoft solution.
Not as well optimized as Citrix in any method, but less expensive. Printing support is poor - really only for populate HP laser printers. Very bad for multi-function inkjet type devices. Mac support is very poor. Graphics look bad - no support for transparencies or gradations. You’ll want to reduce them. Pretty good control of the environment. Terminal services drops connections frequently, so it’s a really good idea to test.

GoGlobal (http://www.graphon.com/) is a similar service. It started life in the Unix world, and now runs on a machine as small as Windows XP running on a Mac mini, supporting 10 sessions. It’s less expensive than TS or Citrix for small sites.

Citrix is great. The engine has been updated much more than Microsoft, so it’s quite a bit more optimized. The licensing, while expensive, is based on concurrent users, and not total users like FileMaker is licensed. To deploy it, you need to know it pretty well. Amazon EC2 has a single user CitrixXenApp tool that you can play with, which is amazing. Angelo said he’d post a link to that. Maybe This?

Citrix supports a wide array of devices and platforms. It supports all major browsers, and now runs basically as a browser plug-in. There are clients for Linux, old and new versions of Mac and Windows, iPhone, Palm. There is even a citrix client for TRS-80. (kidding) Plug-ins run really well, and update beautifully. You can publish an entire Windows desktop or just an app.

The new product that Angelo showed is the ability to run a full Citrix connection that includes a FileMaker client starting at $65 a month per user. This is not licensed for concurrent use, but for each user. It might be perfect for a few users who are on the road to access a complex application, where other users connect to the server directly. This might also be perfect for a developer who travels the country in an RV and wants to do work on client systems, even though the local WiFi is really bad. I wonder who we know like that. Hmm.

Don Levan - Small Things | Loosely Joined

We’re thrilled to have Don in Portland tonight, talking about some interesting philosophical aspects of FileMaker development, which translate into immediately useful tips in many cases.

Don first showed this at Pause on Error in New York in July, and now is doing the west coast FMPug tour.

This is mostly about the procedural script methods in FileMaker - the scripting language. This is not an object oriented system, so it makes sense to apply best practices from other procedural systems.

Build small scripts, that are independent. Expect all scripts to be called by other scripts, and they all have input and output. Consider the unix pipe | where you can send the output from one program (like ls or history) into the input of another unix program, like grep. In this way, you can filter the results of a directory or list of commands used by a certain string, for example #history | grep ssh   Very cool stuff (the history command is new to me - it shows you all the commands you’ve done in Terminal)

Unix has the concepts of StandardInput, StandardOutput, and StandardError. These translate in FileMaker as Get (ScriptParameter) and Get (ScriptResult)

When a script fails, make it fail loudly. This way, it’s not a big problem for users when a small thing is failing that can aggregate to become a big problem, like the proverbial 2 cent rounding error. It’s a good idea and a common practice to turn Error Capture on, but this suppresses the display of error messages. The errors are still captured though, so if you call a subscript, make sure to pass any error.

Example script: Using a single script to create related record in a portal, to get around the lame issue of the last portal line being blank. But how to do this with a single script that can be used from any context or layout, and for multiple different portals on the same layout? The trick is the parameters he sends to the script being called, which specifies the ‘model’ (which is borrowed from the web world - I usually specify context, table, or action or something like that) then the table and key field where we’re starting (using the GetFieldName() function) and the context we’re heading to to create a record.

This utility script can then be tested very well, updated centrally, is insulated from layouts and context. It uses the SetFieldByName function to specify both the field and the value that were grabbed earlier. It’s beautiful because it’s contextless, and not at all brittle.

The next example shows using methods like this to build reports, which also adds in the Bruce Robertson ‘Virtual Lists’ methods. Don builds a return separated list with all the report data, an Array of all the data in name value pairs. Looks kind of like XML. Check for any errors, and then pass the array to a DisplayReport script. The beauty here is that a single report system can run any kind of report from any table with no changes to the script at all - only in what script parameters are specified. The details of the script were very involved, but each thing was well insulated, and could be understood if you focus on it for a minute, and don’t try to sit in the front row and blog when you should be paying attention instead.

‘Fold Knowledge into Data’

The last example involves syncronizing FileMaker and MySQL data. This is the most complex example, and there are some really detailed parts. It uses some ‘black box’ scripts that do the heavy lifting, but don’t need to be modified to make any changes. Instead, there’s a single master controller script that is edited if there are new fields or tables that need to be added to the synchronize method. Each new field or table is specified in exactly one place, and if it fails to work, it fails loudly and quickly, to minimize any downtime.

Program logic can be stupid and robust. Keep as many variables local as possible, instead of global.

There are some really great ideas here.

Don relies heavily on the SetParameter and ReadParameter custom functions which are available from Geoff Coffey at Six Fried Rice. These are pretty cool, and look more robust that what I usually use, which is the GetParam function from John Sindelar. Usually, I don’t use a SetParam function, but just provide name value pairs like this:   context = person ; action = create ; id = 12345

Don really like OmniGraffle and OmniFocus. Loved reading the release notes they have every day that list all the bugs they fix when and why.

David Simpson, part 2

Part 2: Northwind Database conversion

Microsoft uses a database called ‘Northwind’ that is a standard demo that is perhaps the most famous in the Access world. It is built by MS to show off the product, and is more complex, though still a pretty simple template by FileMaker standards. Maybe it’s about as complex as the sample files provided with FileMaker, but not in the league of the Business Tracker template from Excelisys.

David prepared two conversions of the file. One was a straight conversion with no modification of the text file to fix errors on the conversion. This file has a few errors in the relationships, portals, some missing fields, and other small errors that would need to be addressed in FileMaker.

There are many little details that need to be addressed to get a really good conversion. Little things like dealing with queries for portals, file naming conventions (using underscores in field names, etc) dealing with boolean and other data storage conventions (false = 0) These things all may take about 2 hours for an experienced developer on a system the size of Northwind.

There’s no easy way to troubleshoot the more complex queries used in Access upon conversion. The queries are stored in FileMaker in a comment, so you can still read them. David uses a tool to run the queries in an external tool called Navicat (cross platform) that can help with the queries. This tool tells you which field or part of the code is broken, which FileMaker does not tell you.

Once Navicat fixes the query code, you can copy and paste this back in to the conversion tool, and it will execute the updated code so you can test it.
————-
He also has a 2nd version of the converted file with many of these fixes made in the Migrator program, where it’s perhaps more efficient to fix. This way, the same conversion error is not repeated and has to be fixed only once in Migrator. This uses the queries run though Navicat, plus font changes, and other mass changes done to the objects being converted.

Overall, this shows that there’s great deal of variability when you convert different Access databases into FileMaker.

Queries are brought into FileMaker as scripts that use the DoSQL plug-in, which is really cool. These are pretty easy to read and work with, though you might want to use different methods to get the same data in FileMaker, like a relationship or Enter Find Mode, Set Field, Perform find, and return the set of records as IDs, or whatever method you’re using now

In general, Access databases created by basic users that use the simpler functions and don’t write lots of queries and VB code will convert into FileMaker beautifully. Solutions created by professional Access developers may have queries behind every field on every layout, which would be very time intensive to convert. These may still be very worthwhile to convert in an automated way, but will just require more planning and work in the middle stage, and several test conversions. Also, David offers consulting services to do the conversion, since he obviously has great expertise with the tools, and has worked with many types of databases.

David Simpson - .com solutions - Access to FileMaker

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.

Ernest Koe goes under the hood

Another belated post and thanks to Ernest Koe from The Proof Group, who brought the fmSpark and NinjaCal Roadshow to Portland for our May meeting. fmSpark is Proof’s mail-merge solution and NinjaCal is a super-easy-to-integrate native FileMaker calendar widget.

We got to see not only how these products work, but also a look at the thought process that went into developing them.

It was an under-the-hood session covering a range of topics including scripting practices, working with multiple developers, MVC design and other FileMaker goodness.

360Works makes cool plug-ins

Nobody wrote up our March 2009 meeting, so a belated retroactive “thanks” to Jesse Barnum of 360Works who demonstrated many cool things you can’t do in FileMaker - unless you have the right plug-in! Among the demos:

• Install plug-ins onto your users desktops without having to touch their computer or even restart FileMaker
• Super-easy audit logging with FileMaker 10 script triggers, with color-coded changes highlighted
• Search the text of PDF documents inside FileMaker
• Grab files and images from a MySQL database, gmail account, and FTP server
• Suck the contents of a web page and all of its images into FileMaker fields
• Send an HTML-formatted e-mail message, with a link to trigger a FileMaker script from the email viewer

Check it out: www.360works.com

PiVOT details

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.

Rick Aguirre Presents PiVOT

Tonight, Rick Aguirre from Beezwas is presenting a really cool framework used internally at Beezwax for new project. It’s called PiVOT, and relies on the DoSQL plug-in from My FM Butler.

Google-like searches in FileMaker

Matt Navarre describes a technique for scripting searches across multiple tables in the same solution, and presenting your users with an intuitive list of the results.

Nice job, Matt!

FileMaker 10 a crowd pleaser

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

…

Still reading?

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!