Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
Database Servers
DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybase
Desktop Databases
FileMakerFoxProMS AccessParadox
General
General DB TopicsDatabase Theory
Related Topics
Java Development.NET DevelopmentVB DevelopmentMore Topics ...

Database Forum / FileMaker Topics / August 2008

Tip: Looking for answers? Try searching our database.

Converting a FM9 table to MySQL View with minimal effort

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Micah - 18 Aug 2008 21:51 GMT
I want to be able to migrate a large (many columns) FM table to a MySQL
view with minimal FM effort.  I want to normalize the data in MySQL and
present a de-normalized view for FM because of the many scripts that are
written for the current DB structure.  Can I do this by accessing the
MySQL view in FM, and reconnecting all the table occurrences or is there
more work to do?

Thank you,
Micah
Jens Teich - 19 Aug 2008 14:17 GMT
> I want to be able to migrate a large (many columns) FM table to a MySQL
> view with minimal FM effort.  I want to normalize the data in MySQL and
> present a de-normalized view for FM because of the many scripts that are
> written for the current DB structure.  Can I do this by accessing the
> MySQL view in FM, and reconnecting all the table occurrences or is there
> more work to do?

Yes there will be more work to do. FileMaker is not only a database
but also an environment for application development.

-jens

Signature

Free PlugIn for Regular Expressions with FileMaker:
http://jensteich.de/regex-plugin/

Micah - 19 Aug 2008 20:25 GMT
>> I want to be able to migrate a large (many columns) FM table to a MySQL
>> view with minimal FM effort.  I want to normalize the data in MySQL and
[quoted text clipped - 7 lines]
>
> -jens

Ok.  Can you give me an estimate on what needs to be done?

Thanks,
Micah
Jens Teich - 19 Aug 2008 21:03 GMT
>>> I want to be able to migrate a large (many columns) FM table to a MySQL
>>> view with minimal FM effort.  I want to normalize the data in MySQL and
[quoted text clipped - 7 lines]
>
> Ok.  Can you give me an estimate on what needs to be done?

The easier part will be the extraction of the data model. Looking up
the tables and fields and writing a batch file to create a similar SQL
structure is straight forward. As a first problem here you will find a
strongly used type of field in FileMaker called calculations. They
have to be included in the application layer of your new database.

Data transfer will also be not too difficult. You might use a text
based format like csv or a more advanced technique like FileMaker ESS
(External SQL Sourced) for this part. ODBC is required here.

But FileMaker databases contain more: (1) business logic in scripts
calculations and custom functions; (2) an interface layer built of
layouts. Here you need some FileMaker knowlegde to analyse the status.

-jens

Signature

Free PlugIn for Regular Expressions with FileMaker:
http://jensteich.de/regex-plugin/

Micah - 19 Aug 2008 21:41 GMT
>>>> I want to be able to migrate a large (many columns) FM table to a MySQL
>>>> view with minimal FM effort.  I want to normalize the data in MySQL and
[quoted text clipped - 21 lines]
>
> -jens

I'm can make the MySQL view represent the current table and field
structure of the FM table, I'm not worried about that.  Once I do that
and I am presenting the exact same data, what will I need to do in FM to
use that data that is in the exact same order as the current FM table?
Thanks,
Micah
Bill - 20 Aug 2008 12:34 GMT
> >>>> I want to be able to migrate a large (many columns) FM table to a MySQL
> >>>> view with minimal FM effort.  I want to normalize the data in MySQL and
[quoted text clipped - 28 lines]
> Thanks,
> Micah

A basic question: Why do you want to go to MySQL in the first place?  
You say you want to use the scripts (and presumably layouts) of the FM
database, with the SQL tables as data source. Why not just use the FM
database as-is, without going to SQL at all?

Signature

Bill Collins
For email, change "fake" to "earthlink"

Micah - 20 Aug 2008 20:01 GMT
>>>>>> I want to be able to migrate a large (many columns) FM table to a MySQL
>>>>>> view with minimal FM effort.  I want to normalize the data in MySQL and
[quoted text clipped - 32 lines]
> database, with the SQL tables as data source. Why not just use the FM
> database as-is, without going to SQL at all?

Because I want to work with normalized data in PHP.  To convert our FM
system to a normalized data structure will take at least 9 months.  I
can normalize the data and put a new front end on it in 4 months using
MySQL while still presenting a denormalized view to FM for integration.
Can anyone tell me the steps necessary on the FM side to accommodate this?

Thanks,
Micah
Philippe Manet - 20 Aug 2008 23:35 GMT
> convert our FM
> system to a normalized data structure will take at least 9 months.

