SQLFieldName ( Field )
This function is like GetFieldName () for ExecuteSQL function. Make FQFN quoted and dot separated for ExecuteSQL function.
Average rating: 4.4 (30 votes) Log in to vote
|
Koji Takeuchi - Show more from this author
TonicNote, Inc. https://tonicnote.com |
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 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, 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". |
||
Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.