[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Filemaker 11 - MySQL

Posted on 2011-05-12
21
Medium Priority
?
1,039 Views
Last Modified: 2012-05-11
I need to push, and update Filemaker 11 data (stored locally) from 1 database to a hosted MySQL database on my web site.
I have already established ODBC connectivity between the two tables.
It's data for an Alumni website that I maintain currently.

Thanks for your assistance.

Frank
0
Comment
Question by:frankb3
  • 9
  • 9
  • 3
21 Comments
 
LVL 25

Expert Comment

by:Will Loving
ID: 35746712
Assuming you have corresponding tables and fields between the mySQL and FileMaker database, you will need a method of determining which records get updated - perhaps a modification date in both systems - and then a  set of script steps to update those records. Both the check and update would presumably be part of the same FileMaker script.
0
 

Author Comment

by:frankb3
ID: 35746803
First I need to find a way to push my local data in Filemaker to my hosted MySQL database.
0
 
LVL 25

Expert Comment

by:Will Loving
ID: 35746867
The script is used to 'push' the data. If you have made the ODBC connection, have the mySQL table appearing in the FileMaker Relationship graph, and have a relationship created between key fields (such as a serial number or other unique record identifier) then you just need to create the script to find the records to update and update them.
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
LVL 25

Expert Comment

by:Will Loving
ID: 35746893
Ah! Do you mean that you don't yet have your local data in the mySQL database at all?

Again, assuming you have the setup described above, you should be able to go to a layout in your FM database that is based on the mySQL 'shadow' table and then do an import from the same FileMaker file into the mySQL file. I'm assuming you either have matching field names or can line up the corresponding fields.
0
 

Author Comment

by:frankb3
ID: 35751054
The tables are matched...but MySQL is on a server.  Filemaker is local.  Ok. great suggestion.  Tried it. Filemaker seems to go through the operation of importing, showing 1700 records in process, but once done the message comes up saying 1700 records imported 11 fields had errors.  The MySQL still has only the couple of test records I added via the web interface.
0
 
LVL 25

Expert Comment

by:Will Loving
ID: 35751093
It sounds like your FileMaker to mySQL connection is not setup or working correctly. Make sure that:

1. The relationship from the Filemaker file to the mySQL table has the "Allow record creation" checkbox marked
2. That your mySQL database will allow creation of new records
3. When you go to a layout in FM that is based on the mySQL layout, that you can manually create a new record that shows up in mySQL

Also, what errors were reported?
0
 

Author Comment

by:frankb3
ID: 35751141
Allow Recrds;  checked
MySQL does allow creation of records (I don't see anywhere to check that)
I can create remote records on the shadow table layout.

See attached for the error. FM to mySQL issue FM to mySQL issue
0
 
LVL 25

Expert Comment

by:Will Loving
ID: 35751185
I'm guessing that you have 11 fields that you are uploading. Are you possibly using the "New Table" option on the right side of the import field matching dialog? Can you take a screen shot of the field matching dialog showing the FM fields on the left and the mySQL fields on the right?
0
 

Author Comment

by:frankb3
ID: 35751510
0
 
LVL 25

Expert Comment

by:Will Loving
ID: 35751578
Hmmmm.... if you can create records manually, I can't think of a reason why the import would fail. Check the import.log file which should have been created in the same folder as your FileMaker file and see if there are any clues there.
0
 
LVL 8

Expert Comment

by:Tocacar
ID: 35752771
This link shows how to migrate the data from FMP to MySQL via the command line.  It's from 2004, but hopefully still of use to you (it's very thorough with lots of useful screenshots):

http://dev.mysql.com/tech-resources/articles/filemaker_mysql_whitepaper/filemaker_to_mysql_whitepaper01.htm
0
 
LVL 25

Accepted Solution

by:
Will Loving earned 1000 total points
ID: 35753442
Interesting article Tocacar. However, given the current FM 11 ability to connect directly to SQL data sources and the desire by frankb3 to continually update the mySQL database from FM it seems like it doesn't really answer the question about the errors. Any thoughts or experience with that?

I push/update data all the time to mySQL and SQL Server sources and I've never seen this error. My guess is that it is some minor configuration error but I can't think what...

frankb3 - I have three suggestions at this point pending further clues:

1. check/upload the import.log which may well have more details on the errors
2. upload a screen shot of the relationship graph
3. upload a screen shot of the relationship dialog between the two tables (I'm assuming the BSHS Alumni is the mySQL table)
0
 
LVL 8

Expert Comment

by:Tocacar
ID: 35753577
@willmcn
I don't have a huge amount of experience in this area, but I'm always happy to try and help out :)

@frankb3
Have you checked your MySQL data types against the FM data you're trying to push in for each field?

0
 
LVL 25

Expert Comment

by:Will Loving
ID: 35753675
I mentioned in the previous note that I push or update data to mySQL regularly, but I realized that I do it using Set Field[] rather than import. Frank, check the import.log file and if there are no clues there, I will give you a technique for creating records using Set Field[].

One other comment: if you are wanting to update records in the future, you will need a unique record identifier that is in both FM and mySQL. The simplest way to accomplish this is to add a number field to FM called something like RecordSerial and set it to auto-enter (under "Options") a serial number starting with 1. To serialize all existing records in the database, add the RecordSerial field to a layout based on your FM table (not the mySQL shadow table). Then, go to Records -> Show All Records to remove any found set, and then Records -> Replace Field Contents. In the Replace dialog use the Serial Number option and have it also Update the next serial number. After that any new records will be automatically serialized. This RecordSerial field should also exist - without any auto-enter - in your mySQL database.
0
 

Author Comment

by:frankb3
ID: 35753686
Tocacar & willmcn

Thanks for your help.  I did a fresh import creating a new table in mySQL and paired the new table in Filemaker. That seems to have done the trick.  I also added a very unique identifier field in FM before exporting as an Excel and set that field as the key in mySQL.  So far it's working as it should.

Do either of you have experience with how to port mySQL to my web site?  It's an alumni website with a page for every class year going back to 1957.  There are three ways I'm posting the data (currently manually via DW/html).  By unique class year page, if the person is deceased I list them on a separate column on the class page, and then on the home page a list of the most current listings sorted by year.  It's a daunting task to keep up with...thus the reason I'm incorporating mySQL....
0
 

Author Comment

by:frankb3
ID: 35753713
BTW the import log is not there.
0
 
LVL 8

Assisted Solution

by:Tocacar
Tocacar earned 1000 total points
ID: 35753726
frankb3 - I'm glad you got it working!

You could publish the information to the web using Filemaker.  If you go this route, Instant Web Publishing is the quick and dirty option / Custom Web Publishing (using Filemaker's API for PHP) is the more technical/ flexible choice.  You're probably going to need FM Server to do this in any meaningful way though.  If you stick with MySQL, you can use PHP to publish the data.  There are a ton of sites on the web that can help you along the way.  

Good luck!
0
 
LVL 25

Expert Comment

by:Will Loving
ID: 35753817
...what tocacar said ;-D

When you refer to porting mySQL to the website, I assume you actually mean having the website allow active interaction with the mySQL database. Whether you do interaction with mySQL or FileMaker, you're probably going to wind up using PHP to talk to the database. You can use IWP as tocacar suggests and that is the quickest, non-programming route, but you will probably need FM Server to do it. Any way you do it there's a bit of a learning curve but you'll get there. Good luck!
0
 

Author Comment

by:frankb3
ID: 35753986
Just added the additional serial number and did an update import into the shadow table. It was successful.  Thanks.  You guys have been a great help!

Frank
0
 

Author Comment

by:frankb3
ID: 35754231
Tocacar assisted with the solution.  
0
 

Author Closing Comment

by:frankb3
ID: 35754241
This site is a great resource.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month18 days, 20 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question