MightyData

FileMaker and WordPress Consultants

Using SQL to Duplicate a Record Set

by Leave a Comment

Duplicating a single record in FileMaker is simple. However, what if you are faced with duplicating not just a record, but all related child records? I wanted to accomplish this without modifying the graph, and without looping through records to build my data sets.

For my customer’s development project, I had a table of tickets with associated time, material, and other related records where sometimes only minor changes to a big ticket were needed. Rather than create the related records from scratch each time, we needed a fast and simple method to duplicate key elements from each related table, and at the same time retaining the relation information from the newly created ticket or parent table.

I chose to use FileMaker Pro 12’s native SQL to build arrays with field/value pairs. Unlike a language like PHP, which has a built in logic for handling arrays, in FileMaker these have to be constructed using certain text-character separators, and therefore are not iron-clad arrays. However, once I constructed the, um, pseudo-arrays I could loop through the array values and set my new records.

Although it’s quite possible to set up an interface where you list all fields in related tables that need to be duplicated, I chose to set my field names within the SQL statement. Any changes to fields then need to be made inside the SQL statement, although with a little more programming my solution could conceivably adopt the field storage option. For the sake of this example, I elected to keep the syntax in the SQL statement fairly simple (no custom functions, although the GetFieldName function accounts for any Table Occurences name changes). If field names change or you want to add more fields, the SQL needs to be updated. Also, in regards to your own fields, you need to be aware of any reserved names which will cause the SQL to fail.

Download example file Tickets.fmp12.

The Related Table Query

To begin with, I set a variable with the original ticket number so I could message the user after the script finished. Then I used SQL to select the fields for each related table, and set my array. The example from the time table shows this format. I concatenated the field name and the value from the field. For number, date, and time fields I found that I needed to use the double pipe concatenation symbol instead of the plus sign; using the plus sign for these non-text field types caused some strange behaviors, usually stripping out the value.

In order to later parse out the values from my pseudo-array I added “^” as the field separator. I picked this character I didn’t anticipate it to exist in any field, unlike commas. You may notice the row separator uses a tilde or ~ symbol, instead of a pilcrow or return symbol which is the default separator. The reason for this is explained below in the script parameter section for the subscript the creates the new records.

Let([
query = "
SELECT
'" & GetFieldName ( TIME_ENTRY::NameFirst ) & "|'+namefirst,
'" & GetFieldName ( TIME_ENTRY::NameLast ) & "|'+namelast,
'" & GetFieldName ( TIME_ENTRY::TimeBegin ) & "|'||timebegin,
'" & GetFieldName ( TIME_ENTRY::TimeEnd ) & "|'||timeend
FROM time_entry
WHERE id_ticket = ?";
result = ExecuteSQL( query ; "^" ; "~" ; TICKET::ID )
];
result
)

With my result sets in hand, I duplicated the ticket with the “Duplicate Record/Request” script step. Since I used Get (UUID ) for my primary table ID I had to uncheck the “Do not replace existing value of field (if any)” check box, otherwise the UUID would also get duplicated.

Alternate Duplicate Record Option

If you do not use the “Duplicate Record/Request” you can perform the same action using New Record/Request. First, you would need another SQL statement, grabbing only select items from the record to be duplicated.

Let([
query = "
SELECT
'" & GetFieldName ( TICKET::TicketDescription ) & "|'+ticketdescription,
'" & GetFieldName ( TICKET::TicketName ) & "|'+ticketname,
'" & GetFieldName ( TICKET::ID_Customer ) & "|'+id_customer
FROM ticket
WHERE id = ?";

result = ExecuteSQL( query ; "¶" ; "" ; TICKET::ID )
];
result
)

In order to set the fields you loop through the SQL result, adding pipe separators between the fields (or another separator that later can be switched). With my field/value pair I could use the “Set Field by Name” script step.

Set Variable [$itemCount; Value: ValueCount( $ticketResult )]
Loop
  Exit Loop If [Let ( $i = $i + 1 ; If ( $i > $itemCount ; Let ( $i = "" ; True ) ) ) ]
  Set Variable [$row; Value: GetValue( $ticketResult ; $i )]
  Set Variable [$field; Value: GetValue( Substitute( $row ; "|" ; "¶" ) ; 1 ) ]
  Set Variable [$row; Value: GetValue( Substitute( $row ; "|" ; "¶" ) ; 2 )]
  Set Field By Name[ $field; $value]
End Loop
Commit Records/Requests []

After Parent Record duplicated

Next, I set variables with my new ID and ticket number. The new ID would be used in the subsequent related records. I called a subscript for each related table, passing in the ticket ID, the layout of the related table, and the SQL result for that related table’s values, which is spelled out in the example file. In the subscript I used a return delimited set of values for the script parameters. To address the point raised above with the tilde separator, if I had used the default return delimiter between rows in my ExecuteSQL my script parameter method would strip out everything but the first row or record. To prevent this I used the tilde, and then in the subscript added back the pilcrow/return character as the row separator. To validate the values in my Data Viewer as I tested the scripts I have this in two actions.

Set Variable [$SQL_Lines; Value: GetValue( Get( ScriptParameter ); 3)]
Set Variable [$SQL_Result; Value: Substitute( $SQL_Lines ; "~" ; "¶" )]