and what is the point about "normalized structure", that can justify 9
or 4 monthes of specialized work ?
Micah - 21 Aug 2008 00:31 GMT
>> convert our FM
>> system to a normalized data structure will take at least 9 months.
>
> and what is the point about "normalized structure", that can justify 9
> or 4 monthes of specialized work ?

I don't have the desire to explain to you why normalization is
important.  I suggest reading up on it.
Micah
d-42 - 21 Aug 2008 02:28 GMT
> Because I want to work with normalized data in PHP.  To convert our FM
> system to a normalized data structure will take at least 9 months.

> I
> can normalize the data and put a new front end on it in 4 months using
> MySQL while still presenting a denormalized view to FM for integration.

Frankly, I can't really envision a scenario where taking an FM
database, converting it to MySQL, and then exposing the tables back to
FM from MySQL as denormalized views would be preferable or indeed,
faster than just cleaning cleaning up the structure in filemaker.

> Can anyone tell me the steps necessary on the FM side to accommodate this?

You really can't effectively -convert- a solution of more than trivial
complexity that uses local fm tables, to one that uses external odbc
views (from say mysql).

The steps to do it are simple enough:

"All you have to do is"...

define the schema in mysql, export the data, set up your 'denormalized
views for filemaker' in mysql,  setup an OBDC dsn, and then reference
it in filemaker (file -> manage -> external data sources), and then go
through your filemaker relationship graph, and change the data source
for each table occurrence from its local table reference to the
external counterpart.

The problem is that not everything works through an ODBC link that
works on a local table, and there are nuances to everything from
record numbering, record locking, auto-enter, calculation fields,
storage (global fields are really per user, for example), value list
issues, not to mention general performance issues for queries,
relationships, etc, etc.

The odds of just taking a reasonably complex file and converting it
like this and having it actually work well enough to be usable are
next to none.

It would take someone who is a filemaker expert to do it with any
chance of quick success, and even then I would expect it to take
considerable debugging and testing to ensure it worked correctly
afterwards.  And no disrespect, but based on the fact that you are
asking basic questions on how it might be done suggests that you don't
really appreciate the task you are approaching here.

If you've converted a solution from local filemaker tables to external
filemaker tables, you should have some appreciation for the sort of
things that can go wrong. Convertering to ODBC would easily be an
order of magnitude harder.

I'm not saying you won't be successful, especially if you are willing
to hammer away at it, but it will probably take much longer than
you've anticipated.

I'd say you be better off rebuilding the solution from scratch using
the existing one as a functional prototype. That would probably be
true whether you wanted to have the data in filemaker or in an
external database.

-regards,
Dave
Micah - 21 Aug 2008 18:17 GMT
>> Because I want to work with normalized data in PHP.  To convert our FM
>> system to a normalized data structure will take at least 9 months.
[quoted text clipped - 10 lines]
> -regards,
> Dave

I wanted to avoid an immediate overhaul of the FM system because a
rewrite would take years.  I thought if I can present the same table
name, relationships, and data it would integrate with the current
system, but I realize that is not possible now.  I am looking into other
options.

Thanks,
Micah
Chris Brown - 22 Aug 2008 00:37 GMT
>>> Because I want to work with normalized data in PHP.  To convert our FM
>>> system to a normalized data structure will take at least 9 months.
[quoted text clipped - 18 lines]
> Thanks,
> Micah

I agree with Dave, rebuild the FM db from scratch. Think of it as a
template for the input/data display requirements.

The 'de-normalised' bit: the 'de-noramalized' (i.e. the non-normailzed)
FM fields present on the layouts,  can be be (generally) redefined to
use hopped relationships equivalents in the normalized tables... THere
goes the old FM structure...

Just out of curiosity, how may tables and fields are you dealing with?
Micah - 22 Aug 2008 00:56 GMT
>>>> Because I want to work with normalized data in PHP.  To convert our FM
>>>> system to a normalized data structure will take at least 9 months.
[quoted text clipped - 28 lines]
>
> Just out of curiosity, how may tables and fields are you dealing with?

Over 100 tables and 1000 fields.
David Simpson - 25 Aug 2008 23:52 GMT
If you have this many tables/fields, it seems like FmPro Migrator might be
helpful. It will also convert the repeating fields if you have them in your
FileMaker database.

Signature

David Simpson
www.fmpromigrator.com

>>>>
>>>>> Because I want to work with normalized data in PHP.  To convert our FM
[quoted text clipped - 32 lines]
>
> Over 100 tables and 1000 fields.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.