FileMaker vs. Access
The debate has been longstanding in the FileMaker community about how FM is substantially better and easier than Access. I’m not here to rehash a seemingly silly and hardly useful flame war, but with the pending (a year from now can be considered pending for Microsoft 
) release of Office 12, FileMaker Inc, and developers will have to contend with the mass of Office users who might want to instead use Access as their data-platform of choice.
Office 12, being one of Microsoft’s cashcows, has been under great scrutiny and been rearchitected and has a new and interesting UI on top of it. Their use of “ribbons” or large visual toolbars to display options and actions instead of a myriad of menus is one of the main innovations.
I’m posing this kind of philosphical debate to question what FileMaker users and developers would want to see in FileMaker 9? A FileMaker wishlist can tend to be very solution specific and not broad enough in terms of asking for justifiable productivity gains to warrant the added expense of a new version, so what of Access? What do people think of Access in comparison to FileMaker? I set up a rather intricate hierarchical data view in Access and found it surprisingly easy to implement. Beyond that, the process of customizing the data entry forms, sharing the data, and more was beyond simple and quite frankly, a pain in the ass. I could have done twice as much in half the time with FileMaker aside from the native hierarchical view control. That being said, the only reason I had that control was because it was installed with my version of Office/Access and it was one of the Pro features.
I’m chiming in on this finally (I originally wrote this post before CES 2006) because I’m interested in software, Microsoft, Vista, Office and where all three will go and if there will be a generational leap in productivity and user friendliness. Some people hate Microsoft, I don’t. I’m no lover either, but I do admire some products that they produce. I tend to adore Apple products more, but not always…
Back to FileMaker and Access: what are some of the things people really are looking for? What do you miss most from the feature set of 4D or Access? I want real answers here people.
Some FileMaker updates
For those not in the know, FileMaker Server 8 Advanced is shipping. If you’ve committed fully to the version 8 platform of FileMaker, then this is worth noting.
In addition, FileMaker Inc is also promoting a set of graphics that can be added for the ’spit and polish’ to any FileMaker solution. While they promote this as a 79.99 value, I think that the FamFamFam Silk icons for the price of FREE are far better. Don’t forget the Mini icons which are also FREE.
This is my first real post of the new year. There’s a lot to look forward to and I hope to bring a lot to you. Wordpress 2.0 is out. Apple is glowing with new goodies. Chris has released some of his hard and beautiful work out unto the open. And I’ve got a lot of catching up to do. It starts now! Hoo ahh!!
For some more fun reading and viewing…
Take a look at Tauquil and a post he put together based on the Bill Gates preview of some Longhorn technology. It’s both funny and worrisome given the preview of what is actually coming this far in Longhorn
My company has recently moved to Filemaker. I had been dabbling with Access before and there are a couple of things that really annoy me with Filemaker.
I would like to link non-filemaker files into my Dbs and give them relationships to each other, run calculations on the data.
Much in the way that I can (in Access) create an Excel spreadsheet which if changes are made to it in Excel is instantly reflected in the Db without the need to do a lengthy import.
The reason for this feature is because I have a large multi-user database on SQL which needs to be accessed by our other systems to perform calculations on. An import of these records into Filemaker would take an extremely long period of time & would have to be repeated hourly to remain accurate.
Thats an interesting point. It’s an aspect of FileMaker I wish would receive more attention and power as well. 4D also has a similar feature with their Office Object Library. One which quite frankly, make me jealous as a FM developer.
I have been thinking about this problem a lot and I think there may be a Mac or Windows based solution by using FileMaker -> REALbasic app -> Excel kind of data workflow where we could use REALbasic’s Office Automation featureset as the glue to to automate the exchange of data between the FM db and Excel. Excel can also use ODBC and some VBscript to get data in and out of a spreadsheet, that may also be a solution as well. Unfortunately, some of this kind of thinking should be more clearly provided by FM Inc, but hasn’t been. I remain hopeful that it will be because they must surely be paying attention to 4D and Access!
Hi Denis!
Thought you might find this interesting: Just this week I was contacted (out of the blue) by a recruiter about a FileMaker opportunity. It’s an Access to Filemaker conversion project for a large healthcare organization. That’s no typo. Access TO FileMaker! I was shocked to say the least, because 9 times out of 10 the conversion is the other way around.
And I’m also seeing a lot of other FileMaker opportunities these days. Some are upgrade projects (Filemaker 6/7 to 8), others are for new applications. It’s very encouraging! Things are looking up for us FileMaker developers.
But anyway, you asked: what are some of the things people really are looking for?
What I’d like to see in future versions of FileMaker are features and functions that (to me anyway) seem as though they should have been in the FM for quite awhile. Here are a few:
* Better ODBC support. I want to be able to create really elegant FileMaker-based interfaces to other DBs (SQL Server, Oracle, etc.). I don’t want to have to use a plug-in for this.
* The ability to dynamically specify filenames and directories for file imports and exports. Again, I know you can do this with a plug-in — but I shouldn’t have to use one for this.
* The ability to perform find requests using SQL queries instead of the traditional “find” function.
* Freeform editing of scripts.
* Javascript-like event handlers for things “onMouseOut” and “onMouseOver” and “onChange” and so on when working in browse mode.
I could go on and on. Don’t get me wrong — I love FileMaker and v8 is more than I imagined it would be. But some of the things that I listed above are simply missing.
Can’t wait to see what the FM comes up with next!
Interesting set of requests. I always find the question of what users want to be the most interesting because it most greatly reflects the interesting ways developers and users are using FileMaker.
A lot of this seems like no brainers, huh?! The dynamic filenames as well as the ability to natively move, rename, and delete files. Tim Neudecker, on your blog, echoed the same thoughts on ODBC and SQL support. The idea of JavaScript handlers is a good one and I would think that they would be somewhat easy to implement given that FM has incorporated the ability to change the mouse cursor over buttons and display tooltips
[lightbulb]
Given that we can execute Scripts with the execution of calculations, couldn’t we put a calc in a tooltip to execute a script, using the MacExample plugin or WinExample plugin that is included with Developer 7/Advanced 8 (get more info on that plugin here if you need it) ??
[/lightbulb]
Btw Tim, when are you going to open up FileMaker Addict to comments from non blogspot members?
FileMaker 8 is a tremendous improvement, but for basic control of data entry, there are still a LOT of features missing that have been in Access for over a decade (I speak as someone who did a lot of Access development back in the version 2.0 and 97 days, and those ancient versions are still beyond FileMaker in many sophisticated features).
1. Field- and Record-level events. It doesn’t have to be the huge list that Access has, but at least OnEnter and OnExit events, and OnSave would be nice as well. And built-in, not some kludgy plug-in. This could save so many work-arounds and make for MUCH more robust solutions. I really, really want this.
2. A way to lock users onto a window (like the Access “Dialog Window” setting). Now that FileMaker has multiple windows (will wonders never cease!), we need a way to keep users from straying from a process until it is completed (something better than the wimpy Pause script step).
3. More ways to use variables in scripts. For instance, I’d like to be able to use a variable to call a script. Currently you have to explicitly select the script to run in Perform Script. Also in stored Finds, I’d like to use variables as well as literal strings. I could cite numerous other examples. The more we can use variables, the more versatile our scripts can be.
Access has had all this stuff since shortly after Bill Gates was potty-trained. What’s FileMaker been DOING all these years!
I think we both echo the same thoughts. FM is so00 advanced because of its loving embrace of simplicity and elegance, but sometimes it’s frustrating to deal with the lack of feature that seem self-evident. A kind of OnSave functionality should be easy and simple to implement as we already have the ability to specify on a layout by layout basis whether or not automatic or user-initiated record saving is enabled.
The app is obviously able to know when a record is commited and it should be a no-brainer that users want to hook into that added feature.
The idea of specifying a window as a dialog is great as well. The Pause script step has certainly become overused as tool of circumlocution. AMEN TO USING VARIABLES IN STORED FINDS!!! Did I emphasize that one enough? This is another self evident request from my point of view. My users always perform finds using requests based on criteria that is passed and not static (as in literal strings).
FileMaker has likely been growing due to its ease of use and decent scalability, but I concur some of this has to be implemented ASAP !
Attachable scripting, so i can write a script in a text file using a faster editor, save it to a directory, and call it from a FM script or calculation.
The ability to comment calculations.
You CAN comment within calculations now. (Yeah!) Just place “//” at the start of a line, and that line becomes a comment.
Case(
// if the user has said Yes, set the field to Angelina
Contact::Active = 1; “Angelina”;
// if the user has said No, set the field to Jennifer
Contact::Active = 0; “Jennifer”;
// otherwise, the Contact::Active field is blank or null, so make sure this field is blank
“”
)
it’s not fancy, but it works for me!
Don’t forget you can also do multiline comments as of version 7 as well:
/* This is the first line
and another…
and another…
and another…
and another…
and another…
and this marks the end of the comments */
Here are a few more:
* The ability to store the input field values that are entered in custom dialogs into variables. From what I can tell, you have to store these values in fields. (Sure, you can use global fields, but I don’t think you should have to.)
* The ability to actually display global variable ($$) values. Perhaps using a merge field, such as: <<$$FM_Wish_List>>
* The ability to specify the size and position of a custom dialog box.
* The ability to use more than 3 fields in a custom dialog box. If a scroll-bar is needed, so be it.
* The ability to use drop down lists, pop-up menus, checkboxes, and radio buttons — as well as value lists — for the input fields. And you might was well throw in the “auto-complete” option while you’re at it.
* I could go on for days about tab controls, but let’s start with some easy ones: a real “go to tab” script step, and the ability to specify a tab’s name using a calculation (without having to use any work-arounds).
* How about this one — support for something like CSS-like stylesheets.
We can dream, can’t we?!?
All good stuff, Tim.
Another in the “Access has done this for years” pile:
How about true Boolean data types? (I haven’t seen much criticism about this, so maybe it doesn’t matter to others.) I want the data engine to take care of that stuff. I also want to place a Boolean field on a layout and have it default to a checkbox, a real checkbox that can ONLY be True or False, 1 or 0 (or Null, I suppose, although FileMaker doesn’t support nulls, not really). Having to program these things over and over again and hope that some snarky user doesn’t find a way through it should be unnecessary in 2006.
And Amen to the comments on tabs. I’m using them and liking them for what they are, but they’re still primitive. One revealing comment on tabs is that I’m using them in conjunction with the old-style make-believe tabs because the FileMaker tab control object provides so little functionality.
I know that FileMaker’s whole thing is the compromise between easy and powerful, but sometimes I want powerful more than easy. Anyway, the hoops one jumps through to implement certain things in FileMaker IS NOT EASY — some things are actually a lot easier in Access once you’ve taken the trouble to learn the language. Maybe I’ll always be unfairly critical because I learned Access years ago, then felt like half of my tools were taken away from me when I moved to FileMaker. With FileMaker 7 and 8, they closed a lot of distance — and some things like the Relationship Graph are even better than I remember from Access — but overall, FileMaker still has a way to go before I feel like I’m working with a complete set of tools.
Microsoft may be jerks, but if Access was multi-platform, I might not be so keen on FileMaker. (There, I said it.)
Interesting on the true Boolean data types. I’ve just always relegated the necessity of a boolean to a yes/no or true/false radio button in a text field and adding an autoupdate calc to make sure that multiple selections are not permitted. Do we really need booleans? Hmmm… if so, lets have ‘em!
Emile,
I do similar error-checking as well. Sure would be nice if we didn’t have to do that ourselves, though.
Ric
I completely agree… At the very least though we can now quasi-modularize the codebase of certain types of DBs with FileMaker 8. I’m consistently reusing a contacts DB template, it’s nice that I can FINALLY reuse it nicely and somewhat easily. Though it could always be easier…
Out of curiousity, a) Does Access have some kind of built-in graphing b) exactly what features did you find easier and/or better? I’m sure there are some, I’m just not as familiar with Access as I am with FM.
Cheers!
Riccardo wrote, “I know that FileMaker’s whole thing is the compromise between easy and powerful…”
I used to think that too, but I’m not so sure anymore. I think that what makes FileMaker special is how it hides complexity from the average user. Think about it: with FileMaker, non-technical users can put together fairly robust solutions with little to no assistance. (I don’t think you could say the same thing for any other database on the market.)
Also, I’ve been thinking a lot lately about some possible reasons that things are still “missing” from FileMaker after all these years. As we’ve said in earlier posts, features and functions that seem so obvious to us — that seem like they should be in FileMaker today — are still conspicuously missing.
Javascript-like event handling (ex: onMouseOut, onMouseOver, onChange) is a big one. I’m sure this is a complicated feature to implement, but still - why isn’t it supported yet? Is it because, by “sandbagging” features like this, they can implement them later and therefore help to justify another upgrade? Or are they afraid that by providing features like this (that you can currently only get using plug-ins), that they’d start to eat into the plug-in market? Obviously, I don’t know the answer. But either way, it makes sense.
Regardless, I find myself using plug-ins more and more these days — something I used to avoid whenever possible. However, my clients are demanding more robust solutions, and some of what they want is only possible using plug-ins.
One last thing: I don’t expect FileMaker to do everything. For example, I am working on a solution right now that requires some very basic image manipulation functionality (resize, etc). I would never really expect FM to be able to do this “out of the box” — and for features like this, plug-ins are certainly acceptable. It’s features that seem like they should be “built-in” but aren’t that make me nuts. And features that seem “half-done” as well, such as dialog boxes, tab controls, etc.
FileMaker still rules, and even if there were a cross-platform version of Access I’d avoid it like the plague. So let’s keep on dreaming up things for the FileMaker Feature Wishlist that Denis has been so kind to put together. Maybe some of our dreams will come true in the next release!
Make no mistake, I want to light a fire under FM’s ass. I love their product but at the same time I expect serious features and revisions if I’m campaigning to get my clients to switch from their v6 setups to v8 .
There are great resources and what not to aid developers but I REALLY want to avoid a release that can be juxtaposed to 5.5 to 6.0 which had most of us asking “ok, what changed?”
I think the majority of these ideas that we’re putting forth are great and could put FM above and beyond Access, 4D, etc. By at least vocalizing much of these ideas, I can work on getting them to FM in a serious and professional manner and hopefully get someone to take notice of what we want and expect.
I still remember *vividly* a user asking on the newsgroup (a couple of years ago at this point), when v5 would be coming out because he was looking forward to finally getting tooltip functionality (interesting assumption, no?)… many of these things seems so self-explanatory, but alas maybe no one is really explaining them and communicating them well, that we have to wait 4 years and 4 revisions to really get them.
Ok, here’s another one:
* The ability to hide and show a toolbar (the text formatting toolbar in particular) via a script step.
Seems like that one is an oversight. (And to be honest, I didn’t even know that you couldn’t do it until a few minutes ago!)
I completely agree, using variables in stored finds would really help with my scripting.
Also better DDE functionality. Being able to Send DDE commands would be great. I’ve just started looking into it to create a Database that runs alongside our CRM system. The idea being that if the user opens the database in the CRM system (Goldmine) the current record will automatically open & vice versa.
I keep on thinking that I haven’t read the documentation correctly. So yes, clearer help files would be great, sometimes FM doesn’t make clear exactly what it means.
(That said looking up at the grammar I’ve used, the fault isn’t just with FM! Please forgive, it’s been a long day.)
What about php and web?
One thing that I would really like to see is the grouping of records in a list view. Where you get a little +/- signs button at the far left. You see this on most MS software, Access, excel, sharepoint. Why not FMP.
I think you mean a hierarchical view of data. I know a LOT of developers have developed shareware modules that emulate the look and feel of something like this, but nothing that is perfect. Submit a feature request at Filemaker.com
Bit late to the party but I’d like to see the ability to disallow text formatting for fields. Not sure if this would be set in the field definition, or on the layout (pros & cons to both) but sometimes I’d like to stop users changing the font to MS Comic 14 point purple italic.
Hmmmmm, you could do a couple of things. First, you could maybe disallow menubar access and then also put an auto-update validation calc on the field that removes any formatting or keeps it a certain color, size, and font. I’ll add it to the growing FM wishlist
Here’s one -
Tha ability to create field names dynamically with calculations.
Recently, I had to transfer a 5.5 DB to 8.5 and the original creator made a database with 30 fields called “ingredient1, ingredient2, …etc.”
I had to transfer it to portal rows.
It would have been nice to make a loop like:
Loop:
set variable $counter 1;
set portal row last FMfield(”ing” $counter); [”text”] //my dream!!!
$counter ;
Exit loop if $counter > 30;
End Loop;
I know you can do that with evaluate, but it doesn’t work with set field script step.
Here are a few ideas. How about a script being able to get the name of an object that called it, this could let me store all of my script parameters in a variable and look them up using that object name as a key.
Or how about in field definitions, the ability for object introspection. Then when fields reference themselves for auto-enter calculations and I duplicate the field, I won’t have to change the calc every single time.
My first reaction is to ask if this was really an UN-normalized set of fields, in other words is this really a list of, say, recipe ingredients?
My thought would be that the contents of these fields should all go into a single field in a child File linked to the parent file. If this IS recipes, say, then the link field for each File might be the name of the recipe.
[PARENT FILE]
misc. fields
Recipe Name Recipe Name
Ingredient
…whatever
And if this not about recipes, you can figure out how else it might be named. it’s been my experience, however, that when someone has named fields something1, something2, etc., that’s usually un-normalized data.
By normalizing the file, you can then create things like ordering information (how much total butter will all the recipes need x the number of times a recipe is ordered) and so on.
Ric
It looks like my attempt at diagramming the Parent and Child files has not made into the post. I will assume you understand what I’m getting at anyway.
Hello,
I am working at a non-profit organization where a number of the people that work there have problems with Excel. We have over 9000 members in our database. We use to work with Access, and then went to Donorworks. Both are good if they are used properly. In the past, we have not used either one very well, so instead of trying to learn them, they are switching to FileMaker Pro.
We now have a person who has never done database in his life working with FM. They have a very hard time following directions, and I just don’t think they are going to get what they were looking for in FM. Can you take data and send it to Excel? What are the good points in FM? I guess I would like to learn how it simple reports can be created. In Access we have worked with Word to create form for a phonathon. Can this be done in FM?
Ralph,
You’re looking at a situation that I would argue is one of FM’s best purposes: the simple and straightforward management of data from many users . I would not put a solution like this on the shoulders of someone who has never used FileMaker and “has a hard time following directions.” Regardless, FM is very forgiving and can certainly do what you are looking to do.
To answer your questions:
- Yes, FM can send/export data in Excel format VERY easily.
- There are many good points of FM, most notably the forgiveness in managing data with multiple users. (9000 records is relatively painless for FM to handle)
- Reports are certainly doable but there are different speed constraints. It basically comes down to whether you need data from a basic Find Request or something more complicated with averages and summaries of multiple forms of data. But yes, reports can be created simply using scripts
- Better than requiring Word, a form can be set up using Instant Web Publishing and the only thing users would need for the phonathon would be a computer with a web browser like Firefox or Internet Explorer.
Let me know if you need more info.
A way that I have used to pull data into Excel from FileMaker is by use of Excel webQueries. I would use PHP to pull the data out of FileMaker, and then call the web page from the Excel WebQuery.
This allows you to do quite a bit of formatting control from the PHP side. Not my favorite way, but it did seem to work fairly well for one way report generation.
I wouldn’t suggest using it to submit data to FileMaker, although that is also possible.
Hello!
I am looking for a really simple feature: to access fields within a script, I need to access fields not by name (or not as normally done), I have the name of the field stored at a variable and want to use the variable to read and write to the fields.
In my solution I am speaking of about 20 fields and I have to make many “IF $Variable IS ” use field “abcde”. This can’t be the proper way I think.
Best regards, Patrick
Hello,
Your best bet is to use the evaluate function.
Let’s say you have a field called “myfield” that contains the text “firstname”, and first name is the field you want to get.
You should be able to do something like:
evaluate(”myfield”)
and that will get the contents of the field firstname.
I forgot to mention, that I have to read and to write to this fields.
One other thing I also don’t get running proper: I would like to make a relation like table1::field1 contains table2:field2
If you have many people and some groups but the people can be member of differant groups and also changing during the years. And I want a relation between the person and his groups at the moment (to know how much he has to pay for example). Maybe there is an easy way, with functions no problem, but as an relation I could not do it
Patrick,
You cannot connect unstored calculations and relate the tables.
All fields that you relate have to be stored calculations or regular fields.
Just take the example to have a table with persons and all usual fields (name, phone, adress, … and groupmembership) and a table with groups (groupID, groupname, meetingtime, purpose, … and allmembers).
So for the users field “groupmembership” I made an optionsfield with all groupnames (so each new group is also listed in here). But on the other way round. looking at the groups-table I also want to now (automatically) which persons have joined this group. I cant relate with the field Persons::Groupmembership because the most people are in more than one group.
All fields are text fields without calculation. For the moment I have written a script to update the list of active members for each group, but this is not very satisfying and therefor my wish to build up a relation between tables if the value of one field is found in the field of the second table.
From a users point of view, when we hire developers, we want a solution that can be upgradable and maintainable. How can we even hire a filemaker programmer residing in some remote corner of a filemaker forum to get any useful work for us.
We looked into a couple of databases before finally settling with Access and MS SQL. Some issues which avoided us from choosing filemaker for our hospitals located in Asia and Middle East were
1. Easy availability of developers in regions outside of US and Europe. More competitive developers to choose from, better quality of products we get. FMP Developer’s competition? They are just struggling to survive.
2. Inability of FMP to integrate well with other office applications like Excel or Word.
3. Access is easy enough for computer literate people willing to start learning a database and later find the info useful for bigger databases.
- thing that we get done using Access and VB will require you to learn stuff like - Applescript/VB (Mac/Win), UNIX, perl, java…. Good for computer engineers and techie people but for people from other professions its too much work to use a database.
Got a mac and need a database to fool around in your new shiny computer… Go learn filemaker. But the concepts you learn in filemaker like Table of Occurences and concepts you missout in FMP like SQL Queries can seriously limit your ability to work on other databases should a need arise.
My experience:
I started fiddling around with FMP6 (when each table was an individual file) in 2005 because I found myself in a position that required I keep track of a ton of data — from employment to educational records to research. I was able to pick it up relatively quickly given its intuitive interface. All you need is a minor background in logic. I made some databases that are still being used in my old department.
Fast-forward to today: I’m in another position with a department who needs a couple specialized databases but will not consider paying for FMP. I am forced to learn Access and I’m hitting walls around every corner. Things that required fiddling with settings or options in FMP require actual coding in Access.
If I had my choice, I’d stick with FMP solely because I don’t need to know any programming language. If you know logic and can see the table-relationships in your head (or a handy white-board) you can make anything happen.
I’ll agree with everyone else — If you’re not a systems professional and need a database, learn FMP. If you’re looking for a program to learn so you can potentially make a career move, learn Access.
The one thing I need badly in FM and cannot believe I do not see much comment about, is CROSS-TAB reports/views. This is one of the most basic of database functions and it is entirely absent in FM, while intrinsic to Access. There are some very tricky scripts and workarounds for FM but as yet, I have seen NOTHING, not even a comment about this giant gap. It’s a shame because FM has come such a long way and I love it, but to me it will not be a fully-functional database until that feature is part of it.