If you use a different method of passing multiple parameters, you just adapt the SQL and subscript to handle this. My goal with the example file is to keep it simple with no custom functions.

The subscript, “Duplicate_RelatedTable”, goes to the relevant layout, then uses a nested loop to create the record and set the values. Since the ticket’s time entry portal might contain multiple values, I needed to create a new record for each one, and set the relevant values from the previous parent record. Since I used the same Foreign key naming convention this let me use Set Field by Name for the Ticket ID in the related tables.

Go to Layout [$Layout]
Set Variable [$itemCount; Value: ValueCount( $SQL_Result )]
Loop
  Exit Loop If [Let ( $i = $i + 1 ; If ( $i > $itemCount ; Let ( $i = "" ; True ) ) ) ]
  Set Variable [$row; Value: GetValue( $ SQL_Result; $i )]
  New Record/Request
  Set Variable [$ForeignID; Get ( LayoutTableName ) & "::ID_Ticket" ]
  Set Field By Name[$ForeignID; $TicketID // from script parameter ]
  Set Variable[ $record; Value: Substitute( $row ; "^" ; "¶" ) ]
  Loop
    Exit Loop If [Let ( $j = $j + 1 ; If ( $j > ValueCount( $record ) ; Let ( $j = "" ; True ) ) )]
    Set Variable[ $data; GetValue( $record ; $j ) ]
    Set Variable [$field; Value: GetValue( Substitute( $data ; "|" ; "¶" ) ; 1 ) ]
    Set Variable [$row; Value: GetValue( Substitute( $data ; "|" ; "¶" ) ; 2 )]
    Set Field By Name[$field; $value]
  End Loop
Commit Records/Requests []
End Loop

The counter is handled within the Exit Loop condition. As there are multiple loops I wanted to make sure I cleared out all the values of the counter variable after each section within the loop completed.

With all this in place, the user only needs to click on the “duplicate record” button, and the script runs. Once done they see a message as to which ticket number was duplicated and the new ticket number. Success!

Virtual List in 3 Easy Steps

by 19 Comments

The Virtual List technique has been around for a few years (created by Bruce Robertson ca. 2009). Over time and with changes to FileMaker, implementation of this technique has evolved. Knowing how to set up and use virtual lists will help with creating certain reports, especially if you draw the values from ExecuteSQL statements in FileMaker Pro 12.

Articles (see Lisette Wilson’s Taming the Virtual List, Part I and Part II) and examples are fairly easy to find. So why write another one? Many examples and articles include use-specific cases to illustrate the virtual list. I’m going to attempt to boil this down to three simple steps. Once you draft the basics of this process into a FileMaker file, you can use this as a template for more complex reports. You can re-use the draft as a template, copying the table and script from one solution to another. This technique requires one dedicated reporting table, plus a script to populate some global variables. The fields in the reporting table are set up as calculation fields that get their values from the list within the global variable.

To illustrate the process, I added a table to the Contacts Start Solution as a sample database, which you can download here.

Step 1: The Reporting Table

When you visualize the report itself, it usually consists of rows and columns. Each row is a record in the table, and each column corresponds to a field. At minimum, you need two fields. One field is a hidden id field; it’s a crucial component of the report, but is not displayed. The second field appears on the report, and will contain data generated by the script. This simple two column example below could have been created as a SubSummary report. However, the power of the virtual list is that it allows you to combine data from many tables, something not easily within reach by a SubSummary report. In my demo file I have three fields, one for the id and two for the actual column data (state and count).

Data from contacts table pulled into the virtual reporting table

Step 2: The Fields

Of the two fields mentioned in the table, the id field governs what shows up in the data field or column. This field is set up as a number, starting at one, with each subsequent record incrementing by one. Most reports will have less than 100 rows, so a general rule of thumb is to just create 100 records (however, if your report demands more rows, just create more records). If you create a table view with the report layout, the id field should show 100 rows, with values from 1 through 100.

The second field is the first column of the report. Reports can have multiple columns, so new fields are created as needed, using the same principle. To keep it generic, in my report template I simply named the fields “column1” and “column2” and set them up as text fields. If you need to format data as numeric values, i.e. $43,000.00, this can be done with text formatting functions. The column field is set up as an unstored calculation: GetValue ( $$COLUMN1 ; id ). This will make more sense after Step 3, the script that creates the $$COLUMN1 global variable. Before the $$COLUMN1 variable exists, the “column1” field has no value. Once the variable is created, the field calculates from the content of $$COLUMN1, which is a list, and extracts the value that corresponds to the id number for that row. This is the magical part of the virtual list technique.

Assume that $$COLUMN1 contains these values:

  • Banana
  • Apple
  • Orange
  • Pear
  • Grape

This list has five values. For the first five records, as indicated by the values 1 through 5 in the id field, the GetValue() function will pull the corresponding fruit into the “column1” field.

Step 3: Filling In The Rows

This is the most flexible part of the process. It can range from complex loops within loops to a simple progression of steps to fill each row and column. First, to test if the reporting table works, you can create a simple looping script:

Set Variable[ $Limit ; Value: 100 ]
Loop
Exit Loop If [ Let( $i = $i + 1 ; $i < $Limit ) ]
Set Variable[ $$COLUMN1 ; Value: If( isempty($$COLUMN1) ; $i ; $$COLUMN1 & ¶ & $i ) ]
End Loop
Refresh Window [Flush cached join results]

This loop will fill in the first column values with numbers, by virtue of the GetValue function.

To fill in your report with real values, change the “Set Variable” line to something real. In the ContactsVL database, I wanted a count of people living in each state. I created a variable with all the states, and looped through this list. I then ran an SQL query inside the loop for each state.

#Column1
Set Variable [$$COLUMN1 ; Value: If( IsEmpty( $$COLUMN1 ) ; $State ; $$COLUMN1 & ¶ & $State ) ]
#Column2
Set Variable [ $Result ; Value: ExecuteSQL( “select count(*) from contacts where \”Work State\” = ‘” & $State & “‘” ); “” ; “” ) ]
Set Variable [ $$COLUMN2 ; Value: If( IsEmpty( $$COLUMN1 ) ; $Result ; $$COLUMN1 & ¶ & $Result ) ]
#End

