I need to use ExecutiveSQL to grab a value from an External Data Source, to use in a script.
The value is the path from where I want to grab a file to use in a File.
I have never used SQL. I did google but it did not make sense to me.
The ExecuteSQL function only works with table occurrences of the file's relationship graph.
Claris FileMaker’s ExecuteSQL() function is independent of the relationship graph.
ExecuteSQL runs SQL queries directly against the underlying tables, so:
-
It does NOT use relationships you’ve defined in the graph.
-
You must explicitly define joins using SQL (
JOIN,WHERE, etc.). -
It works more like querying a traditional relational database than navigating FileMaker’s native relationship system.
For example, even if two tables are related in the graph, ExecuteSQL() won’t “see” that relationship unless you recreate it in your SQL query.
So, while you still need matching "keys" for ExecuteSQL, it does NOT rely on the RG.
--
Here is a super simple example.
You have two tables: CUSTOMER and ORDERS
No relationship in RG.
CUSTOMER table has all the FileMaker default fields plus "name"
ORDERS table has all the FileMaker default fields plus "CUS_FK" (this is the primary key from the CUSTOMER table)
All we need to do is tell SQL that we want certain fields and "Join" the two tables on the PRIMARY KEY field in CUSTOMER with CUS_FK in ORDERS.
(this is similar to what the RG does, but we do not need a relationship in RG for this to work.)
ExecuteSQL ( "select name,
o.orderNumber,
o.cus_fk
from
customer c, orders o
where
c.PrimaryKey = o.cus_fk"; "," ; "¶" )
--
For my contrived example with one CUSTOMER, phil, and three orders in the ORDERS table, we get:
Phil,1,F87602ED-843B-4112-94DC-5D79BCAA8972
Phil,2,F87602ED-843B-4112-94DC-5D79BCAA8972
Phil,3,F87602ED-843B-4112-94DC-5D79BCAA8972
--
SQL is so important everywhere in all relational databases.
HTH
I would really recommend you learn SQL.
“SQL is the lingua franca of relational databases.”
It's all I use (except, sometimes, in FileMaker).
Not trying to pick a fight here… but I assure you that ExecuteSQL () is fully dependent on the relationship graph.
I wrote that the function only works with table occurrences. I never mentioned relationships. Table occurrences are creatures of the relationship graph.
And no… ExecuteSQL () doesn't run queries directly against the underlying tables. If a table name is not in the relationship graph, the query will fail.
In SQL databases like MS SQL, relations are defined on tables, and those relations set the rules about querying data globally for an application.
FileMaker runs on a relational database, but it is not a SQL database. Since there a no relation on the tables, FileMaker can't set the rules that way,
As bdbd said, in FileMaker, relations are established between TO, that is Table Occurrences. The TOs are kind of aliases for the tables. On top of that, FileMaker has a richer set of logical conditions one may use to link fields on TOs.
In FileMaker, what set the rules for querying data are the relations established on the Relationship Graph. Whatever way you "connect" to a FileMaker Database, from within FileMaker or through an API, you need to specify a Layout, something not known in SQL world. A Layout is created on a TO, that TO and it's related TOs define the Domain that is available to query.
When you use ExecuteSQL() you do it in the context of a Layout - context is very important in FileMaker. The SQL Query is translated for Draco - the database engine - in order to run it. That is why ExecuteSQL() can be slower, but in some circumstances it is quite fast.
@bdbd, if I said something wrong, please do not hesitate to correct me.
Let me start by saying that my answer to the original post (OP) was to specify that ExecuteSQL () is dependent on the relationship graph. I said that because the OP stated "I need to use ExecutiveSQL to grab a value from an External Data Source". For this to work, the external data source must be represented in the file's relationship graph.
While ExecuteSQL () is dependent on the relationship graph, it is independent of the relationships defined in the relationship graph and independent of context. That's because ExecuteSQL ()'s query must define the context and all relationships.
I'll not go into the details of a query. There's enough material for a course on that subject alone. If interested, W3 School's web site is a good place to learn the basics of SQL queries.
I'd like to list a few common ways that one might use to grab a value from an External Data Source's table, along with brief descriptions of the additional code required to support each method mentioned.
1) Expose the external table on the relationship graph, and grab the value through a relationship.
This would require:
- Setting up an external data source definition, pointing to the external file
- Adding a Table Occurrence to the relationship graph
- Adding one or more relationships to allow access to the aforementioned Table Occurrence from other contexts.
2) Expose the external table on the relationship graph, and grab the value via ExecuteSQL.
This would require:
- Setting up an external data source definition, pointing to the external file
- Adding a Table Occurrence to the relationship graph
3) Provide a script in the external file, which can be called to return the desired value.
This would require:
- Setting up an external data source definition, pointing to the external file.
- Adding a script in the external file
Comments:
-
My main motivation for sharing the above is to clarify that ExecuteSQL would not be a "magic bullet" that allows for accessing an external value without any other supporting code or architecture, or setup.
-
Also, I'm hoping that the difference between item 1 and item 2 will help illustrate an attractive aspect of using ExecuteSQL, which is the freedom from having to define relationships on the relationship graph.
-
The above said, please do not confuse the lack of necessity for relationships to be equivalent with being entirely free of the relationship graph. This is not the case. As already mentioned by @bdbd, Table Occurrences are a requirement for effectively using ExecuteSQL, and Table Occurrences are defined in the FM relationship graph.
-
The above list isn't exhaustive; these are just a few common approaches.
-
It also does not discuss the matter of security/permissions across separate files.
-
If I have this same need in multiple files, i.e., several separate files need to access this external value, then I start to lean towards something along the lines of option #3 listed above, i.e., define a script in the external file which can be invoked to return the target value, or any of several target "config values" which can be specified via a supplied script parameter. I like this because it minimizes the amount of supporting modifications that I need to make to each consuming file, pretty much limiting the need to defining an external data source entry in each such file. This approach assumes that each file's need for accessing the value would happen within a script that would get the value by calling the external script (hence the need for the external data source, to make invoking the external script possible).
Agreed, the tables themselves need to be in the RG, but not the "relationships" between them for ExecuteSQL() to work as with Regular FileMaker data operations.
Per @steve_ssh 's comment to me, I removed the "untrue" comment I added above. Sorry for any confusion.
Thanks for all of the SQL lessons. I am still confused.
I want to use the paths specified in MA_Pref within the scripts of 22 other files on the FMS Server
I put the MA_Pref on the RG, without a relationship it would not populate in the script. Using a CONSTANT in both MA_Pref and Frost_Upload, it worked. I do not want to go this route, so I looked at ExecuteSQL.
I googled how to use ExecuteSQL to retrieve one field's value. The SNIP I posted is what I got. I do not follow SQL enough to make the substitutions for real time.
I would recommend ChatGPT, never Google (well, for privacy, never Google for anything). You'll get, with ChatGPT (or other), IMHO, MUCH MUCH better responses.
It sounds as though MA_Prefs is only one record, used to store preferences. If that is true, then you may use an "X" relationship to between "Data" and "MA_Pref", using a field on either side that will never be empty, such as a primary key.
Having done that, you don't need ESQL, you simply set the variable to MA_Pref::Receiving_Server.x & "Frost_upload.xslx"
The basic SQL statement looks like this: SELECT a FROM b WHERE c, where a is a list of field names, b is a table name and c is a condition.
The list of field names is comma-separated. Each field name can be fully qualified or not. A fully qualified field name in SQL takes the form of tableName.fieldName.
The table name is the name of the table occurrence in the relationship graph.
The simplest condition takes the form of leftSideArgument = rightSideArgument, where each argument is a field name or a value. The leftSideArgument is typically the name of the field on which to search. The rightSideArgument is typically the search term.
Be mindful of the fact SQL has reserved characters and words, that the SQL syntax uses characters that could be found in FileMaker table or field names and that the first word characters have additional limitations. Some field names of the MA_Pref table conflict with SQL, namely the period. The best is to remove special characters from names. Names must otherwise be quoted in SQL statements.
The ExecuteSQL ()'s query parameter must be a string or a calculation that returns a string. The string must be a valid SQL query. I see FileMaker formatted field names in the string of the script example. These are not merged as part of a calculation. This example cannot yield a valid SQL statement.
You need not relate any table in the relationship graph for Execute () to query these tables.
As @Malcolm said, one gets the impression the MA_Pref table contains only one record. If this is true, there is absolutely no need to use ExecuteSQL ().
Again, W3 School's web site is a good place to learn the basics of SQL queries.
Hope this helps.
Good post, @bdbd!
I would just add that if you (the "OP") use a third-party SQL tool (like "DataGrip") it can connect to any JDBC-enabled database (including FileMaker). Such a powerful SQL tool can also help you with queries where you see all the options-as you type them. It also has "AI" built in and can help you write the query.
Highly recommended. Really speeds up SQL development.
Once you get a query working, you can add parameters for FIleMaker's ExecuteSQL syntax.
Thanks, I have never created an "X" relationship. Connect a field from each table and designate it "X".
That seems to work for me. The file I am testing a fairly limited on, just one table.
In other files that have more tables, I need to create the "X" relationship to each table that gets an IMPORT or EXPORT script?
I recommend checking there is effectively only one record in such a table, maybe when starting the app. I have seen situations where there were more than one record, not very pleasent.
There are four of us that use FileMaker. The other three only have four or five of the 22 that they access. None even know about MA_Pref. So there is just me using it to build new functionality.
Do you use the debugging tool?
I would put a stop point on the Export Records and step through the script in debug mode. Inspect the variable that stores the export path carefully. We want to make sure that we are generating the correct path.
If the variable is wrong it can mean that the field being referenced is empty, or that the relationship isn't valid in the current context.