Storing user data in an external data source?

[iwasnevy]

I've gotten a lot of help from these forums in the past, but now I have a few specific questions that I need help answering. I know this is long, please bear with me as I'm trying to understand how this works…

 

I'm developing a database that will be used for daily journaling. The development of the database has become complex enough that there are a couple entire tables dedicated solely to allowing the database to function more like an application on the presentation layer. These tables support things like calculations, navigation, controlling a log-in/change name/change password process, displaying an interactive calendar, and supporting the core of the UI functionality, among many other things.

 

At this point in the development, I'm looking to distribute this to other users in a small group of about 6 people for beta testing. Once rolled out, it'll go to a group of about 100 users.  It'll be run across several platforms - Winxp, vista, 7, Mac OS X, etc.

 

From an application implementation (best practices) point of view, this has become large enough that I feel justified in separating the user's data (the table that holds all the journal entries they will be creating) off into a separate file in the user's documents folder. This way their data is NOT part of the database (application/runtime) itself, which ideally resides in their applications folder, it's safe if only their documents folder is being backed up, and is safe from being overwritten by future application updates.

 

The vision is to have a separate flat child database only for the user's data, which is accessed only by the main parent database, which is a runtime. The user opens the parent database and uses its UI to enter data into fields existing in the child database.

 

In seeing if this was realistic, I've created a flat filemaker database containing the user's data table and set it as an external data source in the main database. In Manage External Data Sources, I've made filemaker calculate the path to the data file relative to the user's home folder using "file:../Documents/my data/test file.fp7".  I've tested it on a temporary user account on my mac and it works despite the different path to the user's home folder, so it looks like it can indeed be rolled out across several different user's machines, at least on mac.

 

I'm not sure if I got this part correct though - I have a feeling it'll break when I go to a windows machine. I was looking for a way to calculate the path using Get ( DocumentsPath ) but it doesn't look like I can reference either a variable or a table field in Mange External Data Sources.

 

So, can I calculate a path relative to the user's documents folder for an external data source? Across platforms? Even if I need to create a fork for Windows with a different filepath calculation?

 

I'm new to using an external data source. It appears as though I need to set the external database as a TO in the parent database with a relationship between them. If I want to navigate from one record in the child database to the next using the parent database's UI, I need to create a one-to-one relationship and have matching key fields in each database. So child record #1 is related to parent record #1, child record #2 is related to parent record #2, etc etc etc. The end result being that when displaying fields from the child table on a layout in the parent table, and I'm looking at parent record #1, I'm seeing the contents of the field on child's record #1. Same with record #2, 3, etc. Did that make sense? 

 

Is this correct?

 

If so, I assume the parent table only needs a key match field, while the child database can contain all the fields (in addition to a key match field) and I simply reference and display them on a layout in the parent database? Or do I create identical tables in both and somehow read the data from the child into the parent and write any commits back to the child?

 

If so, what's the best way to do this?

 

And lastly:

 

What are the limitations inherent in referencing fields in a child table: In calculations, scripts, script triggers, variables and whatever else I'm not thinking of?

 

TIA for any help or insight you all can give!

[truelifeajf]

You can enter in more than one file reference for an external data source.

 

For example, you could put:

 

file:/mylocal_file.fmp12

fmnet:192.168.0.1/myhosted_file.fmp12

 

If the first can't be found, Filemaker will look for the second location

 

So (and I've never tried it) but I can't see why you couldn't put a Mac path and then a WIN path in there. If one can't be found, it'll use the other.

 

As for the rest of your question, I'm not entirely sure what you're asking.

 

I *think* it's just a terminology issue. It sounds like you have designed your solution using the "separation model" which means your UI is one FM file, and your data is in another FM file. The UI references the data in the data file.

 

If that's the case then I'm thinking by "parent", you mean "UI" and by "child", you mean "data".

 

If that's correct then you need no relationship. Normally, all your TO's would come from the SAME file, but in your case, all your TO's will be from the "data" file. Your relationship graph looks no different except you can tell your TO's come from another file because their names are in italics.

 

In terms of the pros / cons of using the separation model... that's been discussed here many times. I love it, I always develop using it, regardless of how big or small the job is. It makes hosting a solution much faster because all the UI elements on each user's workstation and it gives me flexibility in development in that I can change a UI to my hearts content and then give that to all users, without having to worry about any data importing, etc.

 

For me, the biggest downer is that you need almost the same relationship graph in both the UI AND Data file.

 

But for me, I begin development in a single file, creating all the main relationships, key functions, layouts, etc.

 

Then, I duplicate the file and build my separated solution.

[iwasnevy]

Thanks for your reply. Good to know about the multiple file paths, That may be a solution.

 

To the terminology, yes you are correct in that I'm trying to move this to a separation model. The "Parent" database is indeed the UI and the "child" database is the user's data. I'd like to store the user's data file in their documents folder and the UI file in their applications folder.

 

Since this app is still in development, I can continue to make changes to the UI file leaving the user's data alone and not have to deal with importing data with each future update (as you pointed out).

 

I've never built an FM database referencing an external data source, so maybe that's where I need some schooling - it appeared to me that the only way to make the external data source available was to add it as a TO in the UI file's database setup and create a relationship to it. Only then did the fields in the data file become available for use in the UI file's layouts, calculations etc. I'll go do some reading to figure out what I may be missing here.

[truelifeajf]

When you adda TO in a single file solution, you pick a table from the current file.

 

But in a multi-file solution (or the separation model where your "main" file is just a UI file), you pick a table from ANOTHER file.

 

The UI file doesn't really know or care whether you've selected a local table or a table from an external file.

 

Now that you have a TO in your relationship graph from the external file, you can create a new layout and base the layout on that TO and you'll see all the data from the external file.