MBS FileMaker Advent calendar

[MonkeybreadSoftware]

Today we start with the first door of this year's MBS Advent calendar. This year, we have come up with a themed Advent calendar for you. Over the next 24 doors, you will learn how to work with SQL within a FileMaker database using MBS functions.

So let's start today with the question: What is SQL in general, and what can I use it for?

SQL stands for Structured Query Language and refers to a language that can be used to query, modify, create, and delete data in a relational database. SQL can be used to work with a wide variety of databases, including MySQL, MariaDB, and PostgreSQL. SQL can also be used in FileMaker. On the one hand, it allows you to access other databases (e.g., with the Execute SQL script step or with the SQL component in MBS), thus providing a connection between FileMaker and the external database and making communication at all possible. But you can also use SQL internally in FileMaker, for example, to make queries to the database. To do this, you can use either FileMaker's own ExecuteSQL function or the MBS functions from the FMSQL component. We would like to focus on this part in this Advent calendar.

Even though FileMaker already offers many built-in options for searching, evaluating, and linking data, SQL can be an extremely flexible addition to a FileMaker database. SQL allows you to access data in FileMaker directly and independently of the layout. This means that you can retrieve information spread across multiple tables without having to create additional relationships in the relationship graph or layouts. You can define complex queries that combine, filter, or group data from different tables, for example. For example, you can use a single SQL query to determine how many orders a customer has placed in the current year—something that would often only be possible in FileMaker itself with multiple scripts, loops, or auxiliary relationships.

When working with FileMaker's own function, we can send SELECT queries to the database, which we will examine in more detail in the next section. SELECT queries allow you to retrieve information, similar to a search query. However, instead of a selection of records, you receive text that separates the individual pieces of information with predefined delimiters. The MBS plugin has a total of 65 functions in the FMSQL component to provide you with additional support when working with SQL, so that you can, for example, create, update, or delete records and handle errors even easily. In the next 23 days, I would like to dive into this topic with you so that you can then take full advantage of all the possibilities SQL has to offer.

[MonkeybreadSoftware]

Before we take a look at what MBS can do with SQL, let's take a look at FileMaker's own functions and get started with the SQL language. In today's example, we are working with a table listing Christmas movies. It is called Movie and has the following fields: Name, Director, Year and Generated_Sales. The first queries will be run on this table in a moment.

Important terms in the SQL language are SELECT FROM and WHERE. These are based on the English language, making it relatively easy to understand what a statement does. If we want to get the data from a specific field, we can write SELECT followed by the name of the field from which we want the data. SQL is layout-independent and does not automatically know from which table we want the data; we have to specify this in the FROM part. So we write FROM and then the table name. If we want to use SQL to query all movie names that are available in the Movie table, the query looks like this:

SELECT Name FROM Movie

We can use this query right away in the ExecuteSQL function in FileMaker. We should not mix up this function with the Execute SQL script step, because that one isn't meant for working on our FileMaker database, but for accessing an external database. Let's now turn to the ExecuteSQL function and take a look at its structure.

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

We see several parameters that need to be filled in. First, we have the parameter for the SQL statement, where we can enter the SQL expression we have already determined. Then we have the field separators. We can not only retrieve data from one field, as in this example, but also from several fields. These fields must then be separated from each other in the result text. We can choose this separator here; in our example, we use a space. However, there is not only a field separator, but also a separator between the individual data records. In our example, we choose a line break to separate the data records from each other. Optionally, you can also work with arguments in the function. We will come to these later.

If we now insert the SQL query and other parameters into the function, it looks like this:

Set Variable [ $Text ; Value: ExecuteSQL ( "SELECT Name FROM Movie" ; " " ; "¶" ) ]

The result we get is text that we can then reuse, e.g., as shown here in a dialog box for output.

We have already mentioned that we can return not just one field in the result, but several. If you do not want to select specific fields, you can also return all fields. To do this, you do not need to write all field names one after the other; instead, you can place an * after SELECT.

