Dynamically importing sql data

[Darren La Padula]

Hey there:

I have built a fm/sqlserver synch module for a client. On a layout, they are able to select the fm tables to synch and the fields within the tables that will synch. Based on their selections, I script the generation of the necessary sql statements and off I go. On the export (fm to sql) it works super. My obstacles are on the sql to fm side (we're using version 8).

I am auto-generating the sql "select" statement as well, according to the fields they've selected to import. I'm using a sql plugin to make the sql call. My problem is the inability to dynamically tell filemaker which fields to populate when creating the new record. I have also tried to auto-generate an xml statement to import so I could dynamically map the fm field name to the correct value, but the only way that works is if I script the generation of a file to import (you can only specify xml http requests via calculation in a sript).

So basically I'm looking for any super clever ideas on how to approach this. It would far easier to hardcode the import, but need to have the utility as hands off as possible - if the client wants to synch a new field, they don't want to call me to hard code the new fields into the script.

Any help greatly appreciated.

thanks.

[hunter]

Are you using the ODBC plugin?

I scripted an import from SQL to FM, using ODBC. It allows you to match the fields via Specify Import Order when using the Import Records script step.

The database structures are different however. I get one record per each question from an online survey. So I had to also set up another script that imported the downloaded records to another FM database to combine all the questions into one record per each survey submission.

[Darren La Padula]

My problem is is that I don't know what fields the client has selected to import - he selects those fields as part of a different layout. So when I run the import, I need to somehow tell filemaker what the name of the field to import to via a variable or global field where I am storing the names of the fields he wants to import. Make sense?