I don't know of any way of doing this. I'd be leery of making that change. I don't think using SQL in FileMaker is the fastest thing on earth and you may run into some slowdowns.
Here is an old thread hashing the pros and cons:
What happens if you recreate the table in MySQL and then change the data source for the table from FileMaker to the MySQL table? FileMaker may just match up field names automatically, at which point you can start manipulating your MySQL schema.
Thanks for your reply sreese. Speed is a concern of mine alright, so we're going to do some live testing before we decide to go ahead or not. Thanks for the link - it's a good help
That's actually the plan - have all the FileMaker fields available in MySQL - It all falls nicely into place - the problem is when you go to remove a mapped MySQL field in FileMaker which would be a placeholder for a calculation field and add the correct Calculation field, the link is broken
Two pieces of software to look at:
DraConventions 2empowerFM Developer Assistant - Lets your search all of your scripts for things. I love it.
If you're on a mac FMPerception - Fast FileMaker Analysis Tool - geist interactive is amazing. I played with it a bit while @ devcon.
Well, I'm glad I understood the problem well enough, anyway.
the problem is when you go to remove a mapped MySQL field in FileMaker which would be a placeholder for a calculation field and add the correct Calculation field, the link is broken
I'm not super-familiar with MySQL; based on a quick search I am guessing that you're referring to MySQL's Generate type column, is that right? Is it possible to use ALTER and CHANGE to change, say, an INT column to a Generate column? Would it be possible to set up the table so that you have very basic Generate columns with the same names as your FileMaker calculated and then change the formula for those columns later?
I'll look into these straight away - I've already looked at 2empowerFM's clipboard explorer, which I thought might have been of help.
Thanks very much for the info and your time
Thanks again for the reply Peter. Not quite as you say though. Let's say there are 5 fields in FileMaker:
Create the 5 fields in MySQL and export the data, then add the MySQL database as an external source everything will display fine, but none of the calculation and summary fields will work as they are now data fields in MySQL. You can't change the type of these fields, instead you have to delete the calc fields in FileMaker and add new "proper" calc fields and the link is lost.
This is getting clearer to me now, Obatics. However, can you not recreate Calc3 and Summary5 as Generated columns in MySQL before adding the MySQL table as a data source?
This is where I'm getting my info: MySQL :: MySQL 5.7 Reference Manual :: 18.104.22.168 CREATE TABLE and Generated Columns
Actually I hadn't thought a Generated column for the calcs - that actually might work! Thanks very much Peter. I don't think the summary and global fields will work, but the calcs are a but portion of the problem.
Thanks again Peter.
Without any idea of what the actual architecture of your system is or why you're moving to a MySQL backend, I suggest that you might want to consider keeping calculation, global and summary fields in your FileMaker file and keep your static data in MySQL.
EDIT: to clarify that I'm the one without any idea.
I have a client who is using a FileMaker database for the last +10 years and want to open up access to users working from home and creating web forms for customers and suppliers. Currently, users requiring access from home access their internal database through an open port on their router, which they find slow and unreliable.
We figure the best way to approach this is by migrating their data to MySQL, provided there isn't a big impact to speed for users working within the office, so we're going to test it out before making a final decision.