"No Holding Back FileMaker Blogging"


Navigation:


Support this site by clicking on a sponsor below or becoming a patron!



Beginner, Intermediate and Advanced Video Training



Become a patron of this FREE web site!


Recent Blogs:

Window Locking
Window Locking

Everything Changes
Everything Changes

Subsummary Unique Count
Subsummary Unique Count

Scripted Change Log
Scripted Change Log

Abstracted Log
Abstracted Log


Meta-Consulting
Meta-consulting is a service Database Pros offers to help other developers over programming hurdles via GoToMeeting. Email John Mark Osborne or call John Mark at (909) 393-4664 to find out more about this service.


Tips & Tricks Videos


Quick Tip:

Tab Character
The Tab key moves from field to field in a FileMaker database while in Browse mode, but it does not insert a tab character into a text block, field or calculation formula. To insert a tab character into a text block, field or calculation formula, hold down the Option (Macintosh) or Ctrl (Windows) while typing the Tab key.



Create a Complete Contact Manager


Fun Stuff:

FileMaker, Inc.
FileMaker, Inc. or FMI is a wholly owned subsidiary of Apple, Inc. It was once named Claris when it also sold MacWrite, MacPaint, Claris Emailer, ClarisWorks, etc. but changed it's name in 1998 when it focused on FileMaker. FileMaker started out as a DOS application called Nutshell but was shaped into the powerhouse it is today by Claris and FMI.



Document Management Videos







RSS Feed
To Separate or Not
Level: Advanced
Version: FileMaker 15
Category: General
Thursday, February 16, 2017
Thanks to Steven Blackwell and Colleen Hammersley for sharing their separation model expertise. Without it, this blog entry would not have been possible.

To Separate or Not

Professional developers pay attention to the number of files in their FileMaker solutions because it’s a crucial factor in good database design. Most developers utilize the standard single file structure because it makes development easier. On larger multifaceted projects, a developer might use several files for separate areas of the company. A smaller set of developers like to use the separation model to avoid issues with updates to their solutions and other benefits that will be explored. It’s very important to choose the file structure the best suits your style as well as the project at hand.

To separate or not to separate, that is the big developer debate. Before I debate my point of view, let’s define the separation model. It’s actually quite simple but very different from the standard single file development path. Instead of using a single file, the separation model places tables, fields and data in one file and layouts, scripts and other interface components in another file. The user never sees the data file, always working with the interface file. This is possible because FileMaker files can reference external FileMaker files as if they were local. There is no speed degradation. You can create layouts in the interface file based on data tables and then add records into the data file from the interface file. You can search data records from the interface file the same as if you were actually in the data file. The user will never know the difference.

Proponents of the separation model site the main advantage as facilitating offsite development without requiring an import. In the traditional single file development process, changes to a solution can be done in two ways. First, you can develop directly on the live file itself. Or, you can develop offline and then import the data from the live version.

Live Development
The easiest approach is development on a live system. Of course, you don’t want to start a new project on FileMaker Server. I’m only talking about the inevitable changes that come six months down the line. Clients never know everything they want even with the best planning. Changes to a live system are immediately available to users, which is both good and bad. With changes like adding a report layout or a script, there is a big wow factor when the feature just appears. You can make the change and nobody will know you were developing till you reveal the last step such as adding a button to a layout. You don’t have to amass all the changes over months and then release it all at once.

BTW: The ability to make schema changes in Manage Database (Define Fields back then) on a live solution hosted by FileMaker Server was added way back in FileMaker 5.0v3.

The key is not to add something that has the possibility of causing havoc. For example, imagine creating or editing a script that contains the Delete All Records script step. What if there is a bug and the script starts deleting all the records in a table. This is the fear all developers contend with when developing on live systems and why backups are so important. It is an especially big concern when editing a script that is currently in use. What if someone runs it before you are done. Of course, you can disable the script or work on a duplicate but these scenarios need to be worked out ahead of time rather than learning the hard way.

