Converting a FM9 table to MySQL View with minimal effort

Jens Teich wrote:
> Micah <micahjg26.RemoveThis@netscape.net> 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 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
>

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

Thanks,
Micah
 >> Stay informed about: Converting a FM9 table to MySQL View with minimal effort 

Micah <micahjg26 DeleteThis @netscape.net> 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 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.
>
> 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/
 >> Stay informed about: Converting a FM9 table to MySQL View with minimal effort 

Jens Teich wrote:
> Micah <micahjg26 RemoveThis @netscape.net> 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 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.
>> 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
 >> Stay informed about: Converting a FM9 table to MySQL View with minimal effort 

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

> Jens Teich wrote:
> > Micah <micahjg26.DeleteThis@netscape.net> 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 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.
> >> 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"
 >> Stay informed about: Converting a FM9 table to MySQL View with minimal effort 

Bill wrote:
> In article <48ab288e$0$26125$9a6e19ea@news.newshosting.com>,
> Micah <micahjg26.TakeThisOut@netscape.net> wrote:
>
>> Jens Teich wrote:
>>> Micah <micahjg26.TakeThisOut@netscape.net> 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 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.
>>>> 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
 >> Stay informed about: Converting a FM9 table to MySQL View with minimal effort 

On Aug 20, 12:01 pm, Micah <micahj... RemoveThis @netscape.net> 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
 >> Stay informed about: Converting a FM9 table to MySQL View with minimal effort 

Micah <micahjg26 DeleteThis @netscape.net> 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 ?
 >> Stay informed about: Converting a FM9 table to MySQL View with minimal effort 

Philippe Manet wrote:
> Micah <micahjg26 RemoveThis @netscape.net> 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
 >> Stay informed about: Converting a FM9 table to MySQL View with minimal effort 

d-42 wrote:
> On Aug 20, 12:01 pm, Micah <micahj....DeleteThis@netscape.net> 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.
>
> -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
 >> Stay informed about: Converting a FM9 table to MySQL View with minimal effort 

Micah wrote:
> d-42 wrote:
>> On Aug 20, 12:01 pm, Micah <micahj....RemoveThis@netscape.net> 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.
>>
>> -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


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?
 >> Stay informed about: Converting a FM9 table to MySQL View with minimal effort 

Chris Brown wrote:
> Micah wrote:
>> d-42 wrote:
>>> On Aug 20, 12:01 pm, Micah <micahj....TakeThisOut@netscape.net> 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.
>>>
>>> -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
>
>
> 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?

Over 100 tables and 1000 fields.
 >> Stay informed about: Converting a FM9 table to MySQL View with minimal effort 

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.

--

David Simpson
www.fmpromigrator.com
"Micah" <micahjg26.DeleteThis@netscape.net> wrote in message
news:48adf955$0$29596$9a6e19ea@news.newshosting.com...
> Chris Brown wrote:
>> Micah wrote:
>>> d-42 wrote:
>>>> On Aug 20, 12:01 pm, Micah <micahj....DeleteThis@netscape.net> 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.
>>>>
>>>> -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
>>
>>
>> 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?
>
> Over 100 tables and 1000 fields.
 >> Stay informed about: Converting a FM9 table to MySQL View with minimal effort