SQL and Find Requests

[D_Settles]

I want to construct a SQL Statement based on data entered in a FM Find Request. The end goal is to create a Script containing the Query and transmit the Query via ODBC to access a remote DB and then import the data into my FM DB.

 

I cannot seem to find any documentation regarding using a FM Find Request and SQL. Is this possible? Does anyone have any info regarding an approach to this problem?

[Ted S]

Mr. Settles,

 

Can you rephrase your question? I'm not sure if you're are trying to query a Filemaker database with SQL or query a non-FileMaker database (such as MS Access) from a Filemaker script.

[D_Settles]

Restated Message -

In a FM Database, I want write a FM Script that constructs a SQL Query and transmits the Query via ODBC to access a remote SQL Server DB and then import the data from the SQL Server DB into my FM DB.

 

I want to derive the variables in the SQL Statement from data entered in a FM Find Request. For Example: The data derived from the FM Find Request might include a ClientID + all Invoices created on a specified Date.

[Ted S]

Okay, I have the general idea now. A bit more clarification is needed: Do you want to base the SQL query on the RESULTS of a Filemaker find request or on the the value that you are looking for? I know it sounds like a distinction without a difference but it matters.

 

Also, what version of FM and Mac or windows?

[D_Settles]

I am not sure I understand the distinction between "value" and "results". However, I can describe how I want the interface to function.

 

1. In FM DB, Click on an icon that triggers a script that includes a find request. Pause.

2. Enter a "start date" "end date" either in 2 different fieds or using the "..." date range function. Click continue.

3. Script inserts the "Date Range" in a SQL statement. FM transmits the SQL statement via ODBC to a SQL Server DB on a remote Server.

4. FM Imports the results of the SQL query and populates a set of new records.

 

At this time, I am accomplishing steps 1-3 described above by using Excel. Excel creates a .csv document using the imported data. Then, I am importing the data into FM. It works, but is not an elegant solution.

[slstrother]

I am attaching a clone of a file I made back in 2004. I think it does what you are describing, but does not use any find requests. It queries an external database using a date range entered by the user.

 

Hope this helps,

 

Steve