Most developers, including myself, work on live systems because it is convenient for the client as well as the developer. It is also very reliable but there is still a chance that something could go wrong. If FileMaker unexpectedly quits due to software conflict or the power going out, there is always a chance of corruption. Most developers will revert to a backup if a FileMaker file ever crashes. Better safe than sorry. Therefore, it is a good idea to constantly backup your solution. Back up your solution as often as you are willing to recreate lost work. And, you can’t rely on standard backup systems because backing up an open database can create corrupt backups. Only use FileMaker Server to backup.

The most volatile time for a FileMaker file is during a commit. In the past, FileMaker used to have more corruption issues because it was saving changes as you made them. Since the advent of FileMaker 7, FileMaker only saves when exiting Manage Database and other modal dialogs, Layout Mode and when scripts are saved. A crash during this process of committing changes is the most common cause of corruption. Avoiding crashes is the best way to avoid corruption. Developing on a live system over a network or the internet prolongs the commit process, increasing the chance for a crash to interrupt the commit process. In addition, the chance of being dropped over a remote connection is always a possibility that can be avoided by developing in single-user mode from your local hard drive. I say local hard drive because I have seen many developers develop from a shared server thinking they are safe. FileMaker is a hard disk based system so working from a shared volume in single-user mode causes the same latency problem as working on a multi-user live system. The moral of the story is make sure you have a fast and reliable internet connection.

Live development also needs to contend with record locking. If someone is modifying a record and a developer needs to edit or add a field, the user will need to commit the record before the developer can exit Manage Database. While this won’t necessarily cause corruption, it can slow down the development process. In addition, there are enough moving pieces in a live environment that it is difficult to predict the impacts to data that live development could cause.

While FileMaker, Inc. recommends developing on a local copy of a file when asked directly, they do not publish any white papers regarding best practices. It is quite clear that live development is a valid approach simply by the fact that it is not blocked by the program. I have been working on live systems for over a decade and have never had any problems with corruption. It is a convenient development style that you should consider by weighing the upside and downside as directly related to the specific project and the needs of the client.

Importing
I personally know several developers who will not develop on a live system. These developers have two choices left. Either they can choose the separation model or they can import data when a solution is updated. With the import approach, the developer works offline on a copy of the solution and then imports the current data into the new version of the solution. This allows for the convenience of a single file solution, which is the easiest and most natural development method for FileMaker. Combining interface and data into a single file is what makes FileMaker the program that it is. Removing it’s greatest strength should not be overlooked.

The downside of working offline with a single file solution is importing the current data. While the script to transfer data is not that complex, it often takes a long time to import the data. Imagine tables with millions of records. Importing could take all night. Yes, I said night because you will need to do the import while all users are offline. In addition, if you have one mismatched or forgotten field, you will have to start over again, if you even notice the mistake.

Updating by import also works best by collecting feature additions and enhancements over time. While this requires users to wait for features, it also gives the developer time to test a feature more thoroughly. While programming can be tested in a live system, any mistakes will be felt on live data, possibly requiring a backup. With careful planning, thoughtful programming and good testing, live systems can be safe but never as safe as programming offline.

Separation Model
You are beginning to see why developing on a live system is so convenient when compared to working on that same solution offline. The separation model fits nicely between the first two approaches. It attempts to make updating a solution easier when working offline. For example, if you want to add a new report, that is done in the interface file. When you are done making the changes offline including layouts and scripts, simply replace the old interface file with the new one and you are done. It’s that easy! No importing required.

