Dynamic import from access DB to FM

[kelbantaemi]

Here is my problem I need to dynamically update FM7 table records

from

a series of Access Tables. I presently can, via ODBC import the

entire

table(s) into FM7, this is being done by a manually run script

executed by the user. The user needs to do this often to keep the FM7

data current. What I really want to do is filter the import based on

data from my FM7 table. Is there a way to query the Access Table from

a FM7 data field, for just the one relevant Access record via the

keyfield, and import just that record.

Details:

I have two Access Tables, "Job_Number" keyfield is

"Job_Num"

and "Job_Operation" keyfields are "Job_OpNum and

"Job_Num". New jobs

are added and scheduled throughout the day. FM7 must generate

specific

documents based on "Job_Num" and Job_OpNum, as these new jobs

are

scheduled. I want the user to execute a script, enter the required

job

number and the script via ODBC import the matching record(s), from

the

Access Table(s).

I am really stuck and can not go any further, any help here would be

greatly appreciated.

[SurferNate]

I don't know if there are any easy solutions to your problem. Depending on the size of your source data set you might build an intermediate import table. I call this a "data dump". You could then import all of the aAccess data periodically into the "dump table", use filemakers functionality to filter your new data, and script any other actions that are required. After everything has completed successfully, the "dump file" could be automatically cleared by a script and left clean for the next data update cycle. This is only a practical solution if your source data set is not overwhelming.

[kelbantaemi]

Thank you for your responce to my post. But if I undersand your suggestion correctly, that is what I am presently doing. What I really need is, after user types in a Job Number in a FM layout the required fields are populated with data from Access DB datasource via ODBC. New Jobs come in to the Access datasource continualy through out the day. And having to re-import the full table(s) prior to each user request is time consuming.

[mattley]

Create a Text calculation field with the SQL query in it including a WHERE clause based on the value entered by the user. Along the lines of

"Select * from where JobNo = '" & & "'"

I use a global field called Quote which contains the ' Char. and & I hope that makes sense.

You can then use this in the import script step. specifying the ODBc source. and that field as the calulated query.

[kelbantaemi]

Thanks Matley,

As luck would have it I figured it out just as you have stated in your post not more then a day or so prior to your message. I am using a text calculation for the SQL statement, and a field refrance to an imput field for the user variable.

My solution appears to work in my simple test FM Table. I am unclear on your use of the global field however.