How do I create a duplicate table in FileMaker using an external SQL table

[JoniS]

I am using FileMaker 9 as mostly a front-end to an SQL database. Unfortunately, at this time, FileMaker does not allow me to create value lists using the tables from my SQL database.

This is what I was thinking might work as a work-around: duplicate all the records from the SQL table into a table in the "local" FileMaker file, and somehow update the records in the "table copy" every time the FileMaker file is opened. Once I have an exact copy of that external table now in FileMaker, I can use the FileMaker table to make a value list. I have no idea how to do any of this. And if anyone has a better idea for a work-around, I'd be very happy to hear it! Thank you in advance for any help you can provide.

Edited February 26, 2008 by Guest

[mr_vodka]

Unfortunately I dont know of a better way. I guess you could do the import with a script when the file opens. Once you have the values in the temp table, you should be able to use FM value lists.

http://fmforums.com/forum/showpost.php?post/262209/

BTW. FileMaker's did not intend for users to user it as a Front End into external sources. They make it pretty clear that it should not be used strictly as a front end.

Edited February 26, 2008 by Guest
Added BTW

[LaRetta]

You can import only the value list values by using something like:

SELECT DISTINCT fieldname FROM filename ;

This would import only one of each value from the SQL table.

[JoniS]

Thanks, John! I didn't know that the FileMaker creators actually frowned upon using FileMaker as a front-end to external datasources... but that is what my organization wanted...

In any case, I am very much a beginner with FileMaker, especially when it comes to scripting. Could you show me how to get a script to run immediately after opening a file, and how to get a script to make a copy of a table and all its records? (Or point me in the right direction toward other posts. I can't seem to search well on this site yet.)

[JoniS]

Thanks, LaRetta!

Since that is a SQL statement, do I set up a script in FileMaker using the command:

Execute SQL []

And how do I get those records to populate a FileMaker table?

[mr_vodka]

Create a script and then under File --> File Options there is an option to specify a script.

Your script could be as simple as Import records [] (ODBC Data source) or Execute SQL [] command.

[JoniS]

Oh! Genius! It was hiding from me under "File Options" all along!

And I'm very excited because I got the Import Records command to work for me for copying the records from SQL to a temp table in FileMaker. The only quirky thing now is that every time I run the Import Records script to copy the User table, it takes me through every step in every window again instead of just doing it in the background. Is there a way around that?

[mr_vodka]

Check the option under the import script step of "Perform without Dialog"

[JoniS]

Thank you - it works like a charm!

[presley]

Hi,

I am having exactly the same problem -----and I am wondering about your solution -----I get how you are creating a local FM file, through a script, to enable value lists ---

The question I have is:

How are you pushing edited data BACK to the MySQL table after editing in a local file using the method described ?.....

[JoniS]

Here is my ugly work-around:

I absolutely DO NOT let the users change the data in the temp tables. Instead, I added the SQL table (the table that I am importing the records from) to the relational diagram in FileMaker. The user updates all records in that SQL table via a layout. If the user makes changes to any records in the SQL table, I allow them to click a button that "saves the changes" by re-importing the SQL table's records to the temp table in filemaker.

It's a bit of a dirty work-around, but it works because my users will very rarely be making changes to those SQL tables that I need to make into value lists.

Hope that helps.