bi-directional connection to filemaker 11 advance and mysql 5.5

I have a server1 (O/S is Windows 2008) that runs Filemaker server advanced and the client machines (O/S is windows7) are running Filemaker pro advanced.
I have another server2 (O/S is Windows 2008)is running mysql IIS7 and PHP.

I am trying to make a live bi-directional connection between Mysql and filemaker (Filemaker will be hosting the data). I have remote clients that will be adding data through a browser window connecting to mysql. I need the information they add to be put into the filemaker database live. I have local users that will be using Filemaker Pro advanced to also enter data and the remote users need to be able to see the changes.

What I have done so far. I have installed the mysql odbc 5.1 driver on the server1 that is hosting the data.
1, Created a table in Mysql that has the same fields as the filemaker database.
2, made a connection with the external data sources within filemaker.
3, have then gone into Manage Database and have added the external table
4, Made the relationships between both Filemaker Pro fields and the the fields in the mysql table.

I am not sure what I am doing wrong but I cannot see any data being exchanged between filemaker or Mysql.

Questions

Do I have to install the filemaker odbc client on the server hosting mysql?
Do I have to create a Sql Query to make Filemaker and Mysql exchange Data?

lostinfmlandAsked:
Who is Participating?
 
Will LovingPresidentCommented:
OK, a few points:

1

ust to clarify, your table occurrence called "firstname" is the FileMaker table, the one called "firstname 2" is the mySQL table. I would recommend renaming them something other than what is also a field name just to avoid confusion but for now I'll refer to them as "firstname (FM)" and firstname 2 (mySQL)" just to be clear about what we're talking about.

2

If you look at the relationships graph you will see that your relationship is not just based on id, but also on the "firstname" field. That's why both id and firstname are listed in the upper portion of each table occurrence. The way it is currently setup, it requires that matching records have not only the same id but also the same first name. If you double-click on the relationship 'box' - the small box with the = sign between the two table occurrences, you will see that there are two "predicates" (conditions to be met), not one. So, you need to delete the predicate that says firstname = firstname and leave only the id = id. That will fix the problem with the relationship.

3

The third issue is with your Set Field step. In item 9-12 of your list, you are selecting the field to "Set" and the value to set it to. The field that is being set should be the "firstname" field from "firstname 2 (mySQL)". The value you are setting it to should be nothing more than the "firstname" field in "firstname (FM)". You do not need a semi-colon or any other operator. I only put the semi-colon in to distinguish between the field being set and the value it is being set to.
I've attached a sample that includes two FileMaker files: "FM SQL Join.fp7" and "faux mySQL.fp7". I don't have an easy way to demo connecting to mySQL, but connecting to an external FileMaker file is essentially the same, at least for the relationship and scripting issues we are talking about. So, just open FM SQL Join.fp7 and you'll see in the relationship graph:

 Relationship Graph
The Contacts table for the file you are in is on the left, and the Contacts table for the external database - in this case my "faux mySQL" file - is on the right and it's name is in italics indicating that it is an external file. You external mySQL table occurrence will also have italics.

Then click on the = box on the relationship line between the two Table Occurrences and you'll see:

 Corrected relationship
The corrected script (with a few bells and whistles) looks like this:

 ScriptExternal-Tables.zip
0
 
Will LovingPresidentCommented:
Just because you have setup relationships in FileMaker between the two tables doesn't mean that they will automatically synchronize. You basically have two choices: 1) write a script that synchronizes the two tables periodically in FileMaker, or 2) use only one table: either the table in mySQL, or change your PHP to talk connect directly to your FileMaker server and pull data from the FM tables instead of mySQL.

Only the second option will make your data truly "live". A sync script can be setup as a Scheduled Script in the FileMaker Server Admin console to run at whatever frequency you wish but the data will only be as current as the last sync.

For an interesting examination and example of using FileMaker with external data sources such as mySQL, see:

http://examples.oreilly.com/filemaker9tmm/screencasts/ess/screencast.html
0
 
lostinfmlandAuthor Commented:
I would like to do the mysql option I do understand it is not live.
I have created just a very simple database with just named firstname with only 1 column called firstname
and a mysql table called firstnamesql and 1 colum called first_name.
If I go to view> go to Layout choose firstnamesql I can see me data in the mysql table and if I go to view>Go to Layout and choose firstname I can see my data in the filemaker table.

