Brian Dunning's FileMaker Custom Functions

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

Phillip Caulkins   Phillip Caulkins
Caulkins Consulting
http://I don't have a URL to post

Share on Facebook Share on Twitter

  Sample input:
SQLRef ( GetFieldName ( TO::field ) ; 1 )
SQLRef ( GetFieldName ( TO::field ) ; 2 )
SQLRef ( GetFieldName ( TO::field ) ; 3 )
  Sample output:
"TO"
"field"
"TO"."field"

  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\" ; "" ; ¶ )

 

Comments

Mike Beargie   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.

Let ([
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"
)
];
Result
)
 
Phillip Caulkins   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.
 
Beverly   Beverly, KY
Aug 21, 2015
No, Phillip. Mike's right. See a similar function by Koji.
https://www.briandunning.com/cf/1476

I created something similar to Koji's then I revised mine to use your passing of other parameters:

/*
fn_convertfield_2SQL ( _field ; _q )
beverlyvoth
*/

If ( not IsEmpty ( _field )

; Let(
[ _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
 
Beverly   Beverly, KY
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...
 
Bill   Bill, Longmont
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.
 
beverly   beverly, KY
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 "". :)
 

Log in to post comments.

 

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

Under construction. Email me your wish list for improvements.