Skip to end of metadata
Go to start of metadata

There are two custom functions related to SQL, one is GetFieldNameSQL and the other is GetTableNameSQL. They were designed to get the right field name and table name even if the name is modified in database management. They looks as follows.

 

 

GetFieldNameSQL

Let([

f = GetFieldName(Field);

b =Substitute(f; "::";¶)

];

Quote(GetValue(b;2))

)

GetTableNameSQL

Let([

f = GetFieldName(Field);

b =Substitute(f; "::";¶)

];

Quote(GetValue(b;1))

)

 

When you are working on ExecuteSQL, try to use these two functions to get field and table name instead of using the name directly. Examples are listed below.

 

 

Original SQL in FileMaker

Modified SQL

ExecuteSQL ( "SELECT firstname FROM Employees WHERE empID =?";"";""; $empID)

ExecuteSQL ( "SELECT" & GetFieldNameSQL(Employees:: firstname) &

" FROM  " & GetTableNameSQL(Employees::firstname) & " WHERE " &  GetFieldNameSQL(Employees::empID) & " =?";"";""; $empID)

 

Reminders:

1.? is used to represent the parameter in where statement and you can add the real parameter in the end of the SQL.

2. Remember to put every words except the fieldname and table name in “” and keep space in front of and in the end of each word.

  • No labels