How can I make the data from the firstnamesql show up in the firstname table in filemaker in the same column?
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Will LovingPresidentCommented:
I'm not sure what you mean by the "mySQL option"...I would take that to mean that you want to just use the table in mySQL but then you say you understand it is not live which would imply the sync option. It sounds like you are wanting to keep separate tables and to sync the mySQL data to FileMaker. To do that you need to create a script and what that script does depends on your goals. If you only want to move data one direction then you can just sync the data periodically. I would also suggest having the field names be identical between the two systems. It will make it easier to match up and keep track of what you are doing as your number of fields grows.

First, what field are you matching on in your relationships? There has to be a common "key" or identifier to cause data to be updated between the two systems. For example, if you have a Contact record and you are wanting to sync field data between mySQL and FM, then both systems need to have records with the same fields and a common identifier, the ContactID or serial number, whatever you call it. This needs to be unique, auto-entered serial number, it will not work to base the relationship something changeable such as a name or email address.

Layouts are based on a single table though you can display data from related tables. Assuming you have your relationship properly setup between the two systems using the ContactID (or whatever you choose to call it), the you can write a script that simply sets the value of the field in FM to the value of the related field on mySQL. e.g.

Set Field [ Contacts::firstname ; mySQLContacts::firstname ]

I'm giving the tables separate names here but making the field names identical. The above script step is just one field on one record. To do many field you would need to repeat this and to do so with many records would require the use of either the "Replace" command or a looping script. Normally, to avoid updating all records whether they need it or not, I would build some sort of search mechanism so that only the records in mySQL that had been modified since the last sync would be synchronized. This can be done by checking modification timestamp (fast but you need to make sure the timestamps are using the same time zone) or by creating a calculation field that compares field data between the tables (slower but effective).
0
 
jvaldesCommented:
If you leave the data in a MySQL table , Filemaker will allow you to put the data into MySQL live so that there is no delay and no need to sync. To do that all you need to do is link Filemaker with the table in MySQL that has the data, create a relationship in Filemaker if needed. if you just want to input data, just create the layout with the MySQL table as the table used (this is allowed when you select the table associated with your layout, you will see an option to select an external table) and that is all that is needed.
0
 
lostinfmlandAuthor Commented:
Unfortanatley I have to keep two different databases. But they have to basically mirror one another. I believe I can create some sql queries that would accomplish the task. I just need to figure out what the queries should look like. I have tried some very basic ones such as

UPDATE firstnamesql
SET first_name = ( SELECT firstnamesql.first_name
FROM firstname)

I know I dont have a primary key but, seems like it should still exchange data. I dont get an error.
0
 
Will LovingPresidentCommented:
It may work because you only have one field and one record, but if you want to have more than that you will need a primary key to create a proper relationship between the records in the two tables. And, while you can write SQL queries to do the updates, it is probably easier and faster to create a script in FileMaker to do that.

Each step in the script would use the "Set Field" command where you are setting the value of the FileMaker based field to the value of the mySQL field. (This is assuming you have setup the relationship between the FM table and the mySQL table using a unique primary key.)

As noted previously, the script step might look something like this.

Set Field [ Contacts::firstname ; mySQLContacts::firstname ]
Set Field [ Contacts::lastname ; mySQLContacts::lastname ]
Set Field [ Contacts::honorific ; mySQLContacts::honorific ]
Set Field [ Contacts::AddressLine1 ; mySQLContacts::AddressLine1 ]

To then make this check all records, you would need to add a Loop and Go to Record [ Next; Exit After Last ]

Go to Record [ First ]

Loop

Set Field [ Contacts::firstname ; mySQLContacts::firstname ]
Set Field [ Contacts::lastname ; mySQLContacts::lastname ]
Set Field [ Contacts::honorific ; mySQLContacts::honorific ]
Set Field [ Contacts::AddressLine1 ; mySQLContacts::AddressLine1 ]

Go to Record [ Next ; Exit After Last ]

End Loop

