SQLRef ( FieldRef ; Option )
Makes it possible to refer indirectly to field and table occurrence names in SQL queries
Average rating: 4.3 (32 votes) Log in to vote
http://I don't have a URL to post
SQLRef ( GetFieldName ( TO::field ) ; 2 )
SQLRef ( GetFieldName ( TO::field ) ; 3 )
Function definition: (Copy & paste into FileMaker's Edit Custom Function window)
A key problem with ExecuteSQL queries as that you end up with field and table occurrence names quoted in a literal string. If you then rename a field or table occurrence, the query syntax becomes incorrect and you get the infamous ? result.
This function enables your Query to indirectly refer to field and table occurrence names such that renaming a field or table occurrence does not produce an error in a Query that refers to that object.
Example: ExecuteSQL ( "SELECT " & SQLRef ( GetFieldName ( Table::_pkID ) ; 2 ) & " FROM " & SQLRef ( GetFieldName ( Table::_pkID ) ; 1 ) ; "" ; ¶ )
Evaluates the same as:
ExecuteSQL ( "SELECT \"_pkID\" FROM \"Table\" ; "" ; ¶ )
Mike Beargie, MainSpring, Inc.
Aug 15, 2014
Purely by preference, but I cleaned it up and added the GFN() function into the CF section to make it easier to call.
TheField = GetFieldName ( Field );
TheList = Substitute ( TheField ; "::" ; ¶ );
Result = Case (
Option = 1 ; Quote ( GetValue ( TheList ; 1 ) ) ;
Option = 2 ; Quote ( GetValue ( TheList ; 2 ) ) ;
Option = 3 ; Quote ( GetValue ( TheList ; 1 ) ) & "." & Quote( GetValue( TheList ; 2 ));
"Error Option out of range"
Phillip Caulkins, Modesto, CA
Aug 18, 2014
|But doesn't putting the GFN inside the function mean that you have to pass the field parameter as quoted text? That would seem to defeat the purpose of this custom function--to avoid explicitly naming a field reference as quoted text so that future name changes to a table occurrence or field do not cause the query to fail.|
Aug 21, 2015
No, Phillip. Mike's right. See a similar function by Koji.
I created something similar to Koji's then I revised mine to use your passing of other parameters:
fn_convertfield_2SQL ( _field ; _q )
If ( not IsEmpty ( _field )
[ _fullnm = GetFieldName ( _field )
; _list = Substitute ( _fullnm ; "::" ; Char(13) )
; _tabl = GetValue ( _list ; 1 )
; _fld = GetValue ( _list ; 2 )
//; _sqlnm = If ( _q = 1 ; Quote(_tabl) & "." & Quote(_fld) ; _tabl & "." & _fld ) // disabled to use case for more options
; _sqlnm = Case
( _q = "qt" ; Quote(_tabl)
; _q = "qf" ; Quote(_fld)
; _q = "t" ; _tabl
; _q = "f" ; _fld
; _q = "qtqf" ; Quote(_tabl) & "." & Quote(_fld)
; _tabl & "." & _fld // default (other values or empty passed as param)
]; Substitute ( _sqlnm ; Char(34) ; Char(92) & Char(34) )
) // function to return escape-quoted or plain text for SQL from FM fields
; "" ) // end if
Sep 3, 2015
|More testing. I think with un-related fields the GFN() needs to be *outside* the call, as Phil stated. back to the drawing board...|
Oct 27, 2015
Not to quibble, but change the comment to read
Depending on value selected in Option returns TO name, quoted field name, or "TO.Field" text
That way it's in a 1, 2, 3 rather than a 2, 1, 3 order. I often forget what parameters mean about half a year after I write a CF, so comments are helpful.
Nov 3, 2015
|Agreed, Bill! that's why I was trying to come up with "sensible" values to pass, such as qt, qf, t, f, qtqf or "". :)|
Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.