Brian Dunning's FileMaker Custom Functions

SQLFieldName ( Field )

This function is like GetFieldName () for ExecuteSQL function. Make FQFN quoted and dot separated for ExecuteSQL function.

  Average rating: 4.4 (29 votes) Log in to vote

Koji Takeuchi   Koji Takeuchi
TonicNote, Inc.
https://tonic-note.com

Share on Facebook Share on Twitter

  Sample input:
SQLFieldName ( name_to::name_field )
  Sample output:
\"name_to\".\"name_field\"

  Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

SQLFieldName ( Field )
2012.09.05, Koji Takeuchi


description:
Make FQFN quoted and dot separated for ExecuteSQL function.
ex1:
SQLFieldName ( name_to::name_field )
-> \"name_to\".\"name_field\"
ex2:
SQLFieldName ( name_field )
-> \"name_to\".\"name_field\"
# When TO name is not specified, complemented by current TO name.

 

Comments

Ryan Tayco   Ryan Tayco, Philippines
Jan 27, 2018
Using quotation only in FQFN sometimes will not work especially when it starts with special characters (ex. _Table::Field or Table::_Field)

I modified your script and replaced the Quote() with "\\\"" from the start and at the end.

From: Quote ( GetValue ( split ; count_split ) )
to: "\\\"" & GetValue ( split ; count_split ) & "\\\""

This is the modified version of your custom function:
================================
COPY BELOW

/*
SQLFieldName ( Field )
2012.09.05, Koji Takeuchi <takeuchi@nemoux.com>


description:
Make FQFN quoted and dot separated for ExecuteSQL function.
ex1:
SQLFieldName ( name_to::name_field )
-> \"name_to\".\"name_field\"
ex2:
SQLFieldName ( name_field )
-> \"name_to\".\"name_field\"
# When TO name is not specified, complemented by current TO name.

-------------------------------------------------------------------
Revision:
01/27/2018, Ryan Tayco (mybase.solutions@gmail.com)

The backslash "\" is missing and the original script only quotes the values
which will not work on FQFN start with a special character.

*/

Let (
[
split =
Substitute ( GetFieldName ( Field ) ; "::" ; ¶ ) ;
count_split =
ValueCount ( split ) ;
name_to =
Case (
count_split > 1 ;
"\\\"" & GetValue ( split ; 1 ) & "\\\"" ;
"\\\"" & Get ( LayoutTableName ) & "\\\""
) ;
name_field =
"\\\"" & GetValue ( split ; count_split ) & "\\\""
] ;

name_to & "." & name_field
)
 
Koji Takeuchi   Koji Takeuchi, TonicNote, Inc.
Jan 28, 2018
Thanks for comment and my apologies...

Maybe the description which I wrote is not appropriate, but back slash itself is not a big deal.
Name of TOs and name of fields just need to be quoted in calculation and it works fine in query even if TO (field) name is like "_ORDER::__id".
 

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.