Archive by Author

New Space

For the last 5+ years, the FileMaker group in Portland has been meeting at MacForce. Sadly, they closed their doors on July 31, so now we’re moving to a new space for meetings.

800 NE Oregon, Room 1E.  Portland OR 97232

Also, we’re moving to a new meeting time, during the day. We will meet from 3pm to 5pm. This will hopefully make it easier for people with families to come to the meeting, though I know it will make it more difficult for others.

Productive Computing

With a record crowd of over 40 people, Marc and Keith Larouchelle demonstrated all their best stuff.

Core3

Marc started with the amazing Core3 CRM, which is a full featured solution that could run an entire company from companies and people, connections between them, quotes, opportunities, orders and invoices. Also there is a full-fledged calendar, email integration, and tons more. A company could easily use this beautiful FileMaker 11 flagship solution to run their business. And all for only $2500. I would hope for some different license levels, perhaps a lite version, and some other options, and maybe that will come in time.

Plug-Ins

Next up, Marc started showing the great plug-ins. Not many people in the room were interested in the Outlook manipulator, so he quickly moved on to FMBooks Connector, as over half the people in the room use QuickBooks. He showed the really cool features of pulling and pushing data, and reminded users that to implement this, some knowledge of QuickBooks is required, as QB is a very complex database that you need to understand a little bit.

The Plug-In was initially created internally to save them time, and then they realized that this was a big time-saver for lots of people.

iCal Manipulator Plug-In

Allows FileMaker to import calendars and events, and modify them directly in iCal. iCal has a unique ID for each calendar, and also a UID for each event. This allows modification of the existing event from either side, which is nice. Google Calendar and Outlook can subscribe to vCal (iCal) events. If you subscribe to a calendar, some servers don’t allow you to modify the event, it’s ready only. Repeating events are also supported. It handles multiple records for a repeating event, or a rule-based single record with a (potentially) very complex advanced screen.

It also supports attachments, multiple invites, and other features where iCal goes beyond the vCal standard.

Address Book

This plug-in outsells the iCal solution 10 to 1. It’s such a central and great thing on the Mac, since so many things on the Mac connect to Address Book, such as the iPhone, calendar, mail, spotlight search, and more. The Plug-In can push and pull data from Address Book, and the latest version supports groups. The address book is very relational on the Mac, but amazingly, Outlook on Windows is still a totally flat address book. Or perhaps thats not amazing.

eSign Signature

Allows you to bind a database record to a signature. Connects with hardware that is a USB device that captures a customer signature. What is stored is binary data that stores the X and Y coords of the signature, not just the final graphic. If the text of the record is changed, the signature can be invalidated. This is a Windows only plug-in, which may become Mac compatible at some point.

Biometric

Anyone remember the Sony Puppy reader? Very few. Not many people… Now there is a very advanced and pretty bulletproof reader. The plug-in is not quite released yet.

Drinks

We will be establishing a standard venue where we go drinking after the meetings each month. This is a tradition in Seattle that works out well, and really binds the community, and allows time for socialization that’s not easy to do in a meeting format. This month, we are testing the waters at Bistro Montage, which is 4 blocks North of MacForce

FileMaker Go! Hello iPhone and iPad…

This week FileMaker made a huge announcement:

Given this great news, we’ll take half of the Tuesday meeting to discuss FileMaker Go for iPhone and iPad. We’ll pass around a few examples in case you haven’t gotten your hands on it yet, and we’ll talk about what we’ve learned so far in this very exciting first week.

Matt Navarre and John Sindelar will co-present this FM Go section after John’s presentation on Filtered Portals using the new feature in FileMaker Pro 11. John will make a strong case why Filtered Portals is the sleeper feature in FileMaker Pro 11.

Then we’ll open it up to general discussion and Q&A.
This is a game-changer. Come see why!
Please RSVP for this meeting at FMPug

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.

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.