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.

[MonkeybreadSoftware]

Today, I would like to introduce you to an important topic in SQL: Joins.

In FileMaker, we have relationships between the tables we work on, allowing us to relate data records to each other. We do not have these direct, fixed connections between two tables in SQL. Here, we have to create the relationship in the query, and we do that with Joins. There are different types of Joins, which we will now learn about using small examples so that we can use them later in your actual database. To do this, we will use a small section of our Movies table, which contains 5 movies for the example. We will simplify the primary key as a sequential number, as this is of course different in real life.

Primarykey	Name	Director	Launch	Generated_Sale
1	Die Hard	John McTiernan	1988	141,603,197
undefined	----	----	----	----
2	Christmas Vacation	Jeremiah S. Chechik	1989	71,319,526
undefined	----	----	----	----
3	Love Actually	Richard Curtis	2003	250,200,000
undefined	----	----	----	----
4	The Polar Express	Robert Zemeckis	2004	315,249,768
undefined	----	----	----	----
5	Crown for Christmas	Alex Zamm	2015	-
undefined	----	----	----	----

We also have a table called Roles2, which contains the roles in the films.

Name	Actor	Movie
John McClane	Bruce Willis	1
undefined	----	----
Hans Gruber	Alan Rickman	1
undefined	----	----
Karl	Alexander Godunov	1
undefined	----	----
Clark Griswold	Chevy Chase	2
undefined	----	----
Ellen Griswold	Beverly D'Angelo	2
undefined	----	----
Cousin Eddie	Randy Quaid	2
undefined	----	----
David	Hugh Grant	3
undefined	----	----
Karen	Emma Thompson	3
undefined	----	----
Harry	Alan Rickman	3
undefined	----	----
Allie Evans	Danica McKellar	5
undefined	----	----
King Maximillian	Rupert Penry-Jones	5
undefined	----	----
Princess Theodora	Ellie Botterill	5
undefined	----	----

(INNER) JOIN

First, we have the INNER JOIN, which is the join I use the most. The INNER JOIN connects two tables so that only records that match in both tables are displayed. It therefore only shows common hits – anything that has no connection is hidden. In our case, this means that only movies and actors with matching primary keys and names will be displayed. If we apply an INNER JOIN, often referred to as just JOIN, to these tables with primary key = Movie, it looks like this:

SELECT Rols2.Movie, Movies2.Name, Movies2.Director, Movies2.Launch, Rols2.Actor 
FROM Movies2 
JOIN Rols2 
ON Movies2.PrimaryKey = Rols2.Movie

First, we have our SELECT part again, in which we specify the fields. This time, we want fields from both tables, which is why we also put the table name in front of it, separated by a dot. We specify all the fields from which we want the data. Then we come to the FROM part. Here, we first specify table A, then the word JOIN, and then table B. After we have specified the tables, we then add the keyword ON. After this, we can specify the connection over which the result will be created. In our example, we now want to match the two tables using the Primary Key and Movie fields if the values in these fields are the same.

FM.SQL.Execute" ; Get(FileName); "SELECT Rols2.Movie, Movies2.Name, Movies2.Director, Movies2.Launch, Rols2.Actor FROM Movies2 JOIN Rols2 ON Movies2.PrimaryKey = Rols2.Movie" ) ] Set Variable [ $r ; Value: MBS("FM.SQL.Text"; $SQLRes; ""; ""; ""; ""; "¶"; "-") ] Show Custom Dialog [ "Result" ; $r ] Set Variable [ $r ; Value: MBS("FM.SQL.ReleaseAll") ]

However, we not only have INNER JOIN, but also other types of JOIN.

LEFT JOIN

Let's move on to LEFT JOIN. A LEFT JOIN is a special form of joining two tables that guarantees that all records from the left table appear completely in the result, regardless of whether there are matching entries in the right table. You can think of it this way: The left table forms the fixed point, and for each of its rows, the database tries to find the corresponding information from the right table. If a matching record exists in the right table, both are combined and output. However, if there is no corresponding entry, the query still includes the row from the left table in the result. In this case, the fields in the right table are simply filled with NULL.

Nice to know:
In this context, NULL means that a specific field does not contain a value - not "0", not "empty", not "unknown", but truly no existing data entry.
undefined

LEFT JOINs are useful when the left table is the focal point of the evaluation. A classic example would be a list of all available products (left table) that needs to be linked to the sales for the current day (right table). Even if a product has not been sold today, it still appears in the result because it is in the left table. In this way, missing information, anomalies, or gaps can be clearly identified. In our example, we now want the movies in the left table and the roles in the right table. These are also linked via the primary key of Movies2 to the Movies field in the Rols2 table.

Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT Movies2.Name, Movies2.Director, Movies2.Launch, Movies2.PrimaryKey, 
   Rols2.Movie, Rols2.Name, Rols2.Actor 
   FROM Movies2 LEFT JOIN Rols2 ON Movies2.PrimaryKey = Rols2.Movie" ) ]
Set Variable [ $r ; Value: MBS("FM.SQL.Text"; $SQLRes; ""; ""; ""; ""; "¶"; "-") ]
Show Custom Dialog [ "Result" ; $r ]
Set Variable [ $r ; Value: MBS("FM.SQL.ReleaseAll") ]

This is what the result looks like:

Unsupported joins in FileMaker

The FileMaker engine for SQL, that the plugin also works with, cannot handle RIGHT JOIN and FULL OUTER JOIN. Therefore, you cannot use them in conjunction with FileMaker. However, in case you encounter them, we will explain both here. RIGHT JOIN is very similar to LEFT JOIN, except that instead of taking all records from the left table, we take them from the right - regardless of whether a corresponding record exists in the left table or not, the fields in the left table that correspond to a record that has no match in the left table are filled with NULL. Another join type is the FULL OUTER JOIN. This includes all records from the left and right table. If there is no match for a record in the opposing table, the records are still transferred to the result set and we also fill them with NULL.

CROSS JOIN

I would also like to mention CROSS JOIN. This join does not use a specific criterion or field, but connects all records in the right table with all records in the left table, regardless of whether the records are matching. So if table A has x entries and table B has y entries, we end up with a result set containing x*y records. We also talk about forming the Cartesian product of these two tables. If we then want to narrow down the records, we can use a WHERE clause. A CROSS JOIN can have the following structure:

SELECT *
FROM 
tabel 1 CROSS JOIN tabel 2

However, another notation is also possible, because we also form the Cartesian product when we combine two tables in the FROM section by listing them one after the other.

SELECT *
FROM tabel 1 ,tabel 2

Today, I would like to give you a little task, which we will solve tomorrow in the door. How would our query

SELECT Rols2.Movie, Movies2.Name, Movies2.Director, Movies2.Launch, Rols2.Actor 
   FROM Movies2 
   JOIN Rols2 ON Movies2.PrimaryKey = Rols2.Movie

look like if we wanted to express it with a CROSS JOIN?

Have fun trying it out, and I hope to see you again tomorrow.

[MonkeybreadSoftware]

Welcome to door 7. We still have some homework from yesterday to discuss. The task was to solve this query with a cross join:

SELECT Rols2.Movie, Movies2.Name, Movies2.Director, Movies2.Launch, Rols2.Actor 
FROM Movies2 
JOIN Rols2 ON Movies2.PrimaryKey = Rols2.Movie

Such a cross join may look like this:

# CROSS JOIN WITH WHERE
Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT Rols2.Movie, Movies2.Name, Movies2.Director, Movies2.Launch, Rols2.Actor 
   FROM Rols2 CROSS JOIN Movies2 WHERE Rols2.Movie=Movies2.PrimaryKey" ) ]
Set Variable [ $r ; Value: MBS("FM.SQL.Text"; $SQLRes; ""; ""; ""; ""; "¶"; "-") ]
Show Custom Dialog [ "Result" ; $r ]
Set Variable [ $r ; Value: MBS("FM.SQL.ReleaseAll") ]