In another example, rather than a list you can loop through, you have an individual requirement for the report, with text for report sub-heads. Here it’s just a matter of stepping through each row and setting the variable $$COLUMNx with the result. By adding values to a growing list, you then can parse this list into the rows of the reporting table. And for the subheads you can use text formatting functions to add bold and increase the font size to make these stand out.

Conclusion

Once you have set up a reporting table as in steps one and two, this technique can be used for many different kinds of reports. With a simple starter reporting table and a script or two ready in a template, you can quickly expand this concept to specific solutions.

SQL Reserved Words in FileMaker

by Leave a Comment

Unless you are a fan of Steve Ditko’s the Question, debugging FileMaker 12’s ExecuteSQL function is an art in itself. Even using a Custom Function to display the exact error message in the Data Viewer, as with Andries Heylen’s debug function, you may not always get the full story. As MightyData’s Darren Burgess wrote about SQL debugging nightmares, there are many pitfalls for FileMaker developers exploring the new world of SQL from within the calculation dialog.

The first key to SQL peace of mind is a valid SQL statement. Reading Beverly Voth’s excellent “Missing Manual” on the ExecuteSQL function, or even FileMaker’s help page, provides tips and references for checking your SQL statement. However, even a simple “select * from tablename” can result in a question mark if the tablename is a reserved word. As the FileMaker ODBC Guide indicates, there are quite a few reserved words, and these need to be escaped in your query.

Memorizing these reserved words is no easy task. After running into this issue once, I built a small FileMaker solution that’s fairly easy to integrate into a database file, so I can look for any reserved words in tables and fields. This might be the geek equivalent of stupid pet tricks, though it also has the added feature of exposing how to query FileMaker’s internal tables and fields with SQL.

Checking for reserved words with this method requires just one completely portable script. Using the built-in “FileMaker_Tables” and “FileMaker_Fields” meta tables, the queries pull a list of table occurences and the check these against the list from the ODBC Guide.

"Select TableName from FileMaker_Tables"

Checking whether each table exists in the reserved word list is carried out by a PatternCount function. While I initially had this as a Custom Function, I brought it into the single script to make it more portable.

Once the tables are checked the script moves on to the fields, where it checks these in the base table names. First we get a distinct set of the base tables, and then using a sub-select method inside a loop, go through each table and list the fields. Then each field is checked to see if it’s in the reserved word list.

"Select Distinct BaseTableName from FileMaker_Tables"

"select count(FieldName) from FileMaker_Fields 
where TableName = ( SELECT Distinct BaseTableName 
FROM FileMaker_Tables WHERE BaseTableName = '" & $baseTableName & "')"

I added a visual progress bar from Tim Cimbura, as a large table or database can take a while to check. The results appear in a simple web viewer. As the solution is unlocked you can change this to a table using the virtual list technique, or any other visual method you prefer. To render the progress bar and results just copy the two web viewers to a layout of your choice, along with the button to trigger the script.

I discovered the issue when I had a table called “transaction” which is a reserved word. There are a few words that we might use in tables or fields without thinking they’ll affect development, but if you start using SQL, checking for any reserved words might help at least eliminate this issue from any queries. The sample file has a couple of deliberate reserved words in tables and fields, and one table without any fields.

SQL_Reserved.fmp12

 

Tips for Debugging FileMaker SQL Queries

by 3 Comments

Our loyal readers will know that we have published several articles extolling the virtues of the new ExecuteSQL function in FileMaker 12. Well, now it’s time to come clean and tell you the truth about this powerful function and its hidden ability to turn your hair prematurely grey, cause you to lose sleep, or perhaps develop an annoying and embarrassing facial tick.

If you intend to embark on the rewarding path of integrating SQL into your FileMaker development, be warned. And be prepared, as one simple punctuation character, innocent in all other contexts among our many human languages, may lead to premature aging.

? Marks The Spot

This character is the lowly question mark (?), and is the only feedback provided by the FileMaker calculation engine when you write malformed SQL statements within the ExecuteSQL function.  SQL queries must be syntactically perfect in order for them to work. When you start writing SQL in FileMaker, you are stepping out of the safety of FileMaker’s tightly controlled programming environment that, for example, does not allow you to specify fields, scripts or other objects that don’t exist in the system.

