Importing from old copies of same database

[Hijack]

Hello all,

I have a FileMaker solution I am planning on rolling out to several small companies. I know that over time, I'll be enhancing the database to include new tables and data that are not present in older copies. When creating a data migration script to take customer data from an older version to a newer version, I've found that FileMaker has trouble when you script an import and suddenly the import source doesn't have the table you expected to be found.

FM will default to the next available table and just carry on happily. Of course, if you set "Matching Field Names" as the import arrange by method no fields will match (normally) BUT, FileMaker will create a blank record in the target table for each record in the mismatched source table. This is very frustrating.

Is there a way to query the source database and grab a list of all table names or table instance names ahead of time to do some error checking so that new tables with no corresponding source table from an older copy of a database can be skipped?

TableNames is a promising function but that will only display the table names from an OPEN database. I need to be able to query a closed file.

Thanks!

[bcooney]

"suddenly the import source doesn't have the table you expected to be found."

How can that happen? You know the structure of all versions that you've released.

[Hijack]

That's true. But, consider this scenario. A newer version of a database contains a table called "Table10". In the migration script in the new database you have 10 steps.

Import from Table1 in source to Table1 in target.

Import from Table2 in source to Table2 in target.

Import from Table3 in source to Table3 in target.

and so on... until all tables are covered.

Now, the old copy of the database you are importing from only has tables 1-7. But, when importing, the migration script calls all 10 import steps so tables 8, 9 and 10 don't have sources tables. So, those import steps default to table 7 in the source, which is the last table available.

I don't want to have to create a separate import scripts to cover every version combo of source tables that might be encountered. Over time, the source may have 7 tables, 8 tables, 9 tables, etc. Some folks may have a really old copy of the database while some people may have a copy that is just 1 release behind. I'd like to query the source and find out if a table exists and then skip that import steps if the table is missing from the source.

[bcooney]

"TableNames is a promising function but that will only display the table names from an OPEN database. I need to be able to query a closed file."

Why not open it? At least for this query. Otherwise, you'll need to identify the source version and then conditionally import (which, as you say, you'd rather not do).

[Hijack]

The TableNames function relies on the database name as a parameter. If I open the older version AND the new version, I'll have two databases with the same name open at the same time. I haven't tried this scenario but I'm betting that the database that was opened first will be the one that the function hooks into to return the table list.

Plus, the database relies on local account authentication so users would have to auth again and there is a fairly beefy startup script that runs to prep the environment that I'd have to think out if it were running as a secondary opening over a database with the same name with layouts with the same names opening. Then, how to close that database when it would have layout names with the same ones as were already open with the copy I want to keep open and yadda yadda yadda. Now my head hurts again.

FM could admittedly be greatly improved with some more complete import functionality for developers who make turn key solutions that they plan on later updating. I don't think I've ever seen an FM solution vendor who has a good, customer side self-upgrade mechanism. All the ones I've encountered have had to manually migrate customer data to a newer version of a database for them.

[Vaughan]

Now, the old copy of the database you are importing from only has tables 1-7. But, when importing, the migration script calls all 10 import steps so tables 8, 9 and 10 don't have sources tables. So, those import steps default to table 7 in the source, which is the last table available.

It's called version control.

The import process will have to recognise which version the "old" solution is and apply a suitable migration routine accordingly. The updater may require a separate import process for each major release of the solution, and it may need to cascade these as the solutions become older.

One routine cannot be expected to work for all cases especially when there have been significant schema changes.

[Hijack]

It's called version control.

urg.

Just one or two simple functions would make that task 10 times simpler. Just being able to draw out the table names from an unopened file would be a huge step in the right direction.

I could come up with another 2 or 3 functions along those lines but I'm sure we all have our wish list of dream functions that FM currently doesn't have. Another function would be "Import all matching tables and all matching fields" in a single script step.

[Vaughan]

No need for new functions. The simplest method is for the developer to include a version number in the database, then script the update process to read this version and work out what to do.

While an "import all tables" functions would be nice it wouldn't really help for a solution that is getting schema changes. If the old solution doesn't have some tables then some kind of defaults will probably need to be set up in the new solution to make it work. Or, the migration might need manual intervention.

[Vaughan]

Just being able to draw out the table names from an unopened file would be a huge step in the right direction.

For security the files are encoded so they cannot be simply read with a text editor.

[bcooney]

I don't think I've ever seen an FM solution vendor who has a good, customer side self-upgrade mechanism. All the ones I've encountered have had to manually migrate customer data to a newer version of a database for them.

http://fmforums.com/forum/showpost.php?post/234474/

I've had success through about 5 versions with this routine, with very computer-novice users.

[Hijack]

http://fmforums.com/forum/showpost.php?post/234474/

I've had success through about 5 versions with this routine, with very computer-novice users.

Thanks for the example! While it's not exactly what I'm trying to do, I did take away a good dev technique I hadn't considered which is using a global container to prompt a user for a file reference and then pulling out the file name and path for designating the import source file. That in itself is a great tip.

Your FileA and FileB examples had the same table structure. I'm looking for a solution for when the table structure is dissimilar. I need to be able to detect that dissimilarity and import conditionally based on the difference.

Vaugn, I did have to default back to evaluating the conditions based on a version number which I have embedded in my solution from day 1. I was hoping to get away from that and detect based on the actual table presense or absense of target tables from the source mainly 'cause I'm lazy. Now I have to maintain a more accurate version history of when certain tables where added, which, again, I have done but sometimes my chicken scrawl is hard to follow.

Thanks for the advice, all.

[bcooney]

The "select source" into a global is useful for users that move the runtime files from the installed location. Typically, they have not moved anything and so the "auto-find" works for most.

In the commercial solution that uses this technique, I also name the file with the version number, so when the user selects their source file, I now know the version of the source and branch accordingly in my scripts. I just include an import for the tables that were in the source version to the corresponding tables in the new version.

Edited October 11, 2010 by Guest