But, the development process is never that straightforward. I almost always add a new field when creating a report. It’s impossible to plan ahead for every possible change you might need to make in the future. If any change includes a field or a table, the data file will need to be modified, putting you right back where you started with the importing of data into the new version. Separation model advocates say that in a properly planned solution, tables and fields never need to be added. When pressed, proponents often reveal that extra generic fields of every type are added to every table to accommodate possible changes, Feature creep is a common enough occurrence that it has been given a name. I have been servicing clients for decades and one thing remains consistent, they always add more to a project. Not only that, these generic fields need to be modified to the right type and functionality, bringing separation model advocates right back to modifying a live system, or at least bringing down the FileMaker system to work on it locally. While the separation model approach can absorb some changes to the data file, it doesn’t allow complete flexibility.

In addition, the separation model is a cumbersome and inefficient development model. The reason FileMaker doesn't automatically separate the data and the interface is because it is much easier for most people to work with a single file. It's difficult to demonstrate how much easier working on a single file with a single example but let me try. Let's say you are creating a report layout and need to add a calculation to organize a report via a subsummary part. In a single file scenario, you just need to enter Manage Database, create the calculation field and exit Manage Database. In the separation model, you need to switch files, enter Manage Database, switch the field tab to show the correct table (in the single file scenario the table attached to the layout where you are displays in Manage Database automatically), create the calculation, exit Manage Database and switch back to the other file. Repeat this procedure hundreds of times and you can see how cumbersome and time consuming it is when compared to the simpler single file process.

IMHO
You are probably reading this blog because you want my opinion so I’m not going to beat around the bush. I heartily dislike the separation model. I really don’t get why developers trade the simplicity and efficiency of a single file solution for a two file solution. Combined with live system development for unexpected additions, I think it is the ideal development approach.

I think the best way to explain my issues with the separation model is with examples. The first example is doubling of effort. Many things need to be done twice including security, scripting and some relationship graph elements, just to name a few. For example, every time a password is added to the interface file, it also needs to be added to the data file. The best solution is to write a script but that takes time and takes away from the convenience of simply adding credentials to a single file. Active Directory or Open Directory can be employed but that really is only an option at larger companies with an IT staff. FileMaker is mainly a small to medium database solution. In addition, different privilege sets still need to be defined in the two files. The interface file security protects layouts and scripts while the data file protects tables and fields. This splits the interface for security into two places, making a more complex interface.

One of the ways developers make money is by developing efficiently. The separation model requires that tables and fields be added to one file and layouts and scripts be added to another file. This process takes away from the natural development method in FileMaker. How easy is it to add a new field to a table in a single file scenario. It is best understood when viewing how difficult it is to add one in the separation model. Imagine you are working on report layout and need a new calculation field to attach to a sub-summary part. You can’t just type Command-Shift-D or Ctrl-Shift-D. You have to first select the data files and then enter Manage Database. This process only takes a few seconds but multiplied by the thousands of times you enter and exit Manage Database and all of a sudden you have added hours onto your project, not to mention interrupting your train of thought.

I make money by programming efficiently and the separation model slows down that process. Separation model proponents say proper planning avoids this issue by adding all your tables and fields at one time after a lengthy planning process. This takes away from the natural strength of FileMaker which is forgiveness. You can add, delete or edit a field or table at any time and FileMaker file will update references throughout the entire solution. Some aspect of organic development is what makes FileMaker unique in the marketplace. Don’t take that away. Plan your solution, especially in areas like relational structure, but also plan on your solution growing organically in other areas. Too much planning ends up costing time and money.

Other benefits of the separation model include the ability to have different interface files for different users or devices. Again, this complicates the entire process. I prefer just to have separate layouts in a single file to accommodate different interfaces. This allows you to work on multiple interfaces without switching to different interface files. Not to mention some interface elements like scripts often transcend device types and would need to be programmed multiple times with different interface files.

Author:
John Mark Osborne
jmo@filemakerpros.com
www.databasepros.com

This blog is completely free. Please support it by clicking on one of the advertisers at the left side of the window or becoming a patron. Thanks so much!

Comments:

James 04/20/2019
  Thanks All for your help. John Mark Osborne's comment especially helpful, thanks.

