Dynamic ODBC Import

[Ted S]

Hi folks,

Sorry if this has been asked and answered here before but my searches came up blank.

I have an FM6 database with a global field called gSystemID. I would like to script a routine that would import from an Oracle DB only records that matched the vaule in the gSystemID field. Here's what I would like to do as written as a simple SQL statement:

SELECT phone, fax, city FROM installed_base WHERE system_id = gSystemID;

There doesn't seem to be any way to insert a variable into the import records function. What am I missing here?

Thanks,

[slstrother]

Create another global gQuery and build the query in this field using the set field script step.

Set Field["gQuery",""SELECT phone, fax, city FROM installed_base WHERE system_id = "& gSystemID"]

Then change your import records script step to specify the query using the gQuery field value.

Hope this helps

[Ted S]

Create another global gQuery and build the query in this field using the set field script step.

Set Field["gQuery",""SELECT phone, fax, city FROM installed_base WHERE system_id = "& gSystemID"]

I understand this and it makes good sense to me.

Then change your import records script step to specify the query using the gQuery field value

I don't see how to do this. There doesn't appear to be any mechanism to do this. Maybe I'm in a whole wrong area; I'm in FILE -> IMPORT RECORDS -> ODBC SOURCE...

[slstrother]

Create a script using the Import Records script step, specify the file, select ODBC, select the appropriate DSN, enter user name and password if needed, then a specify query window will show, then select field value for the query and select the gQuery field.

[Ted S]

Hmmmmm.... even though I was always going to script the import routine it never occurred to me that the import records script step would do anything other than call up the parameters of the last import that was run manually. I thought it was going to behave like finds and sorts do in past versions of FMP.

I've only recently upgraded from v3 to v6 and have only played with v7 for a couple hours so it looks like I've got a lot more learning to do.

I am pleased to see that the folks at FileMaker are moving away from this retain the settings from that last manual operation method of performing the operation in a script step. That method was always unintuitive and iffy to me.

Thanks for your help slstrother!

[Leb i Sol]

Hey Ted!

Going through ODBC as slstrother said has a lot more power as you can user SQL based querries ...from round,sum,distinct,group by etc. where u can "shape" your data before it is imported..hence eliminates work/scripting u need to do in FM ...such as filter for duplicates and some mentioned above.

and above all ODBC is the way to import data that is NOT in ".fpX" format

I would gladly pass you some samples....

Take care!