lostinfmland
asked on
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?
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?
ASKER
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?
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?
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).
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).
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.
ASKER
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.
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.
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::AddressLine
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::AddressLine
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.
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::AddressLine
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::AddressLine
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.
ASKER
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.
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?
Are you able to understand my suggestion about scripting?
ASKER
I believe I do it is just a little different from the regular sql script I use.
I have provided an approach to do what you want in the following question link: https://www.experts-exchange.com/questions/26840004/FM-Using-ODBC.html
ASKER
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
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
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
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
ASKER
forgot their is also a SET Field [firstname::id = firstname 2 id]
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
Go to Record [ First ]
Loop
Set Field [ firstname::first_name = firstname 2::first_name ]
Go to Record [ Next ; Exit After Last ]
END LOOP
ASKER
I made those changes and nothing seems to have changed none of the data in the filemaker table appears in the mysql database.
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
Go to Record [ First ]
Loop
Set Field [ firstname 2::first_name ; firstname::first_name ]
Go to Record [ Next ; Exit After Last ]
END LOOP
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
AWSOME!!! thankyou..
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