Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Code Block
linenumberstrue
Let ( [ ~sql = "
	SELECT t1.~field
	FROM ~table1 t1
	JOIN ~table2 t2
	ON t1.~field = t2.~field
	WHERE ~field LIKE '%~value%'
	AND ~field=?
	ORDER BY ~field";
 
	$sqlQuery = Substitute ( ~sql ;
		[ "~table1" ; SQLTableName ( Table1::fieldName ) ];
		[ "~table2" ; SQLTableName ( Table2::fieldName ) ];
		[ "~field" ; SQLFieldName ( Table1::fieldName ) ];
		[ "~value" ; Table::field ]
	);
 
	$sqlResult = ExecuteSQL ( $sqlQuery ; "" ; "" ;
    	$value;
    	$value[2];
    	$value[$n]
	)
];
	//Substitute ( $sqlQuery ; "	" ; "" ) &¶& // sql preview
	If ( $sqlResult = "?" ;
		Let ( ~debug = False ; If ( ~debug ; SQLDebugResult ( $sqlResult ) ; False ) );
		$sqlResult
	)
)

 

In order to see any SQL errors, the above calculation must be used within FileMaker's Data Viewer. The SQLDebugResult custom function (below) is very simple and was a hidden "feature".

 

Code Block
titleSQLDebugResult (custom function)
linenumberstrue
If ( sql = "?" ; "" ; sql )

//If passing in ExecuteSQL results in an error, return empty so the error will be returned within the Data Viewer.