Brian Dunning's FileMaker Custom Functions

SQLRefSimple ( FldRef ; Option1TO2Fld3Both )

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

  Average rating: 3.5 (2 votes) Log in to vote

Bill Thurmes   Bill Thurmes
MDCA
http://www.miyotadca.com

Share on Facebook Share on Twitter

  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)

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

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.