SQLRefSimple ( FldRef ; Option1TO2Fld3Both )

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

Average rating: 3.0 (3 votes) Log in to vote

Bill Thurmes - Show more from this author
MDCA
http://www.miyotadca.com

Sample input:

SQLRefSimple ( Table::Fld ; 1 )
SQLRefSimple ( Table::Fld ; 2 )
SQLRefSimple ( Table::Fld ; 3 )

Sample output:

"Table"
"Fld"
"Table"."Fld"

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

// SQLRefSimple ( TableOccurrence::Field ; Option1TO2Fld3Both )
// 'Simplified' copy of SQLRef, by Phil Caulkins; only change is in name of 2nd parameter, so you always know what 1,2,3 do
//Depending on value selected in Option returns TO name, quoted field name,  or "TO.Field" text
//
Let ( [	FieldRef = GetFieldName ( FldRef ) ;
	Option = Option1TO2Fld3Both ;
	TheList = Substitute ( FieldRef ; "::" ; ¶ ) ] ;
        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"
                   )
        )

First, this is blatantly copied from Phil Caulkin's SQLRef. I use it all the time, but I could never remember, if I want the FieldName, do I use option 1 or option 2? So I changed the name of the parameter, telling the result of each number. Looks ugly, but is very functional.

Indirect ESQL queries, where the field or table names get changed by FileMaker when you change the name of either the table or the field, are much better than direct ESQL queries, where the names are hardwired; the latter produce errors when either name is changed.

Direct (less robust): ExecuteSQL ("SELECT Fld FROM Table"...
Indirect (more robust): ExecuteSQL ("SELECT " &SQLRefSimple(Table::Fld;2)&" FROM "&SQLRefSimple(Table::Fld;1)... evaluates to the the same text as the Direct, but works even if you change the FieldName from Fld to Field.

Comments

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

SQLRefSimple ( FldRef ; Option1TO2Fld3Both )

Comments

Support this website.

Support this website.