First, we create the cross join across both tables and then limit the result set using the WHERE clause so that only records are included in the result, where the Movie field from the Rols2 table matches the PrimaryKey field from the Movies2 table. This gives us the correct result:

Now let's move on to today's topic. Until now, we have always queried the database and had the results output as complete text. We want to change that today, because we can also go through and evaluate our results field by field. To do this, we use the FM.SQL.Field function. This function returns the value for a specific field. In the parameters of the function, we first specify the reference for the result, then the row number we want, then the column, and finally, we can optionally set a flag. If we set the flag to 0, which is also the default value, we get the value from the field; if we specify 1, we get the file name for a container field. If we specify 2, we get the container value back in base64-encrypted form.

Let's look at an example: we want to query the data from the results and then use it to create new data records. In this example, we want to write all movies with their name, launch year, and generated sales that were made before the year 2000 to a separate table. So that we don't have to deal with converting lists, we use the FM.SQL.Field function to run through the result. And this is what it might look like:

Go to Layout [ “Door7” (Door7) ; Animation: None ]
Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT Name, Launch, Generated_Sales FROM Movie WHERE Launch<2000" ) ]
Set Variable [ $CountRow ; Value: MBS("FM.SQL.RowCount"; $SQLRes) ]
Set Variable [ $i ; Value: 0 ]
Loop [ Flush: Always ]
	New Record/Request
	Set Field [ Door7::Name ; MBS( "FM.SQL.Field"; $SQLRes; $i; 0 ) ]
	Set Field [ Door7::Launch ; MBS( "FM.SQL.Field"; $SQLRes; $i; 1 ) ]
	Set Field [ Door7::Generated_Sales ; MBS( "FM.SQL.Field"; $SQLRes; $i; 2 ) ]
	Set Variable [ $i ; Value: $i+1 ]
	Exit Loop If [ $i ≥ $CountRow ]
End Loop

First, we make sure that we are in the correct layout in which we want to add our data records. In our case, this is the Door7 table that we have just created. Then we execute the appropriate query. Here we retrieve the fields we need: Name, Launch, and Generated_Sales from the Movie table, and we only want the records where Launch was earlier than 2000. Once we have the result, we determine how many records were found. To do this, we use the FM.SQL.RowCount function, which we already know from Door 5. We now go through the loop with this information and run through the individual fields. We retrieve the individual fields with the FM.SQL.Field function and use it to set the fields. When addressing the individual values, we must make sure that we do not start counting at 1 and instead start at 0.

Another helpful function is the FM.SQL.FieldType function. With this function, we can determine the field type of a field, e.g., whether it is text or a number.

To do this, we first specify the reference for the function and then specify the desired field with row and column numbers. Let's determine this for our query:

Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT Name, Launch, Generated_Sales FROM Movie WHERE Launch<2000" ) ]
Set Variable [ $TypName ; Value: MBS( "FM.SQL.FieldType"; $SQLRes; 0; 0 ) ]
Set Variable [ $TypLaunch ; Value: MBS( "FM.SQL.FieldType"; $SQLRes; 0; 1 ) ]
Set Variable [ $TypSale ; Value: MBS( "FM.SQL.FieldType"; $SQLRes; 0; 2 ) ]
Show Custom Dialog [ "Type" ; "Name: "& $TypName  & "¶Launch: " & 
   $TypLaunch &"¶Generated_Sales: " & $TypSale ]
Set Variable [ $r ; Value: MBS("FM.SQL.ReleaseAll") ]

We will look at the fields in the first row.

But today I would like to show you something else. Until now, we have only searched for entire values, such as a specific name or a number. But what if we only want to search for fragments? We still know that the movie title had something to do with Miracle. But how do we search for something like that? For this purpose, we have something in SQL called a wildcard. These are characters that we can use as placeholders. First, we have the % character. This stands for any character string that can be of any length. It can represent no, one, or several characters. This is best illustrated with an example. We still want to search for movies that contain the word "Miracle" somewhere. We don't know if it's at the beginning, end, or in the middle. So we use the placeholder and put it before and after the word Miracle.

Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT Name FROM Movie WHERE Name LIKE '%Miracle%'" ) ]
Set Variable [ $r ; Value: MBS("FM.SQL.Text"; $SQLRes;"";"";"";"";"¶¶";" - ") ]
Show Custom Dialog [ $r ]

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

Our results found 2 matching films:

In one film, the word "Miracle" appears right at the beginning, and in the other film, it appears at the end. But what if we know for sure that the film title does not begin with this term? That's where the second wildcard I want to introduce to you today comes in. The underscore stands for exactly one arbitrary character. For example, if we write h_t, it would find entries with hat and hot, but not hight. This is because we have an h at the beginning and a t at the end, but there is more than one character in between. So if we want to see the films that have at least one other character before the word 'Miracle', meaning that the title does not begin with it, we can combine the two wildcards: a single character, then a string of any length, the word 'Miracle', and then, for any characters that may follow, the wildcard for a string of any length again. The query then looks like this:

Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT Name FROM Movie WHERE Name LIKE '_%Miracle%'" ) ]

And we only get one movie back.

When using wildcards, we need to be aware that the terms are case sensitive. This means that a title in which the word "miracle" was written in lowercase would not have been found.

This brings us to the end of today's door, and we wish you a wonderful second Advent.

[MonkeybreadSoftware]

In Door 2, we saw that FileMaker itself has a function called ExecuteSQLe that can output error messages when errors occur in the process. If we have an error in the SQL query with the MBS functions, then instead of a result or a reference number, we receive an error message from the function that went wrong.

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

In some cases, it would be nice to have a function that would allow you to simply query the error without having to look at the first characters of the output. The plugin provides two functions that can help you with this: FM.ExecuteSQL.LastError and FM.ExecuteSQL.LastErrorMessage. The FM.ExecuteSQL.LastError function gives us an error code for the last error in the SQL. The error code always refers to the last SQL query called. If everything went well with the SQL query, we get the error code 0 back. Otherwise, we receive the corresponding error code, and there are several of these. This error code can also be very useful if you need to make a case distinction, because if it is 0, you know that there was no SQL error in the function and can continue working with the result, or if an error occurred, you can terminate the script, for example.

Set Variable [ $r ; Value: MBS( "FM.ExecuteFileSQL"; Get(FileName);
   "SELECT Nme, Launch FROM Movie WHERE Director=? OR Launch=? AND NOT Director=?";   
   "-" ;  "¶"; "Chris Columbus"; 2000; "Greg Beeman" ) ]
If [ MBS("FM.ExecuteSQL.LastError")=0 ]
	# Do something
Else
	Exit Script [ Text Result:    ]
End If

The error code does not provide much assistance in resolving the error, or we need to know what this code is stand for. For this reason, there is the FM.ExecuteSQL.LastErrorMessage function, which displays the corresponding error message for the last SQL query. In this example, the text clearly indicates what is incorrect:

Set Variable [ $r ; Value: MBS( "FM.ExecuteFileSQL"; Get(FileName); 
   "SELECT Nme, Launch FROM Movie WHERE Director=? OR Launch=? AND NOT Director=?";
   "-" ;  "¶"; "Chris Columbus"; 2000; "Greg Beeman" ) ]
Set Variable [ $errorcode ; Value: MBS("FM.ExecuteSQL.LastError") ]
Set Variable [ $errortext ; Value: MBS("FM.ExecuteSQL.LastErrorMessage") ]
Show Custom Dialog [ "Error" ;  $errorcode & "-" & $errortext ]

We spelled the name of the field incorrectly. If we correct this, our SQL will be correct again and we will not get an error message.

That's it for today on error handling. I hope you enjoyed it and we'll see each other again tomorrow.

[MonkeybreadSoftware]

Today, I would like to introduce two functions that you can use to evaluate the data in your results even further: FM.SQL.Sum and FM.SQL.Avg. The FM.SQL.Sum function calculates the total from a column in our results. Let's try this out right away and calculate the total number of screenings for all films. To do this, we have the following code:

Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT Generated_Sales FROM Movie" ) ]
Set Variable [ $r ; Value: MBS("FM.SQL.Sum"; $SQLRes; 0; ""; "") ]
Show Custom Dialog [ "Sum of generated sales" ; $r ]