Set Variable [ $Text ; Value: ExecuteSQL ( "SELECT * FROM Movie" ; ", " ; "¶" ) ]

This time, we have used a comma as the field separator.

As you can see, the output also displays fields that are not on the layout. This behavior once again highlights the independence from the layout.

If we want data from multiple fields, we must separate them with a comma in the SELECT part. In this example, we have the name of the movie and the director. This time, the fields are separated by a hyphen.

Set Variable [ $Text ; Value: ExecuteSQL( "SELECT Name, Director FROM Movie" ; "-" ; "¶" ) ]

Let's try this with the name and year:

Set Variable [ $Text ; Value: ExecuteSQL( "SELECT Name, Year FROM Movie" ; "-" ; "¶" ) ]

Now it gets strange, because we get a question mark back instead of the movies with the corresponding years. The question mark is displayed when the function has not produced a reasonable result. There was an error. However, we cannot determine where the error occurred with this function. For this, we need the ExecuteSQLe function, which returns an error text in addition to the question mark in case of errors. The parameters of the function are the same.

Set Variable [ $Text ; Value: ExecuteSQLe( "SELECT Name, Year FROM Movie" ; "-" ; "¶" ) ]

Based on the error message, we can now see that the problem lies with the field name Year, as this is the same name as a FileMaker function and this causes a conflict. We can easily solve the problem by changing the field name and repeating the query with the changed field name.

In SQL, we not only want to retrieve data on all records for different fields, but as we already indicated yesterday, data can also be filtered, and we can use this to formulate searches in our database in SQL style. An important key to this is the WHERE clause. Here, we can specify what our data must have in common in order for it to be included in the result set. For example, we can search for all films made by a specific director. To do this, we first specify the fields we want to display in the SELECT part, as we did before. In the FROM part, we then specify the table name from which the data comes. Now comes the new part, the WHERE part. Here, we query the director named Chris Columbus. Since this is text, we enclose the name in single quotation marks. The SQL statement then looks like this:

SELECT Name, Launch FROM Movie WHERE Director = 'Chris Columbus'

However, we can not only search for equal values, but also for values that are smaller or larger. For example, we can list all films made after 2015 by placing Launch>2015 in the WHERE section. Now, it may happen that we get a question mark as a return value in the output. Why is that? If we have defined our Launch field as text, the text cannot be compared with greater than or less than. For this reason, we must ensure that the field has the correct data type. Then our query will work.

As in the IF statements in FileMaker, we can also specify conditions with AND, OR, and NOT here. This allows us to link multiple conditions for our data and define more specific search queries. In the next query, we want all movies directed by Chris Columbus, plus movies released in 2000 that were not directed by Greg Beeman.

Such a query would look like this:

