Tables vs. Files

[The Mad Jammer]

I just got a version of FMP 7 and started poking around. I've used version 5 for quite a while and have a pretty good handle on it but version 7 presents some new features that I find a bit odd, like tables. Being a SQL guy I understand the concept behind SQL tables quite well and stored procedures are quite familiar for me. What I dont understand at first glance is the relative difference between tables and files in FMP 7. For instance, if I define a database with several tables in it and link them, what is the difference between that and creating files and then defining relationships between files?

i.e. If I create a database table of Items and then create a cost table and charges table and link them to the items table, how is that any different than creating an items file, a cost file and a charges file and creating relationships among them? I prefer to have the tables of a database all together (ala MS-Access, MS-SQL etc) rather than having separate files for each. or perhaps i don't understand FMP's use of tables yet.

The Mad Jammer

[Ender]

It is a feature of FM7 that you can have multiple tables per file. It is often preferable to use this feature to make script reuse and account management simpler. However, FileMaker will treat relationships to external file's tables as if they were in the same file, just by adding file references to those files.

You should read the FM7 Foundation and Migration Methodologies tech brief on filemaker.com, and search the forums a bit to explore the trade offs of multiple tables per file vs. multiple files.

[T-Square]

I'll chime in to say that I am exceedingly pleased that multiple tables can now live in one OS file.

I can't tell you how many times I have had to walk my clients through re-building their relationships in FM4 & 6 when their systems crashed, taking the Filemaker relationships with them. That doesn't happen when the tables all live in one OS file.

Put another way, when you store your database tables in separate files, you take control of the system partially out of Filemaker's hands, and put it in the OS. What, for example, happens when your client accidently moves one of the files out of the filesystem folder, and leaves the others in place? Chaos, as Filemaker has to ask the user where the file is, and they uniwttingly click to an old version of the database file...

Given the options, I'd go with one file/many tables every time, unless I were building a modular system or had problems with file sizes.

[The Mad Jammer]

Ender and T-Square,

Thanks for your comments here. Of course, I am currently in the middle of creating a new FMP application that will replace a Clarion DOS application I wrote for a client of mine 15 years ago. And of course, the timeline is impossible. So i appreciate your comments about the vagaries of the product.

Frankly, the part I find most interesting is the possiblity of printing things like invoices and other reports that have mulitple records without having to define the report and switch over to a completely different file and defining what amounts to duplicate releationships. That always seemed very forced to me and difficult to work with especially if there are third parties involved. I have created invoices before that had data from 3 or 4 different source files on it and it was a real task getting FMP to print invoice line items along with customer information from a customer file, along with forwarder information from another file and consignee information from another file and etc... I could get all the information together in an Items file but printing the invoice and having to tie all these files togehter was a real PIA.

Now if we could just get the text boxes to size themselves automatically on a report we might have something. Crystal reports has done that for years, and although I understand that FMP is a database tool, MS-Access reporting features are advanced enough to handle growing a text box to the size of the data it contains. It would be nice to see that happen in FMP as well.

The Mad Jammer