SQLRef ( FieldRef ; Option )

Makes it possible to refer indirectly to field and table occurrence names in SQL queries

  Average rating: 4.3 (33 votes) Log in to vote

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

  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, 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, 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, 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, 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, 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, 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.

Support this website.

This library has been a free commmunity resource for FileMaker users and developers for 20 years. It receives no funding and has no advertisements. If it has helped you out, I'd really appreciate it if you could contribute whatever you think it's worth: