SQLGetField ( field )

Use to get the table name for SQL use from a field. Can use a reference as a field so it doesn't matter if the field or table name changes

Be the first to rate this function Log in to vote

Paul - Show more from this author
Peak 14 LLC
http://www.peak14.solutions

Sample input:

SQLGetField ( myTable::myField )

Sample output:

"myTable"."myField"

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

/*
 *  CUSTOM FUNCTION
 *    SQLGetField ( field )
 *
 *  DESCRIPTION
 *    Use to get the table name for SQL use from a field. Can use a reference as a field so it doesn't matter if the field or table name changes
 *
 *  PARAMS
 *    field: Either the fieldName or the field reference
 *
 *  RETURNS
 *    The table name to be used in SQL format or empty
 *
 *  DEPENDENCIES
 *    --NONE--
 *
 *  CHANGELOG
 *    2019-10-16: Paul McCudden (paul@peak14.solutions): Created
 *    2019-10-16: Alex Zueiv: contributed the amazing ~fn calc to avoid fieldname errors
*/


Let (
  [
    ~field = field;
    ~quote = "\"";
    ~fn = Case (
      not EvaluationError ( GetFieldName ( ~field ) ) ;
        GetFieldName ( ~field ) ;
      not EvaluationError ( GetField ( field ) ) or EvaluationError ( Evaluate ( field ) ) = 103 ;
        ~field
    )
  ];

  If ( not IsEmpty ( ~fn ) ; ~quote & Substitute(  ~fn ; "::" ; Quote ( "." ) ) & ~quote )
)

Can pass in a dynamically created field name or a reference with or without the table name included. Very helpful for when field names can change.

Comments

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

SQLGetField ( field )

Comments

Support this website.

Support this website.