So I had this thought that it would be pretty simple to write a CF to make SQL queries with quoted and dynamic table and field references a bit quicker. I already have a couple CFs that I wrote just for the dynamic references but they still require a Let statement and ….

Anyway turns out it was anything but simple due to all the variables and possibilities with ExecuteSQL. And I look at all those CF parameters and I wonder if it's aptly named at all, LOL.

But it is actually very simple to use within its limitations. Anyway, I thought I'd chuck it up here for a laugh at least.

PS. It uses UniqueValues so 16 or 17 only, although it is only for an error check so could be removed, and it's not recursive.

**EasySQL ( TabFld1 ; TabFld2 ; TabFld3 ; TabFld4 ; Dist|1| ; FldSep ; RowSep ; WH|1|_GB|2|_HV|3|_Fld_Expr1 ; WGH_Fld_Expr2 ; WGH_Fld_Expr3 ; WGH_Fld_Expr4 ; WGH_Fld_Expr5 ; WGH_Fld_Expr6 ; WGH_Fld_Expr7 )**

Example: EasySQL ( table::field1 ; table::field2 ; table::field3 ; "" ; "" ; "|" ; "¶" ; 1 ; table::ID ; " =3" ; "" ; "" ; "" ; "" )

returns: field1value|field2value|field3value

field1value|field2value|field3value

field1value|field2value|field3value

where the value in the ID field = 3.

………………………………………………………………………………..

*/* Allows for construction and execution of simple SQL statements. Field and table name references are dynamic so the SQL won't break if tables/fields are renamed. There are 4 parameters for table::field names on the SELECT side of the statement. If more fields are required then use List ( GetFieldName ( table::field ) ; GetFieldName ( table::field ) ; ....) in any of the four parameter spaces to include the desired fields.*

*The FROM side of the statement is automatic based on table::field references used. The function will allow multiple tables but only using the UNION join, INNER JOIN and LEFT OUTER JOIN are not supported. Accordingly the same quantity of fields must be specified for each table.*

*There are specified parameter placements for DISTINCT, Field Seperator and Row Seperator. Enter the number 1 in the correct parameter to include DISTINCT in the SQL statement. Wrap the field and row seperators in quotations "" as per normal text constants.*

*There are 7 parameters to the right of the Row Seperator parameter for including WHERE (1), GROUP BY (2), HAVING (3), field names by reference and expressions. All 7 of these parameters accept any of the above data but there can only be one data type per parameter and they must be in the order required by SQL. Wrap expressions in quotations "" as per normal text constants. SQL arguments that are not explicitly listed here are not built into the CF however they may work by typing them out in the correct sequence in any of the 7 parameter spaces to the right of the Row Seperator parameter.*

*Unused parameters should contain "". Placing a 0 in parameter 7 (WGH_Fld_Expr7) will return the SQL statement for debugging purposes and it will not be executed.*/*

