BTW, this is not documented in the FMP 12 help.
1 of 1 people found this helpful
>> BTW, this is not documented in the FMP 12 help.
BTW, this IS documented in "The Missing FM 12 ExecuteSQL Reference"
be sure to get the PDF as it is more complete than the article and get the demo file(s) to see even more examples.
I thought we all kinda knew that the graph is the only reality when it comes to FileMaker data.
Nope. Still learning.
Since its a visual representation of the relationships, would we call it visual reality?
P.S. any table occurrence on the graph can be used with eSQL. HOWEVER, the context is as if the base table is called in the FROM clause (relationship ignored).
-- sent from my iPhone4 --
Thanks Beverly. I should have looked at my copy of your document on my iBooks - it is a constant companion for me when writing ExecuteSQL!
I find it odd that you must use a FMP occurrance name when the only thing ExecuteSQL knows is the base table. I can see how this would be confusing because a table of name PATIENTS may not have an occurrance with that exact name, and using any valid occurance will resolve to PATIENTS anyway. The graph does not matter at that point since FMP relationships are not considered.
I have not run into this issue because I typically have the generic table name in the graph. Appears to be an odd choice by the FMI programmers for v12, but maybe there will be some reason in the next rev.
I find it odd that you must use a FMP occurrance name when the only thing ExecuteSQL knows is the base table.
Yep, that's what I thought.
I have not run into this issue because I typically have the generic table name in the graph.
Same here. I was an early adopter of eSQL and this is the first time I encountered this.
Unfortunately, FileMaker's own documentation remains muddled on the distinction between tables and table occurances. For example, the functions Get(LayoutTableName) and Get(ActiveFieldTableName) both return the name of the TO, not the base table. One would never know that, however, just by reading FM's help pages on those functions, where the term "table occurance" is never uttered.
"Table" references in functions—including the intentionally context-naive ExecuteSQL function—and calculations seem to always need a TO to serve as a proxy to the underlying base table. Can anyone think of exceptions where a function either returns a true base table name or requires that a table be referenced by its base table name?
Layout is based on the TO, not the base table. It would "filter" the records if there were restrictions or relationships that would narrow a "found set" (by GTRR - go to related records). Fields are on the Layout and may also be restricted in the access/privileges. Having the TO for the field is because it's on the layout.
Access through eSQL is not layout based (no context), ergo any TO used/called will really be calling the base table.
If you use this eSQL function FILEMAKER_TABLES, you will get a list of the available tables - included will be the TableName AND BaseTableName.
Base tables might make sense for the file you're in, but how would you reference tables in other files? I don't know if they could have done it any other way but with table occurrences.
what is esql?
esql is jargon for ExecuteSQL.
So that we don't confuse the function ExecuteSQL() with the script step Execute SQL or because we are too lazy to type it all out.