GetBaseTableName ( fieldOrTO )

Gets the base table name (how it's listed in database management) from a field or table occurance

Average rating: 5.0 (1 vote) Log in to vote

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

Sample input:

GetBaseTableName ( myTableOccurance::myField )
GetBaseTableName ( "myTableOccurance::myField" )
GetBaseTableName ( "myTableOccurance" )

Sample output:

"myTable"
"myTable"
"myTable"

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

/*
 *  CUSTOM FUNCTION
 *    GetBaseTableName ( fieldOrTO )
 *
 *  DESCRIPTION
 *    Lists the base table name for the passed field or table occurrence
 *
 *  PARAMS
 *    fieldOrTO: A field's name, reference to a field, or a table occurrence name. If blank, will use the current table name.
 *
 *  RETURNS
 *    The base table name as text
 *
 *  DEPENDENCIES
 *    --None--
 *
 *  CHANGELOG
 *    2021-09-17: Paul McCudden (paul@peak14.solutions): Created
 *    2021-10-19: Paul McCudden (paul@peak14.solutions): Fixed bug that happened when the fieldOrTO wasn't a related field
*/

Let (
    [
        ~fn = Case (
            not EvaluationError ( GetFieldName ( fieldOrTO ) );
                GetFieldName ( fieldOrTO );
            not EvaluationError ( GetField ( fieldOrTO ) ) or EvaluationError ( Evaluate ( fieldOrTO ) ) = 103;
                fieldOrTO
        );
        ~tn = Case (
            IsEmpty ( ~fn ) and IsEmpty ( fieldOrTO ); Get ( LayoutTableName );
            IsEmpty ( ~fn ); fieldOrTO;
            If (
                Position ( ~fn ; "::" ; 1 ; 1 );
                GetValue ( Substitute ( ~fn ; "::" ; ¶ ) ; 1 );
                ~fn
            )
        )
    ];

    ExecuteSQL (
        "SELECT DISTINCT BaseTableName " &
		    "FROM FileMaker_Tables " &
            "WHERE TableName = '" &

                // Use filter values instead of SQL to allow for inconsistent letter cases.
                GetValue ( FilterValues ( ExecuteSQL ( "SELECT DISTINCT TableName FROM FileMaker_Tables "; ""; "" ); ~tn ); 1 )

            & "'"; 
		""; 
		"" 
	)

)

Uses FileMakers System Tables (https://www.databuzz.com.au/using-executesql-to-query-the-virtual-schemasystem-tables/) to get the base table name from the passed parameter. The parameter can be:

A field reference
A dynamically created field name (or any text string that evaluates to a valid field)
A table occurrence name
Blank (will use the current layout's table occurrence)

Comments

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

GetBaseTableName ( fieldOrTO )

Comments

Support this website.

Support this website.