Set Variable [ $Text ; Value: ExecuteSQL( „SELECT Name, Launch FROM Movie WHERE Director = 'Chris Columbus' OR Launch=2000 AND NOT Director='Greg Beeman'" ; "-" ; "¶" ) ]

We already mentioned that FileMaker has arguments. These arguments are a bit like placeholders for values that we want to use in our SQL statement. This allows us to specify the name of the director as an argument. In the query text, we then insert a question mark instead of the value. It might look like this:

Set Variable [ $Text ; Value: ExecuteSQL( "SELECT Name, Launch FROM Movie WHERE Director = ?" ; "-" ; "¶";"Chris Columbus" ) ]

The output is exactly the same. But on the one hand, we have made the query more maintainable and can save ourselves the typing for concatenating values if the specified value is a variable. But probably the most important reason for using these arguments is that we have more security because we can avoid SQL injection. Let's assume we want to give the user free input and define our query as follows:

Set Variable [ $Text ; Value: ExecuteSQL( "SELECT Name, Launch FROM Movie WHERE Director ='" & $Test&"'" ; "-" ; "¶" ) ]

The variable Test contains what we got back from the user input. Ideally, this is the name of a director. But and this is the crux of the matter — theoretically, the user could also enter SQL commands and thus retrieve information that they should not have access to. For example, if the user enters the following:
Chris Columbus' OR NOT Director='Chris Columbus

Set Variable [ $Test ; Value: "Chris Columbus' OR NOT Director='Chris Columbus" ] Set Variable [ $Text ; Value: ExecuteSQL( "SELECT Name, Launch FROM Movie WHERE Director ='" & $Test & "'"; "-" ; "¶" ) ]

Then this is an input that is accepted, since we have also set the appropriate quotation marks, and all data is then displayed because the query is correct for all data records.

Of course, we want to avoid this, as it could cause much more potential damage. And for this purpose, we have our arguments that make the whole thing a prepared statement, which we read as formatted text and not as an instruction.

Set Variable [ $Text ; Value: ExecuteSQL( "SELECT Name, Launch FROM Movie WHERE Director = ?" ; "-" ; "¶";"$Test" ) ]

That brings us to the end of today's door, and I look forward to welcoming you back tomorrow.

[MonkeybreadSoftware]

Today is the third day. Yesterday, we learned what we can do with FileMaker's internal functions. Today, we want to look at an MBS function that is very similar in structure: the FM.ExecuteFileSQLfunction.

Let's take a look at our query for all movies directed by Chris Columbus, plus movies released in 2000 that were not directed by Greg Beeman. We remember that this was the query:

SELECT Name, Launch 
FROM Movie 
WHERE Director = 'Chris Columbus' OR Launch=2000 AND NOT Director='Greg Beeman'

We now want to execute this query in the MBS function FM.ExecuteFileSQL.

This function requires various parameters. First, we enter the file name, which this function should access. In our case, this is the same file in which we are executing the script, so we can specify Get(FileName) here. Then comes the SQL statement. These are the parameters that must always be specified for the MBS function.

Set Variable [ $r ; Value: MBS( "FM.ExecuteFileSQL"; Get(FileName); 
   "SELECT Name, Launch FROM Movie WHERE 
   Director = 'Chris Columbus' OR Launch=2000 AND NOT Director='Greg Beeman'" ) ]

Optionally, we can also specify the field separators and line separators in this function. Prepared statements are also possible here, and at the end of the parameter chain, you specify the values to be inserted into the query instead of the question marks, separated by semicolons.

Set Variable [ $r ; Value: MBS( "FM.ExecuteFileSQL"; Get(FileName); 
   "SELECT Name, Launch FROM Movie WHERE 
   Director=? OR Launch=? AND NOT Director=?";"-" ;  "¶"; "Chris Columbus"; 2000; "Greg Beeman" ) ]

But if the function does the same thing as the FileMaker function, why do I need it at all and not just use the existing one? If you only want to run a few SELECT FROM WHERE SQL queries, the FileMaker function is perfectly sufficient. However, the MBS function can not only query values from the database, but also use SQL expressions that change the data structure.

For example, we can insert new records into the database, update them or delete a specific record. We will learn about this and much more in the coming days.

It is also possible to delete an entire table, meaning not just all entries but the entire structure. To do this, use

DROP TABLE TableName

Always exercise extreme caution when performing deletion operations before dismantling your database. With DROP Table, only the table is deleted, not the associated layouts.

I hope to see you again tomorrow to continue exploring the possibilities of SQL.

[MonkeybreadSoftware]

Yesterday, we learned about an SQL function from MBS. Just like FileMaker's own function, it returned text that we could then work with. This time, we want to take a look at the FM.SQL.Executefunction. Instead of returning text output, this function creates a reference. A reference is a reference to the working memory in which the result of the function is temporarily stored. This allows you to use the result returned by the SQL query with other cool MBS functions, which you will learn about later in this calendar. The advantage of having a reference instead of text is that the text does not have to be reloaded into the working memory every time we want to work with the result. The result with the reference is simply stored in the working memory, which saves us a lot of time when dealing with large amounts of data. This also gives us the option of running through the result (in another door) or selecting a different output type. We will output the result as text as well as a matrix. The FM.SQL.Text function is available for output as text. Let's take a look at such a query on our database. Here, we want to search for all movies made before the year 2000. In the FM.SQL.Execute function, we first specify the database on which we want to perform our operations.If we leave this parameter blank, we do not limit the query to one database. Instead, we keep the option open for multiple databases. In our case, it is clear which one we want, and we specify it. Then the SQL statement follows. The result of this function, our reference, is then stored in our variable $SQLRes. We then use this reference as the first parameter in our next function, FM.SQL.Text. Then we can determine the range of data to be displayed. We have parameters for the first and last row and the first and last column. For now it is sufficient to leave the parameters empty, because then the entire result set will be displayed. Two further parameters then stand for the row separator and field separator.

Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT Name, Launch FROM Movie WHERE Launch<2000" ) ]
Set Variable [ $r ; Value: MBS("FM.SQL.Text"; $SQLRes;"";"";"";"";"¶¶";"| - |") ]

The result looks like this:

There is also a special feature to note for line and field separators. If you want to have line and field separators that are longer than one character, you cannot use the FM.ExecuteFileSQL function from yesterday's door, because only one character is allowed here. If you enter multiple characters, only the first character will be used and the rest will be ignored.

Set Variable [ $r ; Value: MBS( "FM.ExecuteFileSQL"; Get(FileName); 
   "SELECT Name, Launch FROM Movie WHERE Launch<2000"; "| - /";"¶¶" ) ]

If you need multiple characters, you should also select the FM.SQL.Execute function in combination with the FM.SQL.Text function.

As mentioned above, the result can be returned not only as text, but also as a matrix. This matrix can then be used with the matrix functions of the MBS FileMaker Plugin. With the FM.SQL.ToMatrixfunction, we convert an SQL reference into a matrix reference. This allows us to first determine the result as a matrix reference and then return an HTML to the result, which we can then display in a web viewer.

Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT Name, Launch FROM Movie WHERE Launch<2000" ) ]
Set Variable [ $Matrix ; Value: MBS("FM.SQL.ToMatrix"; $SQLRes) ]
Set Variable [ $HTML ; Value: MBS("Matrix.HTML"; $Matrix ) ]
Go to Layout [ “WebViewer” (Christmas2025 Copy) ; Animation: None ]
Set Web Viewer [ Object Name: "web" ; URL: "data:text/html;charset=utf-8," & $html ]

There's one thing we shouldn't forget when working with references. When we don't need the reference anymore, we have to release it so we don't overfill our memory. In the SQL area, we have two suitable functions for this: FM.SQL.Release and FM.SQL.ReleaseAll. With the FM.SQL.Release function, we can release a specific reference, which we then specify as a parameter. FM.SQL.ReleaseAllreleases all SQL references in memory at the same time. Since you also have to work with references in the matrix, you must ensure that the references are released here as well.

Set Variable [ $r ; Value: MBS("Matrix.ReleaseAll") ]
Set Variable [ $r ; Value: MBS("FM.SQL.Release"; $SQLRes) ]

That brings us to the end for today. See you tomorrow for the fifth door.

[MonkeybreadSoftware]

Yesterday, we looked at the FM.SQL.Execute and FM.SQL.Text functions. In the parameters of the FM.SQL.Text function, we saw that we can limit our result set during output. But how large is our result set actually?

Let's find out together today. We have two functions that are essential for this: FM.SQL.RowCount and FM.SQL.FieldCount. With these two functions, we can count the rows or columns of our result.

Let's try it out right away. We have again used the query SELECT Name, Launch FROM Movie WHERE Launch<2000. We know that our result contains two fields, namely Name and Launch, and that the result contains a total of 11 movies.

This is what our script looks like:

Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT Name, Launch FROM Movie WHERE Launch<2000" ) ]
Set Variable [ $CountRow ; Value: MBS("FM.SQL.RowCount"; $SQLRes) ]
Set Variable [ $CountField ; Value: MBS("FM.SQL.FieldCount"; $SQLRes) ]
Show Custom Dialog [ "Count Rows and Fields" ; 
   "Row Count: " & $CountRow & 
   "¶Field Count: " & $CountField ]
Set Variable [ $r ; Value: MBS("FM.SQL.ReleaseAll") ]

First, we use our query again, just as before. Instead of outputting the result as text this time, we use the FM.SQL.RowCount and FM.SQL.FieldCount functions to determine the corresponding values. In both functions, the reference received from the FM.SQL.Execute function is used in the parameters. In the dialog, we then assemble our desired result accordingly. Since we are working with references here, we must of course release them again at the end.

When we run this script, our result looks like this:

So we get what we expected. Now we can use this knowledge in the FM.SQL.Text function, for example. Here we can limit our output to a specific range. We left these parameters blank in the last door, but now we want to fill them.

Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT Name, Launch FROM Movie WHERE Launch<2000" ) ]
Set Variable [ $CountRow ; Value: MBS("FM.SQL.RowCount"; $SQLRes) ]
Set Variable [ $CountField ; Value: MBS("FM.SQL.FieldCount"; $SQLRes) ]
Set Variable [ $r ; Value: MBS("FM.SQL.Text"; 
   $SQLRes;0;$CountRow-1;0;$CountField-1;"¶¶";"| - |") ]
Show Custom Dialog [ "SQL Result" ; $r ]
Set Variable [ $r ; Value: MBS("FM.SQL.ReleaseAll") ]

If we select these settings, we get the entire range specified because we display all rows from index range 0 to Row Count-1 and also the fields from index 0 to Field Count-1. If we want, we can also set the ranges differently. For example, if we want to output all data from the second row onwards and we don't want the last row, we can set the index to 1 for the rows to start at the second row and Row Count-2 to exclude the last row.

If you have already worked with SQL, you may know that the word COUNT is also a keyword in SQL, because here too we can determine how many rows the result set has. We set the COUNT in the SELECT part. The field names are replaced by an * in brackets. In this case, we do not need to select specific fields, because this example is only about the number.

Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT COUNT (*) FROM Movie WHERE Launch<2000" ) ]

This statement also returns 11.

But what is the difference, you may ask. This becomes clear when we apply the FM.SQL.RowCount and FM.SQL.FieldCount functions to this statement:

Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT COUNT (*) FROM Movie WHERE Launch<2000" ) ]
Set Variable [ $CountRow ; Value: MBS("FM.SQL.RowCount"; $SQLRes) ]
Set Variable [ $CountField ; Value: MBS("FM.SQL.FieldCount"; $SQLRes) ]
Show Custom Dialog [ "Count Rows and Fields" ; 
   "Row Count: " & $CountRow & 
   "¶Field Count: " & $CountField ]

Contrary to what you might expect, our result now only has one row and one column. This contains the number 11. This means that the SQL keyword COUNT works on the database and FM.SQL.RowCount and FM.SQL.FieldCount work on the result set.

Now I would like to show you a special use of Count. By adding another keyword, you can now find out the number of different years our films can have. This means that if two films were made in 1992, 1992 is only counted once. The keyword we need for this is DISTINCT. We put this in the brackets and specify the field from which we want to determine the number of different values. In our case, this is the Launch field. The query then looks like this:

Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT COUNT (DISTINCT Launch) FROM Movie WHERE Launch<2000" ) ]

Our answer is 10, because two of the films listed were made in the same year.

That brings us to the end of today's door. I hope we'll see again tomorrow.