Converting a FM9 table to MySQL View with minimal effort

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 Database 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

Micah writes:

> 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 Database 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

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

Jens Teich wrote:
> Micah writes:
>
>> 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 Database 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
>

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

Thanks,
Micah

Micah writes:

>>> 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 Database 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.
>
> 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

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

Jens Teich wrote:
> Micah writes:
>
>>>> 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 Database 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.
>> 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
>

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

In article <48ab288e$0$26125$9a6e19ea@news.newshosting.com>,
Micah wrote:

> Jens Teich wrote:
> > Micah writes:
> >
> >>>> 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 Database 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.
> >> 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
> >
>
> 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

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?

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

Bill wrote:
> In article <48ab288e$0$26125$9a6e19ea@news.newshosting.com>,
> Micah wrote:
>
>> Jens Teich wrote:
>>> Micah writes:
>>>
>>>>>> 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 Database 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.
>>>> 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
>>>
>> 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
>
> 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?
>

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

Micah wrote:

> 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 ?

Philippe Manet wrote:
> Micah wrote:
>
>> 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

On Aug 20, 12:01*pm, Micah wrote:

> 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