Let ( [

f1 = GetFieldName ( TabFld1 ) ;

f2 = GetFieldName ( TabFld2 ) ;

f3 = GetFieldName ( TabFld3 ) ;

f4 = GetFieldName ( TabFld4 ) ;

f1 = If ( PatternCount ( f1 ; "::" ) = 1 ; f1 ; TabFld1 ) ;

f2 = If ( PatternCount ( f2 ; "::" ) = 1 ; f2 ; TabFld2 ) ;

f3 = If ( PatternCount ( f3 ; "::" ) = 1 ; f3 ; TabFld3 ) ;

f4 = If ( PatternCount ( f4 ; "::" ) = 1 ; f4 ; TabFld4 ) ;

d = If ( Dist|1| = 1 ; "DISTINCT " ; "" ) ;

fs = If ( IsEmpty ( FldSep ) ; "|" ; FldSep ) ;

rs = If ( IsEmpty ( RowSep ) ; "¶" ; RowSep ) ;

w1 = GetFieldName ( WH|1|_GB|2|_HV|3|_Fld_Expr1 ) ;

w2 = GetFieldName ( WGH_Fld_Expr2 ) ;

w3 = GetFieldName ( WGH_Fld_Expr3 ) ;

w4 = GetFieldName ( WGH_Fld_Expr4 ) ;

w5 = GetFieldName ( WGH_Fld_Expr5 ) ;

w6 = GetFieldName ( WGH_Fld_Expr6 ) ;

w7 = GetFieldName ( WGH_Fld_Expr7 ) ;

w1 = Case ( PatternCount ( w1 ; "::" ) = 1 ; GetValue ( Substitute ( w1 ; "::" ; "¶" ) ; 2 ) ;

WH|1|_GB|2|_HV|3|_Fld_Expr1 = 1 ; " WHERE " ;

WH|1|_GB|2|_HV|3|_Fld_Expr1 = 2 ; " GROUP BY " ;

WH|1|_GB|2|_HV|3|_Fld_Expr1 = 3 ; " HAVING " ;

WH|1|_GB|2|_HV|3|_Fld_Expr1 ) ;

w2 = Case ( PatternCount ( w2 ; "::" ) = 1 ; GetValue ( Substitute ( w2 ; "::" ; "¶" ) ; 2 ) ;

WGH_Fld_Expr2 = 1 ; " WHERE " ;

WGH_Fld_Expr2 = 2 ; " GROUP BY " ;

WGH_Fld_Expr2 = 3 ; " HAVING " ;

WGH_Fld_Expr2 ) ;

w3 = Case ( PatternCount ( w3 ; "::" ) = 1 ; GetValue ( Substitute ( w3 ; "::" ; "¶" ) ; 2 ) ;

WGH_Fld_Expr3 = 1 ; " WHERE " ;

WGH_Fld_Expr3 = 2 ; " GROUP BY " ;

WGH_Fld_Expr3 = 3 ; " HAVING " ;

WGH_Fld_Expr3 ) ;

w4 = Case ( PatternCount ( w4 ; "::" ) = 1 ; GetValue ( Substitute ( w4 ; "::" ; "¶" ) ; 2 ) ;

WGH_Fld_Expr4 = 1 ; " WHERE " ;

WGH_Fld_Expr4 = 2 ; " GROUP BY " ;

WGH_Fld_Expr4 = 3 ; " HAVING " ;

WGH_Fld_Expr4 ) ;

w5 = Case ( PatternCount ( w5 ; "::" ) = 1 ; GetValue ( Substitute ( w5 ; "::" ; "¶" ) ; 2 ) ;

WGH_Fld_Expr5 = 1 ; " WHERE " ;

WGH_Fld_Expr5 = 2 ; " GROUP BY " ;

WGH_Fld_Expr5 = 3 ; " HAVING " ;

WGH_Fld_Expr5 ) ;

w6 = Case ( PatternCount ( w6 ; "::" ) = 1 ; GetValue ( Substitute ( w6 ; "::" ; "¶" ) ; 2 ) ;

WGH_Fld_Expr6 = 1 ; " WHERE " ;

WGH_Fld_Expr6 = 2 ; " GROUP BY " ;

WGH_Fld_Expr6 = 3 ; " HAVING " ;

WGH_Fld_Expr6 ) ;

w7 = Case ( PatternCount ( w7 ; "::" ) = 1 ; GetValue ( Substitute ( w7 ; "::" ; "¶" ) ; 2 ) ;

WGH_Fld_Expr7 = 1 ; " WHERE " ;

WGH_Fld_Expr7 = 2 ; " GROUP BY " ;

WGH_Fld_Expr7 = 3 ; " HAVING " ;

WGH_Fld_Expr7 ) ;

f = List ( f1 ; f2 ; f3 ; f4 ) ;

w = w1 & w2 & w3 & w4 & w5 & w6 & w7 ;

s = " SELECT " & d ;

i = ValueCount ( f ) ;

qTab = UniqueValues ( Substitute ( f ; "::" ; "¶::" ) ) ;

qTab = ValueCount ( qTab ) - PatternCount ( qTab ; "::" ) ;

f = If ( i = 1 ; "\"" & f & "\"" ; "List ( \"" & Substitute ( f ; "¶" ; "\" ; \"" ) & "\" )" ) ;

j = "UNION" ;

QA = Case ( i = 1 ; 1 ;

qTab = 1 ; 1 ;

Mod ( i ; 2 ) ≠ 0 ; 0 ;

Substitute ( 10 ^ ( qTab - 1 ) - 1 ; 9 ;

"ad = ad + t ;

t1 = PatternCount ( " & f & " ; GetValue ( Substitute ( GetValue ( " & f & " ; ad ) ; \"::\" ; \"\¶\" ) ; 1 ) ) ;

bl = If ( t / t1 = bl ; bl ; 0 ) ; " ) ) ;

QA = If ( QA = 0 or QA = 1 ; QA ; Evaluate ( "Let ( [ ad = 1 ; t = PatternCount ( " & f & " ; GetValue ( Substitute ( GetValue ( " & f & " ; ad ) ; \"::\" ; \"\¶\" ) ; 1 ) ) ; bl = 1 ; " & Left ( QA ; Length ( QA ) - 2 ) & "] ; bl ) " ) ) ;

lt = Substitute ( 10 ^ i - 1 ; 9 ;

"ii = ii + 1 ;

cf = Quote ( GetValue ( Substitute ( GetValue ( " & f & " ; ii ) ; \"::\" ; \"\¶\" ) ; 2 ) ) ;

ct = Quote ( GetValue ( Substitute ( GetValue ( " & f & " ; ii ) ; \"::\" ; \"\¶\" ) ; 1 ) ) ;

nt = Quote ( GetValue ( Substitute ( GetValue ( " & f & " ; ii + 1 ) ; \"::\" ; \"\¶\" ) ; 1 ) ) ;

fq = Case ( IsEmpty ( fq ) and ii < " & i & " and ct = nt ; \"" & s & "\" & cf ;

IsEmpty ( fq ) and ii < " & i & " and ct ≠ nt ; \"" & s & "\" & cf & \" FROM \" & ct & \" " & j & s & "\" ;

IsEmpty ( fq ) and ii = " & i & " ; \"" & s & "\" & cf & \" FROM \" & ct ;

ii < " & i & " and ct = nt and RightWords ( fq ; 1 ) ≠ \"SELECT\" and RightWords ( fq ; 1 ) ≠ \"DISTINCT\" ; fq & \", \" & cf ;

ii < " & i & " and ct = nt and ( RightWords ( fq ; 1 ) = \"SELECT\" or RightWords ( fq ; 1 ) = \"DISTINCT\" ) ; fq & \" \" & cf ;

ii < " & i & " and ct ≠ nt ; fq & \", \" & cf & \" FROM \" & ct & \" " & j & s & "\" ;

ii = " & i & " and RightWords ( fq ; 1 ) ≠ \"SELECT\" and RightWords ( fq ; 1 ) ≠ \"DISTINCT\" ; fq & \", \" & cf & \" FROM \" & ct ;

ii = " & i & " and ( RightWords ( fq ; 1 ) = \"SELECT\" or RightWords ( fq ; 1 ) = \"DISTINCT\" ) ; fq & \" \" & cf & \" FROM \" & ct ) ; " ) ;

lt = Evaluate ( "Let ( [ ii = 0 ; fq = \"\" ; " & Left ( lt ; Length ( lt ) - 2 ) & "] ; fq )" ) & w ] ;

Case ( QA = 0 ; "ERROR: Multiple tables have been selected and the Qty of fields from each table must be the same" ;

w7 = 0 ; "ExecuteSQL ( " & Left ( lt ; Length ( lt ) - 1 ) & " ; " & Quote ( fs ) & " ; " & Quote ( rs ) & " )" ;

ExecuteSQL ( lt ; fs ; rs ) )

)

You might be interested in comparing what you've done to the system of CF's used in the "button bars as value lists example" found in:

Adventures in FileMaking #2-enhanced value selection

You can check on the Flexible SQL and SQLII tabs to see how this works. You type a simple SQL expression into one text field and a second text field updates with an expanded "safe SQL" expression with no quoted field or table occurrence names and yet has the original SQL in place without inserted & operators and function calls so that it's fairly easy to read and understand the original query expression. You can then copy/paste this expanded expression into the data viewer to test and to add the final query details needed before implementing the result in a script step or calculation field.

All the needed CF's are in the file and may be imported into your own files if you find them useful.