First, we'll run our SQL query again. I've decided to limit the output to the column here, but if you need additional columns for the result, that's no problem at all, because we'll define the relevant column later in the FM.SQL.Sum function. This gives us our reference from the FM.SQL.Execute function again, which we can then use as the first parameter in the FM.SQL.Sumfunction. Furthermore, we specify in the parameters on which column the operation should be applied. Since we only have one column here and the index starts at 0, we specify 0 here. We can also select the range of rows that we want to use from the result for this action. So if you only want to get the box office takings from the first three films in the database as a sum, you can specify 0 and 2 in the parameters. However, since we want all the films here, we simply leave the two parameters blank or we can even leave them out completely. Our result is then displayed in a dialog box.

The same applies to the FM.SQL.Avg function, except that here it is not the sum but the average that is calculated. So if we want to calculate the average movie box office, we can write the code as follow:

Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT Generated_Sales FROM Movie" ) ]
Set Variable [ $r ; Value: MBS("FM.SQL.Avg"; $SQLRes; 0; ""; "") ]
Show Custom Dialog [ "Average of generated sales" ; $r ]

As we saw with door 5, we are working with the calculated result here. Once again, we have the option of using aggregate functions in SQL to control the whole process. Here, we have the keywords SUM and AVG. The aggregate functions are again applied directly to the database and not to the result first. Here, too, we again use the keyword in the SELECT part. After SELECT, we specify the keyword for our desired function, in this case SUM, followed by the field from which we want to calculate the sum in brackets. Similar to COUNT, we can again use a WHERE clause to preselect the values on which the sum is to be calculated. In our case, we want the sum of the box office takings for all Chris Columbus films.

SELECT SUM(Generated_Sales) 
FROM Movie 
WHERE Director='Chris Columbus'"  

This is what the script looks like:

Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT SUM(Generated_Sales) FROM Movie WHERE Director='Chris Columbus'" ) ]
Set Variable [ $r ; Value: MBS("FM.SQL.Text"; $SQLRes; ""; ""; ""; ""; "¶"; "-") ]
Show Custom Dialog [ "Sum of generated sales generat…" ; $r ]
Set Variable [ $r ; Value: MBS("FM.SQL.ReleaseAll") ]

It works similarly with AVG.

Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT AVG(Generated_Sales) FROM Movie WHERE Director='Chris Columbus'" ) ]
Set Variable [ $r ; Value: MBS("FM.SQL.Text"; $SQLRes; ""; ""; ""; ""; "¶"; "-") ]
Show Custom Dialog [ "Average of generated sales gen…" ; $r ]
Set Variable [ $r ; Value: MBS("FM.SQL.ReleaseAll") ]

That brings us to the end of today's door. I hope you enjoyed it.

[MonkeybreadSoftware]

Welcome to Door 10. Today, I would like to talk to you about the FM.SQL.Max and FM.SQL.Min functions. As their names suggest, these two functions determine the lowest or highest value within a result set. As we did yesterday, we first send an SQL query to the database using the FM.SQL.Execute function. We can then apply FM.SQL.Max and FM.SQL.Min. In our example, we want to determine the values of the highest and lowest generated sales. Our script then looks like this:

Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT Generated_Sales FROM Movie" ) ]
Set Variable [ $max ; Value: MBS("FM.SQL.Max"; $SQLRes; 0; ""; "") ]
Set Variable [ $min ; Value: MBS("FM.SQL.Min"; $SQLRes; 0; ""; "") ]
Show Custom Dialog [ "Minimum and Maximum generated …" ;
   "Min: " & $min & "¶Max: " & $max ]
Set Variable [ $r ; Value: MBS("FM.SQL.ReleaseAll") ]

Here, we could again select the range in our rows in the parameters that we want to use for value determination. In our case, it's all of them, so we leave both parameters blank. If we run it like this, we get this dialog:

Min remains blank. Why is that? If we look again at the information in our database, we see that we have rows in which no value is entered, but instead a dash. To get around this and still get a value, we need to add a WHERE clause to filter out this value. In FileMaker, we have selected the Number type for the field, which means that the dash is not accepted as a number and the field is accepted as empty. We can now filter out these fields by writing WHERE Generated_Sales!=0. Then we get the matching number as a minimum.

Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT Generated_Sales FROM Movie WHERE Generated_Sales!=0" ) ]

SQL also has corresponding aggregate functions for minimum and maximum that work directly on the database and not on the result set like the MBS functions. Here they are called MIN and MAX. The same form applies here as we saw yesterday, too.

SELECT Max(Generated_Sales) FROM Movie

First comes the SELECT, then the word MIN or MAX, depending on whether you want the minimum or maximum. Then, in brackets, comes the field from which we want to determine the minimum or maximum. In our case, this is Generated_Sales. Finally, the FROM part follows, and optionally our WHERE part again. With SQL aggregate functions, we don't need to filter out the empty fields. These are simply ignored.

Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT Max(Generated_Sales) FROM Movie" ) ]
Set Variable [ $max ; Value: MBS("FM.SQL.Text"; $SQLRes; ""; ""; ""; ""; "¶"; "-") ]
Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT Min(Generated_Sales) FROM Movie" ) ]
Set Variable [ $min ; Value: MBS("FM.SQL.Text"; $SQLRes; ""; ""; ""; ""; "¶"; "-") ] 

Show Custom Dialog [ "Minimum and Maximum generated …" ; "Min: " & $min & "¶Max: " & $max ]

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

I would like to introduce you to another clause in SQL: GROUP BY

GROUP BY combines records into groups that have the same value in one or more fields. Within each of these groups, you can use aggregate functions such as MIN, MAX, SUM, AVG, or COUNT to calculate group results. Without GROUP BY, aggregate functions always refer to the entire table. With GROUP BY, they refer to each group separately.

Let's do this for the years and output the highest Generated_Sales for each year.

SELECT Launch, MAX(Generated_Sales) FROM Movie GROUP BY Launch

In this return, we want to see the Launch field and also the maximum amount. To do this, we first specify the Launch field in the SELECT part, separate it with a comma from our aggregate function MAX, which is then written as we have already discussed. Then comes the FROM part, followed by the GROUP BY clause, after which we specify the field by which we want to group. In our case, these are the years of launch and thus the Launch field.

Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT Launch, MAX(Generated_Sales) FROM Movie GROUP BY Launch" ) ]
Set Variable [ $r ; Value: MBS("FM.SQL.Text"; $SQLRes; ""; ""; ""; ""; "¶"; "-") ]

Show Custom Dialog [ "Max generated sales from the i…" ; $r ]
Set Variable [ $r ; Value: MBS("FM.SQL.ReleaseAll") ]

Now it would also be nice to see the movies that are involved. So just add the Name field to the SELECT part? No, unfortunately it's not that simple. When creating a query with GROUP BY in SQL, you have to pay attention to an important basic rule: All fields in the SELECT part must either be in the GROUP BY clause or be summarized by an aggregate function such as MIN, MAX, SUM, or AVG. If this rule is broken, the query is logically unclear and SQL will abort with an error. Name is not grouped and not aggregated. SQL would therefore not know which movie title to use. That's why we have to support SQL and formulate the query differently.

SELECT  Launch, Name, Generated_Sales
FROM Movie m1
WHERE Generated_Sales = (SELECT MAX(Generated_Sales)
			 FROM Movie m2 
			 WHERE m2.Launch = m1.Launch)

First, in the SELECT part, we again specify the fields we want to get back. Then, in the FROM part, we specify that the information should come from the Movie table. But there is a special feature here, because we specify an alias for the table after the table name. This will be important again in a moment. Then we have a WHERE part in which we make an inner query. To determine the highest annual revenue, the inner query executes a MAX function on the same table, this time under the alias m2. This subquery filters the table so that only movies whose launch year matches the current movie from the outer query are considered. The maximum sales value is then calculated for this group of films. The result of the subquery is thus the highest sales achieved in the respective year. The WHERE condition compares this maximum value from the inner query with the sales of the current film in the outer query. If both values match, it means that this film is one of the highest-grossing films of its release year. Accordingly, this film – or, in the event of a tie, several films from the same year – is included in the overall result. This provides a list of the most successful films per year, each with the year, title, and box office , without the need for a GROUP BY.