There are ways to make this conditional so it only updates records that need to be changed, but my take is that you need to familiarize yourself with the "ScriptMaker" first before getting into the fine-tuning.
0
 
lostinfmlandAuthor Commented:
The filemaker database i have has over 40 fields and does have a primary key. I am just trying to get and understanding of how it works and how I can integrate into the needs that I have. I thought trying out a database with as few fields as possible would be the simplest and then migrate to the larger one.
0
 
Will LovingPresidentCommented:
I think your process is a good one, starting with a few fields and testing for feasibility. It's usually a good idea when posting a question to give as much information as possible about what you are trying to do - such as the structure of the db and the fact that you do already have a primary key - so that answers don't tell you things you already know. Without a clear understanding of where you are starting from or an example file, it's harder to know how to address the problem or what the skill level is of the questioner.

Are you able to understand my suggestion about scripting?
0
 
lostinfmlandAuthor Commented:
I believe I do it is just a little different from the regular sql script I use.
0
 
jvaldesCommented:
I have provided an approach to do what you want in the following question link: http://www.experts-exchange.com/Apple/Software/Filemaker_Pro/Q_26840004.html

0
 
lostinfmlandAuthor Commented:
I need some help here for some reason the two tables just do not communicate with one another
This is the table that is setup on my mysql database: CREATE TABLE firstname(id INT(3) NOT NULL AUTO_INCREMENT, first_name VARCHAR(50), PRIMARY KEY(ID))

In my filemaker I have the same  screenshot of relationship
my script looks like this:
Set Field [ firstname::first_name = firstname 2::first_name ]
Go to Record [ First ]

Loop

Set Field [ firstname::first_name = firstname 2::first_name ]
Go to Record [ Next ; Exit After Last ]

END LOOP
0
 
lostinfmlandAuthor Commented:
forgot their is also a SET Field [firstname::id = firstname 2 id]
0
 
Will LovingPresidentCommented:
You don't need to include the first name in the relationship, just the ID. The script should look like this:

Go to Record [ First ]

Loop

Set Field [ firstname::first_name = firstname 2::first_name ]
Go to Record [ Next ; Exit After Last ]

END LOOP
0
 
lostinfmlandAuthor Commented:
I made those changes and nothing seems to have changed none of the data in the filemaker table appears in the mysql database.
0
 
Will LovingPresidentCommented:
I think you are confused about how the Set Field works. It has two parts, "Specify", where you specify the field in the mySQL table that you want to change, the firstname field, and "Specify Calculation" where to specify what you want the value to be that is put in that field, meaning the value in the FileMaker firstname field.

Go to Record [ First ]

Loop

Set Field [ firstname 2::first_name ;  firstname::first_name ]
Go to Record [ Next ; Exit After Last ]

END LOOP
0
 
lostinfmlandAuthor Commented:
I think I am very confused. These are the steps I have been doing.
1, I create a mysql table using the following script CREATE TABLE firstname(id INT(3) NOT NULL AUTO_INCREMENT, first_name VARCHAR(50), PRIMARY KEY(ID)).
2, I go into filmaker pro create a table called firstname.
3 I go to file>manage>database> select the fields tab, select id then hit the options button  I select Auto-Enter select Serial number press ok.
4, I go to file>manage>External Data Sources Select New, select ODBC Specify my DSN select Specify user name and password fill in the username and password. select ok
5, go to File>Manage>Database  select the Relationship tab, select create table data source, I select my DSN which is called names. select firstname and it gets named firstname 2  for the table press ok.
6 I then drag the id from the firstname 2 to firstname it creates an = relationship select OK.
7 I then select Scripts> Manage Scripts, Select New I then choose Go to Record/Request/Page, select move in the specify field I select First.
8, I then select Loop and press move.
9, I then select Set Field select Calculated result: press Specify In the drop down list I select firstname 2 and double click on first_name.
10, I put a ; after first_name
11 I then go back to the drop down and select firstname double click on first_name.
At this point I get an error stating an operator (e.g. +,-,*...)is expected here.
12 I change the ; to an =  no error but it does not work.

as you can tell iam a newbie and am lost no idea what to do about the error

0
 
lostinfmlandAuthor Commented:
AWSOME!!! thankyou..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.