Our latest interview is with Allan Hunter, FileMaker Solutions Developer for "alwaysBEthere" (a wireless-centric telecomm company based in Minneapolis). Like many of the FileMaker developers that we've interviewed in the past, Allan didn't start off in IT. Read on to find out how he discovered FileMaker, how he is managing to maintain sanity while working on a very complex application redesign, his impression of FileMaker 9, and much more.
On your personal Web site you mentioned that you have been using FileMaker Pro since version 1. How did you originally learn about FileMaker and get started with it?I was a college student studying social work and was doing an internship with the New York Association of Alternative Service Providers. We had info on a whole slew of probation, community service, intensive-supervision, etc etc programs throughout the state; for some entries, some of the question-categories only had very short answers; others went on for paragraphs. The desired outcome was a nicely formatted directory, with 2 or 3 organizations on a page if they were short enough to fit that many on a page, fewer (down to 1 per page) if it was an org with lots of info, therefore taking up a lot more vertical space.
I had heard that FileMaker II could do a list view that would put additional records per page if they would fit, without breaking a record in half at the end of a page. The db module in MS Works would not do that, so I said "We need to get a copy of this FileMaker product". By the time we acquired it, it had been up-versioned to "FileMaker Pro". It was a joy to work with. It was a very basic solution, no scripting, just designing the listing layout graphically and then printing it and having it come out perfectly.
Tell us about your transformation from being a social worker to being a professional FileMaker developer.Our organization was cramped for space, so the main filing cabinet upstairs only had room for *current, active* clients. When a case was closed, the file went into the basement. There was *supposed* to be a filing system down there, but there just wasn't, not really: social workers would trundle their closed files down when they had a moment, yank open any old drawer, and shove their files in and leave. It was truly one of those "Black Hole of Calcutta" situations. Meanwhile, most social workers were in the habit of keeping their most active files at their own desks.
Our clients were senior citizens, some of whom would get one agency confused with another; they might, for example, go to the unaffiliated office down the block and say "I was supposed to come back with these forms" when they were actually supposed to have returned to our office. The other org would call on the phone, "Do you have Dorothy Hemperer as a client, perchance?" And whoever answered the phone would cover the mouthpiece and shout into the room, "Yo, Connie, Allan, Tania, anyone got a Dorothy Hemperer?" And maybe Tania would reply "That name sounds familiar, I think that might be Tara's client, but Tara's out to lunch". Or perhaps Dorothy Hemperer would come into our office but not mention that she'd been seen prevously, so the intake worker would do a whole new workup on her, a new chart would be created, and her case would be assigned; now she's got two social workers, mutually unaware of each other.
The director said "We desperately need a database, this is ridiculous". Hmm, database? I told him I had done some work with databases, and would take a crack at it. I built a simple solution at first, using FileMaker 2.1, just the client's data and the social worker's name, client status, intake date, etc, and the ability to do a Find to see who has what clients. And a list view to sort and print. All of it manual. One day I was exploring the menus and discovered the ScriptMaker. FileMaker ver 1 hadn't had *anything* like this. Wow. Soon I had 15 or 20 scripts in the scripts menu, including routines for getting weekly, monthly, and annual totals for statistica reporting, and of course restoring each social worker's caseload and sorted and printed. Shortly after, I discovered buttons. And subsummary parts! I could summarize the data by category and display totals within each category! Soon it became obvious that I had more of a flair for this than I did for doing the social work.
It sounds like you did some very interesting FileMaker development work at BBDO -- supporting over 60 FileMaker-based solutions and around 300 users. Tell us a little about the work you did there, and if you would, please elaborate on the challenges that you faced in terms of security.Most of the solutions were distinct from each other - the company was divided up into specialized workgroups that just worked on "their stuff". They ranged in complexity from little single-file 4-layout systems to 20-table relational databases with orthodox hierarchical structure. Before I was hired they had already walked around and snagged all working solutions and put them on FileMaker Server, instead of them being scattered all over. The IT Department's "programming" division would develop new solutions per request as well as supporting the existing solutions. They managed everything from advertisement insertion orders to magazines & newspapers to clocking the time per project of the "creative" talent to setting down the sizes, names, text "slugs", and images of new Quarks for print-media ads, to tracking all items that were in an analog-format library of photos tapes and videotapes going back decades....
This was all under FileMaker 5 and shortly after, 5.5, when I arrived. The security architecture of FileMaker 5 was pretty much identical to the security architecture of FileMaker Pro 1.0, and it was by far the most user-hostile part of an otherwise user-friendly system. Access was by password alone (not account + password + associated privilege set). If someone's responsibilities got shifted, they would have to learn a new password, AND either everyone just shrugged and decided it didn't matter that they still knew the old one or else it had to be changed which meant changing it for EVERYONE with those permissions/privs. Some individuals were users of more than one of those 60+ solutions, which meant learning multiple passwords. It wasn't unusual to stroll down the halls and see lots of monitors with post-it notes containing Name of Solution + password right there in plain sight. And some overworked senior authority on some project would ask a junior associate to update the dates on all the Jones Account records. Junior associate would discover she didn't have editing privs. "Oh, here, use my password". And the only logs generated recorded the username, as set in FileMaker Preferences, and what file they had opened and when. But the usernames were badly out of date, sometimes empty or had the company name, and were easy for an end user to change.
I changed the on-open scripts in each solution to pass info to a central log file, recording username and access privilege and date, then used that to create a table of users; I made a table of solutions, then made a join table of each user's privileges within the solution, based on what they had BEEN having, and finally got rid of all non-developer passwords except for a standard "end user" password, which was made automatic so no one would ever see the built-in "enter password" dialog except for the developers; I made a screen in the central file of users that demanded they enter their real name in one global and their password in the other, and if CurrentUser::Password didn't equal g.Password they weren't getting off that screen short of clicking the cancel (quit app) button. If they did enter a matching pair, I would load their established privileges in the form of a standard set of global fields which would populate similar globals in each solution, and those would be referenced by scripts (and, once we had FileMaker 5.5, also by record-level access calculation formulas). All menus except base-level editing (cut, copy, paste) were ripped out of the menus for all users, and all such functions were given back as scripts that would check for privilege. A user log was also recording each person's logon (actual name, solution name, time, date, and the value of each of the global privs parameters; plus info like platform, OS version, app version, and FmPro Privileges UserName for matching up with usernames showing up in the FmServer Console).
In this day and age it would be considered inappropriate to insert a roll-your-own security systme like that, but the built-in security in FileMaker of that era was so unweildy that it was useless, so it was a step forward. It's easy to forget just how bad FileMaker's security architecture was at that time. For instance, you could not go into Access Privilege of a live hosted file *at all*, not even on a read-only basis. If you wanted to find out what privs were conferred by a given password, you had to go snag a copy of yesterday's backup and open that locally, or else kick everyone out of the solution and unserve the file and copy it off FmServer and, again, open it locally.
Anyway, having done that much with the security console, I went on to list each file in each solution, so as to allow easy search by filename to find what solution owned it, or to search for solution and get a quick listing of its files; and added portals to all users of the solution and all solutions of the current user (the local record was the record of the user by full name, and the solution field was a global with a dropdown VL of all hosted solutions). Clicking on any value in either list would "hop" to that value. Last logon date for each user prominently displayed, and buttons to bring up the Log records for the currernt user or the currently selected solution. Etc. I even put in a map of our 4-floor office so that by clicking a button you could get a graphic display of exactly where the current user sits. (I should mention that the office numbering scheme in that place was devised by a sadistic maniac: Office number 15 would be next to office 115 then would come office 215 then 315. Office 20 might be nowhere nearby.)
Where are you working now, and what role does FileMaker play in your responsibilities there?It's a company that acts as intermediary between companies that want cell phones and cell phone plans for their emloyees, on the one hand, and the big cellular service providers on the other. They track all their workflow in FileMaker. Their solution, like the ones as BBDO, sort of grew up separately, each being devised for its own purpose, but unlike the BBDO solutions, the ones at this company (which is named AlwaysBEthere) became interknit and interwoven. Most of it was apparently built originally under FileMaker 6, then those files were converted simply by opening them in FileMaker 7, with no cleanup being attempted. Then additional tables and TOs were added to those files.
Even focusing only on the files I decided were the primary central core files, I counted 414 native tables in all. That's tables, not table occurrences. Probably 2/3 of them consist mostly of serial number, date created and modified, time created and modified, creator, modifier, and then 2-10 data fields. A great many of them are redundant in function (one table of contact people associated with one type of project, a totally different table of contact people affiliated with a different project. One table had a "Next Step" table and a separate "Last Step" table. In many cases entire files had apparently been duplicated in the Finder, then renamed, with the tables and table occurrences within them remaining the same, then gradually diverging from each other.
In the relationships display of most of the fields, the lines cross each other in a snarly mess, they cross directly over the table occurrence boxes, the table occurrence boxes themselves are often partly on top of each other. Most navigation was by use of the actual layouts menu, and dozens of scripts are available to users via the Scripts menu. This solution is a very odd hybrid of routines that have no place in a FileMaker solution of newer vintage than FileMaker 2, alongside of deployments of modern-era functionalities. For instance, there are scripts that move data by flipping back and forth between "Copy" and "Paste". And yet the same files will have 34 custom functions or will make extensive use of auto-enter calcs that re-evaluate and do replace their values. Go figure.
So my first task was to explain that this needed to be rebuilt in a single file and vastly simplified and cut down to size. That didn't take long, they were ready to hear that and did not argue. So my second task has been to do exactly that. I have replicated the core functionality, in a completely backward-compatible structure, in a single FileMaker file containing only 85 native tables. (I will be able to lower that number, as I've discovered some of what I replicated was not in fact still in use. No one ever removed functionality that had become obsolete or had never proven useful). All navigation is scripted and recorded, and all scripts are executed via button. The relationship diagram is so large I've created a Relationships table to let me quickly find any relationship by native table, by table occurrence, or by involved fields, and to see a color-swatch corresponding to the section of the relationships screen where those table occurrences can be found. (I hope to simplify the rel diagram over time, too -- it is much more concise than the source system was, but even so I have 460 table occurrences on that graph, and it looks like an enlarged photo of a computer's motherboard or something.
Initial beta-testing rollout starts on Monday, actually, so this is a very exciting time for me. Now that I have a coherent (if still overcomplicated and over-convoluted) structure laid down, I needs to put users in it and see about accomodating their needs.
What do you like best about your job?Being able to work from home and set my own hours. And tackling this challenge has been quick a kick for me. It is not the solution I would have designed if I had been designing from the ground up, but a sudden massive change to their workflow and a lack of backwards compatibility were not options here.
What do you like least about your job?I will confess that there have been a few times in the 4 months that I've been working on this when I've broken out in a cold sweat, put my head down and whimpered and said "I can't do this, no one could do this, this is going to fail. I have only one task and if I can't do that one task, that's, umm, totally not good". Sometimes it's things like finding a file with 34 custom functions, most of which reference other custom functions which in turn reference yet other custom functions, a few of which are corrupted and indecipherable. I opened one and found it to be defined, in its entirety, as "Let ( [input_timestamp = textfield_to_native_timestamp ( ZN". Sometimes it's opening a file to find that every table occurrence in the thing has names like "PTO_fk_JS_I__RelConst_Org_Rule". Things like that that would be merely annoying in a smaller solution just make me feel overwhelmed sometimes. I guess it's the flip side of enjoying the challenge!
I first learned about you from one of your posts on TechNet. You mentioned that you are in the process of redesigning an application that you inherited that is... well... hellish. 49 files and somewhere in the ballpark of 414 tables overall. How is that project coming along?See above. It's actually 56 files, but I skipped some after designating them as peripheral. The 414 tables are in the files I actually have been working from.
Having been through a complex application redesign project like this, what advice would you give to someone who is taking a position (or taking on a project) where they will be responsible for a database created by someone else?In my experience that's going to be the case more often than not if you're being hired as an in-house corporate developer. Only rarely are you going to be hired to create a database for them for the first time. (This seems far less true for developers who work freelance, which I've also done). My advice would be: first off, anticipate that there will be a lot of things you won't like in the solution you've inherited. FileMaker is so flexible that there are a zillion ways of doing almost anything, and each developer is going to have his or her preferred approaches, and it's only natural that you'll quickly find things that make you roll your eyes; so acknowledge that, then don't let it get in the way of you learning things, seeing new techniques, coming to appreciate some of the advantages of doing things in this different way. At a minimum, you have to have a modicum of respect for the work that the database is doing for the folks who hired you. And if your first instince is to pick up a sharp axe and put the awful thing out of its misery and start over, you're most often not going to be giving your employer the best-possible benefit of your services.
I know that probably sounds inconsistent after I've described this messy solution in terms that probably make it plain that I regard as being very very far from elegant and cleverly built. But some of that is a standard curmudgeon's tendency to emphasize the frustrations.
What are your general impressions of the latest release of FileMaker Pro (version 9)? Are you happy with it? Disappointed?Mildly disappointed. It kind of reminds me of the FileMaker 6 release -- underwhelming at the time, but some of the new features may grow on me to the point I hate working in 8.5. I was really really hoping to be able to Set Field [
], to Perform Script [], to have a thin client or a better pricing model than a full-priced copy of FmPro for each workstation; I was hoping to be able to designate a "template" layout and other layouts could 'subscribe" to its elements so that to change them on the template changed them everywhere; I was hoping Tabs would be fixed (you should be able to scroll through ALL screens of a file in layout mode without fishing around blindly for possible hidden tabs concealing other layout elements; you should be able to asses what tab you're on within a script, Get(ActiveTabNumber) or some such, and to navigate tabs elegantly, Go to Tab [by name or number], instead of using klunky workarounds.
On the other hand, I sure do like that non-modal ScriptMaker. Can we have that with Define Relationships and Define Fields as well, maybe?
What are your favorite new features of FileMaker 9?
The non-modal Scriptmaker, as I said; and the formula-driven formatting controls. The "put your scripts in folders" on the other hand is probably going to be the one that I shrugged at but will find increasinbly indispensible as I get spoiled by it.
If you could add a few features to FileMaker, what would they be?
See above. I've been waiting for indirect field referencing on the "subject side" since the heyday of FileMaker 5. And as also mentioned, I want indirect script referencing too. To add to a theme, I'll toss in indirect referencing of privilege sets in the Create Account script step. I really should NOT have to have a raft of If / Else If / End If clauses to handle the different priv sets.
What's your favorite tool, plugin, or technique for developing FileMaker databases?
Honestly? I like to go "New File" and just start typing. The most important tool is behind my eyeballs. Visualizing how I want it to behave, and then making it happen. It's like improvising on the piano, I'm totally addicted to the actual experience of making it happen. Everything else is one or another nice tool in the toolbox but nothing that really sticks out as THE tool or THE technique.
Are you a certified FileMaker developer? If so, how has being certified help you and your career? If not, are you planning on getting certified?
I decided I should go out and get certified. Took no prep courses, read no guides, just went in and took it cold. Got my butt handed to me. I discovered that there are great many details that I don't just know off the top of my head. I think I should probably get the cert, but before I take another run at it I will probably break down and take one of those courses. But I don't like that. I somehow feel like if I'm going to take a prep course that's designed to bone up for the test, I'm being tested on my ability to take that particular test, rather than being tested as a FileMaker Developer.
What advice would you give to someone that is just starting to do FileMaker development?
Years ago, there was this guy named Arthur Naiman, who did THE MACINTOSH BIBLE along with some friends, and he had the 10 commandments of the Mac, the first of which was THIS IS THE MAC; IT IS SUPPOSED TO BE FUN. That sums up my feelings about FileMaker. Play with it. Discover what you CAN do with it rather than picking some vastly complex and challenging task as your first enterprise and trying to become an expert developer in 4 months. Do something simple and then elaborate on it and elaborate on it some more.
What significant changes have you seen take place in the FileMaker world over the years?
When I first discovered the world of other FmPro developers in a big way, in the late 90s, it seemed like everyone had gone plugin-crazy. The extensible architecture allowing them was still pretty new, I guess. Nowadays there seems to be more confidence in the native tools. Not that no one uses plugins any more, but it's definitely a deeper toolbox we get to start out with.
I think we're finally getting some respect from developer-folks outside the FileMaker community. That's been a long time coming. For so long we have been treated as if we were playing with a toy, not a real database environment.
You're active on FileMaker's TechNet. What do you think of the service so far? What improvements or changes would you like to see made to the service?
I've been on since the late 90s but I was pretty inactive in the FileMaker 7 era. I was still working in 6 and the discussions swung pretty rapidly to 7 and I kind of faded from the scene. When I finally picked up FileMaker 8 I came back.
I do wish they'd take webcrossings out back and hit it over the head with a shovel though. The email is horrible (return characters messed up, odd characters interspersed from time to time), the usenet format is horrible (every third post consists of HTML, and HTML does not belong in usenet; my usenet reader turns those into attachemnts I have to detach and open in a web browser and I am not going to get a different newsgroup reader just to accomodate a non-standards-compliant newsgroup), and the web interface is horrible (tiny little posting area, awkward page layout, unintuitive search tools, etc).
I've gotten to the point I would give up email if they'd ditch webcrossings and just go with a vBulletin mesage board. But what I really want is a plain-text email digest that is formatted properly so that email programs will automatically bust each digest up into separate sub-emails as posts, as the old Info-Mac Digest used to be. That's easy to manage in Eudora and easy to search for a set of text-strings to find info on whatever I'm trying to look up.
Let's talk about DevCon. In an earlier email to me, you mentioned that "I've only done one FmPro DevCon and it stands out as one of the most unpleasant experiences I've ever subjected myself to." Yikes! What happened?
Orlando, Florida. No high-speed internet provided in the hotel rooms. No high-speed internet provided out on the conference floor either. Lousy food. Nothing to do and nowhere to go for my girlfriend who came along. Instead of "workshop" experiences that I was expecting -- "OK Joe here is going to show you how this technique works, open FileMaker on your laptops..." -- it was like attending a 3-day advertisement for FileMaker. I didn't feel like I learned anything. The one truly bright moment was seeing Rich Colombre demonstrate controlling external functions: "FishMaker Pro", the Billy Bass automated with a home automation via serial-port kit.
You were amusic student at the University of New Mexico at one point. Does music still play an important part in your life?
I never made a career out of it but I still bang on the piano and compose some new pieces & songs every now and then.
Do you have an iPod? if so, what are you listening to these days?
I do not, in fact, have an iPod. I have a PowerBook and I have playlists of my .MP3 files. Lately I've been doing "Radio Station Allan", doing a Find in FileMaker for certain categories of my music then sorting them to random sort and snagging about 400 tracks' worth and making that into a playlist to listen to while I work.
Tell us about your "Theory Site."
That would be another entire interview unto itself! Your readers are entirely welcome to go there and poke around. I'm afraid that since I've left the academic world I don't write much theory any more.
What technology has most changed your life?
The computer, definitely.
What are your favorite things to do that don't involve work?
Go to new restaurants and try foods I've never eaten; play around on my Mac and learn how to make it do new things; play the piano; go for long walks, I guess you could call it "urban hiking."
What inspires you?
Different things at different times, I suppose. Often having little to do with the inspiring experience or object per se, just something kicking my mind to go off in some new channel.
What's next for you? Any big plans that you'd like to share with us?
At the moment, and for the last 4 months, I've been so deeply immersed in this one project I haven't made any big plans.
Thanks Allan!
To learn more about Allan, please visit his Web site, located at
http://members.bellatlantic.net/~adhdah/FmPro/fmdevindex.html. To learn more about alwaysBEthere, visit: http://www.alwaysbethere.com/