In our result, the years are still in no particular order. We want to change this and use the keyword ORDER BY to sort the result. We place this at the end of our outer query and pass the field name Launch, according to which we want to sort the result.

Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT  Launch, Name, Generated_Sales 
   FROM Movie m1 
   WHERE Generated_Sales = (SELECT MAX(Generated_Sales)
                            FROM Movie m2 
                            WHERE m2.Launch = m1.Launch ) 
                            ORDER BY Launch") ]
Set Variable [ $r ; Value: MBS("FM.SQL.Text"; $SQLRes; ""; ""; ""; ""; "¶"; "-") ]
Show Custom Dialog [ "Max generated sales from the i…" ; $r ]
Set Variable [ $r ; Value: MBS("FM.SQL.ReleaseAll") ]

This gives us a sorted result

That's it for today's door. I hope you enjoy trying it out and I hope to see you again tomorrow.

[MonkeybreadSoftware]

Today, we want to take a look at how we can use SQL to insert new records into our table. This feature is incredibly cool because you can insert a record into your FM table without having to switch layouts. Personally, this is my favorite use of SQL in FileMaker databases. Let's take a closer look at the appropriate SQL command:

INSERT INTO table_name (field1, field2, field3, ...) 
VALUES (value1, value2, value3, …)

First, we have the keywords INSERT INTO. This is followed by the name of the table into which the new data is to be inserted. The field names for which we want to specify a value are given in brackets. This is followed by the keyword VALUES, which indicates that the specified fields are now to be filled with data. We then enter the values for the individual fields, also in brackets, in the correct order. If we now want to enter a new Christmas movie for our Movie table, it might look like this:

Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "INSERT INTO Movie (Name, Director, Launch, Generated_Sales) 
    VALUES ('A Christmas Story', 'Bob Clark', 1983, 19200000)" ) ]

The data record is inserted quickly and easily.

If you don't want to remember the entire SQL command, MBS also offers a function called FM.InsertRecord. In this function, you first specify the file in which you want to insert the record. Since we want to stay in the same file, we use Get(FileName) here. Next, we select the table to which the data record is to be added. Then we alternately specify the field name and the corresponding value. We can specify as many fields as necessary with the corresponding value. For our movie, it looks like this:

Set Variable [ $r ; Value: MBS("FM.InsertRecord"; Get(FileName); "Movie";
   "Name"; "A Christmas Story"; 
   "Director"; "Bob Clark"; 
   "Launch"; 1983; 
   "Generated_Sales"; 19200000) ]

I hope this useful tool will make your work a little easier too.

[MonkeybreadSoftware]

Today, I would like to explain the term UNION in SQL in detail. With UNION, we can combine results from multiple tables. To demonstrate this, we will create another table, because in a film, there are not only leading roles, as we have in the Rols2 table, but also supporting roles. These also have a role name and an actor, and then we assign the film to them as well.

We now want to combine these two tables in the result. We want to know which actors appeared in the individual films, regardless of whether they played a leading or supporting role. To do this, we formulate our UNION statement. This consists of two SELECT-FROM parts that are then connected to each other. In the first SELECT FROM part, we want the fields Name, Actor, and Movie from the Rols2 table. In the second part, we want the fields Name, Actor, and Movie from the table Supporting_roles. We now combine these two queries by writing the term UNION between them. Since we now get all actors back, this can become quite confusing. For this reason, we want to sort the whole thing according to the individual films. The query then looks like this:

SELECT Name, Actor, Movie 
FROM Rols2 
UNION 
SELECT Name, Actor, Movie 
FROM Supporting_roles 
ORDER BY Movie

This gives us the following result:

But maybe we only want to have one movie in the issue. For this, we need the WHERE clause. Since we have two SELECT parts that make a selection, we can also work with two WHERE parts. So we filter for the desired movie, in this case the second one. That's why our query looks like this:

SELECT Name, Actor, Movie 
FROM Rols2 
WHERE Movie='2' 
UNION 
SELECT Name, Actor, Movie 
FROM Supporting_roles 
WHERE Movie='2'

With some roles, it's a bit tricky. They don't occur very often, but they play an enormously important role in the plot. This is the case with Brian Doyle-Murray, who stars in the film Christmas Vacation. He has been listed once in the table of leading roles and once in the table of supporting roles. The simple UNION now only shows him once. This is because UNION sorts out all multiple records and only allows one record in the result set. With a UNION ALL, it's different; here, multiple records are also included in the result set. Let's use UNION ALL here in the same way as Union, so that our script step looks like this:

Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT Name, Actor, Movie FROM Rols2 WHERE Movie='2' 
    UNION ALL SELECT Name, Actor, Movie FROM Supporting_roles WHERE Movie='2' 
    ORDER BY Name" ) ]

We see Brian Doyle-Murray listed twice here in the role of Clark's boss.

That brings us to the end of today's episode, and I hope to see you again tomorrow.

[MonkeybreadSoftware]

In Door 8, we learned about error handling and were able to use the FM.ExecuteSQL.LastError and FM.ExecuteSQL.LastErrorMessage functions to read the error code and error description provided by the last SQL query with FileMaker. We can display such an error in a dialog box, for example. Often, you may not even know which SQL query was involved. But how can we find out? The FM.ExecuteSQL.LastSQL function helps us with this. It provides you with the last SQL command as run by the plugin.

We can now write this script to query the error in the SQL query and at the same time output the SQL query itself.

