The problem must be in the use of the dynamic table name, or that the target is a "New Table". Those are the two differences between the two import statements.
Place a Pause Script step after the import step (in the loop) to see what it looks like after the first table is supposed to be imported. Did it create a new table? What layout is it in?
Table created on SQL import is named usin DSN name, as you saw. It is reasonable since the SQL can have any number of tables in FROM clause.
I don't have any idea why method 1 don't import records. You got no error so $tableName should be valid without quoted...
When you import, I've found you have to either be in the context of the target table (i.e., sitting on a layout based on it), or explicitly include it in the Import Records script step. Suggest you do this instead:
1) Set up a layout for each table with a name exactly the same as the table.
2) Insert a Go to Layout script step into your loop, like this:
Go to Layout [ $tableName ]
Import Records [ ]
This will probably work better for you.
Also, your script above has the Import step creating a new table. Or at least it's attempting to. Note that you said in your description that you already had tables for these imports. Do you mean to create a new table each time? If not, remember that new table creation is not supported on Go or when importing server-side. (Don't know if those apply, but something to consider.) If a table of that name already exists, then that may be the problem.
As another recommendation, parameterize your SQL statement. Use:
SELECT * FROM ?
and then pass the name of the table as a parameter. Not only is this more secure, it will also allow FileMaker to parse out any spaces or other unruly characters in your table names.
Thanks for the replies:
Putting a pause did not help.
I tried setting up layouts ahead of the IMPORT _ that did not work either.
I don't plan on using this on GO and I am not worried about security because it really is only done once in a while.
I am curious about the parameter option on the SQL statement - . Could you please send me an example?
Secondly -: no suggestions about batch renaming the tables from JJSQL 2....x to the individual names from the TABLEname table?
What exactly are you trying to accomplish here? Describe your desired result.
I have an SQL DB with 300 tables that is the backend of a CRM . I am trying to recreate the CRM in Filemaker , import all the data from the SQL.
I know I can individually import all the tables and I may never need all 300 tables.
I was just hoping there would be a nice automated way to do this now.
If everything was in Filemaker at least I would not have to keep referring to the SQL DB.
I also tried importing all the data into ACCESS --- successful. but again when you try to import the data from ACCESS to Filemaker the import only brings over the empty fields - is this a bug?
The Lopp method is the best - if only we could also batch rename the tables.
What would be great is you could create a FILEMAKER schema with tables names and fields from a single CSV file.
Maybe someone has written that already out there?
Your SQL in method#1 has $variable in it, so the SQL is not valid if the $variable is not defined, so while you defining script it never be valid and matching import fields are not stored in script step.
"SELECT * FROM " & Case ( $tableName = "" ; "Addresses" ; $tableName )
not tested. This may fail since columns in tables are different.
Interesting idea USER19752 but how will that work for the 300 tables I need
Sorry - I wasn't very clear. The Pause step can be used as a debugging tool. When the script paused the first time, you should have seen your first complete import table imported. If not, you can at least see what layout you landed in. Cancel and debug, rinse, repeat.
Sorry my last comment was for "why method#1 didn't import record data". Created table name thing is not resolved with it, so not help your main issue.
If you use Windows, DSN can be editted through registry.
This might be worth looking at:
I don't know what version it starts with (I have 14), but, if you haven't already looked into it, there's ESS (external sql sources).
This would be useful if you could take an Oompa-Loompa off of the chocolate-bar line for a day to do the clicking for you select multiple tables at once in the following steps (maybe you can) ...
Once your DSN is created, go to the relationships graph and click + to add an occurrence of one (hopefully more) of your 300 tables via the ODBC/DSN connection.
FM is supposed to build a "shadow table" with a "shadow schema" and populate the table with data. I haven't actually tried this myself.
Interestingly, the shadow schema inherits much of the field validation rules from the SQL source.
Also, note that you can corrupt the original sql data by manipulating it in the shadow table, I think. (So work with a copy.)
Fellow contributors - is this correct?
Re: If you use Windows, DSN can be editted through registry.
Sorry this is not helpful for your issue...
I used this for one DSN accessing to many files of FM database, changing filename in DSN.
Changed DSN name is not usable, since DSN name in script step can't be changed...
I guess in the end I will look for a developer to help me write this in Java or C. The fact that there are apps like FM Migrator suggest that it can't be done easily from within FM.
Yes, but it really doesn't help him much. He'd have to add the 300 shadow tables individually. He's trying too avoid doing the job one at a time.