Here is a short list of syntax errors that will cause your SQL query to return a question mark and for you to lose a few minutes off of your projected lifespan:

  • Using one of the 250-odd SQL reserved words as a table or field name
  • Using unacceptable characters (like an underscore ‘_’) as the first character of a field name
  • Using SQL syntax that is not accepted by the FileMaker SQL API (there are many versions of SQL)
  • Improper quoting of elements in the query
  • Improper expression of date syntax

Fortunately for you, I have sacrificed some of my naturally colored hair and a few precious minutes off of my lifespan to bring you some useful tips for surviving the FileMaker/SQL learning curve. Most notably, I want to explore how to use the script debugger and data viewer to assist in the debugging of your SQL queries.

SQL for Beginners

First off, this is not going to be an extensive tutorial in using SQL with FileMaker.  If you are just getting started, I would recommend the following resources:

How to Debug SQL in FileMaker

Efficient debugging of SQL queries is best accomplished by building the query within a Let function. Here is an example calculation whose job is to determine if the record about to be created in the script “Create Conversion” is the first record of a certain type to be created after a particular date in the Person’s record called the InquiryResetDate:
Let ( 
// variables
[
  type = Q( $type ) ;
  resetDate = Q( PeopleLayout » People::inquiryResetDate ) ;
  pID = Q( PeopleLayout » People::id ) ;
  SQL = "
    SELECT COUNT ( id )
    FROM Conversions
    WHERE type = " & type & "
    AND id_Person = " & pID & "
    AND creationDate >= " & resetDate ] ;

// calculation 
Case ( ExecuteSQL ( SQL ; "" ; "" ) ≥ 1 ; 0 ; 1 )

//DEBUGGING
//type
//resetDate
//pID
//SQL
//ExecuteSQL ( SQL ; "" ; "" )

)   // end LET
In this case, the SQL query is relying on data values from various local script variables and fields, so we start our Let function by pushing these various values into Let variables. Any of these values can drive unexpected results in the SQL query if they are not properly set up by the script and calculation, so pushing the values into Let variables improves our debugging chances later.  (Note that Q() is a custom function courtesy of Kevin Frank that wraps a value in single quotes):
Let ( 
// variables
[
  type = Q( $type ) ;
  resetDate = Q( PeopleLayout » People::inquiryResetDate ) ;
  pID = Q( PeopleLayout » People::id ) ;
Next, the SQL query itself is set to a Let variable by combining SQL statements with other defined Let variables to construct an SQL query. Note that careful, readable formatting of the query allows for simplified debugging later:
SQL = "
  SELECT COUNT ( id )
  FROM Conversions
  WHERE type = " & type & "
  AND id_Person = " & pID & "
  AND creationDate >= " & resetDate
Finally, in the calculation section of the Let function, we return a calculation and include, commented out, each of the elements that make up the Let function:
// calculation 
Case ( ExecuteSQL ( SQL ; "" ; "" ) ≥ 1 ; 0 ; 1 )

//DEBUGGING
//type
//resetDate
//pID
//SQL
//ExecuteSQL ( SQL ; "" ; "" )

)   // end LET

Debugging in Action

And now the fun can begin!  If my ‘Create Conversion’ script is not working, and I suspect a faulty SQL query, I run the script with debugger on. When the debugger runs the step in question, the value for variable $isFirstAfterResetDate will return a “?”:

Bad SQL query in Data Viewer

I then open the script, grab the calculation from the step, and drop it into the data viewer. Since the script is currently running, I am assured that all of the conditions of layout context and field/variable values that may be used in the calculation are in force. I then use the Let function to return the values of the various elements in the calculation by uncommenting them in the data viewer.

Here is the original calculation, returning the result the script is expecting:

SQL calculation with valid syntax

We can return one of the Let variables to make sure a valid and properly quoted value exists for that variable:

Record ID with single quotes

And here we return the SQL query itself, to ensure that it is properly formed.  (Of course the usefulness of this assumes that I know what properly formed SQL statement looks like!):

Check SQL query syntax

Once you have started creating SQL queries in FileMaker that actually work, save them in a library.  I use Notational Velocity to maintain my library of valid, working SQL queries. Having this as a resource is extremely valuable when it comes time to create a new query of particular type or style that already exists. It is perhaps the most valuable method for debugging SQL – which is simply using proven calculations – and makes it much more likely that your SQL queries will not be buggy and lead to that dreaded, hair-greying, insomnia-producing and lifespan shortening punctuation mark. I wish you the best of luck in the hard work of learning SQL. Post a comment if you have any questions!

FileMaker SQL WAN Performance Trouble

by 10 Comments

One of the new all-the-rage features in FileMaker 12 is the new ExecuteSQL function. When I heard about this new function and its potential to massively simplify the graph, I decided I did not want to wait to convert my customer’s School Management Solution to FMP12 and elected to use the free BaseElements plugin to access the FileMaker internal SQL API in FileMaker 11.

Some months and many hours of  learning curve time later, I was able to successfully implement SQL queries in many areas of my customer’s solution. Since I am using a plugin in a FMP11 solution, I took advantage of the opportunity to use SQL Insert, Update, and Delete queries to create, modify and delete records without having to create any schema to support the actions. As a result, I created very few additional relationships and was able to write significantly shorter scripts as I implemented several new features for the customer.

After deploying the SQL-enabled solution, we encountered a nasty performance issue over the WAN. This solution has a person table with 20,000 records and a related Event table with 80,000 records. Every time the remote WAN user added a new event, FileMaker would freeze for as long as 5 minutes. This issue could not be replicated for LAN users.

Debugging revealed that after the event was added, the very next script step that performed a SQL Select on the Events table was the source of the long delay/freeze.

Here is the original offending SQL code, part of a script step whose intent was to capture the ID of an event for purposes of conditional formatting and data entry control.  This query was part of a more complex calculation, however for our purposes the simplified SQL statement is sufficient:

SELECT id
FROM Events
WHERE id_Person = $personID AND status = 'No' AND method <> 'NonEvent'

In examining this code, I concluded that it could be simplified by adding a field to the Events table, Events::eventIsValid. This is a stored autoenter Boolean calculation that resolved to ‘1’ when the status = ‘No’ and method ≠ ‘NonEvent’. I then added that query to the SQL statement, thereby reducing the query by one condition:

SELECT id
FROM Events
WHERE id_Person = $personID AND eventIsValid = 1

Unfortunately, this change did not significantly affect WAN performance, as there was still a minutes-long delay when this calculation was evaluated.  At this point, in the interest of getting this bug fix delivered to the client as quickly as possible, I elected to abandon SQL as a technique for this particular function in the database. To that end, I returned to a good, old-fashioned, multi-predicate FileMaker relationship that would serve the purpose of finding all of the related event records that were considered valid as per the business rules of the customer.

Lesson learned in this case is to consider all possible solutions when debugging and optimizing a solution. In this case, the new, fancy FileMaker feature would have required more time than was of value to the customer, and so I resorted to a more conventional approach rooted in the pure FileMaker techniques. Ultimately the priority was to serve the customer with a timely resolution of the bug – a simple case of the end result (delivering value to the customer) trumping a focus on the underlying technology.

Stay tuned for a followup to this blog post where I elaborate on the technical solution to the slow SQL query.

12 Days of FileMaker 12: New Calculation Functions

by Leave a Comment

In this session from the 12 Days of FileMaker 12, we explore over a dozen new functions in FileMaker 12. The new features include improved container fields, new window styles, and a new plug-in updating methodology. It doesn’t end there, as there is a pair of functions that deal with ID values, some FileMaker Go specific functions, and native access to FileMaker’s SQL engine with the ExecuteSQL function.

New Container Functions

FileMaker 12 calculation categories

Two new categories appear in the calculation dialog: Container Functions and Mobile Functions.

The former provides the ability to get the height and width of content in containers using GetHeight( ) and GetWidth( ) functions. It returns height and width in pixels for images. Other items in the container field, such as movies or files, return 0. In addition, we have the ability to generate a thumbnail of an image with the GetThumbnail( ) function.

Since FileMaker 12 now enables us to store containers in external folders, checking the validity of the stored data reveals whether this has been changed or tampered with outside of FileMaker. Using VerifyContainer( ) shows 0 or 1 depending on whether the files have changed or not.

New Mobile Functions

There are two specific mobile functions that deal with Location information: Location( ) and LocationValues( ). Previous versions of FileMaker in mobile devices were forced to get geolocation information using JavaScript. By making this native, you can get longitude and latitude from Location and store these in your database. LocationValues displays a return delimited list containing longitude, latitude, altitude, horizontal accuracy, vertical accuracy, and age in minutes.

Both functions accept two parameters, one required and one optional. The first is the accuracy of meters, and the second is the length in seconds allowed before timeout.

Updating Plug-ins

Another new feature in FileMaker Pro 12 radically changes the method to update plug-ins. Previous versions managed this through a daunting and often complex AutoUpdate process via FileMaker Server. There is now an Install Plug-in File script step, and a function to return a list of currently installed plug-ins: Get( InstalledFMPlugins ). This result includes the plugin name, version, and current state – Enabled, Disabled, or Ignored. The publisher must enter the version number in the resource file (Windows) or info.plist.file (Mac), so keep this in mind when setting up this process.

ID and Tab Panel Functions

The new pair of ID functions each return a long string of alphanumeric characters but are completely different in purpose. Get( UUID ) returns a universal unique identifier, a unique 16-byte string in the format 8-4-4-4-12, such as E10FD6CB-2CB3-471A-B4B5-3CC2509F49EE. This function lets users create unique keys and avoids key collision when merging records from multiple tables, for example.

Meanwhile, Get( PersistentID ) captures the unique identifier of the device accessing your solution, either desktop or mobile. Whereas you still can get the account name or username, the new string like AF376F280690001931BFED4E890FADDB shows the device ID.

Along with some new tab control script triggers, there are two new functions.

  • Get( TriggerCurrentTabPanel )
  • Get( TriggerTargetTabPanel )

When coupled with the script triggers, navigating from tab to tab will return values via these functions. The first shows the originating tab panel (number and object name) and the second the current tab panel (number and object name). The naming can be  confusing when going to a new tab, so “current” is the actual target. Still, with these values we have a way to track and control navigation between tab control panels.

Window-Style Functions

A new feature in FileMaker 12 is the ability to have two new window states. Previously new windows were document windows. If you wanted to prevent the users from closing them, you needed to set up a script, pause and then start the script again, or use script triggers.

Now you can set up a floating document window and a dialog window. So, to go along with this, we have a function that returns a number as to the state of the window, which we can use to determine action based on that window. Get( WindowStyle ) will return the following:

  • 0 – Window is a document window
  • 1 – Window is a floating document window
  • 2 – Window is a dialog window

Execute SQL Function

Finally, a function that deserves a webinar all on it’s own, and in fact FM Academy just had such a webinar: Greg Lane’s walk-through of the ExecuteSQL( ) function.

It is a function that previously required plugins. There are some limitations compared to the plug-in functionality because ExecuteSQL( ) only permits the use of the SELECT statement. It means no ability to update, insert, delete, drop, or replace via SQL. Having this as a new tool in FileMaker creates great opportunity. The function format is:

ExecuteSQL( sqlQuery ; fieldSeparator ; rowSeparator { ; argument … } )

The sqlQuery is the SELECT statement, placed inside double quotes, such as “select first_name from contacts”. The fieldSeparator and rowSeparator default to a comma and return when you use “” for the parameters. You can use other separators such as tab, pipe, etc.

The optional arguments parameter lets you use a record’s field value as a parameter, useful when querying the context of individual records. Learning this function will give you a great new tool in your FileMaker development.

FileMaker Calculations Training

12 Days of FileMaker 12: Execute SQL Function

by Leave a Comment

FileMaker 12’s new ExecuteSQL function opens up the power and flexibility of the SQL query language to any FileMaker calculation. In this session from the 12 Days of FileMaker 12, we explore techniques for using the ExecuteSQL function within calculations to gather and manipulate data for reports, charts and dashboards.

Using the ExecuteSQL Function

This video explores a variety of built-in and custom functions to slice and dice data in any context. We also cover the basics of the SQL SELECT statement so anyone can get started quickly.

About Greg Lane

Greg Lane is VP of Application Development at Skeleton Key, a FileMaker Business Alliance Platinum level member in St. Louis, Missouri. He is a FileMaker 7, 8, 9, 10, and 11 certified developer and has enjoyed solving problems with FileMaker for more than 20 years.

Greg has authored articles for FileMaker Advisor Magazine and has contributed to books, white papers, and other FileMaker-related publications. He is looking forward to speaking about FileMaker and SQL again at the 2012 FileMaker Developer Conference.

FileMaker 12 Adds Native SQL Queries

by 31 Comments

Probably one of the most talked about features in FileMaker Pro 12, at least from a developer perspective, is the new ExecuteSQL() calculation function. To users interacting with FileMaker records, this is largely a hidden feature. Yet this powerful new option impacts developers in several ways.

Previously, developers used plugins to expose the SQL underpinnings of FileMaker. Now, a portion of SQL has become native to FileMaker. This portion is limited to the “Select” statement. Those same plugins remain viable options for developers who seek to insert, update, or delete records.

The programming language SQL (Structured Query Language, see Wikipedia entry) centers on the query, or Select statement, to pull data from one or more tables. For developers new to SQL there are several books or internet resources available, yet the most concise and immediately useful tool is probably FileMaker’s own ODBC and JDBC guide.

The SELECT Statement

Starting with the most elemental of queries, and breaking down the Select query into multiple lines helps us visualize the different components of the query. We can then add more pieces as we master each section. We start with selecting a field from a table.

SELECT some_field_name
FROM some_table_name

It’s important to note that this statement selects the field name, not the value in the field. So if we said, “select Department from Contacts” we would get a list of all the departments in every record from the Contact table. You can also include mathematical operations or modify strings in the query. To count values you can use count(*) or count(field_name) for all records in the first instance, or only records that contain a value in the field_name.

The WHERE Clause

The Select statement works well in its basic fashion, but what we’re really after is the Where clause, combined with the ability to interact with multiple tables in one query. Rather than building complex relationships in the graph to accomplish the same result, we’re now able to solve this through SQL.

When FileMaker Pro 11 introduced portal filtering we were able to simplify our graph and remove several Table Occurrences, and now with ExecuteSQL the graph can focus more on pure relationships, as this function is context independent. We can query table occurrences from anywhere in FileMaker, without having to navigate to specific layouts or rely on additional table occurrence groups.

The next step along the SQL path is adding the Where clause and additional tables.

SELECT some_field_name
FROM some_table_name
WHERE a_condition_applies

The condition serves to limit the Select statement, as without it you would select all the records in the table. So, a simple Select statement with a Where clause would look like “select Department from Contacts where City = ‘Dallas'”.

ExecuteSQL Syntax

The way the ExecuteSQL function behaves plays a key role in writing SQL statements. According to the calculation dialog, the syntax is:

ExecuteSQL( sqlQuery ; fieldSeparator ; rowSeparator { ; arguments… } )

The sqlQuery is the query string – the select statement. Since this is text, we write this inside double quotes. The fieldSeparator and rowSeparator default to comma and return, so unless we need the result to appear in a different format we can simply use double quotes. The arguments is optional, but will be discussed further below. In this query we can count all the records in the state of Texas, using single quotes for the literal value of TX.

ExecuteSQL( "select count(*) from Contacts where State = 'TX'"; ""; "" )

Pages 55-57 of the ODBC guide list several reserved words. A few of these words might appear in your solutions as field names, such as “order” or “size”, or “First” and “Last” instead of FirstName or LastName (or NameFirst and NameLast). These words will need to be escaped in queries. In addition, field names with spaces also need to be escaped.

ExecuteSQL( "select \"Order\" from Orders where \"Product Name\" = 'FileMaker'" ;"";"")

Unlike MySQL, for example, the ExecuteSQL function will show just ? in the result if there is an error in your query, with no hint as to what caused the error.

Using the Arguments Parameter

The arguments option allows the use of multiple arguments in the sqlQuery string. These arguments use the question mark as a placeholder, and are then listed in order in the argument section.

ExecuteSQL( "
select count(*)
from Books
where zkf_authorID = ?
and  datePublished < ? ;
""; ""; Author::zkp_AuthorID ; Get( CurrentDate ) – 365)

Rather than using conditions outside the current record (i.e. State = ‘TX’), here we input values from a specific record, to see which books were published within a year for the author of the current book record.

The FROM Clause

The ability to consider multiple tables falls under the From clause, but also in this action we must point back to the fields in the Select clause. These fields can be prefixed with the table name or alias. Adding an alias is useful as it reduces clutter in the query. With a table called Customers we can change this to from Customers C, and prefix the field as C.NameFirst in the Select statement. To query across multiple tables we “join” these with an equal sign (=), which will include only matching rows in the results.

SELECT C.NameFirst, C.NameLast, O.DateOrdered
FROM Customers C, Orders O
WHERE C.custID = O.custIDf

Wrapping Up

The new ExecuteSQL function opens up a multitude of possibilities. In scripting you can build queries across tables, populating variables for use in virtual list techniques. In charting you can gather data for dashboards in the same way, without having to jump to various different layouts and loop through records to summarize them. For tooltips you can provide information specific to the current record that looks across related tables. All this can be accomplished without adding a single new table occurrence.

Further Explorations in SQL for FileMaker

by 1 Comment

Once you start down the path exploring new features or technologies, often the deeper you dig the more cool stuff you expose. In my first post on SQL and FileMaker I focused simply on pulling and comparing data between tables. There are several advantages to employing SQL in FileMaker: speed and context perhaps the primary ones. While SQL the graph remains clean, as the queries remain inside the script, no additional Table Occurences are required.

One area in FileMaker, where we tend to rely on jumping through hoops in the graph, deals with report creation. This is especially true when pulling together reports from multiple sources. Solutions require extra TOs to build queries through relationships, and significant scripting to hop between contexts and gather data.

A recent game changer is Bruce Robertson’s Virtual List (see Lisette Wilson’s recent article and her follow-up for an overview), which gathers data into a utility table for the report output. Combining SQL and the Virtual List seems like a perfect marriage. Martha Zink, Advocate of Awesomeness at MightyData, recently showed me an example of SQL combined with the Virtual List. This post borrows heavily from concepts in her solution.

SELECT From ( SELECT )

Again, for the SQL queries I used a plugin. Several plugins expose FileMaker’s internal SQL. I selected MMQuery from CNS Plug-ins. One nice feature of its Execute SQL function is the ability to set your own delimiters between fields returned in the result. The default delimiter is the comma, but when returning number you may end up with extra commas and I set my delimiter to the pipe symbol. Other plugins may not have this feature, and require you to substitute the comma for the pipe symbol before sending the data through the Virtual List process.

(I recently discovered another plugin, from the makers of Base Elements, that is not only free but lets you execute Shell and SQL, plus a host of other features.)

Although I’m not going to show long query examples in detail, I want to highlight one SQL idea used to build the queries that get sent to the Virtual List “engine”, and how to parse the output from SQL in order for the Virtual List engine to parse the data. The beauty of the SQL in Martha’s solution turns on an elegant and powerful technique in SQL: subselects.

In essence, this is a query nested within another query. The advantage lies in removing joins, which makes the query more readable. Subselects also allow you to build result sets against which you then can run other queries. For example, imagine if you want to pull a list of fields using an ID value or set of IDs. You first want to get that ID set, and then you can select information based on this list.

select <> from Table as T where T.ID in
( select O.ID from OtherTable as O where age < 45 )

To illustrate this process I added some formatting. The key lies in bracketing the select that creates the initial result, and then selecting from this above the result. This “inner” query inside the parentheses is processed first. With “in” the outer query looks at a list of records.

The inner select in the above example is a very simple statement. As with any other select in SQL you can change this to look across multiple tables, using multiple criteria, AND/OR, and other options. Using subselect is almost like creating virtual tables that exist solely within a query. Inside the <> would be all the fields required in the final result.

Dynamic Criteria by the User

In the solution that called for SQL, each report called for user-selected criteria, and searching across multiple tables. Relying solely on the graph or scripting to build the data would have required complex TOs and scripts jumping from layout to layout to build data, possibly even hard-coding criteria. By making the reporting dynamic, users could select from drop downs for different criteria, such as “single” and/or “double” graft. The query would build a pair of select statements, with the sub-select handling building a second list.

SELECT
DISTINCT P.id
FROM (( Patient as P
JOIN Admission as A ON P.id = A.idPatient )
JOIN Intervention as I ON A.id = I.idAdmission )
JOIN allvalue as AV on I.id = AV.idIntervention
WHERE lower( AV.Type ) = 'proximal'
AND lower( AV.Field1 ) = 'aortic arch'
AND lower( AV.Field2 ) = 'graft replacement'
AND lower( AV.Field4 ) LIKE '%single y-graft%'
AND I.Proximal_Date BETWEEN DATE '2002-01-01' AND DATE '2011-08-12'

OR P.id IN (
	SELECT DISTINCT P.id
	FROM (( Patient as P
	JOIN Admission as A on P.id = A.idPatient )
	JOIN Intervention as I ON A.id = I.idAdmission )
	JOIN allvalue as AV on I.id = AV.idIntervention
	WHERE lower( AV.Type ) = 'proximal'
	AND lower( AV.Field1 ) = 'aortic arch'
	AND lower( AV.Field2 ) = 'graft replacement'
	AND lower( AV.Field4 ) LIKE '%double y-graft%'
	AND I.Proximal_Date BETWEEN DATE '2002-01-01' AND DATE '2011-08-12'
	)

While both queries end up fairly complex, imagine the complexity if she had to rely on joins to perform both queries. All of this takes place inside FileMaker, using (in her case) the SyncDek plugin for the SQL. Combine the power of SQL with the Rapid Application Development (cool interfaces) of FileMaker, and you get true development synergy.

Select * From FileMaker

by 11 Comments

FileMaker Pro succeeds as a database system because of its many layers. As a rapid development database you can create a robust and elegant solution fairly quickly. Dig deeper, invest some time and effort, and you expose powerful tools and options that extend your solution. One such hidden tool is FileMaker’s internal SQL engine, which intrepid developers expose via plugins.

You might ask why bother, since SQL is a complex language with specific syntax we must learn outside FileMaker. Also, almost everything that SQL does within FileMaker can be natively accomplished without adding plugins. However, I believe there are times when the power of SQL combines nicely with FileMaker to produce better, smarter solutions, and this makes learning and using SQL worthwhile.

Some of the reasons to look at SQL in FileMaker include context-free queries, a simplified the relationship graph (fewer TOs in the graph), avoiding complex finds in scripts, and flexibility in building reports. In FileMaker, context is king. When creating or finding records through scripts, for instance, failing to start on the right layout could lead to drastic consequences. SQL removes these context constraints.

Several plugins enable access to FileMaker Pro 11’s internal SQL via functions. These include:

For my testing I used MMQuery, and primarily its ExecuteSQL( query ) function.

My task required comparing two tables with ca. 1 million records each and finding the records from Table A that were missing in Table B, and then make some adjustments to these records. Although I probably could have accomplished this in FileMaker, I like the elegance of SQL as it allows you to select data from various tables, and return only the results that you need. Never before having tried SQL in FileMaker, I started small, testing a basic query to discover constraints or differences in the SQL language from my experience with MySQL.

While testing the SQL queries I discovered that the tablename in each SQL statement refers to FileMaker’s table occurrence name, not the base table name. In my project database, one of the tables I queried lacked a corresponding table occurrence with the same name. The first query resulted in an error that the table did not exist, and when I switched to a table occurrence name, the query worked. Table names are not case-sensitive, so if you have a “Contacts” table occurrence name, you can use “select * from contacts” and the query works.

The Select statement returns single or multiple columns (fields) from single or multiple tables. The syntax follows a certain sequence. FileMaker, Inc’s ODBC/JDBC Developer Guide shows some SQL examples. The initially daunting syntax can be broken down into just the pieces you need from the options:

Syntax for SELECT clause in FileMaker

All text inside the square brackets are optional. I created a script to set two variables – a $query with “select * from contacts” and a $result with ExecuteSQL( $query ) so I could check these in the Script Debugger and use $result in subsequent steps in my script. The “*” in SQL is the same as “all” and gathers all the fields from all the records into the result. To narrow down the query you need to specify the field, or in SQL terms, the column. Querying across a single table requires just the column name, such as “select nameFirst from contacts”, but when querying multiple tables you need to include the tablename or table alias as a prefix to the column name, such as “select contacts.nameFirst from etc.”.

Now, returning to the problem at hand: how to get values from Table A that are not in Table B? Imagine two tables: author and book. In the author table we have an author ID field and the author name. In the book table we have a book ID field, an author ID field (foreign key), and the book title. I need to find authors with no titles in the book table. Starting with a simple join, I can see all the records that match (for these examples I have capitalized SQL commands, and added returns for the sake of clarity):

SELECT *
FROM author, book
WHERE author.id = book.author_id

But this query returns authors and titles that are in both tables, and I need the reverse. Using a LEFT JOIN produces the records that matches and those that do not match.

SELECT * FROM author
LEFT JOIN book
ON author.id = book.author_id

Since this returns all the data from both tables, I need to isolate the records that do not match. In SQL terms these would be any records with NULL values in the related field, ie. the book.author_id. By adding a WHERE clause to limit the found set, the result is what I need.

SELECT author.* FROM author
LEFT JOIN book
ON author.id = book.author_id
WHERE book.author_id IS NULL

Using “author.*” instead of “*” also limits my result set to just the records from the author table. No joins are required in the relationships graph, and my script can execute the query from any context. The excitement of seeing values populated into $result from the SQL query–even the most basic of queries–told me that using SQL as part of FileMaker opens up new vistas in developing solutions. In another post I’ll cover another powerful feature of SQL called sub-selects, and how this can simplify complex reports.

Let’s get started on your project.

It will be more fun than you think.

Get in Touch