Field data in an SQL Query

[Darxim]

I have a text field, and I want to use whatever text may be in that field to run an SQL query script in Filemaker. Could anyone give me an example of this, if it is indeed possible?

[Anatoli]

Why?

[Darxim]

What data is going to be pulled is variable. The user has to define it. We're using Filemaker as a kind of frontend for an SQL database. That database has been used by applications for some time now, so simply switching over the database to Filemaker isn't really an option. However, we do need a quick, versatile frontend so we can bring up the data.

[Anatoli]

RE: However, we do need a quick, versatile frontend so we can bring up the data.

Do you think FM will be OK for this? Maybe with some really excellent SQL plugin.

[slstrother]

You can use the contents of a field as an SQL query in the Import Records script step. To set it up, you will need a field with the query. Create a import script, select ODBC as the file type, select your data source, enter user name and password if required, then choose field contents and select your field.

You can include variables if the query field is a calc or if you build the query using the Set Field script step.

Hope this helps

[Darxim]

You can include variables if the query field is a calc or if you build the query using the Set Field script step.

So I hear, however I have yet to find any documentation on how to actually do so. As such, I don't really know how to make it work. I looked at an example of doing it, but even the example itself returned an error. It must be possible, though, because I keep hearing about it.

So far the only thing I can do with Import Records is import records from a pre-specified (as in not specified during the actual request) database. Doesn't seem too variable. You see, we're going to be using multiple databases, so it needs to be variable.

[slstrother]

Attached is a very simple sample file. The variability is in the WHERE statement, the rest of the query is set up as text strings in the Set Field script step. If you needed to change db's, tables or columns on the fly, I would think it could be done using a similar process. However, you will need to map fields each time.

Hope this helps.

Variable_Query.zip

[Darxim]

Once I realized the point behind putting the query into the text box, I got it to work for what I needed. It was a little confusing at first, but I've got it working now. Thanks!