On 2011 Jun 5, at 18:26, Andrew Gordon wrote:

> Part of our confusion is the difference between a file and a table - our view it that they are the same thing, perhaps we are wrong in our understanding of the terminology.  We store data relating to a particular year's production in a folder for that year, containing the 3 files for that year,  e.g. Receival10, Processing10 and Inventory10. Each file may have a number of layouts.  We generally have a "data entry" layout, then create layouts as needed within the file.


Up thru FileMaker Pro 6 (which is what you've been used to using), there was absolutely no difference between a file and a table. Each file contained exactly 1 table, and each table needed exactly 1 file. Relationships were built from one file to another, which was exactly the same as building them from one table to another. People could use the terms "file" and "table" interchangeably.

Beginning with FMP 7, that was no longer true. A FileMaker Pro FILE could contain multiple TABLES, and relationships could exist between tables but entirely within a single file. At this point, it became important to use the 2 terms precisely:
 • A "file" is a general term that has meaning within your computer's operating system. It's a collection of related information with its own name, address, and end-of-file marker. It has direct meaning to your computer's operating system and file-management structure. Examples of files include word-processing documents, spreadsheets, pictures, music, and of course databases.
 • A "table" is a term specific to databases. It's a collection of fields, all of which should ideally have something to do with a single ENTITY (such as people, addresses, inventory items, money, events, locations, etc.). The table is almost always given the same name as the entity. Your operating system doesn't know diddly about an individual table, but to your database manager (such as FileMaker Pro, Access, SQL Server, Oracle, Foxpro, DBase, etc.) tables are basic bread and butter.

There is no reason why you are REQUIRED to consolidate your Receival, Processing, and Inventory tables all into a single file, but I think you'll find that it'll help.

Also, I think you'll also find that it'll help to create a field in each table that will contain the fiscal year as a datum in each record, so you don't have to create new tables every year — you can just keep using the existing ones but perform date arithmetic to do your multi-year processing.

You will be well served to get a copy of FileMaker Pro ADVANCED for the person who does the actual database development. Everybody else can still use regular FMP to access the database, but the person who does the work will really appreciate all the tools and extra power that FMPA provides.