"No Holding Back FileMaker Blogging"
|
Navigation:
|
Support this site by clicking on a sponsor below or becoming a patron!
Fireside FileMaker Podcast
Become a patron of this FREE web site!
|
|
Recent Blogs:
|
Window Locking
 |
Everything Changes
 |
Subsummary Unique Count
 |
Scripted Change Log
 |
Abstracted Log
 |
|
Meta-Consulting
Meta-consulting is a service Database Pros offers to help other developers
over programming hurdles via GoToMeeting. Email John Mark Osborne or call John Mark at (909) 393-4664 to find out more about this service.
|
Create a Complete Contact Manager
|
Quick Tip:
|
Open Script Disabled
If you have a multiple file solution, it is possible for open scripts, specified via File Options, to not run on opening of the file. This can happen when a file is opened hidden by a related field or via a Perform Script step. Only when the hidden file is selected from the Window menu and displayed will the open script perform. The reasoning behind this behavior is efficiency. If you have to wait for an open script to run just to see data in a related field, you will have unhappy users. Also, imagine if the open script shows a dialog and interrupts what the user is looking at on the original file. The only way to guarantee an open script runs is to use the Open script step or launch the file manually. |
|
|
Tips & Tricks Videos
|
Fun Stuff:
|
Hall of Fame
I started working with FileMaker 25 years ago and a lot has changed. Here are the folks I think need to be recognized for their effort in shaping the early FileMaker (in no particular order): |
|
|
Document Management Videos
|
RSS Feed
|
|
|
|
Preferences and Options
|
Level: Advanced
Version: FileMaker 16
Category: Calculations
Tuesday, October 24, 2017 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
I'm no SQL expert but I know when, and when NOT, to use the ExcecuteSQL function. ExecuteSQL is not for creating replicas of portals, despite the numerous examples floating around the internet. It's also not designed to replace the built-in FileMaker find feature, even though it seems to replicate the search abilities of find mode. In fact, there’s really nothing ExecuteSQL can do that a relationship can’t do, it just can do it without a relationship. In other words, it resolves Relationship Graph clutter but, only under under certain circumstances.
What is SQL and how does FileMaker Support it?
This article will focus on the ExecuteSQL function and how to create a Preferences and Options area, along with a discussion on how and when ExecuteSQL should be used. But, a quick overview of the gamut of SQL abilities, contained within the FileMaker application, will assist in understanding how the ExecuteSQL function fits into the big picture. Through the years, FileMaker has steadily increased it's support of SQL. It's very mature now with many features for accessing the SQL engine. While I've used all the SQL abilities in FileMaker, ExecuteSQL is by far my most commonly utilized SQL feature.
If you aren't familiar with SQL, it's an acronym for "structured query language". Many database applications use SQL to perform finds, analyze relationships and create, edit and delete records. Programs like Oracle, Sybase, Microsoft SQL Server, Access and Ingres all support the SQL language and actually rely on it. FileMaker is no different than these heavy hitters, allowing access to the SQL language via a script step called Execute SQL, ODBC/JDBC imports and a calculation function called ExecuteSQL. However, FileMaker doesn't rely on the SQL language like many other database applications. Instead, it uses SQL to enhance it's feature set.
Now, don't confuse the Execute SQL script step with the ExecuteSQL calculation, as they are very different. Their only similarity is their name (although the function has no space). The Execute SQL script step supports inserts, deletes, updates and all the other good stuff available from SQL but only when acting on external data sources like Microsoft SQL Server or Oracle. However, the ExecuteSQL function is an entirely different beast, allowing interaction with FileMaker itself but only in terms of performing a search. There's no adding, deleting or editing records with the ExecuteSQL function. Importing with ODBC or JDBC also allows for SQL queries but into external data sources in order to find the data for the import.
Choose Wisely
As mentioned at the beginning of this article, ExecuteSQL is not a substitute for other features in FileMaker, like relationships or find mode. ExecuteSQL is designed to reduce relationship clutter. So, what does that mean? Well, have you ever found yourself adding a table occurrence and creating a relationship just for a single calculation or script. It makes me cringe sometimes. Each time you have to do this, the relationship graph becomes more and more difficult to navigate. In fact, FileMaker, Inc. has introduced dozens of features over the years to reduce relationship and other Manage Database clutter including Filtered Portals, Conditional Formatting, Hide Object, Placeholder Text, Custom Functions and ExecuteSQL.
If you already have the relationship in place to show related records in a portal or display some related fields then there's no need to use ExecuteSQL to query for some results. It's much easier to use the List function, in most cases, since it can result in a return-separated list of related values. It's only when you have to create relationships for no other reason than to support a single feature in your solution, that ExecuteSQL starts becoming handy. Once you recognize that ExecuteSQL is designed to clean up the relationship graph from unnecessary table occurrences and relationships, it becomes easier to understand. I see so many people substituting ExecuteSQL for FileMaker features that are better suited for the task, it makes me wonder if some developers are familiar the breadth of FileMaker abilities available to them or do they just use what's comfortable. Choosing the right feature for the right job is key to designing a smoothly running FileMaker solution.
ExecuteSQL Reach
Since ExecuteSQL can only query a FileMaker table and return the results, it has a very refined skill set. The results that are returned do not allow for interaction with FileMaker records like a list view layout. ExecuteSQL merely has the ability to display the data it found as a delimited list of values. Sure, it can simulate a portal by combining the results with a Web Viewer and an HTML table but that's where it's reach ends. Knowing the limitations of ExecuteSQL is the first and foremost concern when deciding whether or not to employ this function. The second consideration is whether it has a distinct advantage over other FileMaker features, such as speed, simplicity or, as I am writing about, uncluttering the relationship graph.
Understanding through Example
It seems as if ExecuteSQL isn't very useful until you consider how to wield this focused weapon in the scenarios where it is best suited. Basically, it allows you to query any table, without a relationship, and return the record data as a calculated result. So, how can this tool be used? I find a good example really helps in the learning process but, especially so in the case of ExecuteSQL. The example I have chosen for this article is a Preferences and Options area. Imagine a client asking for each user on a solution to be able to personalize their experience. For instance, a user might want to specify a startup layout or the size of their screen. Solution wide options can also be handled with ExecuteSQL, gathering formatting specifications for phone numbers or company information.
A relationship can gather this information just as easily as ExecuteSQL but, what if the information from Preferences or Options needs to be utilized in a variety of different tables? That's a new relationship for every table that needs to access the Preference or Option data, creating relationship clutter. ExecuteSQL requires no relationships or table occurrences to gather related data. In fact, a single calculation function can gather data from a Preferences or Options table no matter where it needs to be delivered. That's because ExecuteSQL is context independent. It doesn't care, like relationships, about the starting point, just about where it is gathering the data from.
Preferences and Options Tables
The Preferences will ultimately contain a record for every account using the solution and one field for every preference setting. On open, I like to run a script that checks to see if the account logging on is represented by a record in the Preferences table and add it when necessary. It might look something like the following code:
It's very important to set the fields in Preferences to auto-enter the default data into each preference field so there is an initial value for new accounts. If you have a preference for the startup layout, you might auto-enter the standard layout name like "CONTACT_Form" but make other layout choices available via a popup menu. The variety of preference options vary greatly but another one I like is screen size. I usually allow them to choose a percentage of their screen size but you could also provide specific dimensions in a popup menu. And, don't forget to auto-enter the account creation value that will be required to locate the correct preference record!
Notice the "account_create_search" calculation field. It simply transforms the account name stored into lowercase. This is very important since SQL performs case sensitive searches while FileMaker is not concerned with case when it comes to account names and will auto-enter what the user types in on logon. Passwords are a different case, of course, requiring the case to match. The trouble comes if the user enters a proper case account name on first opening and then lowercase on subsequent logons. The proper case account name is stored on their preference record and the search is performed using a lowercase account, creating a disconnect you don't have to be concerned with most other FileMaker features.
ExecuteSQL Basics
Before showing the code to grab a preference value for the current account, let's start with the basics of ExecuteSQL. There are four possible parameters: the SQL query itself (which is contained in quotes), the field separator, the row separator and the optional arguments:
ExecuteSQL(
SQL Query; //The SQL code is contained within quotes
Field Separator; //"" designates default of comma
Row Separator; //"" designates default of carriage return
Arguments //Use the question mark to specify one or more FileMaker calculation formulas
)
Here's a quick example with real data:
ExecuteSQL(
"select name_first, name_last, number from CONTACTS where name_first = ?";
"";
"";
EXECUTESQL::Search
)
The first parameter specifies the SQL query. In this example, the query grabs the First Name, Last Name and Number fields from the CONTACTS table where the First Name field equals the contents of the Search field. Think of it as a find in the CONTACTS table for all records with a first name matching the contents of a global Search field. If records are found, the First Name, Last Name and Number fields are returned. This complicated query language takes time to become familiar but always remember there are tons of examples on the internet to glean.
The second parameter is the field separator. This is similar to when you are exporting data. If you export in tab-separated format, each field is separated by a tab character and each record by a carriage return. In the case of ExecuteSQL, you get to specify the field separator. If a blank value ("") is specified, a comma is used to separate the fields.
The third parameter is the row or record separator. If a blank value ("") is specified, a carriage return is used to separate the rows.
The optional fourth parameter refers back to the question mark from the first parameter. It is an argument that is evaluated and used in place of the question mark. As many arguments as desired can be used and are referred to the parameters after the third, in order of appearance. Compare how arguments in the ExecuteSQL function work with variables in the Let function. You can declare them to make your code easier to update and easier to read. However, according to FileMaker, Inc., parameters are used to prevent security vulnerabilities through injection attacks.
Honestly, most people don't worry about injection attacks but making your code easier to write is all important. Arguments accomplish this by allowing you to work within the standard calculation engine. The FileMaker SQL parsing engine can be very picky when including FileMaker functions whereas the arguments parameter allows for the flexibility you are used to in FileMaker calculation dialog. I also like that arguments function similarity to the Let function, making your code easier to organize.
When the Search field contains "John", the result of some sample data might look like the following:
John,Osborne,11
John,Griswald,25
John,Pantaleon,98
John,Zayas,99
John,Hiraki,15
DEFINITION: SQL injection is used to attack data-driven applications, in which nefarious SQL statements are inserted and executed (e.g. grab the entire database).
Preference Examples
Let's move onto our real example demonstrating how to grab preference record information specific to the current account. Here's the ExecuteSQL formula to grab the screen size from the Preferences table:
ExecuteSQL(
"select screen_size from PREFS where account_create = ?";
"";
"";
Lower(Get(AccountName))
)
It uses reserved "select" and "where" SQL clauses to locate a field titled "screen_size" in the "PREFS" table where the account name in PREFS equals the current account name or Get(AccountName). It's important to note again that the Lower function is employed since SQL is case sensitive and FileMaker does not care about case when receiving the account name on logon. In addition, since only one record will be found and only one field value will be grabbed, the field and row separators are inconsequential.
Here's the same code but without the use of the optional argument parameters. Notice the single quotes that are required, making the code harder to construct:
ExecuteSQL(
"select screen_size from PREFS where account_create = '" & Lower(Get(AccountName)) & "'";
"";
""
)
Here's an example of the code in the Move/Resize Window script step to resize the window on startup to a percentage of the entire screen.
Notice the use of a Custom Function called "Preferences" to centralize the code. If the code ever changes, using a Custom Function allows it to be changed once and flow out to all the places where it is being used. Here's the Custom Function code:
ExecuteSQL(
"select " & Field & " from PREFERENCES where account_create_search = ?";
"";
"";
Lower(Get(AccountName))
)
The only difference fromthe original code is a reference to a custom function parameter called "Field". When the Custom Function is called from a calculation formula, fields from the Preferences table are designated by putting the field name in quotes.
It's important to reiterate that the above formulas don't require context like a relationship. ExecuteSQL works outside the relationship engine. It can simply reach out and grab data from any table it so desires, requiring no relationships. But, that amazing ability is limited by the way the results are returned. You don't get a found set of records but a data set of values. However, in this example, all that is needed is data to allow a script to branch correctly, so it works perfectly. I believe ExecuteSQL becomes less effective as soon as multiple records and field values need to be collected.
Options
Options work almost exactly the same except that no find is required. Options are solution wide settings, like phone formatting or company information, so there is only one record in the Options table. Therefore, all you have to do is select the field:
ExecuteSQL(
"select " & Field & " from OPTIONS";
"";
""
)
In other words, there are no find criteria wuth the "where" SQL clause, as seen in the Preferences ExecuteSQL formula. For completeness, here's the Custom Function for Options:
ExecuteSQL(
"select " & Field & " from OPTIONS";
"";
""
)
The Options Custom Function can be called from within a calculation dialog using the following function and parameter call so it can be included in an auto-enter calculation attached to a phone field:
Options("phone_formatting")
What about Containers?
Since ExecuteSQL returns text data, how can you grab the content of container fields? For example, let's say we are trying to maneuver a logo from Options to a print layout. The first step is to encode the logo in the Options table using the following formula in a calculation field. Name the calculation field "logo_ascii" and choose a text result:
Base64Encode(logo)
In the table where the printing is occurring, create a calculation field using the following formula with a container result:
Base64Decode(Options("logo_ascii"); "logo.jpg")
What Else?
There are many other uses for ExecuteSQL. My aim in this article was to help you understand how to figure out when to use ExecuteSQL. Yes, I did use a specific example throughout much of the article but that's not really the point. While Preferences and Options are worthy ExecuteSQL solutions, every developer has different needs. Learn how and why you would want to use ExecuteSQL. Don't just apply the ExecuteSQL function to a problem you don't know how to solve any other way. Learn the plethora of FileMaker features and apply them where each makes sense.
Author:
John Mark Osborne
jmo@filemakerpros.com
www.databasepros.com
Example File (available to patrons at $5.00 per month): Download here if you are a patron
This blog is completely free. Please support it by clicking on one of the advertisers at the left side of the window or becoming a patron. Thanks so much!
|
|
Comments:
|
|
|
Saurabh Das
|
03/30/2020
|
|
|
Thank you for sharing your views on Structured Query Language or SQL function in FileMaker systems in such great detail. People who are still getting acquainted and familiarised with the unique features of FileMaker and the use of SQL are sure to appreciate this post greatly. It is also going to benefit them much.
|
|
|
Carmen Fisher
|
10/25/2017
|
|
|
Thanks, this was a very helpful article!
|
|
|
|