Patrick Shepherd, re ''Data separation was native to OMNIS DBMS. It was easy to fix problems and add features, ..... '' - same with FileMaker Pro, very easy to sync live and dev. One click and the scripts upload all the live data to the dev. - I think that is why FileMaker has all in one - just a smart idea. But thanks for comments. James

James
Response by:   John Mark Osborne 04/24/2019
Well said. If FileMaker was meant to separate the interface and data then it would be inherent in the design. The main reason developers offer for using the separation model is it prevents time consuming script designed to import data into a new version. I think that argument is vanquished by the appearance of the Data Migration tool. Might as well use FileMaker as it was intended so you can be more efficient.
James 03/06/2019
  Help / advise wanted pls. I have inherited a separation model FM DB - can I and how, undo the separation thing and get a one file solution?
Response by:   John Mark Osborne 03/07/2019
That's going to be tough to do... unfortunately. There's no best way to attack this type of migration. If you transfer the layouts first then the scripts won't refer to the layouts correctly. If you do the scripts first then the scripts won't be attached to the buttons. Same thing with the relationships but you can't copy and paste them so it's even harder. You just have to create them from scratch, opening yourself up to mistakes. I've adopted many separation model solutions and I just leave them as is. I curse a lot when I'm working on them but it's just too much work to fix them. Not what you wanted to hear but your stuck with this idiotic FileMaker structure.
Brian Panhuyzen 01/24/2019
  Always glad to have my own position on this topic confirmed. Those developers who think the separation model is superior because they can just switch the interface file and leave the data file intact are fooling themselves. It would be true if the data file was just raw data, but as you point out, it also requires calcs and relationships – you have to add these all the time, and if you only do it once your whole "separation" advantage is lost, because you will now have to replace both the interface and the data files. I've inherited several separated solutions and they are pain in the butt PLUS they take more hours to maintain. This FileMaker madness of thinking the separation model is superior has to end!
Response by:   John Mark Osborne 01/25/2019
Ditto! I too am always glad when I'm affirmed. I have also inherited many separation model solutions. Such a pain to program. Thanks for taking the time to add a comment. I really appreciate it!
Patrick Shepherd 12/06/2017
  Before networking became ubiquitous in schools, I developed a student management system for elementary and middle schools using OMNIS. Data separation was native to OMNIS DBMS. It was easy to fix problems and add features, send the new app version to the user and have OMNIS update any file structure changes in the users' data files. Do you know why FileMaker has gone a direction very few other programs have gone (Word, Excel, Pages, and others come to mind)?
Response by:   John Mark Osborne 12/07/2017
The reason FileMaker doesn't automatically separate the data and the interface is because it is much easier for most people to work with a single file. It's difficult to demonstrate how much easier working on a single file in this short response space but let me try with a single example. Let's say you are creating a report layout and need to add a calculation to organize a report via a subsummary part. In a single file scenario, you just need to enter Manage Database, create the calculation field and exit Manage Database. In the separation model, you need to switch files, enter Manage Database, switch the field tab to show the correct table (in the single file scenario the table attached to the layout where you are displays in Manage Database automatically), create the calculation, exit Manage Database and switch back to the other file. Repeat this procedure hundreds of times and you can see how cumbersome and time consuming it is when compared to the simpler single file process. Hope this helps you understand why working in a single file is much more efficient.
Dominick Capobianco 04/12/2017
  Well stated JMO and I agree completely. Not a big fan of the separation model.
Edward Callaghan 02/22/2017
  Jmo

I was looking recently at this exact topic and couldn't really get my head round the whole proponents of separation model. I'm now clearer that you and I are both right.
Response by:   John Mark Osborne 02/22/2017
Hey Edward! Longtime no see. I just don't get over to the UK very often. BTW, glad I wasn't wrong lol!

Add Comment:

First Name: *
Last Name:
Email: *
Web Site:
Comment: *
 Email Addresses will not be shared on the web site!