Data Separation and calculations

[cincin]

Hello,

I am trying to setup a fairly complex DB for data separation in FMP 8.5.

I would like to ultimately end up with 2 files:

- The data file which would contain only :

*The data tables, with only data fields, keys and log fields (creation date etc...).

*A table for user preferences and other user globals

*A table for the user specified value lists

- The GUI file which would contain everything else: layouts, graphics, scripts, non-user value lists, cascading delete structure, all calculations but NO user data.

The obvious advantage being that I can distribute the solution and update it at anytime simply by replacing the GUI file.

Of course, so as to allow expansion, the data files will include pre-created generic data fields of all types (text, numbers, dates etc). I am thinking 10 to 20 spare fields in each table. The GUI file will contain an informative developer table to keep track of what all those spare fields contain once in use.

The calculations is where it gets spicy. At this point I have considered a first approach which goes as such:

A Calc table in the GUI file that contains a single record and a large number (large enough to allow expansion) of text fields per table in the data file, properly identified as belonging to a specific data table and numbered, to store all the calculations, along with other fields to store developer information regarding the calcs.

A number of pre-created calc fields of each type in the data file's tables that are all preset to Evaluate() their equivalent in the Calculation table of the GUI file.

This way I can have the layouts in the GUI file, showing records from the data file tables with all the calcs being evaluated properly from the data table's point of view. I can replace the GUI file anytime to update the solution granted that I don't run out of spare data fields or spare calc fields.

I can update the calculations, scripts, layouts, cascading delete structure, relationship structure etc. The only things I can see not being able to do is to create new auto-enter fields, serials, lookups, summary fields or to change validation options. Most of those behaviours can be replicated by scripts.

The main concerns that I have with this model are:

- Only unstored calcs are possible. Since all the calcs in the data tables are evaluating the fields in the related Calculations table, they cannot be stored and so cannot be used to form relationships. FMP 8.5's cool multi-key relationships does render the use of concatenated keys pretty much obsolete but I'm not sure I won't need stored calcs at some point.

- No taking advantage of FMP 8.5's auto-enter calcs. Placing the Evaluate() in the auto-enter calc option of fields of the data tables simply doesn't work. The calc never gets evaluated.

So then the question is: How else can I separate the data ?

Any other proven efficient data separation models out there ?

Is the model described above really the way to go ?

Am I missing something somewhere ?

Hints, tips and considerations are greatly appreciated as I want to make an educated decision before building my solution.

I took a good look around looking for previous posts or published white papers on the subject but couldn't find anything that would shed light on my concerns.

Thank you all for your help.

[jamesducker]

My two penn'orth:

The only real benefit of data separation seems to be that in the event of corruption etc you can go back to an earlier version of the GUI and have no problem loading the data in.

Having tried both separated and non-separated projects over the years, experience tells me that this benefit comes at the great expense of efficiency and ease of programming.

All my projects automatically export all the data from all the tables every night (usually to Excel files and, to preserve container fields, FileMaker files too). So if anything goes wrong I can go back to an earlier version, clone it, reimport the data from the backup, and all sorted :-)

FileMaker is designed to keep data and interface together. It is much easier to work with it than try to fight it.

James

[Genx]

You seem to think that the separation model refers always to the split of interface and data into two distinct files... It doesn't.

http://www.fmforums.com/forum/showtopic.php?tid/170155/tp/3/

http://www.fmforums.com/forum/showtopic.php?tid/184392/

3 Potential Benefits that i can think of right now:

1) Speed over LAN / WAN

2) Differing Backup Periods

3) Re-curring use of particular "modules" between files

P.S. I agree with you that in the case of small projects, using the separation model is more often than not, overkill... But i don't think we should be putting a taboo on the Separation Model.

Edited February 25, 2007 by Guest

[HissingSid]

Hi,

I'm working on exactly the same model having just took the plunge to 8.5. UI in one file with one record and loads of calcualtions and scripts which is intended to be installed on each client computer. Data in a separate file which will eventually sit on a FM server.

I have a problem which I'm sure is simple but I'm missing something and hope that one of you guys can help.

I can update my relationship key so that my calculations work on the current record using scripts but I need to be able to have the relationship key base on the current record in a found set.

Here is the scenario. User goes command f and does search. Found set is returned. My primary key is a global number field which retains the last value entered by a script but I want it to update to match the key in the current record from the data file. Then all of my calculations will be applied to the data in the current record.

Can I only do this with a script? Do I need to use an "on event" plugin to trigger a script to do it or is there some way I can make a calc field update automatically without a script?

I hope you can help.

Thanks

Sid

PS I'd like to thank Genx for his posts on separation. This model of data on server UI on client computer is the answer to a major issue in our business plus we get the benefit of only moving data around the network not UI.