We don't see your ~q (queryCalc). So it may be difficult to determine.
You can have a FIELD hold the entire let statement and it can be a calculated field. But you cannot have a "dynamic parameter" like you have.
You might try something like this:
Evaluate ( "ExecuteSQL ( ~q ; \"\" ; \"\" ; " & ~p & ")" ) // UNTESTED!
Evaluate ( myQueryField ) // this works and has been tested with my queries
I use this last method in my example files. All queries are in a text field which is used to set the 'result' text field.
thanks - I'll stop trying to get that calculation to work then!
I think I understand - so I have the query sitting in a text field (myQueryField) and then just use that to get the 'result' field.
So it's a matter of syntax and escaping in that calculation for myQueryField.
my parameters calc with 2 examples:
"\"" & Substitute ( List ( wcSQLentry::text) ; ¶ ; "\";\"" ) & "\""
1 - "%red%";"%yellow%";"%blue%"
2 - "%red%";"10"
my query calc with 2 examples:
"select wcStock.stock_id from wcStock where " & Substitute ( List ( wcSQLentry::query ) ; ¶ ; " and " )
1 - select wcStock.stock_id from wcStock where wcStock.title like ? and wcStock.title like ?
2 - select wcStock.stock_id from wcStock where wcStock.title like ? and wcStock.sale_status = ?
Pattern matching can be done with the LIKE sql operation but you are going to run into problems trying to use vars because i don't think LIKE can use variables and "%red%" is not equal to '%red%' (single quotes vs double quotes).
ExecuteSQL ( "SELECT column FROM table WHERE column LIKE ?" ; "" ; "" ; "%this_text%" )
will fail for either reason.
yes, it's a matter of evaluation. Your substitute worked, because it evaluated the string and placed the ~p into the ~q. The Evaluate() function - as you've noticed - with the correct concatenation and placement of quotes (escaped as needed), does it's job and makes a 'normal' query that can be executed.
You might want to review the sql 'solutions' that help you build queries. They truly are 'dynamic' (allowing changes) and then get processed.
(this last one has a great online documentation with more information: http://www.seedcode.com/pmwiki/pmwiki.php?n=SQLexplorer.SQLexplorer)
co is correct. IF the values are parameters, though, FM's ExecuteSQL() function will take care of the single quotes as needed. So care must be taken if VARs are used as the parameters. I may need to cast with
GetAsText ( $myTxt )
GetAsNumber ( $myNum )
for example, when using them this way (as parameters in ExecuteSQL queries)
ah - the 'copy calculation' script in the seedcode example looks like it'll have exactly what I need...
thanks for the pointers...
I've got it all working (in a temp hard coded way) for now and will expand it later using this - and it's great to see a query builder working in filemaker! Now users can create, save, share and manage finds properly!
Well, yes and no. If you use this to FIND the id of records with ExecuteSQL() and then use a method (GTRR, for example) to actually display those records, yes. If you just display "found information" (query results) in a text field (or perhaps print a report of the field), then it's mildly useful for "find". Or is if you use query+virtual list/table/report, then it's a yes!
It is possible to use native (scripted finds) with such as your example, but maybe it's more fun to use ExecuteSQL(). It all depends on:
how large is the queried dataset
how will you use the results
the ability to create the SQL 'code'
The query builders are handy! Keep in mind they may not be able to get everything you want. ExecuteSQL() may be limited in some ways. There are work-arounds involving queries+virtual list/tables+queries. And there are many SQL plug-ins which give you more than you can do with just ExecuteSQL(). And there is always FM as an ODBC/JDBC source and use another app to make the queries in 'real SQL'.
In parameter "%this_text%" doesn't contain double quote in the value.