#Toms guide filemaker pro free how to

Since no layout is used, any fields in the table occurrence can be used. Find Specific Fields in a TableĪdd the names of fields to return just those results. My preference is to use uppercase for the keywords, so they are easy to find in my scripts and calculations. The SQL statement commands are also not case sensitive, so that ” SELECT * FROM ” is the same as ” select * from “. Names are case insensitive for SQL tables and columns. If you have named your table occurrence with characters (spaces or other characters that may return an error to your result), then quote it: ExecuteSQL ( ' SELECT * FROM 'my table' '

The “*” (asterisk) is a shortcut for “all fields/columns” and since we have not used a WHERE clause, all records and all fields will be returned from the specified table occurrence. All records are available (with permission) to make the queries. The “found sets of records” are not used when using the ExecuteSQL function. Keep in mind that if you are using fields to supply the criteria (any clause), that you may need to be on a layout that shows the field available in the Specify Calculation dialog. No FileMaker layouts or relationships are required to make ExecuteSQL return results. Some of the SQL functions may or may not work as expected. NOTE: there is no differentiation between FileMaker table occurrences and External SQL Sources (ESS) table occurrences on the Relationship graph. And the table must have at least one record to return any result (see the system functions in the example database). The table occurrence must be ON the relationship graph in the file where the ExecuteSQL is performed, as you cannot query an unknown datasource. The function will not “filter” from the relationship (or any) context. You must use FileMaker table occurrences (T.O.) as named on the relationship graph for your ExecuteSQL queries, but they will evaluate as if the base table had been selected. You must have something to find (SELECT *) and a table (FROM «mytable»). In its most basic statement, SQL SELECT will find all columns (fields) from a single table: SELECT * FROM mytable ]Ĭomma-separated sort list Find Everything in a Table The “?” arguments are used with the ExecuteSQL(sqlQuery fieldSeparator rowSeparator. To become familiar with the function, start with the ExecuteSQL, FM12 help topic. Any discrepancies between the reference and the function will be noted here, if possible. The FileMaker 12 ODBC and JDBC Guide is helpful, but it has uses outside (and beyond) the ExecuteSQL() function. This is the ExecuteSQL() function reference for which you’ve been waiting. It may not have all the nuances needed for other data sources. This is not a complete FileMaker and ESS guide using SQL calls (if using Import or Execute SQL script steps or ExecuteSQL() function or ESS). This is not a complete FileMaker and SQL guide, as FileMaker may be an ODBC source and the SQL queries made against it may vary from the terms used by ExecuteSQL(). This is not a complete SQL guide, as other databases may use other syntax.

There you will find links to solutions that help you create and test your queries. If you don’t need to learn the terms, jump right to the Helpful Example Databases section, below.

#Toms guide filemaker pro free how to

Since there are already many examples of how to write the ExecuteSQL queries, links to these will be listed at the end of this article. This tutorial attempts to explain some of the SQL terms, if you are new to writing SQL statements. There seem to be many questions about the usage of SQL (Structured Query Language) with the ExecuteSQL function in FileMaker 12.