Set Variable [ $r ; Value: MBS( "FM.ExecuteFileSQL"; Get(FileName); 
   "SELECT Nme, Launch FROM Movie WHERE Director=? OR Launch=? 
   AND NOT Director=?";"-" ;  "¶"; "Chris Columbus"; 2000; "Greg Beeman" ) ]
If [ MBS("FM.ExecuteSQL.LastError") ≠ 0 ]
	Set Variable [ $errorcode ; Value: MBS("FM.ExecuteSQL.LastError") ]
	Set Variable [ $errortext ; Value: MBS("FM.ExecuteSQL.LastErrorMessage") ]
	Set Variable [ $SQLQuery ; Value: MBS("FM.ExecuteSQL.LastSQL") ]
	Show Custom Dialog [ "Error" ; "Last SQL Query: " &$SQLQuery & 
	   "¶¶Errorcode: " &  $errorcode & "¶¶Error text: " &  $errortext ]
End If

Have fun figuring out your SQL queries.

[MonkeybreadSoftware]

We have already established that we can output the results of our SQL query not only as text, but also as a matrix, for example. In today's door, I would like to show you another way of representing the results of your query. Today, we want to receive our results as a JSON return. JSON (JavaScript Object Notation) is an easy-to-understand, text-based data format that is used to store and exchange information in a structured way. It consists of simple key-value pairs and lists is both easy to read for humans and easy to process for computers. JSON is often used to transfer data between applications, web services, or databases.

Three different functions are available for working with SQL in FileMaker. Let's start with the FM.SQL.JSONRecords function. This function can output the data we determine in an SQL query as JSON. To do this, we first specify the reference to our SQL query in the function and then specify the names for the keys in the JSON. In our case, we kept the same names as the field names in the database. That's all it takes to get a result. In this example, we limited the SQL query to the fields Name, Director, Launch, and Generated_Sales.

Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT Name, Director, Launch, Generated_Sales FROM Movie" ) ]
Set Variable [ $JSON ; Value: MBS("FM.SQL.JSONRecords"; $SQLRes; 
   "Name¶Director¶Launch¶Generated_Sales") ]

And this is a part of our result:

[
	{
		"Name":	"The Nightmare Before Christmas",
		"Director":	"Henry Selick",
		"Launch":	1993,
		"Generated_Sales":	95514188
	}, 
	{
		"Name":	"It's a Wonderful Life",
		"Director":	"Frank Capra",
		"Launch":	1946,
		"Generated_Sales":	3300000
	},
…
	{
		"Name":	"The Santa Clause 2",
		"Director":	"Michael Lembeck",
		"Launch":	2002,
		"Generated_Sales":	172900000
	}
]

This result now contains the desired information from our table and could be passed on to another application that can read JSON data.

But the FM.SQL.JSONRecords function has a few more optional parameters that I'd like to look at with you now. That's because we have the option of setting different flags. Flags are additional options that, in this case, can influence the output of the JSON. Here are the options:

1	get all values as text
2	get all dates, times and timestamps in SQL format (formatted by plugin) instead of GetAsText() by FileMaker
undefined	----
4	arrays instead of objects for the individual rows
undefined	----
8	to get containers as objects with name, size and data entries.
undefined	----
32	detect JSON in text values and return the JSON inline.
undefined	----

For example, by specifying a one, we can output all values we receive from the SQL result as text values instead of, for example, the year of publication as a number. In this case, this would also be enclosed in quotation marks and be a text in the result. Then the first object in our JSON would look like this:

…
	{
		"Name":	"The Nightmare Before Christmas",
		"Director":	"Henry Selick",
		"Launch":	"1993",
		"Generated_Sales":	"95514188"
	}, 
…

Instead of objects containing key-value pairs for each data record, the 4 gives us an array listing the values of a data record. Depending on how you want to proceed with the JSON, this can be a practical format. The result may then look like this:

[
	[
		"The Nightmare Before Christmas", 
		"Henry Selick", 
		1993, 
		95514188
	], 

…
[
		"The Santa Clause 2", 
		"Michael Lembeck", 
		2002, 
		172900000
	]
]

Our table does not currently have any container fields, but if we look at another table where an image is stored in a container field, we can see that, in addition to the data stored in the container, we also receive information such as the field name and size when we specify an 8 in the flags. This might look like this:

[
	{
		"Text":	"Hello",
		"Image":	{
			"name":	"image:Tree.png",
			"size":	1562721,
			"data":	„iVBORw0KGgoAAAANSUhEUgAABJ0AAAbs …
			        … oIACCiiggH2+tpNbF1YFOAAAAAElFTkSuQmCC"
		}
	}
]

Of course, it is also possible that there may be fields in which JSON can be found. If we do not set the corresponding flag here, the JSON in a field is simply recognized as text. If we set flag 32, the JSON is integrated into the JSON structure.

[
	{
		"Text":	"Hello",
		"JSON":	{
			"Name":	"Santa Clause",
			"Nickname":	"Santa",
			"YearOfBirth":	270
		},
…

We can also combine flags by simply adding them together. So, if we want to combine the JSON container flag (8) and the JSON flag (32), it would look like this:

# with Flags Container and JSON 
Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT Text, JSON, Image FROM Door14" ) ]
Set Variable [ $JSON ; Value: MBS("FM.SQL.JSONRecords"; $SQLRes; 
   "Text¶JSON¶Image"; 8+32) ]

In addition to the flags, we can also limit our return by defining which part of the SQL result we want as JSON. To do this, we can specify the first and last rows as parameters.

If we only want to receive a single data record as JSON, the FM.SQL.JSONRecord function is particularly suitable for this. This is because here you can select which data record from the SQL query result set you want to receive as JSON. In this function, we first define the reference and then the row we want to receive. Here, we start counting again at 0, which would then be the first row, 1 the second row, and so on. Next, we need the list with the key names again, and finally, if necessary, we can set the exact same flags here that we already learned about with FM.SQL.JSONRecords.

# FM.SQL.JSONRecord
Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT Name, Director, Launch, Generated_Sales FROM Movie" ) ]
Set Variable [ $JSON ; Value: MBS("FM.SQL.JSONRecord"; $SQLRes;
   0; „MovieName¶Director¶Launch¶Generated_Sales“, 0) ]

As a result, we receive the first movie in the database as JSON. We have assigned the key MovieName to the Name field.

Now all that remains is the FM.SQL.JSONColumn function. This function returns an array containing the entries from a column in the SQL query result. So if we want to have all the movies as a JSON array, we can use this function on our SQL query result to read this column. It might look like this:

# FM.SQL.JSONColumn
Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT Name, Director, Launch, Generated_Sales FROM Movie" ) ]
Set Variable [ $JSON ; Value: MBS("FM.SQL.JSONColumn"; $SQLRes;0) ]

Here, too, we first specify the reference from the previously submitted SQL query and then the column we want as an array. In our case, it is the Name column and therefore the first one, which has the index 0. If necessary, the flags can also be specified here. However, flag 4 is omitted because it is already a JSON array.

Our result looks like this:

I hope you enjoyed this door as well, and we'll see again tomorrow.

[MonkeybreadSoftware]

Today, I would like to introduce you to a few functions that do not directly affect SQL queries, but can still be very useful. In order to perform SQL queries, we need to know the database structure of our database. Which tables do we have and what are the names of the fields in the corresponding tables? I would now like to find this out together with you. Our plugins have included the relevant functions for years.

Let's start with the functions provided by the names of the tables that exist in the database. You may be wondering: functions? Why more than one? This is because we need to distinguish between tables and base tables. Base tables are the actual, physically existing tables within a FileMaker database. Each of these tables contains the actual records and fields, regardless of how often or under what name they are used in the relationship graph. These base tables are queried using the MBS function FM.QueryBaseTableNames. The function does not require any additional parameters if you want to know the base tables from the database in which the corresponding function is called. If you want to have the table names from another database, you need the optional parameter in which you can specify the name of the desired database.

Then we have the FM.QueryTableNames function. In contrast to the base table, this function also counts table occurrences, which are only references to the base tables, and displays them in the list. The parameters for this function are identical to those for the previous function.

FileMaker also has functions for querying base table names and table names. While FileMaker's own BaseTableNames function and the MBS function FM.QueryBaseTableNames produce the same results, there is a difference between TableNames and FM.QueryTableNames. When we delete a base table in a database, the table occurrence remains in the relationship graph even though no table is assigned to it. FileMaker returns such table occurrences without a base table as tables, whereas the plugin sorts out these table occurrences.

Let's take a look at what values the individual functions output when used on our database. This is our relationship graph.

Because we're working with SQL here all the time, we don't have any relationships between the tables. The dark gray tables are table occurrences of tables that once existed but have now been deleted, so these table occurrences no longer have a base table. The blue table is a table occurrence with the base table Movie as its basis.

So what happens now if I first apply the BaseTableNames and FM.QueryBaseTableNames functions to this database?

Set Variable [ $BaseTableFM ; Value: BaseTableNames ( Get(FileName) ) ]
Show Custom Dialog [ "FM -  BaseTableNames" ; $BaseTableFM ]
Set Variable [ $BaseTableMBS ; Value: MBS("FM.QueryBaseTableNames"; Get(FileName)) ]
Show Custom Dialog [ "MBS -  FM.QueryBaseTableNames" ; $BaseTableMBS ]

The results are identical here.

The situation is different for TableNames and FM.QueryTableNames. Here, the MBS function does not display table occurrences that are not assigned to a base table. They are still listed in the FM function.

Set Variable [ $TableFM ; Value: TableNames ( Get(FileName) ) ]
Show Custom Dialog [ "FM - TableNames" ; $TableFM ]
Set Variable [ $TableMBS ; Value: MBS("FM.QueryTableNames"; Get(FileName)) ]
Show Custom Dialog [ "MBS -  FM.QueryTableNames" ; $TableMBS ]

For us, it's not just the table and base table names that are interesting, but also the field names in each table. Here, too, we have two functions: FM.QueryFieldsForBaseTableName and FM.QueryFieldsForTableName. As the name suggests, there is again a distinction between base tables and tables. We can see this difference when we query the fields of the Movie_Copy table occurrence. The FM.QueryFieldsForTableName function provides us with the corresponding field names, while FM.QueryFieldsForBaseTableName returns an empty text because it is a table occurrence and not a base table. When calling both functions, we first specify the table name in the parameters and then, if necessary, the file name of the database.

Set Variable [ $BaseFieldNamesMBS ; Value: MBS("FM.QueryFieldsForBaseTableName"; "Movie_Copy"; Get(FileName)) ]
Show Custom Dialog [ "MBS -  FM.QueryFieldsForBaseTa…" ; $BaseFieldNamesMBS ]
# 
Set Variable [ $FieldNamesMBS ; Value: MBS("FM.QueryFieldsForTableName"; "Movie_Copy"; Get(FileName)) ]
Show Custom Dialog [ "MBS -  FM.QueryFieldsForTableN…" ; $FieldNamesMBS ]

That brings us to the end of today's session. I hope to see you again tomorrow.

[MonkeybreadSoftware]

Today we want to take a look at how to delete records. As already mentioned in door 3, we can also delete records using the MBS SQL functions. Today you will learn how this works.

For deleting, we have the functions FM.DeleteRecord and FM.DeleteRecords in the MBS FileMaker Plugin, which I would like to introduce to you. As the name suggests, the FM.DeleteRecord function is well suited for deleting single records from the database. This function works with unique fields. In the function, we first define the file in which we find the record to be deleted in the parameters, then the table name, and finally the field information that identifies the data record. We specify the field name in which we find the unique ID and then the unique ID itself of the record to be deleted. This is the information you must provide. You can also specify the data record with additional unique IDs. It is important that the fields that help us identify the data record not only contain unique values, but that this is also reflected in the database structure in the validation. If we set the name as a field in the Door7 table, even though the values are unique, an error will occur because it is not declared as unique in the database. So, if we want to delete the movie Gramlins from our database in the Door7 table, we use the PrimaryKey field and the primary key corresponding to the movie for identification.

Set Variable [ $r ; Value: MBS("FM.DeleteRecord"; Get(FileName); "Door7"; "PrimaryKey"; "C44FD2D1-75CA-4C4E-967A-9632C1BA64B9") ]

If we want to delete multiple records, or if we don't have the option of using the unique ID, we can use the FM.DeleteRecords function instead. The structure of the function is exactly the same, except that here the field that identifies the record does not have to contain unique values. This means we can now also delete movies by name.

Set Variable [ $r ; Value: MBS("FM.DeleteRecords"; Get(FileName); "Door7"; "Name"; "Die Hard") ]
Show Custom Dialog [ $r ]

However, multiple records can also be deleted simultaneously. For example, all movies released in 1992.

Set Variable [ $r ; Value: MBS("FM.DeleteRecords"; Get(FileName); "Door7"; "Launch"; "1992") ]
Show Custom Dialog [ $r ]

But we don't just have functions specifically for deletion; we can also perform deletion using SQL functions. To do this, we need the keyword DELETE. To delete specific data records, we need the following structure:

DELETE FROM TabelName WHERE condition

So we specify that we want to delete records, define the table from which we want to delete the records in the FROM section, and specify the condition that a record must fulfill in order to be deleted in the WHERE section.

For example, we can delete all movies released before 1990.

Set Variable [ $r ; Value: MBS("FM.ExecuteFileSQL"; Get(FileName); 
   "DELETE FROM Door7 WHERE Launch < 1990") ]

Sometimes we may want to delete all records in a table. This is also very easy to do with SQL. To do this, we simply omit the WHERE clause and all records in the specified table will be deleted.

Set Variable [ $r ; Value: MBS("FM.ExecuteFileSQL"; Get(FileName); "DELETE FROM Door7") ]

But be careful: once you delete something from the database, you cannot undo this step, so use this powerful tool wisely.

I hope you enjoy using these features.

[MonkeybreadSoftware]

Welcome to today's door. Today, I would like to introduce a function that allows you to compare changes in tables. The FM.CompareTables function is available for this purpose. It compares two tables for their fields and records and checks whether data has been changed, added, or deleted. This function has several parameters, so let's take a look at them together.

MBS( "FM.CompareTables"; FileName1; TableName1; IDField1; FileName2; TableName2; IDField2 { ; Options } )  

First, we have the file name of the database that contains the first table to be compared, followed directly by the table name and then the ID field from the table. This must be unique so that a comparison is possible. Then we do the same for the second table: file name, table name, and ID field. The ID field from the first table and the ID field from the second table should contain values that can be compared with each other. Optionally, we can then specify options to influence the output we receive. But more on that later.

We have copied the file we have been working with and made a few changes to the Movies table, which I would now like to explore with you.

We fill in the parameters of our function:

Set Variable [ $File ; Value: Get(FileName) ]
Set Variable [ $JSON ; Value: MBS( "FM.CompareTables"; $File; "Door7"; "PrimaryKey"; 
   "Christmas2025_Copy"; "Door7"; "PrimaryKey"  ) ]

We will now run this in our source file. However, we must also note that if the fmplugin privilege exists in your database in the security settings, this privilege must also be activated in both files for the script to function properly. Furthermore, if we have to perform the comparison on two databases, both must be opened with the same FileMaker. If everything is correct, we will receive a JSON like this.

{
	"Messages":	[],
	"TableName1":	"Door7",
	"TableName2":	"Door7",
	"FileName1":	"Christmas2025",
	"FileName2":	"Christmas2025_Copy",
	"FieldsIgnored":	[],
	"RemovedFields":	[],
	"Fields":	[
		"CreatedBy", 
		"CreationTimestamp", 
		"Generated_Sales", 
		"Launch", 
		"ModificationTimestamp", 
		"ModifiedBy", 
		"Name", 
		"PrimaryKey"
	],
	"AddedFields":	[
		"Ranking"
	],
	"RemovedIDs":	[
		"1D047820-6864-4CB9-88A0-0F15FA3D82D5"
	],
	"AddedIDs":	[
		"D45E3ABC-68F0-408D-8B7F-A2B777489DD8"
	],
	"ChangedRecords":	[
		"3E5C7469-5322-43D6-8404-F48735DD8A0C", 
		"027E9C32-01FA-4296-B1E4-8E38371BC540", 
		"C4FFF754-1D79-4C4E-8E3F-B49B6BD446A2", 
		"BF6DA185-CD55-4DFB-B414-CD6A897CC21A"
	]
}

Let's take a look at this together. First, we can see in the upper keys exactly which tables from which files are being compared here. Then we see that no fields have been removed from the table and all the fields that are present in the original table are listed again. A field called “Ranking” has been added, one record has been removed, and a new one has been added. Three additional records have been changed. The corresponding IDs are displayed so that we can identify the corresponding records. However, this does not provide us with much information about the changes in the records, and this is where the optional options mentioned above come into play. If we enter a 1 here, we will receive more detailed information about the changes:

{
	"Messages":	[],
	"TableName1":	"Door7",
	"TableName2":	"Door7",
	"FileName1":	"Christmas2025",
	"FileName2":	"Christmas2025_Copy",
	"FieldsIgnored":	[],
	"RemovedFields":	[],
	"Fields":	[
		"CreatedBy", 
		"CreationTimestamp", 
		"Generated_Sales", 
		"Launch", 
		"ModificationTimestamp", 
		"ModifiedBy", 
		"Name", 
		"PrimaryKey"
	],
	"AddedFields":	[
		"Ranking"
	],
	"RemovedIDs":	[
		"1D047820-6864-4CB9-88A0-0F15FA3D82D5"
	],
	"AddedIDs":	[
		"D45E3ABC-68F0-408D-8B7F-A2B777489DD8"
	],
	"ChangedRecords":	{
		"3E5C7469-5322-43D6-8404-F48735DD8A0C":	[
			{
				"Field":	"Launch",
				"OldValue":	"1993",
				"NewValue":	"1994"
			}, 
			{
				"Field":	"ModificationTimestamp",
				"OldValue":	"2025-12-06 01:19:07",
				"NewValue":	"2025-12-10 11:08:36"
			}
		],
		"027E9C32-01FA-4296-B1E4-8E38371BC540":	[
			{
				"Field":	"ModificationTimestamp",
				"OldValue":	"2025-12-06 01:19:07",
				"NewValue":	"2025-12-10 11:08:55"
			}, 
			{
				"Field":	"Name",
				"OldValue":	"Gremlins",
				"NewValue":	"Gremlin"
			}
		],
		"C4FFF754-1D79-4C4E-8E3F-B49B6BD446A2":	[
			{
				"Field":	"ModificationTimestamp",
				"OldValue":	"2025-12-06 01:19:07",
				"NewValue":	"2025-12-11 15:54:06"
			}
		],
		"BF6DA185-CD55-4DFB-B414-CD6A897CC21A":	[
			{
				"Field":	"ModificationTimestamp",
				"OldValue":	"2025-12-08 12:52:00",
				"NewValue":	"2025-12-10 11:09:35"
			}, 
			{
				"Field":	"Name",
				"OldValue":	"I'll Be Home for Christmas",
				"NewValue":	"I will Be Home for Christmas"
			}
		]
	}
}

We can now see every single change in the JSON and compare the old and new values at a glance.

We can also set a 2 in the options, which will give us a list of all IDs in addition to the basic information in the JSON.

{
	"Messages":	[],
	"TableName1":	"Door7",
	"TableName2":	"Door7",
	"FileName1":	"Christmas2025",
	"FileName2":	"Christmas2025_Copy",
	"FieldsIgnored":	[],
	"RemovedFields":	[],
	"Fields":	[
		"CreatedBy", 
		"CreationTimestamp", 
		"Generated_Sales", 
		"Launch", 
		"ModificationTimestamp", 
		"ModifiedBy", 
		"Name", 
		"PrimaryKey"
	],
	"AddedFields":	[
		"Ranking"
	],
	"RemovedIDs":	[
		"1D047820-6864-4CB9-88A0-0F15FA3D82D5"
	],
	"IDs":	[
		"C771BFDA-C49F-4E1D-A489-C071FBF06F84", 
		"2C1D5302-1A63-49AA-A6DD-1361F675D096", 
		"A090D14E-31AA-4684-99C9-9639D5194871", 
		"C44FD2D1-75CA-4C4E-967A-9632C1BA64B9", 
		"9951D64D-BCF9-41BA-BA50-03CE17D061E1", 
		"94ADC72B-F80E-48E2-AC9B-9E2E10576C55", 
		"82E92D98-7750-4A7C-95CD-78B1C386F3BA", 
		"7CB7D5DC-FB55-4A54-8765-9D8E6B120838", 
		"0DC9DFA0-7152-4071-913A-BAB4D51D80E8", 
		"DC6C070F-1C0D-408F-B425-195121E3F2B6", 
		"176B2740-3AE0-4846-A21D-CF5FAE3F5C0C", 
		"788B5BF3-D55D-4698-BFE7-98D16B36D652", 
		"3E5C7469-5322-43D6-8404-F48735DD8A0C", 
		"E6D966F4-8B06-422A-A3AF-4B4305447D1A", 
		"8DB8549F-A503-4E7A-B4E7-AEAC22E08700", 
		"027E9C32-01FA-4296-B1E4-8E38371BC540", 
		"AA9121AE-A49E-4D8C-8CC3-8AF5ECDF8AD1", 
		"15410990-349B-45EF-B80F-C9315058F335", 
		"C4FFF754-1D79-4C4E-8E3F-B49B6BD446A2", 
		"B3BA53FD-FBE9-457B-96D2-46103D0167E7", 
		"C4F29C78-6286-450C-9564-BE20CBAAEA19", 
		"BF6DA185-CD55-4DFB-B414-CD6A897CC21A"
	],
	"AddedIDs":	[
		"D45E3ABC-68F0-408D-8B7F-A2B777489DD8"
	],
	"ChangedRecords":	[
		"3E5C7469-5322-43D6-8404-F48735DD8A0C", 
		"027E9C32-01FA-4296-B1E4-8E38371BC540", 
		"C4FFF754-1D79-4C4E-8E3F-B49B6BD446A2", 
		"BF6DA185-CD55-4DFB-B414-CD6A897CC21A"
	]
}

Of course, if necessary, the two options can be combined by adding the numbers together. This will give us a JSON with the maximum information.

I hope this function helps you when you want to compare two tables.

[MonkeybreadSoftware]

Welcome to door 18 of our Advent calendar. Today, I would like to show you how to update data records.

The plugin offers two types of functions for this purpose. On the one hand, we have functions that update a single data record, and on the other hand, we have functions that update several data records at once. Let's start with the single records. Here we have the functions FM.UpdateRecord, FM.UpdateRecord2, FM.UpdateRecord3, FM.UpdateRecord4, and FM.UpdateRecord5. We'll come back to why there are so many update functions in a moment.

Let's take a look at the structure of the FM.UpdateRecord function. First, we need to specify the file name of the database in which we want to update data. In our case, this is the current one, and we can use the familiar Get function from FileMaker. Then we enter the name of the table in which the record is located, and then we can specify an ID field that uniquely identifies the record, as well as the appropriate value required for identification. As in Door 16, we must ensure that not only are the values in the table unique, but that they have also been defined as unique in the database structure. The parameters of the function are then followed alternately by the field names and the corresponding values to be updated. There are two films with the same name, Miracle on 34th Street. We entered the old film in our database. But we actually wanted to enter the new one. Of course, we could now remove the data record and add a new one with the data, but we can also update it with the new step. If we insert the data just discussed into the function, it looks like this:

Set Variable [ $r ; Value: MBS("FM.UpdateRecord"; Get(FileName); "Movie"; 
   "PrimaryKey"; "0E9810F9-46B0-4508-99B6-458B3C88808E"; 
   "Director";"Les Mayfield";"Launch";1994;"Generated_Sales";46300000) ]

We have already mentioned that we have various functions that sound similar but only have a different sequential number. These functions are very similar to the function we have just learned about, except that we can specify as many ID fields as the number in the function, which specify the data record for us.

In addition to the functions for updating a single data record, there are also functions for updating multiple data records: FM.UpdateRecords, FM.UpdateRecords2, FM.UpdateRecords3, FM.UpdateRecords4, FM.UpdateRecords5.

Here, too, we see the numbers behind the functions, and here, too, the reason is that, as with the previous functions, we can have a different number of fields to identify the data record, depending on the function. But let's take a look at an example here as well: We have a Genre field for each movie. So far, it says Christmas Movie everywhere. We want to change that now. We want all films directed by Chris Columbus to now have the term “Christmas comedy” as their Genre. The parameters of the function are identical to those of the individual functions. The only exception is that the ID fields for identification no longer have to be unique. We can then implement the example as follows:

Set Variable [ $r ; Value: MBS("FM.UpdateRecords"; Get(FileName); "Movie"; 
   "Director"; "Chris Columbus"; "Genre";"Christmas comedy") ]

SQL also offers a way to update fields. To do this, we use the keyword Update. In the SQL statement, we first have the keyword UPDATE followed by the table name. Now we have to specify which fields should receive which values. We introduce this part with the term SET, followed by the field names with an equal sign and then the corresponding value. The individual fields can then be listed separated by commas. Last but not least, we have the WHERE clause, in which we define to which data records this change should apply.

This results in the following structure:

UPDATE Table SET Field1= Value1, Field2=Value2… WHERE Condition

Let's imagine that one of our films had the wrong year in the data record. The Santa Claus 2 film was not broadcast in 2001, but in 2002. We now want to correct this.

Set Variable [ $r ; Value: MBS("FM.ExecuteFileSQL"; Get(FileName); 
   "UPDATE Movie SET Launch = 2002 WHERE Name='The Santa Clause 2'") ]

That brings us to the end of today's door. I hope you enjoyed it, and see you tomorrow for door number 19.

[MonkeybreadSoftware]

Welcome to door 19. In previous doors, we have already seen some alternatives to pure text output. Today, I would like to show you another one, which is output as CSV.

A CSV (Comma-Separated Values) is a simple, text-based file format for storing tabular data. Each row corresponds to a data record, and the individual values are separated by a delimiter (usually a comma or semicolon). It is easily readable by both - humans and machines. We now want to output our data from the database as CSV.

The FM.SQL.CSV function is available for this purpose. In the parameters, we first define our SQL query. That's all we need for the function, but like many functions, we can also optionally specify a range in the SQL result we want to get back, allowing us to specify the first and last rows. In the same way, we can now limit the columns by specifying the first and last columns. If we want to deviate from the line break as the line separator and the semicolon as the column separator, we can also define this in the parameters. Last but not least, we can specify a flag that determines whether our values should all be returned as text. If we want this, we write 1 in the flags.

MBS( "FM.SQL.CSV"; SQLref { ; FirstRow; LastRow; firstCol; lastCol; 
   rowSeparator; colSeparator; Flags } )  

Let's try it out on our database. We would like to output the movies released between 2000 and 2010 as CSV. First, we develop the query for this and then insert it into the function just described. We would like to keep all specified rows and columns, use the standard separators, and not set the flag. Therefore, we can ignore the optional parameters in this example.

Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName);
   "SELECT Name,Launch,Director 
   FROM Movie 
   WHERE Launch>=2000 AND Launch<=2010 ORDER BY Launch" ) ]
Set Variable [ $r ; Value: MBS("FM.SQL.CSV"; $SQLRes) ]
Show Custom Dialog [ $r ]
Set Variable [ $r ; Value: MBS("FM.SQL.ReleaseAll") ]

But we can not only generate CSV text from our FM database, we can also add new records with CSV text. To do this, we use the FM.InsertRecordCSV function. Here, we first indicate in the parameters the file in which we want to insert the data, as well as the table name. Then we list the field names we want to fill with data, followed by the CSV text. It is important to ensure that the CSV text has exactly as many columns as the fields we specify, otherwise an error will occur. If there is a field in the CSV that should remain empty, the separators must still be set, but the space between the separators remains empty. Optionally, we can now specify the separator and one or more fields that do not appear in the CSV but are to be filled in the database and remain the same for all inserted records. Let's look at a concrete example of how this function is used.

Here we have four movies that we want to add to our Movie table:

Richie Rich's Christmas Wish;John Murlowski;1998;
A Dennis the Menace Christmas;Ron Oliver; 2007;
Beethoven’s Christmas Adventure;John Putch;2011;
The Santa Clause 3: The Escape Clause;Michael Lembeck; 2006;110800000

We see that the first three films do not specify a generated sale, so we leave the information blank.

Now we can insert our values into the function:

# CSV Import
Set Variable [ $Import ; Value: MBS("FM.InsertRecordCSV"; Get(FileName); "Movie"; 
   "Name¶Director¶Launch¶Generated_Sales"; 
   "Richie Rich's Christmas Wish;John Murlowski;1998;¶
   A Dennis the Menace Christmas;Ron Oliver; 2007;¶
   Beethoven’s Christmas Adventure;John Putch;2011;¶
   The Santa Clause 3: The Escape Clause;Michael Lembeck; 2006;110800000";
   ";"; "Genre"; "Christmas comedy") ]
Show Custom Dialog [ $import ]

First, we see that the file name is specified again with Get(FileName), just like our table name Movie. This is followed by the list of fields that we want to fill in the database. Here, we select Name, Director, Launch, and Generated_Sales. Now comes the CSV-formatted text we saw earlier. Please pay attention to the line breaks here, otherwise it will be seen as a single line and an error will occur. We use the standard semicolon as the separator and then we want to specify a field that should be set for all CSV-imported records in the database. We set the Genre field to Christmas comedy for each data record. We also see that a dialog box is displayed after the call, showing us the result of the function. This result is the number of imported data records and should return a 4 if everything works. This is what the new entries in the database look like:

Now there is one last feature I would like to introduce to you today, because we can import data not only as CSV, but also as TSV. TSV (Tab-Separated Values) separates the values with a tab character. Because tabs hardly ever occur in normal text content, TSV is often more robust and easier to process in practice. The data is easy for humans to read. However, TSV is not as well known as CSV.

The biggest difference between CSV and TSV is therefore the separator.

So we also want to import our data as TSV. The data then looks like this:

Richie Rich's Christmas Wish	John Murlowski	1998
A Dennis the Menace Christmas	Ron Oliver	 2007
Beethoven’s Christmas Adventure	John Putch	2011
The Santa Clause 3: The Escape Clause	Michael Lembeck	 2006	110800000

The separator is a tab. We already mentioned that we can have empty fields in CSV by simply omitting the value between the separators. The same applies to TSV. However, one difficulty is that you cannot see the last tab, because in the entries where the generated sale is missing, there is still a tab after the year. The function has a similar parameter assignment to the previous function. Here, too, the file name and table name are specified first. Then come the fields as a list and the TSV-formatted text. We cannot specify a different separator here. So we stay with the tab. Again, if necessary, we can specify one or more fields with the corresponding value that should apply to all imported TSV records. This function also returns the number of imported records.

# TSV Import 
Set Variable [ $importTSV ; Value: MBS( "FM.InsertRecordTSV"; Get(FileName); "Movie"; 
   "Name¶Director¶Launch¶Generated_Sales"; 
   "Richie Rich's Christmas Wish	John Murlowski	1998	 ¶
   A Dennis the Menace Christmas	Ron Oliver	 2007	 ¶
   Beethoven’s Christmas Adventure	John Putch	2011	 ¶
   The Santa Clause 3: The Escape Clause	Michael Lembeck	 2006	110800000" ; "Genre"; "Christmas comedy" ]
 
Show Custom Dialog [ "TSV Import" ; $importTSV ]

That's all for today. I hope you enjoyed it, and let's meet again tomorrow.

[MonkeybreadSoftware]

Welcome to door 20 of this advent calendar. We already saw in door 7 how to copy a part of a table. There, we read each field individually and then wrote it to a new table. But there is an easier way to do this. With the FM.InsertRecordQuery function. This function can transfer certain entries from one table to another.

MBS( "FM.InsertRecordQuery"; InsertFileName; InsertTableName; FieldNames; QueryFileName; 
   SQL Statement { ; Params... } )  

First, we specify the file name and table name of the table into which the data is to be inserted. This is followed by a list of the field names into which the data is to be written. Then comes the file name of the file on which the SQL query is to run, followed by the SQL query itself. If parameters are used in the SQL query, you can specify them afterwards.

Let's look at an example: We create a new table called Door 20 and want to copy all movies from the Movie table that were released between 2000 and 2009 into this empty table. Our Door 20 table has the fields Name, Director, Launch, and Generated_Sale. Our function then looks like this:

Set Variable [ $r ; Value: MBS("FM.InsertRecordQuery"; Get(FileName); "Door20"; 
   "Name¶Director¶Launch¶Generated_Sales"; Get(FileName); 
   "SELECT Name, Director, Launch, Generated_Sales FROM Movie WHERE Launch>=2000 AND Launch <=2009 ") ]

We can again solve this task with SQL.

INSERT INTO InsertTable (Field1, Field2…)
SELECT Field1, Field2…
FROM QueryTable
WHERE condition

The keyword here is INSERT INTO, followed by the table into which we want to insert the records. If we want to fill all fields from this table, we can write an * after it, but if we want to fill individual fields of this table with the records, we write the fields in brackets and separated by commas. Then we write our normal SQL query on the database that provides us with the data records.

Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "INSERT INTO Door20(Name, Director, Launch, Generated_Sales) 
   SELECT Name, Director, Launch,Generated_Sales 
   FROM Movie 
   WHERE Launch>=2000 AND Launch <=2009" ) ]

Here, too, the rows were copied into the new table.

That brings us to the end of today's door. I hope you enjoyed it and that you'll be back tomorrow for the 21st door.